NAME
sqlfs.pl - Mount Fuse filesystem on a SQL database
SYNOPSIS
% sqlfs.pl [options] dbi:<driver_name>:database=<name>;<other_args> <mount point>Options:
--initialize initialize an empty filesystem
--quiet don't ask for confirmation of initialization
--allow_magic allow "magic" directories (see below)
--unmount unmount the indicated directory
--foreground remain in foreground (false)
--nothreads disable threads (false)
--debug enable Fuse debugging messages
--module=<ModuleName> Use a subclass of DBI::Filesystem
--option=allow_other allow other accounts to access filesystem (false)
--option=default_permissions enable permission checking by kernel (false)
--option=fsname=<name> set filesystem name (none)
--option=use_ino let filesystem set inode numbers (false)
--option=direct_io disable page cache (false)
--option=nonempty allow mounts over non-empty file/dir (false)
--option=ro mount read-only
-o ro,direct_io,etc shorter version of options
--help this text
--man full manual pageOptions can be abbreviated to single letters.
DESCRIPTION
This script will create a userspace filesystem stored entirely in a SQL database. Only the MySQL, SQLite and PostgreSQL database engines are currently supported. Most functionality is supported, including hard and symbolic links, seek() and tell(), binary data, sparse files, and the ability to unlink a file while there is still a filehandle open on it.
The mandatory first argument is a DBI driver database source name, such as:
dbi:mysql:database=my_filesystemThe database must already exist, and you must have insert, update, create table, and select privileges on it. If you need to provide hostname, port, username, etc, these must be included in the source string, e.g.:
dbi:mysql:database=my_filesystem;host=my_host;user=fred;password=xyzzyIf you request unmounting (using --unmount or -u), the first non-option argument is interpreted as the mountpoint, not database name.
After initial checks, this command will go into the background. To keep it in the foreground, pass the --foreground option. Interrupting the foreground process will (try to) unmount the filesystem.
MORE INFORMATION
This is a front end to the DBI::Filesystem module, which creates a fully-functioning userspace filesystem on top of a relational database. Unlike other filesystem-to-DBM mappings, such as Fuse::DBI, this one creates and manages a specific schema designed to support filesystem operations. If you wish to mount a filesystem on an arbitrary DBM schema, you want Fuse::DBI, not this.
Most filesystem functionality is implemented, including hard and soft links, sparse files, ownership and access modes, UNIX permission checking and random access to binary files. Very large files (up to multiple gigabytes) are supported without performance degradation.
Why would you use this? The main reason is that it allows you to use DBMs functionality such as accessibility over the network, database replication, failover, etc. In addition, the underlying DBI::Filesystem module can be extended via subclassing to allow additional functionality such as arbitrary access control rules, searchable file and directory metadata, full-text indexing of file contents, etc.
"Magic" Directories
The --allow_magic option enables a form of "view" directory in which the directory is automagically populated with the results of running a simple (or complex) SQL query across the entire filesystem. To try this out, create one or more directories that begin with the magic characters "%%", and then create a dotfile within this directory named ".query". ".query" must contain a SQL query that returns a series of one or more inodes. These will be used to populate the directory automagically. The query can span multiple lines, and lines that begin with "#" will be ignored.
You must understand the simple schema used by this module to be able to write such queries. To learn about the schema, see DBI::Filesystem.
Here is a simple example which will run on all DBMSs:
# display all files greater than 2 Mb in size
select inode from metadata where size>2000000Another example, which uses MySQL-specific date/time math:
# all .jpg files created/modified within the last day
select m.inode from metadata as m,path as p
where p.name like '%.jpg'
and (now()-interval 1 day) <= m.mtime
and m.inode=p.inode(The date/time math syntax is very slightly different for PostgreSQL and very much different for SQLite)
The files contained within the magic directories can be read and written just like normal files, but cannot be removed or renamed. Directories are excluded from magic directories. If two or more files from different parts of the filesystem have name clashes, the filesystem will append a number to their end to distinguish them.
Unsupported Features
The following features are not implemented:
* statfs -- df on the filesystem will not provide any useful information
on free space or other filesystem information.
* nanosecond times -- atime, mtime and ctime are accurate only to the
second.
* ioctl -- none are supported
* poll -- polling on the filesystem to detect file update events will not work.
* lock -- file handle locking among processes running on the local machine
works, but protocol-level locking, which would allow cooperative
locks on different machines talking to the same database server,
is not implemented.You must be the superuser in order to create a file system with the suid and dev features enabled, and must invoke this commmand with the mount options "allow_other", "suid" and/or "dev":
-o dev,suid,allow_otherSupported Database Management Systems
DBMSs differ in what subsets of the SQL language they support, supported datatypes, date/time handling, and support for large binary objects. DBI::Filesystem currently supports MySQL, PostgreSQL and SQLite. Other DBMSs can be supported by creating a subclass file named, e.g. DBI::Filesystem:Oracle, where the last part of the class name corresponds to the DBD driver name ("Oracle" in this example). See DBI::Filesystem::SQLite, DBI::Filesystem::mysql and DBI::Filesystem:Pg for an illustration of the methods that need to be defined/overridden.
Fuse Installation Notes
For best performance, you will need to run this filesystem using a version of Perl that supports IThreads. Otherwise it will fall back to non-threaded mode, which will introduce occasional delays during directory listings and have notably slower performance when reading from more than one file simultaneously.
If you are running Perl 5.14 or higher, you *MUST* use at least 0.15 of the Perl Fuse module. At the time this was written, the version of Fuse 0.15 on CPAN was failing its regression tests on many platforms. I have found that the easiest way to get a fully operational Fuse module is to clone and compile a patched version of the source, following this recipe:
$ git clone git://github.com/dpavlin/perl-fuse.git
$ cd perl-fuse
$ perl Makefile.PL
$ make test (optional)
$ sudo make installAUTHOR
Copyright 2013, Lincoln D. Stein <lincoln.stein@gmail.com>
LICENSE
This package is distributed under the terms of the Perl Artistic License 2.0. See http://www.perlfoundation.org/artistic_license_2_0.