Subversion Repositories phpLibraryV2

<?php

/* database.class.php

   Author: R. W. Rodolico (rodo at dailydata.net)

   GNU Public License

   20091023

   Encapsulates a database, specifically targeting allowing a database and HTML forms to 

   interact. Main function is to allow editing of databases simply by defining the database

   structure in a variable

   This code was written in procedural form in my library.php, but the decision to "objectify" 

   it was made for ease of maintenance in the future. For example, I had thought to define 

   columns that would be editable

   in an HTML widget, or special needs column types such as columns with special content (ie, 

   one application we have stores PHP in a text column, and another stores menus and reports in

   two tables with very weird storage requirements). By simply adding extensions to the basic

   DBColumn class, these special purpose uses can be rapidly created.

   A database object may be created manually, or may be created as an array of information

*/

define( 'DEBUG', true );

require_once "Logging.class.php"; // For logging

/* 

   This is the basic column type. It is a simple string, 

      left justified on HTML display

      <input type='text'> for form input

      varchar for db storage

   It is also used as the basis for all of the other column types

*/

/*

   CSS Classes used

   db_string      display and input a string

   db_field_name  display table column header or td

   db_textarea    display and input textarea (td or <textarea>)

   db_bool        display and input true/false (radio)

   db_date        display and input date (text)

   db_datetime    display and input date and time (text)

   db_int         display and input integers

   db_real        display and input floating point numbers

   db_password    display and input password (password, display is a series of 8 asterisks)

   db_file        display and input file (type="file")

*/

class DBColumn { // base class for various column types

   /*

      A column has

         name

         type (used to define how it is processed)

         nullable (if it can be null or not)

         default value (used if creating a row)

         required (must have a non-null value entered)

         readOnly (will be displayed as text in an input form)

         width (size of the actual field)

   */

   public      $columnName;   // name in database

   public      $primaryKey;   // true if this is a member of the primary key

   public      $displayName;  // human readable name, uses $columnName if not defined

   public      $value;        // in most cases, can hold the actual value (not type file, MTM)

   public      $required;     // if false, may be set to null

   public      $default;      // for not null columns, what value to use if it is null.

   public      $readOnly;     // if set, field will be displayed and not editable

   public      $width;        // width of input field and/or html display

   public      $nullable;     // true/false if it can be null (as opposed to empty)

   // Accepts a column name (required), and array of definitions, and a value (both optional)

   public function __construct ( $columnName, $definition = null, $value=null) {

      $this->columnName = $columnName;

      $this->value = isset($value) ? $value : '';

      $this->displayName = isset($definition['display name']) ? 

                           $definition['display name'] : 

                           $this->columnName;

      $this->required = isset($definition['required']) ? $definition['required'] : false;

      $this->default = isset($definition['default']) ? $definition['default'] : '';

      $this->width = isset($definition['width']) ? $definition['width'] : 00;

      $this->readOnly = isset($definition['readonly']) ? $definition['readonly'] : false;

      if ( $this->default == 'null' ) {

         $this->null = true;

         $this->default = '';

      }

      $this->primaryKey = ( isset($definition['keyfield']) ? $definition['keyfield'] : false);

   } // function __construct

   function __toString() {

      return isset( $this->value ) ? $this->value :  'null';

   }

   /*

    * function simply returns the column name and the display name

    * formatted so as to work correctly in an SQL select statement

    * Will also prepend the table name passed in as a parameter 

    * (if it exists) to allow explicit definition of the column

    * for multi-table queries

    */ 

   public function getQueryColumn ( $tableName = null, $useDisplayName = false ) {

      return array(  'fieldnames'  => ($tableName ? "$tableName." : '' ) 

                     . $this->columnName

                     . ( $useDisplayName ? " '$this->displayName'" : '' )

                     );

   } // getQueryColumn

   /* 

      function takes a string, and looks for the array names in $values, replacing occurrences

      of it with 

   */

