NAME

dbitable - an object-oriented Perl module for handling single tables from an SQL database.

SYNOPSIS

use dbitable;

my $tab= dbitable->new('table', $database_handle,
                       $table_name, $primary_key)->load();

$tab->value($key1,$column_name,$new_value);
$tab->store();
$tab->pretty_print();

DESCRIPTION

Preface

This module defines the dbitable - class. A dbitable-object can hold some or all parts of a table of an SQL database. It is also possible, to load the results of a user-defined view into a dbitable-object. And a dbitable-object can also be used to read or write to an ASCII file in a (more or less) human-readable format.

dbitable is based on the DBI module, see also the DBI manpage for more information.

Two basic methods for a table are load and store. These methods are used to load data from the database or store data to the database or the file.

The usual way of handling a dbitable-object is to create it, fetch some data with load, inspect or modify the data, and, optionally, write the table back to the database using store.

To do all this, the user of this module doesn't need to know the SQL language. Knowledge on the basics of relational databases, however, will be needed.

utilities to access the database:

In order to access the database and create a database-handle, the following two functions can be used:

dbitable::connect_database()
my $dbh= dbitable::connect_database($dbname,$username,$password)

This method creates a connection to the database. The database corresponds to the first parameter of the connect function of the DBI module. See also the DBI manpage. The function returns the DBI-handle or undef in case of an error. The DBI-handle is also stored in the internal global handle variable. This variable is used as default in the dbitable constructor function new() when it's DBI-handle parameter is an empty string ("") or undef.

dbitable::disconnect_database()
dbitable::disconnect_database($dbi_handle)

This function is used to disconnect from the database. If the parameter $dbi_handle is an empty string, the default DBI-handle is used (see also description of connect_database).

creation of a dbitable-object:

A dbitable object is created using the new method:

my $tab= dbitable->new('table',$database_handle,
                       $table_name, $primary_key);

my $tab= dbitable->new('view',$database_handle,
                       $table_name, $primary_key, $sql_query);

my $tab= dbitable->new('file',$filename,$tag);

my $tab= dbitable->new('file',$filename,$tag,@column_list);

# clone the existing structure:
my $tab2= $tab->new();

my $tab2= $tab->new('table',$database_handle,
                    $table_name, $primary_key);

my $tab2= $tab->new('view',$database_handle,
                    $table_name, $primary_key, $sql_query);

my $tab2= $tab->new('file',$filename,$tag);

The new method currently knows three parameter formats. Note that, if new is called as method of a parent dbitable-object, the parent object is cloned and the type (table,view or file) is changed to the type specified in the first parameter.

The $database_handle parameter may be a valid database-handle, e.g. created with the connect_database function, or an empty string. In this case, the default database-handle is used (see description of connect_database).

Notes on the primary key:

The primary key is a single column or a combination of columns that is unique for each line in a table and gives a means to access single lines of the table. However, dbitable also supports tables, where such a column with unique values doesn't exist. In this case, dbitable just assigns a number to all lines in the table, starting with "1".

The primary key is set or found in the following way:

direct specification of a single column

In this case, the user supplies the information, which column is to use as primary key. The $primary_key parameter in the examples above is a string with the name of that column. Note that this column must have a value that is unique for each line. Otherwise several lines will occupy the same space in the dbitable-object which means that some lines in the dbitable-object will be missing.

direct specification several columns

In this case, the user supplies the information, which columns are to use as primary key. The $primary_key parameter is in this case a reference to an array of column names. The primary key is then composed by the value of all the columns concatenated with the character seqeuence "||". Example:

my $tab= dbitable->new('table',$database_handle,
                       $table_name,
                       [$primary_key1_column1,
                        $primary_key1_column2 ...]
                       );
automatic determination of the primary key columns

If you give "" (the empty string) or <undef> as value for the $primary_key parameter, dbitable tries to find out the primary key columns itself by querying the database. In many cases, dbitable will determine the correct primary key columns, which are then used to identify each single line of the table. If dbitable is not able to find the primary key columns, it will use line-numbering (see below) to access the lines of the table.

line numbering

For tables of the type "view" when the user didn't specify the primary key columns like shown above or for tables of the type "table" where the user didn't specify and dbitable couldn't find the primary key columns by another database query, the lines of the table are simply numbered. Each line of the table is accessed with a number, starting with "1" for the first line. There are no primary key columns in this case. Note that functions like primary_keys() return undef for such an object.

The three object types

loading:

