SQL fragment abstraction

SQL fragment abstraction

par Petr Skoda,
Nombre de réponses : 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...

En réponse à Petr Skoda

Re: SQL fragment abstraction

par 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

En réponse à Petr Skoda

Re: SQL fragment abstraction

par Katarzyna Potocka,

Thanks for the update!

And also I can’t believe that we didn’t manage to talk even for 5 minutes clin d’œil

Next year?!?

En réponse à Katarzyna Potocka

Re: SQL fragment abstraction

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