path: root/shared/classes/0sql_row_obj.php

<?php
// TODO
// Improve caching to include anything constructed from the DB
// Fall back to multi key if single key didn't work (so unique key can be null)
// consider replacing the one sql_col class with a bunch of child classes for different types (will work well with defaults() when 5.3.0 comes out)
// make sure things really work with enum
// replace the functionality of hasattrs and getattr somehow - more complex custom queries than refers_to
// learn about MySQL indices and see if I can use them better (AKA multi_key but SQL-supported)
// Make the from-SQL init more polished, take account of things after the last ) like ENGINE and DEFAULT CHARSET
// Make from-SQL init and other stuff support UNIQUE KEY with a name different than the column's (currently ignored)
// Remove support for 'signed' and print notices so I can correct my classes to start using unsigned when necessary
// Consider removing the array-based init and only allowing SQL-based (with some sort of file-based cache so we don't have to init every time, and of course it auto-updated based on mtime)
// Support \-quoting of ' and maybe also " in SQL-based init (write general function given a string to return the part that is one word in SQL?)
// store cache as static info in the proper class instead of in a global class that gets copied by reference into every instance (waiting for PHP 5.3.0)
// Take account of http://dev.mysql.com/doc/refman/5.1/en/silent-column-changes.html
// Put sql_col in its own file
// Allow using different DBs and even different connections for each class/table
// Make sure functions are in the proper order, etc. and consider using standardized PHPDoc commenting, which would be good for SoC
// Clean up the variables I'm using which then can't be column names (after 5.3.0 this will still be an issue because you can't have a static and a member var with the same name)
// Make sql_row_obj::debug a real debug function which is silent by default but can be turned on or replaced by an external function
// Get rid of any dependencies on other PHP code of mine
// Experiment with PDOStatement->fetchObject() (file:///usr/share/doc/php-docs-20071125-r2/en/html/function.PDOStatement-fetchObject.html)
// Change the default fetch() type from BOTH to either assoc or num, check for places that need to manually change it
// Probably a good idea to accomplish some of this with PDO::ATTR_STATEMENT_CLASS (file:///usr/share/doc/php-docs-20071125-r2/en/html/function.PDO-setAttribute.html)
// TODO Don't use rowCount - PDO docs say it doesn't work with all backends
abstract class sql_row_obj { // If the name of this class changes, it must be updated where is_subclass_of() is found
	// PDO object to use for queries
	protected static $pdo;
	// The static cache so each class is only initialized once per page-load (maybe this should really be once per class edit, saved in a file somewhere...)
	private static $cache=array(), $table_cache=array(), $ref_cache=array();
	// These are input by the source class (auto-filled by cache after init)
	protected $table, $columns, $primary_key; // TODO $unique_keys
	// These are loaded from the static cache
	private $auto_increment, $num_key, $misc_key;
	// These are run-time variables
	private $db_values=array(), $values;
	// Sets the PDO object to use
	public static function set_pdo_obj(&$obj) {
		$obj->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		self::$pdo=$obj;
	}
	// Checks if set_pdo_obj has been called yet and complains if it hasn't
	private static function check_pdo_obj() {
		if (!is_object(self::$pdo)) {
			throw new Exception('sql_row_obj::set_pdo_obj() has not been called to set the PDO object to use for queries.');
		}
	}
	// Makes an SQL query using $sql and returns the resulting object
	private static function sql_query($q) {
		self::check_pdo_obj();
		return self::$pdo->query($q);
	}
	public static function sql_quote_string($s) {
		self::check_pdo_obj();
		$r=self::$pdo->quote($s); // We're not supposed to use this because PDO::ODBC doesn't support it
		return $r===false?"'".str_replace("'", "''", $s)."'":$r; # If the PDO call failed, at least fix single quotes
	}
	// Handles debug data TODO something
	private static function debug($type, $text) {
	}
	// Initializes the class by converting the columns given into sql_col objects and caching certain other info in the static cache array
	// TODO should be static (5.3.0)
	private function init_from_array() {
		// Converts from array format to object format so classes can be written in array format
		foreach ($this->columns as $name => $col) {
			if (is_array($col)) {
				$col=new sql_col($col);
				$this->columns[$name]=$col; // This is a copy, not a reference, so we set it back to the array also
			}
			if ($col->auto_increment) {
				$this->auto_increment=$name;
			}
		}
	}
	private function init_constructors() {
		// Fills num and misc constructors with other unique columns
		// TODO check where this is called and see if we really need this first check
		// TODO fix this for multi-column keys - as of now, it's dangerous!
		if (!isset($this->num_key) || !isset($this->misc_key)) {
			foreach ($this->columns as $name => $col) {
				if ($col->unique) {
					if ($col->is_numeric()) {
						if (!isset($this->num_key)) {
							$this->num_key=$name;
							if (isset($this->misc_key)) {
								break;
							}
						}
					} else {
						if (!isset($this->misc_key)) {
							$this->misc_key=$name;
							if (isset($this->num_key)) {
								break;
							}
						}
					}
				}
			}
		}
	}
	// Inserts generated data to the cache so we won't have to do this again
	private function cache_me() {
		self::$cache['table'][get_class($this)]=&$this->table;
		self::$cache['columns'][get_class($this)]=&$this->columns;
		self::$cache['primary_key'][get_class($this)]=&$this->primary_key;
		self::$cache['auto_increment'][get_class($this)]=&$this->auto_increment;
		self::$cache['num_key'][get_class($this)]=&$this->num_key;
		self::$cache['misc_key'][get_class($this)]=&$this->misc_key;
		// Inserts this class into a lookup-table if necessary so we know which tables are serviced by which classes
		if (!isset(self::$table_cache[$this->table])) {
			self::$table_cache[$this->table]=get_class($this);
		}
	}
	// Initializes this class based on the sql table given by $this->table
	// TODO should be static (5.3.0)
	private function init_from_create() {
		$c=self::sql_query('SHOW CREATE TABLE `'.$this->table.'`')->fetchColumn(1);
		$c=explode("\n", $c); // If we split by commas, things could get real messed up so we rely on \n
		unset($c[count($c)-1]); # Removes closing ), but also some options like engine and default charset
		unset($c[0]); # Removes CREATE TABLE `x`
		foreach ($c as $line) {
			$line=trim($line);
			$line=rtrim($line, ',');
			if ($line[0]=='`') {
				$col=new sql_col();
				$name=substr($line, 1, strpos($line, '`', 1)-1);
				$line=trim(substr($line, strlen($name)+2));
				$this->columns[$name]=new sql_col($line);
			} else {
				if (strpos($line, 'PRIMARY KEY') === 0) {
					$line=substr($line, strpos($line, '(')+1);
					$line=substr($line, 0, strrpos($line, ')'));
					$line=explode(',', $line);
					foreach ($line as $col) {
						$col=trim($col);
						$col=trim($col, '`');
						$this->primary_key[]=$col;
					}
				} elseif (strpos($line, 'UNIQUE KEY') === 0) {
					// TODO process multi-column unique keys, multiple unique keys correctly (steal the text processing side from PRIMARY KEY above)
					$line=explode(' ', $line, 3);
					$line=trim($line[2]);
					$line=substr($line, strrpos($line, '('));
					$u=substr($line, 2, strlen($line)-4);
					$this->columns[$u]->unique=true;  // If this is boolean true, we just add UNIQUE to the col def., if a string, we add a named UNIQUE KEY at the end (TODO)
				}
			}
		}
	}
	function __construct() {
		if (!isset($this->table)) {
			throw new Exception('Class '.get_class($this).' doesn\'t provide a table name ($table)');
		}
		// If the class is in the cache already, bring over the cached data by reference,
		// if not, initialize the class and add its data to the cache
		if (isset(self::$cache['columns'][get_class($this)])) {
			//$this->table=&self::$cache['table'][get_class($this)]; // Kinda pointless
			$this->columns=&self::$cache['columns'][get_class($this)];
			$this->primary_key=&self::$cache['primary_key'][get_class($this)]; // Not really necessary, it's not changed by init
			$this->auto_increment=&self::$cache['auto_increment'][get_class($this)];
			$this->num_key=&self::$cache['num_key'][get_class($this)];
			$this->misc_key=&self::$cache['misc_key'][get_class($this)];
		} else {
			if (isset($this->columns)) {
				$this->init_from_array();
			} else {
				$this->init_from_create();
			}
			$this->init_constructors();
			$this->cache_me();
		}
		// Load default values for all columns
		foreach ($this->columns as $name => $col) {
			$this->values[$name]=$col->default;
		}
		// No arguments to constructor - leave values as defaults
		if (func_num_args() == 0) {
			self::debug(get_class($this), 'null constructor');
			return;
		}
		// One argument - this may be one of four things:
		// 1. An array directly from an sql result fetch() (in associative form - TODO accept numeric index),
		// 2. The primary key if the primary key has one column
		// 3. An integer intended to be used to fetch the object from the DB by unique numeric column
		// 4. A string intended to be used to fetch the object from the DB by unique column
		// 5. Just the value to be put into the first column (handled like any arbitrary number of values)
		if (func_num_args() == 1) {
			if (is_array(func_get_arg(0))) {
				// We're assuming that the array comes from sql_result->fetch() - assoc form, so we do from_array(from_db=true), but this may not be wise
				$this->from_array(func_get_arg(0), true);
				self::debug(get_class($this), 'array constructor');
				return;
			}
			$arg=func_get_arg(0);
			if (isset($this->primary_key) && count($this->primary_key) == 1) {
				if (is_numeric($arg) && $this->columns[$this->primary_key[0]]->is_numeric() || !$this->columns[$this->primary_key[0]]->is_numeric()) {
					self::debug(get_class($this), 'primary key constructor ('.$this->primary_key[0].')');
					$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE `'.$this->primary_key[0].'`='.$this->columns[$this->primary_key[0]]->sql_value($arg));
					if ($r->rowCount() == 0) {
						throw new Exception(get_class($this).' object constructed with single argument ('.$arg.') but found no rows with this in the `'.$this->primary_key[0].'` column (PRIMARY_KEY).');
					} else {
						$this->from_array($r->fetch(PDO::FETCH_ASSOC), true);
						return;
					}
				}
			}
			if (is_numeric($arg) && isset($this->num_key)) {
				self::debug(get_class($this), 'numeric constructor');
				$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE `'.$this->num_key.'`='.$this->columns[$this->num_key]->sql_value($arg));
				if ($r->rowCount() == 0) {
					throw new Exception(get_class($this).' object constructed with single numeric argument ('.$arg.') but found no rows with this in the `'.$this->num_key.'` column (UNIQUE numeric).');
				} else {
					$this->from_array($r->fetch(PDO::FETCH_ASSOC), true);
					return;
				}
			} elseif (isset($this->misc_key)) {
				self::debug(get_class($this), 'misc constructor');
				$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE `'.$this->misc_key.'`='.$this->columns[$this->misc_key]->sql_value($arg));
				if ($r->rowCount() == 0) {
					throw new Exception(get_class($this).' object constructed with single value ('.$arg.') but found no rows with this in the `'.$this->misc_key.'` column (UNIQUE non-numeric).');
				} else {
					$this->from_array($r->fetch(PDO::FETCH_ASSOC), true);
					return;
				}
			}
			// We have a table that requires multiple columns to identify a given row
			// and we have the right number of arguments to expect that's what's happening
		} elseif (isset($this->primary_key) && func_num_args() == count($this->primary_key)) {
			self::debug(get_class($this), 'primary key constructor ('.implode(', ', $this->primary_key).')');
			for ($i=0; $i<func_num_args(); $i++) {
				$this->db_values[$this->primary_key[$i]]=func_get_arg($i);
			}
			$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE '.$this->sql_id());
			if ($r->rowCount() == 0) {
				if (count($this->primary_key) == count($this->columns)) {
					$this->values=$this->db_values;
					$this->db_values=array();
				} else
					throw new Exception(get_class($this).' object constructed with '.func_num_args().' values '.$this->sql_id().' but no rows were found (PRIMARY KEY).');
			} else {
				$this->from_array($r->fetch(PDO::FETCH_ASSOC), true);
				return;
			}
		}
		// If we haven't loaded values from the DB or an array, use each argument to fill one field, in order
		if (func_num_args() > count($this->columns)) {
			throw new Exception('new '.get_class($this).'() called with too many arguments (accepts up to '.count($this->columns).' but got '.func_num_args().'.');
		}
		$i=0;
		foreach ($this->columns as $name => $col) {
			$this->values[$name]=func_get_arg($i);
			if (++$i == func_num_args()) {
				break;
			}
		}
	}
	// Takes an array and populates the object with values from the array
	// If $from_db is set true, fills db_values column
	// NOTE: if from_db is true, every column must have a value in the array, even if null
	// Returns the number of columns filled, or false if not given an array
	function from_array($array, $from_db=false) {
		if (!is_array($array)) {
			return false;
		}
		$cols_filled=0;
		foreach ($this->columns as $name => $col) {
			if (array_key_exists($name, $array)) {
				$this->values[$name]=$array[$name];
				if ($from_db) {
					$this->db_values[$name]=$array[$name];
				}
				$cols_filled++;
			} elseif ($from_db) {
				throw new Exception('from_array called with $from_db=true, but column `'.$name.'` not specified.');
			}
		}
		return $cols_filled;
	}
	// Writes the object's data to the database, either by UPDATE or INSERT
	public function write() {
		$q=($this->is_in_db()?'UPDATE':'INSERT INTO').' `'.$this->table.'` SET ';
		$i=0; // Number of columns we've set so far
		$to_change=array();
		foreach ($this->columns as $name => $col) {
			if (!array_key_exists($name, $this->db_values) || $this->values[$name] !== $this->db_values[$name]) {
				$sql_val=$col->sql_value($this->values[$name]);
				if (is_array($sql_val)) {
					throw new Exception('Failed to write '.get_class($this).' object to table `'.$this->table.'` because column `'.$name.'` ('.$sql_val['description'].') '.$sql_val['reason'].' (value: '.$sql_val['value'].')');
				}
				if (++$i > 1) {
					$q.=', ';
				}
				$q.='`'.$name.'`='.$sql_val;
			}
		}
		if ($i == 0) {
			self::debug(get_class($this), '<span style="color: red">write() with no modifications</span>');
			return;
		}
		if ($this->is_in_db()) {
			$q.=' WHERE '.$this->sql_id();
		}
		$r=self::sql_query($q);
		// Fill auto-increment column if it was null before the query
		if (isset($this->auto_increment) && $this->__get($this->auto_increment) === null) {
			$this->__set($this->auto_increment, self::$pdo->lastInsertId());
		}
		// We've just written the current values to the db, so it stands to reason they are now the values in the db
		$this->values_to_db_values();
		return $r;
	}
	// (Re-)Loads data from the database
	public function load() {
		if ($this->is_in_db()) {
			$r=self::sql_query('SELECT * FROM `'.$this->table.'` WHERE '.$this->sql_id());
			$this->from_array($r->fetch(PDO::FETCH_ASSOC), true);
		}
	}
	// Deletes this row from the database and clears db_values array
	public function delete() {
		if ($this->is_in_db()) {
			self::sql_query('DELETE FROM `'.$this->table.'` WHERE '.$this->sql_id());
			$this->db_values=array();
		} else {
			throw new Exception('Tried to delete '.get_class($this).' object that was not in the database.');
		}
	}
	// Returns an SQL clause that will single out the row represented by this object
	// Order of choice is:
	// 1. Primary key
	// 2. Numeric key
	// 3. Misc. key
	// TODO support multi-column unique keys
	function sql_id() {
		if (isset($this->primary_key)) {
			$id=count($this->primary_key)>1?'(':'';
			$i=0;
			foreach ($this->primary_key as $name) {
				if (++$i > 1) {
					$id.=' AND ';
				}
				$id.='`'.$name.'`='.$this->columns[$name]->sql_value($this->db_values[$name]);
			}
			$id.=count($this->primary_key)>1?')':'';
			return $id;
		} elseif (isset($this->num_key) && $this->db_values[$this->num_key] !== null) {
			return '`'.$this->num_key.'`='.$this->columns[$this->num_key]->sql_value($this->db_values[$this->num_key]);
		} elseif (isset($this->misc_key) && $this->db_values[$this->misc_key] !== null) {
			return '`'.$this->misc_key.'`='.$this->columns[$this->misc_key]->sql_value($this->db_values[$this->misc_key]);
		} else {
			throw new Exception('Tried to generate SQL to select unique '.get_class($this).' object, but there were no available unique indicators (primary key, numeric, misc).');
		}
	}
	// If no argument is given, returns whether this object represents a row that is currently in the database.
	// If an argument that evaluates to false is given, clears the db_values array, causing it to be known as not in the db.
	// if an argument that evaluates to true is given, fills the db_values array with values from the values array.
	function is_in_db() {
		if (func_num_args() == 0) {
			// db_values being populated indicates that it is in the database
			return (count($this->db_values) > 0);
		}
		if ($set) {
			$this->values_to_db_values();
		} else {
			$this->db_values=array();
		}
	}
	// Sets cached database values to current values - used in is_in_db(true) and in write()
	function values_to_db_values() {
		foreach ($this->columns as $name => $col) {
			$this->db_values[$name]=$this->values[$name];
		}
	}
	// Returns the SQL code to drop the table from the database
	// TODO this should be static (probably needs 5.3.0)
	function drop_table($ifexists=true) {
		return 'DROP TABLE '.($ifexists?'IF EXISTS ':'').'`'.$this->table.'`';
	}
	// Returns the SQL code to create the table represented by this class
	// TODO this should be static (probably needs 5.3.0)
	function create_table($ifnotexists=true) {
		$q='CREATE TABLE '.($ifnotexists?'IF NOT EXISTS ':'').'`'.$this->table.'` ('."\n";
		$i=1;
		$rows=array();
		foreach ($this->columns as $name => $col) {
			$rows[]="\t".'`'.$name.'` '.$col->describe();
		}
		if (isset($this->primary_key)) {
			$rows[]="\t".'PRIMARY KEY (`'.implode('`, `', $this->primary_key).'`)';
		}
		foreach ($this->columns as $name => $col) {
			// TODO support multi-column unique keys
			if ($col->unique && is_string($col->unique)) {
				$rows[]="\t".' UNIQUE KEY `'.$col->unique.'` (`'.$col->unique.'`)';
			}
		}
		$q.=implode(",\n", $rows)."\n";
		$q.=')';
		return $q;
	}
	// Returns PHP code to define this class with the description of the SQL table already set up
	function to_php() {
		$r="class ".get_class($this)." extends ".get_parent_class($this)." {\n\tprotected \$table='".$this->table."', ";
		if (isset($this->primary_key)) {
			$r.='$primary_key=array(\''.implode('\', \'', $this->primary_key).'\'), ';
		}
		$r.="\$columns=array(\n";
		$i=0;
		foreach($this->columns as $name => $col) {
			$i++;
			$r.="\t\t'$name' => array (\n";
			$cols=$col->to_array();
			$j=0;
			foreach ($cols as $name => $val) {
				$j++;
				if (is_bool($val)) {
					$val=$val?'true':'false';
				} elseif (!is_numeric($val)) {
					$val="'".str_replace("'", "\'", $val)."'";
				}
				$r.="\t\t\t'$name' => $val".($j<count($cols)?',':'')."\n";
			}
			$r.="\t\t)".($i<count($this->columns)?',':'')."\n";
		}
		$r.="\n\t);\n}\n";
		return $r;
	}
	//////// IMPORTANT //////////
	// 
	// For __set(), __get(), __isset(), __unset(), and __call():
	// See PHP Language Reference -> Classes and Objects (PHP 5) -> Overloading
	//
	/////////////////////////////
	// Magic function that sets the value of the columns
	function __set($name, $value) {
		if (array_key_exists($name, $this->columns)) {
			$this->values[$name]=$value;
		} else {
			throw new Exception('Tried to set undefined property \''.$name.'\' of '.get_class($this).' object to \''.$value.'\'.');
		}
	}
	// Magic function that gets the value of the columns
	function __get($name) {
		if (array_key_exists($name, $this->columns)) {
			return $this->values[$name];
		} else {
			throw new Exception('Tried to get undefined property \''.$name.'\' of '.get_class($this).' object.');
		}
	}
	// Magic function that checks if the value of a column is set (not null)
	function __isset($name) {
		if (array_key_exists($name, $this->columns)) {
			return ($this->values[$name] !== null);
		} else {
			throw new Exception('Tried to isset() on undefined property \''.$name.'\' of '.get_class($this).' object.');
		}
	}
	// Magic function that gets angry if you try to unset a column, because they can be null, but not deleted
	function __unset($name) {
		throw new Exception('Tried to unsset() property \''.$name.'\' of '.get_class($this).' object, but sql_row_obj values may not be unset.  Try '.get_class($this).'->'.$name.'=null.');
	}
	// Magic function that's used to forward get_xxx() calls to get('xxx')
	function __call($name, $args) {
		if (substr($name, 0, 4) == 'get_') {
			$col=substr($name, 4);
			return $this->get(substr($name, 4));
		} else {
			throw new Exception('Undefined call to '.get_class($this).'->'.$name.'().');
		}
	}
	// Fetches objects for 'refers to' columns (column given by $name)
	function &get($name) {
		// TODO in 5.3.0, this can be a static variable in each class instead of array[$class]
		if (isset(self::$ref_cache[get_class($this)][$name][$this->__get($name)])) {
			return self::$ref_cache[get_class($this)][$name][$this->__get($name)];
		}
		if (!isset($this->columns[$name])) {
			throw new Exception('Tried to fetch object for `'.$name.'` column of '.get_class($this).' object, but that column does not exist.');
		}
		$col=$this->columns[$name];
		if (!isset($col->refers_to)) {
			throw new Exception('Tried to fetch object for `'.$name.'` column of '.get_class($this).' object, but that column does not refer to another table row.');
		}
		if (!$this->__isset($name)) {
			$null=null;
			return $null;
		}
		list($reftable, $refcol)=explode('.', $col->refers_to, 2); // Should be refclass also
		$obj=self::table_to_obj($reftable);
		if ($obj === null) {
			throw new Exception ('Tried to fetch object for `'.$name.'` column of '.get_class($this).' object, but there is no class available to handle the `'.$reftable.'` table.');
		}
		$r=self::sql_query('SELECT * FROM `'.$reftable.'` WHERE `'.$refcol.'`='.$col->sql_value($this->__get($name)));
		if ($r->rowCount() == 0) {
			return null;
		} elseif ($r->rowCount() == 1) {
			$obj->from_array($r->fetch(PDO::FETCH_ASSOC));
			self::$ref_cache[get_class($this)][$name][$this->__get($name)]=&$obj;
			return $obj;
		} else {
			throw new Exception('Fetching '.$refclass.' object for `'.$name.'` column of `'.$this->table.'` table based on `'.$refcol.'`='.$col->sql_value($this->__get($name)).' and expected one row but got '.$r->rowCount().'.');
		}
	}
	// Returns an instance of whichever class extends sql_row_obj for the given table, or null if none found
	// TODO this shouldn't have to instantiate every class, we need 5.3.0 so this can be fully static
	public static function &table_to_obj($table) {
		if (isset(self::$table_cache[$table])) {
			$obj=new self::$table_cache[$table]();
			return $obj;
		}
		foreach (get_declared_classes() as $class) {
			if (is_subclass_of($class, 'sql_row_obj')) {
				$r=new ReflectionClass($class);
				if (!$r->isInstantiable()) continue;
				unset($r);
				$obj=new $class();
				if ($obj->table == $table) {
					self::$table_cache[$table]=$class;
					return $obj;
				}
			}
		}
		$null=null;
		return $null;
	}
	// Same as table_to_class, but returns the name of the class, not an instance
	public static function table_to_class($table) {
		$obj=self::table_to_obj($table);
		return $obj===null?null:get_class($obj);
	}
}
// This class represents a column in an SQL table definition
class sql_col {
	public $type, $length, $unsigned=false, $charset='utf8', $collate, $not_null=false, $default=null, $auto_increment=false, $unique=false, $comment, $refers_to;
	private static $defaults;
	public function __construct($array=null) {
		if (is_array($array)) {
			// TODO this should probably be a switch inside a foreach
			if (isset($array['type']))
				$this->type=strtoupper($array['type']); // To allow for lower-case types
			if (isset($array['length']))
				$this->length=$array['length'];
			if (isset($array['unsigned']))
				$this->unsigned=$array['unsigned']?true:false; // To allow for non-booleans that can evaluate to boolean
			if (isset($array['charset']))
				$this->charset=$array['charset'];
			if (isset($array['collate']))
				$this->collate=$array['collate'];
			if (isset($array['not_null']))
				$this->not_null=$array['not_null']?true:false;
			if ($this->is_numeric() && isset($array['auto_increment']))
				$this->auto_increment=$array['auto_increment']?true:false;
			if (isset($array['default']))
				$this->default=$array['default'];
			elseif ($this->not_null) { // TODO add the default non-null val for the rest of types (http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html)
				if ($this->is_numeric() && !$this->auto_increment)
					$this->default=0;
				elseif ($this->type == 'ENUM') {
					// $this->default=$this->length; // TODO finish this
				} elseif ($this->is_string())
					$this->default='';
			}
			if (isset($array['unique']))
				$this->unique=$array['unique']?true:false;
			if (isset($array['refers_to']))
				$this->refers_to=$array['refers_to'];
		} elseif (is_string($array)) {
			$type=$array;
			if (strpos($type, ' '))
				list($type, $opts)=explode(' ', $type, 2);
			if (strpos($type, '(') !== false) {
				// TODO differentiate between things with length and ENUM
				$length=substr($type, strpos($type, '('));
				$type=substr($type, 0, strlen($type)-strlen($length));
				$length=substr($length, 1, strlen($length)-2);
				$this->length=$length;
			}
			$this->type=strtoupper($type);
			if (isset($opts)) {
				$opts=explode(' ', $opts);
				for ($i=0; $i<count($opts); $i++) {
					$word=$opts[$i];
					switch (strtoupper($word)) {
					case 'NOT':
						if (strtoupper($opts[++$i]) == 'NULL') {
							$this->not_null=true;
						} else {
							$i--; // We assume it's NULL and backtrack otherwise
						}
						break;
					case 'NULL':
						$this->not_null=false;
						break;
					case 'UNSIGNED':
						$this->unsigned=true;
						break;
					case 'AUTO_INCREMENT':
						$this->auto_increment=true;
						break;
					case 'CHARACTER':
						if (strtoupper($opts[$i+1]) == 'SET') {
							$word.=' '.$opts[++$i];
						} else {
							break;
						}
					case 'COLLATE':
					case 'DEFAULT':
					case 'COMMENT':
						$string=$opts[++$i];
						if ($string == "''") {
							$string='';
						} elseif (substr($string, 0, 1) == "'") {
							// An odd number of ' at the end means an unquoted '
							// The $i<count($opts) is just to avoid infinite loops that shouldn't happen
							//echo strtoupper($word).' '.$string;
							while ( ( strlen($string) - strlen(rtrim($string, '\'')) ) % 2 == 0 && $i<count($opts)) {
								$string.=' '.$opts[++$i];
							}
							$string=str_replace("''", "'", $string);
							$string=substr($string, 1, strlen($string)-2);
						} elseif (strtoupper($string) == 'NULL') {
							$string=null;
						}
						switch (strtoupper($word)) {
						case 'CHARACTER SET':
							$this->charset=$string;
							break;
						case 'COLLATE':
							$this->collate=$string;
							break;
						case 'DEFAULT':
							$this->default=$string;
							break;
						case 'COMMENT':
							if (preg_match('/^refers to(?::| |: )([a-zA-Z0-9_$]+\.[a-zA-Z0-9_$]+)$/', $string, $match)) {
								$this->refers_to=$match[1];
							} else {
								$this->comment=$string;
							}
							break;
						}
						break;
					}
				}
			}
		}
	}
	// True for data types that accept a length parameter, false otherwise
	public function has_length() {
		switch($this->type) {
		case 'VARCHAR':
		case 'CHAR':
		case 'BIT':
		case 'TINYINT':
		case 'SMALLINT':
		case 'MEDIUMINT':
		case 'BIGINT':
		case 'INT':
		case 'INTEGER':
		case 'FLOAT':
		case 'DOUBLE':
		case 'DOUBLE PRECISION':
		case 'DECIMAL':
		case 'DEC':
		case 'ENUM':
			return true;
		default:
			return false;
		}
	}
	// Returns the length parameter given, or a default length for this column
	// TODO make all the defaults smart values, not 10 and 7,3 (find out what MySQL does if left blank)
	public function get_length() {
		if (isset($this->length)) {
			return $this->length;
		} else {
			switch($this->type) {
			case 'VARCHAR':
				return 255;
			case 'CHAR':
				return 1;
			case 'TINYINT':
				return 3;
			case 'BIT':
			case 'SMALLINT':
			case 'MEDIUMINT':
			case 'BIGINT':
			case 'INT':
			case 'INTEGER':
				return 10;
			case 'FLOAT':
			case 'DOUBLE':
			case 'DOUBLE PRECISION':
			case 'DECIMAL':
			case 'DEC':
				return '7,3';
			default:
				return null;
			}
		}
	}
	// True if this is a numeric column, false otherwise
	public function is_numeric() {
		switch($this->type) {
		case 'INT':
		case 'INTEGER':
		case 'SMALLINT':
		case 'TINYINT':
		case 'MINIUMINT':
		case 'BIGINT':
		case 'NUMERIC':
		case 'DEC':
		case 'DECIMAL':
		case 'FLOAT':
		case 'REAL':
		case 'DOUBLE PRECISION':
			return true;
		default:
			return false;
		}
	}
	// True if this is a text column, false otherwise
	public function is_string() {
		switch($this->type) {
		case 'VARCHAR':
		case 'CHAR':
		case 'TEXT':
		case 'ENUM':
		case 'SET':
			return true;
		default:
			return false;
		}
	}
	// Returns the row used to create this column in the CREATE statement
	public function describe() {
		$d=$this->type.($this->has_length()?'('.$this->get_length().')':'');
		if ($this->is_numeric() && $this->unsigned)
			$d.=' UNSIGNED';
		if ($this->is_string()) {
			$d.=' CHARSET '.$this->charset;
			if (isset($this->collate))
				$d.=' COLLATE '.$this->collate;
		}
		if ($this->not_null)
			$d.=' NOT NULL';
		if (isset($this->default))
			$d.=' DEFAULT '.$this->sql_value($this->default);
		if ($this->is_numeric() && $this->auto_increment)
			$d.=' AUTO_INCREMENT';
		if ($this->unique === true)
			$d.=' UNIQUE';
		if (isset($this->comment))
			$d.=' COMMENT '.sql_row_obj::sql_quote_string($this->comment);
		elseif (isset($this->refers_to))
			$d.=' COMMENT '.sql_row_obj::sql_quote_string('refers to '.$this->refers_to);
		return $d;
	}
	// Returns the array necessary to generate this column using the constructor
	public function to_array() {
		$me=get_object_vars($this);
		$defaults=self::defaults();
		$r=array();
		foreach ($defaults as $name => $val) {
			if ($me[$name] !== $val) {
				$r[$name]=$me[$name];
			}
		}
		return $r;
	}
	// Formats the given value for use in an SQL statement in this column - escapes strings and
	// checks that the value is allowed by the column's definition
	public function sql_value($value) {
		if ($value === null) {
			$value='NULL';
			if ($this->not_null && !$this->auto_increment) {
				$value=$this->sql_value_fail('may not be null', $value);
			}
		} else {
			if ($this->has_length() && strlen($value) > $this->length) {
				// TODO warning here about length being over the maximum
				// Needs to have a different way of handling things that
				// aren't actually lengths, like ENUM
			}
			if ($this->is_numeric()) {
				if (!is_numeric($value)) {
					$value=$this->sql_value_fail('must be numeric', sql_row_obj::sql_quote_string($value));
				} elseif ($this->unsigned && $value < 0) {
					$value=$this->sql_value_fail('is unsigned', $value);
				}
			} else {
				$value=sql_row_obj::sql_quote_string($value);
			}
		}
		return $value;
	}
	// Helper for sql_value() - returns the reason for failure, the value that failed (both
	// passed in by sql_value()) and column description in a nice associative array for error handling.
	private function sql_value_fail($reason, $value) { // Just puts together the array for brevity of code
		return array('reason' => $reason, 'value' => $value, 'description' => $this->describe());
	}
	// Returns a (cached) array of the default values of a sql_row instance
	private static function &defaults() {
		if (!isset(self::$defaults)) {
			self::$defaults=get_object_vars(new sql_col());
		}
		return self::$defaults;
	}
}
?>