API Guide

Introduction

The SWAT Model2 system is an ORM (Object-Relational Mapping) system that uses PHP classes to interface with databases. Under the model we have, there is a PHP class for every table in the database, and each of those classes inherits (directory or indirectly) from SModel2, which implements the core functionality. Any time you grab an entry from the DB, you will get an instance of an SModel2-derived class. SModel2 can also be used for non-DB datasources, such as XML queries, and that used extensively in SDR and SDR Service.

The core API consists of a few classes that implement most of the functionality:

DBI2: the database access layer
SModel2: base class for all model classes
- SDatabaseModel: implements database access for model classes
- SXMLModel: implements XML-related functionality for model classes
genmodel.pl: not a class, but a Perl script that generates custom model files (explained below).

The DBI2 class implements an object-oriented interface to a database. It provides methods for initializing, querying and using transactions. See its documentation for more details. You can use a DBI2 instance to access the database directly. But usually, you will want to use the model objects for your database instead of submitting queries manually, so you shouldn't need to use DBI2 except for complex queries.

Generating Model Classes

Usually, a project will want to have a set of class files for the database it uses and these will go in app/model. You first want to create an SQL file that describes your database (which you should do anyways because you need an SQL file to define the DB). The Perl script reads in this file and generates the classes for you. Now, you must specify some additional parameters for the script to control how it generates the class files. Typically, however, you will want to run it like this (assuming you are inside your app/model directory for your project):

../../../common/scripts/genmodel.pl --dbi=mydb --classprefix=My mydb.sql

This will create a model class for each table in mydb.sql. Each model class will have "My" prefixed to its name (where the name of the class is otherwise going to be the table name). You are also specifying that you want the name of the DBI2 instance to be 'mydb'. You use this name when grabbing the DBI2 object from the DBI2 class:

// grab the DBI2 object from DBI2's static getDBI() method (all DBI2 objects
//   are accessible this way)
$dbi = DBI2::getDBI('mydb');

// OR (this is preferable because it will auto-create the DBI):
$dbi = MyModelBase::getDBI();

// run some queries
$dbi->query('INSERT INTO foo SET bar = "baz"');

You will also get a class called "MyModelBase", from which all your model classes derived and which also contains a method called init(), which you call to set up the database connection. You will call init() before you use your database. You should probably put this method in your standardIncludes.php5 unless it needs to be done on a script-by-script basis pending certain conditions. Any attempt to use your model classes or get the DBI2 instance from your model base class with getDBI() will result in init() being called for you automatically. If this is safe for you (that is, you don't need to pass any options to init()), then don't bother putting init() in a standard place.

The configuration for connecting must be placed in your model base class. A skeleton is generated for you by genmodel.pl, but you obviously have to fill it in. The following is an example of how it would look in your model base class:

public static $DB_INFO = array(
        // default set up information
        0 => array(
            'host' => 'mysql-be',   // default host
            'user' => 'usr_mydb',   // default database user (password from below)
            'name' => 'db_mydb'     // database name
        ),

        // users for this database
        'adm_mydb' => 'p4ssw0rd',
        'usr_mydb' => 'p4ssw0rd'
    );

Additionally, you can override the model base configuration with a global variable that has the same structure as what you have in the model base class, but which has higher precedence, if present. Also, not all fields must be present. Only the field you intend to override. The variable must be called "$<DBI name>_DB_INFO".

For both the global variable and the config in the model base class, the first entry must be 0 and point to an array that contains the host, name of the database and default user. After that, each entry is a username => password pair for all the users of the database.

You can specify which user is used to connect, or which server to connect to by calling init() with additional parameters:

// to connect with the admin user instead of the default (regular) user
MyModelBase::init('adm_mydb');

// to connect with the default user, but to the prod server
MyModelBase::init(false, 'mysql-be-yes-i-really-mean-prod');

// to connect as the admin user to the prod server
MyModelBase::init('adm_mydb', 'mysql-be-yes-i-really-mean-prod');

// this also works (these variables MUST be global)
$mydb_DB_USER = 'adm_mydb';
$mydb_DB_SERVER = 'mysql-be-yes-i-really-mean-prod');
MyModelBase::init();

