#!/usr/local/bin/perl

# lb2510.pl
# desc: print to file table and column schema for Voyager database
# wordsettings: MonoSpac821DL 9pt portrait, 1" T&B, .9" L&R margins
# end_autoz

use DBI;

$pagesize = 60;
$firstheading = 1;
@tablename = ();


### connect to database
open(pwfile, "sqlpw.cfg") or die "Cannot open password file";
$pw = <pwfile>;
close(pwfile);
chomp $pw;
($user, $pw) = split /\//, $pw;
$dbh = DBI->connect('DBI:Oracle:host=voyager.library.wmich.edu;sid=LIBR',
                    $user, $pw)
  or die "connecting: $DBI::errstr";

# get distinct list of table names
$sqlquery = sprintf("select table_name from user_tab_columns order by table_name");
$sth = $dbh->prepare($sqlquery) or die "preparing query statement";
$rc = $sth->execute;

$oldtablename = "";
$numtables = 0;
$loopctr = 0;
while (@row = $sth->fetchrow_array)
{
  if ($row[0] ne $oldtablename)
  {
    $numtables++;
    $tablename[$numtables] = $row[0];
    $oldtablename = $row[0];
  }
  $loopctr++;
}
$sth->finish;


# get number of columns per table
$idx = 1;
while ($idx <= $numtables)
{
  $sqlquery = sprintf("select count(*) from user_tab_columns
                       where table_name = '%s'", $tablename[$idx]);
  $sth = $dbh->prepare($sqlquery) or die "preparing query statement";
  $rc = $sth->execute;
  @row = $sth->fetchrow_array;
  $tablecolumns[$idx] = $row[0];
  $idx++;
}
$sth->finish;


# generate the report
$fileout = ">lb2510.rpt";
$fopen = sprintf("Cannot open %s for output\n", $fileout);
open(fileout, $fileout) or die $fopen;
doheading();
$idx1 = 1;
while ($idx1 <= $numtables)
{
  $sqlquery = sprintf("select table_name,
                              lower(column_name),
                              lower(data_type),
                              nvl(data_precision, data_length),
                              data_scale,
                              nullable
                       from user_tab_columns
                       where table_name = '%s'
                       order by column_name", $tablename[$idx1]);
  $sth = $dbh->prepare($sqlquery) or die "preparing query statement";
  $rc = $sth->execute;

# get data from query, mark rows where the Oracle column name ends in "_id"
#  and store in array
  $numrows = 0;
  while (@row = $sth->fetchrow_array)
  {
    $isid = 0;
    $col = $row[1];
    if (substr($col, length($col)-3, 3) eq '_id')
    {
      $isid = 1;
    }
    $rowstr = join '|', $row[0], $row[1], $row[2], $row[3], $row[4], $row[5], $isid;
    @tbldata[$numrows] = $rowstr;
    $numrows++;
  }

# output data from temporary array
  $firstone = 1;
  # output "_id" columns first
  $idx2 = 0;
  while ($idx2 < $numrows)
  {
    @row = split /\|/, $tbldata[$idx2];
    # if fieldname ends with "_id"
    if ($row[6] == 1)
    {
      if ($firstone)
      {
        $firstone = 0;
        if (($linectr + $tablecolumns[$idx1]) > $pagesize) {doheading();}
        printf fileout ("%-30.30s %-30.30s %-8.8s %6.6s %3.3s %1s\n",
                        $row[0], $row[1], $row[2], $row[3], $row[4], $row[5]);
      }
      else
      {
        printf fileout ("%-30.30s %-30.30s %-8.8s %6.6s %3.3s %1s\n",
                        " ", $row[1], $row[2], $row[3], $row[4], $row[5]);
      }
      $linectr++;
    }
    $idx2++;
  }
  # now output non "_id" columns
  $idx2 = 0;
  while ($idx2 < $numrows)
  {
    @row = split /\|/, $tbldata[$idx2];
    # if fieldname does not end with "_id"
    if ($row[6] != 1)
    {
      if ($firstone)
      {
        $firstone = 0;
        if (($linectr + $tablecolumns[$idx1]) > $pagesize) {doheading();}
        printf fileout ("%-30.30s %-30.30s %-8.8s %6.6s %3.3s %1s\n",
                        $row[0], $row[1], $row[2], $row[3], $row[4], $row[5]);
      }
      else
      {
        printf fileout ("%-30.30s %-30.30s %-8.8s %6.6s %3.3s %1s\n",
                        " ", $row[1], $row[2], $row[3], $row[4], $row[5]);
      }
      $linectr++;
    }
    $idx2++;
  }
  if ($linectr < ($pagesize-3))
  {
    print fileout "\n";
    $linectr++;
  }
  $idx1++;
}
close(fileout);


### clean up at end
$sth->finish;
$dbh->disconnect;

sub doheading
{
  if (not $firstheading) {print fileout "\f";}
  else {$firstheading = 0;}
  printf fileout ("Table Name                     Column                         Type     Length Dec Null?\n");
  printf fileout ("%s %s %s %s %s %s\n\n", '-'x30, '-'x30, '-'x8, '-'x6, '-'x3, '-'x5);
  $linectr = 3;
}