#!/usr/local/bin/perl alarm (300); # File name: db_util.pl # dbEase version 1.03 # MODIFICATIONS: Version upgrade to dbEase version 1.03 # # MODIFICATIONS to db_util.pl # 1. Simple text edit (09/29/98) # # 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 provide interfaces for adding data to, modifying the data of, ## or deleting data from a table, as well as exporting to a comma delimited ## file of a table or deleting an entire table along with the tables Search ## and Result pages, if they exist. ## ## It excepts an array of parameters. the main routine handles the following ## parameters or the lack of these parameters : ## param 'tableName' ## param 'util' with values of : ## ADD, MODIFY, DELETE, EXPORT, ## EXPORT Table, DELETE Table, or VIEW. ## param 'view_what' with values of : ## View Definitions or View Data ## param 'CANCEL' ## the subroutines expect and pass a variety of parameters as well as the ## params listed above, and will be describe in the subroutines. ### use CGI; $query = new CGI; my $conf_file = "./dbEase.config"; require $conf_file; ################################################################### #---------------------------- MAIN -------------------------------# # # Conditionals to decides whether we're viewing : # - a 'text-box' from the Edit Search page screen of the # script_util.pl # - or an actual .._Search_HTML.page being called live ################################################################### ## if a table is not specified provide an interface to select one. if (!($query->param('tableName'))) { &print_form(); } ## if param 'util' is ADD goto the add screen elsif ($query->param('util') eq 'ADD') { &add(); } ## if param 'util' is MODIFY goto the modify screen elsif ($query->param('util') eq 'MODIFY') { &modify(); } ## if param 'util' is DELETE goto the delete screen elsif ($query->param('util') eq 'DELETE') { &delet(); } ## if param 'util' is EXPORT (calling from the navigation menu or ## from the delete table screen) ## goto the add screen. elsif ($query->param('util') eq 'EXPORT') { &export(); } ## if param 'view_what' is View Definitions ## (called from print_form if param 'util' is VIEW) ## goto the view definitions screen elsif ($query->param('view_what') eq 'View Definitions') { &view_defs(); } ## if param 'view_what' is View Data ## (called from print_form if param 'util' is VIEW) ## goto the view data screen elsif ($query->param('view_what') eq 'View Data') { &view_data(); } ## if param 'util' is DELETE Table goto the add screen elsif ($query->param('util') eq 'DELETE TABLE' || $query->param('drop')) { &drop_table(); } ## if param 'CANCEL' exist then it was called from ## the warning screen of the delete table screen. elsif ($query->param('CANCEL')) { &cancel(); } ## none of expexted parameters were pass else { print $query->header; print $query->start_html(-title=>"Error", -bgcolor=>"#ffffff"); print "
", "A crucial parameter was not sent.

Try again."; print $query->end_html; exit; } ################################################################### # this routine gives the user a list of tables to choose from. # ################################################################### sub print_form { my %header_ends = ("ADD" => ' Records to', "MODIFY" => ' Records in', "DELETE" => ' Records from', "EXPORT" => ' Records from'); print $query->header; print $query->start_html(-title=>"ADMIN", -bgcolor=>"#ffffff"); ## need to hit the data base for a list of the tables. my $dbh = &dbConnect(); my $UTIL = $query->param('util'); # $UTIL is the desired action (util) ## ie (add, modify, delete data ## export a table or delete a table) print ""; print "

param('util'), "\" target=\"HELP\">HELP

"; print "
", "$UTIL$header_ends{$UTIL}

"; my @arr = $dbh->listtables; # @arr is an array of the tables in the db ## to be used in the popup menu. ## Added 20050404 by brettm to remove ticks around db namea ## This was caused by use of newer mysql perl module. foreach (@arr){ s/^\`//g; s/\`$//g; } @arr = sort { lc($a) cmp lc($b) }@arr; print $query->start_form(-method=>GET, -action=>'./db_util.pl'); print $query->hidden('util',"$UTIL"); # pass along the action (util) print "", $query->popup_menu(-name=>"tableName", -"values"=>[@arr]); ## if the action (util) is VIEW need to give user choice ## of data or definitions. if ($query->param('util') eq 'VIEW') { print "
"; print $query->popup_menu(-name=>"view_what", -"values"=>['View Definitions', 'View Data']); print " What to view.
\n"; } ## if the action (util) is EXPORT need to give user choice ## of the delimiter for the fields. elsif ($query->param('util') eq 'EXPORT') { my %delimit = ("," => 'Comma ,', "\t" => 'Tab ', "|" => 'Pipe |'); print "

