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?