NAME

RDBAL::Schema - RDBAL Schema information object

SYNOPSIS

use RDBAL;
use RDBAL::Schema;
$X = RDBAL::Connect('username', 'password', 'server');
$schema = new($X,$database);
$database = $schema->Database();
@user_tables = $schema->User_Tables();
@views = $schema->Views();
@procedures = $schema->Procedures();
@system_tables = $schema->System_Tables();
@fields = $schema->Table_Fields($table);
@fields = $schema->System_Table_Fields($table);
@fields = $schema->View_Fields($view);
@fields = $schema->Procedure_Parameters($procedure);
@fields = $schema->Fields($object,$object_type);
$field_info = $schema->Field_Info($object,$field,$object_type,$info_type);
$primary_key_number = $schema->Primary_Key($object,$field,$object_type);
$field_type = $schema->Field_Type($object,$field,$object_type);
$field_length = $schema->Field_Length($object,$field,$object_type);
$field_width = $schema->Field_Width($object,$field,$object_type);
$field_precision = $schema->Field_Precision($object,$field,$object_type);
$field_scale = $schema->Field_Scale($object,$field,$object_type);
$field_identity = $schema->Field_Identity($object,$field,$object_type);
$field_null = $schema->Field_Null($object,$field,$object_type);
@indexes = $schema->Indexes($table,$object_type);
@primary_keys = $schema->Primary_Keys($table,$object_type);
@keys = $schema->Keys($table,$object_type);
@children_tables = $schema->Children($table);
@parent_tables = $schema->Parents($table);
@field_equivalences = $schema->Relation($parent,$child);
# Get comments (views' and procedures' definitions)
$comments = $schema->Comments($object,$object_type)
# Get view's tables
@tables = $schema->View_Tables($view)

ABSTRACT

This perl library uses perl5 objects to make it easy to retrieve information about a particular Sybase or MS SQL databases's schema.

INSTALLATION:

If you wish to change the location of the schema cache directory from the default value of '/usr/local/schema_cache', edit Config.pm.

To install this package, just change to the directory in which this file is found and type the following:

perl Makefile.PL
make
make test
make install

and to create the schema cache directory:

make schema_cache

DESCRIPTION

The schema information available includes:

Objects: tables, views, and procedures

Objects' fields (or parameters)

Objects' fields' properties: type, length, precision, scale, identity column, nullable

Tables' indexes and primary keys

Parent => child relations between tables including primary key/foreign key equivalences.

Views' and Procedures' definitions (Comments).

A view's underlying tables.

The database connection is cached in the schema object. Objects and their fields properties and index information are retrieved when the schema object is created. Table relationship information is retrieved for all tables when the first relationship information is requested.

CREATING A NEW RDBAL::Schema OBJECT:

$query = new RDBAL::Schema($connection,$database);

OR

$query = new RDBAL::Schema($connection,$database, -option => value);

Options are passed as: -option => value, where -option is one of:

-server         Database server name.  This is used to differentiate
                between databases when caching.
-server_type    Database server type.  This is used to differentiate
                how to retrieve the schema.  The default is Transact-SQL
                or a hand-crafted schema cache file.  Currently, the
                only correct values for this are I<undef>, I<oracle>,
                I<dbi:Sybase>, or I<dbi:Oracle>.
-get_system     1 or undef.  A true value for this option causes retrieval
                (and caching) of schema for system tables.
-nocache        1 or undef.  A true value causes the cached schema to not
                be used and a new cache to be written.

This will create a new schema object for the database. This must be given an open connection to a RDBAL database server object:

use RDBAL;
$connection = RDBAL::Connect('username', 'password', 'server');

Fetching the database from the schema object:

$database = $schema->Database();

The database may be retrieved from the database schema object.

Fetching the user tables from the schema:

@user_tables = $schema->User_Tables();

The user tables may be retrieved from the database schema.

Fetching the views from the schema:

@views = $schema->Views();

The views may be retrieved from the database schema.

Fetching the procedures from the schema:

@procedures = $schema->Procedures();

The procedures may be retrieved from the database schema.

Fetching the system tables from the schema:

@system_tables = $schema->System_Tables();

The system tables may be retrieved from the database schema if the -get_system option was given when the schema object (or its cache) was created.

Fetching the fields from the schema for a user table:

@fields = $schema->Table_Fields($table);

A user table's fields may be retrieved from the database schema.

Fetching the fields from the schema for a system table:

@fields = $schema->System_Table_Fields($table);

A system table's fields may be retrieved from the database schema if the -get_system option was given when the schema object (or its cache) was created.

Fetching the fields from the schema for a view:

@fields = $schema->View_Fields($view);

A view's fields may be retrieved from the database schema.

Fetching the parameters from the schema for a procedure:

@parameters = $schema->Procedure_Parameters($procedure);

A procedure's parameters may be retrieved from the database schema.

Fetching the fields from the schema:

@fields = $schema->Fields($object,$object_type);

$object_type is optional and defaults to 'User Table'.

The fields may be retrieved from the database schema for $object_type's of:

'User Table'

'System Table'

'View'

'Procedure'

Fetching the primary key number from the schema for a field:

