SugarCRM cache directory – it is NOT a cache directory!

Here is a useful reminder from a few years back – “SugarCRM cache directory – it is NOT a cache directory!“.   Unlike most modern day web applications, which use cache/ folder for temporary files, which are safe to delete, SugarCRM keeps a bunch of stuff in there, which, if disappeared, would leave you in a very uncomfortable and confused stay.

Things have obviously improved over the years, but it’s still far from perfect.  And while we are on the subject of surprising issues with SugarCRM, make sure check my other post about working with encrypted values.  Basically, the summary is: backup, backup, backup!  If you want to sleep well at night, backup SugarCRM’s full file system (files, configurations, temporary files, caches, etc) and its database.  And never ever change anything.

Single Sign-On Between SugarCRM and Request Tracker

As mentioned before, over the last few month I’ve been involved in quite a few integration projects, using mostly SugarCRM and Request Tracker.  One of the interesting challenges was the Single Sign-On (SSO) between the two.

Continue reading “Single Sign-On Between SugarCRM and Request Tracker”

Working with encrypted values in SugarCRM 6.5

SugarCRM comes with a variety of modules that store values in the database.  Some of those values are encrypted.  For example, mailbox passwords for inbound and outbound email configurations.

When you create this configurations through the web interface or the API, you don’t need to worry about encryption, as SugarCRM handles that all by itself.  But sometimes, you need to access those values from third-party code.  The easiest way would be of course to use the same API functionality, but this is not always possible (different machines, different technology stack, etc).

It is still possible decrypt the values in the database, if you know where to look.

First of all, here is a little side note for InboundEmail and OutboundEmail modules.  InboundEmail is a full-featured module, which you can find in modules/InboundEmail folder.  OutboundEmail is however not – it lives in include/OutboundEmail .  This might seem surprising, but the reason for this (probably, as I don’t know for sure) is that outbound email configuration is much simpler.  Inbound emails are linked with folders, which are then used to subscribe users, etc.  Outbound emails are just SMTP configurations to use, directly linked to users.

Anyways.  Let’s get back on track.

Most of the encryption and decryption magic happens in include/utils/encryption_utils.php.  If you look through the code, you’ll notice that it deals with mostly two things:

  1. Generating or reading an existing encryption key.
  2. Encrypting or decrypting text with Blowfish, using the encryptionkey.

Encryption keys are stored in custom/blowfish/ folder.  The files that you’ll find there have weird names and a .php extension.  The name of the file comes from the module, for which the key will be used.  ROT13 algorithm is used to convert the name of the module into the file name.  (Note, that for outbound email, the name of the module is OutBoundEmail, not OutboundEmail).

If the encryption key file does not exist, a new one will be generated.  The file will contain a PHP snippet like this:

<?php // created: 2016-04-18 10:00:00 
  $key = array ( 0 => 'a0a0a0a0-b1b1-c3c3-d4d4-e5e5e5e5e5e5',
);

If you accidentally remove the file, then you won’t be able to decrypt any of the values, encrypted with this key, so make sure you backup this up.  Especially considering that this folder might be in your .gitignore, as a sub-folder of custom/ which stored lots of auto-generated stuff.

Note that the file actually defines a $key variable, which, if you will include it in your code, can overwrite your $key variable. So, be warned.

Now, the encryption and decryption is handled with the Crypt_Blowfish library from Pear.  You can find it in include/Pear/Crypt_Blowfish folder.

A little note for the above as well.  The Blowfish.php file which contains the Crypt_Blowfish class, requires the Blowfish/DefaultKey.php file (from the setKey() method).  That requirement uses relative path, but not based on the current file.  Yeah, I know.  So, if you just copy over the library somewhere else, you might need to adjust either path variables, or the setKey() method.

Armed with this knowledge, you can now work with encrypted values stored by the SugarCRM in the database.  Good luck!

SugarCRM, RoundCube and Request Tracker integration on a single domain

In my years of working as a system administrator I’ve done some pretty complex setups and integration solutions, but I don’t think I’ve done anything as twisted as this one recently.  The setup is part of the large and complex client project, built on their infrastructure, with quite a few requirements and a whole array of limitations.  Several systems were integrated together, but in this particular post I’m focusing primarily on the SugarCRM, RoundCube and Request Tracker.  Also, I am not going to cover the integration to full extent – just the email related parts.

