The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.

NAME

DBIx::FileSystem - Manage tables like a filesystem

SYNOPSIS

  ##############################################################
  # access the data using the access class
  ##############################################################
  use DBIx::FileSystem qw( :symbols );

  my $fs = new DBIx::FileSystem( dbconn => $DBCONN,
                                 dbuser => $DBUSER,
                                 dbpasswd => $DBPWD,
                                 progdbver => $PROGDBVER );

  my $fs = new DBIx::FileSystem( rdbh => \$dbh_set_elsehwere,
                                 progdbver => $PROGDBVER );
  my %vars = ( column_1 => undef,       # columns to read from db
               column_2 => undef   );

  my %searchvars = ( column_a => "myvalue",     # the WHERE part
                     column_b => 1234,
                     column_c => [ 'LIKE', 'abc%' ] );

  $r = $fs->get_conf_by_var( $dir, $default_filename, $filename_column,
                             \%vars, \%searchvars );
  die $fs->get_err if $r != OK;


  ##############################################################
  # implement the interactive configure shell
  ##############################################################

  use DBIx::FileSystem qw( mainloop recreatedb );
  my %vdirs = 
  ( 
     table_one => 
     { 
       # ... column description here ...
     },
     table_two => 
     { 
       # ... column description here ...
     },
  );

  my %customcmds = ();

  if( $#ARGV==0 and $ARGV[0] eq 'recreatedb' ) {
    recreatedb(%vdirs, $PROGNAME, $VERSION, 
               $DBCONN, $DBUSER, $DBPWD);
  }else{
    # start the command line shell
    mainloop(%vdirs, $PROGNAME, $VERSION, 
             $DBCONN, $DBUSER, $DBPWD, %customcmds );
  }

The upper synopsis shows how to access the data using the access class. The lower 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:

-

An access class to read the data edited by the shell.

-

A user interface as 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.

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. 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 via the shell's 'vi' command, 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. The access class implented in DBIx::FileSystem handles a defaultfile correctly.

FileSystem's shell 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'.

The chell commands cat, sum, and vgrep show the usage of the default value by showing '->' instead of '=' when printing content. Example: "MyVar -> 1234". Here the value of MyVar is NULL in the database and the default value (1234) from the defaultfile is printed.

METHODS (access class)

new()

Constructor. Parameters are:

dbconn

Mandatory unless parameter rdbh is set DBI connect string to an existing database. Depends on the underlying database. Example: "dbi:Pg:dbname=myconfig;host=the_host";

dbuser

May be undef. DBI database user needed to connect to the database given in $DBCONN.

dbpasswd

May be undef. DBI password needed by $DBUSER to connect to the database given in $DBCONN. May be set to undef if no password checking is done.

rdbh

Mandatory unless parameter dbconn, dbuser, and dbpasswd are set. A reference to an DBI database handle. Its up to ther user to connect to the database before accessing data and to disconnect after everything has been done. Method database_bad() should be called after 'new' to check the version number (expect a connected DBI handle). The database must contain the tablestatus table defined elsehwere in this document, see VERSION INFORMATION.

progdbver

Mandatory. A character string with max. length of 16. Holds the version number of the database layout. See VERSION INFORMATION elsewhere in this document.

database_bad()

Checks the status of the database (connected, right version number). Returns 0 if database is ok, 1 otherwhise. Sets the errorstring for method 'get_err()'

get_err()

Returns the last error message as a string. May be a multiline string, because some DBD drivers report errors in multiline format. The error message corresponds to the last DBIx::FileSystem method that has not returned OK.

get_conf_by_var()

Read data from the database.

$r = get_conf_by_var( $dir, $default_fname, $fname_column, \%v, \%sv )

Parameters are:

$dir

String. Input parameter. The name of the directory / database table to search in.

$default_fname

String. Input parameter. The filename (symbolic name) of the defaultfile. If no defaultfile is defined for this $dir: undef. See section "DEFAULTFILE FUNCTION" elsewhere in this document.

$fname_column

String. Input parameter. The name of the column holding the filename (symobic name) for the files. This parameter is called 'fnamcol' in %vdir hash for the shell.

