Interacting with the Database

Introduction

{TODO: Finish CDbCommand, Models,How To's for all sections} 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 [1] 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();