Difference between revisions of "Interacting with the Database"

Latest revision as of 23:57, 6 February 2014

Introduction

X2Engine uses MySQL for its database architecture. All tables are prefixed with 'x2_' to differentiate them from any other tables in the database and allow for users to install X2Engine without having to create a database specifically for it. Nearly all tables have an 'id' column which serves as the primary key. Relational data is handled through the x2_relationships table which allows for the specification of model types and IDs rather than through join tables. Tables which are used to store records or other user data generally have associated Model classes to allow for easier data manipulation, while some of the more technical and system data focused tables often have no model record and must be accessed/modified in other ways. There are three ways for a developer to access data stored in one of the tables.

Raw SQL

This is the most simplistic and tedious way to access data. Requires knowledge of the SQL programming language, but allows for the most flexibility and power.

Pros

• Speed. Using raw SQL is often the fastest way to retrieve data from the database, there's none of the overhead associated with the other methods.
• Flexibility. There are several places in the software which generate a large SQL query using a variety of conditionals to dynamically attach and remove pieces of the query. This sort of flexibility is much harder to attain with the other methods.
• Power. The amount of data and the kind of data you can extract with raw SQL is often much greater than the other methods. There are a variety of MySQL functions which exist that don't really translate well into PHP wrappers.

Cons

• Difficulty. SQL is notoriously difficult to work with due to poorly documented error messages and very little feedback as to if a query is returning the proper data.
• Messiness. Writing the software solely with Raw SQL would make the codebase look like a mess. SQL queries tend to become lengthy and verbose, and many of the wrapper functions available make the code much cleaner.
• Security. Improperly used SQL can create vulnerabilities in the software. Raw SQL should be avoided for user entered data if possible to prevent SQL injection. The other methods have easier ways of handling this.

Personally I try to use this only when I need something that can't easily be accessed via CDbCommands or Models, or when I need to write a query that will have lots of dynamic parts and doesn't really fit well as a CDbCriteria. Obviously this is the only method of interaction with the database through an administrative interface like phpMyAdmin or Virtualmin, and no parameter binding is available.

Examples

Raw SQL can (for the most part) be used in 3 ways within X2Engine. The first two are both similar and are exclusively ways of accessing rows of data from the database. These are CSqlDataProvider, and the Model's findBySql method. The purposes of these examples are to show how to get effectively the same set of data using three different methods, even though this is not the ideal situation to use Raw SQL. Thus, some of these queries are excessively simplistic and do not return very useful data, but an example of how to use SQL in this context is demonstrated.

CSqlDataProvider

$count = Yii::app()->db->createCommand('SELECT COUNT(*) FROM x2_contacts WHERE firstName IS NOT NULL')->queryScalar();
$sql = "SELECT * FROM x2_contacts WHERE firstName IS NOT NULL";
$dataProvider = new CSqlDataProvider($sql, array(
    'totalItemCount' => $count,
    'sort' => array(
        'attributes' => array(
             'id', 'lastName', 'email',
        ),
    ),
    'pagination' => array(
        'pageSize' => 10,
    ),
));

This code will get a Data Provider populated with all records from the Contacts table where the First Name field is not blank. Parameters to be bound can be provided as a "params" array. The fields listed in the "sort" array specify which columns can be sorted on, and providing a "totalItemCount" is required for pagination. The CSqlDataProvider has a few places where it can be very useful, like for creating a Grid View of data from a table with no associated model. However, it is often easier to simply make a model and gain access to a lot of other features in the process.

Find By SQL

$sql = 'SELECT * FROM x2_contacts WHERE firstName IS NOT NULL';
$contacts = X2Model::model('Contacts')->findAllBySql($sql);

This code will return an array of Contact models where the First Name field is not blank. As with the CSqlDataProvider, parameters can be bound as an optional second function parameter of findAllBySql. This is fairly simplistic and not really all that useful, unless you're looking to loop through and perform data operations on the models. There are better ways to do this particular task. However, these can be loaded into a CActiveDataProvider by calling its setData method on this array.

CDbCommand

$sql = 'SELECT * FROM x2_contacts WHERE firstName IS NOT NULL';
$contacts = Yii::app()->db->createCommand($sql)->queryAll();

This code will return an array of arrays, where each secondary array is an associative array of Contact attributes. Parameters can be bound as an optional second function parameter of queryAll, the first being a flag of whether to return an associative array (which defaults to true). This is also not particularly useful because it will be difficult to get any data changes you make back into the database, and there are again better ways to obtain this kind of data.

CDbCommand

CDbCommand is a class provided by the Yii Framework which allows for one layer of abstraction from writing raw SQL to run queries. As seen in the documentation, wrapper methods for most SQL commands are available so that a query which may look like: SELECT * FROM x2_contacts WHERE name='John Smith' turns into:

$result = Yii::app()->db->createCommand()
->select('*')
->from('x2_contacts')
->where('name=:name', array(':name' => 'John Smith'))
->queryAll();

