Setting up SproutCore with a PHP backend part 2

Note: This part is not finished yet.

This is the second part on how to set up a PHP backend with SproutCore.

Essentials

This part is more about PHP and MySQL than about SproutCore, but it enables you to focus on your application instead of writing much or difficult and complicated PHP code. The PHP code that is part of this tutorial will be directly useful to the Rails REST approach, but it is possible to rewrite it to use the other REST URL Scheme.

Essential in this tutorial is the concept of directly translating resources to table names or PHP classes. This concept will make it easier and more transparent to retrieve data using the same names and classes on both the SC side as the PHP side.

It is also assumed every table always has an column called “id”. This is common practice, but it makes sense to keep it in mind as all the functions you will encounter in this tutorial will depend on it.

Another concept used in this tutorial is the possibility in PHP to program in a functional way and to do similar things as possible in JavaScript. This programming style depends on the use of the PHP eval() function. In JavaScript, this is usually a bad idea, as it is very difficult to prevent misuse as the code is completely open to the world. In PHP, it is much easier to prevent the injection of PHP code into code to be evaluated.

That said, it is still quite possible the code in this page allows for injection of code, so if you encounter a security bug, please feel free to correct it!

In this tutorial, the database used to store the data is MySQL, so the mysql php functions will be used. Please feel free to use this code to create a database independent version and if you do, please post your code here!
This tutorial is based on the article “Going dynamic with PHP” by Jack Herrington, which can be found at http://www.ibm.com/developerworks/opensource/library/os-php-flexobj/

Templates

Database Object Template

The use of templates in PHP is nothing like using templates in C++, but can be best compared to the JavaScript way of extending existing objects.
Most of the actions in regard to tables are exactly the same for every table, except for the table name and columns names.
It therefore makes sense to write a PHP class that contains the standard actions and can set up itself after the table name has been provided.

class DBObjectTemplate_class {
	
	private $_fieldnames = array();
	private $_tablename = "";
	
	function __construct($tablename){
		/** 
			Function to set up the class itself
			It creates the $this->_fieldnames array with all the fieldnames belonging to the table the class is initiated with
			It also creates the $this->[fieldname] properties and the $this->_tablename property
		*/		
		// first retrieve the field names of the table
		$tablename = mysql_real_escape_string($tablename);
		
		$query = "SHOW COLUMNS from " . $tablename;
		$errormessage = "Error setting up the class of table " . $tablename;
		$result = mysql_query($query) or fataldberror($errormessage . ": " . mysql_error());
		$this->_tablename = $tablename;
		$numberofrecords = mysql_num_rows($result); 
               // no need for checking for $numberofrecords < 0, as an empty table seems quite useless to me :-)
		for($index=0;$index<$numberofrecords;$index++){
			$currentrecord = mysql_fetch_array($result);
			$fieldname = $currentrecord['Field'];
			$this->_fieldnames[] = $fieldname;
			$codetoeval = "\$this->$fieldname = \"\";";
			eval($codetoeval);
		}
	}	
}

This class is still empty, in the sense that there are no functions that will do anything. The constructor function retrieves the column names and sets the internal array $_fieldnames to contain those names. In addition, it generates the members using the column names.
Please note the backslash before some of the variables in the code to evaluate: normally PHP would replace those variables with their value, but as we want to have it regarded as a variable by the eval process, we have to escape it to prevent PHP replacing those variables.

Now add a function to load a record:

	function init($id){
		$tmpid = mysql_real_escape_string($id);
		$query = "select * from " . $this->_tablename . " where id = " . $tmpid;
		$errormessage = "Error when retrieving a record from table " . $this->_tablename . " with id " . $tmpid;
		$result = mysql_query($query) or fataldberror($errormessage . ": " . mysql_error(), $query);
		
		// only accept one record
		$numofrecords = mysql_num_rows($result);
		if($numofrecords == 1){
			$currentrecord = mysql_fetch_array($result);
			for($index=0;$index<count($this->_fieldnames);$index++){
				$currentfieldname = $this->_fieldnames[$index];
				$codetoeval = "\$this->$currentfieldname = \$currentrecord['$currentfieldname'];";
				eval($codetoeval);
			}
			$this->_initialised = true;
			return true;
		} else {
			return false;
		}
	}