$primary_key_number = $schema->Primary_Key($object,$field,$object_type);

$object_type is optional and defaults to 'User Table'.

The primary key number may be retrieved from the database schema for a field. undef is returned if the field is not a primary key.

Fetching the field type from the schema:

$field_type = $schema->Field_Type($object,$field,$object_type);

$object_type is optional and defaults to 'User Table'.

The field type may be retrieved from the database schema.

Fetching the field length from the schema:

$field_length = $schema->Field_Length($object,$field,$object_type);

$object_type is optional and defaults to 'User Table'.

The field length may be retrieved from the database schema.

Fetching the field width from the schema:

$field_width = $schema->Field_Width($object,$field,$object_type);

$object_type is optional and defaults to 'User Table'.

A value for the character string width of a field may be retrieved from the database schema.

Fetching the field precision from the schema:

$field_precision = $schema->Field_Precision($object,$field,$object_type);

$object_type is optional and defaults to 'User Table'.

The field precision may be retrieved from the database schema.

Fetching the field scale from the schema:

$field_scale = $schema->Field_Scale($object,$field,$object_type);

$object_type is optional and defaults to 'User Table'.

The field scale may be retrieved from the database schema. If the field datatype does not have a scale, the value is undef.

Fetching the field's identity column status from the schema:

$field_identity = $schema->Field_Identity($object,$field,$object_type);

$object_type is optional and defaults to 'User Table'.

The field's identity column status may be retrieved from the database schema. Nonzero implies the field is an identity column.

Fetching the field nullable from the schema:

$field_null = $schema->Field_Null($object,$field,$object_type);

$object_type is optional and defaults to 'User Table'.

Whether a field is nullable may be retrieved from the database schema. It is a 1 if the field is nullable.

Fetching a field's (or parameter's) information from the schema:

$field_info = $schema->Field_Info($object,$field,$object_type,$info_type);

$object_type is optional and defaults to 'User Table'. $info_type is optional and defaults to 'Type'.

A field's information may be retrieved from the database schema for $object_type's of:

'User Table'

'System Table'

'View'

'Procedure'

and $info_type's of:

'Primary_Key'

'Type'

'Length'

'Precision'

'Scale'

'Identity'

'Null'

The primary key number may be retrieved from the database schema for a field. undef is returned if the field is not a primary key.

The field type may be retrieved from the database schema.

The field length may be retrieved from the database schema.

The field precision may be retrieved from the database schema.

The field scale may be retrieved from the database schema.

The field's identity column status may be retrieved from the database schema. Nonzero implies the field is an identity column.

Whether a field is nullable may be retrieved from the database schema. It is a 1 if the field is nullable.

Fetching the indexes from the schema:

@indexes = $schema->Indexes($table,$object_type);

$object_type is optional and defaults to 'User Table'.

The indexes may be retrieved from the database schema. Each index is reported as (each item seperated by tabs):

index_name	index_description	comma_seperated_index_field_list

Example:

PK_STS	clustered, unique located on default	chromosome, arm, id_number

Fetching the primary key fields from the schema:

@primary_keys = $schema->Primary_Keys($table,$object_type);

$object_type is optional and defaults to 'User Table'.

The primary key fields may be retrieved from the database schema.

Fetching all index keys from the schema:

@keys = $schema->Keys($table,$object_type);

$object_type is optional and defaults to 'User Table'.

All of the index keys may be retrieved from the database schema. Each array element is a comma delimited list of the index's keys.

Fetching the children tables from the schema:

@children_tables = $schema->Children($table);

A table's children tables may be retrieved from the database schema.

Fetching the parent tables from the schema:

@parent_tables = $schema->Parents($table);

A table's parent tables may be retrieved from the database schema.

Fetching the field equivalences from the schema:

@field_equivalences = $schema->Relation($parent,$child);

The key field equivalences may be retrieved from the database schema. Each key field equivalence array element is given as: parent_key_field=child_key_field

Fetching the comments (views' and procedures' definitions) from the schema:

$comments = $schema->Comments($object,$object_type)

$object_type is optional and defaults to 'Procedure'.

The comments (views' and procedures' definitions) may be retrieved from the database schema.

Getting a view's underlying tables from the schema:

@tables = $schema->View_Tables($view)

A view's underlying tables may be retrieved from the database schema.

Quote a field's value if necessary

$quoted_field = $schema->Quote_Field($object,$field,$value,$object_type);

Appropriately put quote marks around a field's value. Single quote marks get doubled, example: dont't ==> "don''t".

Example Script

#!/usr/local/bin/perl

use RDBAL
use RDBAL::Schema;

$server   = shift;
$database = shift;
$username = shift;
$password = shift;

# Check to see if we want to use a different name for the server
if ($RDBAL::Layer{'SybaseDBlib'} || $RDBAL::Layer{'ApacheSybaseDBlib'}) {
    $server = 'sybase_sql';
} else {
    $server = 'odbc_sql';
}
if (!defined($server) ||
    !defined($database) ||
    !defined($username) ||
    !defined($password)) {
    die "Usage is: get_schema.pl server database username password\n";
}

