NAME
DBIx::FileSystem - Manage tables like a filesystem
SYNOPSIS
use DBIx::FileSystem;
my %vdirs =
(
table_one =>
{
# ... column description here ...
},
table_two =>
{
# ... column description here ...
},
);
if( $#ARGV==0 and $ARGV[0] eq 'recreatedb' ) {
recreatedb(%vdirs, $PROGNAME, $VERSION,
$DBHOST, $DBUSER, $DBPWD);
}else{
# start the command line shell
mainloop(%vdirs, $PROGNAME, $VERSION,
$DBHOST, $DBUSER, $DBPWD);
}This synopsis shows the program (aka 'the shell') to manage the database tables given in hash %vdirs.
DESCRIPTION
The module DBIx::FileSystem offers you a filesystem like view to database tables. To interact with the database tables, FileSystem implements a command line shell which offers not only a subset of well known shell commands to navigate, view and manipulate data in tables, but also gives the convenience of history, command line editing and tab completion. FileSystem sees the database as a filesystem: each table is a different directory with the tablename as the directory name and each row in a table is a file within that directory.
The motivation for FileSystem was the need for a terminal based configuration interface to manipulate database entries which are used as configuration data by a server process. FileSystem is neither complete nor a replacement for dbish or other full-feature SQL shells or editors. Think of FileSystem as a replacement for a Web/CGI based graphical user interface for manipulating database contents.
REQUIREMENTS
The DBI module for database connections. A DBD module used by DBI for a database system. And, recommended, Term::ReadLine::Gnu, to make command line editing more comfortable, because perl offers only stub function calls for Term::ReadLine. Note: Term::ReadLine::Gnu requires the Gnu readline library installed.
FUNCTIONS
recreatedb(%vdirs,$PROGNAME,$VERSION,$DBHOST,$DBUSER,$DBPWD);
Recreate the tables given in %vdirs. Will destroy any existing tables with the same name in the database including their contents. Will create a table 'tablestatus' for version information. Tables not mentioned in %vdirs will not be altered. The database itself will not be dropped. Checks if %vdirs is valid. Returns nothing.
mainloop(%vdirs,$PROGNAME,$VERSION,$DBHOST,$DBUSER,$DBPWD);
Start the interactive shell for the directory structure given in %vdirs. Returns when the user quits the shell. Checks if %vdirs is valid.
parameters
- %vdirs
-
A hash of hashes describing the database layout which will be under control of FileSystem. See DATABASE LAYOUT below for details.
- $PROGNAME
-
The symbolic name of the interactive shell. Used for errormessages and command prompt.
- $VERSION
-
A character string with max. length of 16. Holds the version number of the database layout. See VERSION INFORMATION below for details.
- $DBHOST
-
DBI connect string to an existing database. Depends on the underlying database. Example: "dbi:Pg:dbname=myconfig;host=the_host";
- $DBUSER
-
DBI database user needed to connect to the database given in $DBHOST.
- $DBPWD
-
DBI password needed by $DBUSER to connect to the database given in $DBHOST. May be set to undef if no password checking is done.
TRANSACTIONS
FileSystem uses autocommit when talking with the database. All operations done by FileSystem consist of one single SQL command.
DATABASE LAYOUT
FileSystem sees a table as a directory which contains zero or more files. Each row in the table is a file, where the filename is defined by a column configured with the %vdirs hash. Each file holds some variable = value pairs. All files in a directory are of the same structure given by the table layout. A variable is an alias for a column name, the value of the variable is the contents of the database.
When editing a file FileSystem generates a temporary configuration file with comments for each variable and descriptive variable names instead of column names. The variable names and comments are defined in %vdirs hash as shown below. So, in the following description:
'directory' is a synonym for 'table'
'file' is a synonym for 'row',
'variable' is a synonym for 'column'DEFAULTFILE FUNCTION
Each directory optionally supports a defaultfile. The idea: If a variable in a file has value NULL then the value of the defaultfile will be used instead. The application using the database (for reading configuration data from it) has to take care of a defaultfile.
FileSystem knows about a defaultfile when viewing a file and shows the values from the defaultfile when a variable contains NULL. A defaultfile can not be removed with 'rm'.
%vdirs hash
The %vdirs hash defines the database layout. It is a hash of hashes.
%vdirs = (
DIRECTORY_SETTING,
DIRECTORY_SETTING,
# ... more directory settings ...
);The DIRECTORY_SETTING defines the layout of a directory (database table):
# mandatory: the directory name itself
dirname => {
# mandatory: description of table
desc => "dir description",
# mandatory: Defines if this directory is read-
# only or writable for the shell. If set to 1
# then the commands new/vi/rm are allowed.
edit => [0|1],
# mandatory: The column which acts as filename.
# The column must be of type 'char' and len
# must be set.
fnamcol => 'colname',
# optional: Name of a default file. This file
# will be automatically created from
# &recreatedb() and cannot be removed. The
# defaultfile is only usefull when edit = 1.
defaultfile => 'filename',
# optional: Function reference to a function
# that will be called when a file of this
# directory will be removed. The rm command
# will call this function with parameters
# ($dir, $file) of the file to be removed
# and after all other builtin checks are done.
# The function has to return undef if remove
# is ok, or a one line error message if it is
# not ok to remove the file
rmcheck => \&myRmCheckFunction,
# mandatory: column settings
cols => {
COLUMN_SETTING,
COLUMN_SETTING,
# ... more column settings ...
},
},The COLUMN_SETTING defines the layout of a column (database column).
# mandatory: the columnname itself
colname => {
# mandatory: column type of this column
# (see below COLUMN_TYPE)
COLUMN_TYPE,
# optional: extra constraints for this column
# when creating the database with
# &recreatedb(). Example: 'NOT NULL'
colopt => 'OPTIONS',
# optional: Function reference to a function
# that will be called before a value gets
# inserted/updated for this column and after
# builtin type, length, range and reference
# checks has been done. Gets the new
# value as a parameter. Returns undef
# if the value is ok or a one line error
# message if the value is not ok.
valok => \&myValCheck,
# optional: When this option exists and is set
# to 1 then this column will be set to NULL
# when copying af file with 'cp'.
delcp => 1,
# mandatory: Descriptive long variable name
# for this column. Will be used as an alias
# for the columname for display or edit/vi.
var => 'VarName',
# mandatory: One line description what this
# variable is good for. Will be show up as
# a comment when displaying (cat) or editing
# (vi) this file.
desc => "...text...",
# mandatory: A counter used to sort the columns
# for display/editing. Smaller numbers come
# first. See example pawactl how to setup.
pos => NUMBER,
},The COLUMN_TYPE defines, if the column is a normal database column or a reference to another file in another directory. A column is either a normal column or a ref-column.
normal column:
# mandatory: database type for this column.
# Allowed types:
# - when this column acts as the filename
# ('fnamcol'in DIRECTORY_SETTING): char
# - when edit=1 set in DIRECTORY_SETTING:
# char, int, smallint
# - when edit=0 set in DIRECTORY_SETTING:
# char, int, smallint, date, bool, ...
type => 'dbtype',
# optional: length of column. Only usefull
# when type => 'char'. Mandatory if this
# column is used as the filename.
len => NUMBER, ref-column:
# mandatory: A directory name of another
# directory. Allowed values for this variable
# will be existing filenames from directory
# 'dirname' or NULL. rm uses this information
# to check for references before removing a
# file. editing/vi uses this information to
# check a saved file for valid values.
ref => 'dirname', DATABASE CONSTRAINTS
The user can set database constraints for scpecific columns with the colopt option in %vdirs. FileSystem takes care of these constraints and reports any errors regarding the use of these constraints to the user. Because the errormessages (from the DBI/DBD subsystem) are sometimes not what the user expects it is a good idea to use the custom rmcheck and valok functions within %vdirs together with database constraints. This has more advantages:
When using database constraints the database takes care about integrity. Other programs than FileSystem can not destroy the integrity of the database.
FileSystem, rmcheck and valok custom functions report 'understandable' error messages to the user, they also report the errornous line number to the editor after editing and saving an odd file. Database errors have no line numbers.
FileSystem functions, rmcheck and valok custom functions will be called just before a database operation. If they fail, the database operation will not take place.
FileSystem may be buggy.
VERSION INFORMATION
When using FileSystem for managing configuration data for a server process, you have three versions of database layout in use:
database layout given in %vdirs hash
database layout in the database itself
database layout the server process expects
To make sure that all three participants use the same database layout FileSystem supports a simple version number control. Besides the tables given in %vdirs FileSystem also creates a table called 'tablestatus'. This table has two columns, tag and value, both of type char(16). FileSystem inserts one entry 'version' when recreating the database and inserts the version string given as parameter to &recreatedb.
Before doing any operations on the database when calling &mainloop(), FileSystem first checks if the version string given as parameter to &mainloop() matches the version string from database in table 'tablestatus', row 'version', column 'value'. If they do not match, FileSystem terminates with an error messages.
When modifying the %vdirs hash it is strongly recommended to change/increment the version number given to &mainloop() also. To be on the safe side you should recreate the database after changing %vdirs. Keep in mind that you will loose all data in the tables when calling &recreatedb(). Alternative way: Modify %vdirs and increment the version string for the &mainloop() call. Then start your favourite SQL editor and manually change the database layout according to %vdirs.
The server process should take care of the version number in 'tablestatus' also.
COMMAND SHELL
The command line shell offers command line history, tab completion and commandline editing by using the functionality by using the installed readline library. See the manpage readline(3) for details and key bindings.
Supported commands are:
- cat
-
Usage: 'cat FILE'. Show a file contents including generated comments.
- cd
-
Usage: 'cd DIR'. Change to directory DIR. The directory hierarchy is flat, there is no root directory. The user can change to any directory any time. You can only change to directories mentioned in the %vdirs structure. FileSystem does not analyze the system catalog of the underlying database.
- cp
-
Usage: 'cp OLD NEW'. Copy file OLD to file NEW (clone a file). When copying, the variables marked as 'delcp' will be set to NULL in file NEW. Requires write access to the directory.
- help
-
Usage: 'help [command]'. Show a brief command description.
- ls
-
Usage: 'ls'. Show the contents of the current directory. The %vdirs hash defines, which columns are used as a filename.
- new
-
Usage: 'new FILE'. Create a new FILE in the current directory. All variables are set to NULL. Requires write access to the directory. new will fail if a column has a 'NOT NULL' constraint. Use 'vi' instead.
- quit
-
Usage: 'quit'. Just quit.
- rm
-
Usage: 'rm FILE'. Remove FILE. You can only remove files that are not referenced. Reference checks are done by FileSystem using the reference hierarchy given in the %vdirs hash. To un-reference a file set the reference entry in the referring file to NULL, to another file or remove the referring file. 'rm' requires write access to the directory.
- sum
-
Usage: 'sum FILE'. Show the summary of FILE. The summary only shows the variables and their values, without any comments. 'sum' knows about the 'defaultfile': If a FILE has variables = NULL and a defaultfile is given, then sum shows '->' and the value of the defaultfile instead of '=' and the value of the variable.
- ver
-
Usage: 'ver'. Show version information.
- vi
-
Usage: 'vi FILE'. Edit FILE with an editor. Starts the default editor given in the shell environment variable $EDITOR. If this is not defined, it starts
/usr/bin/vi. After quitting the editor the file will be checked for proper format and values. If there is something wrong, the user will be asked to reedit the file or to throwaway the file.In case of reediting a file because saving was rejected, the editor is started over with '+LINENO' as the first parameter to let the cursor directly jump to the error line. If the editor given in $EDITOR does not support this syntax an error will occur.
If FILE does not exist it will be created after saving and quitting the editor. This is usefull when a column has a 'NOT NULL' constraint.
Note: Only the values will be saved in the database. All comments made in the file will get lost. If you need comments, add a 'comment' Variable for this directory in %vdirs.
Note: The file parser currently is very simple. Currently it is not possible to assign a string of spaces to a variable.
- wrefs
-
Usage: 'wrefs FILE'. Show who references FILE. Reference checks are done by FileSystem using the reference hierarchy given in the %vdirs hash. Other references to FILE will not be detected because FileSystem does not read the system catalog of the database. Note: A non-existing FILE will not be referenced by anyone.
BUGS
AUTHOR
Alexander Haderer alexander.haderer@charite.de
SEE ALSO
perl(1), DBI(3), dbish(1), readline(3)