   private function replaceTokens ( $string, $values ) {

      foreach( $values as $token => $value ) {

         $string = preg_replace("/$token/", $value, $string);

      }

      return $string;

   }

   function makeSafeSQLValue ( $value = null ) {

      if ($value) {

         $this->value = $value;

      }

      if (strlen($this->value)) {   // it actually has a value

         if(get_magic_quotes_gpc()) {

            $this->value = stripslashes($this->value);

         }

         $this->value = mysql_real_escape_string( $this->value );

         $this->value = "'" . $this->value . "'";  // put the quotes around it

      } else {

         $this->value = ($this->nullable ? 'null' : "''");

      }

      return $this->value;

   }

   function toHTML( $type = 'view', $parameters = '' ) {

      $return = '';

      switch ( $type ) {

        case 'view': $return = "<tr><td>$this->columnName</td><td>$this->value</td></tr>";

                     break;

        case 'list': $return = '<td>' . $this->value . '</td>';

                     break;

        case 'edit': $return = "<tr><td>$this->columnName</td><td>$this->value</td></tr>";

                     break;

      } // switch

      return $return;

   } // toHTML;

}

/* ============================================================================================

   class DBColumnBool

   Used for multi row columns, ie HTML TextArea's and DB Text columns 

*/

class DBColumnText extends DBColumn {

} // class DBColumnText

/* ============================================================================================

   class DBColumnBool

   used for True/False, 1/0, etc...

      html display T or F (can be overridden)

      Input is a True/False drop down on form input

      Stored in a char

*/

class DBColumnBool extends DBColumn {

   /*

    * array containing the values considered as the keys. If '' is included, an empty string is

    * considered false also

   */ 

   public      $falseValues;

   public function __construct ( $columnName, $definitions = null, $value=null) {

      parent::__construct( $columnName, $definitions , $value  );     // first call base class

      $this->falseValues = isset( $definitions['null values'] ) ? 

                           $definitions['null values'] : 

                           array ( 'f' => 1, 'F' => 1, 'n' => 1, 'N' => 1, '0' => 1, '' => 1 );

   } // function __construct

   public function makeSafeSQLValue ( $value = null ) {

      // definition of values which will be interpreted as false

      if ($value) {

         $this->value = ($value == 'null' ? '' : $value);

      }

      if ($this->value) {

         $this->value = $this->falseValues[$this->value] ? '0' : '1';

      } else {

         $this->value = ($this->nullable ? 'null' : "'0'");

      }

      return $this->value;

   } // function makeSafeSQLValue

   /*

    * override default to return a function that displays "true" or "false"

    */ 

    public function getQueryColumn ( $tableName = null, $useDisplayName = false ) {

      $columnName = ($tableName ? "$tableName." : '' ) . $this->columnName;

      return array(  'fieldnames'  => "if ($columnName = 1, 'true','false')" .

                     ( $useDisplayName ? " '$this->displayName'" : '' )

                  );

   } // getQueryColumn

} // class DBColumnBool

/* ============================================================================================

   class DBColumnDate

   holds a date only (ie, no time)

      html display is yyyy-mm-dd (can be overridden)

      input uses advanced library

      stored in a date

*/

class DBColumnDate extends DBColumn {

   function makeSafeSQLValue ( $value = null ) {

      if ($value) {

         $this->value = ($value == 'null' ? '' : $value);

      }

      if ( $result = strtotime( $value ) ) {

         $this->value = "'" . Date( 'Y-m-d', $result) . "'";

      } else {

         $this->value = ($this->nullable ? 'null' : "'0000-00-00'");

      }

      return $this->value;

   }

} // class DBColumnDate

/* ============================================================================================

   class DBColumnDateTime

   holds a date time stamp

      html display is yyyy-mm-dd hh:mm:ss (can be overridden)

      input uses advanced library

      stored in datetime

*/

class DBColumnDateTime extends DBColumn {

