<?php


/*
 *
 * SQL ActiveRecord psuedo-implementation for PHP5 w/SPL iteration
 * Kyle Drake
 * There's no copyright, do whatever the hell you want with it.
 *
 * I don't recommend using this for production, but it's a nice example of how
 * you can implement a small, database-agnostic object-relational mapper with SPL and AdoDB.
 * I don't remember if it lazy-loads or not.. hah.
 *
 */

/*

CONVENTIONS:

That the primary and foreign keys have the same name (company.company_id=company_drinks.company_id).
Unfortunately, MySQL does not do a good job of storing foreign key relationships, at least with MyISAM tables,
so this convention is used to resolve that.


CREATE:

$companyObj = new SQLActiveRecord($db, 'company');
$companyObj->company_name = "COOL TECH";
$companyObj->company_address = '858 24th Ave. NE';
$companyObj->save();  // Automatically escapes strings for you!

// Want to get the Insert ID? Call $companyObj->Insert_ID()


READ:

$companyObj = new SQLActiveRecord($db, 'SELECT * FROM company');

If you only need a single record:

echo $companyObj->company_name;

// Want to know number of records? Use $companyObj->RecordCount();

Otherwise, you can iterate through records:


foreach($companyObj AS $companyRecord)
{
	// echo $companyRecord->company_name;

	// Iterate through related table:
	
	foreach($companyObj->company_drink AS $drinkRecord)
	{
		// echo $drinkRecord->company_drink_name;
	}
}


UPDATE:

$companyObj = new SQLActiveRecord('SELECT * FROM company WHERE company_id=2');
$companyObj->company_name = "COOLER THAN COOL TECH";
$companyObj->save(); // Saves using the primary id.


DELETE:

$companyObj = new SQLActiveRecord('SELECT * FROM company WHERE company_id=2');
$companyObj->delete(); // Deletes all related data too. Only works for single record ATM, but this is not hard to change.


Tables for testing:

CREATE TABLE `company` (
  `company_id` int(10) unsigned NOT NULL auto_increment,
  `company_name` varchar(255) NOT NULL,
  `company_address` varchar(255) NOT NULL,
  `company_city` varchar(255) NOT NULL,
  PRIMARY KEY  (`company_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `company_drink` (
  `company_drink_id` int(10) unsigned NOT NULL auto_increment,
  `company_drink_name` varchar(255) NOT NULL,
  `company_drink_price` varchar(255) NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`company_drink_id`),
  KEY `company_id` (`company_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


*/


class SQLActiveRecord implements Iterator
{
	private $error = null;
	private $rs;
	private $db;
	
	private $table_name;
	public $primary_keys = Array();
	private $foreign_tables = Array();
	
	
	// __get and __set store field data in this property. The fields are put in this property because if they are put directly into the class,
	// then a print_r() on the data will spit out everything in the object, including the private properties (the worst being the RecordSet).
	// If there is some way to keep it from doing this, I would love to know it.
	
	// Should be array?
	
	public $var_data;
	
	
	// Look for variable locally, then try the fields in $this->var_data. The foreign table objects are stored in $this->, while all the
	// field data should be stored in $this->var_data.
	
	function __get($property)
	{
		if($this->var_data->$property)
		{
			return $this->var_data->$property;
		}
		else
		{
			return null;
		}
	}
	
	
	function __set($property, $value)
	{
		if(isset($this->var_data->$property))
		{
			$this->var_data->$property = $value;
		}
		else
		{
			$this->$property = $value;
		}
	}
	
	
	// Build the object, based on the query given. Create a $db pointer in $this->db.
	
	function __construct($db, $query)
	{	
		$query = trim($query);
		$this->db = &$db;
		
		// If only one word is provided, we assume it is a table select for getting a class with empty fields.
		// build_fields() will create the $this->var_data fields for population using the table and a ADODB method, because
		// ADODB doesn't provide the fields in the RecordSet unless the query has returned results.
		
		if(stripos($query, ' ') === false)
		{
			$this->table_name = $query;
			
			if($this->build_fields($query) == null)
			{
				$this->error = "Could not retreive table fields.";
				return null;
			}
		}
		else
		{
			$this->rs = $this->db->Execute($query);
			
			if($this->rs)
			{	
				if(!$this->RecordCount())
				{
					$error = $db->ErrorMsg();
					
					return null;
				}
				else
				{
					$this->table_name = $this->get_table_name($query);
					
					$this->build();
					
					return 1;
				}
			}
		}
		
		
	}
	
	
	// Just closes the record for now.
	
