An MVC Framework
An MVC Framework

The Model Class


Persistence

Obviously, almost any application will need to store it's data objects over more than just a few milliseconds. For persistence we'll use a database. The database will be accessed via the singletons DB().


Singletons

A typical application may only need and thusly have, a single database connection. So, often a singleton pattern is used. But what if you wanted to store some Customer data in a totally separate database. (e.g. credit card data) I did, so this Framework has singletonS. The $metadata allows each data object to come from a different database, if need be. It's all referenced from the GetDB_Conn() function. Another array in the $metadata stores the database connection to each different database. GetDB_Conn() is called with the name of the database as a parameter and it either pulls the connection information from the array, or if it's not present, creates the connection and stores that information.

function getDB_Conn($dbname) {
	global $metadata;

	static $instance = array();
	$dbinfo = $metadata["Databases"][$dbname];

	if( !isset( $instance[$dbname] ) ) {
		$instance[$dbname] = new DB($dbname, $dbinfo["dbhost"], $dbinfo["dbuser"] );
	}

	return( $instance[$dbname] );
}
	

Automatic ORM

I said in the beginning that I liked Active Record but questioned why I need to bother with it at all. So this is what I've done: I've synced the $metadata with the SQL schema such that every Data Object maps directly to a table in the database, field for field. You don't have to do it this way, but there really isn't a reason not to. Your choices are to set the Data Object as Primary or the Database as Primary. Or make them the same and save yourself a huge headache. By aligning the $metadata with the schema I've allowed automatic ORM. Saving an object is the same as saving a row in a table. Without any coding on the developer's part, it's automatic. This gives you a cohesive logical data structure between your application and the datastore. And removes another opportunity for errors to creep in.

As mentioned earlier, the metadata config is in flux. Part of what is changing is the addition of a datastore type along with the application's data type. (e.g. your application can specify data as 'money' while your datastore might only recognize 'numeric'.) So while I'm at it, I'm considering adding a datastore mapping field. Just in case you need to map an object field to a different datastore field name. But this would still require that a data object be contained within a single table. The basic idea will be that data types and field names match unless specifically overridden. Again, supporting my basic premise that you should be able to paint outside the lines without breaking things.

For those who either cannot map data objects directly to tables or perhaps refuse to, there is still a solution within this Framework. Define a set of data objects that map directly to your datastore and then define an aggregate data object that links to the directly mapped objects. Using the features of the Cascading Setter/Getters your application can interact with the aggregate object without knowing anything about the underlying directly mapped objects. (This can also be done in reverse where the aggregate object is the directly mapped and you create subordinate objects and interact with them.) You don't even have to override the Save() or Retrieve() functions. Just make sure to set Lazyload to 'false' on the linked objects.


Caching Data Objects

It would not be unusual to query SalesOrders that subsequently involved only a handful of Customers. The Query would Retrieve() all the SalesOrders and each SalesOrder would then load it's linked Customer. But if you have a hundred (100) SalesOrders for only five (5) Customers it would be a burden on the database to query each of those five (5) Customers' data a hundred (100) times. So, the Framework caches all retrieved data into a temporary cache. When a Retrieve() or getAll() call is made, the Framework first checks the cache and if not found, only then makes an actual call to the database. The result is five (5) calls to the Customer table instead of one hundred (100). You, the developer, gets the same efficiency whether you retrieve the orders for five (5) Customers or retrieve a hundred (100) SalesOrders involving five (5) Customers. And you didn't have to think about it. Plus, you get the added bonus of only creating five (5) Customer objects.

This cache is dumped immediately after the Request is processed, so there is no need to check for old data in the cache.

Originally, I did think about storing the cache in something like memcache so it would be persistent between Requests. The data objects would contain a timestamp of when they were retrieved and upon entering a Request you could query the database table checking for updates since the timestamp and Retrieve() those objects that changed. While it was tested, it was pulled from the Framework. Under typical usage it just didn't save more resources than it cost. But, if you have an application that would benefit from it, it can be added.

No PDO?

In the code below you'll see that PostgreSQL support is hard coded. But why? Why not use PDO and then the Framework is compatible with any database?

Because. No, really, because in practice you, the developer, will work with one database. Your application is not likely to access several different database systems in one Request. So the code reflects the commonality of accessing a single database system. If you use MariaDB instead of PostgreSQL, then just change the functions called to the appropriate database system. You'll probably only do it once. This Framework was designed with C++ in mind so things like PDO were omitted in lieu of using things like libpq or any other DB interface. And the other reasons go to the next question.

NoSQL?

This Framework was designed with an SQL database in mind. But it didn't need to be. The Model class has a single Save() function that calls either _insert() or _update(). Those functions in turn call the DB functions. If you want to use a NoSQL datastore then replace the DB functions with the equivalent NoSQL. The fact that the data objects store their data in a key-value pair lends itself to be serialized into a NoSQL document.

Another possible adaptation is to add a Serialize() function to the Model class. The object linking would facilitate a cascaded serialization so the result would be one document with all the related data. This document could then be saved in the datastore. And an UnSerialize() function would reverse the process.

Instead of a Serialize()/Unserialize() you could add to_json() and from_json(), which is in the style of C++. This wasn't added here yet because JSON is being handled in the rendering routines. But it seems likely it will be implemented here to handle NoSQL.


The DB Class

The DB Class encapsulates the direct access to the database system you're using. It provides the connection capability and access to the query routines. In addition it provides a function for SQL quoting to escape out any SQL commands in any data.

For standard convenience, the DB Class also provides basic functions for CRUD. This let's the developer access these standard operations and pass a DataMap instead of generating an SQL command. These functions are used by the Model Class for this purpose.