   function makeSafeSQLValue ( $value = null ) {

      if ($value) {

         $this->value = ($value == 'null' ? '' : $value);

      }

      if ( $result = strtotime( $this->value ) ) {

         $this->value = "'" . Date( 'Y-m-d H:i:s', $result) . "'";

      } else {

         $this->value = ($this->nullable ? 'null' : "'0000-00-00 00:00:00'");

      }

      return $this->value;

   }

} // class DBColumnDateTime

/* ============================================================================================

   class DBColumnInt

   holds an integer

      html display is right justified

      input verifies numerics only

      stored in int (may be stored in int unsigned)

*/

class DBColumnInt extends DBColumn {

   public      $range;                 // if defined, number must be within this range

   public      $signed = true;        // if false, uses int unsigned

   public function __construct( $columnName, $definitions = null, $value = null) {

      parent::__construct( $columnName, $definitions, $value );     // first call base class

      $this->range['min'] = isset( $definitions['min'] ) ? $definitions['min'] : null;

      $this->range['max'] = isset( $definitions['max'] ) ? $definitions['max'] : null;

      $this->signed = isset( $definitions['signed'] ) ? $definitions['signed'] : true;

   } // function __construct

   function makeSafeSQLValue ( $value = null ) { // simply remove all non-numerics

      if ($value) {

         $this->value = $value == 'null' ? '' : $value;

      }

      $this->value = preg_replace( '/[^0-9]/', '', $this->value );

      if ( ! strlen($this->value) ) {

         $this->value = $this->nullable ? 'null' : '0';

      }

      return $this->value;

   }

} // class DBColumnInt 

/* ============================================================================================

   class DBColumnReal

   holds a floating point number

      html display is right justified

      may be padded

      input verfies floating point number

      stored in float

*/

class DBColumnReal extends DBColumnInt {

   public      $decimalPlaces;

   public function __construct( $columnName, $definitions = null, $value = null) {

      parent::__construct( $columnName, $definitions, $value );     // first call base class

      $this->decimalPlaces = $definition['decimal places'] ? $definition['decimal places'] : 0;

   } // function __construct

   function makeSafeSQLValue ( $value = null ) { // same as int version, but allows periods

      if ($value) {

         $this->value = $value = 'null' ? '' : $value;

      }

      $this->value = preg_replace( '/[^0-9.]/', '', $this->value );

      if ( ! strlen($this->value) ) {

         $this->value = $this->nullable ? 'null' : '0';

      }

      return $this->value;

   }

} // class DBColumnReal

/* ============================================================================================

   class DBColumnPassword

   holds a hash of a password

      HTML display is a series of 8 stars

      html input is type='password'

      stored in a char(32). This is the MD5 sum of the password

*/

class DBColumnPassword extends DBColumn {

   function makeSafeSQLValue ( $value = null ) {

      parent::__makeSafeSQLValue( $value ); // first, process as a string

       // then, set it to call the MD5 function MySQL, PostgreSQL, Oracle. MS SQL-Server does

       // not have this function

      $this->value = 'MD5(' . $this->value . ')';

   }

   function __toString() {

      return '****************';

   } // function __toString override

   /*

    * override default to return just a string of 8 asterisks

    */ 

    public function getQueryColumn ( $tableName = null, $useDisplayName = false ) {

      return array(  'fieldnames'  => "'********'" .

               ($useDisplayName ? " '$this->displayName'" : '' )

               );;

   } // getQueryColumn

} // class DBColumnPassword

/* ============================================================================================

   class DBColumnFile

   holds file

      html display is file name, click to download

      input is type='file'

      stored either in a blob, or in a varchar as the filename on disk

*/

class DBColumnFile extends DBColumn {

   public      $viewable; // if true, can be displayed on screen, ie pictures

   // if false, this is a blob column which contains the file. If true, it is a varchar which 

   // contains the path

   public      $onDisk = true;