$table->load()

$table->load(%options)

my $tab= dbitable->new($database_handle,
                       'table',$table_name, $primary_key)->load();

This method loads the table from the database (for the type "table") or from a file (type "file") or via a user-defined SQL statement (type "view"). %options is a hash that is used to pass optionial parameters to the function. The function returns the object itself, so it can be cascaded with new in a single call, as you can see in the third example.

In case of an error, the function calls dbdrv::dberror or dbdrv::dbwarn and returns undef. If the dbitable-object already contains data and the SQL command issued fails, the data will be left intact.

Known options are:

storing:

$table->store()

$table->store(%options)

This method writes the content of the table back to the database (for type "table") or to the file (for type "file").

Known options are:

export/import to files:

$table->export_csv($filename, %options)

This method exports to a table to a file using the csv format, which means comma separated value. If $filename is an empty string or <undef>, the table is written to standard-out. The following options are known:

$table->import_csv($filename, %options)

This method imports a table from a file using the csv format.

importing a table:

$dest_tab->import_table($source_tab,%options);

This method is used to import the contents of an existing table-object into a new one. It is not necessary, that both tables are of the same type. Known options are:

miscelanious functions:

std_database_handle()
my $dbh= dbitable::std_database_handle();

This returns the internal standard database-handle. This database-handle is used, when the undef or an empty string is used where otherwise a database-handle is expected. The standard database-handle is simply the last database-handle that was returned by the function dbitable::connect_database. If dbitable::connect_database was never called, std_database_handle returns undef.

primary_keys()
my @keys= $table->primary_keys(%options)

This method returns a list consisting of the primary key of each line of the table. If no options are given, all the primary keys are given in a more or less random order.

The following options are known:

native_order
my @keys= $table->primary_keys(order_native=>1)

returns the primary keys just in the order they were returned from the database. Note that this only works when each load command on the dbitable object had the option "save_native_order" set.

order_by
my @keys= $table->primary_keys(order_by=>[@column_names_list])

the primary keys are sorted according to the contents of the columns given in the list. The first column has the highest precedence. When the 1st column is equal in two lines, then the second column is used for further sorting and so on.

col_maps
my @keys= $table->primary_keys(col_maps=>{ $column1 => \%col_map1,
                               $column2 => \%col_map2, ... } )

By this option, column-maps can be specified. These map the values of a column to another value. Currently these are only used for sorting (see above).

filter
my @keys= $table->primary_keys(filter=>'updated')

my @keys= $table->primary_keys(filter=>'inserted')

In this case, only primary keys of lines are returned, that are marked as "updated" or marked as "inserted".

primary_key_columns()
my @pk_cols= $table->primary_key_columns()

This method returns the names of the primary-key columns in upper case. In most cases there is exactly one primary key column. Note that a table may also have no primary key columns at all (see comments on primary keys at the start of this document). The function returns undef in this case.

primary_key_column_indices()
my @pk_col_indices= $table->primary_key_column_indices()

This method returns the indices of the primary-keys in the list of all columns. The index numbering of all columns starts with 0. Note that a table may also have no primary key columns at all (see comments on primary keys at the start of this document). The function returns undef in this case.

primary_keys_are_numeric()
if ($table->primary_keys_are_numeric())
  { ... }

This function returns 1 when all primary key columns are numbers. When there is only one primary key column and it is numeric, the primary key generation feature (primary key mode: "generate") can be used.

primary_key_auto_generate_possible()
if ($table->primary_key_auto_generate_possible())
  { ... }

This function returns 1 when it is with the current table possible to use auto-generated primary keys (see above).

column_list()
my @columns= $table->column_list()

This method returns a list consisting of all column names in upper case. The columns have the same sort order as in the oracle database.

column_hash()
my %columns= $table->column_hash()

This method returns a hash that maps each column-name it's column-index. Columns are numbered starting with 0.

get_column_property()
my %columns= $table->column_properties()
my $length= $table->column_properties($columnname, $propertyname)

This method returns a hash that maps each column-name it's type, length, precision, null-flag and default value if no columnname and one of the property names given as argument. If columnname and propertyname given the value will be returned.

get_column_type
my $type= get_column_type($columnname)

This method returns the kind of logical columntype for $columnname. (string, number, date, file or <undef>)

get_object_type
my $object = get_object_type()

If you want to know what there is received from database (table, view or sql (query)), use this simple function.

max_column_widths()
my %columns= $table->max_column_widths($minwidth,$maxwidth)

