DBIforms is a set of perl scripts that provides a fast and easy mechanism to generate HTML forms to manipulate database information. These scripts are based on the Perl DBI module and can be implemented using any database backend for which there is a corresponding Perl DBD module.

The concept behind DBIforms is to provide 5 basic database functions:

• insert
• update
• delete
• view
• search

The generation of reports from the data in the database is considered outside the scope of this effort and is not addressed by these functions. However, the search function assumes a custom reporting script has been created by the implementor of DBIforms and is used to display the results of the search request. More about that later in this document.

DBIforms stores it's own configuration information in the database and must be added before other implementations can be generated. To create an implementation of DBIforms, perform the following steps:

• Create a cgi-bin directory to contain the Perl DBIforms scripts and be sure this directory is recognized by the web server to contain executable programs. Using the CMDB as an example: /usr/local/httpd/cgi-bin/CMDB02
• Create an HTML document directory to contain various static and dynamic forms associated with the database that you are accessing using DBIforms. Using the CMDB as an example: /usr/local/httpd/htdocs/CMDB02
• Copy the DBIforms scripts into the cgi-bin directory created for this project:
  • insert.pl
  • update.pl
  • delete.pl
  • view.pl
  • search.pl
  • mkinputpages.pl
  • mkinputlines.pl
  • DS_DBIforms.pl
  • SUB_DBIforms.pl
• Modify the DS_DBIforms.pl to access the datasource associated with the project being implemented.:

  
    $ENV{LD_LIBRARY_PATH} = "/usr/i486-linux-libc5/lib:\
    /usr/local/openlink/lib:\
    /usr/local/lib";
  
    $ENV{DBI_DSN} = "DBI:Sybase:CMDBDEVL";    # DSN 
    $ENV{DBI_USER} = "cmdbadm";               # Database user name
    $ENV{DBI_PASS} = "configadm";             # Database user password
    $ENV{LD_LIBRARY_PATH} = "/usr/local/lib:/usr/i486-linux-libc5/lib";
    $ENV{DD_CGIBIN} = "/usr/local/httpd/cgi-bin/CMDB02";  # Full path dir
    $ENV{DD_HTDOCS} = "/usr/local/httpd/htdocs/CMDB02";   # Full path dir
  

• After modifying the DS_DBIforms.pl file to reflect the values associated with the target database, load the base DBIforms information by running the appropriate scripts:

  
  ./mkinputpages.pl
  ./mkinputlines.pl
  

  Error messages may be displayed the first time each script is executed because the script first drops, then creates the tables it uses. Since the first time the script is executed, the tables don't exist, an error is reported when dropping the tables. On any subsequent execution of the script no errors should be reported.

