Rev 40 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?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
?>