   public function __construct( $columnName, $definitions = null, $value = null) {

      parent::__construct( $columnName, $definitions, $value );     // first call base class

      $this->viewable = $definition['viewable'];

   } // function __construct

} // class DBColumnFile

/* ============================================================================================

   class DBColumnLookup

   Holds a foreign key value

      html display is target entry

      input is a dropdown

      stored as an int unsigned foreign key (tablename.primary key)

  The display will be a drop down box which will be filled with display values from the foreign

  table

  Basically, will generate the query

   select $this->$remotePrimaryKey,$this->remoteDisplayField 

   from $this->remoteTableName 

   where $this->filter

   sort by $this->sortOrder

  where

   '$this->filter' will only be used if it is non-null (otherwise, there will be no 

          where clause)

   'sort by $this->sortOrder' will only be used if $sortOrder is non-null (otherwise, 

         there will be no sort by)

   $remoteDisplayField must resolve to a single column in the query result, ie concat(field1,

         field2), not field1,field2

  The <select> box will have the remote field highlighted that corresponds to $this->value

  If $this->value is null and this is a nullable field, the special text "No Value" 

     is added to the <select> box

  If $this->value is null and this is not a nullable field, the first item in the drop down 

     will be selected.

*/

class DBColumnLookup extends DBColumn {

   // the actual table name in the database used for lookups

   protected   $remoteTableName;

   // column name in $remoteTableName that matches this columns value

   protected   $remotePrimaryKey;

   // a string used in select statement to build a display

   protected   $remoteDisplayField; 

   // if set, will generate an additional where clause (anded) to limit the display of fields

   protected   $filter;   

   // if set, will generate a sort by clause to determine display order          

   protected   $sortOrder;

   // used for self-referential joins

   protected   $remoteTableAlias;

   public function __construct( $columnName, $definitions = null, $value = null) {

      parent::__construct( $columnName, $definitions, $value );     // first call base class

      $this->remoteTableName = isset( $definitions['table'] ) ?

                                 $definitions['table'] : null;

      $this->remotePrimaryKey = isset($definitions['keyfield']) ? 

                                 $definitions['keyfield'] : null;

      $this->remoteDisplayField = isset( $definitions['display_field']) ? 

                                 $definitions['display_field'] : null;

      $this->remoteTableAlias = isset( $definitions['table alias'] ) ? 

                                 $definitions['table alias'] : null;

      $this->filter = isset( $definitions['filter'] ) ? $definitions['filter'] : null;

      $this->sortOrder = isset( $definitions['sort by'] ) ? $definitions['sort by'] : null;

   } // function __construct

   /*

    * override default to return a function that displays "true" or "false"

    */ 

   public function getQueryColumn ( $tableName = null, $useDisplayName = false ) {

      $return = array();

      if ( $tableName == $this->remoteTableName ) {

         // this is a self-referential table!! Everything must be done via an alias

         // verify an alias has been created

         if ( ! isset( $this->remoteTableAlias ) )

            throw new Exception ("DBColumnLookup->getQueryColumn has self referential table, " .

                                 "but 'table alias' not defined for " .

                                 "$tableName.$this->columnName" );

         $return['from']["$this->remoteTableName as $this->remoteTableAlias"]['on'] = 

               "$tableName.$this->columnName = $this->remoteTableAlias.$this->remotePrimaryKey";

         $return['fieldnames'] = 

               "$this->remoteTableAlias.$this->remoteDisplayField '$this->displayName'";

      } else { // standard join

         $return['from'][$this->remoteTableName]['on'] = 

               "$tableName.$this->columnName = $this->remoteTableName.$this->remotePrimaryKey";

         $return['fieldnames'] = 

               "$this->remoteTableName.$this->remoteDisplayField" .

                     ( $useDisplayName ? " '$this->displayName'" : '' );

      }

      return $return;

   } // getQueryColumn

} // class DBColumnLookup

