SMF SQL wrapper (php)

smf-sql.php

<?php
#ini_set('display_errors',0);
header('Content-Type: application/json');
interface SQL { function sql(); }
interface RecordSet{ function add(Field $f); function field($name,$as=''); function fields();}
class DB
{
	const USERNAME = 'root', PASSWORD = 'password';
	const HOST = 'localhost', DB_NAME='smf';
	const TABLE = "smf_%s";
	static $current = null;
	
	static function con() { if(self::$current == null) new DB(self::DB_NAME); return self::$current; }
	
	static function table($t) { return preg_match('/^'.sprintf(self::TABLE,'.*?').'$/',$t)>0?$t:sprintf(self::TABLE,$t); }

	function __construct($name)
	{
		if(!mysql_connect(DB::HOST,DB::USERNAME,DB::PASSWORD))
			throw Exception('cant connect');
		mysql_select_db($name);
		$this->result = (object)null;
		self::$current = $this;
	}

	function query(Query $q,$key='id')
	{
		$this->result = array();
		$this->query =  $q->sql();
		$res = mysql_query($this->query);
		$count = mysql_num_rows($res);
		if(!$res or $count<1)
			return;
		if($count == 1)
			$this->result = (object)mysql_fetch_object($res);
		else
			while($row = mysql_fetch_object($res))
			{
				$id = property_exists($row,$key)?$row->id:count($this->result);
				$o = (object)null;
				foreach($row as $t => $v)
					$o->$t = self::getCasted($v);
				$this->result[$id]= $o;
			}
		$this->count = $count;
	}
	
	function getCasted($v)
	{
		if(is_null($v))
			return null;
		if(is_bool($v))
			return (boolean)$v;
		if(is_int($v))
			return intval($v);
		if(is_float($v))
			return floatval($v);
		if(is_numeric($v))
			return doubleval($v);
		return $v;
	}

	function requery()
	{
		query($this->query);
	}
	
	public $query = null;
	public $count = 0;
	public $result = array();
	public $error = null;
}

class Table implements SQL
{
	protected $name;
	function __construct($n){ $this->name = DB::table($n); }
	function name($n = ''){ if($n == '') return $this->name; else return $this->name == $n; }
	function sql(){ return "`{$this->name}`"; }
	function __tostring(){ return $this->sql(); }
}

class Field implements SQL
{
	static function find(RecordSet $r, $n, $a)
	{
		if($n==null and $a==null)
			return null;
		foreach($r->fields() as $f)
			if( $f->named($n) || ($n!=null && $f->alias($n)) || ($n == null && $f->alias($a)) )
				return $f;
		return null;
	}	
	
	protected $name;
	var $alias = null;
	private $table = null;
	var $tabled = false;

	function __construct($n, $as='', Table $t=null, $td = false) { $this->name = $n; $this->alias = $as; $this->table = $t; $this->tabled = $td;}

	function name($t = false){ return (($t && self::tabled())?$this->table.'.':'')."`{$this->name}`"; }
	
	function sql(){ return self::name($this->tabled).($this->alias!=null?' AS '.$this->alias:''); }

	function __toString(){ return $this->sql(); }
	
	function table($t = ''){ if($t == null) return $this->table; else return $this->table->named($t); }
	
	function tabled(){ return $this->table != null; }
	
	function named($n){ return $this->name == $n; }
	
	function equal(Field $f){ return $f->named($this->name); }
	
	function alias($a = ''){ $as = $this->alias==null?$this->name:$this->alias; if($a == null) return $as; else return $as == $a; }
}

class Order extends Field implements SQL
{
	const __SQL = " ORDER BY %s %s";
	
	const ASCENDING = true, DESCENDING = false;
	static function direction($asc=true){ return $asc?"ASC":"DESC"; }
	
	private $asc;
	
	function __construct($n, $o = true, Table $t=null) { parent::__construct($n,'',$t); $this->asc = $o;}
	
	function name($sql = true){ return $sql?parent::name():$this->name;}
	
	function sql(){ return sprintf(self::__SQL,self::name(),self::direction($this->asc)); }
}

class Limit implements SQL
{
	private $count, $offset;

	function __construct($c, $o = 0) { $this->count = $c; $this->offset = $o; }

	function sql() { return " LIMIT ".$this->offset.", ".$this->count; }
	function __toString(){return self::sql();}
}

class Join implements SQL
{
	const __SQL = " LEFT JOIN %s ON %s";

	private $table, $where;
	
	function __construct($t, $where='1=1')
	{
		$this->table = ($t instanceof Table)?$t:new Table($t);
		$this->where = $where;
	}
	
	function sql() { return sprintf(self::__SQL,$this->table, $this->where); }
	
	function __toString() { return $this->sql(); }
}

class Record extends Table implements RecordSet
{
	protected $fields = array();
	
