#!/usr/bin/perl
package Schema;

#######################################################################
## Author : Gail Binkley
## Date   : 2000-01-29
##
## Description : This package (class) contains subroutines (methods)
##               for retrieving details about a single table or
##               all tables in a schema
##
## Usage :
## use Schema;
##
## To instantiate a new schema object :
## my $schemaObj = Schema->new(database=>$database,
##                             user=>$user,
##                             owner=>$owner,
##                             table=>$table);
##
#######################################################################
use strict;
use DBI;

use File::Basename;
use lib dirname($ENV{DOCUMENT_ROOT})."/lib/common";

use Login qw(ConnectToDatabase);

## Global Variables
my $dbh;

#######################################################################
sub new {     ##### Constructor #####
#######################################################################
    my ($self, %args) = @_;

    $self = {};   # create empty Schema object hash
    bless $self;

    if ($args{'database'}) {

        $self->{'_databaseName'} = lc($args{'database'});

        if ($args{'user'}) {
            $self->{'_user'} = lc($args{'user'});
        } else {
            print "No database user was passed to this object: $!\n.";
            return;
        }

       $dbh = &ConnectToDatabase($self->{'_databaseName'}, $self->{'_user'});

    } else {
        print "No database name was passed to this object: $!\n.";
        return;
    }

    if ($args{'owner'}) {
        $self->{'_owner'} = uc($args{'owner'});
    }
    else {
        print "No schema owner was passed to this object: $!\n.";
        return;
    }

    if ($args{'table'}) {
        $self->{'_table'} = uc($args{'table'});
    }
    else {
        print "No table name was passed to this object: $!\n.";
        return;
    }

    if ($self->{'_table'} eq 'ALL') {

        my $isValidOwner = $self->isValidOwner;
        # check to see if owner is valid
        if ($isValidOwner) {

            $self->initAllTables;
            return $self;
        } else {
            print "An invalid user name was passed to this object: $!\n.";
            return;
        }

    } else {

        # check to see if owner and table are valid
        # only initialize object if both exist
        my $isValidOwner = $self->isValidOwner;
        my $isValidTable = $self->isValidTable;
        if ($isValidOwner && $isValidTable) {

            $self->initOneTable;
            return $self;

        } else {
            print "An invalid user name and/or table name was passed to this object: $!\n.";
            return;
        }
    }

    $dbh->disconnect;
}

#######################################################################
sub isValidOwner {
#######################################################################
# Returns a boolean (0/1) depending if the owner exists

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT username
          FROM all_users
         WHERE username = ?");

    $sth->execute($self->{'_owner'});
    my ($username)= $sth->fetchrow;
    $sth->finish;

    if ($username) {
        $self->{'_isValidOwner'} = 1;
    } else {
        $self->{'_isValidOwner'} = 0;
        print "Schema owner '$self->{'_owner'}' is not in the '$self->{'_databaseName'}' database: $!\n.";
        return;
    }
}

#######################################################################
sub isValidTable {
#######################################################################
# Returns a boolean (0/1) depending if the table name is exists.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT table_name
          FROM all_tables
         WHERE owner = ?
           AND table_name = ?");

    $sth->execute($self->{'_owner'}, $self->{'_table'});
    my ($table_name)= $sth->fetchrow;
    $sth->finish;

    if ($table_name) {
        $self->{'_isValidTable'} = 1;
    } else {
        $self->{'_isValidTable'} = 0;
        print "Table '$self->{'_table'}' is not owned by  '$self->{'_owner'}' in the '$self->{'_databaseName'}' database: $!\n.";
        return;
    }
}

#######################################################################
sub initOneTable {
#######################################################################
# Initialize entire Schema object for one table

    my ($self) = @_;

    $self->getTableNumRows;
    $self->getTableComment;
    $self->getColumns;
    $self->getPK;
    $self->getFKs;
    $self->getIndexes;
    $self->getConstraints;
    $self->getSequences;

}

#######################################################################
sub initAllTables {
#######################################################################
# Initialize entire Schema object for all tables

    my ($self) = @_;

    $self->getAllTableInfo;
    $self->getAllColumns;
    $self->getAllPKs;
    $self->getAllFKs;
    $self->getAllIndexes;
    $self->getAllConstraints;
    $self->getAllSequences;

}

#######################################################################
sub getTableNumRows {
#######################################################################
# Returns the number of rows in a table.
# Does not do a count(*), but returns the number of rows from the last
# time the table was analyzed

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT num_rows
          FROM all_tables
         WHERE owner = ?
           AND table_name = ?");

    $sth->execute($self->{'_owner'}, $self->{'_table'});
    my ($tableNumRows)= $sth->fetchrow;
    $sth->finish;

    $self->{'_tableNumRows'} = $tableNumRows;

}

