The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
RDBAL-1.2
River stage zero No dependents
++
/ RDBAL::Schema

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 PostgreSQL, Sybase, MS SQL, or Oracle 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

Installation of pg_schema.pl

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.

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.

pg_schema.pl

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:

smalldatetime to datetime
tinyint to int
float4 or float8 to float

pg_schema.pl reads the input DDL on standard input and writes the output DDL to standard output.

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<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');

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".

Schema Cache File Format

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.

Object Descriptor

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:

Type = 'O'
Object = owner.object (owner defaults to 'dbo')
Object type:
       S => 'System Table'
       U => 'User Table'
       V => 'View'
       P => 'Procedure'
       TABLE => 'User Table'
       VIEW => 'View'
       PROCEDURE => 'Procedure'
Field name
Field type
Field length
Field precision
Field scale
Identity field (1 if true)
Nullable field (1 if true)

Primary Key Descriptor

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:

Type = 'PK'
Name of object having primary key
Object type ('User Table' or 'System Table')
Database
Owner (default is 'dbo')
Object (table)
Primary Key field
Position in primary key
Primary Key name (optional)

Foreign Key Descriptor

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:

Type = 'FK'
Object type ('User Table' or 'System Table')
Database
Owner (default is 'dbo')
Primary Key Object (table)
Primary Key field
Database
Owner (default is 'dbo')
Foreign Key Object (table)
Foreign Key field
Foreign key field position in tuple
Update rule number -- always 1, if present
Delete rule number -- always 1, if present
Foreign Key name (Optional)
Primary Key name (Optional)

Index Descriptors

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):

Type = 'I'
Index Object (table)
Index Object type ('User Table' or 'System Table')
Index Name
Index description
Comma seperated, ordered index field list

Comments (and stored procedure definitions) Descriptors

A comment (or definition) descriptor contains the following (tab delimited) elements:

Type = 'C'
Object
Object Type is one of: 'User Table', 'System Table', 'View', 'Procedure'
Comment or definition (carriage returns replace newlines and tabs are replaced by five spaces

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).

Other types of database servers:

(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, 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.

1 POD Error

The following errors were encountered while parsing the POD:

Around line 1867:

You forgot a '=back' before '=head2'