Database Administration via SugarCRM Framework

Recently I found myself in a situation that required me to delete a large amount of records from a SugarCRM database.  More to the point, I needed to delete all the account and contact records.

Under normal circumstances, the task would have been rather simple.  One approach that would normally work would be to simply select all the records via the ListView and then delete the records.  A second -- and faster --- approach would be to simply truncate the table via a MySQL database administration tool such as SQLyog or phpMyAdmin.

Notice I stated "normal circumstances."  

My scenario was such that the environment hosting the SugarCRM instance in question did not allow me to select all the records and then delete them.  Due to number of records that needed to be processed (over 70K), the system tended to time out and fail.  Smaller batches was simply not going to work as it was taking a minute or more to process 200 contacts.  At that pace, it would have taken me hours to delete the entire batch of records just in one module.  

You may be wondering why I even bothered with the ListView when the SQLyog/phpMyAdmin option would have been far easier and faster.  The answer is actually equally simple.  Much like many other hosting providers, this one in particular does not provide a means for connecting directly to the database via external tools.  It is further complicated by the fact that similar tools could not be installed -- nor were available -- on the server, nor was I able to access the file system.

I am stuck, right?

Well, not quite.  After giving it some thought for a few minutes, I realized I could manipulate the SugarCRM framework to get it to do what I needed, although it is not really designed for the task I had in mind.

Let us take a look at how I solved this particular problem.


First of all, I can't stress enough the importance of making a backup before you try any of this.  Following the steps described in this article WILL RESULT IN DATA LOSS.  It is your responsibility to make sure you are protected against such dangers.  DO NOT ATTEMPT to follow the instructions in this article unless you are purposely wanting to purge your data.

To address my problem I settled on going the route of the table truncation, as it would be the simplest to use and would also save me quite a bit of time.

To accomplish my goal of truncating the accounts and contacts table, I needed a way to execute an ad-hoc SQL query through a default installation of SugarCRM.  Given that SugarCRM itself does not provide a tool for doing so out-of-box, my first challenge was to figure out a way to execute the query.

I found a perfect and simple solution to that problem: use a menu.ext.php file. Normally that file is used to define custom shortcuts/menu options within a module.  In reality, SugarCRM simply executes the contents of said file whenever the affected module is accessed.

Through it, I can inject code that performs the database operations I needed, a simple "TRUNCATE TABLE accounts" command.

Here is what my menu.ext.php file looks like:

<?php

if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point'); 

require_once('data/SugarBean.php');
$bean = new SugarBean();

$query = "TRUNCATE TABLE accounts; ";
$bean->db->query($query, true);

?>

But I still had another problem.  In order to use that file, I needed to place it within the directory structure hosting the SugarCRM system in question, yet I don't have access to the file system.

Another simple solution came to mind: use the module loader.

Thus, I proceeded to create a simple manifest.php file for my module.  Here are the contents of that file:

<?php
global $sugar_config;
$upload_dir = $sugar_config['uploads_dir'];
$manifest = array(
'acceptable_sugar_versions' => array(
'regex_matches' => array(
0 => "5\.2\.0.*",
1 => "5\.5\.*.*"
),
),
'name' => 'DB Ops',
'description' => 'Truncate Tables',
'is_uninstallable'  => true,
'author' => 'Angel Magaña',
'published_date'  => 'April 30, 2010',
'version' => '1.0.1',
'type' => 'module',
);

$installdefs = array(
'id' => 'MenuTest',
'menu' => array(
                         array( 'from' => '<basepath>/menus/menu.ext.php',
'to_module' => 'Accounts'),
)
);

?>

Next, I needed to package the module into a zip archive and followed these steps:  

1. Created a new folder named "Admin"
2. Created a sub-folder in "Admin" and named it "menus"
3. Placed manifest.php into the "Admin" folder
4. Stored menu.ext.php in the "menus" sub-folder

To finish the module package creation, I selected the contents of the Admin folder and created a zip file.  Note that creating the package by zipping the Admin folder instead would result in a flawed module package.

Now that module package was ready, I proceeded to install it via the Module Loader in the Admin control panel within SugarCRM as I would any other module.  

Clicking the Accounts module immediately caused SugarCRM to execute my commands (although I didn't actually define any new shortcuts or other visual indicators) and my goal was accomplished.  

Lastly, I removed/uninstalled the module to protect myself from accidentally truncating the table again in the future.