Pages

Monday, May 3, 2010

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.  

5 comments:

  1. Angel, thats so slick, nice hack!!!!

    ReplyDelete
  2. Angel,

    I have added email flags and I need to create a package to copy my modified files for "/include/SugarEmailAddress"

    I need to copy in these files:
    /include/SugarEmailAddress/SugarEmailAddress.js
    /include/SugarEmailAddress/SugarEmailAddress.php
    /include/SugarEmailAddress/templates/forEditView.tpl

    Also........... I need to add this to /include/language/en_us.lang.php


    $GLOBALS['app_strings']['LBL_EMAIL_BILLING']= 'Blng';
    $GLOBALS['app_strings']['LBL_EMAIL_NOTICES']= 'Ntcs';
    $GLOBALS['app_strings']['LBL_EMAIL_PROOFS']= 'Prfs';

    Would your example in this blog handle it. I am not sure how to handle updating the app_strings

    ReplyDelete
    Replies
    1. No, the subject of this example is completely unrelated. However, if you need assistance with the creation of a module loader manifest file that copies files for you at install time, give this post a look as it includes an example of how to do that:

      http://cheleguanaco.blogspot.com/2011/04/logic-hooks-ondemand-installations.html

      Delete
  3. Nice post.

    Funny enough, I have been facing the same kind of issues, and found an extension that allows me to execute any custom query from the Sugar GUI.
    It is called SynoReports, you should give it a try.

    ReplyDelete

Your comments, feedback and suggestions are welcome, but please refrain from using offensive language and/or berating others. Thank you in advance.