NAME

fusqlfs - FUSE file system to mount DB and provide tools to control and admin it

SYNOPSIS

fusqlfs [options] database directory

EXAMPLES

fusqlfs dbname ./mnt
fusqlfs --host=localhost --port=5432 --engine=PgSQL --user=postgres --password=12345 dbname ./mnt
fusqlfs --database=dbname --user=postgres --mountpoint=./mnt
fusqlfs -d dbname -m ./mnt -u postgres -p 12345 -e PgSQL

OPTIONS

Basic options

--host, -h

Host name to connect, defaults to localhost.

--port, -P

Port number to connect to, default depends on database engine in use.

--user, -u

Username to authorize.

--password, -p

Password to authorize.

--database, --db, -d

Database name to connect to. Mandatory.

--mountpoint, -m

Mointpoint, must be an empty directory. Mandatory.

--engine, -e

DB engine to use. Can be either PgSQL or MySQL for now. PgSQL is really implemented, MySQL is in my todo list. Defaults to PgSQL.

Other options with values

--logfile, -l

Filename to store debug output to in daemon mode. Doesn't have any meaning in foreground mode, as all debug output are sent to STDERR in this case. Default is don't store debug data anywhere. This option has sense only with "--daemon" option set and at least one --debug option in command line.

--charset, -C

Default charset, used for tables creation, results display etc. Defaults to current locale's charset.

--fnsep, -s

File name fields separator, used to compose filenames out from multi-field primary keys. If you have table with primary key like (obj_id, name), every record in DB will be visible as a file with its name composed of this two fields (like "12.odrie", "43.nanny" etc.) This option's value is used as a separator to glue field values together. Defaults to single dot (.). You may wish to change it if you have table with text fields in primary key with a dot in them.

For tables without primary keys, see --namemap options below.

--namemap, -n

Table-to-fields map to format filenames for data rows. Normally data records filenames are composed from primary key fields concatenated with fields separator (see --fnsep option above). But sometimes you can have tables without primary keys, so this algorithm will not work and you won't be able to look inside such tables data. To enable data lookup feature for such tables you can use --namemap options. This option can be repeated multiple times, each --namemap option defines single table-to-fields mapping in format: tablename:fieldname1,fieldname2,...,fieldnamen, so this fields list will be used for given table instead of primary key fields.

For example there're a lot of tables without primary keys in INFORMATION_SCHEMA database of MySQL, but mounting this system database can be very helpful to look inside MySQL schema definitions. You can use the following command to mount INFORMATION_SCHEMA and be able to review TABLES table with tables metadata:

fusqlfs --engine mysql --host localhost --database information_schema --mountpount ~/mnt/db --namemap TABLES:TABLE_SCHEMA,TABLE_NAME

A few notices: 1) table and field names in these mappings are case sensitive, if you put --namemap tables:table_schema,table_name option into command above you would end up with empty data directory for TABLES table, 2) the field names per table you provide must identify each table row in a unique way (i.e. be real primary key, even if not marked so with PRIMARY KEY in table definition), otherwise you will get unexpected behavior (for instance, you won't be able to edit each row consistently, as what "file" you read may not match the "file" you write to, so you can clobber your data in no time).

--limit, -L

Integer, number of data rows to show in table's data subdir, defaults to 0 (means all rows). Useful if you are going to browse really big databases, as listing all data records as files can be very slow and memory consuming.

You can also try to change and tune your cache strategy with --cache and --cache-limit options (see below).

If this is an issue for you, use this option to limit number of listed table rows. You can still get record by requesting filename equal to primary key value (id usually) directly, if you know it, even if you don't see it in directory listing.

--cache, -c

Cache strategy to choose. There're three strategies for now (in order of speed decreasing):

memory

store everything in memory, fastest but can be overkill for your memory in case of big databases,

limited

like memory, but number of stored elements is limited to some defined value, and cache is cleared down to this limit if it's exceeded (least used entries removed first); good if you have limited memory space (see FusqlFS::Cache::Limited for details),

file

store some data in files, good if you are going to work with really big databases, e.g. with large blobs (see FusqlFS::Cache::File for details).

For details see --cache-limit option description below.

--cache-limit, -M

Integer, threshold for cache strategy (see --cache option for details), defaults to 0, which means back up to "memory" strategy. Meaning depends on chosen cache strategy.

For "limited" cache strategy it means number of max cache items to store. If number of cached items exceeds this value, cache cleanup is forced, least used entries removed first.

I recommend to set this value to at least 3/4 of total objects in your database (including all tables, sequences, views, data rows and other objects, browsable with this program), which is about 60% cache hits (~45% for 1/2 and ~56% for 2/3). But this is just a basic recommendation based on educated guess and some tests with "entry" names generated with normally distributed random generator. Experiment is your best advisor in this case.

For "file" strategy it means min entry size in bytes to store on disk (caches simple files only for now, dirs/symlinks and the like are not cached, which is ok in most cases). If file size is smaller then given size, then it is stored in memory. Useful if you are going to review big chucks of data in your database, e.g. large blobs etc.

Common advice: set --cache-limit above zero and --cache to anything but "memory" only if you really have low memory issues with the program, as all other cache strategies are slower than simple memory access. "Limited" cache method has to support additional structures to analyze data usage, and so it's slower than simple "memory" cache strategy, while "file" cache method has to check and update real files on your disk, so both of them are slower than simple "memory" caching (they're still faster than database requests, however).

