NAME

DBIx::FileSystem - Manage tables like a filesystem

SYNOPSIS

  use DBIx::FileSystem;
  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, 
	       $DBHOST, $DBUSER, $DBPWD);
  }else{
    # start the command line shell
    mainloop(%vdirs, $PROGNAME, $VERSION, 
	     $DBHOST, $DBUSER, $DBPWD, %customcmds );
  }

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

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 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.
    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 => {			

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

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.

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

AUTHOR

Alexander Haderer alexander.haderer@charite.de

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)