This method returns a list that contains the maxmimum width for each column. It is guaranteed that the returned values are larger than $minwidth and smaller than $maxwidth.

foreign_keys()
my $r_foreign_keys= $table->foreign_keys()

This method returns a reference to a hash that maps column-names to a list containing two elements, the name of the foreign table and the column-name in the foreign table. Note that this function works only for the type 'table'.

resident_keys()
my $r_resident_keys= $table->resident_keys()

This method returns a reference to a hash that maps column-names to a list of lists. Each sub-list contains two elements, the name of the resident table and the column-name in the resident table. "Resident" is used here in opposition to "foreign". Viewed from the perspective of the resident table, the current table is the foreign table. The same relation is between foreign key and resident key. A foreign key is always unique, it belongs to a single foreign table. Viewed from the other side, this uniqueness is not guaranteed. That is the reason that the hash contains a list of lists. Note that this function works only for the type 'table'.

value()
my $value= $table->value($primary_key, $column_name)

$table->value($primary_key, $column_name, $value)

This method is used to get or set a value, that means a single field of a single line. In the first form, the value is returned, in the 2nd form, the value is set. Note that changes do only take effect in the database or file, when store() is called later on. Note too, that in the 2nd form, the function returns $value when everything was ok, and undef if setting the value was impossible due to constraints dbitable.pm knows of (e.g. primary keys must be always unique in the table).

find()
my @pk_list= $table->find($column_name, $value, %flags)

This method searches the table to find a row where the value in the specified column matches the given value. Note that this method may be slow (a linear search) for columns that are not the primary key column. The method returns a list of primary keys that fullfill the match criteria. The %flags parameter is used to pass certain options. Known options are:

find_first

If this is set (non-zero) the find function returns only the primary key of the first line that matches, nothing more.

warn_not_pk

If this is set (non-zero) the function warns when the given column us not the primary-key column.

warn_multiple

If this is set (non-zero) the function warns when more than one line was found that matches the given value.

add_line()
my $primary_key= $table->add_line(%values)

This method is used add a line to the table. %values is a hash, that contains "column-name" "value" -pairs for the line. Note that each column must be specified and column-names must be upper-case! Currently there are no defaults supported. Note that the method returns the primary key for the new line. Note too, that this primary key changes with the next call of store(), see also store(), but via aliasing (see add_aliases) it can then still be used to access the line. If the value of the primary key is specified in the list of values, it is taken as it is, and no new primary key is generated.

delete_line()
$table->delete_line($primary_key)

This method deletes a line from the table. Note that this change is made in the database or file only at the next call of store().

add_column_aliases()
$table->add_column_aliases($column_name,%options)

This method is used to add aliases for a specific column to the column-aliases. Two options are known, "uppercase" and "lowercase". The alias is forced to be upper- or lowercase if one of these options is set.

add_aliases()
$table->add_aliases(%aliases)

This method is used to add aliases to the table. An alias is a key that is different from the value of the primary key, that can be used to access a single line. An arbitrary number of aliases can be defined for a line. Note however, that each alias must be unique. The %aliases parameter is a hash, containing pairs of the alias and the corresponding primary key.

resolve_alias()
my $primary_key= $table->resolve_alias($alias)

This method returns the primary key that is associated with a given alias.

max_key()
my $max= $table->max_key()

This function returns the maximum primary key for a given table. Note that this value is obtained directly by an SQL query from the database, no matter what the current content of the table-object is.

dump()
$table->dump()

This method dumps the complete internal data-structure of a table-object and is for debugging purposes only.

dump_s()
my $r_buffer= $table->dump_s()

This method dumps the complete internal data-structure of a table-object to a text variable and returns a reference to that variable. It is for debugging purposes only.

pretty_print()
$table->pretty_print()

$table->pretty_print(%options)

This method is used to pretty-print a given table.

Known options are:

miscelanious variables:

$sim_delete

when set to 1, the deletion of lines in the table is only simulated and not done for real. The default of this variable is 1, so deleting lines is disabled as default !

$last_error

This variable contains the error-message for the last error non-fatal that occured. An example is the case, when a new object of type "table" is created, but the table doesn't exist. new() returns undef in this case and $last_error is set to "table doesn't exist".

EXAMPLES

query a database (type "table")

This example queries the p_insertion table here at HZB. It connects to the database and creates a "table" object. new and load are called in one line:

use strict;
use dbitable;

my $dbname= "DBI:Oracle:bii_par";

