SugarCRM Pre_Install

I recently was working with a Sugarcrm third-party notifications module. It's funtionality required the use of 27 interlinking database tables. In working with the module, locally, I designed and tested some 50 notifications, all saved to the database. Here's where the fun begins. Since I had developed everything on my local server, I needed to move all the notifications through our testing process to three different servers: development, test, and production. Needless to say, I didn't want to redo all 50 notifications on each server, which could lead to coding errors and re-testing each time I moved to a new server. This is a common problem when moving data from one server to another. What do I do?

It's a large database, so I didn't want to copy the entire database to each server, this could cause other problems with other developers work, and other modules that were different from one server to the next. Since all the notifications were stored in the database tables, What I needed to do was just copy the 27 tables to the other databases. Ok, how do I do that and maintain the interconnecting ids between tables?

Well easy, I export the 27 tables in SQL from the database and import them to the next database. Hold on here! Not a good idea! Why? Well SQL is prone to syntax errors, mostly from misplaced quotes or line endings. What happens when you get half way through an import and SQL balks. Well, it just stops, but what about your data? Well, your not sure what table you've updated, and what was not, leading to questionable data, and potentially shutting down your entire application from mismatched ids between tables. So now, what? If you run the SQL again you could duplicate data, another database mess. Most database export scripts give you a bunch of "INSERT INTO" queries to update a database which ignores a check for duplicate ids. So, on another import, you end up with more corrupted data and duplicate primary ids, which again can shut down your application, not good, not safe.

Now admittedly you can get around most of this by editing the export script, replacing the "INSERT INTO" queries with "REPLACE INTO" queries, and then using transaction and commit tags around the export. This will delete any duplicate records, and only update if the entire script of SQL queries run without an error. This will work, but it doesn't help you with finding errors, and there is a chance for creating errors while editing the export script.

There is another way, that takes advantage of Sugar's module loader.  The module loader is used to load Sugar and third-party zip packages into the SugarCRM application.  What is not well known is that module loader can run a pre, or post_install, stand-alone, zip package that will run a script. I'll use pre_install for the rest of this article.  The install package can be used to run SQL queries.  This gives you the ability to run the SQL import in an installation zip package.

The advantage of doing this is that the SQL file module loader install will only run once, there's no danger of running twice. If any syntax errors occur the package is not installed. This gives you to two other advantages, your application will not go belly up and crash from messed up database tables, because nothing is installed unless everything works, and more important, it gives you one import zip package that is easy to install and repeatable across all your servers. A much safer way to go.

There's always a catch to something the looks relatively easy. The catch is there are certain naming conventions you need to follow to make this all work. We need to create a module loader package.

Let's use a stripped down example, and put a package together. Suppose I want to insert a record into the "teams" table with a non-standard Sugar ID. Sugar uses a unique, global, 32 bit, alphanumeric id for every record in the database, however that doesn't mean you can't use your own unique id. So for our example, we'll insert a record with the unique id of "MD1234" that we will want to insert across all our servers. Let's create the package.

We create a package directory. The pre and post install must be laid out with a specific directory structure, or the install won't work. Our import files need to be in the scripts directory under the main package directory.

SetUpTeamId

In the main directory, I like to create a "README.txt" file that details everything I'm doing and how I did it for the pre_install package. This is important, because six months later, when its time to move to another server, and you have moved on to other things, and forgot what the package was for, and how to use it. I can't overemphasize the importance of the README.txt file as a memory jogger. It is visible in the module loader along with the license file, which is the next file, "LICENSE.txt," we'll put in the main directory. Just copy the license file over from the Sugar main directory.

Next, we need a manifest file, "manifest.php" in the main directory that tells Sugar what to do with the files in the "scripts" directory. Here's what your manifest file should look like:

/*************************************
The manifest file 

Project: SetUpTeamId
*************************************/

$manifest = array(
    0 => array (
        'acceptable_sugar_versions' => 
        array (
            0 => '6.5.*',
        ),
    ),
    1 => array (
        'acceptable_sugar_flavors' => 
        array (
            0 => 'PRO',
        ),
    ),
    'readme' => 'runs adhoc SQL query to insert a record with a unique id in the "teams" table.',
    'name' => 'SetUpTeamId',
    'description' => 'Run Ad Hoc SQL to create a unique ID record in the teams table',
    'is_uninstallable' => true,
    'author' => 'YourCorp.com',
    'published_date' => '2015-03-17',
    'version' => '1.0.0',
    'type' => 'module',
);