/* ============================================================================================

   class DBColumnManyToMany

   simply indicates a one to many relationship

      HTML display is a list of remote table values

      input is a multi-select

      stored as a secondary table with 

         one set of columns containing the primary key of this table

         second set of column containing the primary key of a second table.

*/

class DBColumnManyToMany extends DBColumn {

} // class DBColumnManyToMany

/*

 * A table is made up of columns, has relationships with other tables,,

 * has an array (possibly null) of columns that make up the primary key

 * the queries property is an array of arrays. The array has (currenty)

 * two possible values, list and record. Each row contains an 

 * associative array with rows

 *   fieldnames - array of field names for select clause

 *   from       - array of tables to select from

 *   where      - array of clauses for the where part of the query

 *   order      - array of field names used for order by

 * the form of the generated query is

 * select {fieldnames} from {tables} where {where} order by {order}

 * See makeQueryDefinition for the method used to dynamically create this

 * NOTE: from is a complex array. It keys off of the table name, and 

 * optionally has two sub-arrays, 'join' and 'on'. 'join' gives the 

 * type of join (default is left outer) and on gives the conditions. The

 * on conditions are exactly as placed in the parentheses, ie 

 * fieldname=fieldname

 * 

 * $columns is an indexed array of DBColumn or one of its descendan

 * classes. Thus, $columns['columnname']->_toString() will have different

 * output depending on the actual type of the underlying object.

*/

class DBTable {

   public    $tableName;      // name of the table in the database

   public    $columns;        // array of columns in the table

   public    $displayName;    // display name of the table (human readable)

   public    $displayColumns; // list of columns which should be displayed. DEPRECATED

   public    $displayQuery;   // query for creating a list. DEPRECATED

   public    $queries;        // array of queries for list and record

   public    $currentRecords; // contains the query, key values and data returned from a query

   /*

    * constructor. Accepts multiple parameters and creates an instance

    */ 

   public function __construct( $tableName, $definition ) {

      $this->tableName = $tableName;

      // if a display name is not passed in, use the table name

      $this->displayName = ($definition['display name'] ? 

                           $definition['display name'] : $tableName );

      /*

       *  if a list of display columns is not passed in, use the column names from the

       * column definition

      */

      $this->displayColumns = ( $definition['display columns'] ? 

                  $definition['display columns'] : array_keys($definition['field info']) );

      // if a display query is not passwd in, just do a select *

      $this->displayQuery = ($definition['display query'] ? 

                  $definition['display query'] : "select * from $this->tableName" );

      // get the column definitions

      $this->columnDefinitionsArrayToObject( $definition['field info'] );

      $this->queries = isset($definition['queries']) ? $definition['queries'] : array();

      $this->currentRecords = null;

   } // function __construct

   public function getRecord( $keys ) {

      print "<p>In getRecord, keys are</p><pre>";  print_r( $keys ); print "</pre>";

      $this->loadFromDatabase( $keys );

      //$this->currentRecords['query'] = $this->makeWhereClause($keys);

      //$this->currentRecord = new DBRecord( $this, $keys );

      //$this->currentRecord->loadFromDatabase();

   } // function getRecord

   public function makeWhereClause ( $keys ) {

      $return = array();

      foreach ( $keys as $keyField => $value ) {

         if ( isset( $this->columns[$keyField] ) ) {

            $return[] = "$keyField = '$value'";

         }

      }

      return ' where ' . implode( ' and ', $return );

   }

   /* 

    * loads record from database. either $this->keyValues or the

    * parameter $keyValues must contain enough information to uniquely

    * identify this record.

    * Upon successful query, the primary keys are stored in 

    * $this->keyValues, and an array of all returned columns

    * is placed in $this->values.

    */ 

