Subversion Repositories php_library

<?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

*/

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 {

   /*

      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 or manyToMany)

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

   public      $default;      // for not null columns, what value to use if it is null. Also displayed on creation of an insert new row screen

   public      $readOnly;     // if set, an input field will not be created for this column; it will be displayed instead.

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

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

   /* 

      display and data input are based upon these three display templates

      These templates can be modified at runtime by the appropriate functions

      Templates built in assume data will be displayed in a table

      NOTE: these templates have class attributes which may be loaded via a css file to ease formatting

   */

   protected   $HTMLHeaderTemplate = '<td class="db_field_name">~~display_name~~</td>';

   protected   $HTMLValueTemplate =  '<td class="db_string">~~value~~</td>';

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td ><input type="text" name="~~column_name~~" class="db_string"> value="~~value~~"</td>';

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

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

      $this->columnName = $columnName;

      $this->value = $value;

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

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

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

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

      $this->readOnly = $definition['readonly'];

      if ( $definition['default'] == 'null' or $definition['null_ok'] ) {

         $this->null = true;

         $this->default = '';

      }

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

   } // function __construct

   // following three functions simply allow user to set and get values for the different templates

   public function valueTemplate( $newValue = '' ) {

      $returnValue = $HTMLValueTemplate;

      if ($newValue) {

         $HTMLValueTemplate = $newValue;

      }

      return $returnValue;

   }

   public function headerTemplate( $newValue = '' ) {

      $returnValue = $HTMLHeaderTemplate;

      if ($newValue) {

         $HTMLHeaderTemplate = $newValue;

      }

      return $returnValue;

   }

   public function inputTemplate( $newValue = '' ) {

      $returnValue = $HTMLInputTemplate;

      if ($newValue) {

         $HTMLInputTemplate = $newValue;

      }

      return $returnValue;

   }

   /* 

      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 = '' ) {

      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 will return a display field based on template, with the value inside it */

   public function displayHTML ($template = '') {

      return replaceTokens($HTMLValueTemplate, array('~~value~~'=>$this->value));

   } // function displayHTML

   /* function will return a formatted header for column names of tables, etc... */

   public function HTMLColumnName ($template = '') {

      return replaceTokens($HTMLHeaderTemplate, array('~~display_name~~'=>($this->displayName ? $this->displayName : $this->columnName), 

                                                      '~~column_name~~' => $this->columnName));

   } // function HTMLColumnName

   /* function will return an input field */

   public function HTMLInputField ($template = '') {

      return replaceTokens($HTMLInputTemplate, array( '~~display_name~~'=>($this->displayName ? $this->displayName : $this->columnName), 

                                                      '~~column_name~~' => $this->columnName,

                                                      '~~value~~'=>$this->value

                                                    )

                          );

   } // function HTMLInputField

}

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

   class DBColumnBool

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

*/

class DBColumnText extends DBColumn {

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td><textarea name="~~column_name~~" class="db_textarea">~~value~~</textarea>';

   protected   $HTMLValueTemplate =  '<td class="db_textarea">~~value~~</td>';