What this function does is actually quite simple: it retrieves the record with id $id and sets the members of the class provided in $this→fieldnames with the values of the retrieved record. It also sets $this→initialised to true to indicate data has been loaded.
This can be useful to prevent other functions to run.

Now the create function:

	function create(stdClass $data){
		// Function to create a new record in the database.
		// $data is a PHP object
		$querystart = "INSERT into " . $this->_tablename;
		$properties = array();
		$values = array();
		for($index=0;$index<count($this->_fieldnames);$index++){
			$currentfieldname = $this->_fieldnames[$index];
			if(property_exists($data,$currentfieldname)){
				$properties[] = $currentfieldname;
				$currentvalue = eval("return \$data->$currentfieldname");
				$values[] = mysql_real_escape_string($currentvalue);
			}	
		}
		if(count($properties)>0){
			$propertiesquery = join(",",$properties);
			$valuesquery = join(",",$values);
			$query = $querystart . " (" . $propertiesquery . ") VALUES (" . $valuesquery . ")";
			$errormessage = "Error creating a new record in the table " . $this->_tablename;
			mysql_query($query) or fataldberror($errormessage . ": " . mysql_error());
			$lastid = mysql_insert_id();
			$this->init($lastid);
		}
	}

The function is written around iterating through a PHP object using the fieldnames in $this→_fieldnames, to make sure only the appropriate members will be saved. To make sure we start with an object, type hinting is used to prevent a different kind of parameter. This also counts for the update function.

	function update(stdClass $data){
		// function to update an existing record in the database
		// the id property needs to be present in the $data object
		$querystart = "UPDATE " . $this->_tablename . " set ";
		$key_value_sets = array();
		if(isset($data->id)){
			//$data->id MUST have value
			$currentid = $data->id;
			for($index=0;$index<count($this->_fieldnames);$index++){
				$currentfieldname = $this->_fieldnames[$index];
				if(property_exists($data,$currentfieldname)){
					$currentvalue = eval("return \$data->$currenfieldname");
					$tmpkeyvalueset = $currentfieldname . "=" . mysql_real_escape_string($currentvalue);
				}	
			}	
			if(count($key_value_sets)>0){
				$keyvaluequery = join(",", $keyvaluesets);
				$query = $querystart . $keyvaluequery . " where id=" . $currentid;
				$errormessage = "Error updating the existing record with id " . $currentid . " in the table " . $this->_tablename;
				mysql_query($query) or fataldberror($errormessage . ": " . mysql_error());
				// re-init object
				$this->init($currentid); 
			}
		}
	}
	
	function delete(stdObject $data){
		// function to delete the record indicated by $data or the current record if data happens to be null.
		// If the object is not initialised, do nothing
		$query = "";
		if($data == NULL){
			if($this->_initialised){
				// get id
				$currentid = $this->id;
				$query = "DELETE FROM " . $this->_tablename . " WHERE id = " . $currentid;	
			}
		} else {
			// get id
			if(isset($data->id)){
				// $data->id must have a value, therefore isset instead of property_exists
				$currentid = $data->id;
				$query = "DELETE FROM " . $this->_tablename . " WHERE id = " . $currentid;	
			}
		}
		if($query != ""){
			$errormessage = "Error deleting the record with id " . $currentid . " in the table " . $this->_tablename;
			mysql_query($query) or fataldberror($errormessage . ": " . mysql_error());
		}
	}

The delete function can be used in two different ways: one is providing an object to delete from the database and the other is to omit the object and use the id from the current record. Of course, the object needs to be initialised before that can happen, so a check is in place.

The constructor and init, create, update and delete functions make up our template database object.

Using the Database Object Template together with autoload()

There are a few possible ways of using this template. The following piece of code is one of them:

$codetoeval = "class $classname extends DBObjectTemplate_class { function __construct(){ parent::__construct($tablename); } }";
eval($codetoeval);

In combination with the __autoload() function, which enables you to include or require classes as you go, this function enables you to make a copy of the DBObjectTemplate_class, but name it differently. Let’s assume you have a table called products. If you do

 $var = new products_class; 

you would normally have to either write a separate product_class class, or require a file in which the class has been declared. Using this in your autoload() function, it will be created on the fly, without the use of external files or declarations. Of course, it is nice to have a check whether the table actually exists before a class is created, because it could become possible to execute arbitrary code. The autoload function with those checks implemented could look like this one:

