“Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.”
— John F. Woods
Rewriting the SQL of a JDBC type Data Source was a neat trick for a specific, limited use case, but to use that tactic on a more traditional requirement would require that we fill in a few of the holes left unattended by that simplistic example. How we deal with the local copy of the data is one major consideration. How we deal with multiple, concurrent users is another one. The latter concern is probably the easiest to address, so let’s take that one on first.
The simplest way to support active multiple users is to treat the Data Source like an airplane restroom and put up some kind of Occupied sign that limits access to only one user at a time. In this case, it would actually be more like an Unoccupied sign that signals that the Data Source is available for use. Using our previous example’s SQL, it might look something like this:
SELECT
STATUS,
EXPIRATION_DATE
FROM
LICENSE_MASTER
WHERE
ID='AVAILABLE'
The presence of the key word AVAILABLE is our cue that the Data Source is open for business. If that word is not present, then we wait our turn. In practice, that might look something like this in Javascript:
function fetchData() {
var ds = new GlideRecord('sys_data_source');
ds.get('name', data.config.dataSource);
var sql = ds.sql_statement;
if (sql.indexOf('AVAILABLE') != -1) {
ds.sql_statement = sql.replace('AVAILABLE', data.license.number);
ds.update();
var job = new GlideRecord('scheduled_import_set');
job.get('name', data.config.jobName);
gs.executeNow(job);
} else {
if (attempts++ < maxAttempts) {
gs.sleep(sleepMs);
fetchData();
}
}
}
Once you have replaced the key word AVAILABLE with your own key, everyone else who comes along will no longer find the welcome sign out, and will have to wait their turn to utilize the service. The one thing that you will have to do, however, is to reset the SQL back to its open-for-business state once your import job has completed. That can be handled in the specifications for the import job itself. Near the bottom of the Scheduled Import form, there is a check-box labeled Execute post-import script. Checking this box opens up a Script input field where you can enter a script that will run once the Scheduled Import job has completed:
To reset the SQL in the Data Source to make it available for the next potential user waiting in line, you can do something like this:
var ds = import_set.data_source.getRefRecord();
var sql = ds.getValue('sql_statement');
sql = sql.substring(0, sql.indexOf("'")) + "'AVAILABLE'";
ds.setValue('sql_statement', sql);
ds.update();
With the post-import script in place, your key value will be safe through the execution of the import job, but then as soon as it completes, will once again be replaced with the key word AVAILABLE to signal to to the next person in line that it is their turn in the barrel. Things may take a tad bit longer under this strategy, but at least users will not be stepping on each other’s requests when they attempt to use features implementing this concept. And of course, the most important thing is that it satisfies Rule #1.
