#!/usr/local/bin/perl alarm (300); # File name: tabdef.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 is an interface for the # 1. creation of the table definition, # 2. verification of the table definition, and # 3. creation of the table itself . # ## When called initially it expects one parameter $noCols, # which is obviously the number of columns for the table # and produces $tableName and $colDefs. ## When called to change the fields it expects # $tableName $noCols, and $colDefs. ## When called to verify the table it expects # an assortment of parameters $tableName, $noCols, and # an array of paramaters in the form # colName[1..$noCols], colType[1..$noCols], and colLen[1..$noCols] # which are arrays of the field names, field types, and field lengths, # respectively. This subroutine produces $colDefs with the form # name[1] type[1](length[1]) status[1] ... # name[$noCols] type[$noCols](length[$noCols]) status[$noCols] # and passes $tableName, $noCols, and $colDefs. ## When called to actually create the table it expects # $tableName, $noCols, and $colDefs. use CGI; my ($query) = new CGI; my $conf_file = "./dbEase.config"; require $conf_file; ################################################################### #---------------------------- MAIN -------------------------------# # # These conditionals decides whether we're creating or verifying a # table, and ensures that the $noCols is not 0 # ################################################################### print $query->header; if ($query->param('newtable') eq 'start') { $new = 1; &print_prompt(); } elsif ($query->param('verify')) { &verify(); # checks that the defs. are legal } elsif ($query->param('CREATE')) { &create(); #actually creates the table } elsif ( !($query->param('noCols')) || $query->param('noCols') == 0 || $query->param('noCols') =~ /\D/) { $new = 0; &print_prompt(); } &do_work(); ################################################################### #-------------------------- END MAIN -----------------------------# ################################################################### ################################################################### # If number of columns specified is empty, or number is illegal # Prompt for the number again ################################################################### sub print_prompt { print $query->start_html(-title=>"Create a Table", -bgcolor=>"FFFFFF"); print ""; print "

", "HELP

"; if ($new == 1) { print "
Create a new Table"; } else { print "
To Create A Table :", "
    "; print "", "
  • You must specify the number of fields in the table.", "
  • The specified number of fields for your table must be greater than ", "zero (ie. a positive integer).

"; } print $query->start_form(-action=>'./tabdef.pl'); print "", "Number of fields (ie columns) in the table : ", $query->textfield(-name=>'noCols', size=>2, -maxlength=>2); print "


", $query->submit(-name=>'Submit'); print $query->end_form; print $query->end_html; exit; } #################################################################### # Entered with appropriate number of columns specified # # Obtain all the table definition information #################################################################### sub do_work { print $query->start_html(-title=>"Table Definition", -bgcolor=>"FFFFFF"); print ""; print "

", "HELP

