Talk:DB layer 2.0

Revision as of 22:26, 5 May 2008 by Petr Škoda (škoďák) (talk | contribs) (Interface proposal)

Jump to: navigation, search


  • OOP
  • easy unit testing
  • full abstraction (AdoDB, PDO)
  • easy to use
  • code must not know on which db it is running (no condifional db family hacks anymore!)
  • no magic quotes and slashing anymore

Various Notes (before 20080501)

  • AdoDB, AdoDB over PDO, PDO. (my initial +1 to continue using underlying ADOdb).
  • Placeholder types: :named or ? (will require PHP parsing under some DBs).
  • Multiple connections supported (auto-contained DB object).
  • PERF debugging available.
  • Logging available (to detect wrong uses).

  • Total breakage (all contrib uses)
  • Total breakage (current dmllib 1.0 functions definition).
  • Development mode (branch with radical replacement, some compatibility layer to minimize breakage - current dmllib instantiating new dmllib + stripping slashes ?)

  • Mahara experience. ( it's also worthwhile to note that a long long time ago I also did this for elgg which involved doing the addslashes/stripslashes audit through the code base - we didn't have to do this with mahara as we had placeholders and no slashes right from the start - Penny )
  • Interface example.
  • dmllib 2.0 tests since the beginning.
  • dmllib 2.0 documentation since the beginning.
  • Classes implementation order (PG, MySQL, MSSQL, Oracle).
  • More ideas.

  • Next meeting. TODO: document MDM20080501, AdoDb/PDO decision, Interface + PHP documentation + Docs documentation, define tests to perform. ADDRESS IT for... 20080508 ?

Eloy Lafuente (stronk7) 19:00, 29 April 2008 (CDT)


I think it's interesting to discuss the potential of moving to PDO instead of ADODB but I'm not convinced it needs to be in the same conversation - we should be able to switch out ADODB and use PDO instead with completely no change outside dml - surely nowhere in the code is using $db directly - should always be using execute_sql

Penny Leach

Yup 100% agree, underlying stuff will be completely hidden, so it's a non-priority decision. We'll be able "replace" it more or less easily or, alternatively, create new "Moodle drivers" using different internal stuff. Just prospecting your opinion about PDO. I've done some (basic) research and it seems not to be 100% ready for production under some DBs. But wanted to know if you have some experience with it (or at least you initial feeling).
Eloy Lafuente (stronk7)

Interface proposal

Sample implementation with complete API definition is in MDL-14679

All classes are stored in lib/dml/ directory. The naming convention is dbtype_dblibrary_moodle_database for database classes. For example oci8po_adodb_moodle_database and oci_pdo_moodle_database. There is a new configuration option $CFG->dblibrary which can be adodb, pdo or anything else in case of 3rd party modifications. The $CFG->dbtype is expected to contain internal driver name.

Each database class must fully abstract all operations with database including API for XMLDB editor.

To minimise conversion costs all function names are kept, though the prepared statements need a bit different method parameters:

  • $sql parameters need $params array with values, $sql query must not contain any user submitted data - instead use ? or :name parameters, both types are supported, but not both in the same query
  • $select parameters must be accompanied by $params too
  • $field1, $value1 are replaced by $conditions array - you can have more than one condition in get_records() and more than three in get_record() now

The use of recordsets was changed substantially - they can be used in foreach($rs as $record) directly. Recordset closing should be mandatory now, this might help with performance and memory consumption later. Each library abstraction or database class must define own recorset class with moodle_recordset interface.

Parameter types - :name and ?

The :name style seems to be much more flexible than ?.

Pros (name:)

  • order not important
  • possible validation

Pros (?)

  • supported by all backends except Ora
  • no probelm with overwriting


  • expects ? except for ora (not sure)


  • supports both

API changes

Donal suggested that get_records and families should return an empty array rather than false so you can iterate straight over them.

My +1 to return array() if nothing found and false if error, I do not think that if ($resultarray =get_records()) {} is wrong. Petr Škoda (škoďák)

default $DB object

Martin proposed to use global $DB instead of function returning $mdb instance or factory methods. Nicolas confirmed that it should be suitable for unit testing purposes.

sample code:

 function xyz($userid, $courseid) {
     global $DB, $CFG;
     if ($records = get_records_sql('SELECT * FROM {$CFG->prefix}abc WHERE userid=? AND courseid=?', array($userid, $courseid))) {

Things like $db->debug = true; can be changed to

$DB->set_debug(true);  // sets the adodb debug var