Continue reading “SugarCRM, RoundCube and Request Tracker integration on a single domain”

Single Sign-On with SugarCRM and RoundCube Using Multiple PHP Sessions

I am currently involved in an interesting integration project at work.  As part of it, we need to create a single sign-on process between SugarCRM (version 6.5.20) and RoundCube (version 1.1.4) webmail application.  RoundCube webmail is being displayed within the iframe inside the SugarCRM user interface, so it would help if users didn’t have to login to RoundCube since they are already authenticated in SugarCRM.

Once the user is authenticated in the SugarCRM, a PHP session is created with, among other information, authenticated user ID.  Using that ID, we can fetch the full user record and get his IMAP credentials, necessary for the RoundCube login.  While this wasn’t too difficult, there were a couple of road bumps that I’d like to document here, so that next time I won’t have to work it all out from scratch again.

Continue reading “Single Sign-On with SugarCRM and RoundCube Using Multiple PHP Sessions”

On Open Source

An excellent comment in regards to Open Source and SugarCRM.  Not one, but two awesome quotes.  One:

In my eyes (and those of the open source community), the “high value” stuff is the stuff you can improve, and the “low value” stuff is the stuff you cannot.

and two:

I should also correct John Mark’s perception that slashdot is the be-all, end-all center of gravity for open source development. The beauty of the open source model is precisely that there can be many centers that collectively interoperate. Thus, Red Hat and Fedora, as two separate centers, both draw strength and network effects from the centers that are GNOME and Mozilla and Python and of course, Project GNU. None of these projects needs to “own” the others in order to create true value within its own center. Red Hat chose a path of letting 1000 flowers bloom, and that strategic decision allowed both a richer field (for the community) and a greater harvest (for Red Hat). SugarCRM can be successful without creating a monopoly of open source development. But it can also fail if it divorces itself from the open source values that make it one of the best alternatives to other proprietary solutions today.

SugarCRM deployment efforts

Since we started working on SugarCRM in the office, one of the hardest tasks that we had was solving the deployment issue.  On one hand, SugarCRM comes with some really nice GUI tools, such Studio and Module Builder.  On the other hand, the system is large and complex and should be developed and tested in a separate, non-production environment.

We’ve spent a lot of effort over the last couple of month trying to solve the puzzle.  The problem is that there is a tricky combination of files updates and database changes, some of which can be just copied over while others have to be executed from the destination machine’s administration.

So, what we did first was complete separation of environments.  Each developer had his own machine on which he could install and configure as many instances of SugarCRM as he saw fit.  Also, each developer had a separate branch in the Subversion, so that he could work on his own stuff without being afraid to run into conflict with anyone.

After that, we created a development server with a checkout of common trunk.  For extra insurance, we did a checkout from a system user, who does not have any write permissions in the repository.  In this case, even if someone will accidentally try to commit from the development server, we would be sure that it fails.

Now, each developer had to merge his changes into trunk, and then test them on the development server.  This procedure is very similar to the production deployment and consisted of two parts.  Firts part was updating all the relevant files (a bit more on this in a moment) with svn update.  Second part was logging into SugarCRM and doing Admin -> Repair -> Quick Repair and Rebuild.

The graphical tools that come with SugarCRM are powerful, but a bit confusing.  The biggest confusion for me was (and maybe still is) between Module Builder and Studio.  Studio can be used to customize core modules that are shipped with SugarCRM.  The results of these customizations are stored in custom/modules directory, and when loaded into the database, can be observed in _cstm tables (for example, accounts_cstm).  This is where new custom fields and things like that are going.  Module Builder is a tool which can help you customize existing modules or build the new ones. The confusion here is because both of these tools can be used to do the same things.  But with Module Builder you’d be working closer to the core system and modifying “original” functionality.  You can build your own modules too, by the way.  The results of the Module Builder work with go into modules/ directory, and changes in the database will take place in the original tables.  One thing to remember though, is that you’ll need to push Save & Deploy button every time you are finished with changes in Module Builder.  This is like compiling and building a module.  If you forget this step, then your module will hang in its source somewhere around custom/modulebuilder directory.

