Skip to content

Common Architecture

Jump to bottom Edit New page
Andy Theuninck edited this page Sep 18, 2015 · 7 revisions

Lane and Office have a fair number of similarities.

Class Auto Loading

Both rely on class auto loading: Lane uses AutoLoader.php; Office uses FannieAPI.php. It is generally not necessary to include any other file. Always check whether the class exists before including one of these files. All other classes can simply be referenced as needed and they will be loaded on demand.

Both auto load classes provide a static method named listModules([string] $base_class [, [boolean] $include_base_class). This method will return an array of all known classes that inherit from the specified base class (the base class itself is excluded from the list by default). Specific search paths for specific base classes may be defined in those methods.

Plugins

In both Lane and Office a plugin consists of a collection of classes. Classes within plugins will only be auto loaded if the plugin has been enabled. Plugins interact heavily with the auto loader's listModules method. Anywhere listModules is called is somewhere that a plugin can provide an alternate or supplementary class.

Creating Database Structures

Both Lane and Office use a base class named BasicModel to define tables and views. 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
OR
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 classes and 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 migrations. Use cautiously - breaking other people's installs is still frowned upon. Example:
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 getter/setter methods for each column. This step is no longer necessary on Office but still occurs on the Lane.

php BasicModel.php TableNameModel.php

Updating Database Structures

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. CLI synatx:

php BasicModel.php --update datebase_name 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 database.

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. Posting issues before schema changes is encouraged both to give others warning as well as to see if multiple changes can be combined into one [ideally] seamless update.

Querying via BasicModels

While BasicModels were originally created to provide greater flexibility in updating table schemas they have evolved into a simple ORM (Object-relational mapping). The ORM is limited to single table queries and does not support grouping, aggregates, or even some more complex WHERE conditionals. This section lists some common usage of BasicModels and their rough SQL equivalents.

Querying a table with a BasicModel:

$model->find();

vs.

SELECT * FROM table

Querying w/ sorting:

$model->find('column');

vs.

SELECT * FROM table ORDER BY column;

Querying w/ a WHERE clause:

$model->column1(15);
$model->column2('new');
$model->find();

vs.

SELECT * FROM table WHERE column1=15 AND column2='new';

Querying with operators other than equality (supports >, >=, <, <=, =, <>):

$model->column1(15, '>=');
$model->column2('new', '<>');
$model->find();

vs.

SELECT * FROM table WHERE column1>=15 AND column2<>'new';

Getting a single record by primary key:

$model->idColumn(1);
$model->load();

vs.

SELECT * FROM table WHERE idColumn=1;

Saving a record by primary key:

$model->idColumn(1);
$model->column1(20);
$model->save();

vs.

-- if record exists
UPDATE table SET column1=20 WHERE idColumn=1;
-- otherwise
INSERT INTO table (idColumn, column1) values (1, 20);

Deleting a record by primary key:

$model->idColumn(1);
$model->delete();

vs.

DELETE FROM table WHERE idColumn=1;

SQL Abstraction

While no one is actively using anything other than MySQL (or MariaDB), both Lane and Office rely on an abstraction layer class named SQLManager. This class provides functionality to transfer data between servers including in some cases servers using different variants of SQL. The class' methods are generally identical to the old MySQL API. The equivalent of mysql_query is SQLManager::query, equivalent of mysql_num_rows is SQLManager::num_rows, etc. SQLManager also provides helper methods for things like date and time functions that vary widely across SQL implementations.

All database calls should happen via SQLManager. There are no immediate plans to extend support to other databases but this approach has proven useful in bridging the mysql/mysqli/PDO situation and transition.

MySQL calls:

$dbc = mysql_connect($host, $username, $password);
mysql_select_db($database_name, $dbc);
$query = 'SELECT upc, details FROM products';
$result = mysql_query($query, $dbc);
while ($row = mysql_fetch_row($result)) {
    $upc = $row['upc'];
    $upc = $row[0]; // both numeric and named keys
}

Equivalent with SQLManager:

$dbc = new SQLManager($host, $type, $database_name, $username, $password);
$query = 'SELECT upc, details FROM products';
$result = $dbc->query($query);
while ($row = $dbc->fetch_row($result)) {
    $upc = $row['upc'];
    $upc = $row[0]; // both numeric and named keys
}

Prepared statements work like this: the SQLManager::prepare method takes an SQL string with parameter markers and returns a prepared statement. The SQLManager::execute method takes a prepared statement as the first argument and an array of parameter values as the second argument.

$ps = $sql->prepare('
    SELECT upc 
    FROM products 
    WHERE department=? 
        AND tax=?');
$result = $sql->execute($ps, array(5, 0));
Add a custom footer
Clone this wiki locally