Location: PHPKode > scripts > Base For PostgreSQL > class.base.php
<?
/**
* 1. id column which is integer and Not Null.
* 2. _DONT_ user table name such as user - seems to crash postgres...
* Quick Start Guide to Pear DB
* http://vulcanonet.com/soft/?pack=pear_tut
*
* This base class will once for all eliminate your needs to modify your class code
* every time you want to add/remove a new column to your table structure. It will
* even allow you to create a add/update/delete object interface to _any_ table
* structure without adding one single function!
* Sounds too good to be true - well it isn't  and it's here and it's free.

*/
class Base {
	// Database number types.
	var $_numbers = array('int4', 'int8', 'real');

	// File variables.
	var $_files = array();

	// Master record fields.
	var $_dbMasterFields = array('status', 'timestamp', 'updatetimestamp');


	/**
	* Init basic object parameters.
	*
	* Set initial object parameters based on table structure.
	* http://pear.php.net/package/DB/docs/1.7.6/DB/DB_common.html#methodtableInfo
	*/
	function Base($id = null) {
		$this->class = get_class($this);

		// Use class name as default table name.
		if(!$this->_table)
			$this->_table = $this->class;

		// Extract table structure (prefix with t1.).
		foreach($GLOBALS['db']->tableInfo($this->_table) as $key => $column)
			$this->_arTableStructure[$column['name']] = $column['type'];
		$this->_dbFields = array_keys($this->_arTableStructure);

		// Read object data.
		if($id) {
			$this->id = $id;
			$this->getFields();
		}

		return true;
	}


	/**
	* Debug log.
	*/
	function debugLog($message) {
		error_log(date('Y-m-d H:i:s')." ".$this->class."::".$message."\n", '3', "/tmp/debug.log");
		return true;
	}


	/**
	* Move tmp file and return unique filename.
	*/
	function moveFile($file) {
	// todo: dynamic path !

		$filename = '/var/www/html/slott/backupota/devel2/system/files/'.mktime().((double)microtime() * 1000000).basename($file['tmp_name']);
		rename($file['tmp_name'], $filename);

		return $filename;
	}


	/**
	* Get next available serial id.
	*
	* PHP implementation of SQL nextval.
	*/
	function getSeqId() {
		$sql = "select nextval('objects_id_seq')";
		$res = $GLOBALS['db']->query($sql);
		$row = $res->fetchRow();

		$this->debugLog("Geting id for ".$this->_table." from objects_id_seq: ".$row[0]);

		return $row[0];
	}


	/**
	* Create master object record.
	*
	* Each object using the base class will have a master object record.
	* Creating the master record like this requires carefull planing as
	* it could cause a infinite loop - thus the need to overrule this
	* function in the object class itself.
	*/
	function createMasterRecord() {
		$object = new Object();
		$object->setField('class', $this->class);
		$object->create();
		$this->id = $object->id;
		return true;
	}


	/**
	* Create new record.
	*/
	function create() {
		// Create master record for object.
		if(!$this->createMasterRecord())
			return false;

		// Traverse fields and generate sql statements for set fields.
		foreach($this->_arTableStructure as $field => $type) {
			if(isset($this->$field) && $field != 'id')
				// Numbers must be typecasted.
				if(in_array($type, $this->_numbers))
					$arSql[$field] = (real)$this->$field;
				// Strings must be encapsulated.
				else
					$arSql[$field] = "'".addslashes($this->$field)."'";
		}

		// Add id to sql.
		$sqlFields = "id,";
		$sqlValues = $this->id.",";

		// Build sql statement.
		$sqlFields .= implode(',', array_keys($arSql));
		$sqlValues .= implode(',', $arSql);

		$sql = "insert into ".$this->_table." (".$sqlFields.") values (".$sqlValues.")";
		$res = $GLOBALS['db']->query($sql);
		if (DB::isError($res)) {
			$this->debugLog($res->getDebugInfo());
			return false;
		}
		$this->debugLog("Object from class ".$this->class." created with id: ".$this->id);

		return true;
	}