One function specific to certain types of databases is the NextSeq() function which returns the next value in a specified sequence. If you use a database system which does not provide sequences then another mechanism will have to be chosen to get primary key Ids for your data objects.

Other than that, this is your basic DB connector.


define('DB_NONNULL',0001);	// null values are not permitted
define('DB_IDXD',0002);		// array index is Key otherwise array value is 'Key = Value' format

define('DB_STDI',0003);		// do not allow nulls & use array index
define('DB_STDNI',0001);	// do not allow nulls & use array value

class DB {

	private $conn;
	private $status;
	private $error;

	function DB( $dbname, $dbhost, $dbuser, $dbpasswd = "" ) {
		$this->conn = pg_connect( "host=$dbhost dbname=$dbname user=$dbuser");	// use for testing
		//$this->conn = pg_pconnect( "host=$dbhost dbname=$dbname user=$dbuser");	// use for production
		// remember authentication is now done in libpq
		// for other DBs use appropriate authentication
		
		if( $this->conn === false ) {
			fprintf( $GLOBALS['SQLERR'], "An SQL query error occurred. DB failed to connect.\n" );
		} else {
			$this->status = pg_connection_status( $this->conn );
			$this->error = pg_last_error( $this->conn );
		}
	}
	
	function OK() {
		return ( $this->status === PGSQL_CONNECTION_OK );
	}

	function Query( $qrystr ) {
		$dbstmnt = new DBStmnt( $this->conn, $qrystr );
		$dbstmnt->query();
		return $dbstmnt;
	}

	static function Quote( $value ) {
		$value = pg_escape_string( $value );
		$value = "'" . $value . "'";
		
		return $value;
	}

	function errorInfo() {
		$this->error = pg_last_error( $this->conn );
		$err = array();
		$err[] = $this->error;
		$err[] = $this->status;
		
		return $err;
	}

	function Select( $tablename, $field_list, $where = null, $order_by = null ) {
		$query = "SELECT " . $field_list . " FROM " . $tablename;
		if( $where != null ) {
			$query .= " WHERE " . $where;
		}
		if( $order_by != null ) {
			$query .= ' ORDER BY ' . $order_by;
		}
		$query .= ';';

		return $this->query( $query );
	}

	function Insert( $tablename, $data_array ) {
		$this->pair_data($data_array);
		$query = "INSERT INTO " . $tablename . " ( ";
		$query .= implode(", ", array_keys($data_array) );
		$query .= " ) VALUES ( ";
		$query .= implode(", ", $data_array );
		$query .= ");";

		$result = $this->Query( $query );
				
		return $result->result;
	}

	function Update( $tablename, $data_array, $where ) {
		$this->pair_data($data_array, DB_STDNI);
		$query = "UPDATE " . $tablename . " SET ";
		$query .= implode(", ", $data_array);
		$query .= " WHERE " . $where . ";";

		$result = $this->query( $query );

		return $result->result;
	}

	function Delete( $tablename, $keyfld, $keyfldname = "id" ) {
		$query = 'DELETE FROM ' . $tablename . ' WHERE ' . $keyfldname . '=' . DB::Quote($keyfld) . ';';

		$result = $this->query( $query );

		return $result->result;
	}

	function NextSeq( $tablename, $fldname = "id" ) {
		$seqstr = $tablename . "_" . $fldname . "_seq";
		$result = $this->query("SELECT nextval('" . $seqstr . "');");

		return $result->fetchColumn(0);
	}

	function pair_data( &$array, $flags=DB_STDI ) {
		foreach( $array as $idx => $value ) {
			if( ($flags & DB_NONNULL) && ($value === null) ) {
				unset($array[$idx]);
				continue;
			}
			if( $flags & DB_IDXD ) {
				$array[$idx] = $this->quote($value);
			} else {
				$array[$idx] = $idx . '=' . $this->quote($value);
			}
		}
	}


}
	

The DBStmnt Class

The DBStmnt Class is returned by the DB::Query() function and by extension, all other CRUD functions. It is the result statement returned by a query.

The DBStmnt will contain the results of it's query or an error message. If the query returned tuples from the database then these can be retrieved using the 'fetch' functions. Scalars are retrieved by fetching column '0'.

DBStmnts are typically created by the DB::Query() function. You can however, create them on their own if you have a query you want to run repeatedly and want to save a little time in reconstructing the query. For this reason, the DBStmnt actually calls the database interface query() function. Thus you will need to change this if using another database system.

class DBStmnt {

	private $conn;
	private $query;
	private $binds;
	private $result;
	private $status;

	function DBStmnt( $conn, &$query ) {
		$this->conn = $conn;
		$this->query = $query;
	}

	function query() {
		
		$this->result = @pg_query($this->conn, $this->query);
		//              ^ insert error suppression - catch error in model.class.inc
		if( $this->result === false ) {
			$this->status = pg_last_error($this->conn);
		}
	}

	function fetch_assoc( $row = null ) {
		return pg_fetch_assoc( $this->result );
	}

	function errorInfo() {
		$err = array();
		$err[] = pg_result_error( $this->result );

		return $err;
	}


	function rowCount() {
		if( is_resource($this->result) ) {
			return pg_num_rows( $this->result );
		}
		error_log($this->status);
		return 0;
	}

	function fetch( $row = null ) {
		return $this->fetch_assoc($row);
	}

	function fetchColumn( $fldnum ) {
		return pg_fetch_result( $this->result, $fldnum );
	}
}
	


Back to Top
Top

© 2012 and beyond Lawrence L Hovind - All Rights Reserved