Database

Overview

All Sugar products support the MySQL and Microsoft SQL Server databases. Sugar Enterprise and Sugar Ultimate also support the DB2 and Oracle databases. In general, Sugar uses only common database functionality, and the application logic is embedded in the PHP code. Sugar does not use or recommend database triggers or stored procedures. This design simplifies coding and testing across different database vendors. The only implementation difference across the various supported databases is column types. 

Primary Keys, Foreign Keys, and GUIDs

By default, Sugar uses globally unique identification values (GUIDs) for primary keys for all database records. Sugar provides a Sugarcrm\Sugarcrm\Util\Uuid::uuid1() utility function for creating these GUIDs in the following format: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee. The primary key's column length is 36 characters. 

The GUID format and value has no special meaning (relevance) in Sugar other than the ability to match records in the database. Sugar links two records (such as an Accounts record with a Contacts record) with a specified ID in the record type relationship table (e.g. accounts_contacts).

Primary keys in Sugar may contain any unique string such as a GUID algorithm, a key that has some meaning (e.g. bean type first, followed by info), an external key, or auto-incrementing numbers converted to strings. Sugar chose GUIDs over auto-incrementing keys to enable easier data synchronization across databases and avoid primary-key collisions.

You can also import data from a previous system with one primary key format and make all new records in Sugar use the GUID primary key format. All keys must be stored as globally unique strings with no more than 36 characters.

To implement a new primary key method or to import data with a different primary key format (based on the existing GUID mechanism for new records), keep in mind the following rules of primary key behavior:

• Quote characters : Sugar expects primary keys to be string types and will format the SQL with quotes. If you change the primary key types to an integer type, SQL errors may occur since Sugar stores all ID values in quotes in the generated SQL. The database may be able to ignore this issue. MySQL running in Safe mode experiences issues, for instance.
• Case sensitivity : The ID values abc and ABC are treated the same in MySQL but represent different values in Oracle. When migrating data to Sugar, some CRM systems may use case-sensitive strings as their IDs on export. If this is the case, and you are running MySQL, you must run an algorithm on the data to make sure all of the IDs are unique. One simple algorithm is to MD5 the ID values that they provide. A quick check will let you know if there is a problem. If you imported 80,000 leads and there are only 60,000 in the system, some may have been lost due to non-unique primary keys caused by case insensitivity.
• Key size : Sugar only tracks the first 36 characters in the primary key. Any replacement primary key will either require changing all of the ID columns with one of an appropriate size or to make sure you do not run into any truncation or padding issues. MySQL in some versions has had issues with Sugar where the IDs were not matching because it was adding spaces to pad the row out to the full size. MySQL's handling of char and varchar padding has changed in later versions. To protect against this, make sure the GUIDs are not padded with blanks in the database by removing any leading or trailing space characters.

Indexes

Indexes can be defined in the main or custom vardefs.php for a module in an array under the key indices. See below for an example of defining several indices:

  'indices' => array(
     array(
         'name' => 'idx_modulename_name',
         'type' => 'index',
         'fields' => array('name'),
     ),
     array(
         'name' => 'idx_modulename_assigned_deleted',
         'type' => 'index',
         'fields' => array('assigned_user_id', 'deleted'),
     ),
 ),

The name of the index must start with idx_ and must be unique across the database. Possible values for type include primary for a primary key or index for a normal index. The fields list matches the column names used in the database.

Doctrine

In order to provide robust support for Prepared Statements, which provide more security and better database access performance, Sugar 7.9 has adopted parts of Doctrine's Database Abstraction Layer, especially the QueryBuilder class, for working with prepared statements. The picture below shows how Sugar objects like DBManager and SugarQuery utilize Doctrine to provide this functionality, while still using the same toolset that has existed in Sugar.

DBManager

The DBManager class will use Doctrine QueryBuilder for building INSERT and UPDATE queries.

SugarQuery

The SugarQuery class will use Doctrine QueryBuilder for building SELECT queries.

SugarBean

The SugarBean class will continue to use DBManager class for saving all fields.