The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
sqlfs-perl-1.04
River stage zero No dependents
2 ++
/ sqlfs.pl

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 page

Options 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_filesystem

The 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=xyzzy

If 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>2000000

Another 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_other

Supported 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 install

AUTHOR

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.