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 togather. 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.

--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,

file

store some data in files, good if you are going to work with really big databases, e.g. with large blobs.

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 recomendation 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).

--format, -f

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

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 three times. If it is mentioned once you will see critical DB errors, two -Ds in a line enable DB warnings, three times means 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.

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:kstep@p-nut.info>

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.