#######################################################################
sub getTableComment {
#######################################################################
# Returns the comment/description for a table.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT comments
          FROM all_tab_comments
         WHERE owner = ?
           AND table_name = ?");

    $sth->execute($self->{'_owner'}, $self->{'_table'});
    my ($tableComment)= $sth->fetchrow;
    $sth->finish;

    $self->{'_tableComment'} = $tableComment;

}

#######################################################################
sub getAllTableInfo {
#######################################################################
# Returns all table names, counts, and comments for a schema

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.table_name, a.num_rows, b.comments
          FROM all_tables a, all_tab_comments b
         WHERE a.table_name = b.table_name
           AND a.owner = b.owner
           AND a.owner = ?
         ORDER BY a.table_name");

    $sth->execute($self->{'_owner'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;

    $self->{'_allTableInfo'} = $arrayRef;

}


#######################################################################
sub getColumns {
#######################################################################
# Returns all column specifications for a table in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.column_name, a.data_type, a.data_length,
               a.data_precision, a.data_scale, a.nullable, a.data_default,
               b.comments
          FROM all_tab_columns a, all_col_comments b
         WHERE a.owner = b.owner
           AND a.table_name = b.table_name
           AND a.column_name = b.column_name
           AND a.owner = ?
           AND a.table_name = ?");

    $sth->execute($self->{'_owner'}, $self->{'_table'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_columnInfo'} = $arrayRef;

}

#######################################################################
sub getAllColumns {
#######################################################################
# Returns all column specifications for all tables in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.table_name, a.column_name, a.data_type, a.data_length,
               a.data_precision, a.data_scale, a.nullable, a.data_default,
               b.comments
          FROM all_tab_columns a, all_col_comments b
         WHERE a.owner = b.owner
           AND a.table_name = b.table_name
           AND a.column_name = b.column_name
           AND a.owner = ?
           ORDER BY a.table_name");

    $sth->execute($self->{'_owner'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_allColumnInfo'} = $arrayRef;

}

#######################################################################
sub getPK {
#######################################################################
# Returns all primary key specifications for a table in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.constraint_name, a.column_name, a.position
          FROM all_cons_columns a, all_constraints b
         WHERE a.owner = b.owner
           AND a.table_name = b.table_name
           AND a.constraint_name = b.constraint_name
           AND b.constraint_type = 'P'
           AND a.owner = ?
           AND a.table_name = ?
         ORDER by a.position");

    $sth->execute($self->{'_owner'}, $self->{'_table'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_PKInfo'} = $arrayRef;

}

#######################################################################
sub getAllPKs {
#######################################################################
# Returns all primary key specifications for all tables in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.table_name, a.constraint_name, a.column_name, a.position
          FROM all_cons_columns a, all_constraints b
         WHERE a.owner = b.owner
           AND a.table_name = b.table_name
           AND a.constraint_name = b.constraint_name
           AND b.constraint_type = 'P'
           AND a.owner = ?
         ORDER by a.table_name, a.position");

    $sth->execute($self->{'_owner'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_allPKInfo'} = $arrayRef;

}

#######################################################################
sub getFKs {
#######################################################################
# Returns all foreign key specifications for a table in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.constraint_name, b.column_name,
               c.table_name, c.column_name, c.position
          FROM all_constraints  a,
               all_cons_columns b,
               all_cons_columns c
         WHERE a.constraint_name = b.constraint_name
           AND a.owner           = b.owner
           AND c.constraint_name = a.r_constraint_name
           AND b.position        = c.position
           AND a.constraint_type = 'R'
           AND c.owner           = ?
           AND a.table_name      = ?
         ORDER BY a.constraint_name,
                  c.position");

    $sth->execute($self->{'_owner'}, $self->{'_table'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_FKInfo'} = $arrayRef;

}

#######################################################################
sub getAllFKs {
#######################################################################
# Returns all foreign key specifications for all tables in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.table_name, a.constraint_name, b.column_name,
               c.table_name, c.column_name, c.position
          FROM all_constraints  a,
               all_cons_columns b,
               all_cons_columns c
         WHERE a.constraint_name = b.constraint_name
           AND a.owner           = b.owner
           AND c.constraint_name = a.r_constraint_name
           AND b.position        = c.position
           AND a.constraint_type = 'R'
           AND c.owner           = ?
         ORDER BY a.table_name,
                  a.constraint_name,
                  c.position");

    $sth->execute($self->{'_owner'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_allFKInfo'} = $arrayRef;

}

#######################################################################
sub getIndexes {
#######################################################################
# Returns all indexes for a table in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.index_name, a.uniqueness, b.column_name
          FROM all_indexes a, all_ind_columns b
         WHERE a.table_owner = b.index_owner
           AND a.table_name = b.table_name
           AND a.index_name = b.index_name
           AND a.owner = ?
           AND b.table_name = ?
           AND NOT EXISTS (SELECT 'x'
                             FROM all_constraints c
                            WHERE c.constraint_name = a.index_name
                              AND c.constraint_type = 'P'
                              AND a.table_owner = ?)
         ORDER BY b.index_name,
                  b.column_position");

    $sth->execute($self->{'_owner'}, $self->{'_table'}, $self->{'_owner'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_indexInfo'} = $arrayRef;

}

#######################################################################
sub getAllIndexes {
#######################################################################
# Returns all indexes for all tables in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.table_name, a.index_name, a.uniqueness, b.column_name
          FROM all_indexes a, all_ind_columns b
         WHERE a.table_owner = b.index_owner
           AND a.table_name = b.table_name
           AND a.index_name = b.index_name
           AND a.owner = ?
           AND NOT EXISTS (SELECT 'x'
                             FROM all_constraints c
                            WHERE c.constraint_name = a.index_name
                              AND c.constraint_type = 'P'
                              AND a.table_owner = ?)
         ORDER BY a.table_name,
                  b.index_name,
                  b.column_position");

    $sth->execute($self->{'_owner'}, $self->{'_owner'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_allIndexInfo'} = $arrayRef;

}

#######################################################################
sub getConstraints {
#######################################################################
# Returns all check and null constraints for a table in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.constraint_name,
               b.column_name,
               a.constraint_type,
               a.search_condition
          FROM all_constraints a,
               all_cons_columns b
         WHERE a.owner           = b.owner
           AND a.constraint_name = b.constraint_name
           AND a.constraint_type = 'C'
           AND a.owner           = ?
           AND a.table_name      = ?
         ORDER BY a.constraint_name,
                  b.position");

    $sth->execute($self->{'_owner'}, $self->{'_table'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_constraintInfo'} = $arrayRef;

}

#######################################################################
sub getAllConstraints {
#######################################################################
# Returns all check and null constraints for a table in an array.

    my ($self) = @_;

    my $sth = $dbh->prepare("
        SELECT a.table_name, a.constraint_name,
               b.column_name,
               a.constraint_type,
               a.search_condition
          FROM all_constraints a,
               all_cons_columns b
         WHERE a.owner           = b.owner
           AND a.constraint_name = b.constraint_name
           AND a.constraint_type = 'C'
           AND a.owner           = ?
         ORDER BY a.table_name,
                  a.constraint_name,
                  b.position");

    $sth->execute($self->{'_owner'});
    my $arrayRef = $sth->fetchall_arrayref();
    $sth->finish;
    $self->{'_allConstraintInfo'} = $arrayRef;

}


#######################################################################
sub getSequences {
#######################################################################
# Returns all Oracle sequences for a table in an array.

    my ($self) = @_;

    my ($arrayref, $seqfile);

    if ($self->{'_databaseName'} eq 'dictyBase' || $self->{'_databaseName'} eq 'sdev') {

        $seqfile = "/usr/local/dicty/www_dictybase/db/lib/data/dictyBase_sequences.xls";

    } elsif ($self->{'_databaseName'} eq 'mad' || $self->{'_databaseName'} eq 'mdev') {

        $seqfile = "/usr/local/dicty/www_dictybase/db/lib/data/smd_sequences.xls";

    } elsif ($self->{'_databaseName'} eq 'gen') {

        $seqfile = "/usr/local/dicty/www_dictybase/db/lib/data/gen_sequences.xls";

    } else {

        return $self->{'_allSequenceInfo'};

    }

    open(IN, "$seqfile") ||
        die "Can't open '$seqfile' for reading:$!\n";

    while (<IN>) {

        chomp;
        my $line = $_;

        my ($table, $column, $sequence_name) = split (/\t/, $line);

        if ($self->{'_table'} eq $table ) {
            push @{$arrayref}, [$column, $sequence_name];
        }
    }

    $self->{'_sequenceInfo'} = $arrayref;

}

#######################################################################
sub getAllSequences {
#######################################################################
# Returns all Oracle sequences for all tables in an array.

    my ($self) = @_;

    my ($arrayref, $seqfile);

    if ($self->{'_databaseName'} eq 'dictyBase' || $self->{'_databaseName'} eq 'sdev') {

        $seqfile = "/usr/local/dicty/www_dictybase/db/lib/data/dictyBase_sequences.xls";

    } elsif ($self->{'_databaseName'} eq 'mad' || $self->{'_databaseName'} eq 'mdev') {

        $seqfile = "/usr/local/dicty/www_dictybase/db/lib/data/smd_sequences.xls";

    } elsif ($self->{'_databaseName'} eq 'gen') {

        $seqfile = "/usr/local/dicty/www_dictybase/db/lib/data/gen_sequences.xls";

    } else {

        return $self->{'_allSequenceInfo'};

    }

    open(IN, "$seqfile") ||
        die "Can't open '$seqfile' for reading:$!\n";

    while (<IN>) {

        chomp;
        my $line = $_;

        my ($table, $column, $sequence_name) = split (/\t/, $line);

        push @{$arrayref}, [$table, $column, $sequence_name];
    }

    $self->{'_allSequenceInfo'} = $arrayref;

}

sub databaseName { $_[0]->{_databaseName} }
sub user { $_[0]->{_user} }
sub owner { $_[0]->{_owner} }
sub table { $_[0]->{_table} }
sub tableComment  { $_[0]->{_tableComment} }
sub tableNumRows  { $_[0]->{_tableNumRows} }
sub columnInfo { $_[0]->{_columnInfo} }
sub PKInfo { $_[0]->{_PKInfo} }
sub FKInfo { $_[0]->{_FKInfo} }
sub indexInfo { $_[0]->{_indexInfo} }
sub constraintInfo { $_[0]->{_constraintInfo} }
sub sequenceInfo { $_[0]->{_sequenceInfo} }
sub allTableInfo { $_[0]->{_allTableInfo} }
sub allColumnInfo { $_[0]->{_allColumnInfo} }
sub allPKInfo { $_[0]->{_allPKInfo} }
sub allFKInfo { $_[0]->{_allFKInfo} }
sub allIndexInfo { $_[0]->{_allIndexInfo} }
sub allConstraintInfo { $_[0]->{_allConstraintInfo} }
sub allSequenceInfo { $_[0]->{_allSequenceInfo} }

#######################################################################
1;
#######################################################################

# Pod Documentation from here on down

=pod

=head1 Name

Schema.pm

=head1 Description

This Perl object (Schema.pm) provides all relevant meta data about an
Oracle schema (set of database objects belonging to a given Oracle
user in a given Oracle instance).  The Schema.pm object contains
information about one table or all tables for a given schema.  Once an
object has been instantiated, methods are available to retrieve the
individual attributes of the object.  Most accessor methods return an
array reference.

=head1 Instantiating a New Schema Object

Usage:  my $schemaObj = Schema->new(database=>$database,
                                    user=>$user,
                                    owner=>$owner,
                                    table=>$table);

$database = Oracle instance/database name
$user     = Oracle username used to connect to the database
$owner    = Oracle username who owns the tables
$table    = Either a specific table name or 'ALL' for all tables

=head1 Accessor Methods

=head2 getTableNumRows

Returns the number of rows in a table in a scalar reference.  It does
not do a count(*), but returns the number of rows calculated the last
time the table was analyzed.

Usage:         my $tableNumRows = $$schemaObjRef->getTableNumRows;

=head2 getTableComment

Returns the table description in a scalar reference.

Usage:         my $tableComment = $$schemaObjRef->getTableComment;

=head2 getAllTableInfo

Returns all table names, number of rows per table, and table
descriptions in an array reference.

Usage: my $allTableInfo  = $$schemaObjRef->allTableInfo;

       foreach my $row (@{$allTableInfo}) {

           my ($table_name, $NumberOfRows, $description) = @{$row};

           ........

=head2 getColumns

Returns all column information for a given table in an array
reference.  The column attributes include: column name, data type,
length, precision, scale, nullable (Y/N), column description.  Columns
that have a character data type, the length attribute gives the
maximum size of the column.  Columns that have a number data type, the
length is given by the precision attribute and the scale gives the
number of places after the decimal point.

Usage:  my $columnInfo = $$schemaObjRef->columnInfo;

        foreach my $row (@{$columnInfo}){

           my ($column_name, $data_type, $length, $precision, $scale, $nullable, $default_value, $description) = @{$row};

           ........

=head2 getAllColumns

Returns all column information for all tables in an array reference.
The column attributes include: table name, column name, data type,
length, precision, scale, nullable (Y/N), column description.  Columns
that have a character data type, the length attribute gives the
maximum size of the column.  Columns that have a number data type, the
length is given by the precision attribute and the scale gives the
number of places after the decimal point.

Usage:  my $allColumnInfo = $$schemaObjRef->allColumnInfo;

        foreach my $row (@{$allColumnInfo}) {

           my ($table_name, $column_name, $data_type, $length, $precision, $scale, $nullable, $default_value, $description) = @{$row};

           ........

=head2 getPK

Returns all primary key information for a table in an array reference.
The primary key attributes include: primary key name, column in the
primary key, position of the column in the primary key.

Usage:  my $PKInfo = $$schemaObjRef->PKInfo;

        foreach my $row (@{$PKInfo}){

           my ($pk_name, $column_name, $position) = @{$row};

           ..........

=head2 getAllPKs

Returns all primary key information for all tables in an array
reference.  The primary key attributes include: table name, primary
key name, column in the primary key, position of the column in the
primary key.

Usage:  my $allPKInfo = $$schemaObjRef->allPKInfo;

        foreach my $row (@{$allPKInfo}){

           my ($table_name, $pk_name, $column_name, $position) = @{$row};

           ............

=head2 getFKs

Returns all foreign key information for a table in an array reference.
The foreign key attributes include: foreign key name, column in the
foreign key, table name the foreign key references, column name the
foreign key references, position of the column in the foreign key.

Usage:  my $FKInfo = $$schemaObjRef->FKInfo;

        foreach my $row (@{$FKInfo}){

           my ($fk_name, $column_name, $referenced_table, $referenced_column, $position) = @{$row};

           ............

=head2 getAllFKs

Returns all foreign key information for all tables in an array
reference.  The foreign key attributes include: table name, foreign
key name, column in the foreign key, table name the foreign key
references, column name the foreign key references, position of the
column in the foreign key.

Usage: my $allFKInfo = $$schemaObjRef->allFKInfo;

       foreach my $row (@{$allFKInfo}){

          my ($table_name, $fk_name, $column_name, $referenced_table, $referenced_column, $position) = @{$row};

          ........

=head2 getIndexes

Returns all indexe information for a table in an array reference.  The
index attributes include: index name, uniqueness (UNIQUE/NONUNIQUE),
column_name.  Primary key indexes are not included.

Usage:  my $indexInfo = $$schemaObjRef->indexInfo;

        foreach my $row (@{$indexInfo}){

           my ($index_name, $uniqueness, $column_name) = @{$row};

           ..........

=head2 getAllIndexes

Returns all indexe information for all tables in an array reference.
The index attributes include: table name, index name, uniqueness
(UNIQUE/NONUNIQUE), column_name.  Primary key indexes are not
included.

Usage: my $allIndexInfo = $$schemaObjRef->allIndexInfo;

       foreach my $index_row (@{$allIndexInfo}){

           my ($table_name, $index_name, $uniqueness, $column_name) = @{$row};

           ..........

=head2 getConstraints

Returns all check and null constraint information for a table in an
array reference.  The constraint attributes include: constraint name,
column name, constraint type (NULL/CHECK), constraint criteria.

Usage: my $constraintInfo = $$schemaObjRef->constraintInfo;

       foreach my $row (@{$constraintInfo}){

        my ($constraint_name, $column_name, $type, $criteria) = @{$row};

        ..........

=head2 getAllConstraints

Returns all check and null constraint information for all tables in an
array reference.  The constraint attributes include: table name,
constraint name, column name, constraint type (NULL/CHECK), constraint
criteria.

Usage: my $allConstraintInfo = $$schemaObjRef->allConstraintInfo;

       foreach my $row (@{$allConstraintInfo}){

          my ($table_name, $constraint_name, $column_name, $type, $criteria) = @{$row};

          ...........

=head2 getSequences

Returns all Oracle-generated sequence information for a table in an
array reference.  The sequence attributes include: column name,
sequence name.

Oracle sequences are not associated with a particular table in the
data dictionary.  Consequently, this information is stored in a flat
file for each database.  It would probably be better if this data was
stored in the database itself.

Usage: my $sequenceInfo = $$schemaObjRef->sequenceInfo;

       foreach my $row (@{$sequenceInfo}){

          my ($column_name, $sequence_name) = @{$row};

          ...........

=head2 getAllSequences

Returns all Oracle-generated sequence information for all tables in an
array reference.  The sequence attributes include: table name, column
name, sequence name.

Oracle sequences are not associated with a particular table in the
data dictionary.  Consequently, this information is stored in a flat
file for each database.  It would probably be better if this data was
stored in the database itself.

Usage: my $allSequenceInfo = $$schemaObjRef->allSequenceInfo;

       foreach my $row (@{$allSequenceInfo}){

          my ($table_name, $column_name, $sequence_name) = @{$row};

          ...........
=cut