	public function __destruct()
	{
		$this->close();
	}
	
	
	// Deletes the current record, and also deletes any related data in the foreign tables using the primary key.
	// This only works for a single record at the moment. This is kindof deliberate, I don't like the idea of deleting 
	// An entire set of records. I don't see why you couldn't iterate through the ResultSet and run a delete() each time,
	// but I haven't tried to do this yet.
	
	public function delete()
	{
		$delete_key = $this->primary_keys[0];

		foreach($this->foreign_tables AS $foreign_table)
		{
			$query = 'DELETE FROM ' . $foreign_table . ' WHERE ' . $delete_key . '=' . $this->db->qstr($this->var_data->$delete_key);
			
			$this->db->Execute($query);
		}
		
		
		// Delete this record, for this primary key.
		
		if(@$this->primary_keys[0])
		{

			
			$query = 'DELETE FROM ' . $this->table_name . ' WHERE ' . $delete_key . '=' . $this->db->qstr($this->var_data->$delete_key);
			
			$this->db->Execute($query);
		}
	}
	
	
	// Run INSERT or UPDATE query based on whether the primary_key is present in $this->var_data or not.
	// It now 
	
	public function save()
	{
		$field_names = $this->get_table_fields($this->table_name);
		// $field_names = array_keys($this->rs->fields);
	
		
		if(@$this->primary_keys[0] && $this->primary_keys[0] != "") // Edit
		{
			$query = 'UPDATE ' . $this->table_name . ' SET ';
		}
		else // Create
		{
			$query = 'INSERT INTO ' . $this->table_name . ' SET ';
		}
			
		$i = 0;
		
		foreach($field_names AS $k)
		{
			$query .= $k . '=' . $this->db->qstr($this->var_data->$k);
			
			if($i != (count($field_names) - 1))
			{
				$query .= ', ';	
				$i++;
			}					
		}
		
		if(@$this->primary_keys[0] && @$this->primary_keys[0] != "") // Edit
		{
			$foreign_key = $this->primary_keys[0];
			
			$query .= ' WHERE ' . $this->primary_keys[0] . '=' . $this->db->qstr($this->var_data->$foreign_key);
		}
		
		$rs = $this->db->Execute($query);
		
		if($rs)
		{
			// Save the new primary key value into $this->var_data.
			
			if(count($this->primary_keys) == 0)
			{
				$this->primary_keys = $this->get_primary_keys();
			}


			if($this->primary_keys[0] && $this->primary_keys[0] != "")
			{
				$primary_key = $this->primary_keys[0];

				if($this->var_data->$primary_key == "")
				{
					$this->var_data->$primary_key = $this->db->Insert_ID();
				}
			}


			$rs->Close();
			
			return 1;
		}
		else
		{
			$this->error = "Could not update the record.";
			return null;
		}

	}
	
	
	// This method takes the fields from the ResultSet, and inserts them into var_data.
	// It then finds all foreign relationships and connects them. It uses the identical primary/foreign 
	// key name convention to pull this off. This works, but needs a lot of changes, I don't think the foreign
	// retreival should be handled directly by this method.
	
	private function build()
	{
		$field_names = array_keys($this->rs->fields);
		
		foreach($field_names AS $field_name)
		{
			$this->var_data->$field_name = $this->rs->fields[$field_name];
		}
		
		
		// #### Foreign key relationships
		
		
		// No primary keys? Try to retreive them. 0 means none were found, and it will not attempt to load them again.
		
		if(!$this->primary_keys && $this->primary_keys != 0)
		{
			$this->primary_keys = $this->get_primary_keys();
		}
		
		
		// No table list? Try to retreive it. Returned tables are only the ones that the object believes has foreign relations with the table.
		// I don't see why this has to be run more than once, so I'm not so sure it's a best fit for being called within this method rather than
		// by __construct.
		
		if(!$this->foreign_tables && $this->foreign_tables != 0)
		{
			$this->get_foreign_tables($field_names);
		}
		
		
		foreach($this->foreign_tables AS $foreign_table)
		{
			foreach($this->primary_keys AS $primary_key)
			{
				$query = "SELECT * FROM $foreign_table WHERE $primary_key=" . $this->var_data->$primary_key;
				
				// $rs_f = $this->db->Execute($query);
				
				$this->$foreign_table = new SQLActiveRecord($this->db, $query);
				
				// Put some information about the foreign table in $this->var_data, so that we can see that it's been found when we do
				// a print_r on the iterated row.
				
				if($this->$foreign_table->var_data)
				{
					$this->var_data->$foreign_table = "TABLE RELATIONSHIP - Number of Records: " . $this->$foreign_table->RecordCount();
				}
			}
		}
		
		
		return 1;
	}
	
	
	// For all tables in database, scan for primary keys within main table. If they exist, store these tables in the $this->foreign_tables array.
	
