Difference between revisions of "Interacting with the Database"

Revision as of 22:56, 15 August 2013

{TODO: Finish CDbCommand, Models,How To's for all sections}

Introduction

X2CRM 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 X2CRM 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.

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 X2CRM. 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 X2CRM 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.