my $user="guest";
my $pass="bessyguest";

dbitable::connect_database($dbname,$user,$pass) or die;

my $tab= dbitable->new('table',"",'p_insertion',
                       'insertion_key')->load();

$tab->pretty_print();

dbitable::disconnect_database();

arbitrary SQL query (type "view")

In this example, the insertion-device name-key (a number) is mapped to the insertion-device name. For this, the "view" type is used. The device-name has to be constructed (a simple string concatenation) by querying 5 tables. Note that the "constructed" column consists of a concatenation of "part_name", "part_index", "part_family", "part_subdomain", "part_postfix" and "part_domain" and is named (naming is imperative!) to "device_name" by the "... AS device_name" part of the statement. The resulting table-object is just like any other table object, except that a call of the store() method is not allowed.

use strict;
use dbitable;

my $dbname= "DBI:Oracle:bii_par";

my $user="guest";
my $pass="bessyguest";

dbitable::connect_database($dbname,$user,$pass) or die;


my $tab= dbitable->new('view',"",'v_names','name_key',
      "select N.name_key, " .
      "       part_name || part_index || part_family || " .
      "                    part_subdomain || part_postfix || " .
      "                    part_domain AS device_name " .
      "  from p_insertion I, p_name N, p_subdomain S, " .
      "       p_family F, p_domain D " .
      "  where I.name_key = N.name_key AND " .
      "        N.subdomain_key = S.subdomain_key AND " .
      "        N.family_key = F.family_key AND " .
      "        S.domain_key = D.domain_key"
                           )->load();


$tab->pretty_print();

dbitable::disconnect_database();

reading of a table and writing to a file (type "file")

The following example reads the table "p_insertion" and writes it to a file. Since the original table-object is of the type "table", a new object of the type "file" is created by calling the new method on the first table-object. Using dbitable-new(...)> and calling <$ntab->import_table(..)> would have been another possibility to achive this. After the program was executed, you may have a look at the file "TEST.TXT", which is created by this little application.

use strict;
use dbitable;

my $dbname= "DBI:Oracle:bii_par";

my $user="guest";
my $pass="bessyguest";

dbitable::connect_database($dbname,$user,$pass) or die;

my $tab= dbitable->new('table',"",'p_insertion',
                       'insertion_key')->load();

my $ntab= $tab->new('file',"TEST.TXT","TEST-TAG")->store();

dbitable::disconnect_database();

modifying a table and writing back to the database (type "table")

This script reads the table, adds a line and writes back to the database. Note that the primary key 'insertion_key' must not be specified when add_line is called, a new primary key is created automagically. Note too, that if you really try to execute this script, it will fail, since the guest-user has no write priviledges. $dbdrv::sql_trace=1 will force the dbitable module to print all SQL commands to the screen, before they are executed.

use strict;
use dbdrv;
use dbitable;

$dbdrv::sql_trace=1;

my $dbname= "DBI:Oracle:bii_par";

my $user="guest";
my $pass="bessyguest";

dbitable::connect_database($dbname,$user,$pass) or die;

my $tab= dbitable->new('table',"",'p_insertion',
                       'insertion_key')->load();

$tab->add_line(NAME_KEY=> 1,
               INTERNAL_NAME=> "XX",
               IOC_KEY=> 2,
               DESCRIPTION=>"test",
               DEVICE_CONDITION=>1);

$tab->pretty_print();

$tab->store();

dbitable::disconnect_database();

writing a file back to the database

This script reads a table from an existing file. Then it reads from the database and kind of compares this with the data from the file. The changed lines are then stored back to the database. The advantage of reading from the database before writing to it is that the SQL "update" command is only executed for lines that have found to be different from the lines stored in the database.

use strict;
use dbdrv;
use dbitable;

$dbdrv::sql_trace=1;

my $dbname= "DBI:Oracle:bii_par";

my $user="guest";
my $pass="bessyguest";

dbitable::connect_database($dbname,$user,$pass) or die;

my $ftab= dbitable->new('file',"TEST.TXT","TEST-TAG")->load();

my $tab = $ftab->new('table',"",'','');
# 1st '': take table-name from $ftab
# 2nd '': take primary key from $ftab

$tab->load(mode=>"add");

$tab->store();
dbitable::disconnect_database();

AUTHOR

Goetz Pfeiffer, Goetz.Pfeiffer@helmholtz-berlin.de

SEE ALSO

perl-documentation, DBI manpage