Talk:DB layer 2.0

Requirements

• 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)

PDO/ADODB

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

$params - array of sql parameters $name=>$value

$where, $sql - SQL with :name placeholders; converted by us to ? for backends that need it (more flexible when constructing SQL in loops)

$fields - $fieldname=>$requestedvalue

$data - like current data object with 'id' property required

we can search easily for [^>]get_record to find obsolete code

interface moodle_database {

   /// fetching, deleting, inserting and modifying
   public function insert_record($table, object $data);
   public function insert_records($table, array $array_of_datas);

   public function update_record($table, object $data);

   public function execute($sql, array $params=null);

   public function get_record($table, array $fields);
   public function get_record_select($table, $where, array $params=null); 
   public function get_record_sql($sql, array $params=null); 
   public function get_records($table, array $fields, $limitfrom=0, $limitnum=0); // returns associative array
   public function get_records_select($table, $where, array $params=null, $limitfrom=0, $limitnum=0); // returns associative array 
   public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0); // returns associative array 
   public function get_recordset($table, array $fields, $limitfrom=0, $limitnum=0); // returns moodle_recordset
   public function get_recordset_select($table, $where, array $params=null, $limitfrom=0, $limitnum=0); // returns moodle_recordset
   public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0); // returns moodle_recordset

   public function count_records($table, array $fields, array $params=null);
   public function count_records_select($table, $where, array $params=null); 

   public function record_exists($table, array $fields);
   public function record_exists_select($table, $where, array $params=null);

   public function delete_records($table, array $fields);
   public function delete_records_select($table, $where, array $params=null);

   public function get_field($table, array $fields);

   public function set_field($table, $newfield, $newvalue, array $fields);
   public function set_field_select($table, $newfield, $newvalue, $where, array $params=null);

   /// sql contructs
   public function sql_bitand($int1, $int2);
   public function sql_bitnot($int1);
   public function sql_bitor($int1, $int2);
   public function sql_bitxor($int1, $int2);
   public function sql_cast_char2int($fieldname, $text=false);
   public function sql_compare_text($fieldname, $numchars=32);
   public function sql_concat();
   public function sql_concat_join($separator="' '", $elements=array());
   public function sql_ilike();
   public function sql_order_by_text($fieldname, $numchars=32);
   public function sql_substr();
   public function sql_emptyfrom(); // oracle hack

/* not needed if we fix code?
   public function sql_as();
   public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield);
   public function sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield);
   public function sql_max($field);
*/

   /// transactions
   public function begin_sql();
   public function commit_sql();
   public function rollback_sql();

   /// Other
   protected function sql_paging_limit($page, $recordsperpage);
}

interface moodle_recordset extends Iterator {
   public function current();
   public function key();
   public function next();
   public function rewind();
   public function valid();

/*   public function fetch_next(); // next() + current()*/
   public function close();
}

Sample usage

$mdb = get_moodle_db(); // or static factory method
if ($records = $mdb->get_records_select('sometable', "userid = :uid AND something = :ugh", array('uid'=>$userid, 'ugh'=>'grrr')) {
    foreach ($records as $rid=>$record) {
        //....
    }
}

$mdb = get_moodle_db(); // or static factory method
if ($rs = $mdb->get_recordset('sometable', array('userid'=>1, 'gid'=5))) {
    foreach ($rs as $record) { // PHP5 style iteration
        //....
    }
    $rc->close(); // we should also make sure to put this in __destruct in case developers don't close it. (Penny)
}

---

Parameter type - :name or ?

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

AdoDB

• expects ? except for ora (not sure)

PDO

• 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.