function __autoload($classname){
	// generate classes on the fly using the $classname and tablename
	// Classname need to be of the form tablename_class, such as student_class etc
	// If the table does not exist in the DB, the include directory is checked for extra classes
	
	// get table name from $classname
	$lastunderscorepos = strrpos($classname,"_");
	$tablename = substr($classname,0,$lastunderscorepos);
	
	// check whether table actually exists in a way that prevents SQL injection
	$query = "SHOW tables";
	$result = mysql_query($query) or fataldberror("Error checking table existance in database: " . mysql_error());
	$numrows = mysql_num_rows($result);
	$tablefound = false;
	// get the db name
	global $MySQLDBname;
	// compare the class name against the table names in the DB and set $tablefound to true if a match is found
	if($numrows>0){
		for($index=0;$index<$numrows;$index++){
			$currentrecord = mysql_fetch_array($result);
			$fieldname = "Tables_in_" . $MySQLDBname;
			$currenttablename = $currentrecord[$fieldname];
			if($currenttablename == $tablename){
				$tablefound = true;	
			}
		}	
		if($tablefound){
			//match found, create new class
			$codetoeval = "class $classname extends DBObjectTemplate_class { function __construct(){ parent::__construct($tablename); } }";
			eval($codetoeval);
		} else {
			// check for external PHP files to include
			// before requiring the file, check whether it exists
			$filename = "includes/" . $classname . ".php";
			if(file_exists($filename)){
				require_once "includes/" . $classname . '.php';
			} else {
				return false;// do nothing for else, unless this breaks things	
			}
		}
	}
}

This autoload provides a way of including classes based on the table on the fly. If the table does not exist in the database (the name being in $MySQLDBName, declared in for example a config file), the includes directory is checked. PHP normally dies when a file that is required does not exist, and also dies when the type of object declared is not available.
Please keep in mind that this autoload function has been written using a specific naming scheme for classes (which need to end with class, in the case of database tables, it is name_class, such as productclass), so if you have a different naming scheme, do not forget to adjust the function for it!

Database Collection Object

In addition to the Database Object Template, it would also be useful to have a Database Collection Object to prepare a range of Database Objects to be sent to the SC app. So, let’s add one:

class DBCollection_class {
	// collection to hold a set of objects of a specific type
	
	public $records = array();
	public $ids = array();
	
	function __construct($tablename){
		// first find out whether the table name exists.
		// This can be done by initialising one object as the autoload function creating the intended class 
		
		// maybe a check whether $tablename contains php code, which seems unlikely as it would violate the URL
		$tmpobject = eval("return new " . $tablename . "_class;");
		// if the class does not exist, PHP dies here.
		
		if(is_object($tmpobject)){
			// get all ids for this table
			$query = "select id from " . mysql_real_escape_string($tablename);
			$errormessage = "Error while retrieving a collection from table " . $tablename;
			$result = mysql_query($query) or fataldberror($errormessage . ": " . mysql_error());
			$numrows = mysql_num_rows($result);
			if($numrows>0){
				for($index=0;$index<$numrows;$index++){
					$currentrecord = mysql_fetch_array($result);
					$currentid = $currentrecord['id'];
					$newobject = eval("return new " . $tablename . "_class;");
					$newobject->init($currentid);
					$this->records[] = $newobject;
					$this->ids[] = $currentid;
				}	
			}
		}
	}
}

The collection object first creates an object of the type in $tablename. If it fails, PHP dies.
If PHP doesn’t die, all ids are retrieved from the requested table and an array of objects is generated and added to $this→records and the ids of those records to array $this→ids. This setup makes it possible to send it of directly after encoding in json, as the JSON encoded object can be processed directly by SC.Store.

The creation of a collection can be done like this:

$codeto_eval = "return new DBCollection_class(" . $tablename . ");";
$newcollection = eval($codeto_eval);

and sent back to SC using:

 echo json_encode($newcollection); 

The only thing you have to do now is write the REST interface and create the mapping.

Comments

As soon as I have completed my REST interface to something acceptable, I will also post it here. – Maurits

• couple of things, for update, create and show SC expects a JSON encoded array of records with id and type at a minimum. Additionally for create you must also include guid which should be set to the original temporary id sent in by SC. So your Constructor needs to determine the record type from the class name or ??? and all your methods in your DBObjectTemplateclass need to return an array.