", "
"; print "Save as a "; print $query->popup_menu(-name=>"delimiter", -"values"=>[keys %delimit], -default=>',', -labels=>\%delimit); print "", "delimited File
\n"; } print "


", $query->submit(-name=>"SUBMIT"), "\n"; print $query->end_form; print $query->end_html; exit; } ################################################################### # this routine gives the user an interface to either add x rows of # data or add a comma delimited file. # the form expects param 'tableName' and passes : # param 'which' # param 'tableName' # param 'action' # and either param 'numRows' # (No rows to be manually inputed) # or param 'export' # (Name of the camma delimited # file to fill the table). # ################################################################### sub add { my %delimit = (',' => 'comma ,', '\t' => 'Tab ', '|' => 'Pipe |'); print $query->header; print $query->start_html(-title=>"ADD", -bgcolor=>"#ffffff"); print ""; print "

HELP

"; my $table = $query->param('tableName'); # table name print "
", "ADD / IMPORT Data
\n"; if ($query->param('setup') eq 'follow') { print "
", "After you have finished populating the table, ", "you can use the menu to the left to edit any further portions.", "
"; } ## need to use multipart_form for a filefield to work. print $query->start_multipart_form(-action=>'./idm.pl'), "\n"; print $query->hidden('tableName','$table'); # pass along table name print $query->hidden('action','insert'); # pass action, so idm.pl knows ## what to do ## what are we doing manual entry or adding a file. my @radio_butts = $query->radio_group(-name=>'which', -"values"=>['rows','file'], -default=>'none', -nolabels=>"true"); ## file to export print "
", "
"; print $radio_butts[1], " Click here to IMPORT", " an entire File
"; print "\n

Enter the name File to append or use the Browse Button :

\n"; print $query->filefield(-name=>'export', -size=>30), "

"; print "Field Separator "; print $query->popup_menu(-name=>"delimiter", -"values"=>[keys %delimit], -default=>',', -labels=>\%delimit); print "


\n"; ## manual records to enter print "\n
", $radio_butts[0], "Click here to ADD", " a specified number of records", "
\n"; print "Number of rows to add : "; print $query->textfield(-name=>'numRows', -size=>4); # No of rows to add print "\n



\n"; print $query->submit(-name=>"SUBMIT REQUEST"), "\n"; print $query->end_form , "
"; print $query->end_html; exit; } ################################################################### # this routine gives the user an interface to delete data from a # table. # the form calls idm.pl expects and passes param 'which' # param 'tableName' # param 'action' # param 'query_setup' # param 'refine' # and either param 'numRows' # (No rows to be manually inputed) # or param 'export' # (Name of the camma delimited # file to fill the table). # ################################################################### sub delet { my $table = $query->param('tableName'); print $query->header; print $query->start_html(-title=>"DELETE", -bgcolor=>"#ffffff"); print ""; print "

param('util'), "\" target=\"HELP\">HELP

"; ## hit data base for info about fields (ie field names, if char, int or real) my $dbh = &dbConnect(); my $sth = $dbh->listfields($table); my $noCols = $sth->numfields - 1; my @colnames = $sth->name; my @coltype = $sth->type; print "
", "Delete Records from Table", " $table
\n"; print $query->start_form(-action=>'./idm.pl'), "\n"; print $query->hidden('tableName','$table'); # pass table name print $query->hidden('action','delete'); # pass action, so idm.pl knows ## what to do print $query->hidden('query_setup','select_where'); # this lets idm.pl ## know to perform a ## SELECT WHERE ... print "\n"; for ($i=0; $i < $noCols; $i++) { print "\n\n"; } else { print $query->textfield(-name=>"$colnames[$i]", -size=>15), ""; } } print "
", "The $colnames[$i] Field", ""; if ($type{$coltype[$i]} eq 'char') { ## if char give only two possible matching options print $query->popup_menu(-name=>"$colnames[$i],match", -"values"=>['exact','like'], -labels=>{'exact'=>'Exactly Matches ', 'like'=>' Contains '}), "\n"; } else { ## else it real or int give four possible matching options print $query->popup_menu(-name=>"$colnames[$i],match", -"values"=>['=','<>','<=','>='], -labels=>{'='=>'is Equal to', '<>'=>'is Not Equal to', '<='=>'is Less than', '>='=>'is Greater than'}), "\n"; } if ($query->param('refine')) { print $query->textfield(-name=>"$colnames[$i]", -value=>"$query->param('$colnames[$i]')", -size=>15), "
"; print "