\%v

Hashref 'vars'. Input and output parameter. The columns we want to read from the database. Keys: the column-names to fetch. Values: When get_conf_by_var() returns OK: the values from database, else: undef. When calling with $default_fname not undef, get_conf_by_var() will replace all database NULL values in the result with the values found in $default_file. See section "DEFAULTFILE FUNCTION" elsewhere in this document.

\%sv

Hashref 'searchvars'. Input parameter. The filter critieria (SQL: WHERE part) for searching the database. Each key-value pair results in one SQL compare term. If more than one key-value pair is given in %sv, the compare terms are ANDed. Keys: the column-names to use for the compare. Values: either the value itself (undef matches NULL in db) using compare operator '=', or an anon array-ref with $aref->[0] = the SQL compare operator as a string and $aref->[1] = the value used for compare (undef matches NULL in db). Examples:

                                        # --> SQL WHERE part:
  my %sv = ( a => "myvalue",            # a = 'myvalue' AND
             b => 1234,                 # b = 1234      AND
             c => undef                 # c IS NULL     AND
             d => [ 'LIKE', 'abc%' ] ); # d LIKE 'abc%' AND
             e => [ '<', 55 ] );        # e < 55
           );

Note: When checking the search criteria against a defaultfile (parameter $default_fname defined), then the SQL queries generated by DBIx::FileSystem are more complex than given in the example above.

Return values:

  OK      (0)  ok, one file found, result stored in %v's values.
  NOFILE  (1)  no file found, %v's values are undef
  NFOUND  (2)  more than one file found, %v's values are undef
  ERRROR  (3)  error, %v's values are undef, error string set

Import :symbols to use the short version of the symbolic names instead of the integer values or use DBIx::FileSystem::OK, ...

FUNCTIONS (interactive shell)

recreatedb(%vdirs,$PROGNAME,$VERSION,$DBCONN,$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,$DBCONN,$DBUSER,$DBPWD,%customcmds);

    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 elsewhere in this document.

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

$DBCONN

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 $DBCONN.

$DBPWD

DBI password needed by $DBUSER to connect to the database given in $DBCONN. May be set to undef if no password checking is done.

%customcmds

A hash which contains user defined commands to extend the shell (custom commands). If you do not have any commands then set %customcmds = (); before calling mainloop(). The key of the hash is the commandname for the shell, the value is an anon hash with two fields: func holding a function reference of the function implementing the command and doc, a one line help text for the help command.

custom commands

All custom commands are integrated into the completion functions: command completion and parameter completion, where parameter completion uses the files in the current directory.

A custom command gets the shells command line parameters as calling parameters. DBIx::FileSystem exports the following variables for use by custom commands:

$DBIx::FileSystem::vwd

The current working directory of the shell. Do not modify!

$DBIx::FileSystem::dbh

The handle to the open database connection for the config data. Do not modify!

$DBIx::FileSystem::OUT

A fileglob for stdout. Because FileSystem / Gnu ReadLine grabs the tty stdout you can not directly print to stdout, instead you have to use this fileglob. Do not modify!

Please see 'count' command in the example 'pawactl' how to implement custom commands. See the source of DBIx::FileSystem how to implement commands.