	private function get_foreign_tables($field_names)
	{
		$table_list = $this->get_table_list();
		$this->foreign_tables = Array();
		
		foreach($table_list AS $table)
		{
			if($table != $this->table_name)
			{
				$foundkey = 0;
				
				// Get field list for this table.
				
				$table_fields = $this->get_table_fields($table);
				
				if($this->primary_keys)
				{
					foreach($this->primary_keys AS $primary_key)
					{
						foreach($table_fields AS $table_field)
						{
							if($primary_key == $table_field)
							{
								$foundkey = 1;
							}
						}
					}
				}
				
				if($foundkey == 1)
				{
					$this->foreign_tables[] = $table;
				}
			}
		}
	}
	
	
	// Retreives list of tables in current database from SQL server.
	
	private function get_table_list()
	{
		return $this->db->MetaTables();
	}
	
	
	// Retreive primary keys for this table via SQL server. This could be changed to use a naming scheme (value_id) instead.
	// One thing that bugs me: This returns an array? Why the heck would you want more than one -primary- key in a table?
	
	private function get_primary_keys()
	{
		return $this->db->MetaPrimaryKeys($this->table_name);
	}
	
	
	// Gets table name from SQL query. Probably needs optimization. There might be a better way to do this.
	
	private function get_table_name($query)
	{
		$key_start = 'FROM ';
		$key_end = ' ';
		$table_name = "";
		
		$table_tmp = stristr($query, $key_start);
		
		$i = strlen($key_start);
		
		while($i < strlen($table_tmp))
		{
			if($table_tmp[$i] != $key_end)
			{
				$table_name .= substr($table_tmp, $i, 1);
			}
			else
			{
				$i = strlen($table_tmp);
			}
			
			$i++;
		}
		
		return $table_name;

	}
	
	
	// Gets fields for a specified table.
	
	private function get_table_fields($table)
	{
		return $this->db->MetaColumnNames($table);
	}
	
	
	// This method builds the field properties for the specified table if there is no query to run.
	
	private function build_fields($table)
	{	
		if(!is_array($table_fields = $this->get_table_fields($table)))
		{
			$this->error = "Could not retreive fields for table '$table' using MetaColumnNames.";
			
			return null;
		}
		else
		{
			foreach($table_fields AS $table_field)
			{
				$this->var_data->$table_field = "";
			}
			
			return 1;
		}
	}
	

	// Misc Utility Methods
	
	// TODO: Use SPL for Count().
	
	public function RecordCount()
	{
		return $this->rs->RecordCount();
	}
	
	
	public function Insert_ID()
	{
		if($this->db)
		{
			return $this->db->Insert_ID();
		}
		else
		{
			return null;
		}
	}
	
	
	public function close()
	{
		if($this->rs)
		{
			$this->rs->close();
		}
		
		return 1;
	}
	
	
	public function show()
	{
		$this->show_array($this->var_data);
	}
	
	
	public function show_array($array, $title='', $bool=0)
	{
		$return = "";
		
		if($title != '')
		{
			$return .= $title . " ";
		}
		
		$return .= print_r($array, 1);
		$return = '<pre>' . $return . '</pre>';
		
		
		if($bool == 1)
		{
			return $return;
		}
		else
		{
			echo $return;
		}
	}
	
	
	// Iterator functions. These fill the abstracted methods in the built-in PHP Iterator object.
	
    public function rewind() 
	{
		if($this->rs)
		{
			$this->rs->MoveFirst();
		}
    }
    

	public function valid() 
	{
		if($this->rs)
		{
			return !$this->rs->EOF;
		}
		else
		{
			return 0;
		}
    }
    
	
    public function key() 
	{
		if($this->rs)
		{
			return $this->rs->_currentRow;
		}
		else
		{
			return 0;
		}
    }
	
	
    public function current() 
	{
		if($this->rs)
		{
			return $this->var_data;
			
			// return (object)$this->rs->fields;
		}
		else
		{
			return null;
		}
    }
	
	
    public function next() 
	{
		if($this->rs)
		{
        	$this->rs->MoveNext();
        	
        	if(!$this->rs->EOF)
        	{
        		$this->build();
        	}
        }
    }
	
	
	public function __call($func, $params)
	{
		return call_user_func_array(array($this->rs, $func), $params);
	}

	
	public function hasMore()
	{
		if($this->rs)
		{
			return !$this->rs->EOF;
		}
		else
		{
			return 0;
		}
	}
	
	
	public function count()
	{
		if($this->rs)
		{
			return SplObjectStorage::count($rs->RecordCount());
		}
		else
		{
			return 0;
		}
	}
	

}


?>