   public      $HTML = false;       // if true, field contains HTML

} // 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 {

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td><INPUT class="db_bool" type="radio" ~~checked~~ name="~~column_name~~"></td>';

   protected   $HTMLValueTemplate =  '<td class="db_bool">~~value~~</td>';

   public      $falseValues;  // array containing the values considered as the keys. If '' is included, an empty string is considered false also

   /* function will return a display field based on template, with the value inside it */

   public function displayHTML ($template = '') {

      return replaceTokens($HTMLValueTemplate, array('~~value~~'=>($this->value ? 'true' : 'false')));

   } // function displayHTML

   public function HTMLInputField ($template = '') {

      return replaceTokens($HTMLInputTemplate, array( '~~display_name~~'=>($this->displayName ? $this->displayName : $this->columnName), 

                                                      '~~column_name~~' => $this->columnName,

                                                      '~~checked~~'=> ($this->value ? 'checked' : '' ) // puts a check mark in if the value is true

                                                    )

                          );

   } // function HTMLInputField

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

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

      $this->falseValues = $definitions['null values'] ? $definitions['null values'] : array ( 'f' => 1, 'F' => 1, 'n' => 1, 'N' => 1, '0' => 1, '' => 1 );

   } // function __construct

   public function makeSafeSQLValue ( $value = '' ) {

      // 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

} // 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 {

   protected   $HTMLValueTemplate =  '<td class="db_date">~~value~~</td>';

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td ><input type="text" name="~~column_name~~" class="db_date"> value="~~value~~"</td>';

   function makeSafeSQLValue ( $value = '' ) {

      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 {

   protected   $HTMLValueTemplate =  '<td class="db_datetime">~~value~~</td>';

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td ><input type="text" name="~~column_name~~" class="db_datetime"> value="~~value~~"</td>';

   function makeSafeSQLValue ( $value = '' ) {

      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 {

   protected   $HTMLValueTemplate =  '<td class="db_int">~~value~~</td>';

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td ><input type="text" name="~~column_name~~" class="db_int"> value="~~value~~"</td>';

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

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

   public function __constructor( $columnName, $definitions = '', $value = '') {

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

      if ($definition['min']) {

         $this->range['min'] = $definition['min'];

      }

      if ($definition['max']) {

         $this->range['max'] = $definition['max'];

      }

      if ($definition['signed']) {

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

      }

   } // function __constructor

   function makeSafeSQLValue ( $value = '' ) { // 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;

   protected   $HTMLValueTemplate =  '<td class="db_real">~~value~~</td>';

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td ><input type="text" name="~~column_name~~" class="db_real"> value="~~value~~"</td>';

   public function __constructor( $columnName, $definitions = '', $value = '') {

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

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

   } // function __constructor

   function makeSafeSQLValue ( $value = '' ) { // 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 {

   protected   $HTMLValueTemplate =  '<td class="db_password">********</td>';

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td ><input type="password" name="~~column_name~~" class="db_password"> value="~~value~~"</td>';

   function makeSafeSQLValue ( $value = '' ) {

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

      $this->value = 'MD5(' . $this->value . ')'; // then, set it to call the MD5 function MySQL, PostgreSQL, Oracle. MS SQL-Server does not have this function

   }

} // 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

   public      $onDisk = true;   // if false, this is a blob column which contains the file. If true, it is a varchar which contains the path

   protected   $HTMLValueTemplate =  '<td class="db_file">~~value~~</td>';

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td ><input type="file" name="~~column_name~~" class="db_file"> value="~~value~~"</td>';

   public function __constructor( $columnName, $definitions = '', $value = '') {

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

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

   } // function __constructor

} // 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 {

   protected   $remoteTableName;    // the actual table name in the database that is used for lookups

   protected   $remotePrimaryKey;   // column name in $remoteTableName that matches this columns value

   protected   $remoteDisplayField; // a string used in select statement to build a display

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

   protected   $sortOrder;          // if set, will generate a sort by clause to determine display order

   protected   $HTMLValueTemplate =  '<td class="db_file">********</td>';

   protected   $HTMLInputTemplate =  '<td class="db_field_name">~~display_name~~</td><td ><select name="~~column_name~~" class="db_file">~~value~~</select></td>';

   public function __constructor( $columnName, $definitions = '', $value = '') {

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

      $this->remoteTableName = $definition['table'];

      $this->remotePrimaryKey = array($definition['keyfiled']);

      $this->remoteDisplayField = $definition['display_field'];

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

      $this->sortOrder = $definition['sort by'];

   } // function __constructor

   /* function will return an input field */

   public function HTMLInputField ($template = '') {

      $dropDown = makeDropDown ( $this->remoteTableName, 

                                 $this->remotePrimaryKey,

                                 $this->remoteDisplayField,

                                 $this->value

                               );

      return replaceTokens($HTMLInputTemplate, array( '~~display_name~~'=>($this->displayName ? $this->displayName : $this->columnName), 

                                                      '~~column_name~~' => $this->columnName,

                                                      '~~value~~'=>$this->$dropDown

                                                    )

                          );

   } // function HTMLInputField

   // finds value in child table and simply displays it

   public function displayHTML ($template = '') {

      $display = getOneDBValue("select $this->remoteDisplayField from $this->remoteTableName where $this->remotePrimaryKey = $value");

      return replaceTokens($HTMLValueTemplate, array('~~value~~'=>$display));

   } // function displayHTML

} // 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

/*

   This is the basics of a record. Made up of columns, it is designed to hold, create and edit

   a single record of a table

*/

class DBRecord {

   protected $table;

   // The first value is of type DBTable. The primary key columns should be populated

   public function __construct( $table ) {

      if ( $table instanceof DBTable ) { // check to ensure they have passed in a standard table object

         $this->table = $table;

      } else {

         throw new Exception ('DBRecord->__construct: first parameter must by of type DBTable (database.class.php)');

      } // if..else

   } // function __construct

   public function loadFromDatabase ( $keyValues = '' ) {

      /* foreach ( $keyValues as $columnName => $value ) {

         $this->table->columns[$columnName] = $value;

      } */

      require_once( 'DBQuery.class.php' );

      $query = new DBQuery( $this->table->getOneRowQuery( ) );

      $query->useAssociativeArray = true;

      if ($query->run()) {

         if ( $query->rowsAffected == 1 ) {

            // print_r( $query);

            foreach ($query->returnData[0] as $thisColumn => $thisValue ) {

               $this->table->columns[$thisColumn]->value = $thisValue;

            }

         } else {

            throw new Exception ('DBRecord->loadFromDatabase returned ' . $info['count'] . ' rows, expected 1' );

         }

      } else {

         print "$query->error\n";

      }

   } // loadFromDatabase

} // class DBRecord

/*

   A table is made up of columns

   A table has relationships with other tables

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

   A table has an array of columns displayed for choosing a row from a list

   A table has a (possibly null) query to display the list. If null, will default to select *

*/

class DBTable {

   public    $tableName;

   public    $columns;

   public    $displayName;

   public    $displayColumns;

   public    $displayQuery;

   public function __construct( $tableName, $displayName = '', $displayColumns = '', $displayQuery = '', $columnDefinitionArray = '' ) {

      $this->tableName = $tableName;

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

      $this->displayName = ($displayName ? $displayName : $tableName );

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

      $this->displayColumns = ($displayColumns ? $displayColumns : array_keys($columnDefinitionArray) );

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

      $this->displayQuery = ($displayQuery ? $displayQuery : "select * from $this->tableName" );

      // get the column definitions

      $this->columnDefinitionsArrayToObject( $columnDefinitionArray );

   } // function __construct

   // Will create an array of DBColumns based upon the definition in $arr

   // see information in separate file for definition of array

   public function columnDefinitionsArrayToObject ( $arr ) {

      $thisColumn;

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

         switch ( $definitions['type'] ) {

            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 '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

   // parameter keyfields must be an array, with indexes being the key field column names and the values being the values to look for

   public function getOneRowQuery ( ) {

      $select = array();

      $keys = array();

      foreach ( $this->columns as $field ) {

         // print_r( $field );

         $select[] = $field->columnName;

         if ( $field->primaryKey ) {

            $keys[] = "$field->columnName = ". $field->makeSafeSQLValue( );

         } // if

      } // foreach

      return 'select ' . implode(',', $select ) . " from $this->tableName where " . implode( ' and ', $keys );

   } // function getOneRowQuery

}

class DBDatabase {

   public $databaseName;

   public $tables;

   public $displayOptions;

   public function __construct( $databaseName, $tableDefinitionArray = '',  $displayOptions = '' ) {

      $this->databaseName = $databaseName;

      $this->displayOptions = $displayOptions;

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

         $this->tables[$tableName] = new DBTable($tableName, $definition['display name'], $definition['display columns'], $definition['display query'], $definition['field info'] );

      }

   } // function __construct

   /* 

      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() {

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

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

         $html .= "<li><a href='admin.php?command=showtable&tablename=$tableEntry->tableName'>$tableEntry->displayName</a></li>\n";

      }

      $html .= "</ul>";

      return $html;

   }

} // class DBDatabase

?>