", "All conditions are connected with the 'logical AND'.
", "Leaving a field blank will invoke a search on all elements in that field.", "
The \"is Less than\" and \"is Greater than\" options for fields ", "of types
\'int\' and \'real\' are inclusive.
", "If using the \'DELETE INDISCRIMINATELY!\' option,", "fields of type\'char\' will default to the \'Exactly Matches\' ", "option. Fields of types \'int\' and \'real\' will match as specified.", "

"; ## show and select or just delete option my @radio_butts = $query->radio_group(-name=>"show_select", -"values"=>['show','delete'], -nolabels=>"true"); print $radio_butts[0], " SHOW RESULTS AND SELECT.       ", $radio_butts[1], " DELETE INDISCRIMINATELY!

"; print $query->submit(-name=>'SUBMIT'), "

"; print $query->end_form ; print $query->end_html; exit; } ############################################################# # # modify expexts the tableName param and passes to idm.pl # tableName, action, query_setup # # # modify passes tableName, action, query_setup ############################################################# sub modify { print $query->header; print $query->start_html(-title=>"MODIFY", -bgcolor=>"#ffffff"); print ""; print "

param('util'), "\" target=\"HELP\">HELP

"; ##db stuff my $dbh = &dbConnect(); my $table = $query->param('tableName'); my $sth = $dbh->listfields($table); my $noCols = $sth->numfields - 1; my @colnames = $sth->name; my @coltype = $sth->type; print "
", "Modify Records in Table
$table
\n"; print $query->start_form(-action=>'./idm.pl'), "\n"; print $query->hidden('tableName','$table'); print $query->hidden('action','modify'); print $query->hidden('query_setup','select_where'); # so idm.pl knows to ## # create a query print "WHEN :
\n"; print "\n"; ## give textboxes and matching options for ($i=0; $i < $noCols; $i++) { print "\n\n"; } else { print $query->textfield(-name=>"$colnames[$i]", -size=>15), ""; } } print "
", "The $colnames[$i] Field ", ""; if ($type{$coltype[$i]} eq 'char') { print $query->popup_menu(-name=>"$colnames[$i],match", -"values"=>['exact','like'], -labels=>{'exact'=>'Exactly Matches ', 'like'=>' Contains '}), "\n"; } else { print $query->popup_menu(-name=>"$colnames[$i],match", -"values"=>['=','<>','<=','>='], -labels=>{'='=>'is Equal to', '<>'=>'is Not Equal to', '<='=>'is Less than', '>='=>'is Greater than'}), "\n"; } if ($query->param('refine') eq 'refine') { print $query->textfield(-name=>"$colnames[$i]", -value=>"$query->param('$colnames[$i]')", -size=>15), "
"; print "

", "All conditions are connected with the 'logical AND'.
", "Leaving a field blank will invoke a search on all elements in that field.", "
The \"is Less than\" and \"is Greater than\" options for fields of ", "types
\'int\' and \'real\' are inclusive.

"; print $query->submit(-name=>'SUBMIT'), "