• DBIforms is now loaded and ready for use. Each form line may be added individually using the web based interface by running the DBIforms cgi script "insert.pl", however this is not the preferred method. The preferred method is to write a Perl script to add the form lines programmatically. The following example is a script that inserts the form lines for interacting with the CMDB Server02 table:

  
  
  #!/usr/bin/perl -X
  ################################################################
  # Program:	mkSERVERlines.pl
  #		Copyright (c) 2003 by Dana French
  #		All Rights Reserved
  #
  # Description:	Creates example form with input lines
  #
  # Author: Dana French (dfrench@mtxia.com)
  # 
  # Date: 02/15/2003
  # 
  ################################################################
  sub mkrecord()
  {
    print "Working on ${LINENAME}\n";
    $sth = $dbh->do("delete from INPUTLINES4 WHERE LINENAME = '${LINENAME}'");
    $sql = "insert into INPUTLINES4 ( LINENAME, INPUTNAME, INPUTPROMPT, INPUTTYPECGI, INPUTVALUE, INPUTHELP, TABLENAME, FIELDNAME, INPUTTYPESQL, HEIGHT, WIDTH, SELECTED, MULTIPLE, REQUIRED ) VALUES ( '${LINENAME}', '${INPUTNAME}', '${INPUTPROMPT} ', '${INPUTTYPECGI}', '${INPUTVALUE} ', '${INPUTHELP} ', '${TABLENAME}', '${FIELDNAME}', '${INPUTTYPESQL}', '${HEIGHT} ', '${WIDTH} ', '${SELECTED} ', '${MULTIPLE} ', '${REQUIRED} ' )";
    $sth = $dbh->do($sql);
  }
  ################################################################
  sub mkformline()
  {
    $sth = $dbh->do("delete from FORMLINES4 WHERE LINENAME = '${LINENAME}'");
    $sql = "insert into FORMLINES4 ( FORMNAME, LINENAME, POSITIONID ) VALUES ( '${FORMNAME}', '${LINENAME}', '${POSITIONID}' )";
    $sth = $dbh->do($sql);
  }
  ################################################################
  use DBI;
  
  $|=1;
  
  do 'DS_DBIforms.pl';
  
  # my ($sth);
  # my ($sth2);
  # my ($sth3);
  # my ($sql);
  
  $dbh = DBI->connect() ;
  
  $sth = $dbh->do("delete from INPUTPAGE4 WHERE FORMNAME = 'SERVER02'");
  $sth = $dbh->do("insert into INPUTPAGE4 ( FORMNAME, PAGE_TITLE, CONTACT_NAME, CONTACT_EMAIL, CLR_PAGEBG, CLR_LINK, CLR_ALINK, CLR_VLINK, CLR_TDBG, CLR_THBG, CLR_BASEFONT, CLR_THFONT, CLR_TDFONT, SIZ_BASEFONT, SIZ_THFONT, SIZ_TDFONT, CELLSPACING, CELLPADDING, PRETABLE_SCRIPT, POSTTABLE_SCRIPT, FORM_ACTION, FORM_METHOD ) VALUES ( 'SERVER02', 'Servers', 'Dana French', 'dfrench\@mtxia.com', 'white', 'blue', 'yellow', 'red', '#62BCC6', '#211F5C', 'black', 'red', 'black', '4', '4', '3', '0', '5', 'SERVER02_pretable.pl', 'SERVER02_posttable.pl', '', 'POST' )");
  
  ################################################################
  #  Input lines for form server02
  ################################################################
  
  ${LINENAME} = 'server02_frameid';
  ${INPUTNAME} = 'SERVER02_FRAMEID';
  ${INPUTPROMPT} = 'FRAME ID Number';
  ${INPUTTYPECGI} = 'dynamicReference:Frame02:name:id';
  ${INPUTVALUE} = '';
  ${INPUTHELP} = 'Enter the associated FRAME ID Number for this server';
  ${TABLENAME} = 'KEY';
  ${FIELDNAME} = 'frame_id';
  ${INPUTTYPESQL} = 'int';
  ${HEIGHT} = '8';
  ${WIDTH} = '8';
  ${SELECTED} = '0';
  ${MULTIPLE} = '0';
  ${REQUIRED} = '0';
  &mkrecord;
  
  ${LINENAME} = 'server02_name';
  ${INPUTNAME} = 'SERVER02_NAME';
  ${INPUTPROMPT} = 'Server Name';
  ${INPUTTYPECGI} = 'text';
  ${INPUTVALUE} = '';
  ${INPUTHELP} = 'Enter the name of this server';
  ${TABLENAME} = 'KEY';
  ${FIELDNAME} = 'name';
  ${INPUTTYPESQL} = 'varchar(64)';
  ${HEIGHT} = '32';
  ${WIDTH} = '64';
  ${SELECTED} = '0';
  ${MULTIPLE} = '0';
  ${REQUIRED} = '1';
  &mkrecord;
  
  ${LINENAME} = 'server02_hostname';
  ${INPUTNAME} = 'SERVER02_HOSTNAME';
  ${INPUTPROMPT} = 'Host Name';
  ${INPUTTYPECGI} = 'text';
  ${INPUTVALUE} = '';
  ${INPUTHELP} = 'Enter the host name of this server';
  ${TABLENAME} = 'Server02';
  ${FIELDNAME} = 'hostname';
  ${INPUTTYPESQL} = 'varchar(64)';
  ${HEIGHT} = '32';
  ${WIDTH} = '64';
  ${SELECTED} = '0';
  ${MULTIPLE} = '0';
  ${REQUIRED} = '1';
  &mkrecord;
  
  ${LINENAME} = 'server02_managename';
  ${INPUTNAME} = 'SERVER02_MANAGENAME';
  ${INPUTPROMPT} = 'Management Name';
  ${INPUTTYPECGI} = 'text';
  ${INPUTVALUE} = '';
  ${INPUTHELP} = 'Enter the systems management name of this server';
  ${TABLENAME} = 'Server02';
  ${FIELDNAME} = 'managename';
  ${INPUTTYPESQL} = 'varchar(64)';
  ${HEIGHT} = '32';
  ${WIDTH} = '64';
  ${SELECTED} = '0';
  ${MULTIPLE} = '0';
  ${REQUIRED} = '1';
  &mkrecord;
  
  ${LINENAME} = 'server02_osname';
  ${INPUTNAME} = 'SERVER02_OSNAME';
  ${INPUTPROMPT} = 'Server OS Name';
  ${INPUTTYPECGI} = 'text';
  ${INPUTVALUE} = '';
  ${INPUTHELP} = 'Enter the name of the Operating System running on this server';
  ${TABLENAME} = 'Server02';
  ${FIELDNAME} = 'osname';
  ${INPUTTYPESQL} = 'varchar(64)';
  ${HEIGHT} = '32';
  ${WIDTH} = '64';
  ${SELECTED} = '0';
  ${MULTIPLE} = '0';
  ${REQUIRED} = '0';
  &mkrecord;
  
  ${LINENAME} = 'server02_osversion';
  ${INPUTNAME} = 'SERVER02_OSVERSION';
  ${INPUTPROMPT} = 'Server OS Version';
  ${INPUTTYPECGI} = 'text';
  ${INPUTVALUE} = '';
  ${INPUTHELP} = 'Enter the version of the Operating System running on this server';
  ${TABLENAME} = 'Server02';
  ${FIELDNAME} = 'osversion';
  ${INPUTTYPESQL} = 'varchar(32)';
  ${HEIGHT} = '32';
  ${WIDTH} = '32';
  ${SELECTED} = '0';
  ${MULTIPLE} = '0';
  ${REQUIRED} = '0';
  &mkrecord;
  
  ${LINENAME} = 'server02_oslevel';
  ${INPUTNAME} = 'SERVER02_OSLEVEL';
  ${INPUTPROMPT} = 'Server OS Level';
  ${INPUTTYPECGI} = 'text';
  ${INPUTVALUE} = '';
  ${INPUTHELP} = 'Enter the maintenance level of the Operating System running on this server';
  ${TABLENAME} = 'Server02';
  ${FIELDNAME} = 'oslevel';
  ${INPUTTYPESQL} = 'varchar(32)';
  ${HEIGHT} = '32';
  ${WIDTH} = '64';
  ${SELECTED} = '0';
  ${MULTIPLE} = '0';
  ${REQUIRED} = '0';
  &mkrecord;
  
  ${LINENAME} = 'server02_foserver';
  ${INPUTNAME} = 'SERVER02_FOSERVER';
  ${INPUTPROMPT} = 'FailOver Server';
  ${INPUTTYPECGI} = 'text';
  ${INPUTVALUE} = '';
  ${INPUTHELP} = 'Enter the name of the failover server';
  ${TABLENAME} = 'Server02';
  ${FIELDNAME} = 'failoverserver';
  ${INPUTTYPESQL} = 'varchar(64)';
  ${HEIGHT} = '32';
  ${WIDTH} = '64';
  ${SELECTED} = '0';
  ${MULTIPLE} = '0';
  ${REQUIRED} = '0';
  &mkrecord;
  
  ${LINENAME} = 'server02_servicedesk';
  ${INPUTNAME} = 'SERVER02_SERVICEDESK';
  ${INPUTPROMPT} = 'Service Desk Group';
  ${INPUTTYPECGI} = 'dynamicSelect';
  ${INPUTVALUE} = '';
  ${INPUTHELP} = 'Enter the name of the service desk group';
  ${TABLENAME} = 'Server02';
  ${FIELDNAME} = 'servicedeskgroup';
  ${INPUTTYPESQL} = 'varchar(30)';
  ${HEIGHT} = '30';
  ${WIDTH} = '30';
  ${SELECTED} = '0';
  ${MULTIPLE} = '0';
  ${REQUIRED} = '0';
  &mkrecord;
  
  ################################################################
  #  Associate input lines with a form
  ################################################################
  
  ${FORMNAME} = 'SERVER02';
  $sth = $dbh->do("delete from FORMLINES4 WHERE FORMNAME = '${FORMNAME}'");
  
  ${LINENAME} = 'server02_frameid';
  ${POSITIONID} = '1';
  &mkformline;
  
  ${LINENAME} = 'server02_name';
  ${POSITIONID} = '2';
  &mkformline;
  
  ${LINENAME} = 'server02_hostname';
  ${POSITIONID} = '3';
  &mkformline;
  
  ${LINENAME} = 'server02_managename';
  ${POSITIONID} = '4';
  &mkformline;
  
  ${LINENAME} = 'server02_osname';
  ${POSITIONID} = '5';
  &mkformline;
  
  ${LINENAME} = 'server02_osversion';
  ${POSITIONID} = '6';
  &mkformline;
  
  ${LINENAME} = 'server02_oslevel';
  ${POSITIONID} = '7';
  &mkformline;
  
  ${LINENAME} = 'server02_foserver';
  ${POSITIONID} = '8';
  &mkformline;
  
  ${LINENAME} = 'server02_servicedesk';
  ${POSITIONID} = '9';
  &mkformline;
  
  $dbh->disconnect();
  
  

