RDBAL::Schema - RDBAL Schema information object
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)
This perl library uses perl5 objects to make it easy to retrieve information about a particular PostgreSQL, Sybase, MS SQL, or Oracle databases's schema.
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
An example script for converting a DDL in Transact SQL format to PostgreSQL format with foreign key relational integrity enforced and remembered by PostgreSQL rules is included and must be manually installed (change the first line from #!/usr/local/bin/perl if your perl lives elsewhere). See pg_schema.pl below for more.
The schema information available includes:
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.
The rules this script creates are necessary for RDBAL to have foreign key relation information. It translates a foreign key reference into PostgreSQL rules:
FROM:
alter table table2
add foreign key (field1, field2)
references table1 (field1, field2)
go
TO:
Insert RI:
create rule fk_table2_insert as on insert to table2
where new.field1 <> table1.field1 and new.field2 <> table1.field1
do instead nothing;
Update RI:
create rule fk_table2_update as on update to table2
where new.field1 <> table1.field1 and new.field2 <> table1.field2
do instead nothing;
create rule pk_table1_update as on update to table1
where old.field1 = table2.field1 and old.field2 = table2.field2
do instead nothing;
NOTE: the pk_update rule must list all children!
Delete RI:
create rule pk_table1_delete as on delete to table1
where old.field1 = table2.field1 and old.field2 = table2.field2
do instead nothing;
NOTE: the delete rule must list all children!
This script also maps datatypes:
pg_schema.pl reads the input DDL on standard input and writes the output DDL to standard output.
$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<pg>,
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.
-username Database username. This is used to differentiate
between different users views of a database when caching.
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');
$database = $schema->Database();
The database may be retrieved from the database schema object.
@user_tables = $schema->User_Tables();
The user tables may be retrieved from the database schema.
@views = $schema->Views();
The views may be retrieved from the database schema.
@procedures = $schema->Procedures();
The procedures may be retrieved from the database 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.
@fields = $schema->Table_Fields($table);
A user table's fields may be retrieved from the database schema.
@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.
@fields = $schema->View_Fields($view);
A view's fields may be retrieved from the database schema.
@parameters = $schema->Procedure_Parameters($procedure);
A procedure's parameters may be retrieved from the database 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:
$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.
$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.
$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.
$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.
$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.
$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.
$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.
$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.
$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:
and $info_type's of:
@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
@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.
@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.
@children_tables = $schema->Children($table);
A table's children tables may be retrieved from the database schema.
@parent_tables = $schema->Parents($table);
A table's parent tables may be retrieved from the database 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
$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.
@tables = $schema->View_Tables($view)
A view's underlying tables may be retrieved from the database schema.
$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".
The schema cache file contains individual lines which describe some part of a schema (a descriptor). Each descriptor is a tab delimited line with the first element on the line specifying what is being described (the type). Carriage returns (not newlines) may be embedded in the comment (definition) descriptor elements. Blank lines follow each group of index descriptors for a table. Line feeds seperate different types of descriptors.
An object descriptor describes the fields or parameters of a system or user table, a view, or a procedure. It contains the following (tab delimited) elements:
S => 'System Table'
U => 'User Table'
V => 'View'
P => 'Procedure'
TABLE => 'User Table'
VIEW => 'View'
PROCEDURE => 'Procedure'
A primary key descriptor lists the primary key for a table and the fields which comprise the primary key. It contains the following (tab delimited) elements:
A foreign key descriptor lists the foreign key relationships for a table. Each descriptor pairs a primary key field name of one table with the corresponding foreign key field name of another table. It also gives the position each of those fields within the primary and foreign key tuples. It contains the following (tab delimited) elements:
An index descriptor lists the indexes for a table and the fields which comprise that index. It contains the following (tab delimited) elements (blank lines follow each set of indexes for a table):
A comment (or definition) descriptor contains the following (tab delimited) elements:
#!/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();
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.
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
These are features that would be nice to have and might even happen someday (especially if YOU write it).
(mSQL, mySQL, etc.).
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
Thanks very much to:
David J. States (states@ibc.wustl.edu)
for suggestions and bug fixes.
You really mean 'extra' features ;). None known.
Copyright (c) 1997, 1998, 1999 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.
