The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
DBIx-Frame-1.06
River stage zero No dependents
++
/ DBIx::Frame

NAME

DBIx::Frame - a perl module for creating and maintaining DBI frameworks

SYNOPSIS

  use DBIx::Frame;
  DBIx::Frame->init('server', 'dbtype') || exit(0);
  my $DB = DBIx::Frame->new('database', 'user', 'pass') 
    or die("Couldn't connect to database: ", DBI->errstr);

See below for how to actually use this object.

DESCRIPTION

DBIx::Frame is an extension of the standard DBI perl module, designed around mysql, and used to create and maintain frameworks for databases. It has query logging, and a standardized interface for standard SQL statements like 'update' and 'insert' that doesn't require understanding SQL to any great degree. Ideally, the user or developer shouldn't have to know too much SQL to be able to administer a database. On the other hand, it does require a certain setup that isn't necessarily easy to pick up, and isn't standard SQL - with all the problems that this entails.

Database design is discussed below.

USAGE

There are five main sections of usage - database connection, DBI directives, error/logging functions, database structure queries, and helper functions.

Database Connection

init ( SERVER, DBTYPE, DATABASE [, USER [, PASS ]] )

Initializes the variables necessary to connect to a database. SERVER, DBTYPE, and DATABASE are necessary to determine what to connect to; USER and PASS are optional. Returns 1 if successful, 0 otherwise.

new ( DATABASE [, USER [, PASS [, SERVER [, DBTYPE ]]]] )
connect ( [ DATABASE [, USER [, PASS [, SERVER [, DBTYPE ]]]]] )

Creates a connection to the database, and returns a new DBIx::Frame object. DATABASE, PASS, etc are only necessary to override the defaults set with init(), or if they were never set in the first place.

If unsuccessful, returns undef; may also leave an error message in DBI->errstr, if the error was in the DBI connection phase.

table_add ( NAME, FIELDS [, KEYS, LIST, ORDER, ADMIN, REQUIRED, HTML, TEXT ] )

Adds a table to the database object. NAME is the name of the table, and FIELDS is a hash reference to the list of fields and data types; these are the only two required entries, but the others are helpful. For more information on what they are, see below.

(Note that if init() is not run first, and you're invoking this without an item, you could break things down the line. It's best to go ahead and run init() first.)

add_table ( NAME, FIELDS [, KEY, LIST, HTML, TEXT ] )

Deprecated; adds a table to the database object. NAME is the name of the table, FIELDS is a hash reference to the list of fields and data types, KEY and LIST are array references, and HTML and TEXT are code references. table_add() offers things in a more logical order.

set_fields ( NAME, HASHREF )
set_keys ( NAME, KEYINFO )
set_admin ( NAME, ARRAYREF )
set_list ( NAME, ARRAYREF )
set_order ( NAME, ARRAYREF )
set_required ( NAME, ARRAYREF )
set_html ( NAME, CODEREF )
set_text ( NAME, CODEREF )

Sets the FIELDS, KEYS, ADMIN, LIST, ORDER, REQUIRED, HTML, and TEXT fields, respectively, for the given NAME in the database object. These do the actual work of add_table(), or can be invoked individually so that individual scripts can use their own HTML formatting and such.

db ()

Returns a reference to the database that the object connects to. This references is a standard DBI object.

connected ()

Returns 1 if currently connected, 0 otherwise.

disconnect ()

Disconnects from the database. The logged queries and errors are left alone.

DBI/SQL Functions

Note: several of these functions refer to DATAHASH. This is simply a hash reference that has key/value pairs that correspond to those in the database's structure. Any key/value pairs that do not correspond to the structure are dropped before any SQL transactions are performed.

invoke ( QUERY )

Invokes an arbitrary QUERY on the connected database, and logs the query. If the query is successful, returns the appropriate return value (as laid out in the DBI standard); if unsuccesful, sets the error value and returns 'undef'.

(This is essentially the same as preparing and executing a query in standard DBI, with logging, checks for connections, etc.)

func ( FUNCTION )

Invokes FUNCTION on the database, using DBI-func>.

quote ( LINE [, LINE [, LINE [...]]] )
neat ( LINE, LENGTH )
neat_list ( ARRAY, LENGTH [, FIELDSEP ] )

Invokes DBI->quote, DBI->neat, and DBI->neat_list on their inputs, which are the same as the equivalent DBI functions. See their man pages for more information.

insert ( TABLE, DATAHASH [, ALLOW_ADMIN] )

Creates and executes an SQL query to insert an item into TABLE, using the data from DATAHASH. All of the KEY values must be set, and no entry must already exist that matches all of the KEYs; if either problem exists, then this function will fail. Uses invoke().

