Difference between revisions of "Interacting with the Database"

Revision as of 22:56, 6 August 2013

Core Structure

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 whole software in 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

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