updated as of: March 30, 2015
last author: Andy Theuninck
BasicModel is the base class of a very light weight ORM (object relational mapping).
Most subclasses are PHP classes that correspond to tables or views in the database.
One advantage of having a table definition in code is it's possible to do runtime
analysis comparing the class definition to the underlying and table and cope with
differences intelligently. It can also generate SQL for simple queries which
typically saves on boilerplate code.
The latest documentation can be found on the Project Wiki
The information below may be out of date.
The BasicModel class has some command line functionality assist in writing
and maintaining other model classes. To generate a new model, run:
php BasicModel.php --new TableName
php BasicModel.php --new-view TableName
This will create a skeleton class named TableNameModel.php. The chief
difference with using --new-view is some functionality that doesn't make
sense in a read-only context is automatically disabled. You can use a regular
model with a view, but attempting to add/change/delete a record may not
work depending on the underlying database.
There are two important properties to define in the new skeleton class.
The first is $name. By default this would be TableName in the
example. In theory per coding standards
both tables should use StudlyCaps, but there's a long ways to go in
fully implementing that. When retrofitting new model classes to old tables,
the new class should use StudlyCaps but the underlying $name does not have
to match - i.e., ProductsModel corresponds to the table products.
The other property that must be defined is $columns. This is a keyed array where
the column keys are the column names and the array values are the column definitions.
A column definition may include the following attributes:
- type - the SQL datatype of the column. This is the only required attribute.
- default - a default value for the column. Omitting it implies NULL.
- primary_key - boolean. This attribute may be applied to multiple columns for a composite primary key.
- index - boolean. Column should be indexed. Default is false.
- not_null boolean. Column cannot be null. If not_null is used, a default must be provided.
- increment boolean. An identity or auto_increment column. Increment columns are automatically indexed.
- ignore_updates boolean. Changes to this column will not trigger an update on the underlying table.
- replaces previous column name. Helps with schema migrates. Use cautiously - breaking other people's installs is still frowned upon.
class TableNameModel extends BasicModel
protected $name = 'TableName';
protected $columns = array(
'tableNameID' => array('type'=>'INT', 'increment'=>true, 'primary_key'=>true),
'columnOne' => array('type'=>'VARCHAR(50)', 'index'=>true),
'columnTwo' => array('type'=>'TINYINT', 'default'=>0),
A third property that may be defined for the class is $unique. Some operations
must be able to isolate a single, unique record in the table. By default, the primary
key column(s) are used for the uniqueness constraint. You may override this by defining
$unique with array of column names. This can useful if a table has an integer ID column
but is most often referenced using another logically unique column.
Lastly, after defining all the $columns, you need to run BasicModel's generator to
create the methods for each column.
php BasicModel.php TableNameModel.php
The constructor takes one argument, an SQLManager connection object. An object
has a method for each column to get or set that column's value. Continuing with
$tableName = new TableNameModel($sqlInstance);
// get column value
$value = $tableName->columnOne();
// set column value
When a model object is first instantiated, it does not contain any data. The get
methods will simply return the default value for the column. Similarly, the set
methods are just updated the object. Data is loaded from the database and written
to the database using the load() and save() methods, respectively.
To load() a record from the database, you assign values to the unique column(s) of
the object. This specifies which record will be loaded from the database. The load method
requires a primary key or unqiueness constraint. A successful load will set all
columns in the object to the values in the underlying record. Load fails and returns
false is the unique column(s) do not have values, if no record in the database matches
the unique column value(s), or if the table itself does not exist. Example:
The reverse of load() is save(). The save method writes the object's current column values
to the database. If the object's unique columns are defined, save() will perform either
an INSERT or an UPDATE depending on whether the corresponding record exists. If the unique
columns do not have values (or no uniqueness constraint has been defined), save() will always
perform an INSERT. Note that save will only write columns that are set in the current instance.
For instance, this would only update products.cost and leave the remainder of the record unchanged:
$product = new ProductsModel($sqlInstance);
Finally, model objects have a find() method for looking up multiple records.
Simply calling find() on a new object will return all records in the table.
Specifically, find returns an array of model objects whose columns are fully populated
with data from the underlying records. These are new objects; find() does not modify
the object that it is called on.
Filters can be applied by adding a second argument containing an SQL operator
such as <, <=, >, >=, =, !=, or <>. Filters are cummulative
and repeated calls will add more conditions to the query. Some examples:
// WHERE tableNameID < 5
// For backwards compatibility purposes, these are identical filters
// The first one does update the object's columns while the
// second one does not
// There is an optional third argument for rare situations
// where the value matches one of the column names.
// WHERE TableName.columnOne <> TableName.columnTwo
// WHERE TableName.columnOne <> 'columnTwo'
$tableName->columnOne('columnTwo', '<>', true);
Because the model classes contain an extensive table definition, they
can analyze the actual database table for discrepancies and suggest
updates. Models can create tables, add columns to tables, and rename
columns. They will not drop columns from tables although they will notify
the user of the discrepancy between the definition and the actual table.
Updates from the command line require a database name to open the correct
connection. There is a confirmation prompt before applying any updates.
php BasicModel.php --update TableNameModel.php
Two extra class properties are relevant to updates. The $preferred_db
can be set to "op" or "trans". This is strictly for the web GUI to apply
updates correctly. CLI updates will always respect the database value
provided. The other property is $normalize_lanes. If this property is true,
updates will be applied to the server's table as well as the corresponding
table on any configured lanes.
To add a new column, simply define it in the array of columns then re-run
the command to generate getters and setters. To rename a column, specify
the name of the column it replaces in the definition. Changing properties
of a column such as type or increment may work depending on the underlying
If a method exists named 'hookAddColumnNAME' (where NAME is the name of the
new column), that method will be called after creating the column. This can
be used to populate data into that column.
Every effort should be made to avoid creating SQL errors in code by
changing the database. INSERTs are the most common problem. Using model classes
to write records can simplify this otherwise manually checking whether a
column exists can work too. Put a dated comment near this kind of code
to give an indication when the schema changed.