If ALLOW_ADMIN is set, then you may work with fields that are protected by the ADMIN array. (This doesn't actually work yet.)

update ( TABLE, DATAHASH, SELECTHASH [, ALLOW_ADMIN] )

Creates and executes an SQL query to update an item from TABLE, using the (new) data from DATAHASH for the updated values and the (old) data from SELECTHASH to determine which item to update. Note that the search terms won't be empty unless you specifically specify them so. Uses invoke().

If ALLOW_ADMIN is set, then you may work with fields that are protected by the ADMIN array. (This doesn't actually work yet.)

select ( TABLE, DATAHASH [, LIMITHASH [, OTHER]] )

Creates and executes an SQL query to select an item or items from TABLE, using the data from DATAHASH for the selection. Uses MATCHHASH to offer extra information to the select query - specifically, :

  Key     Description
  MATCH   What fields to return; defaults to '*'.
  ORDER   What order to return the fields in.  Defaults to the value of 
          order() for this database/table.  See below for more details.

OTHER adds additional text to the end of the search.

Returns a list of hash refs, each of which is one entry from the table.

Note that this does *not* use invoke(), though standard logging is still done. This is done to preserve the order that the database returned its results in.

select_multi ( TABLEHASHREF [, LIMITHASH [, OTHER]] )

Creates and executes an SQL query to select an item or items from more than one table. TABLEHASHREF is a hash reference, where the hash's keys are the tables to select from and the values are DATAHASH references (as in select()). Returns a list of hash refs, each of which is one entry from the combined tables. The keys these hash references take the form of "TABLE.ITEM".

Does *not* use invoke(), though standard logging is still done.

Note that LIMITHASH is not currently used; while the author expects to get to this at some point, it hasn't been done yet. For now, the API is better off leaving it in.

delete ( TABLE, DATAHASH )

Creates and executes an SQL query to remove an item from TABLE, using the data from DATAHASH to select which item to remove. Uses invoke().

create_table ( TABLE, HASH )

Creates and executes an SQL query to create a table named TABLE, based on HASH. Fails and returns undef if it already exists. Uses invoke().

drop_table ( TABLE )

Drops the table TABLE. Note, this is dangerous - you will lose all of your data in the table. Returns undef if TABLE doesn't exist. Uses invoke().

reset_table ( TABLE )

Drops and then re-creates TABLE. Uses create_table() and drop_table(). Again, this is dangerous.

Error Functions

error ( )
set_error ( STRING [, STRING [, STRING [...]]] )
clear_error ( )

Manipulate the ERROR string. error() and clear_error() should be fairly self-explanatory. set_error joins the STRINGs it gets, and puts them into ERROR.

add_query ( STRING )

Adds STRING to the query list. Returns a reference to an array containing the full set of queries invoked on this object.

clear_queries ()

Clears the query list.

queries ()

Returns the query list, either in an array context or as a string with the queries separated by newlines.

version ()

Returns the package's version number.

Database Structure Queries

tables ( [DATABASE] )

Returns a list of the names of the tables in the current database (or DATABASE, if offered).

fields ( [TABLE [, DATABASE]] )

Returns the field names for the given TABLE. If invoked in a scalar context, returns a hash reference to the field/datatype pairs from TABLE and DATABASE. In an array context, returns just the field names.

If DATABASE is not offered, assumes the current database. If TABLE is not offered, returns the same thing as fieldhash(). Returns undef if TABLE is offered but does not exist, or if no information is available.

list ( [TABLE [, DATABASE]] )

Returns the 'list' array for the given TABLE. If invoked in a scalar context, returns an array reference to TABLE and DATABASE's 'list'. In an array context, returns the whole thing.

If DATABASE is not offered, assumes the current database. If TABLE is not offered, returns the same thing as listhash(). Returns undef if TABLE is offered but does not exist, or if no information is available.

key ( [TABLE [, DATABASE]] )
keys ( [TABLE [, DATABASE]] )
required ( [TABLE [, DATABASE]] )
admin ( [TABLE [, DATABASE]] )
order ( [TABLE [, DATABASE]] )

These functions return the appropriate array of table fields, as in the appropriate hash tables. If invoked in a scalar context, return an array reference to TABLE and DATABASE's information (as appropriate). In an array context, returns the whole thing as separate items.

If DATABASE is not offered, assumes the current database. If TABLE is not offered, returns the same thing as the appropriate xxxxhash(). Returns undef if TABLE is offered but does not exist, or if no information is available.

html ( [TABLE [, DATABASE]] )
text ( [TABLE [, DATABASE]] )

Returns the 'html' or 'text' code reference for the given TABLE and DATABASE, as appropriate. If DATABASE is not offered, assumes the current database. If TABLE is not offered, returns the same thing as xxxxhash(). Returns undef if TABLE is offered but does not exist, or if no information is available.

list_head ( TABLE )

Returns an array of strings that are the headers for a make_list() command. Uses LIST for its data.

make_list ( TABLE, ITEMHASH [, ITEMHASH [, ITEMHASH [...]]] )

Actually uses the LIST function. Takes one or more ITEMHASH (the return from a select() function, essentially), and makes an array of each item that belongs on the list. Returns an array of array references, one for each ITEMHASH that is offered.

keyhash ( [DATABASE] )
fieldhash ( [DATABASE] )
listhash ( [DATABASE] )
requiredhash ( [DATABASE] )
orderhash ( [DATABASE] )
adminhash ( [DATABASE] )
htmlhash ( [DATABASE] )
texthash ( [DATABASE] )

Returns the appropriate hash reference to the underlying data in the DBIx::Frame object. This takes the structure of:

  Key             TABLE1, TABLE2, etc

  Value
    fieldhash     hash reference - keys are the field names in the 
                  table, values are the data types of those keys
    listhash      array reference - field names that are for list()
    keyhash       \ 
    requiredhash   \ array reference - field names that are 'keys', 
    orderhash      / 'required', 'order', or 'admin', as appropriate
    adminhash     /
    htmlhash      code reference - create an HTML table of the data
    texthash      code reference - create a text summary of the data

More information on these is below.

Uses the current database, or DATABASE if offered.

Helper Functions

select_fieldlist ( TABLE, FIELD [, DATAHASH [, OTHER ]] )

Using select() with (or without) DATAHASH, returns an array containing the FIELD field from all matching entries. OTHER is passed into the select(). Useful if you just want to select all of the names out of a Person table, or something similar.

select_fieldlist_id ( TABLE, FIELD [, DATAHASH, [, OTHER ]] )

Does the same thing as select_fieldlist(), except that the entries are returned as a hash, where the keys are the relevant ID fields.

select_list_id ( TABLE [, DATAHASH [, OTHER ]] )

Using select() with (or without) DATAHASH, returns a hash reference containing key/value pairs where the key is the relevant ID and the values are the results of make_list(). Useful for getting a human- parsable version of a select() statement.

DATABASE DESIGN

Alone, this module isn't all that useful - if you're just wanting to invoke some random SQL code on your database, you'll find the DBI module must more useful to you. The real strength of this module is that it allows for a standardized method of creating database modules, which can then be controlled with standard tools. And this is really handy for the sysadmin of a certain research group that needs to maintain a large number of databases, and would rather not rewrite code all the time.

Each DBIx::Frame object contains one or more databases, each of which contains one or more tables. They are initialized using add_table on a created database; generally, this should be taken care of on initialization. Usually each table will be its own module.

Each table has the following information, in order of importance (note that more functions may be added at the discretion of the designer):

NAME

This is a string that contains the name of the table. This should be fairly self-explanatory.

Example (from TCB::Publications::Papers):

  $NAME = "Papers";

Related functions: tables()

FIELDS

This is a hashref containing the fields contained within the table. The hash has field-name/content-type key/value pairs. These fields are assumed to be the only ones the table knows of, and extra data will be discarded by the functions above. As such, this is vital if you actually want to use the database.

Example (from TCB::Publications::Papers):

  $FIELDS = {

  # Non User-Modified Information
  'ID'          =>  'INT NOT NULL PRIMARY KEY AUTO_INCREMENT',
  'CreatedBy'   =>  'TINYTEXT',    'ModifiedBy'  =>  'TINYTEXT',
  'CreateDate'  =>  'DATE',        'ModifyDate'  =>  'DATE',

  # Basic Information
  'Title'       =>  'TINYTEXT',    'Authors'     =>  'TINYTEXT',
  'CorrAuth'    =>  'TINYTEXT',    'PaperType'   =>  'TINYTEXT',
  'PubDate'     =>  'TINYTEXT',    'Pages' => 'TINYTEXT',

  # Journal Information
  'Journal'     =>  'TINYTEXT',    'JournalVolume'  =>  'TINYTEXT',
  'JournalManNum'  =>  'TINYTEXT',

  # Book information
  'BookTitle'   =>  'TINYTEXT',    'BookEditors' =>  'TINYTEXT',
  'BookPublishers' =>  'TINYTEXT', 'BookYear'   =>   'TINYTEXT',

  # TB Information
  'PubStatus'   =>  'TINYTEXT',    'Copies'      =>  'INT',
  'TBRef'       =>  'INT',         'TBCode'      =>  'TINYTEXT',
  'TechRpt'     =>  'TINYTEXT',    'Notes'       =>  'TEXT',
  'AccountNum'  =>  'TINYTEXT',    'Grant1'      =>  'TINYTEXT',
  'Grant2'      =>  'TINYTEXT',    'Grant3'      =>  'TINYTEXT',
  'PDF'         =>  'TINYTEXT',    'Abstract'    =>  'TEXT',

          };

Note the 'ID' field at top. Whenever possible, created databases SHOULD include such a field, to simplify specifying which database entry is being referred to at any given time. MySQL makes this easy; other databases usually have a similar functionality, using slightly different means.

Related functions: fields(), fieldhash()

KEYS

This is an arrayref containing the 'key fields' for each table. Each entry is either a single field names, or an array reference that contains several field names. Its purpose is to keep track of 'unique' entries - if there is already an entry with the field(s), the new item will not be inserted into the database. Also, each of these fields must be present in order to insert the item into the DB. This is vital if you want to add or update information in the database.

Example (from TCB::Publications::Papers):

  $KEYS  = [ 'TBRef', 'TBCode' ];

Defaults to 'ID' if not offered.

Related functions: key(), keyhash()

ORDER

This is an arrayref that defines the fields upon which we should order our select()s on by default. Sorts in ascending order unless preceeded by '-', in which case we'll sort in descending order.

Example (from TCB::Seminar::Lecture):

  $ORDER = [ '-Date', 'Name' ];

Defaults to 'ID' if not offered.

ADMIN

This is an an arrayref that contains fields that shouldn't be created or edited by non-administrative users - that is, these fields are somewhat protected when being worked on by external users. Note that this should not (at this point) be considered safe, it's just a matter of convenience.

Example (from TCB::Conference::Register):

  $ADMIN    = [ 'Approved', 'GotMoney' ];

Defaults to an empty array.

Related functions: admin(), adminhash()

REQUIRED

This is an arrayref that contains fields that must be filled in when an entry is created - that is, fields that shouldn't be empty. Unlike the KEYS information, these fields don't necessarily have to be unique either.

Example (from TCB::Conference::Register):

  $REQUIRED = [ 'LastName', 'FirstName', 'Email', 'Title', 'Institution',
                'Department', 'Housing', 'Gender', 'Citizenship' ];

Defaults to 'ID' or, in most cases, the same as KEYS.

Related functions: required(), requiredhash(), key()

LIST

This is an array reference containing the elements that should be listed off in the case of a make_list() function, and the headers of that list from a list_head() function. These are used in quick summaries of the database information. Defaults to every field in the table.

Each array element can take one of three forms:

Scalar

Must be one of the fields of the table. list_head() will return the name of the field, and make_list() will return the value of that field (or "" if empty).

Hash Reference

Must contain only a single key/value pair. list_head() will return the key of this pair, and make_list() will return the interpreted value. The interpretation is peformed with the following steps:

  - '$$item$$' is replaced with the value of the 'item' field 
    (or "" if empty).  
  - If the value of the pair is an array, the first item is 
    expected to be a code reference, and the later items are 
    arguments to be passed to the code.  The argument set will 
    be ($self, C<ITEMHASH>, ARG1, ARG2, ARG3...)
Code Reference

Runs the code, with the argument set being ($self, ITEMHASH).

Example (from TCB::System::Port):

  $LIST = [ 
    { 'Room' => '$$RoomNumber$$ $$Building$$' },
    { 'Port Speed' => '$$PortSpeed$$' } ,
    'BoxNumber', 'PortNumber', 'PortMachine' ];

Another example (from TCB::Travel::Event):

  $LIST  = [ 
        'Title', 'Location',
        { 'Dates' => [ \&dates, '$$Start$$', '$$End$$' ] },
        { 'URL'   => [ \&url, '$$URL$$' ] }
           ];

Related functions: list(), listref(), list_head(), make_list()

HTML

This is a code reference that, when invoked, will return a string containing a table with form entries to allow the creation of new entries and the updates of old ones. This isn't necessary, but is probably worth your time to create.

The function should take inputs like this:

  $self  - a reference to the DBIx::Frame object
  $entry - a hash reference containing the default data, with the keys
           being the table fields and the values being their contents.
  $type  - the type of function you're working with, typically one of
           'create', 'search', 'edit', or 'view'.
  $opts  - a hash reference containing extra flags that are passed on from
           the script (not the user!) to the subroutine.  These are used 
           to customize the output of html() based on the invoking script.  
           Note that this is not documented in the below example!
  @rest  - anything else it feels like taking

The returned HTML can either be a table (which doesn't require any stylesheet information) or properly formatted HTML4. If the latter, you may want to make sure scripts that invoke this function also use a relevant stylesheet.

Example (from TCB::Publications::Files):

  sub html {
    my ($self, $entry, $type, $options, @rest) = @_;
    my $cgi = new CGI;    $entry ||= {};
  
    my %public = ( 0 => "Public", 1 => "Internal Only" );
    if (lc $type eq 'search') { $public{''} = "*" }
  
    my @types = sort @LINKTYPES;  unshift @types, '';
  
    my @codes = sort grep { $_ if /\S+/ } 
                  $self->select_fieldlist('Papers', 'TBCode');  
    unshift @codes, '';

  my @return = <<HTML;
  <div class="basetable">
   <div class="row2">
    <span class="label">TBCode</span>
    <span class="formw">
     @{[ $cgi->popup_menu('TBCode', \@codes, $$entry{TBCode} || "") ]} 
    </span>
    <span class="label">File Type</span>
    <span class="formw">
     @{[ $cgi->popup_menu('Type', \@types, $$entry{Type} || "") ]}
    </span>
   </div>
  
   <div class="row1">
    <span class="label">Location</span>
    <span class="formw">
     @{[ $cgi->textfield('Location', $$entry{Location} || "", 70, 1024) ]}
    </span>
   </div>
  
   <div class="row1">
    <span class="label">Description</span>
    <span class="formw">
       @{[ $cgi->textarea(-name=>'Description', 
                          -default=>$$entry{Description} || "",
                          -rows=>7, -cols=>60, -maxlength=>65535,
                          -wrap=>'physical') ]}
    </span>
   </div>
  
   <div class="row1">
    <span class="label">Restricted?</span>
    <span class="formw">
      @{[ $cgi->popup_menu('Restricted', [sort keys %public], 
                    $$entry{Restricted} || "", \%public) ]}
    </span>
   </div>
  
  
   <div class="submitbar"> @{[ $cgi->submit(-name=>"Submit") ]} </div>
  
  </div>
  
  HTML
    wantarray ? @return : join("\n", @return);
  }

Related functions: html(), htmlref()

TEXT

This is a code reference that, when invoked, will return a string containing the data that you wish to display in text. This mostly pertains to DBIx::Frame::Text.

Example (from TCB::mysql::user):

  sub text {
  my ($self, $entry) = @_;
  return sprintf(
        "%22s %22s %22s %1s%1s%1s%1s%1s%1s%1s%1s%1s%1s",
        $$entry{Host}, $$entry{User}, $$entry{Password}, 
        $$entry{Select_priv}, $$entry{Insert_priv}, 
        $$entry{Delete_priv}, $$entry{Create_priv},
        $$entry{Drop_priv}, $$entry{Grant_priv}, 
        $$entry{References_priv}, $$entry{Index_priv}, 
        $$entry{Alter_priv} );
}

Related functions: text(), textref()

NOTES

This module was designed for use with MySQL, and hasn't been tested with anything else. Most things should be portable, however, probably with little effort; only some internal code may need changing.

REQUIREMENTS

Perl 5 or better, the DBI module, and the appropriate drivers for your database (DBD::mysql, in our case). To really make it useful you'll want a set of DBIx::Frame database modules, such as TCB::AddressBook or TCB::Publications, though you can (and should) design your own.

SEE ALSO

DBI, DBIx::Frame::CGI. Many of the databases on the MDTools web site (http://www.ks.uiuc.edu/Development/MDTools/) offer further examples of how to design databases and use this package.

TODO

Make this less dependent on MySQL. This should happen naturally, since I'm planning on trying out Oracle next.

Should make something to require unique KEYs into the 'update' command also. This is fairly tricky.

Create and release DBIx::Frame::Text, to further simplify using these tools from the command-line or the web.

Make some functions to change the database's layout on-the-fly, or at least in a few simple steps (easier said than done).

Make some scripts to auto-generate modules to specification.

AUTHOR

Written by Tim Skirvin <tskirvin@ks.uiuc.edu>.

HOMEPAGE

http://www.ks.uiuc.edu/Development/MDTools/dbixframe/

LICENSE

This code is distributed under the University of Illinois Open Source License. See http://www.ks.uiuc.edu/Development/MDTools/dbixframe/license.html for details.

COPYRIGHT

Copyright 2000-2004 by the University of Illinois Board of Trustees and Tim Skirvin <tskirvin@ks.uiuc.edu>.