See also FusqlFS::Cache, FusqlFS::Cache::Base, FusqlFS::Cache::File and FusqlFS::Cache::Limited for detailed description of cache strategies.

--format, -f

Format used to output different text data, like rows, columns description etc. Can be "xml", "json", "yaml", "html", "htmltable" or "native" for now, defaults to "native".

Note a special case of "native" format: it doesn't encode data into any specific text-based format, instead it preserves inner Perl structures. The effect is all formatted files are represented as directories, each field value being regular file with data. For details see FusqlFS::Formatter::Native.

Other special formats are "html" and "htmltable". They are dump-only formats, they produce HTML output (HTML lists for "html" or tables for "htmltable") but don't parse this format back into Perl structures, so you can't edit and save database data while using these formats. For details see FusqlFS::Formatter::Html and FusqlFS::Formatter::Htmltable.

For writing your own formatters see FusqlFS::Formatter.

Boolean options

--innodb

Boolean, MySQL specific. If set, new tables created by the program use InnoDB backend, MyISAM is used otherwise. Defaults to false (MyISAM).

--debug, -D

Incremental, output debug info. You can repeat this option up to five times. There're following debug levels:

1. show critical DB errors,

2. show DB warnings as well,

3. show full SQL statements which caused errors,

4. show full stack trace on errors,

5. show FUSE debug tracing output (really noisy).

Default is no debug messages. See also --logfile to enable debug output logging in daemon mode. If you don't set logfile in daemon mode you won't see any debug output, no matter how many -Ds you put in command line.

--daemon

Boolean, if set the program will daemonize itself. Defaults to true. You may wish to use it as --nodeamon to debug the program.

--mkdir, -k

Boolean, if set the program will try to create mountpoint directory if it doesn't exist yet. Default is false.

--rmdir, -K

Boolean, if set the program will try to remove mountpoint directory after exit. Default is false.

Store options into configuration files

FusqlFS leverages Getopt::ArgvFile package to load configuration from files.

Long story short, you can put @filename on command line, it will interpolate contents of the file into command line. You can place as many @-clauses into command line as you want. This way you can store some common options into a file and use it in fusqlfs invocation. For instance, you can place credentials and database name into databaserc file and then just call fusqlfs @databaserc to mount it.

As an alternative to @-prefix you can use -r option instead to load configuration from given file, so you can write -r filename instead of @filename. This is useful to put into shebang line in fusqlfs config file:

#!/usr/bin/fusqlfs -r
--user=root
--password=pa$$w0rd
--host=localhost
--engine=mysql

Then you need to give execute permission to the config file with chmod a+x and run it directly from anywhere.

DESCRIPTION

This FUSE-daemon allows to mount any DB as a simple filesystem. Unlike other similar "sqlfs" filesystem, it doesn't provide simple DB-backed file storage, but given you full interface to all database internals.

Every table, view, function etc. is a directory, every single field, index, record etc. is a file, symlink or subdirectory in the mounted filesystem. You can create table "mkdir ./mnt/tables/tablename", and remove them with "rmdir" afterwards. You can edit fields as simple YAML-files. All your usual file utilities are at your service including "find", "rm", "ln", "mv", "cp" etc.

Just mount your DB and enjoy!

TODO

• Implement MySQL support.

• Implement PgSQL triggers.

• Write better docs: describe FS structure, rules and precautions to use it as DB admin tool.

AUTHOR

© 2010, Konstantin Stepanov <mailto:me@kstep.me>

LICENSE

This product is distributed AS IS without any warrantly under General Public License v.3 or higher in hope it will be useful for somebody.

License text is included in LICENSE file in this distribution.