	/**
	* Update database.
	*/
	function update($updatetimestamp = false) {
		// Check if row has been updated.
		if($updatetimestamp && $updatetimestamp != $this->updatetimestamp) {
			$this->debugLog("Row id ".$this->id." for table ".$this->_table." was dirty when upddating.");
			return false;
		}

		// Traverse fields.
		foreach($this->_arTableStructure as $field => $type) {
			// Strip t2 prefix.
			$field = str_replace('t2.' ,'', $field);
			//echo $field."<br>";
			// Ignore id, updatetimestamp and arrays columns.
			//echo "field: ".$field."-> ".isset($this->$field)."<br>";
			//var_dump($this);
			//die();
			if(isset($this->$field) && $field != 'id' && $field != 'updatetimestamp' && !is_array($this->$field)) {
				// If file field is updated we must remove old.
				if(in_array($field, $this->_files)) {
					$file = new _File($this->_backup[$field]['id']);
					$file->delete();
				}

				// Numbers must be typecasted.
				if(in_array($type, $this->_numbers))
					$arSql[] = $field." = ".(real)$this->$field;
				// Strings must be encapsulated.
				else
					$arSql[] = $field." = '".addslashes($this->$field)."'";
			}
		}

		// Update object data.
		if($arSql) {
			$sql = "update ".$this->_table." set ".implode(',', $arSql)." where id = ".$this->id;
			$this->debugLog("Update sql: ".$sql);
			$res = $GLOBALS['db']->query($sql);
			if (DB::isError($res)) {
				$this->debugLog($res->getDebugInfo());
				return false;
			}
		}

		// Update timestamp.
		$this->updateTimestamp();

		return true;
	}


	/**
	* Update updatetimestamp to now.
	*/
	function updateTimestamp() {
		// Update master object record.
		$sql = "update objects set updatetimestamp = now() where id = ".$this->id;
		$this->debugLog("Update sql: ".$sql);
		$res = $GLOBALS['db']->query($sql);
		if (DB::isError($res)) {
			$this->debugLog($res->getDebugInfo());
			return false;
		}
		
		return true;
	}
	

	/**
	* Change status of object.
	*/
	function setStatus($status) {
		$sql = "update objects set status = ".(int)$status." where id = ".$this->id;
		$this->debugLog("Update sql: ".$sql);
		$res = $GLOBALS['db']->query($sql);
		if (DB::isError($res)) {
			$this->debugLog($res->getDebugInfo());
			return false;
		}
		return true;
	}


	/**
	* Delete file and db record.
	*/
	function delete() {
		// Delete master object record.
		$object = new Object($this->id);
		$object->delete();
		/*
		$sql = "delete from objects where id = ".$this->id;
		$res = $GLOBALS['db']->query($sql);
		if (DB::isError($result)) {
			$this->debugLog("delete: ".$result->getMessage());
			return false;
		}
		*/

		// Delete object data.
		$sql = "delete from ".$this->_table." where id = ".$this->id;
		$res = $GLOBALS['db']->query($sql);
		if (DB::isError($result)) {
			$this->debugLog("delete: ".$result->getMessage());
			return false;
		}

		// Delete any files attached to object.
		foreach($this->_files as $field) {
			// PHP4 workaround.
			$arFile = $this->$field;
			// Remove physical file.
			if(file_exists($arFile['filename'])) {
				$this->debugLog('Unlinking file '.$arFile['filename'].' aka. '.$arFile['name']);
				unlink($arFile['filename']);
			}

			// Remove db file entry.
			$file = new _File($arFile['id']);
			$file->delete();
		}

	}


	/**
	* Set a specific parameter.
	*
	* Also handles special inserts such as a file array.
	*/
	function setField($field, $value=false) {
		// Handle file input.
		if(is_array($value) && $value['tmp_name']) {
			// todo: Look for custom function to handle this file upload.

			// Use generic function.
			$value = $this->genericFileUpload($value);
		}
		// Does field exists in table.
		else if(!in_array($field, $this->_dbFields)) {
			$this->debugLog("Field ".$field." does not exists in table ".$this->_table);
			return false;
		}

		$this->debugLog("setField: ". $field." <= ".$value);

		// Backup existing values - usefull when updating.
		if(isset($this->$field))
			$this->_backup[$field] = $this->$field;

		// Add field and value to object structure.
		$this->$field = $value;
		return true;
	}