   public function loadFromDatabase ( $keys, $searchValues = null ) {

      if ( ! isset( $keys ) ) {

         throw new Exception ( 'in DBRecord->loadFromDatabase, no record has been' . 

                                 ' loaded and no key fields have been defined' );

      } else {

         require_once( 'DBQuery.class.php' );

         $this->currentRecords['query'] = $this->SQLQuery( 'record' ) . 

                                          $this->makeWhereClause($keys);

         $this->currentRecords['query'] = new DBQuery( $this->currentRecords['query'] );

         $this->currentRecords['query']->useAssociativeArray = true;

         try {

            if ( $this->currentRecords['query']->run() ) {

               if ( $this->currentRecords['query']->rowsAffected == 1 ) {

                  // if ( DEBUG ) print_r( $query);

                  foreach ($this->currentRecords['query']->returnData[0] as 

                           $thisColumn => $thisValue ) {

                     $this->currentRecords['data'][$thisColumn]->value = $thisValue;

                  }

               } else {

                  throw new Exception ('DBRecord->loadFromDatabase returned ' . 

                                       $info['count'] . ' rows, expected 1, query was ' . 

                                       $this->table->getSelectQuery( 'record',  

                                                                  $this->keyValues ) );

               }

            } else {

               print "Error executing query $temp\n$query->error\n";

            } // if $query->run .. else

         } catch (  Exception $e ) {

            print "Error executing query $temp\n$query->error\n";

         }

      } // if..else

   } // loadFromDatabase

   /*

    * iterates through an array. For each row in the array, determines

    * the type of the column and generates an instance of the 

    * appropriate class for it, adding it to the $columns property of

    * this class.

    */

   public function columnDefinitionsArrayToObject ( $arr ) {

      foreach ( $arr as $columnName => $definitions ) {

         switch ( $definitions['type'] ) {

            //case 'string'  :

            case 'text'    : $thisColumn = new DBColumnText($columnName, $definitions );

                              break;

            case 'date'    : $thisColumn = new DBColumnDate($columnName, $definitions );

                              break;

            case 'datetime': $thisColumn = new DBColumnDateTime($columnName, $definitions );

                              break;

            case 'int'     : $thisColumn = new DBColumnInt($columnName, $definitions );

                              break;

            case 'bool'    : $thisColumn = new DBColumnBool($columnName, $definitions );

                              break;

            case 'real'    : $thisColumn = new DBColumnReal($columnName, $definitions );

                              break;

            case 'password': $thisColumn = new DBColumnPassword($columnName, $definitions );

                              break;

            case 'file'    : $thisColumn = new DBColumnFile($columnName, $definitions );

                              break;

            case 'lookup'  : $thisColumn = new DBColumnLookup($columnName, $definitions );

                              break;

            case 'multi'   : $thisColumn = new DBColumnManyToMany($columnName, $definitions );

                              break;

            default        : $thisColumn = new DBColumn( $columnName, $definitions );

         } // switch

         $this->columns[$thisColumn->columnName] = $thisColumn;

      } // foreach

   } // function arrayToObject

   /*

    * creates a query by iterating through all columns in the current

    * table definition. Then stores the resulting query in property

    * $queries[$type]

    * WARNING: overwrites any existing query of the type requested

    */

   private function makeQueryDefinition( $type = 'list' ) {

      // insert the primary table name

      $this->queries[$type]['from'][$this->tableName] = array( );

      // process each column defined

      foreach (  $this->columns as  $columnName => $columnDefinition ) {

         // let the column tell us what should go here

         $temp = $columnDefinition->getQueryColumn( $this->tableName );

         // that returns an aray with fieldnames, where, etc..

         // so process each returned value

         foreach ( $temp as $key => $value ) {

            if ( $key == 'from' ) {

               foreach ($temp['from'] as $table => $definition ) {

                  $this->queries[$type][$key][$table] = $definition;

               }

            } else {

               $this->queries[$type][$key][] = $value;

            }

         } // foreach

      } // foreach

   } // makeQueryDefinition

   /*

    * Reads the queries structure and returns a standard

    * SQL Query.

    * $additionalClauses is an array that may contain the keys

    * 'fieldnames', 'from', 'where' and 'order' which will be

    * appended to the appropriate entries in queries structure

    */