	function __construct($name)
	{
		parent::__construct($name);
		$this->fields=array();
	}
	
	function add(Field $f) { $this->fields[] = $f; }
	
	function field($n,$a='') { return Field::find($this,$n,$a); }
	
	function query()
	{
		$q = new Query($this->name);
		foreach($this->fields as $f)
			$q->add($f);
		return $q;
	}
	function fields() { return $this->fields; }
}

class Query implements SQL, RecordSet
{
	private $fields = array();
	private $tables = array();
	private $order = null;
	var $where = "";
	var $join = null;
	private $limit = null;

	function __construct($name = '')
	{
		$this->tables = array();
		$this->fields=array();
		if($name != '')
			$this->addTable(new Table($name));
	}
	
	function sql()
	{
		$fields = join(', ',$this->fields);
		$tables = join(', ',$this->tables);
		$_j = $this->join;
		$join = ($_j instanceof Join)?$_j->sql():"";
		$where = $this->where==null?"":' WHERE '.$this->where;
		return "SELECT ".$fields." FROM ".$tables.$join.$where.$this->order.$this->limit;
	}

	function add(Field $f) { $this->fields[] = $f; }
	
	function addTable(Table $t) { $this->tables[$t->name()] = $t; }

	function setOrder(Order $o) { $this->limit = ($f = self::field($o->name(false)))==null?null:$o; }
	function SetLimit(Limit $l) { $this->limit = $l; }
	
	function table($n) { return $this->tables[$n]; }
	
	function field($n,$a='') { return Field::find($this,$n,$a); }
	function fields() { return $this->fields; }
}

class User
{
	const ID = 'ID_MEMBER', TABLE = 'members', BANNED = 'log_banned';
	private static $r;

	private static function setWhere($q, $where='1=1')
	{
		$r = self::$r; // retrieves the Recordset
		$_AC = 'is_activated';
		
		$r_id = $r->field(self::ID);
		$r_ac = new Field($_AC,null,$r); // allows us to restrict to active accounts

		$b = new Record(self::BANNED); //creates a record for the banned users tabled
		$b_id = new Field(self::ID,'id',$b); //creates a field for the banned users id
		
		$r_id->tabbed = $b_id->tabbed = true;
		
		//LEFT JOIN $r ON $r_id <> $b_id
		$q->join = new Join($b, "{$r_id->name(true)}<>{$b_id->name(true)}");
		
		return $q->where = "$where AND $r_ac";
	}

	static function init()
	{
		$r = new Record(self::TABLE);
		$r->add(new Field(self::ID,"id",$r,true));
		$r->add(new Field("memberName","name",$r));
		$r->add(new Field("dateRegistered","reg",$r));
		$r->add(new Field("realName",null,$r));
		$r->add(new Field("emailAddress",'email',$r));
		$r->add(new Field("gender",null,$r));
		$r->add(new Field("personalText",'text',$r));
		$r->add(new Field("userTitle",'title',$r));
		$r->add(new Field("birthdate",'birthday',$r));
		$r->add(new Field("posts",null,$r));
		$r->add(new Field("totalTimeLoggedIn","time",$r));
		$r->add(new Field("websiteTitle",'siteName',$r));
		$r->add(new Field("websiteUrl",'site',$r));
		$r->add(new Field("location",$r));
		$r->add(new Field("ICQ",null,$r));
		$r->add(new Field("AIM",null,$r));
		$r->add(new Field("YIM",null,$r));
		$r->add(new Field("MSN",null,$r));
		$r->add(new Field("hideEmail",'emailHide',$r));
		$r->add(new Field("signature",null,$r));
		$r->add(new Field("avatar",null,$r));
		self::$r = $r;
	}

	static function all(Order $o = null, Limit $l = null, DB $db=null)
	{
		if($db==null) $db = DB::con();
		$_LIST = array('id','name','posts','time');
		
		$r = self::$r;
		$q = new Query();
		
		$q->addTable($r);
		foreach($_LIST as $f)
			$q->add($r->field('',$f));
		
		self::setWhere($q);
		
		if($o!=null) $q->setOrder($o);
		if($l!=null) $q->setLimit($l);
		
		if($o!=null)
			$db->query($q,$o->name(false));
		else 
			$db->query($q);
	}

	var $id, $name, $reg, $realName;
	var $email, $gender, $text, $title;
	var $birthday, $posts, $time, $siteName;
	var $site, $location;
	var $ICQ, $AIM, $YIM, $MSN;
	var $emailHide, $signature, $avatar;

	function __construct($var, $key='id', $db=null)
	{
		if($db == null) $db = DB::con();
		$r = self::$r;

		$q = $r->query();
		
		$k = $r->field($key);
		$k->tabled = true;
		
		$q->setOrder(new Order($key));
		
		self::setWhere($q, "{$k->name(true)} LIKE '$var'");
		
		$db->query($q,$key);
		
		foreach($db->result	as $k => $v) $this->$k = $v;
		
		if($this->emailHide == true) unset($this->email, $this->birthday, $this->gender);
		
		unset($this->emailHide);
		
		$db->result = ($db->count>0)?$this:(object)null;
	}

}