	/**
	* Upload file to generic file table and return reference.
	*/
	function genericFileUpload($value) {
		$filename = $this->moveFile($value);

		$this->debugLog("Uploading file ".$value['name']." as ".$filename);

		$file = new _File();
		$file->setField('name', $value['name']);
		$file->setField('filename', $filename);
		$file->setField('type', $value['type']);
		$file->setField('size', $value['size']);
		$file->create();

		return $file->id;
	}


	/**
	* Read fields from database into object.
	*/
	function getFields() {
		// Prefix fields.
		foreach($this->_dbMasterFields as $column)
			$arTmp[] = 't1.'.$column;
		foreach($this->_dbFields as $column)
			$arTmp[] = 't2.'.$column;

		// Generate sql statement.
		$dbFieldList .= implode(',', $arTmp);
		$sql = "
			select ".$dbFieldList."
			from objects t1, ".$this->_table." t2
			where t1.id = t2.id
			and t1.id = ".$this->id;
		$this->debugLog("getFields sql: ".$sql);
		$res = $GLOBALS['db']->query($sql);
		if (DB::isError($result)) {
			$this->debugLog("Error at file::getFields: ".$result->getMessage());
			return false;
		}

		// Read data into object.
		while($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
			foreach($row as $field => $value) {
				$this->debugLog("getField: ".$field." <= ".$value);
				$this->$field = $value;
			}
		$res->free();
		
		// For now seperate query for files.
		foreach($this->_files as $field) {
			$this->debugLog("Expanding field: ".$field." with actual file data from fileId: ".$this->$field);
			// Skip if no file available.
			if($this->$field > 0) {
				$sql = "select name, filename, type, size from _file where id = ".$this->$field;
				$this->debugLog("Get file data sql: ".$sql);
				$res = $GLOBALS['db']->query($sql);
				if (DB::isError($result)) {
					$this->debugLog("getFields: ".$result->getMessage());
					return false;
				}

				// Empty parameter before changing to array.
				unset($arTmp);
				$arTmp['id'] = $this->$field;
				$this->$field = array();
				// Read data into variable.
				while($row = $res->fetchRow(DB_FETCHMODE_ASSOC))
					foreach($row as $key => $value) {
						// Guess this is a PHP4 limitatiton - must construct array and _THEN_ assign array to object variable.
						$this->debugLog("getField: ".$field."[".$key."] <= ".$value);
						$arTmp[$key] = $value;
						// Might work with PHP5.
						//$this->$field[$key] = $value;
					}
				$res->free();

				// PHP4 workaround.
				$this->$field = $arTmp;
			}

		}

		return true;
	}


	/**
	* Return filestream of desired file.
	*
	* Read file from web safe location.
	* This is ideal for non public files - but not web images !
	*
	* This function will handle all needed headers inorder to start stream.
	*/
	function getFile($field) {
		$arFile = $this->$field;
		$file = new _File($arFile['id']);
		$file->getContent();
		return true;
	}


	/**
	* Output XML
	*
	* Optional any XML string may be supplied which will then be added inline to the generated XML.
	*/
	function outputXML($xml = false) {
		
/*		foreach($this->_dbMasterFields as $column)
			$arTmp[] = 't1.'.$column;
		foreach($this->_dbFields as $column)
			$arTmp[] = 't2.'.$column;
var_dump($arTmp);
die();
*/		
		foreach($GLOBALS['db']->tableInfo($this->_table) as $key => $column) {
			// Skip id and timestamp field.
			if($column['name'] != 'id' && $column['name'] != 'timestamp') {
				
				// Expand file array.
				if(is_array($this->$column['name'])) {
					$this->debugLog("found array: ".$column['name']);
					$this->debugLog("size: ".sizeof($this->image));
					$arFile = $this->$column['name'];
					$xml .= "<".$column['name']." id='".$arFile['id']."'>\n";
					foreach($arFile as $key => $value) {
						if($key != 'id')
                                        		$xml .= "<".$key.">".$value."</".$key.">\n";
                                        }
				}
				else
					$xml .= "<".$column['name'].">".$this->$column['name'];

				$xml .= "</".$column['name'].">\n";
			}
		}
		
		return "<".$this->class." id='".$this->id."' timestamp='".$this->timestamp."' updatetimestamp='".$this->updatetimestamp."' status='".$this->status."'>\n".$xml."</".$this->class.">\n";
	}

}
?>
Return current item: Base For PostgreSQL