   public function SQLQuery( $type= 'list', $additionalClauses = null ) {

      // if the particular type of query has not been defined, do so

      if ( ! ( isset( $this->queries) and count($this->queries) ) )

         $this->makeQueryDefinition( $type );

      // now, merge $this->queries and $additionalClauses

      foreach ( array( 'fieldnames', 'from', 'where', 'order' ) as $index ) {

         if ( isset( $additionalClauses[$index] ) 

               and count( $additionalClauses[$index] ) 

               and isset( $this->queries[$type][$index] ) 

               and count( $this->queries[$type][$index] ) ) {

           $additionalClauses[$index] = array_merge( 

                                                      (array)$this->queries[$type][$index], 

                                                      (array)$additionalClauses[$index] );

         } elseif ( isset( $this->queries[$type][$index] ) 

                     and count( $this->queries[$type][$index] ) ) {

           $additionalClauses[$index] = $this->queries[$type][$index];

         } // if, note that if $this doesn't exist then it simply takes on the 

           //value of $addtionalClauses

      } // foreach

      $query = 'select ' . implode( ",\n   ", $additionalClauses['fieldnames'] );

      $from = '';

      foreach ( $additionalClauses['from'] as $table => $join ) {

         if ( $from ) {

            $from = " ($from)\n    ";

            $from .= (isset( $join['join'] ) ? $join['join'] : 'left outer join');

         }

         $from .= " $table ";

         $from .= ( ( isset( $join['on'] ) && $join['on']) ? 

                  " on ( " . $join['on'] . ") " : '' );

      } // foreach

      $query .= "\nfrom $from\n";

      if (isset( $additionalClauses['where'] ) and count( $additionalClauses['where'] ) ) 

         $query .= 'where ' . implode( "\n   and " ,  $additionalClauses['where'] );

      if (isset( $additionalClauses['order'] ) and count( $additionalClauses['order'] ) ) 

         $query .= ' order by ' . implode( ",\n   ",  $additionalClauses['order'] );

      return $query;

   } // SQLQuery

   /*

    * function returns as an array all data returned by a query.

    * The returned array is an indexed array of associativeArray, 

    * with a row containing an associative array of data, where keys 

    * are the field names and the value is the value.

    * The outer array is indexed in order returned by the query, so

    * order by clauses in the SQL are honored

    */ 

   public function toArray( $parameters = null ) {

      $query = $this->SQLQuery( 'list', $parameters );

      $this->currentRecords['query'] = $query;

      return $this->currentRecords;

      $queryResults = new DBQuery( $query );

      $queryResults->useAssociativeArray = true;

      $queryResults->run();

      if ( DEBUG ) $_SESSION['debug'][] = $query;

      $this->currentRecords = $queryResults->returnData;

      return $this->currentRecords;

   }

   /*

    * function will generate two types of return. If "id" is passed in

    * it is assumed to be an array of values that will be used to select

    * a unique row from the table, in which case a new DBRecord is created

    * and it's toHTML is called.

    * 

    * If $id is empty, will return a list of all records in the table

    * based on the display record entry for this table. It calls

    * DBQueryHTML:queryToTable on that instance to generate the table

    */ 