• Scripting the form line insert makes it much easier to make changes if necessary. The above script creates the appropriate entries for the "INPUTPAGE4", "INPUTLINES4", and "FORMLINES4" tables associated with the "SERVER02" form. The "INPUTPAGE4" table contains look-and-feel information for the form, "INPUTLINES4" contains line-by-line configuration information for each input line associated with the form, and "FORMLINES4" contains ordering information for displaying each line on the form. Each form line requires multiple pieces of information described as follows:
  • ${FORMNAME} - A database wide unique name for this form. All subsequent form lines will be associated with this form name.
  • ${LINENAME} - A database wide unique name for this form line.
  • ${INPUTNAME} - A unique HTML name form this form line.
  • ${INPUTPROMPT} - A short prompt to display on the form next to the input field for this form line.
  • ${INPUTTYPECGI} - The HTML datatype for this form line.
  • ${INPUTVALUE} - A default value to use for this form line.
  • ${INPUTHELP} - A help message to display to assist the user in entering the appropriate information for this form line.
  • ${TABLENAME} - The database table name where this information will be stored.
  • ${FIELDNAME} - The field name in the database table where this information will be stored.
  • ${INPUTTYPESQL} - The SQL datatype for the data entered in this form line.
  • ${HEIGHT} - This field has multiple meanings: for CGI datatype "textarea" this is the number of rows of the textarea, for CGI datatype "text" this is the visible size of the input box.
  • ${WIDTH} - This field has multiple meanings: for CGI datatype "textarea" this is the number of columns of the textarea, for CGI datatype "text" this is the maximum number of characters allowed.
  • ${SELECTED} - For CGI datatype "select", a value of "1" identifies this entry as the "selected" value.
  • ${MULTIPLE} - For CGI datatype "select", a value of "1" means the user can select multiple values in the associated select box.
  • ${REQUIRED} - Identifies this form line as a required value that must be entered by the user.
  • ${POSITIONID} - Identifies the position on the HTML form to display this form line.
