<?php

/*

Copyright (c) 2012 Dave Koopman

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

*/

/**

* Class for manipulating rows in a database. This is an abstract class

* and should not be initiated directly.

*

* @since 1.0

*/

abstract class DbTable {

//////////////////////////////////////////////////////////////////////////

/////////////////////////// MEMBERS ////////////////////////////////////

//////////////////////////////////////////////////////////////////////////

/**

* Holds the database connection objects.

*

* We keep an associate array of database connection,

* like this: self::$aDB[$this->dsn]

*

* @var array

*/

public static $aDB = array();

/**

* This variable is the auto-increment, primary key column of the table.

*

* @var int

*/

protected $id;

/**

* Bool of whether this instance may be saved or not.

*

* @var bool

*/

protected $readonly = false;

/**

* This holds the name of the table.

*

* @var string

*/

private $table_name;

/**

* Holds associative array of database keys => values.

*

* @var array

*/

private $database_fields;

/**

* Tells whether the row has been loaded from the database table.

*

* @var bool

*/

private $loaded;

/**

* Tells whether any database_fields have been modified since the

* last load.

*

* @var bool

*/

private $modified;

/**

* Holds associative array of modified fields in keys => bool format

*

* @var array

*/

private $modified_fields;

/**

* Holds the database DSN for the currently connected database.

*

* We need this DSN, because DbTable is a generic object that has an array

* of connections. We need to know which database this particular DbTable

* object is associated with. We keep an associate array of database

* connection, like this: self::$aDB[$this->dsn]

*

* @var string

*/

private $dsn;

private static $object_cache = array();

private static $describe_cache = array();

//////////////////////////////////////////////////////////////////////////

/////////////////////////// METHODS ////////////////////////////////////

//////////////////////////////////////////////////////////////////////////

/**

* Creates an Array of Objects of the given type with the given tuple ids

*

* Populates an array of DbTable objects by selecting all the data in one

* query and then populating the objects. This saves overhead over creating

* the objects in a loop in your code, which will go to the database and load

* the rows one at a time.

*

* @param string $dsn DSN to connect to

* @param string $class_name The kind of classes to create (also to the table name to load from)

* @param array $ids Array of tuple_ids to load

*/

public static function loadArray($dsn,$class_name,$ids)

{

self::connect($dsn);

$aDB = self::$aDB[$dsn];

$objs = array();

$aSQL = "SELECT * FROM $class_name WHERE " . $GLOBALS['primary_keys'][$dsn][$class_name] .

" IN (" . implode(",",$ids) . ")";

$aResult = $aDB->query($aSQL);

if ( DB::isError($aResult) )

return new Error(ERROR_DBCON);

#Error::display("ERROR: (". __CLASS__ ."/". __LINE__ .") Query error: $aSQL b/c".$aResult->getMessage(), 'FATAL');

while ( $aRow = $aResult->fetchrow() )

{

$objs[] = new $class_name($aRow);

}

return $objs;

}

/**

* Fetches a row from the database and loads $this->database_fields

*

* If $this->id is 0, then default values are fetched from the table

* and loaded into $this->database_fields. If $this->id is set to a

* row that doesn't exist, a fatal error occurs.

*

*/

public function load() {

$id = $this->id;

$table_name = $this->table_name;

if ($id == 0) {

if ( isset(self::$describe_cache[$this->table_name]) )

{

$this->database_fields = self::$describe_cache[$this->table_name];

}

else

{

$aSQL = "DESCRIBE `$table_name`";

$aResult = self::$aDB[$this->dsn]->query($aSQL);

$this->database_fields = array();

while ( $aRow = $aResult->fetchRow() ) {

switch ( $aRow['Default'] ) {

case '' :

$aRow['Default'] = null;

break;

case 'CURRENT_TIMESTAMP' :

$aRow['Default'] = Date("Y-m-d H:i:s");

break;

default :

}

$this->database_fields[$aRow['Field']] = $aRow['Default'];

}

self::$describe_cache[$this->table_name] = $this->database_fields;

}

}

else

{

if ( isset( self::$object_cache[$table_name][$id]) )

{

$this->database_fields = self::$object_cache[$table_name][$id];

}

else

{

$aSQL = "SELECT * FROM `$table_name` WHERE {$GLOBALS['primary_keys'][$this->dsn][$table_name]}=".self::$aDB[$this->dsn]->quote($id);

$aResult = self::$aDB[$this->dsn]->query($aSQL);

if ( DB::isError($aResult) )

return new Error(ERROR_BADQUERY);

#Error::display("ERROR: (". __CLASS__ ."/". __LINE__ .") Query error: $aSQL b/c".$aResult->getMessage(), 'FATAL');

$this->database_fields = array();

$numRows = $aResult->numRows();

if (is_int($numRows) && $numRows > 0 )

{

$this->database_fields = $aResult->fetchRow();

$aResult->free();

}

else

{

return new Error(ERROR_IMPROPERUSE);

#Error::display("ERROR (". __CLASS__ ."/". __LINE__ .") with table: $table_name, id: $id", 'FATAL');

#return false;

}

self::$object_cache[$table_name][$id] = $this->database_fields;

$aResult->free();

}

}

$this->loaded = 1;

unset($this->modified_fields);

}

/**

* Sets $this->loaded to 1

*

*/

public function forceLoaded() {

$this->loaded = 1;

}

/**

* Sets $this->loaded to 0

*

*/

public function forceUnloaded() {

$this->loaded = 0;

}

/**

* Returns the value of the field of the current row in the table.

*

* @param string $field

* @return string

*/

public function getField($field) {

if ($this->loaded == 0) {

$this->load();

}

if ( ! array_key_exists($field,$this->database_fields) )

return '';

#return new Error(1);

#Error::display("ERROR: (". __CLASS__ ."/". __LINE__ .") Tried to getField on nonexistent key '$field'".print_r($this, true),"FATAL");

return $this->database_fields[$field];

}

/**

* Returns an associative array of fields => values for the

* currently selected row.

*

* @return array

*/

public function getAllFields() {

if ($this->loaded == 0) {

$this->load();

}

return($this->database_fields);

}

/**

* Returns an array of column names

*

* @return array

*/

public function getColumnNames()

{

return array_keys($this->database_fields);

}

/**

* Returns the primary key of this table row ($this->id)

*

* @return int

*/

public function getID() {

return $this->id;

}

/**

* Returns an array of the column names which are marked as modified

*

* @return array

*/

public function getModifiedColumns()

{

$keys = array();

foreach ( $this->modified_fields as $field => $value )

{

if ( $this->modified_fields[$field] == true )

$keys[] = $field;

}

return $keys;

}

/**

* Establishes a connection to the passed DSN

*

* The connection object that results is stored in the static

* class array self::$aDB[$dsn]

*

* @param string $dsn

*/

static function connect($dsn) {

if ( !isset(self::$aDB[$dsn])) {

self::$aDB[$dsn] = DB::connect( $dsn );

if ( DB::isError( self::$aDB[$dsn] ) )

return new Error(ERROR_DBCON);

//Error::display("Could not connect to $dsn (". __CLASS__ ."/". __LINE__ .") b/c: ".self::$aDB[$dsn]->getMessage(), 'FATAL');

self::$aDB[$dsn]->setFetchMode( DB_FETCHMODE_ASSOC );

# $result = self::$aDB[$dsn]->query("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");

# if ( DB::isError( $result ) )

# return new Error(ERROR_BADQUERY);

#

# $result = self::$aDB[$dsn]->query("SET AUTOCOMMIT = 0");

# if ( DB::isError( $result ) )

# return new Error(ERROR_BADQUERY);

}

}

/**

* Fetches a row from the table and initializes this object.

*

* This method is generally called from the constructor of

* extended objects of DbTable. $tuple_id may be empty, in which

* case a default object is created, rather than fetching a row

* from the table.

*

* $tuple_id may also be an array of data, in which

* case an object containing that data is created rather than asking

* the database. This is useful if you're already doing a query to

* figure out what rows you want and then just want to create objects

* for the returned data.

*

* @param string $dsn

* @param string $table_name

* @param int|array $tuple_id the primary key to fetch, or an array of data to load

*/

protected function initialize($dsn, $table_name, $tuple_id = "")

{

if ( ! isset($GLOBALS['primary_keys'][$dsn][$table_name]) )

{

return new Error(ERROR_CONFIG);

#Error::display("ERROR: (". __CLASS__ ."/". __LINE__ .") GLOBALS['primary_keys'][$dsn][$table_name] does not exist", 'FATAL');

}

self::connect($dsn);

$this->table_name = $table_name;

$this->dsn = $dsn;

if ( is_array($tuple_id) )

{

$this->database_fields = $tuple_id;

$this->id = $tuple_id[ $GLOBALS['primary_keys'][$dsn][$table_name] ];

$this->loaded=1;

}

else

{

$this->id = $tuple_id;

$this->load();

}

#if ( $GLOBALS['dktmp']==2 )

#{ print " about to seg fault 3"; }

}

/**

* Sets the field value.

*

* This sets the field value and marks the associated

* $this->modified_fields. The modified value will not be saved in

* the table until the save() method is called.

*

* @param string $field

* @param string $value

*/

public function setField($field, $value) {

if ($this->loaded == 0) {

if ($this->id) {

$this->load();

};

};

if ( ! array_key_exists($field,$this->database_fields) )

return new Error(ERROR_IMPROPERUSE);

#Error::display("Tried to setField on nonexistent key '$field'".print_r($this, true),"FATAL");

// Only mark as updated if there's a change

if ( $this->id == 0 || $this->database_fields[$field] != $value )

{

$this->database_fields[$field] = $value;

$this->modified = 1;

$this->modified_fields[$field] = true;

}

}

/**

* Sets an array of field values.

*

* This sets the field values and marks the associated

* $this->modified_fields. The modified value will not be saved in

* the table until the save() method is called.

*

* @param array $assoc -- an associative array of form ($field => $value)

*/

public function setFields($assoc) {

if ($this->loaded == 0) {

if ($this->id) {

$this->load();

};

};

foreach ( $assoc as $field => $value )

{

if ( ! array_key_exists($field,$this->database_fields) )

return new Error(ERROR_IMPROPERUSE);

#Error::display("Tried to setFields on nonexistent key '$field'","FATAL");

// Only mark as updated if there's a change

if ( $this->id == 0 || $this->database_fields[$field] != $value )

{

$this->database_fields[$field] = $value;

$this->modified = 1;

$this->modified_fields[$field] = true;

}

}

}

/**

* Deletes this row from the table.

*

*/

public function destroy() {

$id = $this->id;

$table_name = $this->table_name;

if ($id) {

$stmt = "DELETE FROM `$table_name` WHERE {$GLOBALS['primary_keys'][$this->dsn][$table_name]}=".self::$aDB[$this->dsn]->quote($id);

$result = self::$aDB[$this->dsn]->query($stmt);

if ( DB::isError($result) )

return new Error(ERROR_BADQUERY);

#Error::display("ERROR: (". __CLASS__ ."/". __LINE__ .") Query error: $stmt b/c".$result->getMessage(), 'FATAL');

}

}

/**

* Truncates the table. You may want to disable this one, else use with cation.

*

*/

public function truncate() {

$table_name = $this->table_name;

$stmt = "TRUNCATE TABLE `$table_name`";

$result = self::$aDB[$this->dsn]->query($stmt);

if ( DB::isError($result) )

return new Error(ERROR_BADQUERY, $stmt);

}

/**

* Sends "COMMIT" to the database.

*

*/

public function commit() {

self::$aDB[$this->dsn]->query("COMMIT");

}

/**

* Sends "COMMIT" to every database connected in the

* static class array self::$aDB

*

*/

public function commitAll() {

$dsn = array_keys(self::$aDB);

foreach ( $dsn as $value ) {

self::$aDB[$value]->query("COMMIT");

}

}

/**

* Sends "ROLLBACK" to the database.

*

*/

public function rollback() {

self::$aDB[$this->dsn]->query("ROLLBACK");

}

/**

* Sends "ROLLBACK" to every database connected in the

* static class array self::$aDB

*

*/

public function rollbackAll() {

$dsn = array_keys(self::$aDB);

foreach ( $dsn as $value ) {

self::$aDB[$value]->query("ROLLBACK");

}

}

/**

* Sends an INSERT or UPDATE to the database to save any changes.

* If there are no modified_fields, then no UPDATE will be sent.

*

*/

public function save()

{

if ($this->readonly)

return false;

$id = $this->id;

$table_name = $this->table_name;

if (!$id) {

$this->loaded = 0;

};

if ($this->loaded == 0) {

# assume this is a new entity

$stmt = "INSERT INTO `$table_name` (";

if ( is_array($this->database_fields) ) {

foreach ($this->database_fields as $key => $value) {

if (!is_numeric($key)) {

$key = str_replace("'", "\'", $key);

if ($value != "") {

$stmt .= "`$key`,";

}

}

}

}

# Chop last comma

$stmt = ereg_replace(",$", "", $stmt);

$stmt .= ") VALUES (";

if ( is_array($this->database_fields) ) {

foreach ($this->database_fields as $key => $value) {

if (!is_numeric($key)) {

if ($value != "") {

$value = self::$aDB[$this->dsn]->quote($value);

$stmt .= "$value,";

}

}

}

}

# Chop last comma

$stmt = ereg_replace(",$", "", $stmt);

$stmt .= ")";

}

else if ( ! $this->modified )

{

return true;

}

else

{

$stmt = "UPDATE `$table_name` SET ";

foreach ($this->database_fields as $key => $value) {

if (!is_numeric($key)) {

if ($this->modified_fields[$key] == true) {

$value = self::$aDB[$this->dsn]->quote($value);

if ($value == "") {

$stmt .= "`$key` = NULL, ";

} else {

$stmt .= "`$key` = $value, ";

}

}

}

}

# Chop last comma and space

$stmt = ereg_replace(", $", "", $stmt);

$stmt .= " WHERE {$GLOBALS['primary_keys'][$this->dsn][$table_name]}='$id'";

}

$result = self::$aDB[$this->dsn]->query($stmt);

if (DB::isError($result)) {

return new Error(ERROR_BADQUERY);

#Error::display("ERROR: (". __CLASS__ ."/". __LINE__ .") Query error: $stmt b/c".$result->getMessage(), 'FATAL');

}

if ( $this->loaded == 0) {

# Try to get the ID of the new tuple.

$stmt = "SELECT LAST_INSERT_ID() As {$GLOBALS['primary_keys'][$this->dsn][$table_name]}";

$result = self::$aDB[$this->dsn]->query($stmt);

if ( $result->numRows() > 0 ) {

$row = $result->fetchRow();

$proposed_id = $row[$GLOBALS['primary_keys'][$this->dsn][$table_name]];

$this->database_fields[$GLOBALS['primary_keys'][$this->dsn][$table_name]] = $proposed_id;

}

$result->free();

if ($proposed_id > 0) {

$this->loaded = 1;

$this->id = $proposed_id;

}

else

{

return false;

}

}

$this->modified = 0;

unset($this->modified_fields);

self::$object_cache[$table_name][$this->id] = $this->database_fields;

return true;

}

/**

* Changes this record to be readonly or not readonly

*

* @param bool $bool (true for readonly, false for NOT readonly)

*

*/

function setReadOnly( $bool=true )

{

$bool = (bool) $bool;

$this->readonly = $bool;

}

/**

* Retrieves the readonly status of this object

*

* @return bool

*/

function getReadOnly()

{

return $this->readonly;

}

static function convertToAssoc($aObjs,$keyBy=false)

{

if ( ! is_array($aObjs) )

return $aObjs->getAllFields();

$assoc = array();

foreach ($aObjs as $aObj)

{

if ( $keyBy )

$assoc[ $aObj->getField($keyBy) ] = $aObj->getAllFields();

else

$assoc[] = $aObj->getAllFields();

}

return $assoc;

}

public function printme()

{

$aRow = $this->getAllFields();

foreach($aRow as $key => $val)

print $val."\t";

print "\n";

}

}