#!/usr/local/bin/perl
alarm (300);
# File name: sqlify.pl
# dbEase version 1.03
# MODIFICATIONS: Version upgrade to dbEase version 1.03
#
# Files affected as of (12/04/98) that instigated a version upgrade :
# 1. go_get.pl last modified (12/04/98)
# 2. idm.pl last modified (09/29/98)
# 3. db_util.pl last modified (09/29/98)
# 4. script_util.pl last modified (09/29/98)
# 5. search.pl last modified (12/01/98)
# Written by Kenneth Hubert ("production unit 6")
# Copyright 1998, Stratum New Media, Inc. All rights reserved
##
#
#PROPRIETARY NOTICE
#
# (C) 1998 Stratum New Media, Inc. All rights reserved. Any unauthorized use of
# this unpublished work (including reproduction) is prohibited.
# Stratum New Media owns all rights to this work and intends to maintain this
# information as proprietary and confidential. In the event of any inadvertent
# or deliberate and unauthorized use or publication of such work,
# Stratum New Media, Inc. will enforce any and all of its rights under all
# applicable laws.
#
# Stratum New Media provides this report for the sole purpose of permitting the
# recipient, a representative of InfiNet, to evaluate the proposal submitted.
# In consideration of receiving this report, the recipient and any other agents
# and representatives of InfiNet, agree to maintain the information in this
# report confidential, and refrain from disclosing such information to any
# third party or person outside the group directly responsible for this
# evaluation.
#
#
##############################################################
# Requires Perl 5.xx and CGI.pm
##############################################################
##
## This script adds the unique "dbEase_key" to any existing table as to insure
## the Sqlators downward capablities. It needs the 'Update Table' parameter
## to actually perform the update. (ie it prompts the user to go ahead with
## the update.
##
use CGI;
$query = new CGI;
my $conf_file = "./dbEase.config";
require $conf_file;
my @types = ("??", "int", "char", "real");
my @opts = ("", "not null", "primary key");
print $query->header;
print $query->start_html(-title=>"UPDATE Old Tables",
-bgcolor=>"#ffffff");
## help link
print "";
print "";
print "HELP
";
## if the user didn't already agree ask 'em
if (!($query->param('go ahead'))) {
print "Updating existing Tables in your data base simple adds ",
"a dbEase administration key to your tables.",
"
This action is only necessary if dbEase is connecting to a database where Tables already exist.
";
print $query->start_form(action=>'./sqlify.pl'),
$query->hidden('go ahead', 'go ahead'),
$query->submit(name=>'Update Tables');
print $query->end_form, $query->end_html;
exit;
}
## need to hit the data base for a list of the tables.
my $dbh = &dbConnect();
my @arr = $dbh->listtables; # @arr is an array of the tables in the db
foreach $each(@arr) {
my $sth = $dbh->Query("select * from $each" ) or
die print "AN ERROR HAS OCCURRED!
Can not perform the ",
"Select statement:
select * from $each
DB Error : ",
&dbERR(), "
(sqlify.pl)\n";
my $noCols = $sth->numfields;
my @colnames = $sth->name;
## checks to see if the last column is the dbEase admin key.
if ($colnames[$noCols-1] ne "dbEase_key") {
print "
Table $each needs to be Updateded!
";
my $NumRows = $sth->numrows;
my @colopt = $sth->isnotnull;
my @coltype = $sth->type;
my @collen = $sth->length;
print " Copying Column definitions for Table $each...
";
if ($coltype[0] eq'2') {
$coldefs =
"$colnames[0] $types[$coltype[0]]($collen[0]) $opts[$colopt[0]]";
}
else {
$coldefs = "$colnames[0] $types[$coltype[0]] $opts[$colopt[0]]";
}
for ($i = 1; $i < $noCols; $i++) {
if ($coltype[$i] eq'2') {
$coldefs =
"$coldefs, $colnames[$i] $types[$coltype[$i]]($collen[$i]) $opts[$colopt[$i]]";
}
else {
$coldefs =
"$coldefs, $colnames[$i] $types[$coltype[$i]] $opts[$colopt[$i]]";
}
}
$coldefs = "$coldefs, dbEase_key real not null";
print " Copying data in Table $each...
";
for ($i = 0; $i < $NumRows; $i++ ) {
@temp = $sth->fetchrow;
if ($coltype[0] eq'2') {
## if field is a char need to add single qoutes
$temp[0] =~ s/\\/\\\\/g;
$temp[0] =~ s/\'/\\\'/g;
$temp[0] = "'$temp[0]'";
}
$temp2 = $temp[0];
for ($c = 1; $c <= $#temp; $c++) {
if ($coltype[$c] eq'2') {
## if field is a char need to add single qoutes
$temp[$c] =~ s/\\/\\\\/g;
$temp[$c] =~ s/\'/\\\'/g;
$temp[$c] = "'$temp[$c]'"; ;
}
$temp2 = join ', ', $temp2, $temp[$c];
}
$data[$i] = join ', ', $temp2, $i+1;
}
# to drop the table
$dbh->Query("DROP table $each") or
die print"AN ERROR HAS OCCURRED!
",
"Could not drop the Table \'", $each, "\'
DB Error : ",
&dbERR(), "
(sqlify.pl)\n";
##---
print "Recreating Table $each ...
";
#-----
# create the table
my $q = qq|
CREATE table $each ($coldefs)
|;
$sth = $dbh->Query($q) or
die print "AN ERROR HAS OCCURRED!
",
"Unable to create Table \'", $$each,
"\' using the Create statement :", "
$q
DB Error : ",
&dbERR(), "
(sqlify.pl)\n";
#-----
print "Repopulating Table $each ...
";
for ($i = 0; $i < $NumRows; $i++ ) {
$dbh->Query("INSERT INTO $each VALUES($data[$i])") or
die print "AN ERROR HAS OCCURRED!
",
"Unable to insert into Table \'", $each,
"\' using the Insert statement :
",
"INSERT INTO $each VALUES($data[$i])
DB Error : ",
&dbERR(), "
(sqlify.pl)\n";
}
}
else {
print "
Table $each is OK!
";
}
}
print "
All Tables are now in dbEase(SM)) Format.",
"
Use the menu to your left to navigate through dbEase(SM)).";
print $query->end_html;
exit;