$installdefs = array(
    'id' => 'SetUpTeamId',
	'copy' => array (
		0 => array (
		'from' => '/scripts/teamimport.sql',
		'to' => 'cache/upgrades/temp/teamimport.sql',
	  ),
	),
);

The first two arrays detail the sugar version this package with run with and what version of Sugar you're running. The next we have some descriptors. The name is what you'll see in the module loader window for identifying the package. The publish date and your version number is important to keep track of your versions. Type should be module, we're telling module loader to treat this like a regular module install, and 'is_uninstallable' should be set to true, to put uninstall buttons in the module loader view.

The key to the manifest file is telling the Sugar module loader what to do with the files in the package. Here we have a $installdefs array that moves the "teamimport.sql" SQL import file to a specific location on the new server. We tell Sugar to copy the "teamimport.sql" script to 'cache/upgrades/temp/teamimport.sql' The copy location is important, so that module loader knows where to find the file on the new server. I added a teamimport.sql file in there, in case you have a large SQL import. In this example, I will not need to use a separate file. In which case, no files will be needed to be copied in the manifest file.

That's it for the manifest file, we need one more file, "pre_install.php", the code that tells Sugar what to do with the either the "teamimport.sql" file you just copied, or in our simplified example, below, where I don't use a separate file.

Let's take a peak at the pre_install.php file.

/******************************************************************
  pre_install.php -- script to run a DB query via module loader.
*******************************************************************/

function pre_install()
{
    global $sugar_config;
    global $db;

    $query = "REPLACE INTO teams ( (`id`, `name`) VALUES ('MD1234', 'Maryland')"; 

    $db->query($query, true);
}

Any php code, or SQL queries, can be in the pre_install.php file. Sugar globals can be invoked for easy database connection to the new server database to run queries. I bypassed using the teamimport.sql file, because the example was so simple. If I had a long 27 table import, then I would want a separate file containing all my SQL queries. To run that file in the pre_install file, I would have to read-in the file, and then run the queries in a while loop. Something like this:

/******************************************************************
  pre_install.php -- script to run a DB query via module loader.
*******************************************************************/
    global $sugar_config;
    global $db;


    //Open the import file
    $filename = 'cache/upgrades/temp/teamimport.sql';
    $filein = fopen($filename, 'r');
    if (!$filein) {
        $GLOBALS['log']->fatal("[SetUpTeamId:pre_install] Input file ( $filename ) was not readable - load aborted!");
        echo "Input file ($filename) was not readable - load aborted!\n";
        return(1);
    }
    
    //Connect to the db
    $con = dbconnect();
    
    //Read the file a line a time
    while (($line = fgets($filein)) !== false) {
    
    ... your code ...
    
    }

To review, here's some gotcha's to watch out for in using pre and post install:

Files need to be copied to 'cache/upgrades/temp/' on the new server.

The directory structure in the package needs to have a "scripts" directory, where Sugar looks for pre_install.php, and any other files needed.

The code file needs to be named "pre_install.php" or "post_install.php."

The function in the pre_install file must be called "pre_install()" or "post_install()".

One final gotcha, if you decide to get "slick" and say "screw the SQL," I'll just use a "$bean->save," doing this will fire any other hooks you might have either global, or module based, which might not give you the desired result you want, whereas using SQL will not fire your hooks.

To make use of this, zip the files, and install the zip file using module loader.  If there are any errors in running the script then module loader will report it, and the module "install" will fail. Depending on what your script does, and where in it's execution it fails it may leave the job partially done, and require manual roll back. If you are performing multiple operations that need to all succeed or fail together you should structure the script to use a transaction and include appropriate error handling, and log output as needed, and of course, test carefully, locally before moving to other servers.

In closing, I would like to thank Angel Magana's article "Database Administration Redux" as the catalyst that allowed me to get my 27 tables into other servers easily. Happy coding!