• After each form line has been defined in a script, the script is executed to load the values into the DBIforms tables. As an example, the script to create the form lines for the CMDB02 Server02 tables can be executed as follows:

  
  /usr/local/httpd/cgi-bin/CMDB02/mkSERVERlines.pl
  

• Editing of the DBIforms perl scripts may be required to change the path locations associated with the default menu selections. Edit each of the following files, search for "menuarray", and change the directory path appropriately:
  • insert.pl
  • update.pl
  • delete.pl
  • view.pl
  • search.pl

• All reporting is expected to be custom programmed, even the report generated by the "search.pl" script. The "search.pl" script expects to execute a perl script called "report${FORMNAME}.pl", where the variable ${FORMNAME} contains the name of the current form being processed. Before the "search.pl" script will generate reports, the DBIforms implementor must write the perl scripts to process the search request. An example report script is shown for the search function associated with the form named "SEARCH02". The script name is "/usr/local/httpd/cgi-bin/CMDB02/reportSERVER02.pl" :

  
  
  #!/usr/bin/perl -X
  ################################################################
  # Program:	reportSERVER02.pl
  #		Copyright (c) 2003 by Dana French
  #		All Rights Reserved
  #
  # Description:	Allows user to search records in database
  #
  # Author: Dana French (dfrench@mtxia.com)
  # 
  # Date: 11/12/2003
  # 
  ################################################################
  use DBI;
  
  $|=1;
  @script_file = split(/\//, $ENV{SCRIPT_NAME} );
  
  do 'DS_DBIforms.pl';
  do 'SUB_DBIforms.pl';
  
  $dbh = DBI->connect() ;
  
  &cgiParse;
  &selectInputPage;
  
  ${FORMNAME}="SERVER02";
  $titlebar1 = "Configuration Management";
  $titlebar2 = "Server02 Report";
  &header;
  &staticbar;
  &titlebar;
  
  $menuarray{0} = { "prompt" => "Insert ${FORMNAME}", "url" => "/cgi-bin/CMDB02/insert.pl?FORMNAME=${FORMNAME}" };
  $menuarray{1} = { "prompt" => "Modify ${FORMNAME}", "url" => "/cgi-bin/CMDB02/update.pl?FORMNAME=${FORMNAME}" };
  $menuarray{2} = { "prompt" => "Delete ${FORMNAME}", "url" => "/cgi-bin/CMDB02/delete.pl?FORMNAME=${FORMNAME}" };
  $menuarray{3} = { "prompt" => "View ${FORMNAME}",   "url" => "/cgi-bin/CMDB02/select.pl?FORMNAME=${FORMNAME}" };
  
  print "<P><UL>\n";
  
  ${WHERECLAUSE} = "WHERE";
  
  if ( "_${SERVER02_FRAMEID}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND frame_id = '${SERVER02_FRAMEID}'";
  }
  if ( "_${SERVER02_NAME}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND name LIKE '%${SERVER02_NAME}%'";
  }
  if ( "_${SERVER02_HOSTNAME}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND hostname LIKE '%${SERVER02_HOSTNAME}%'";
  }
  if ( "_${SERVER02_MANAGENAME}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND managename LIKE '%${SERVER02_MANAGENAME}%'";
  }
  if ( "_${SERVER02_OSNAME}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND osname LIKE '%${SERVER02_OSNAME}%'";
  }
  if ( "_${SERVER02_OSVERSION}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND osversion LIKE '%${SERVER02_OSVERSION}%'";
  }
  if ( "_${SERVER02_OSLEVEL}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND oslevel LIKE '%${SERVER02_OSLEVEL}%'";
  }
  if ( "_${SERVER02_BUSINESSUNIT}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND businessunit LIKE '%${SERVER02_BUSINESSUNIT}%'";
  }
  if ( "_${SERVER02_FOSERVER}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND failoverserver LIKE '%${SERVER02_FOSERVER}%'";
  }
  if ( "_${SERVER02_SERVICEDESK}" ne "_" ) {
     ${WHERECLAUSE} = "${WHERECLAUSE} AND servicedeskgroup LIKE '%${SERVER02_SERVICEDESK}%'";
  }
  ${WHERECLAUSE} = "" if ( "_${WHERECLAUSE}" eq "_WHERE" );
  ${WHERECLAUSE} =~ s/WHERE AND/WHERE/;
  
  ################################################################
  
  ${sql_statement} = "SELECT COUNT(*) FROM Server02 ${WHERECLAUSE}";
  
  $sth0 = $dbh->prepare($sql_statement);
  $sth0->execute;
  if ($DBI::err) { print STDOUT $DBI::errstr,"\n"; last; }
  my $count_name = $sth0->{NAME};
  while (@countresults = $sth0->fetchrow_array) {
    if ($DBI::err) { print STDOUT $DBI::errstr,"\n"; last; }
    foreach $field_name (@$count_name) {
      ${servercount} = shift @countresults;
    }
  }
  print "Server Records found: ${servercount}<BR>\n";
  
  ################################################################
  
  if ( ${servercount} > 0 ) {
    
    ${sql_statement} = "SELECT DISTINCT id, name FROM Server02 ${WHERECLAUSE} ORDER BY name ";
    
    print "${sql_statement}<BR>\n";
    
    $sth99 = $dbh->prepare($sql_statement);
    $sth99->execute;
    if ($DBI::err) { print STDOUT $DBI::errstr,"\n"; last; }
    my $emp_name = $sth99->{NAME};
    ${rowcount} = 0;
    while (@results = $sth99->fetchrow_array) {
      if ($DBI::err) { print STDOUT $DBI::errstr,"\n"; last; }
      foreach $field_name (@$emp_name) {
        ${${field_name}} = shift @results;
      }
  
      ++${rowcount};
  
      if ( ${CSV} != 1) {
        print "</UL></P><P><HR></P><P><UL>\n";
        print "<P><LI> <STRONG>Server Name:</STRONG> ${name}\n";
        print " <STRONG>Record ID:</STRONG> ${id}</LI></P>\n";
      }    
      ${sql_statement} = "SELECT * FROM Server02 WHERE id = '${id}' ORDER BY id, name";
      $sth1 = $dbh->prepare($sql_statement);
      $sth1->execute;
      if ($DBI::err) { print STDOUT $DBI::errstr,"\n"; last; }
      my $emp_name = $sth1->{NAME};
      if ( ${CSV} == 1 && ${rowcount} == 1 ) {
        print "<P><HR></P>\n";
        foreach $field_name (@$emp_name) {
          print "${field_name}, ";
        }
        print "<BR>\n";
      }
      while (@results = $sth1->fetchrow_array) {
        if ($DBI::err) { print STDOUT $DBI::errstr,"\n"; last; }
        foreach $field_name (@$emp_name) {
          ${${field_name}} = shift @results;
          if ( ${CSV} == 1) {
            print "${${field_name}}, ";
          }
        }
        if ( ${CSV} == 1 ) {
          print "<BR>\n";
          next;
        }
        print "<UL>\n";
        print "<LI>\n";
        print " <STRONG>Frame ID:</STRONG> ${frame_id}<BR>\n";
        print " <STRONG>Host Name</STRONG> ${hostname}<BR>\n";
        print " <STRONG>Management Name</STRONG> ${managename}<BR>\n";
        print " <STRONG>OS Name</STRONG> ${osname}<BR>\n";
        print " <STRONG>OS Version:</STRONG> ${osversion}<BR>\n";
        print " <STRONG>OS Level:</STRONG> ${oslevel}<BR>\n";
        print " <STRONG>Business Unit:</STRONG> ${businessunit}<BR>\n";
        print " <STRONG>Failover Server:</STRONG> ${failoverserver}<BR>\n";
        print " <STRONG>Service Desk Group:</STRONG> ${servicedeskgroup}<BR>\n";
        print "</LI>\n";
        print "</UL>\n";
      }
    }
  }  
  
  print "</UL></P>\n";
  
  &footer;
  
  $dbh->disconnect();
  
  

• Before any information can be manipulated by DBIforms, all tables identified in each form must be created and permission must be granted to the web server user to perform inserts, updates, deletes, etc. An example is shown for granting permissions to the Apache web server user to access the MySQL CMDB database:

  
  grant
  select,insert,update,delete,index,alter,create,drop,reload,shutdown,process,file
  on CMDBDEVL.*
  to wwwrun@mtxdoc
  identified by 'password for wwwrun'
  with grant option;
  
  grant
  select,insert,update,delete,index,alter,create,drop,reload,shutdown,process,file
  on CMDBDEVL.*
  to wwwrun@mtxdoc.mtxia.com
  identified by 'password for wwwrun'
  with grant option;
  
  grant
  select,insert,update,delete,index,alter,create,drop,reload,shutdown,process,file
  on CMDBDEVL.*
  to wwwrun@localhost
  identified by 'password for wwwrun'
  with grant option;