Another thing to keep in mind is the sillyness of the machine trying to figure out another machine.  Meaning that Subversion will often have issues trying to figure out the changes from the last commit, and these issues would be often caused by a lot of automatically generated code by SugarCRM.  In most of these problematic cases, Subversion will just merge the changes, and this would often result in a broken system.  I’ve found at least two reasons behind these: small context size that Subversion uses (3 lines or so) confuses it sometimes, bringing it to a wrong place in the file to do the merging; and rather messy automatically generated stuff by SugarCRM – unnecessary reordering and mixed (DOS and UNIX) ends of lines in a single file.  These problems are mostly related to vardef files (vardef.php and anything *def.php) and language dictionaries (anything with *en_us*php, or whatever your locale is).  The solution we are using at the moment is simple, although a bit heavy on the manual work – instead of merging the changes and checking them every time we simply remove old versions of files and add the new ones in two separate commits.  These way Subversion treats the files as completely different ones and real removes and re-creates them instead of trying to merge.

We follow exactly the same procedure now to deploy to the production server.  We just merge code from trunk/ to branches/stable , commit, then update the files on the production server, and then do the Quick Repair and Rebuild.

The thing about Quick Repair and Rebuild is that it takes the update definitions of your forms and layouts and rebuilds compiled templates.  It also compares the structure of the database with the update definitions in the files and, if needed, updates the database scheme too.  Sometimes you’d get an error of missing table (usually custom tables with _cstm suffix) – just create an empty table manually.  Put a couple of standard fields like id_c, date_modified, and date_entered.  After that, field modifications should be OK.  In case you run into a problem with updates to several fields at once, make sure that SugarCRM put a semicolon (;) at the end of each SQL statement that it shows you in a popup window.  For some weird reason, sometimes it just works, and sometimes it tries to execute several queries without separating them one from another.

So far the setup seems to be working for us just fine, but I’m sure that we’ll have a few changes here and there.  I’ll let you know once we find any better way of doing things.  In the meantime, here are some links that might help your development efforts:

InnoDB is better for SugarCRM

After SugarCRM was deployed, we were experiencing some lock ups.  Not frequent or dangerous, but annoying.  About once a week or every ten days or so, SugarCRM would lock up and won’t answer any queries at all.  Not even the login was possible.  A brief investigation showed that somehow it was locking up the MySQL database – about 15 processes (using “show full processlist”) in Locked state, with no data being sent back or forth.  All locked queries were rather complex, with several JOINs.  The load on the system was somewhat high, since we have about a few dozen operators working on it at the same time.

A similarly brief Google search suggested (see here and here) and explained converting MySQL tables from MyISAM to InnoDB.  A test has been performed and everything went OK.  Our SugarCRM database is about 600 MBytes and it was converted from MyISAM to InnoDB in under 20 minutes.  The best part is that it takes even less to convert back to MyISAM, in case you change your mind.

It’s been a few days now since we did the conversion and it looks OK.  Also, the CRM itself feels a bit faster.

Migrating MS Dynamics CRM to SugarCRM

I’ve been a bit quiet for the last couple of month.  That’s because I was leading an ambicious project at my new job – migration of a Microsoft Dynamics CRM version 3 to SugarCRM Community Edition version 5.0.0.  There were only three people involved, non of us could afford to work full time on the project, and we only had three weeks to do it.

Read on for a story on why it took us longer, how we did, and if it was a success at all.

Continue reading “Migrating MS Dynamics CRM to SugarCRM”

Toolbox : WordPress, CakePHP, SugarCRM, RT

Over the last couple of years I’ve been working a lot with these four applications – WordPress, CakePHP, SugarCRM, and RT.  Each of these is beautiful in its own way.  Each of these tools is an Open Source Software. Each of these tools has a large community. Each of these tools has a free and commercial support and development. Each of these can be used in a number of ways to solve a whole range of problems.  Let me briefly introduce each one of them.

Continue reading “Toolbox : WordPress, CakePHP, SugarCRM, RT”