class Post
{
	const ID = 'ID_MSG', TABLE = 'messages';
	const THREAD = 'ID_TOPIC',BOARD = 'boards';
	const GUEST = '-1';
	private static $r;
	private static $b;

	private static function setWhere($q,$w='', $j_w = '1=1')
	{
		$r = self::$r;
		$_ID  = 'ID_BOARD';
		$_GST = '%'.self::GUEST.'%'; 
		
		$r_id = new Field($_ID,null,$r);

		$b = Post::board();
		$b_id = $b->field($_ID);
		$b_gr = $b->field('','group');
		
		$b_id->tabled = $r_id->tabled = $b_grp->tabled = true;

		//LEFT JOIN $r ON $r_id = $b_id AND $b_gr LIKE $_GST
		$q->join = new Join($b, "{$b_id->name(true)} = {$r_id} AND {$b_gr->name(true)} LIKE '$_GST' AND ($j_w)");

		return $q->where = $w;
	}
	
	private static function board()
	{
		$b = new Record(self::BOARD);
		$b->add(new Field('ID_BOARD','id',$b,true));
		$b->add(new Field('memberGroups','group',$b));
		$b->add(new Field('ID_MEMBER_STARTED','member',$b));
		return $b;
	}
	
	private static function thread()
	{
		$b = new Record('topics');
		$b->add(new Field(self::THREAD,'id',$b,true));
		return $b;
	}
	
	static function init()
	{
		$r = new Record(self::TABLE);
		$r->add(new Field(self::ID,'id',$r,true));
		$r->add(new Field(self::THREAD,'topic',$r,true));
		$r->add(new Field(User::ID,"member",$r,true));
		$r->add(new Field("posterName","memberName",$r));
		$r->add(new Field("posterTime","time",$r));
		$r->add(new Field("subject",null,$r));
		$r->add(new Field("body",null,$r));
		self::$r = $r;
	}

	static function by_thread($id, Limit $l = null, DB $db=null)
	{
		if($db==null) $db = DB::con();
		$_ORDER = 'time';
		$_FIELDS = array('id','member','topic','time');

		$r = self::$r;
		$q = new Query(self::TABLE);
		$o = new Order($_ORDER,false,$r); // Order by post time.

		$r_t = $r->field('','topic');
		
		foreach($_FIELDS as $_f)
			$q->add($r->field('',$_f));
		
		$q->setOrder($o);
		if($l!=null) $q->setLimit($l);
		
		//WHERE `topic` = '$id'
		self::setWhere($q,"{$r_t->name()}='$id'");
		$db->query($q,$o->name());
	}
//*	
	static function blog($id, Limit $l = null, DB $db=null)
	{
		if($db==null) $db = DB::con();
		$_ORDER = 'time';
		$_FIELDS = array('id','member','topic','time');

		$r = self::$r;
		$t_t = self::thread();
		$q = new Query($t_t->name());
		$q->addTable($r);		
		$o = new Order($_ORDER,false,$r); // Order by post time.
		
		$r_t = $r->field(self::THREAD);
		$r_m = $r->field(User::ID);
		
		$t_m = $t_t->field(self::THREAD);
				
		foreach($_FIELDS as $_f)
			$q->add($r->field('',$_f));
		
		$q->setOrder($o);
		if($l!=null) $q->setLimit($l);
		
		//WHERE `topic` = '$id' AND `poster` = `thread.topic_starter`
		self::setWhere($q,"{$r_t->name(true)}='$id' AND {$r_m->name(true)} = {$t_m->name(true)}");
		$db->query($q,$o->name());
	}
//*/
	var $id, $topic, $Field, $time;

	function __construct($var, $key='id', $db=null)
	{
		if($db == null) $db = DB::con();
		$r = self::$r;
		$q = $r->query();

		$k = $r->field('',$key);
		$k->tabled = true;
		
		$q->setOrder(new Order($key));

		// WHERE $key = $var
		self::setWhere($q,"{$k->name()} = '$var'");
		
		$db->query($q);
		foreach($db->result	as $k => $v)
			$this->$k = $v;
			
		$db->result = ($db->count>0)?$this:(object)null;
	}
}

User::init();
Post::init();
$t = DB::con();

//The things it does atm, just uncomment one line at a time :)
#User::all(); //Returns all the users.
#new User(6); //the parameter is the user ID (can search on user name and other fields by adding 'field_name' as the second parameter.#
#Post::by_thread(1); // parameter is the thread ID.
#new Post(2); // parameter is the post ID (can search on other fields by adding 'field_name' as the second parameter.
#Post::blog(1); // doesnt work.

print json_encode($t);