Development:DB layer 2.0 implementation plan

Note: This page is a work-in-progress. Feedback and suggested improvements are welcome. Please join the discussion on moodle.org or use the page comments.

Objectives

• Switch to prepared statements - using placeholders and preparing statements protects against sql injection
• Remove the need for data to be add/strip slashed all over the place in the code.
• Enable easier and productive unit tests.

Requirements

• 100% cross-db
• OOP implementation:
  • Full abstraction: Hide underlying libraries (adodb, pdo...) and drivers to Moodle developers completely.
  • Make it so that the database object can be subclassed - this means for unit tests that want to test database access, the test framework can override necessary methods
  • support ? and :param parameter types (independently of the param types supported by each driver).
  • global $DB
  • profiling, logging and exceptions
• Easy to use (consistent and similar to current dmllib 1.0)
• Easy to migrate (provide documentation and utilities to help on that)
• Complete unit testing (self)
• Easy unit testing (moodle - real and mockup)
• Well documented (PHPDocs + use examples)
• Tasks, bugs and progress tracked in MDL-14679
• Due date: June 24th 2008

See also

• XMLDB Documentation: where both xmldb and ddl stuff is explained.
• DDL functions - Documentation for all the Data Definition Language (DDL) functions available inside Moodle.
• DML functions - Documentation for all the Data Manipulation Language (DML) functions available inside Moodle.

DRAFT-DRAFT-DRAFT

done:

1. import latest adodb for PHP5

in patch MDL-14679:

1. implement basic API as abstract moodle_database class and moodle_recordset interface
2. tweak lib/setup.php and related files - place all $DB setup code into one function setup_DB()
3. keep old global $db until everything is converted to new global $DB
4. all dmllib functions that do not accept $sql or $select can be rewritten to use new $DB - move those classes to lib/dmllib_deprecated.php for now - this allows us to run moodle in hybrid mode for testing purposes
5. move all unused dmllib functions to lib/dmllib_removed.php and remove body - this could be useful during the migration phase of contrib (make them to act as wrappers over new dmllib and emit debugging messages!)
6. implement basic mysql and postgresql classes

todo:

1. test, refactor, rewrite, improve, etc. the new dmllib classes
2. implement classes for all supported backends - oracle, mssql
3. majority of code can be converted to new classes while keeping magic quotes on (we are dealing with numbers mostly), the formslib can already return unslashed data and we can add new parameter to data_submitted() too
4. kill the magic quotes and convert the rest

We could also prepare an experimental mysql pdo driver in parallel - this could help to uncover potential problems in API design or implementation.