If the variables in the last example are set before init() is called automatically (if you don't call it manually), then the effect is the same as the above examples.

Basic Operations

You can use the object-oriented the interface to access data in the database. The core ways to do this are described below. You can always use the DBI2 associated with your project for advanced queries, and then process the results manually.

Retrieving from the Database

To retrieve a single row from one of your tables, you have several options. You can construct a new object and pass in constraints (SModel version 1 style -- and deprecated), create a new object, then call populated, which is better but still not preferred, or you can use the static retrieve() method on that object. Let's see some examples of how to do this, assuming that you have a table called "Student" in "mydb":

// old way and deprecated
$student = new MyStudent(array('id' => 37));
// -> no easy programmatic way to tell if this fails...

// better, but still not preferred
$student = new MyStudent();
if(!$student->populate(array('id' => 37)))
    print "Could not find student with id 37!<br />";
// -> but now we have a dead student object lying around...

// preferred
$student = MyStudent::retrieve(array('id' => 37));
if($student == null)
    print "Could not find student with id 37!<br />";

The argument to the constructor, populate() and retrieve() is a constraint array as you would use for getList(). This type of array is described in the Guide to Database Constraints. You do NOT simply pass an id to these methods. They will treat the array as invalid and fail before even querying. Note that you can use any set of constraints you want, but they need to return zero or one results. That is, they should uniquely identify a row in the table.

Once you have an object, you can access its attributes using the member variable syntax. You can also use this syntax to change attributes. You cannot, however, add new attributes. Nor can you set an attribute to a value that is not allowed for the attribute (e.g., if it is an integer, you can't put a string with alphabetic characters in it). Examples:

// retrieve the object
$student = MyStudent::retrieve(array('id' => 37));

// print some information
print "First name: " . $student->firstName . " and last name: " . $student->lastName . '<br />';

// print out all attributes
foreach($student->getAttributes() as $attr)
    print "$attr: " . $student->$attr;

// change the student's first name (but it won't update in the DB...see next section on committing)
$student->firstName = 'Nobody';

You can use the getList() method to grab multiple results at once, trimmed by constraints and limits and possibly sorted. The getList() method is static in SWAT Model2, unlike in the old SModel. The method takes four arguments:

constraints array: specifies constraints used to build WHERE-clause, can be left as an empty array if no constraints are to be applied
limit array: specifies the number of results to return (if empty, returns all results)
order array: specifies the ordering of the results (if empty, no special ordering is done)
count boolean: if true, then return a count of the number of results instead of returning the results themselves

If you want to use the default for any of these parameters, you must use an empty array (not an empty string, or false or null) except for the count parameter, which should be false. Read the Guide toDatabase Constraints" for more on the parameters to getList(). Below are some examples on our Student object:

// list all students in the Student table
$students = MyStudent::getList();

// list all students that are male
$students = MyStudent::getList(array('sex' => 'M'));

// list students that are male or whose first name is "Chris"
$students = MyStudent::getList(array('OR' => array('sex' => 'M', 'firstName' => 'Chris')));

// list the first ten students in the table
$students = MyStudent::getList(array(), array(10));

// list the third group of ten students in the table (offset 20, length 10)
$students = MyStudent::getList(array(), array(20, 10));

// get a count of all male students
$maleCount = MyStudent::getList(array('sex' => 'M'), array(), array(), true);

Committing

If you want to make changes to the database, either adding new entries, or updating existing ones (or deleting them), you must do this all through the commit() method. Essentially, you make changes to the object(s), e.g., by setting attributes and then you call commit() on the object(s) to push the changes to the database. The commit() method figures out whether to do an INSERT or an UPDATE or a DELETE, and what needs to be inserted, updated or deleted.

Let's consider the first case of creating a new entry in the database. You simply create an object, fill it with data and then commit (if the primary key is an auto-increment integer, then you shouldn't set it before committing).

// create object
$student = new MyStudent();

// fill it with data
$student->sex = 'M';
$student->firstName = 'Thomas';
$student->lastName = 'Thompson';

// and then commit to save the changes to the DB
if($student->commit())
    print "Successfully created a new student with ID $student->id!<br />";
else
    print "Failed to create a new student :(<br />";

If you did not fill in attributes that are required to be filled in, or you gave them values not befitting of their type, then commit() will give you an error and will not commit to the database.

Now suppose that you have a student and you need to change their last name because they got married. You follow pretty much the same procedure as above except that the object must have, at some point, been populated or retrieved or grabbed from a list (otherwise, you'll just end up creating a new student):

// retrieve the student object
$student = MyStudent::retrieve(array('id' => 37));

// change the last name
$student->lastName = 'Thompson';

// and commit to update the DB
$student->commit();

You can also delete objects (say, Mr. Thompson died the next day):

// we can retrieve based on things other than IDs, just as long as only one result is returned
$student = MyStudent::retrieve(array('firstName' => 'Thomas', 'lastName' => 'Thompson'));

// mark Mr. Thomspon's entry as deleted
$student->markForDeletion();

// and commit to make the deletion happen
$student->commit()

Advanced

Transactions

Transactions should be used whenever you must use several queries (or commits) to update the database. A transaction is an all or nothing affair. Either the entire transaction completes, or the entire transaction fails. This way, you don't end up with an inconsistent database. SModel2 makes extensive use of transactions, both on the PHP side and for the database, so that objects are as correct as they can be (given the fact that another script could modify the DB "out from under" the current script). If you are doing single commits that are not related to each other, or you are doing commits that result in foreign objects being committed (see Foreign Keys and Objects section below), then you don't need to worry about transactions. However, other operations that consist of multiple related commits should use transactions.

To start a transaction, you use the following simple line of code:

$DBI->startTransaction()

If you are already in a transaction, this line will do nothing other than keep track of how deep the nesting of transaction requests is. I'll explain that a little bit more in just a bit. Once you are done with all your commits and everything was successful, you commit the transaction itself:

$DBI->commitTransaction()

If, however, some step failed and you want to cancel the transaction, you use this method:

$DBI->cancelTransaction()

Now, you can call DBI2::startTransaction() as many times as you want. Each time you call it, DBI2 increments an internal counter representing the transaction "depth" (how deeply nested transactions are). For each level of depth, you must call DBI2::commitTransaction(). So if you start a transaction and then within that, start another transaction, you must call commitTransaction() twice to actually commit the transaction. DBI2::cancelTransaction(), on the other hand, only needs to be called once to cancel the entire set of nested transactions. And once it has been called, any further commit operations will be outside of a transaction, which may not be desirable. So make sure your code doesn't try to keep doing things after a transaction has been cancelled.

Why have nesting? This allows code inside libraries or elsewhere to be reused and be used as part of a bigger transaction. For example, when you do a commit on an object that has foreign objects which need to be committed, SDatabaseModel uses a transaction so that either all of the objects commit or none of them do. But if you want to commit several of these objects in one transaction, you should be able to do that without interfering with the internals of SDatabaseModel. So with nesting, you start a transaction, call commit on some objects, which then start their own (nested) transactions, and then at the end you call commitTransaction() and it all happens as part of one big transaction, which is what you want. You don't have to know or care about the details of SDatabaseModel's mechanism for committing foreign objects, and it doesn't have to care about whether you are using a transaction. It all "just works".

Foreign Keys and Objects

When building model files, genmodel.pl will detect relationships between tables and generate special code for JOINs and such. You can use the objects just as described above, but you have additional features available to you:

Populate main object and dependent objects
getList() with JOINs
Commit main object and dependent objects

Essentially, any class that has one or more foreign keys will have additional attributes, one for each foreign key. Those attributes are object attributes are not saved to the database. Rather, when you populate and indicate you want to use foreign keys, a JOIN query is used instead and the objects in the DB that those foreign keys point to will be pulled in and instances will be created in PHP and attached to the main object in those special attributes.

To put this in more concrete terms, let's the database has a Student table and a Class table. And for simplicity's sake, a student can only belong to one class. That means the Student table has a classId field which is a foreign key on the Class table. On the PHP side, the MyStudent class will have a classId attribute and will also have a class attribute which can contain an instance of a MyClass object. When you retrieve a student from the DB, it will also retrieve the class and put it in the class attribute. This is done via a JOIN, so it doesn't make two queries. The following code should hopefully make the situation a little bit more clear:

// grab the student and make sure to get the the class as well (retrieveMulti() instead of retrieve())
$student = MyStudent::retrieveMulti(array('id' => 37));

// you can also say:
$student = MyStudent::retrieveMulti(array('Base.id' => 37));

// print some info about the student
print "First name: " . $student->firstName . ", last name: " . $student->lastName . "<br />";

// and about the class
print "Class name: " . $student->class->name . "<br />";

Notice that when using constraints, you must use a special notation to indicate which field you are talking about. The rules are now simple:

A bare field name (without any dots) refers to that field in the main table.
A prefix of just "Base." (e.g., "Base.id") refers to that field in the main table as well.
All other prefixes start with "Base." and consist of a series of member names followed by the attribute name. For example, to refer to the class name from the above example, we would use "Base.class.name", since the member variable that stores the MyClass object in MyStudent is called "class".

You can also use these mechanisms when committing and SModel2 will do the right thing (i.e., what you expect). So if you have a class already created and want to add a student, you can just set the class field of the new student and commit it and it will automatically set up the classId. Additionally, you can create both a class and a student in the same commit action. That's right, if you update or create dependent objects, commit() will automatically commit those objects too. And as if that's not enough, genmodel.pl creates some convenience methods in the generated classes that you can use: in MyClass, you will have an addStudent() method, which creates a student object and commits it. Copious examples now follow:

Creating new student using long way:

// grab the existing class object
$class = MyClass::retrieve(array('id' => 2));

// create and initialize a new student
$student = new MyStudent();

$student->sex = 'M';
$student->firstName = 'John';
$student->lastName = 'Johnson';

// connect student with class
$student->class = $class;

// commit the student
$student->commit();

Creating new student with addStudent():

// grab existing class object
$class = MyClass::retrieve(array('id' => 2));

// add a new student to the class
$student = $class->addStudent('M', 'John', 'Johnson');
if($student == null)
    print "Failed to create a new student :(<br />";
else
    print "Successfully created a new student: " . $student->id . "<br />";

Creating a new class and a new student:

// create the class and initialize it
$class = new MyClass();

$class->name = 'Some Class';

// create new student and initialize it
$student = new MyStudent();

$student->sex = 'M';
$student->firstName = 'John';
$student->lastName = 'Johnson';

// set the student to belong to the new class
$student->class = $class;

// commit (this will commit both $student and $class)
$student->commit();

Attribute Preferences

Each attribute in an SModel2-derived class has additional metadata which describes how that attribute works. These metadata, or "preferences" as I will call them in this section, are explained below. Each preference has a default, so when creating SModel2-derived classes, you don't have to set anything for an attribute unless you need something other than the default for a particular preference. Normally, you define your attributes in a giant array at the beginning of your class and then pass that array to SModel2::registerAttributes() in your class's constructor:

class MyTable extends SDatabaseModel {
    public static $ATTRIBUTES = array(
        'id' => array(),
        'name' => array('type' => 'string'),
        'email' => array('type' => 'string', 'default' => 'nobody@nowhere.com')
    );

    public function __construct() {
        parent::__construct();

        $this->registerAttributes(self::$ATTRIBUTES);
        // ... other initialization stuff
    }
}

Each key entry in the $ATTRIBUTES array has a value that is another associative array that maps preferences to overridden values for those preferences. Since 'int' is the default value for the 'type' preference, we don't say anything for the 'id' field. But for the 'name' field, we want it to be a string, so we put in array('type' => 'string') to indicate that it is not an 'int', but a 'string'. We could, of course, put array('type' => 'int') for 'id', but it would be unnecessary.

The preferences are described below:

type

Indicates the attribute type, in terms of PHP types at this point. Possible values are:

'int' (default): integer
'string': any string
'float': floating point number
'time': timestamp or date
Anything else: name PHP object class

null

Indicates whether the attribute can be null or not (i.e., whether it has "NOT NULL" or "NULL" next to it in the SQL). If the attribute can be null, then this should be true (the default); otherwise, it should be set to false.

edit

Indicates whether the attribute is allowed to be modified through the API. That is, whether the attribute can be set using code like:

$obj->myField = 'foo';

Note that users can always use the database directly to modify these values, so this can only really be used to catch errors in code, and not as a security mechanism.

array

Indicates whether the attribute is an array instead of a single attribute. This is not useful for database model classes since you can't store arrays in a database as such. However, you can have attributes that are never stored in the database and are only used on the PHP side (such as foreign objects). In this case, you will want to mark these attributes as 'commit' => SModel2::COMMIT_NOSOURCE (see commit below). All elements in the array must be of the type given by type (that is, no heterogenous arrays allowed).

default

Provides the default value for the attribute. For numeric types, the default value is 0, for strings it is the empty string "", and for objects it is null.

commit

Indicates how the attribute should be treated during commit operations. It can be one of the following values:

SModel2::COMMIT_NEVER: never commit this field (useful for auto-increment ID fields)
SModel2::COMMIT_ALWAYS: always commit the field, even if it hasn't changed
SModel2::COMMIT_CHANGED: only commit the field when it has changed since object was loaded, or the last commit
SModel2::COMMIT_FIRST: on the first commit (an INSERT commit), treat as COMMIT_ALWAYS, otherwise, treat as COMMIT_CHANGED
SModel2::COMMIT_FOREIGN: attribute is a foreign object and should be committed in conjunction with its associated foreign key
SModel2::COMMIT_NOSOURCE: attribute doesn't exist in database or other data source and should never be committed or populated

foreign

If the attribute is a foreign key, then this should be an array with three elements:

'table' => name of table that this key references
'on' => attribute in referenced table that this key references
'object' => object attribute that we store the foreign object in (and that attribute should have 'commit' == SModel2::COMMIT_FOREIGN)

If you use the genmodel.pl script to generate your model files, you probably won't need to worry about these preferences. However, it is still useful to know if you need to tweak the behavior of your classes.