   function toHTML( $type = 'view', $parameters = '' ) {

      $return = '';

      switch ( $type ) {

        case 'view': $return = "<tr><td>$this->columnName</td><td>$this->value</td></tr>";

                     break;

        case 'list': $queryResults = new DBQuery( $this->displayQuery, $parameters['where'], $parameters['order'] );

                     $queryResults->run();

                     if ( $queryResults->rowsAffected ) {

                        foreach ( $queryResults->returnData as $key => $row ) {

                           $return .= '<tr>';

                           foreach ( $row as $fieldName => $value ) {

                              $thisColumn = $this->columns[$fieldName];

                              $thisColumn->value = $value;

                              $return .= $thisColumn->toHTML('list', $parameters['link']);

                              //$thisColumn->toHTML( 'list' ),true );

                           } // foreach

                           $return .= '</tr>';

                        } // foreach

                     } // if

                     $return = '<table>' . $return . '</table>';

                     //new DBQueryHTML( $this->displayQuery );

                     //return print_r($queryResults,true);

//                    return $queryResults->queryToTable('',true,"<a href=" . $_SERVER['PHP_SELF'] . 

//                                                "?table=$this->tableName&id=%id%>View</a>");

                     break;

        case 'edit': $return = "<tr><td>$this->columnName</td><td>$this->value</td></tr>";

                     break;

      } // switch

      return $return;

   } // toHTML;

/*   function toHTML( $type = 'view' ) {

      if ( isset( $id ) && $id ) {

         $theRow = new DBRecord( $this, array( 'id' => $id ) );

         return $theRow->toHTML();

      } else {

         $queryResults = new DBQueryHTML( $this->displayQuery );

         return $queryResults->queryToTable('',true,"<a href=" . $_SERVER['PHP_SELF'] . 

                                                "?table=$this->tableName&id=%id%>View</a>");

      } // if..else

   } // function toHTML

*/

} // class DBTable

class DBDatabase {

   public $databaseName;

   protected $tables;

   public $defaultTable;

   public function __construct( $databaseName, $tableDefinitionArray = null) {

      $this->databaseName = $databaseName;

      if ( $tableDefinitionArray ) $this->loadTables ( $tableDefinitionArray );;

      $this->defaultTable = DEFAULT_TABLE;

   } // function __construct

   /*

    * function accepts an array of table definitions. For each element

    * in the array, creates a new DBTable and inserts it into the 

    * $tables property

   */

   public function loadTables ( $tableDefinitionArray ) {

      foreach ( $tableDefinitionArray as $tableName => $definition ) {

         $this->tables[$tableName] = new DBTable($tableName, $definition );

      }

   } // function loadTables

   /*

    * Function accepts a string name of a table, and returns the 

    * appropriate DBTable instance from the tables array

   */ 

   public function getTable( $name ) {

      return ( $this->tables[$name] ? $this->tables[$name] : null );

   }

    /*

     * Function which returns a very basic HTML element to the caller

     * which contains an unordered list. Each element of that list

     * is the name of a table, and a link to the calling program

     * with the name of the table as a parameter

   */

   function toHTML (  ) {

      $output = '<ul>';

      foreach ( $this->tables as $name  => $values ) {

         $output .= "<li><a href=" . $_SERVER['PHP_SELF'] . "?table=$name>$name</a></li>"; 

      }

      $output .= '</ul>';

      return $output;

   } // toHTML

   /*

    * function returns an array with the names of all tables in the

    * database definition

    */

   public function toArray() {

      return array_keys( $this->tables );

   }

   /*

    * function returns all table names in a space separated one line

    * string

    */ 

   public function __toString() {

      return implode( ' ', $this->toArray() );

   }

   /* 

    * function will create a quick and dirty set of edit/update screens, allowing untrained 

    * user to modify database files will give list of tables as links. If link is clicked, 

    * will display tabular list of table contents. table contents list will have edit/delete 

    * buttons, and an Add button at the top. If edit button is clicked, will show one row of

    * information, allow user to modify it, then update the database

    * If add button is clicked, will show one row of information, with defaults filled in, 

    * then values will be inserted into database

   */

   public function doAdmin( $returnPage = 'admin.php' ) {

      $html = "<ul class='db_database_admin_tablelist'>\n";

      foreach ( $this->tables as $tableEntry ) {

         $html .= "<li><a href='$returnPage?command=showtable&tablename=".

                  "$tableEntry->tableName'>$tableEntry->displayName</a></li>\n";

      }

      $html .= "</ul>";

      return $html;

   }

} // class DBDatabase

?>