"; my $rows = $query->param('noCols'); # number of fields my $rownum = 1; # counter for rows my $fieldtype = 'char'; # data type of field ('char', 'int', 'real') my (@colDefs, $fieldstatus, $fieldname, $fieldlen, $checked); ## @colDefs -> an array to hold the table definitions passed if ## changing table defs. ## (Only needed if returning from verification) ## $fieldstatus -> Is the field required? ('not null' or ''). ## $fieldname -> duh! ## $fieldlen -> max length of field (only needed if $fieldtype is 'char') ## $checked -> Only needed if returning from verification ## sets check-box to '-CHECKED' or '' print "
", "Submit the Definition to Create A Table
"; #--- # Print messages about proper information for a table definition if ($Tnoname_err != 0 || $Cnoname_err != 0) { $no_name = "red"; } else { $no_name = "black"; } if ($Tname_err != 0 || $Cname_err != 0) { $spaces = "red"; } else { $spaces = "black"; } if ($Tnot_unique != 0) { $Tnot_unique = "red"; } else { $Tnot_unique = "black"; } if ($Cnot_unique != 0) { $Cnot_unique = "red"; } else { $Cnot_unique = "black"; } ## a table of bad characters for user NOT to use and other info print"
    ", "
  • ", "The Table Name and all Field names must not contain any of the following ", ":
    ", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "
    ", "spaces", "~", "`", "!", "#", "\$", "%", "^", "&", "*", "(", ")", "-", "+", "=", "{
    ", "}", "[", "]", "\\", "|", ";", ":", "\"", "'", ",", ".", "?", "\<", "\>", "?", "/
    ", "You may however use names with underscores '_', such as     ", "first_name,favorite_car, etc.
    • ", "
  • Tables and their Fields must not be named ", "after $SQL ", "reserved words.", "
  • ", "Tables and their Fields must have names. ", "
  • ", "Field names must be unique within a table.", "
  • Table names must be unique within a database.", "
    • ", "
  • A column must be in one of the following three types:", " char, int, and real.
  • ", "The type char must be specified with an integer length.", "  The types int and real", " should not be specified with a length.
  • ", "A column may be not null or unspecified.
    • "; ###### ## tell user errors if any if ($Tname_err != 0) { print "

    Your Table ", $query->param('tableName'), " contains spaces.
    Spaces are not permitted in the Table Name.", "
    You should replace any space with an underscores \'_\'.", "
    "; } if ($Tnoname_err != 0) { print "

    ", "You left Table Name blank.
    You must specify the Table Name.", "
    "; } if ($Tnot_unique != 0) { print "

    A Table with the name ", $query->param('tableName'), " already exists in the data base.
    ", "Table Names must be unique within a data base.
    ", "Please rename your Table.
    "; } if ($Cname_err != 0) { print "

    Field(s) ", $Cnum_err, " contain(s) spaces in the Field Name(s).
    ", "Spaces are not permitted in the Field Names.
    ", "You should replace any space with an underscores \'_\'.", "
    "; } if ($Cnoname_err != 0) { print "

    The Field Name(s) in Field(s) ", $Cnoname_err, " were left blank.", "
    You must specify the Field Name(s).
    "; } if ($Cnot_unique != 0) { print "

    The Field(s) ", $Cnum_unique, " have the same Name.
    Field Names must be unique within a Table.", "
    Please rename at least one Field in each pair.
    "; } ######## #--- # Provide the form for the table definition print $query->start_form(-action=>'./tabdef.pl'); print $query->hidden(-name=>'verify', -value=>'verify'); # # Give a textfield for inputing the table name print " Table name : ", $query->textfield(-name=>'tableName', -size=>30); print "

    "; # # Provide an HTML table for inputting the definitions for each field # Check if changeing definitions or setting up for first time. if ($query->param('CHANGE')) { $name = $query->param('colDefs'); @colDefs = split ',' , $name; } print "", "", "", "", ""; # Generate all the HTML table for the table field definitions while ($rows >= $rownum) { if ($query->param('CHANGE')) { ($fieldname, $fieldtype, $fieldstatus) = split ' ' , $colDefs[$rownum - 1], 3; ## need to get rid of '(' and ')' ## format to pass to msql db is : name type(len) status ## this is only done if returning from verification after the ## query sting has already been set up (ie change eq CHANGE) ($fieldtype, $fieldlen) = split /\(/ , $fieldtype, 2; $fieldlen =~ s/\)//e; if ( $fieldstatus eq 'not null') { $checked = "yes"; } else { $checked = "no"; } } print ""; print ""; print ""; print ""; } else { print $query->checkbox(-label=>'yes', -name=>"colOption$rownum", -value=>'not null'), ""; } $rownum++; } print "
    Field Name Field Type Length Required
    Field $rownum ", $query->textfield(-name=>"colName$rownum", -size=>20, -value=>$fieldname), "", $query->popup_menu(-name=>"colType$rownum", -"values"=>['char', 'real', 'int'], -default=>"$fieldtype"), "", $query->textfield(-name=>"colLen$rownum", -size=>3, -value=>$fieldlen), ""; if ($checked eq 'yes') { print $query->checkbox(-label=>'yes', -name=>"colOption$rownum", -checked=>"checked", -value=>'not null'), "
    "; # Generate submit button print "
    ", "", $query->submit(-name=>'Submit Definition'); print $query->hidden('noCols', $rows); # If editting give the opportunity to change everything if ($query->param('CHANGE')) { print "", $query->end_form; print $query->start_form(-method=>'POST', -action=>'./tabdef.pl'); print $query->hidden('newtable', 'start'); print $query->submit(-name=>'Start Over'); print "
    ", $query->end_form; } # Else Generate reset button else { print "", $query->reset; print "", $query->end_form; } print $query->end_html; exit; } ################################################################### # ################################################################### sub verify { # to pick up the input parameter values and set up for db call ($Tname_err, $Tnoname_err, $Tnot_unique) = (0, 0, 0); ($Cnoname_err, $Cname_err, $Cnot_unique) = (0, 0, 0); $errors = 0; $Cnum_err = ''; $Cnum_unique = ''; $Cnoname_err = ''; my ($noCols) = $query->param('noCols'); # number of field my ($tableName) = $query->param('tableName'); # table name my ($colNum) = 1; # counter for rows my ($colDefs) = ''; # definitions in msql format my (@colName, @colType, @colLen, @colOption, @colcolor); ## @colName -> duh! ## @colType -> field data type (ie 'char', 'real', 'int') ## @colLen -> max length of field ## (only needed if corresponding colType is 'char') ## @colOption -> Is the field required? ('not null' or ''). ## @colcolor -> Needed for color coding field option. # check that table name was not left blank and/or for spaces in table name # and/or that table name does not already exist in database. if (!($query->param('tableName'))) { $Tnoname_err = 1; $errors++; } ## check for character msql1.x does not like elsif ($tableName =~ / |\||\(|\)|\-|\+|\=|\*|\&|\^|\%|\$|\#|\@|\!|\~|\[|\]|\{|\}|\<|\>|\?|\,|\\|\`|\"|\'|\:|\;|\.|\//g) { $Tname_err = 1; $errors++; } else { my $dbh = &dbConnect(); @arr = $dbh->listtables; foreach $tn(@arr) { if ($tableName eq $tn) { $Tnot_unique = 1; $errors++; } } } # # check that field names were not left blank and/or for spaces in field names # and/or that field names are unique int the table. while ($noCols >= $colNum) { if (!($query->param("colName$colNum"))) { $Cnoname_err++; if ($Cnoname_err eq '') { $Cnoname_err = "$colNum"; } else { $Cnoname_err = join ', ', $Cnoname_err, $colNum; } $errors++; } ## check for character msql1.x does not like elsif ($query->param("colName$colNum") =~ / |\||\(|\)|\-|\+|\=|\*|\&|\^|\%|\$|\#|\@|\!|\~|\[|\]|\{|\}|\<|\>|\?|\,|\\|\`|\"|\'|\:|\;|\.|\//g) { $Cname_err++; if ($Cnum_err eq '') { $Cnum_err = "$colNum"; } else { $Cnum_err = join ', ', $Cnum_err, $colNum; } $errors++; } $colNum++; } $colNum = 1; ## check for dups in field names while ($noCols >= $colNum) { $colNum2 = $colNum; while ($noCols >= $colNum2) { if ($query->param("colName$colNum")) { if ($query->param("colName$colNum") eq $query->param("colName$colNum2") && $colNum != $colNum2 ) { $Cnot_unique++; if ($Cnum_unique eq '') { $Cnum_unique = "$colNum & $colNum2"; } else { $Cnum_unique = join ', ', $Cnum_unique, "$colNum & $colNum2"; } $errors++; } } $colNum2++; } $colNum++; } # ##-- $colNum = 1; ## if no errors show and verify table structure if ($errors != 0) { &do_work(); } print $query->start_html('Verify Table'); print ""; print ""; print "

    ", "HELP

    "; while ($noCols >= $colNum) { push(@colName, $query->param("colName$colNum")); push(@colType, $query->param("colType$colNum")); push(@colLen, $query->param("colLen$colNum")); if ($colType[$colNum-1] eq "char") { if (!($colLen[$colNum-1])) { $colLen[$colNum-1] = 255; } if ($SQL eq 'Mysql' && $colLen[$colNum-1] > 255) { $colType[$colNum-1] = "blob"; } else { $colType[$colNum-1] = "$colType[$colNum-1]($colLen[$colNum-1])"; } } ## if unspecified (not required) color code = 'black' if ($query->param("colOption$colNum") eq '') { push(@colOption, ' '); push(@colcolor, "black"); } ## else field is 'not null' (required) color code = 'green' else { push(@colOption, "not null"); push(@colcolor, "green"); } ## Putting the msql create string together if ($colNum == 1) { $colDefs = "$colName[$colNum-1] $colType[$colNum-1] $colOption[$colNum-1]"; } else { $colDefs = "$colDefs, $colName[$colNum-1] $colType[$colNum-1] $colOption[$colNum-1]"; } $colNum++; } print < Verify the structure of $tableName

    END ## Display the definitions for user approval print "\n"; print "\n"; print "\n", ""; for ($i=0; $i < $noCols; $i++) { my($opt); if ( $colcolor[$i] eq 'green') { $opt= "Required"; } else { $opt= "Optional"; } print ""; print ""; if ($colType[$i] eq 'blob') { print ""; print ""; } else { print ""; if ($colType[$i] !~ /char/) { print ""; } else { print ""; } } } print <
    Field NameField TypeField Length
    ", "", "$opt ", "", " $colName[$i] ", " char ", "", " 65535
    ", " $colType[$i] ", "", "  
    ", "", " $colLen[$i] 
    Key : Rows with GREEN Text indicate field(s) that are required (ie cannot be left blank)
           Rows with Black Text indicate field(s) that may be left blank
    END ## give 'em buttons to click print "
    ", ""; print $query->start_form(-method=>GET, -action=>'./tabdef.pl'); print $query->hidden(-name=>"tableName", -value=>'$tableName'); print $query->hidden(-name=>"noCols", -value=>'$noCols'); print $query->hidden(-name=>"colDefs", -value=>"$colDefs"); print $query->submit(-name=>'CHANGE'); print $query->end_form; print ""; print $query->start_form(-method=>GET,-action=>'./tabdef.pl'); print $query->hidden(-name=>'create', -value=>'create'); print $query->hidden(-name=>"tableName", -value=>'$tableName'); print $query->hidden(-name=>"noCols", -value=>'$noCols'); print $query->hidden(-name=>"colDefs", -value=>"$colDefs"); print $query->submit(-name=>'CREATE'); print $query->end_form; print "
    "; print $query->end_html; exit; } ################################################################### # # Creates the table requires parameters : # - nocols, # - tableaName # - colDefs. # ################################################################### sub create { my ($dbstr, $sth); print $query->start_html(-title=>"Create a Table", -bgcolor=>"#ffffff"); print ""; print "

    ", "HELP

    "; my $dbh = &dbConnect(); #----- # pick up the input parameter values and set up for db call my ($noCols) = $query->param('noCols'); my ($tableName) = $query->param('tableName'); my ($colNum) = 1; my ($colDefs) = $query->param('colDefs'); $colDefs = "$colDefs, dbEase_key real not null"; #----- # create the table my $q = qq| CREATE table $tableName ($colDefs) |; $sth = $dbh->Query($q) or die print "AN ERROR HAS OCCURRED!

    Unable to create Table \'", $tableName, "\' using the Create statement :", "
    $q

    DB Error : ", &dbERR(), "
    (tabdef.pl)\n"; #----- # announce creation and point user to next possible step print "
    ", "Table $tableName has been Created.
    "; print "
            ", "Setting up your Search Page is next ...
    "; print "\n"; print $query->start_form(-method=>GET,-action=>'./script_util.pl'); print $query->hidden('tableName','$tableName'); print $query->hidden('setup','follow'); print $query->submit(-name=>"SETUP Search Page"), "\n"; print $query->end_form; print $query->end_html; exit; } # End document