%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: dirname = tablename
  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 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 and len should be < 15 for proper
    # 'ls' output
    fnamcol => 'colname',       

    # optional: The column which acts as comment
    # field. The column must be of type 'char' and 
    # len must be set. The comments will be shown
    # by 'll' command (list long).
    comcol => '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.
    # Note: directories which have a column with
    # colopt => 'NOT NULL' constraint (see below):
    # the constraint must also set a default value like
    # colopt => 'DEFAULT 0 NOT NULL', otherwise the creation
    # of the database table will fail.
    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, $dbh) of the file to be removed 
    # and after all other builtin checks are done.
    # $dbh is the handle to the database connection.
    # 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 = columnname
  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. Will be called with
    # ($value_to_check,$hashref_to_othervalues,$dbh)
    # hashref holds all values read in from file,
    # key is the columnname. All hashvalues are 
    # already checked against their basic type, 
    # empty values in the file will be set to undef.
    # $dbh is the handle to the database connection.
    # The valok function has to return 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
    # then this column will be set to NULL 
    # when copying a file with 'cp'. When saving a
    # file the value entered must be uniq for this
    # variable in all files in the dir.
    uniq => 1,                  

    # optional: when this option exists and is set
    # then this column will be set to NULL ('use default')
    # when copying a file with 'cp', but no 'uniq'
    # checking takes place. 'cpdelete' is useful 
    # to avoid breaking a config because of duplicates
    # when copying files with command 'cp'
    cpdelete => 1,                      

    # optional: when this option is set the variable
    # behaves like a collection of flags. Each flag has
    # a bit number ranging from 0..31, a one word flag name,
    # and a long flag description. The value of the variable
    # is the bitwise or of all flags set. The variable's column
    # type COLUMN_TYPE must be integer: type => 'int', see below.
    flags => { bitnumber => [ "flagname", "flag description" ],
               bitnumber => [ "flagname", "flag description" ],
               # more flags settings ...
             }

    # optional: when this option is set the variable
    # behaves like a enum type variable based on type 'int'.
    # Each enum entry in 'enums' has an integer value 'enumvalue',
    # a one word enum name 'enumname' and a long enum description.
    # The possible values of this variable are 'empty' or
    # one of the symbolic 'enumnames', corresponding to
    # NULL or the integer 'enumvalue' in the database. 
    # The variable's column  type COLUMN_TYPE must be integer: 
    # type => 'int', see below.
    enums => { enumvalue => [ "enumname", "enum description" ],
               enumvalue => [ "enumname", "enum description" ],
               # more enum settings ...
             }

    # 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: Textual description what this
    # variable is good for. Will be show up as
    # a comment when displaying (cat) or editing
    # (vi) this file. Long lines can be split with
    # newline '\n'. When this column is 'fnamcol'
    # the text whill show up in header like:
    # "Settings for <desc-here>  <filename>
    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 ( see 'fnamcol'in
    #   DIRECTORY_SETTING): char
    # - when edit=1 set in DIRECTORY_SETTING: char, int, smallint
    #   and if we have a Postgres backend: inet and cidr also
    # - 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 column option 'uniq', and/or the custom rmcheck and valok functions within %vdirs together with database constraints. This has more advantages:

  1. When using database constraints the database takes care about integrity. Other programs than FileSystem can not destroy the integrity of the database.

  2. FileSystem uniq-option, 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.

  3. FileSystem functions uniq test, and custom functions rmcheck and valok will be called just before a database operation. If they fail, the database operation will not take place.

  4. 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:

  1. database layout given in %vdirs hash

  2. database layout in the database itself

  3. 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 'uniq' 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.

ld

Usage: 'ld'. Show the contents (dirs and files) of the current directory in long format. The %vdirs hash defines, which columns are used as a filename. For directories 'ld' will display the directory desc field from %vdirs. For files see command 'll' below.

ll

Usage: 'll'. Show the contents (files only) of the current directory, in long format. The %vdirs hash defines, which columns are used as a filename. If comcol (comment column) is set in %vdirs, then additionally show the contents of this column for each file.

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.

vgrep

Usage: 'vgrep PATTERN'. Find a pattern in all files. Find all lines in all files in current dir where var/value pair matches PATTERN. Print out matching lines. Ignore case when doing pattern matching. Regex patterns will be ignored, all metacharacters will be quoted. vgrep does not search the comments, but only the var=value lines (like the result of command 'sum')

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

-

M:N relations currently not supported.

-

composite primary keys currently not supported

-

The database types 'cidr' and 'inet' are tested with Postgres database and are expected to work with Postgres only.

-

Some database systems dislike the way, DBI uses for quoting the single quote (Method DBI::quote()). They issue a warning when updating or inserting data using quoted quotes. Users of the shell will see this warning when saving a new or existing file using shell's vi command. PostgreSQL version 8+ is known to behave like this.

AUTHOR

Alexander Haderer afrika@cpan.org

SEE ALSO

perl(1), DBI(3), dbish(1), readline(3)