Sugar Community Edition: Databases

All five Sugar editions 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 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.

Indexes

Indexes can be defined in the main or custom vardefs.php for 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.

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 create_guid() utility function for creating these GUIDs in the following format: aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee. The primary key 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).

Sugar allows a primary key to contain any unique string. This can be a different GUID algorithm, a key that has some meaning (such as bean type first, followed by info), an external key, and/or auto-incrementing numbers (converted to strings). Sugar chose GUIDs over auto-incrementing keys to allow for easier data synchronization across databases and avoid primary key collisions when one of the following occurs:

Sugar Offline Client (part of Sugar Enterprise) syncs data with the main Sugar installation.
Sugar SOAP APIs are used for data synchronization.
Tools like Talend are used for data synchronization.

Offline Client uses GUIDs for primary keys for ease of implementation and simpler handling of data conflicts compared to other schemes. If a developer changes Sugar to use some other ID scheme and needs to accommodate data synchronization across data stores, IDs need to be partitioned ahead of time or a system similar to the Sugar implementation for Cases, Quotes, and Bugs created. For modules like these that have human-readable ID numbers (integers) that need to be synchronized across databases, Sugar implements a server ID that is globally unique and concatenates it with an incrementing Case, Quotes or Bug number. Attempting such a change to Sugar requires some careful planning and implementation.

If data synchronization is not an issue, the primary key format can be changed to some other unique string.

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

To perform any of the following:

Implement a new primary key method
Import existing data with a different primary key format based on the existing GUID mechanism for new records

Make note of the following:

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 : IDs 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 need to run an algorithm on the data to make sure all of the IDs are unique. One simple algorithm is toMD5 the ids 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, as a result of case sensitivity.
Sugar only tracks the first 36 characters in the primary key. Any replacement primary key will either require changing all of theID 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 some of the more recent versions. To protect against this, you will want to make sure the GUIDs are not padded with blanks in the DB.