This looks more complicated at first glance, and it is slightly--but it doesn't involve writing any SQL. On top of that, some of the more complicated queries get simpler with this class, and also queries with dynamic information are easier. Let's say I wanted to grab every column except "createDate" from an arbitrary model we don't know the type of. First off, this isn't possible with raw SQL. You need to do pre-processing in PHP to get the query correct. You'd have to loop through all of the attributes, build the select statement, and then lookup the table name and pass that into the string. But for a CDbCommand:

$attributes = $model->attributes;
unset($attributes['createDate']);
$attributes = array_keys($attributes);
$result = Yii::app()->db->createCommand()
->select($attributes)
->from($model->tableName())
->queryAll();

Pros

• Security. CDbCommand allows for parameter binding, which prevents SQL injection so long as you set up your command properly.
• Speed. Still fairly quick, as CDbCommand simply converts the command object into a string SQL query.
• Some complex queries like joins are easier to do with CDbCommand than Raw SQL.

Cons

• Some simple queries like basic selects are more complicated than Raw SQL.
• Still requires some SQL knowledge, as most of the command methods are just aliases for SQL commands.
• Most of Yii's Data Providers / data display objects aren't inherently compatible with CDbCommands (though as of 1.1.13 CSqlDataProvider can take a CDbCommand).

In general, I prefer to use CDbCommand only when there is no Model for the table I am working with, or if I only need 1-2 columns of data and loading the full model would be a waste of memory. It has its uses in some very specific situations but Models should usually be used in favor of this.

Models

Models are the primary method of interaction with the database in X2Engine. All Models in the software extend one of two classes: X2Model or CActiveRecord. X2Model is a special model class that extends CActiveRecord and is used for Models which have dynamic fields rather than a fixed set of attributes. Examples of this include Contacts, Accounts, and most of the major modules. CActiveRecord models are for tables with static field definitions and don't need the advanced functionality of X2Model. Examples of this include models like Fields, Profiles, and most non-major module related models.

Models provide the easiest method of interaction with database records. For example, if we wanted to load a model into a form, allow a user to edit the data, and then update the database record, here is how it would be done in each of the three methods:

Raw SQL

// Code to get the database ID
$attributes=Yii::app()->db->createCommand('SELECT * FROM x2_contacts WHERE id='.$id)->queryRow();
// Code to pass $attributes into the form and load new attributes into $_POST['Contacts']
$update='';
foreach($_POST['Contacts'] as $attribute=>$value){
    $update.=$attribute."='".$value."', ";
}
if(!empty($update)){
    $update=substr($update,0,-2);
    Yii::app()->db->createCommand('UPDATE x2_contacts SET '.$updateQuery.' WHERE id='.$id);
}

CDbCommand

// Code to get the database ID
$attributes = Yii::app()->db->createCommand()
    ->select('*')
    ->from('x2_contacts')
    ->where('id=:id', array(':id' => $id))
    ->queryRow();
// Code to pass $attributes into the form and load new attributes into $_POST['Contacts']
Yii::app()->db->createCommand()
    ->update('x2_contacts', $_POST['Contacts'], 'id=:id', array(':id' => $id));

Model

// Code to get the database ID
$model = X2Model::model('Contacts')->findByPk($id);
$attributes = $model->attributes;
// Code to pass $attributes into the form and load new attributes into $_POST['Contacts']
$model->attributes = $_POST['Contacts'];
$model->save();

As can be seen, the Model method is by far the simplest. What's more, none of the 3 listed methods above will actually work with the X2Engine codebase. This is because the way data is stored is not always the same as it is entered by or displayed to a user. For example, date fields display in a nice, localized format and are entered via a Date Picker widget. However, they are stored in the database as UNIX Timestamps for ease of conversion to other formats and mathematical operations. So simply setting

$model->attributes = $_POST['Contacts'];

will not work, but X2Model has a special method written to handle cases like this. If we instead call

$model->setX2Fields($_POST['Contacts']);

The Model class itself will properly format user input data into a database-friendly format. This is because information on each Model attribute is stored in the x2_fields table and can be easily accessed within the Model class. Performing a similar operation via CDbCommand or Raw SQL would be very lengthy and tedious. Models and X2Model in particular really deserve their own page, so an in-depth discussion on that will be left for elsewhere.

Pros

• Ease of use. Models are incredibly easy to work with and require little to no SQL knowledge to use.
• Functionality. Models have a very large number of built in functions to make your life easier, and we've made even more of these available through X2Model and its related behaviors.
• Flexibility. Yii's code has an absolute ton of functionality designed to work with Models. Many, many things are available to load models, get lists of them and render those lists, etc.

Cons

• Size. Models involve loading all of the data for a row from the database table, but have a lot of associated overhead. There are a large number of method calls and pieces of other information stored in the model that can increase overhead.
• Limited Purpose. Models are mostly intended to be a representation of a row of data, which is fantastic if that's what you need (and for most of what X2 does, it is) but for complicated or specific queries (especially ones collecting aggregate data or statistics) they are not particularly useful.
• Learning Curve. There are a lot of things Models can do, and it certainly took me a while to figure out a lot of the more interesting or obscure features and methods.