"; print $query->end_form ; print $query->end_html; exit; } ############################################################# # # export expects just the tableName parameter # (the table to be exported) # ############################################################# sub export { if (!(-e $DIR)) { ## if exports is not there make it mkdir $DIR, 0774; chmod 0774, $DIR; mkdir $EXPORTDIR, 0774; chmod 0774, $EXPORTDIR; } elsif (!(-e $EXPORTDIR)) { $DIR = $EXPORTDIR; ## if exports is not there make it mkdir $DIR, 0774; chmod 0774, $DIR; } else { ## keep the exports dir clean opendir (EXPORTS, "$EXPORTDIR") or die "can't open directory"; @files = readdir (EXPORTS); closedir (EXPORTS); ## delete .xyz files since the dbEase should be the only one put .xyz files ## in the exports dir. foreach $f(@files) { if ($f =~ /.xyz/g) { $f = $EXPORTDIR . $f; push(@xyzfiles,$f) } } unlink @xyzfiles; } ## get a varying identifier to appended to table name ## (this way don't have to worry about refreshing the screen) $time = time % 100; ## db stuff my $dbh = &dbConnect(); my $table = $query->param('tableName'); my $sth = $dbh->Query("select * from $table") or die print $query->header(), $query->start_html(-title=>"ERROR", -bgcolor=>"#ffffff"), "AN ERROR HAS OCCURRED!

Can not perform the ", "Select Statement:
SELECT * from $table

DB Error : ", &dbERR(), "
(db_util.pl)\n"; my $noCols = $sth->numfields - 1; my $noRows = $sth->numrows; my $seperator = $query->param('delimiter'); ## make a unique filename my $file = join '', $table ,$time, ".xyz"; ## write to file open (EFILE, "+>$EXPORTDIR$file") || die print $query->header, $query->start_html(-title=>"$table 's Definitions", -bgcolor=>"#ffffff"), "can't open $EXPORTDIR$file :: $!"; while (@data = $sth->fetchrow ) { print EFILE $data[0]; for ($i = 1; $i < $noCols; $i++) { if ($seperator ne '\t') { print EFILE $seperator, $data[$i]; } else { print EFILE "\t", $data[$i]; } } print EFILE "\n"; } close(EFILE); ## making this .xyz file the location for the browser will force the browser ## (who does not know what to do with a .xyz file) to save the file. print $query->header(-location=>"$EXPORTURL$file"); exit; } ############################################################# # # view_defs expects the tableName parameter as well as the # view_what = View Definitions parameter to get to this subroutine # ############################################################# sub view_defs { my $table = $query->param('tableName'); print $query->header; print $query->start_html(-title=>"$table 's Definitions", -bgcolor=>"#ffffff"); print ""; print "

HELP

"; ## db stuff my $dbh = &dbConnect(); print "
$table 's", " Definitions

"; my $sth = $dbh->listfields($table); my $noCols = $sth->numfields; my @colnames = $sth->name; my @coltype = $sth->type; my @collen = $sth->length; my @colopt = $sth->isnotnull; my ($i, $opt); my @fontcolor; print "
\n\n"; print "\n"; print "\n"; print "\n"; for ($i=0; $i < $noCols; $i++) { if ($colnames[$i] ne 'dbEase_key') { if ($colopt[$i] eq '1') { $opt= "Required"; $fontcolor[$i] = 'green'; } else { $opt= "Optional"; $fontcolor[$i] = 'black'; } print ""; print ""; print ""; if ($type{$coltype[$i]} ne 'char') { print ""; } else { print ""; } } } print "
Field NameField TypeField Length
", "", "$opt ", " ", "$colnames[$i] ", "", " $type{$coltype[$i]} ", "", "  
", " ", "$collen[$i]
"; print "
NOTE: Once a Table has been defined/created", " it CANNOT be changed. To change a previously defined Table you MUST ", "re-define and re-create the Table.
"; print $query->end_html; exit; } ############################################################# # # view_data expects the tableName parameter as well as the # view_what = View Data parameter to get to this subroutine # ############################################################# sub view_data { print $query->header; print $query->start_html(-title=>"$table 's Data", -bgcolor=>"#ffffff"); print ""; print "

HELP

"; my $table = $query->param('tableName'); ## db stuff my $dbh = &dbConnect(); print "
$table 's Data
"; my $sth = $dbh->Query("SELECT * from $table" ) or die print "
AN ERROR HAS OCCURRED!

", "Can not perform the Select Statement:
SELECT * from $table", "

