DBIx::Frame - a perl module for creating and maintaining DBI frameworks
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.
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.
There are five main sections of usage - database connection, DBI directives, error/logging functions, database structure queries, and helper functions.
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.
SERVER
DBTYPE
DATABASE
USER
PASS
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.
init()
If unsuccessful, returns undef; may also leave an error message in DBI->errstr, if the error was in the DBI connection phase.
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.
NAME
FIELDS
(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.)
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.
KEY
LIST
HTML
TEXT
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.
KEYS
ADMIN
ORDER
REQUIRED
Returns a reference to the database that the object connects to. This references is a standard DBI object.
Returns 1 if currently connected, 0 otherwise.
Disconnects from the database. The logged queries and errors are left alone.
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.
DATAHASH
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'.
QUERY
(This is essentially the same as preparing and executing a query in standard DBI, with logging, checks for connections, etc.)
Invokes FUNCTION on the database, using DBI-func>.
FUNCTION
DBI-
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.
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().
TABLE
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.)
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().
SELECTHASH
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, :
MATCHHASH
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.
OTHER
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.
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".
TABLEHASHREF
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.
LIMITHASH
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().
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().
HASH
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().
Drops and then re-creates TABLE. Uses create_table() and drop_table(). Again, this is dangerous.
create_table()
drop_table()
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.
error()
clear_error()
set_error
STRING
Adds STRING to the query list. Returns a reference to an array containing the full set of queries invoked on this object.
Clears the query list.
Returns the query list, either in an array context or as a string with the queries separated by newlines.
Returns the package's version number.
Returns a list of the names of the tables in the current database (or DATABASE, if offered).
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.
fieldhash()
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.
listhash()
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.
xxxxhash()
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.
Returns an array of strings that are the headers for a make_list() command. Uses LIST for its data.
make_list()
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.
ITEMHASH
select()
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.
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.
FIELD
Does the same thing as select_fieldlist(), except that the entries are returned as a hash, where the keys are the relevant ID fields.
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.
ID
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):
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()
tables()
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.
$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()
fields()
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.
$KEYS = [ 'TBRef', 'TBCode' ];
Defaults to 'ID' if not offered.
Related functions: key(), keyhash()
key()
keyhash()
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' ];
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()
admin()
adminhash()
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.
$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()
required()
requiredhash()
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.
list_head()
Each array element can take one of three forms:
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).
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...)
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()
list()
listref()
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()
html()
htmlref()
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.
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()
text()
textref()
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.
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.
TCB::AddressBook
TCB::Publications
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.
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.
Written by Tim Skirvin <tskirvin@ks.uiuc.edu>.
http://www.ks.uiuc.edu/Development/MDTools/dbixframe/
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.
http://www.ks.uiuc.edu/Development/MDTools/dbixframe/license.html
Copyright 2000-2004 by the University of Illinois Board of Trustees and Tim Skirvin <tskirvin@ks.uiuc.edu>.
To install DBIx::Frame, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Frame
CPAN shell
perl -MCPAN -e shell install DBIx::Frame
For more information on module installation, please visit the detailed CPAN module installation guide.