Database Administration Redux

Some time ago I wrote a post describing an unorthodox method of leveraging the SugarCRM framework to perform database administration duties. It sounds odd, but it is quite helpful for scenarios where one has severely limited access to the server hosting the SugarCRM instance.

While the technique works, its potential dangers make it a bit unattractive. One such danger is that syntax errors in the SQL query to be executed can cause the entire SugarCRM instance to become inaccessible. Coupled with limited access to the server, this danger alone is reason enough to be extra careful. Another high risk danger is the possibility of inadvertently executing the corresponding query more than once, as it would execute every time a specific menu option was selected.

These dangers have encouraged me to look for alternative techniques that would yield similar functionality and at the same time, reduce or eliminate the aforementioned dangers. As it turns out, a minor modification to the previous method accomplishes this goal.

One of the features of the Module Loader install packages is the ability to execute SQL queries when a module is installed. This process occurs automatically, assuming certain guidelines are met, such as including a specifically named file within the package.

The advantage of this approach is that the query only gets executed once, at the time that the module is installed, effectively eliminating the danger of accidentally executing the query again. More importantly, syntax errors and the like do not cause the system to become inaccessible. Such problems simply generate an error and install process is deemed unsuccessful. One is then free to correct the errors without further complications.

What do we need to do to take advantage of this approach?

First, we need to make a file named pre_install.php. Within it, we must define a method by the name of pre_install(). The code placed within that method is automatically executed when the module is installed. 

Below is a example of a pre_install.php file:

<?php

/*************************************
Project: Ad hoc SQL Query
Original Dev: Angel Magaña, November 2011
@2009-2011 Angel Magaña
cheleguanaco[at]cheleguanaco.com


Desc: Manifest file for SugarCRM module installer 

The contents of this file are governed by the GNU General Public License (GPL).
A copy of said license is available here: http://www.gnu.org/copyleft/gpl.html
This code is provided AS IS and WITHOUT WARRANTY OF ANY KIND.
*************************************/

function pre_install()

{

   require_once('modules/Administration/Administration.php');

   $focus = new Administration();

   $query = "DELETE FROM emails WHERE created_by = '1001'; "; 

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

}

?>

The code that you see within the pre_install() method does two things for us: one, it establishes a connection to the database via $focus and two, it executes the SQL query, via $focus->db->query(). Should we have a need to execute more than one query, we can simply alter the $query variable accordingly and then call query() again, i.e. repeat the last two lines of code, but with a different SQL query. 

Side note: one can execute any PHP code one would like within this method, it is not limited to database operations.

After creating the file, the other important step is to store it in a specially named folder within the structure of the package. The folder in which you need to place it must be named scripts. 

SugarCRM will know to look for the pre_install.php file in that folder and automatically execute the code in the pre_install() method. There is no need to add any special instructions to the manifest file or the like, but it is posted below:

<?php
/*************************************
Project: Ad hoc SQL Query
Original Dev: Angel Magaña, November 2011
@2009-2011 Angel Magaña
cheleguanaco[at]cheleguanaco.com


Desc: Manifest file for SugarCRM module installer 

The contents of this file are governed by the GNU General Public License (GPL).
A copy of said license is available here: http://www.gnu.org/copyleft/gpl.html
This code is provided AS IS and WITHOUT WARRANTY OF ANY KIND.
*************************************/


global $sugar_config;


$upload_dir = $sugar_config['upload_dir'];
$manifest = array(
'acceptable_sugar_versions' => array(
'regex_matches' => array(
0 => '6\.*',
),
),
'acceptable_sugar_flavors' => array(
0 => 'CE',
1 => 'PRO',
2 => 'ENT',
), 
'name' => 'Ad Hoc SQL',
'description' => 'Ad Hoc SQL',
'is_uninstallable' => true,
'author' => 'Angel Magaña',
'published_date' => 'December 11, 2011',
'version' => '1.0.1',
'type' => 'module',
);

$installdefs = array(
'id' => 'CG_AHSQL',
);

?>

Now all you need to do to use it is zip the files as you would any other Module Loader package and install it via the Module Loader. The SQL query will automatically execute during the install process.

Related files are available for download from my personal site, via this link:

http://cheleguanaco.com/cms/node/19