DB Error : ", &dbERR(), "
(db_util.pl)
\n"; my $noCols = $sth->numfields; my $noRows = $sth->numrows; my $maxRows = 50; ## number of records to show per screen my $seek = 0; my @colnames = $sth->name; my @coltype = $sth->type; my @colopt = $sth->isnotnull; my ($i, $c, $opt); my (@fontcolor, @row); if ($noRows == 0) { print "
There is NO DATA in $table
"; print $query->end_html; exit; } if ($noRows <= $maxRows) { $maxRows = $noRows; } if ($query->param('start_at')) { $seek = $query->param('start_at'); $sth->dataseek($seek); } print "
There are ", $noRows, " Records in ", $table ,"."; print "
Here are Records ", $seek+1, " through ", &min($noRows,$seek+$maxRows), " .
"; print "
\n\n"; for ($i=0; $i < $noCols; $i++) { if ($colnames[$i] ne 'dbEase_key') { if ($colopt[$i] eq '1') { $opt= "Required"; $fontcolor[$i] = 'green'; } else { $opt= "Optional"; $fontcolor[$i] = 'black'; } print ""; } } print ""; for ($i=0; $i < $noCols; $i++) { if ($colnames[$i] ne 'dbEase_key') { print ""; } } print ""; for ($c=$seek; $c < $noRows && $c < $maxRows+$seek; $c++) { @row = $sth->fetchrow; print ""; for ($i=0; $i < $noCols; $i++) { if ($colnames[$i] ne 'dbEase_key') { if ($row[$i] eq '') { $row[$i] = "   "; } if ($type{$coltype[$i]} eq 'real') { if ($row[$i] =~ /\d/) { $row[$i] = sprintf "%32.2f", $row[$i]; } } print ""; } } print ""; } print "
", "", "$opt
", "", "$colnames[$i]
", "", " $row[$i]
"; ## if more records than $maxRows give a link to the next set if ($noRows > $maxRows+$seek) { print "
", "View Next Set of Records"; } print $query->end_html; exit; } ############################################################# # # drop_table expects the tableName parameter # if the 'drop' parameter exists than the user has already # been asked and is sure they want to drop the table # otherwise promt for verification # ############################################################# sub drop_table { my ($table) = $query->param('tableName'); print $query->header; print $query->start_html(-title=>"Drop Table", -bgcolor=>"#ffffff"); print ""; print "

HELP

"; ## db stuff my $db = &dbConnect(); #---- # user already asked if they are sure if ($query->param('drop') eq "DELETE $table") { # to drop the table $db->Query("DROP table $table") or die print "AN ERROR HAS OCCURRED!

", "Unable to drop Table \'", $table, "\' using the Drop statement:
", "DROP table $table

DB Error : ", &dbERR(), "
(db_util.pl)\n"; ##--- ## Remove the search and result pages associated with this table my $search_page = join '', $DIR, $table, "_Search_HTML.page"; my $result_page = join '', $DIR, $table, "_Result_HTML.page"; my @delfiles = ($search_page, $result_page); unlink @delfiles; #----- # to print out a message to the user print "

Table, ", "$table, has been DELETED!
", "As well as it's Search and Result Pages!"; } ## prompt user for verification else { print "

", "Are You Sure you want to DELETE ", "$table ?
"; print "
  • All data in $table will be LOST", "
  • $table 's Search and Result Pages will be DELETED
"; print "
\n"; print "\n"; print $query->end_form; print "\n"; print $query->end_form, "
" , $query->start_form(-method=>GET, -action=>'./db_util.pl'); print $query->hidden('tableName','$table'); print $query->submit(-name=>'drop', value=>"DELETE $table"), "\n", $query->start_form(-method=>GET, -action=>'./db_util.pl'); print $query->hidden('tableName','$table'); print $query->submit(-name=>"CANCEL"), "\n
\n"; } print $query->end_html; exit; } ############################################################# # # cancel expect the tableName parameter as well as the # 'CANCEL parameter to get to this sub. #just tell user that the table has NOT been dropped. # ############################################################# sub cancel { my ($table) = $query->param('tableName'); print $query->header; print $query->start_html(-title=>"CANCEL DROP", -bgcolor=>"#ffffff"); print ""; print "

HELP

"; print "

", "$table has NOT been Deleted!"; print $query->end_html; exit; } #----- # End the program