# Get connnection to database server
( $X = RDBAL::Connect($username,$password,$server)
 or (die "Failed to connect to $server $username"));

$schema = new RDBAL::Schema($X,$database, -get_system => 1);

$, = "\t";
print "Info for database: " . $schema->Database() . "\n";
print "User Tables:\n";
map {
    $table = $_;
    print "\tTable: $table\n";
    map {
	print "\t\t". $_ . (($schema->Primary_Key($table,$_)) ? '*' : ''),
	$schema->Field_Type($table,$_),
	$schema->Field_Length($table,$_),
	$schema->Field_Precision($table,$_),
	$schema->Field_Scale($table,$_),
	(($schema->Field_Identity($table,$_)) ? 'Identity' : ''),
	(($schema->Field_Null($table,$_)) ? 'NULL' : 'NONNULL')
	  . "\n";
    } $schema->Table_Fields($table);
    print "\t\tPrimary keys:", $schema->Primary_Keys($table,'User Table'),"\n";
    map {
	@keys = split(',',$_);
	print "\t\tIndex keys:", @keys ,"\n";
    } $schema->Keys($table,'User Table');
    map {
	($index_name, $index_description, $keys) = split("\t",$_);
	print "\t\tIndexes:\t$index_name\t$keys\t$index_description\n";
    } $schema->Indexes($table,'User Table');
    print "\t\tComments:", $schema->Comments($table,'User Table'), "\n";
    map {
	print "\t\tParents: $_ (Reverse)", $schema->Relation($_,$table),"\n";
    } $schema->Parents($table);
    map {
	print "\t\tChildren: $_", $schema->Relation($table,$_),"\n";
    } $schema->Children($table);
} $schema->User_Tables();

print "System Tables:\n";
map {
    $table = $_;
    print "\tTable: $table\n";
    map {
	print "\t\t". $_ . (($schema->Primary_Key($table,$_,'System Table')) ? '*' : ''),
	$schema->Field_Type($table,$_,'System Table'),
	$schema->Field_Length($table,$_,'System Table'),
	$schema->Field_Precision($table,$_,'System Table'),
	$schema->Field_Scale($table,$_,'System Table'),
	(($schema->Field_Identity($table,$_,'System Table')) ? 'Identity' : ''),
	(($schema->Field_Null($table,$_,'System Table')) ? 'NULL' : 'NONNULL')
	    . "\n";
    } $schema->System_Table_Fields($table);
    print "\t\tComments:", $schema->Comments($table,'System Table'), "\n";
} $schema->System_Tables();

print "Views:\n";
map {
    $table = $_;
    print "\tView: $table (Tables:", $schema->View_Tables($_), ")\n";
    map {
	print "\t\t". $_,
	$schema->Field_Type($table,$_,'View'),
	$schema->Field_Length($table,$_,'View'),
	$schema->Field_Precision($table,$_,'View'),
	$schema->Field_Scale($table,$_,'View'),
	(($schema->Field_Null($table,$_,'View')) ? 'NULL' : 'NONNULL')
	  . "\n";
    } $schema->View_Fields($table);
    print "\tComments:", $schema->Comments($table,'View'), "\n";
} $schema->Views();

print "Procedures:\n";
map {
    $table = $_;
    print "\tProcedure: $table\n";
    map {
	print "\t\t". $_,
	$schema->Field_Type($table,$_,'Procedure'),
	$schema->Field_Length($table,$_,'Procedure'),
	$schema->Field_Precision($table,$_,'Procedure'),
	$schema->Field_Scale($table,$_,'Procedure'),
	(($schema->Field_Null($table,$_,'Procedure')) ? 'NULL' : 'NONNULL')
	 . "\n";
    } $schema->Procedure_Parameters($table);
    print "\tComments:", $schema->Comments($table,'Procedure'), "\n";
} $schema->Procedures();

REPORTING BUGS

When reporting bugs/problems please include as much information as possible.

A small script which yields the problem will probably be of help. If you cannot include a small script then please include a Debug trace from a run of your program which does yield the problem.

AUTHOR INFORMATION

Brian H. Dunford-Shore brian@ibc.wustl.edu

Copyright 1998, Washington University School of Medicine, Institute for Biomedical Computing. All rights reserved.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

Address bug reports and comments to: www@ibc.wustl.edu

TODO

These are features that would be nice to have and might even happen someday (especially if YOU write it).

Alternative module interfaces to the database servers:

(DBI/DBD).

Other types of database servers:

(Oracle, PostgreSQL, mSQL, mySQL, etc.).

SEE ALSO

RDBAL -- http://www.ibc.wustl.edu/perl5/other/RDBAL.html

Sybase::DBlib -- http://www.ibc.wustl.edu/perl5/other/sybperl.html

Win32::odbc -- http://www.ibc.wustl.edu/perl5/other/Win32/odbc.html

CREDITS

Thanks very much to:

David J. States (states@ibc.wustl.edu)

for suggestions and bug fixes.

BUGS

You really mean 'extra' features ;). None known.

COPYRIGHT

Copyright (c) 1997 Washington University, St. Louis, Missouri. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

cpanm RDBAL

perl -MCPAN -e shell
install RDBAL