Sugar Developer Blog

I have recently been asked to answer this question a few times, “Does SugarCRM require GUIDs for primary keys in the database? ”

At a high level, Sugar was designed to allow the primary keys to be replaced by any unique string. This could either 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.

The GUIDs in Sugar are generally only used as unique primary keys.  They have no other special meaning.  We don’t bother to look at what is in the string and do not rely on what they contain other than the ability to match them against records in the DB and having the include/utils.php function create_guid() return a valid one. Because all of our relationship tables are typed, we link two records (such as an account record with a contact record) with a specified ID in the record type relationship table (e.g. accounts_contacts) instead of just storing the ID and searching to find out what type has that ID.

We chose GUIDs rather than auto-incrementing keys to allow for easier data synchronization across databases. This data synchronization issue comes into play when the Sugar Offline Client (part of Sugar Enterprise) syncs data between the main Sugar installation and the Offline Client or when developers use the Sugar SOAP APIs or a tool like Talend for data synchronization.

The strategy of the Offline Client using GUIDs for primary keys was very easy to implement and we don’t have to worry about data conflicts. If you change the system to use some other ID scheme and do need to accommodate data synchronization across data stores, then you would have to either partition the IDs ahead of time or work out a system similar to what we have for cases and bugs where we have a server ID that is globally unique and a incrementing case or bug number. This is a moderate customization though which would require some careful planning and implementation.

However if you don’t care about data synchronization issues, then you can certainly change the primary key format to some other unique string.

To implement a new primary key method or to import existing data with a different primary key format and then rely on the existing GUID mechanism for new records, there are a few things to look out for:

• The system 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, you might have SQL errors to deal with since we put all ID values in quotes in our generated SQL. The database might be able to be able to ignore this issue. MySQL running in Safe mode will have issues for instance.
• Case-sensitivity can be an issue. IDs “abc” and “ABC” are typically treated the same in MySQL. Some other CRM systems from which people were migrating to SugarCRM were using 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 to MD5 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 might have been lost due to non-unique primary keys resulting from this case-sensitivity issue.
• 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 don’t 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 have 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.

Jacob

I just got back from IBM’s Impact 2008 show. This show was a pretty amazing whirlwind tour with a primary focus around SOA. It took place in the MGM Grand hotel in Vegas. I was amazed at the size of the MGM Grand property. The conference spanned multiple buildings and I would say at least a square mile of territory.

The conference spanned most of the buildings in the picture on the right. Many of the meetings were in the larger buildings that are relatively close. An Un-Conference that I participated in was in the second signature tower. The second signature tower is the middle of the three thin tall towers in the middle picture.

One of the highlights of the conference was the announcement of WebSphere sMash. WebSphere sMash is the commercial offering based on the incubator project Project Zero. ProjectZero is an attempt to implement the PHP language on top of a JVM. WebSphere is going to support dynamic languages. The first two dynamic languages that they are going to support are PHP and Groovy. With this project, you can code your application in PHP, Groovy, C, and Java. All four of these languages will run in the same process space making calls between the different languages very easy. They have also included some great tooling. They have an IDE in Eclipse and an IDE in the web browser. They have also been leveraging their own technologies in the development of the project. They have PHPBB running on the project zero site in WebSphere sMash.

I also had the opportunity to sit down with Jerry Cuomo the CTO and IBM Fellow in charge of this project and record a podcast that is available on IBM’s DeveloperWorks site. At this point, they have two software packages running on sMash: PHPBB and SugarCRM. The instructions for installing and configuring Sugar for sMash are still being refined. There is also a 10 minute video of Sugar on Zero being extended to span multiple languages and leveraging a SOAP to REST API conversion layer.

This project started about 10 months ago and is still getting refined rapidly. At this point it is capable of running Sugar pretty well. It is also setup to create separate Java processes for each application that you are hosting on your machine. Each application has its own set of dependencies and configurations. This allows each application to be setup with the resources that it needs to accomplish its mission independently of any other application on the system. They also have a repository system that allows the distribution of projects complete with custom configurations.

At this point, the current code is implemented in a way to get maximum functionality as quick as possible. a the functionality gets further and further towards completion, I am very interested to see how well the IBM team is able to leverage some of the person centuries IBM has invested in tooling, optimization, monitoring, virtualization, and scalability.

Check out ProjectZero and let me know what you think.

Jacob Taylor

CTO and Co-Founder

In working with IBM on a presentation, the TIOBE Programming Community Index for April 2008 came to my attention.  Here is a quick preview of the top few languages:

It looks like PHP is holding its own as one of the top languages.  The biggest surprise for me was how quickly VB just gained on C++.  VB has been gaining ground for quite some time.  It had the single largest gain.

Jacob Taylor

CTO and Co-Founder