Talk:DB layer 2.0

Various Notes (before 20080515)

Interface proposal

Sample implementation with basic API definition is in MDL-14679

DML (Data Manipulation Language)

lib/dml/ classes are used to read/write data from database.

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, it must not be used outside of dml.

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.

While I like the foreach iterators here... this makes me think if that won't cause people to get confused between get_record() and get_recordset() functions. First ones doesn't need closing but second ones yes. Also, while thinking on this... do you think we could profile how many open recordsets are left open per request, debugging error if necessary? That will help developers, for sure. Eloy Lafuente (stronk7) 19:27, 6 May 2008 (CDT)

get_records() and similar functions that return arrays now return empty array if nothing found and false only if error occured, this should not cause any major regressions, all code should be verified during the conversion (proposed by Donal).

Database classes should not depend on $CFG settings - instead supplly them in constructors or use set_property() methods, this will allow us to use the same library for enrolment and auth plugins.

DDL (Database Definition Language)

lib/ddl classes are used to manipulate database structure - this is done usually from upgrade/installation scripts only.

Database manager instance may be obtained from database instance (ex: $dbman = $DB->get_manager();), this database manager replaces the old functions used in upgrade scripts (ex: $dbman->drop_table($table), $dbman->field_exists($table, $field))

Each database family has one generator class which may be extended to support special types of drivers, these generators are by default shared by all drivers of the same family.

Various Notes (before 20080527)

Agenda:

• From previous meetings:
  • DDL Exceptions.
  • Workshop to support 2.0 dmllib (assignee)
  • Journal to support 2.0 dmllib (Petr)
  • M4 way to go (HEAD, branch, patch)

• Status:
  • M2 status (each one). What's done, what's missing. MDL-14679
  • Migration guide
  • What to do between May 28th and June 11th (M3)
  • Commit May 27th

• Problems:
  • Quick review of https://docs.moodle.org/en/Development:dmllib_2.0_problems

• Everything else

• Next meeting

What parts of adodb do we use/need

• connect to database, configure and set encoding
• execute sql without result with bound parameters
• get results as recorset with bound parameters
• get results as array with bound parameters (can be emulated)
• transactions - begin, end, rollback
• get table columns information - needed for insert and update data validation (can be partially emulated, in fact this could be nice perf boost to use the data we already have in install.xml's)
• get list of tables
• get list of indexes
• get list of keys
• debugging, logging , version info, etc.

PDO and native drivers in PHP5 offer the same level of functionality (except the legacy mysql driver).

Type hinting in sql parameters (will not be implemented)

All db drivers support some sort of type hinting when binding sql parameters. There is also option to autodetect the type from native PHP data type, but unfortunately this does not work well in our codebase (Example is error when passing empty string instead of 0 in pg). The API would be relatively simple when binding parameters one by one, but it is at present unsolved when defining parameters with array.

In case of insert and update we can get the types from our table definitions or by looking directly into database, but in case of general query parameters it is often hard to guess (especially in case of empty string ).

Solution could be to use special hints which would be part of parameter name, the last letter (if capital) would describe the type of parameter :paramN for integer number (seems better than I), :paramF for floating point numbers, paramT for text, paramC for char.

We are extremely lucky we do not use dates, blobs and other complex data types that differ in each database engine. In any case we should always try to submit valid parameter data, supply data types and report potential problems to developers and not rely on various conversions especially in mysql.