From af12ea93ba7979232035cd2e9982719cb494232b Mon Sep 17 00:00:00 2001 From: skodak Date: Fri, 3 Jul 2009 22:38:56 +0000 Subject: [PATCH] MDL-19689 new $strictness parameter in $DB->get_record() and $DB->get_field() family of DML methods --- lang/en_utf8/error.php | 3 + lib/ddl/simpletest/testddl.php | 8 +-- lib/dml/moodle_database.php | 91 +++++++++++++++++--------- lib/dml/oci_native_moodle_database.php | 57 ++++++---------- lib/dml/simpletest/testdml.php | 21 ++++++ lib/dmllib.php | 78 +++++++++++++++++++++- 6 files changed, 185 insertions(+), 73 deletions(-) diff --git a/lang/en_utf8/error.php b/lang/en_utf8/error.php index 211ee8d06c..fb7337cea0 100644 --- a/lang/en_utf8/error.php +++ b/lang/en_utf8/error.php @@ -272,6 +272,8 @@ $string['invalidoutcome'] = 'Incorrect outcome id'; $string['invalidpagesize'] = 'Invalid page size'; $string['invalidpaymentmethod'] = 'Invalid payment method: $a'; $string['invalidqueryparam'] = 'ERROR: Incorrect number of query parameters. Expected $a->expected, got $a->actual.'; +$string['invalidrecord'] = 'Can not find data record in database table $a.'; +$string['invalidrecordunknown'] = 'Can not find data record in database.'; $string['invalidrequest'] = 'Invalid request'; $string['invalidrole'] = 'Invalid role'; $string['invalidroleid'] = 'Invalid role ID'; @@ -317,6 +319,7 @@ $string['moduleinstancedoesnotexist'] = 'The instance of this module does not ex $string['moduledisable'] = 'This module ($a) has been disabled for this particular course'; $string['modulemissingcode'] = 'Module $a is missing the code needed to perform this function'; $string['mustbeteacher'] = 'You must be a teacher to look at this page'; +$string['multiplerecordsfound'] = 'Multiple records found, only one record expected.'; $string['multiplerestorenotallow'] = 'Multiple restore execution not allowed!'; $string['mustbeloggedin'] = 'You must be logged in to do this'; $string['needphpext'] = 'You need to add $a support to your PHP installation'; diff --git a/lib/ddl/simpletest/testddl.php b/lib/ddl/simpletest/testddl.php index 5965c785c2..62a8cd08e1 100755 --- a/lib/ddl/simpletest/testddl.php +++ b/lib/ddl/simpletest/testddl.php @@ -433,7 +433,7 @@ class ddl_test extends UnitTestCase { $this->assertEqual($columns['oneinteger']->has_default , true); $this->assertEqual($columns['oneinteger']->default_value, 2); $this->assertEqual($columns['oneinteger']->meta_type ,'I'); - $this->assertEqual($DB->get_field('test_table1', 'oneinteger', array()), 2); //check default has been applied + $this->assertEqual($DB->get_field('test_table1', 'oneinteger', array(), 1), 2); //check default has been applied /// add one numeric field and check it $field = new xmldb_field('onenumber'); @@ -450,7 +450,7 @@ class ddl_test extends UnitTestCase { $this->assertEqual($columns['onenumber']->has_default , true); $this->assertEqual($columns['onenumber']->default_value, 2.550); $this->assertEqual($columns['onenumber']->meta_type ,'N'); - $this->assertEqual($DB->get_field('test_table1', 'onenumber', array()), 2.550); //check default has been applied + $this->assertEqual($DB->get_field('test_table1', 'onenumber', array(), 1), 2.550); //check default has been applied /// add one float field and check it (not official type - must work as number) $field = new xmldb_field('onefloat'); @@ -466,7 +466,7 @@ class ddl_test extends UnitTestCase { $this->assertEqual($columns['onefloat']->has_default , true); $this->assertEqual($columns['onefloat']->default_value, 3.550); $this->assertEqual($columns['onefloat']->meta_type ,'N'); - $this->assertEqual($DB->get_field('test_table1', 'onefloat', array()), 3.550); //check default has been applied + $this->assertEqual($DB->get_field('test_table1', 'onefloat', array(), 1), 3.550); //check default has been applied /// add one char field and check it $field = new xmldb_field('onechar'); @@ -483,7 +483,7 @@ class ddl_test extends UnitTestCase { $this->assertEqual($columns['onechar']->has_default , true); $this->assertEqual($columns['onechar']->default_value,'Nice dflt!'); $this->assertEqual($columns['onechar']->meta_type ,'C'); - $this->assertEqual($DB->get_field('test_table1', 'onechar', array()), 'Nice dflt!'); //check default has been applied + $this->assertEqual($DB->get_field('test_table1', 'onechar', array(), 1), 'Nice dflt!'); //check default has been applied /// add one text field and check it $field = new xmldb_field('onetext'); diff --git a/lib/dml/moodle_database.php b/lib/dml/moodle_database.php index c1e1e4e5ef..82fb4cafb9 100644 --- a/lib/dml/moodle_database.php +++ b/lib/dml/moodle_database.php @@ -1128,13 +1128,15 @@ abstract class moodle_database { * @param string $table The table to select from. * @param array $conditions optional array $fieldname=>requestedvalue with AND in between * @param string $fields A comma separated list of fields to be returned from the chosen table. - * @param bool $ignoremultiple ignore multiple records if found - * @return maixed a fieldset object containing the first mathcing record or false if not found + * @param int $strictness 0 means compatible mode, false returned if record not found, debug message if more found; + * 1 means ignore multiple records found, return first (not recommended); + * 2 means throw exception if no record or multiple records found (MUST_EXIST constant) + * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode * @throws dml_exception if error */ - public function get_record($table, array $conditions, $fields='*', $ignoremultiple=false) { + public function get_record($table, array $conditions, $fields='*', $strictness=0) { list($select, $params) = $this->where_clause($conditions); - return $this->get_record_select($table, $select, $params, $fields, $ignoremultiple); + return $this->get_record_select($table, $select, $params, $fields, $strictness); } /** @@ -1143,42 +1145,57 @@ abstract class moodle_database { * @param string $table The database table to be checked against. * @param string $select A fragment of SQL to be used in a where clause in the SQL call. * @param array $params array of sql parameters - * @param string $fields A comma separated list of fields to be returned from the chosen table. - * @param bool $ignoremultiple ignore multiple records if found - * @return maixed a fieldset object containing the first mathcing record or false if not found + * @param int $strictness 0 means compatible mode, false returned if record not found, debug message if more found; + * 1 means ignore multiple records found, return first (not recommended); + * 2 means throw exception if no record or multiple records found (MUST_EXIST constant) + * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode * @throws dml_exception if error */ - public function get_record_select($table, $select, array $params=null, $fields='*', $ignoremultiple=false) { + public function get_record_select($table, $select, array $params=null, $fields='*', $strictness=0) { if ($select) { $select = "WHERE $select"; } - return $this->get_record_sql("SELECT $fields FROM {$this->prefix}$table $select", $params, $ignoremultiple); + try { + return $this->get_record_sql("SELECT $fields FROM {$this->prefix}$table $select", $params, $strictness); + } catch (dml_missing_record_exception $e) { + // create new exception which will contain correct table name + throw new dml_missing_record_exception($table, $e->sql, $e->params); + } } /** * Get a single database record as an object using a SQL statement. * - * The SQL statement should normally only return one record. In debug mode - * you will get a warning if more records are found. In non-debug mode, - * it just returns the first record. - * - * Use get_records_sql() if more matches possible! + * The SQL statement should normally only return one record. + * It is recommended to use get_records_sql() if more matches possible! * * @param string $sql The SQL string you wish to be executed, should normally only return one record. * @param array $params array of sql parameters - * @param bool $ignoremultiple ignore multiple records if found - * @return maixed a fieldset object containing the first mathcing record or false if not found + * @param int $strictness 0 means compatible mode, false returned if record not found, debug message if more found; + * 1 means ignore multiple records found, return first (not recommended); + * 2 means throw exception if no record or multiple records found (MUST_EXIST constant) + * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode * @throws dml_exception if error */ - public function get_record_sql($sql, array $params=null, $ignoremultiple=false) { - $count = $ignoremultiple ? 1 : 2; - + public function get_record_sql($sql, array $params=null, $strictness=0) { + $strictness = (int)$strictness; + if ($strictness == 1) { + $count = 1; + } else { + $count = 0; + } if (!$records = $this->get_records_sql($sql, $params, 0, $count)) { // not found + if ($strictness == 2) { //MUST_EXIST + throw new dml_missing_record_exception('', $sql, $params); + } return false; } - if (!$ignoremultiple and count($records) > 1) { + if (count($records) > 1) { + if ($strictness == 2) { //MUST_EXIST + throw new dml_multiple_records_exception($sql, $params); + } debugging('Error: mdb->get_record() found more than one record!'); } @@ -1192,12 +1209,15 @@ abstract class moodle_database { * @param string $table the table to query. * @param string $return the field to return the value of. * @param array $conditions optional array $fieldname=>requestedvalue with AND in between + * @param int $strictness 0 means compatible mode, false returned if record not found, debug message if more found; + * 1 means ignore multiple records found, return first; + * 2 means throw exception if no record or multiple records found (MUST_EXIST constant) * @return mixed the specified value false if not found * @throws dml_exception if error */ - public function get_field($table, $return, array $conditions) { + public function get_field($table, $return, array $conditions, $strictness=0) { list($select, $params) = $this->where_clause($conditions); - return $this->get_field_select($table, $return, $select, $params); + return $this->get_field_select($table, $return, $select, $params, $strictness); } /** @@ -1207,14 +1227,22 @@ abstract class moodle_database { * @param string $return the field to return the value of. * @param string $select A fragment of SQL to be used in a where clause returning one row with one column * @param array $params array of sql parameters + * @param int $strictness 0 means compatible mode, false returned if record not found, debug message if more found; + * 1 means ignore multiple records found, return first; + * 2 means throw exception if no record or multiple records found (MUST_EXIST constant) * @return mixed the specified value false if not found * @throws dml_exception if error */ - public function get_field_select($table, $return, $select, array $params=null) { + public function get_field_select($table, $return, $select, array $params=null, $strictness=0) { if ($select) { $select = "WHERE $select"; } - return $this->get_field_sql("SELECT $return FROM {" . $table . "} $select", $params); + try { + return $this->get_field_sql("SELECT $return FROM {" . $table . "} $select", $params, $strictness); + } catch (dml_missing_record_exception $e) { + // create new exception which will contain correct table name + throw new dml_missing_record_exception($table, $e->sql, $e->params); + } } /** @@ -1224,16 +1252,19 @@ abstract class moodle_database { * @param string $return the field to return the value of. * @param string $sql The SQL query returning one row with one column * @param array $params array of sql parameters + * @param int $strictness 0 means compatible mode, false returned if record not found, debug message if more found; + * 1 means ignore multiple records found, return first; + * 2 means throw exception if no record or multiple records found (MUST_EXIST constant) * @return mixed the specified value false if not found * @throws dml_exception if error */ - public function get_field_sql($sql, array $params=null) { - if ($records = $this->get_records_sql($sql, $params, 0, 1)) { - $record = reset($records); - $record = (array)$record; - return reset($record); // first column + public function get_field_sql($sql, array $params=null, $strictness=0) { + if (!$record = $this->get_record_sql($sql, $params, $strictness)) { + return false; } - return false; + + $record = (array)$record; + return reset($record); // first column } /** diff --git a/lib/dml/oci_native_moodle_database.php b/lib/dml/oci_native_moodle_database.php index 744f896771..4700d2c169 100644 --- a/lib/dml/oci_native_moodle_database.php +++ b/lib/dml/oci_native_moodle_database.php @@ -656,51 +656,32 @@ class oci_native_moodle_database extends moodle_database { /** * Get a single database record as an object using a SQL statement. * - * The SQL statement should normally only return one record. In debug mode - * you will get a warning if more records are found. In non-debug mode, - * it just returns the first record. - * - * Use get_records_sql() if more matches possible! + * The SQL statement should normally only return one record. + * It is recommended to use get_records_sql() if more matches possible! * * @param string $sql The SQL string you wish to be executed, should normally only return one record. * @param array $params array of sql parameters - * @param bool $ignoremultiple ignore multiple records if found - * @return maixed a fieldset object containing the first mathcing record or false if not found + * @param int $mode 0 means compatible mode, false returned if record not found, debug message if more found; + * 1 means ignore multiple records found, return first (not recommended); + * 2 means throw exception if no record or multiple records found (MUST_EXIST constant) + * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode * @throws dml_exception if error */ - public function get_record_sql($sql, array $params=null, $ignoremultiple=false) { - // do not limit here - ORA does not like that - if (!$records = $this->get_records_sql($sql, $params)) { - // not found + public function get_record_sql($sql, array $params=null, $mode=0) { + $mode = (int)$mode; + if ($mode == 1) { + // do not limit here - ORA does not like that + if (!$rs = $this->get_recordset_sql($sql, $params)) { + return false; + } + foreach ($rs as $result) { + $rs->close(); + return $result; + } + $rs->close(); return false; } - - if (!$ignoremultiple and count($records) > 1) { - debugging('Error: mdb->get_record() found more than one record!'); - } - - $return = reset($records); - return $return; - } - - /** - * Get a single field value (first field) using a SQL statement. - * - * @param string $table the table to query. - * @param string $return the field to return the value of. - * @param string $sql The SQL query returning one row with one column - * @param array $params array of sql parameters - * @return mixed the specified value false if not found - * @throws dml_exception if error - */ - public function get_field_sql($sql, array $params=null) { - // do not limit here - ORA does not like that - if ($records = $this->get_records_sql($sql, $params)) { - $record = reset($records); - $record = (array)$record; - return reset($record); // first column - } - return false; + return parent::get_record_sql($sql, $params, $mode); } /** diff --git a/lib/dml/simpletest/testdml.php b/lib/dml/simpletest/testdml.php index 1a59cb8bbd..76fb7ba169 100755 --- a/lib/dml/simpletest/testdml.php +++ b/lib/dml/simpletest/testdml.php @@ -930,6 +930,27 @@ class dml_test extends UnitTestCase { $this->assertEqual(2, $record->course); + // record not found + $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), 0)); + $this->assertFalse($record = $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), 1)); + try { + $DB->get_record_sql("SELECT * FROM {".$tablename."} WHERE id = ?", array(666), 2); + $this->fail("Exception expected"); + } catch (dml_missing_record_exception $e) { + $this->assertTrue(true); + } + + // multiple matches + ob_start(); // hide debug warning + $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), 0)); + ob_end_clean(); + $this->assertTrue($record = $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), 1)); + try { + $DB->get_record_sql("SELECT * FROM {".$tablename."}", array(), 2); + $this->fail("Exception expected"); + } catch (dml_multiple_records_exception $e) { + $this->assertTrue(true); + } } public function test_get_field() { diff --git a/lib/dmllib.php b/lib/dmllib.php index 65b9709b8e..ba52ad57cd 100644 --- a/lib/dmllib.php +++ b/lib/dmllib.php @@ -41,6 +41,9 @@ // Require the essential require_once($CFG->libdir.'/dml/moodle_database.php'); +/** Indicates some record is required to exist */ +define('MUST_EXIST', 2); + /** * DML exception class, use instead of error() in dml code. */ @@ -60,6 +63,7 @@ class dml_exception extends moodle_exception { */ class dml_connection_exception extends dml_exception { /** + * Constructor * @param string $error */ function __construct($error) { @@ -74,11 +78,13 @@ class dml_connection_exception extends dml_exception { class dml_read_exception extends dml_exception { /** @var string */ public $error; + /** @var string */ public $sql; /** @var array */ public $params; /** + * Constructor * @param string $error * @param string $sql * @param array $params @@ -93,16 +99,86 @@ class dml_read_exception extends dml_exception { } /** - * DML read exception - triggered by SQL syntax errors, missing tables, etc. + * Caused by multiple records found in get_record() call. + */ +class dml_multiple_records_exception extends dml_exception { + /** @var string */ + public $sql; + /** @var array */ + public $params; + + /** + * Constructor + * @param string $table table name if known, '' if unknown + * @param string $sql + * @param array $params + */ + function __construct($sql='', array $params=null) { + $errorinfo = s($sql).'
['.s(var_export($params, true)).']'; + parent::__construct('multiplerecordsfound', null, $errorinfo); + } +} + +/** + * Caused by missing record that is required for normal operation. + */ +class dml_missing_record_exception extends dml_exception { + /** @var string */ + public $table; + /** @var string */ + public $sql; + /** @var array */ + public $params; + + /** + * Constructor + * @param string $table table name if known, '' if unknown + * @param string $sql + * @param array $params + */ + function __construct($tablename, $sql='', array $params=null) { + if (empty($tablename)) { + $tablename = null; + } + $this->tablename = $tablename; + $this->sql = $sql; + $this->params = $params; + + switch ($tablename) { + case null: + $errcode = 'invalidrecordunknown'; + break; + case 'course': + $errocode = empty($sql) ? 'invalidcourseid' : 'invalidrecord'; + break; + case 'course_module': + $errocode = 'invalidcoursemodule'; + break; + case 'user': + $errcode = 'invaliduser'; + break; + default: + $errcode = 'invalidrecord'; + break; + } + $errorinfo = s($sql).'
['.s(var_export($params, true)).']'; + parent::__construct($errcode, $tablename, $errorinfo); + } +} + +/** + * DML write exception - triggered by SQL syntax errors, missing tables, etc. */ class dml_write_exception extends dml_exception { /** @var string */ public $error; + /** @var string */ public $sql; /** @var array */ public $params; /** + * Constructor * @param string $error * @param string $sql * @param array $params -- 2.39.5