SQL fragment abstraction

SQL fragment abstraction

by Petr Skoda -
Number of replies: 3

Do you find dealing with two separate variables for SQL code and parameters confusing?

Did you ever get parameter name conflicts or did you receive wrong paramter types when constructing a complex SQL query from different sources?

Lets create a new class that encapsulates SQL code and relevant parameters. And while at it let the new object handle concatenation of SQL with conflicting parameter names and even different parameter types.

Example for illustration:

$part1 = new \core_dml\query('deleted = :param', ['param' => 0]);
$part2 = new \core_dml\query('confirmed = ?', [1]);
$part3 = new \core_dml\query('AND mnethostid = :param', ['param' => $CFG->mnet_localhost_id]);

$sql = new \core_dml\query('SELECT u.* FROM {user} WHERE');
$sql = $sql->append($part1)->append('AND')->append($part2)->append($part3);

[$sqlquery, $params] = $sql;
['sql' => $sqlquery, 'params' => $params] = $sql;

var_export($sql->sql);
// SELECT * FROM {user} WHERE deleted = :param AND confirmed = :qm_param_1 AND mnethostid = :nm_param_1
var_export($sql->params);
// ['param' => 0, 'qm_param_1' => 1, 'nm_param_1' => 1]

$users = $DB->get_records_sql($sql);
$users = $DB->get_records_select('user', $part1->append('AND')->append($part2));

Note that coding this and patching existing DB drivers is easy, the difficult part here is to persuade Moodle HQ to include this in core...

In reply to Petr Skoda

Re: SQL fragment abstraction

by Petr Skoda -

In case anybody was interested, the initial implementation is now available at https://github.com/mutms/moodle-tool_mulib/blob/MOODLE_405_STABLE/classes/local/sql.php

In the end the append() was not very useful, so I removed it completely. Instead I started using comment placeholders in my plugins.

Here are some simplified examples:

$sql = new sql(
    "SELECT u.*
       FROM {user} u
      WHERE u.deleted = :deleted /* confirmed */ /* auth */",
    ['deleted' => 0]
);
$sql->replace_comment('confirmed', 'AND u.confirmed = ?', [1]);
if ($includeauth) {
  $sql->replace_comment('auth', new sql('AND u.auth = :param', ['param' => 'auth']));
}
$users = $DB->get_records_sql($sql->sql, $sql->params);


$sql = new sql(
    "SELECT u.*
       FROM {user} u
      WHERE u.deleted = 0 /* conditions */
   ORDER BY u.lastname"
);
$conditions = [
    new sql("u.auth = :auth", ['auth' => 'manual']),
    new sql("u.auth = :auth", ['auth' => 'ldap']),
    "u.auth = 'email'",
];
$conditions = sql::join(" OR ", $conditions);
$sql->replace_comment('conditions', $conditions->wrap('AND (', ')'));
$users = $DB->get_records_sql($sql->sql, $sql->params);

There is also a new helper method that prepares sql for debugging in database console $sql->export_debug_query():

SELECT u.*
FROM "t_user" u
WHERE deleted = 0 AND (u.auth = :auth OR u.auth = :param1 OR u.auth = 'email')
ORDER BY u.lastname


--auth = manual
--param1 = ldap

In reply to Petr Skoda

Re: SQL fragment abstraction

by Katarzyna Potocka -

Thanks for the update!

And also I can’t believe that we didn’t manage to talk even for 5 minutes wink

Next year?!?

In reply to Katarzyna Potocka

Re: SQL fragment abstraction

by Petr Skoda -
Sure, definitely coming next year too, this is by far the best MoodleMoot event format!