From 520b730c5607a3856eaf09d573488b4baaaf3080 Mon Sep 17 00:00:00 2001 From: stronk7 Date: Sun, 30 Aug 2009 23:00:25 +0000 Subject: [PATCH] MDL-17969 mssql (freetds/odbtp) native driver. Passes all the tests but: - import record - reset sequence ... so it's a good moment to add it. --- lib/dml/mssql_native_moodle_database.php | 1221 +++++++++++++++++++++ lib/dml/mssql_native_moodle_recordset.php | 78 ++ 2 files changed, 1299 insertions(+) create mode 100644 lib/dml/mssql_native_moodle_database.php create mode 100644 lib/dml/mssql_native_moodle_recordset.php diff --git a/lib/dml/mssql_native_moodle_database.php b/lib/dml/mssql_native_moodle_database.php new file mode 100644 index 0000000000..fdbca55a7d --- /dev/null +++ b/lib/dml/mssql_native_moodle_database.php @@ -0,0 +1,1221 @@ +. + + +/** + * Native mssql class representing moodle database interface. + * + * @package moodlecore + * @subpackage DML + * @copyright 2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com} + * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later + */ + +require_once($CFG->libdir.'/dml/moodle_database.php'); +require_once($CFG->libdir.'/dml/mssql_native_moodle_recordset.php'); +require_once($CFG->libdir.'/dml/mssql_native_moodle_temptables.php'); + +/** + * Native mssql class representing moodle database interface. + */ +class mssql_native_moodle_database extends moodle_database { + + protected $mssql = null; + private $temptables; // Control existing temptables (mssql_moodle_temptables object) + + /** + * Detects if all needed PHP stuff installed. + * Note: can be used before connect() + * @return mixed true if ok, string if something + */ + public function driver_installed() { + if (!function_exists('mssql_connect')) { + return get_string('mssqlextensionisnotpresentinphp', 'install'); + } + return true; + } + + /** + * Returns database family type - describes SQL dialect + * Note: can be used before connect() + * @return string db family name (mysql, postgres, mssql, oracle, etc.) + */ + public function get_dbfamily() { + return 'mssql'; + } + + /** + * Returns more specific database driver type + * Note: can be used before connect() + * @return string db type mysql, pgsql, postgres7 + */ + protected function get_dbtype() { + return 'mssql'; + } + + /** + * Returns general database library name + * Note: can be used before connect() + * @return string db type adodb, pdo, native + */ + protected function get_dblibrary() { + return 'native'; + } + + /** + * Returns localised database type name + * Note: can be used before connect() + * @return string + */ + public function get_name() { + return get_string('nativemssql', 'install'); + } + + /** + * Returns sql generator used for db manipulation. + * Used mostly in upgrade.php scripts. mssql overrides it + * in order to share the mssql_moodle_temptables + * between the driver and the generator + * + * @return object database_manager instance + */ + public function get_manager() { + global $CFG; + + if (!$this->database_manager) { + require_once($CFG->libdir.'/ddllib.php'); + + $classname = $this->get_dbfamily().'_sql_generator'; + require_once("$CFG->libdir/ddl/$classname.php"); + $generator = new $classname($this, $this->temptables); + + $this->database_manager = new database_manager($this, $generator); + } + return $this->database_manager; + } + + /** + * Returns localised database configuration help. + * Note: can be used before connect() + * @return string + */ + public function get_configuration_help() { + return get_string('nativemssqlhelp', 'install'); + } + + /** + * Returns localised database description + * Note: can be used before connect() + * @return string + */ + public function get_configuration_hints() { + $str = get_string('databasesettingssub_mssql', 'install'); + $str .= "

'; + $str .= get_string('moodledocslink', 'install') . '

'; + return $str; + } + + /** + * Connect to db + * Must be called before other methods. + * @param string $dbhost + * @param string $dbuser + * @param string $dbpass + * @param string $dbname + * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used + * @param array $dboptions driver specific options + * @return bool true + * @throws dml_connection_exception if error + */ + public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) { + if ($prefix == '' and !$this->external) { + //Enforce prefixes for everybody but mysql + throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily()); + } + + $driverstatus = $this->driver_installed(); + + if ($driverstatus !== true) { + throw new dml_exception('dbdriverproblem', $driverstatus); + } + + $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions); + + ob_start(); + if (!empty($this->dboptions['dbpersit'])) { // persistent connection + $this->mssql = mssql_pconnect($this->dbhost, $this->dbuser, $this->dbpass, true); + } else { + $this->mssql = mssql_connect($this->dbhost, $this->dbuser, $this->dbpass, true); + } + $dberr = ob_get_contents(); + ob_end_clean(); + + if ($this->mssql === false) { + $this->mssql = null; + throw new dml_connection_exception($dberr); + } + + // already connected, select database and set some env. variables + $this->query_start("--mssql_select_db", null, SQL_QUERY_AUX); + $result = mssql_select_db($this->dbname, $this->mssql); + $this->query_end($result); + + // No need to set charset. It's UTF8, with transparent conversions + // back and forth performed both by FreeTDS or ODBTP + + // Allow quoted identifiers + $sql = "SET QUOTED_IDENTIFIER ON"; + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + + // Force ANSI nulls so the NULL check was done by IS NULL and NOT IS NULL + // instead of equal(=) and distinct(<>) simbols + $sql = "SET ANSI_NULLS ON"; + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + + // Force ANSI warnings so arithmetic/string overflows will be + // returning error instead of transparently truncating data + $sql = "SET ANSI_WARNINGS ON"; + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + // Concatenating null with anything MUST return NULL + $sql = "SET CONCAT_NULL_YIELDS_NULL ON"; + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + + // Set transactions isolation level to READ_COMMITTED + // prevents dirty reads when using transactions + + // is the default isolation level of MSSQL + $sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"; + $this->query_start($sql, NULL, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + + // Connection stabilished and configured, going to instantiate the temptables controller + $this->temptables = new mssql_native_moodle_temptables($this); + + return true; + } + + /** + * Close database connection and release all resources + * and memory (especially circular memory references). + * Do NOT use connect() again, create a new instance if needed. + */ + public function dispose() { + parent::dispose(); // Call parent dispose to write/close session and other common stuff before clossing conn + if ($this->mssql) { + mssql_close($this->mssql); + $this->mssql = null; + } + } + + /** + * Called before each db query. + * @param string $sql + * @param array array of parameters + * @param int $type type of query + * @param mixed $extrainfo driver specific extra information + * @return void + */ + protected function query_start($sql, array $params=null, $type, $extrainfo=null) { + parent::query_start($sql, $params, $type, $extrainfo); + } + + /** + * Called immediately after each db query. + * @param mixed db specific result + * @return void + */ + protected function query_end($result) { + parent::query_end($result); + } + + /** + * Returns database server info array + * @return array + */ + public function get_server_info() { + static $info; + if (!$info) { + $info = array(); + $sql = 'sp_server_info 2'; + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + $row = mssql_fetch_row($result); + $info['server'] = $row[2]; + $this->free_result($result); + + $sql = 'sp_server_info 500'; + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + $row = mssql_fetch_row($result); + $info['version'] = $row[2]; + $this->free_result($result); + } + return $info; + } + + protected function is_min_version($version) { + $server = $this->get_server_info(); + $server = $server['version']; + return version_compare($server, $version, '>='); + } + + /** + * Converts short table name {tablename} to real table name + * supporting temp tables (#) if detected + * + * @param string sql + * @return string sql + */ + protected function fix_table_names($sql) { + if (preg_match_all('/\{([a-z][a-z0-9_]*)\}/', $sql, $matches)) { + foreach($matches[0] as $key=>$match) { + $name = $matches[1][$key]; + $sql = str_replace($match, $this->temptables->get_correct_name($name), $sql); + } + } + return $sql; + } + + /** + * Returns supported query parameter types + * @return bitmask + */ + protected function allowed_param_types() { + return SQL_PARAMS_QM; // Not really, but emulated, see emulate_bound_params() + } + + /** + * Returns last error reported by database engine. + */ + public function get_last_error() { + return mssql_get_last_message(); + } + + /** + * Return tables in database WITHOUT current prefix + * @return array of table names in lowercase and without prefix + */ + public function get_tables($usecache=true) { + if ($usecache and $this->tables !== null) { + return $this->tables; + } + $this->tables = array(); + $sql = "SELECT table_name + FROM information_schema.tables + WHERE table_name LIKE '$this->prefix%' + AND table_type = 'BASE TABLE'"; + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + if ($result) { + while ($row = mssql_fetch_row($result)) { + $tablename = reset($row); + if (strpos($tablename, $this->prefix) !== 0) { + continue; + } + $tablename = substr($tablename, strlen($this->prefix)); + $this->tables[$tablename] = $tablename; + } + $this->free_result($result); + } + + // Add the currently available temptables + $this->tables = array_merge($this->tables, $this->temptables->get_temptables()); + return $this->tables; + } + + /** + * Return table indexes - everything lowercased + * @return array of arrays + */ + public function get_indexes($table) { + $indexes = array(); + $tablename = $this->prefix.$table; + + // Indexes aren't covered by information_schema metatables, so we need to + // go to sys ones. Skipping primary key indexes on purpose. + $sql = "SELECT i.name AS index_name, i.is_unique, ic.index_column_id, c.name AS column_name + FROM sys.indexes i + JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id + JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id + JOIN sys.tables t ON i.object_id = t.object_id + WHERE t.name = '$tablename' + AND i.is_primary_key = 0 + ORDER BY i.name, i.index_id, ic.index_column_id"; + + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + if ($result) { + $lastindex = ''; + $unique = false; + $columns = array(); + while ($row = mssql_fetch_assoc($result)) { + if ($lastindex and $lastindex != $row['index_name']) { // Save lastindex to $indexes and reset info + $indexes[$lastindex] = array('unique' => $unique, 'columns' => $columns); + $unique = false; + $columns = array(); + } + $lastindex = $row['index_name']; + $unique = empty($row['is_unique']) ? false : true; + $columns[] = $row['column_name']; + } + if ($lastindex ) { // Add the last one if exists + $indexes[$lastindex] = array('unique' => $unique, 'columns' => $columns); + } + $this->free_result($result); + } + return $indexes; + } + + /** + * Returns datailed information about columns in table. This information is cached internally. + * @param string $table name + * @param bool $usecache + * @return array array of database_column_info objects indexed with column names + */ + public function get_columns($table, $usecache=true) { + if ($usecache and isset($this->columns[$table])) { + return $this->columns[$table]; + } + + $this->columns[$table] = array(); + + $tablename = $this->temptables->get_correct_name($table); + + if (!$this->temptables->is_temptable($table)) { // normal table, get metadata from own schema + $sql = "SELECT column_name AS name, + data_type AS type, + numeric_precision AS max_length, + character_maximum_length AS char_max_length, + numeric_scale AS scale, + is_nullable AS is_nullable, + columnproperty(object_id(quotename(table_schema) + '.' + + quotename(table_name)), column_name, 'IsIdentity') AS auto_increment, + column_default AS default_value + FROM information_schema.columns + WHERE table_name = '$tablename' + ORDER BY ordinal_position"; + } else { // temp table, get metadata from tempdb schema + $sql = "SELECT column_name AS name, + data_type AS type, + numeric_precision AS max_length, + character_maximum_length AS char_max_length, + numeric_scale AS scale, + is_nullable AS is_nullable, + columnproperty(object_id(quotename(table_schema) + '.' + + quotename(table_name)), column_name, 'IsIdentity') AS auto_increment, + column_default AS default_value + FROM tempdb.information_schema.columns + WHERE table_name like '{$tablename}__________%' + ORDER BY ordinal_position"; + } + + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + if (!$result) { + return array(); + } + + while ($rawcolumn = mssql_fetch_assoc($result)) { + + $rawcolumn = (object)$rawcolumn; + + $info = new object(); + $info->name = $rawcolumn->name; + $info->type = $rawcolumn->type; + $info->meta_type = $this->mssqltype2moodletype($info->type); + + // Prepare auto_increment info + $info->auto_increment = $rawcolumn->auto_increment ? true : false; + + // Define type for auto_increment columns + $info->meta_type = ($info->auto_increment && $info->meta_type == 'I') ? 'R' : $info->meta_type; + + // id columns being auto_incremnt are PK by definition + $info->primary_key = ($info->name == 'id' && $info->meta_type == 'R' && $info->auto_increment); + + // Put correct length for character and LOB types + $info->max_length = $info->meta_type == 'C' ? $rawcolumn->char_max_length : $rawcolumn->max_length; + $info->max_length = ($info->meta_type == 'X' || $info->meta_type == 'B') ? -1 : $info->max_length; + + // Scale + $info->scale = $rawcolumn->scale ? $rawcolumn->scale : false; + + // Prepare not_null info + $info->not_null = $rawcolumn->is_nullable == 'NO' ? true : false; + + // Process defaults + $info->has_default = !empty($rawcolumn->default_value); + $info->default_value = preg_replace("/^[\(N]+[']?(.*?)[']?[\)]+$/", '\\1', $rawcolumn->default_value); + + // Process binary + $info->binary = $info->meta_type == 'B' ? true : false; + + $this->columns[$table][$info->name] = new database_column_info($info); + } + $this->free_result($result); + + return $this->columns[$table]; + } + + /** + * Normalise values based in RDBMS dependencies (booleans, LOBs...) + * + * @param database_column_info $column column metadata corresponding with the value we are going to normalise + * @param mixed $value value we are going to normalise + * @return mixed the normalised value + */ + private function normalise_value($column, $value) { + if (is_bool($value)) { /// Always, convert boolean to int + $value = (int)$value; + + } else if ($column->meta_type == 'B') { // BLOBs need to be properly "packed", but can be inserted directly if so. + if (!is_null($value)) { // If value not null, unpack it to unquoted hexadecimal byte-string format + $value = unpack('H*hex', $value); // we leave it as array, so emulate_bound_params() can detect it + } // easily and "bind" the param ok. + + } else if ($column->meta_type == 'X') { // MSSQL doesn't cast from int to text, so if text column + if (is_numeric($value)) { // and is numeric value then cast to string + $value = array('numstr' => (string)$value); // and put into array, so emulate_bound_params() will know how + } // to "bind" the param ok, avoiding reverse conversion to number + + } else if ($value === '') { + if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') { + $value = 0; // prevent '' problems in numeric fields + } + } + return $value; + } + + /** + * Selectively call mssql_free_result(), avoiding some warnings without using the horrible @ + * + * @param mssql_resource $resource resource to be freed if possible + */ + private function free_result($resource) { + if (!is_bool($resource)) { // true/false resources cannot be freed + mssql_free_result($resource); + } + } + + /** + * Provides mapping between mssql native data types and moodle_database - database_column_info - ones) + * + * @param string $mssql_type native mssql data type + * @return string 1-char database_column_info data type + */ + private function mssqltype2moodletype($mssql_type) { + $type = null; + switch (strtoupper($mssql_type)) { + case 'BIT': + $type = 'L'; + break; + case 'INT': + case 'SMALLINT': + case 'INTEGER': + case 'BIGINT': + $type = 'I'; + break; + case 'DECIMAL': + case 'REAL': + case 'FLOAT': + $type = 'N'; + break; + case 'VARCHAR': + case 'NVARCHAR': + $type = 'C'; + break; + case 'TEXT': + case 'NTEXT': + case 'VARCHAR(MAX)': + case 'NVARCHAR(MAX)': + $type = 'X'; + break; + case 'IMAGE': + case 'VARBINARY(MAX)': + $type = 'B'; + break; + case 'DATETIME': + $type = 'D'; + break; + } + if (!$type) { + throw new dml_exception('invalidmssqlnativetype', $mssql_type); + } + return $type; + } + + /** + * Do NOT use in code, to be used by database_manager only! + * @param string $sql query + * @return bool true + * @throws dml_exception if error + */ + public function change_database_structure($sql) { + $this->reset_caches(); + + $this->query_start($sql, null, SQL_QUERY_STRUCTURE); + $result = @mssql_query($sql, $this->mssql); + $this->query_end($result); + + return true; + } + + /** + * Very ugly hack which emulates bound parameters in queries + * because the mssql driver doesn't support placeholders natively at all + */ + protected function emulate_bound_params($sql, array $params=null) { + if (empty($params)) { + return $sql; + } + /// ok, we have verified sql statement with ? and correct number of params + $return = strtok($sql, '?'); + foreach ($params as $param) { + if (is_bool($param)) { + $return .= (int)$param; + + } else if (is_array($param) && isset($param['hex'])) { // detect hex binary, bind it specially + $return .= '0x' . $param['hex']; + + } else if (is_array($param) && isset($param['numstr'])) { // detect numerical strings that *must not* + $return .= "N'{$param['numstr']}'"; // be converted back to number params, but bound as strings + + } else if (is_null($param)) { + $return .= 'NULL'; + + } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646 + $return .= $param; + + } else if (is_float($param)) { + $return .= $param; + + } else { + $param = str_replace("'", "''", $param); + $return .= "N'$param'"; + } + + $return .= strtok('?'); + } + return $return; + } + + /** + * Execute general sql query. Should be used only when no other method suitable. + * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead! + * @param string $sql query + * @param array $params query parameters + * @return bool true + * @throws dml_exception if error + */ + public function execute($sql, array $params=null) { + + list($sql, $params, $type) = $this->fix_sql_params($sql, $params); + $rawsql = $this->emulate_bound_params($sql, $params); + + if (strpos($sql, ';') !== false) { + throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!'); + } + + $this->query_start($sql, $params, SQL_QUERY_UPDATE); + $result = @mssql_query($rawsql, $this->mssql); + $this->query_end($result); + $this->free_result($result); + + return true; + } + + /** + * Get a number of records as a moodle_recordset using a SQL statement. + * + * Since this method is a little less readable, use of it should be restricted to + * code where it's possible there might be large datasets being returned. For known + * small datasets use get_records_sql - it leads to simpler code. + * + * The return type is as for @see function get_recordset. + * + * @param string $sql the SQL select query to execute. + * @param array $params array of sql parameters + * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). + * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). + * @return mixed an moodle_recordset object + * @throws dml_exception if error + */ + public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { + $limitfrom = (int)$limitfrom; + $limitnum = (int)$limitnum; + $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom; + $limitnum = ($limitnum < 0) ? 0 : $limitnum; + if ($limitfrom or $limitnum) { + if ($limitnum < 1) { + $limitnum = "18446744073709551615"; + } + $fetch = $limitfrom + $limitnum; + $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i', + "\\1SELECT\\2 TOP $fetch", $sql); + } + + list($sql, $params, $type) = $this->fix_sql_params($sql, $params); + $rawsql = $this->emulate_bound_params($sql, $params); + + $this->query_start($sql, $params, SQL_QUERY_SELECT); + $result = @mssql_query($rawsql, $this->mssql); + $this->query_end($result); + + if ($limitfrom) { + @mssql_data_seek($result, $limitfrom); + } + + return $this->create_recordset($result); + } + + protected function create_recordset($result) { + return new mssql_native_moodle_recordset($result); + } + + /** + * Get a number of records as an array of objects using a SQL statement. + * + * Return value as for @see function get_records. + * + * @param string $sql the SQL select query to execute. The first column of this SELECT statement + * must be a unique value (usually the 'id' field), as it will be used as the key of the + * returned array. + * @param array $params array of sql parameters + * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). + * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). + * @return mixed an array of objects, or empty array if no records were found + * @throws dml_exception if error + */ + public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { + + $rs = $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum); + + $results = array(); + + foreach ($rs as $row) { + $id = reset($row); + if (isset($return[$id])) { + $colname = key($row); + debugging("Did you remember to make the first column something unique in your call to get_records? Duplicate value '$id' found in column '$colname'.", DEBUG_DEVELOPER); + } + $results[$id] = (object)$row; + } + $rs->close(); + + return $results; + } + + /** + * Selects records and return values (first field) as an array using a SQL statement. + * + * @param string $sql The SQL query + * @param array $params array of sql parameters + * @return mixed array of values + * @throws dml_exception if error + */ + public function get_fieldset_sql($sql, array $params=null) { + + $rs = $this->get_recordset_sql($sql, $params); + + $results = array(); + + foreach ($rs as $row) { + $results[] = reset($row); + } + $rs->close(); + + return $results; + } + + /** + * Insert new record into database, as fast as possible, no safety checks, lobs not supported. + * @param string $table name + * @param mixed $params data record as object or array + * @param bool $returnit return it of inserted record + * @param bool $bulk true means repeated inserts expected + * @param bool $customsequence true if 'id' included in $params, disables $returnid + * @return true or new id + * @throws dml_exception if error + */ + public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) { + if (!is_array($params)) { + $params = (array)$params; + } + + $returning = ""; + + if ($customsequence) { + if (!isset($params['id'])) { + throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.'); + } + $returnid = false; + } else { + unset($params['id']); + if ($returnid) { + $returning = "; SELECT SCOPE_IDENTITY()"; + } + } + + if (empty($params)) { + throw new coding_exception('moodle_database::insert_record_raw() no fields found.'); + } + + $fields = implode(',', array_keys($params)); + $qms = array_fill(0, count($params), '?'); + $qms = implode(',', $qms); + + $sql = "INSERT INTO {" . $table . "} ($fields) VALUES($qms) $returning"; + + list($sql, $params, $type) = $this->fix_sql_params($sql, $params); + $rawsql = $this->emulate_bound_params($sql, $params); + + $this->query_start($sql, $params, SQL_QUERY_INSERT); + $result = @mssql_query($rawsql, $this->mssql); + $this->query_end($result); + + if ($returning !== "") { + $row = mssql_fetch_assoc($result); + $params['id'] = reset($row); + } + $this->free_result($result); + + if (!$returnid) { + return true; + } + + return (int)$params['id']; + } + + /** + * Insert a record into a table and return the "id" field if required. + * + * Some conversions and safety checks are carried out. Lobs are supported. + * If the return ID isn't required, then this just reports success as true/false. + * $data is an object containing needed data + * @param string $table The database table to be inserted into + * @param object $data A data object with values for one or more fields in the record + * @param bool $returnid Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned. + * @return true or new id + * @throws dml_exception if error + */ + public function insert_record($table, $dataobject, $returnid=true, $bulk=false) { + if (!is_object($dataobject)) { + $dataobject = (object)$dataobject; + } + + unset($dataobject->id); + + $columns = $this->get_columns($table); + $cleaned = array(); + + foreach ($dataobject as $field => $value) { + if (!isset($columns[$field])) { + continue; + } + $column = $columns[$field]; + $cleaned[$field] = $this->normalise_value($column, $value); + } + + return $this->insert_record_raw($table, $cleaned, $returnid, $bulk); + } + + /** + * Import a record into a table, id field is required. + * Safety checks are NOT carried out. Lobs are supported. + * + * @param string $table name of database table to be inserted into + * @param object $dataobject A data object with values for one or more fields in the record + * @return bool true + * @throws dml_exception if error + */ + public function import_record($table, $dataobject) { + $dataobject = (object)$dataobject; + + $columns = $this->get_columns($table); + $cleaned = array(); + + foreach ($dataobject as $field=>$value) { + if (!isset($columns[$field])) { + continue; + } + $cleaned[$field] = $value; + } + + return $this->insert_record_raw($table, $cleaned, false, true, true); + } + + /** + * Update record in database, as fast as possible, no safety checks, lobs not supported. + * @param string $table name + * @param mixed $params data record as object or array + * @param bool true means repeated updates expected + * @return bool true + * @throws dml_exception if error + */ + public function update_record_raw($table, $params, $bulk=false) { + if (!is_array($params)) { + $params = (array)$params; + } + if (!isset($params['id'])) { + throw new coding_exception('moodle_database::update_record_raw() id field must be specified.'); + } + $id = $params['id']; + unset($params['id']); + + if (empty($params)) { + throw new coding_exception('moodle_database::update_record_raw() no fields found.'); + } + + $sets = array(); + foreach ($params as $field=>$value) { + $sets[] = "$field = ?"; + } + + $params[] = $id; // last ? in WHERE condition + + $sets = implode(',', $sets); + $sql = "UPDATE {" . $table . "} SET $sets WHERE id = ?"; + + list($sql, $params, $type) = $this->fix_sql_params($sql, $params); + $rawsql = $this->emulate_bound_params($sql, $params); + + $this->query_start($sql, $params, SQL_QUERY_UPDATE); + $result = @mssql_query($rawsql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + return true; + } + + /** + * Update a record in a table + * + * $dataobject is an object containing needed data + * Relies on $dataobject having a variable "id" to + * specify the record to update + * + * @param string $table The database table to be checked against. + * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified. + * @param bool true means repeated updates expected + * @return bool true + * @throws dml_exception if error + */ + public function update_record($table, $dataobject, $bulk=false) { + if (!is_object($dataobject)) { + $dataobject = (object)$dataobject; + } + + $columns = $this->get_columns($table); + $cleaned = array(); + + foreach ($dataobject as $field => $value) { + if (!isset($columns[$field])) { + continue; + } + $column = $columns[$field]; + $cleaned[$field] = $this->normalise_value($column, $value); + } + + return $this->update_record_raw($table, $cleaned, $bulk); + } + + /** + * Set a single field in every table record which match a particular WHERE clause. + * + * @param string $table The database table to be checked against. + * @param string $newfield the field to set. + * @param string $newvalue the value to set the field to. + * @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 + * @return bool true + * @throws dml_exception if error + */ + public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) { + + if ($select) { + $select = "WHERE $select"; + } + if (is_null($params)) { + $params = array(); + } + + /// Get column metadata + $columns = $this->get_columns($table); + $column = $columns[$newfield]; + + $newvalue = $this->normalise_value($column, $newvalue); + + if (is_null($newvalue)) { + $newfield = "$newfield = NULL"; + } else { + $newfield = "$newfield = ?"; + array_unshift($params, $newvalue); + } + $sql = "UPDATE {" . $table . "} SET $newfield $select"; + + list($sql, $params, $type) = $this->fix_sql_params($sql, $params); + $rawsql = $this->emulate_bound_params($sql, $params); + + $this->query_start($sql, $params, SQL_QUERY_UPDATE); + $result = @mssql_query($rawsql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + + return true; + } + + /** + * Delete one or more records from a table which match a particular WHERE clause. + * + * @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 (used to define the selection criteria). + * @param array $params array of sql parameters + * @return bool true + * @throws dml_exception if error + */ + public function delete_records_select($table, $select, array $params=null) { + + if ($select) { + $select = "WHERE $select"; + } + + $sql = "DELETE FROM {" . $table . "} $select"; + + list($sql, $params, $type) = $this->fix_sql_params($sql, $params); + $rawsql = $this->emulate_bound_params($sql, $params); + + $this->query_start($sql, $params, SQL_QUERY_UPDATE); + $result = @mssql_query($rawsql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + + return true; + } + +/// SQL helper functions + + public function sql_bitxor($int1, $int2) { + return '(' . $this->sql_bitor($int1, $int2) . ' - ' . $this->sql_bitand($int1, $int2) . ')'; + } + + public function sql_cast_char2int($fieldname, $text=false) { + return ' CAST(' . $fieldname . ' AS INT) '; + } + + public function sql_ceil($fieldname) { + return ' CEILING(' . $fieldname . ')'; + } + + public function sql_concat() { + $arr = func_get_args(); + foreach ($arr as $key => $ele) { + $arr[$key] = ' CAST(' . $ele . ' AS VARCHAR(255)) '; + } + $s = implode(' + ', $arr); + if ($s === '') { + return " '' "; + } + return " $s "; + } + + public function sql_concat_join($separator="' '", $elements=array()) { + for ($n=count($elements)-1; $n > 0 ; $n--) { + array_splice($elements, $n, 0, $separator); + } + $s = implode(' + ', $elements); + if ($s === '') { + return " '' "; + } + return " $s "; + } + + public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) { + if ($textfield) { + return $this->sql_compare_text($fieldname)." = '' "; + } else { + return " $fieldname = '' "; + } + } + + /** + * Returns the SQL text to be used to calculate the length in characters of one expression. + * @param string fieldname or expression to calculate its length in characters. + * @return string the piece of SQL code to be used in the statement. + */ + public function sql_length($fieldname) { + return ' LEN(' . $fieldname . ')'; + } + + public function sql_order_by_text($fieldname, $numchars=32) { + return ' CONVERT(varchar, ' . $fieldname . ', ' . $numchars . ')'; + } + + /** + * Returns the SQL for returning searching one string for the location of another. + */ + public function sql_position($needle, $haystack) { + return "CHARINDEX(($needle), ($haystack))"; + } + + /** + * Returns the proper substr() function for each DB. + * NOTE: this was originally returning only function name + * + * @param string $expr some string field, no aggregates + * @param mixed $start integer or expresion evaluating to int + * @param mixed $length optional integer or expresion evaluating to int + * @return string sql fragment + */ + public function sql_substr($expr, $start, $length=false) { + if (count(func_get_args()) < 2) { + throw new coding_exception('moodle_database::sql_substr() requires at least two parameters', 'Originaly this function wa +s only returning name of SQL substring function, it now requires all parameters.'); + } + if ($length === false) { + return "SUBSTRING($expr, $start, (LEN($expr) - $start + 1))"; + } else { + return "SUBSTRING($expr, $start, $length)"; + } + } + +/// session locking + + public function session_lock_supported() { + return false; + } + + public function get_session_lock($rowid) { + // NOTE: there is a potential locking problem for database running + // multiple instances of moodle, we could try to use pg_advisory_lock(int, int), + // luckily there is not a big chance that they would collide + if (!$this->session_lock_supported()) { + return; + } + + parent::get_session_lock($rowid); + $sql = "SELECT pg_advisory_lock($rowid)"; + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = pg_query($this->pgsql, $sql); + $this->query_end($result); + + if ($result) { + pg_free_result($result); + } + } + + public function release_session_lock($rowid) { + if (!$this->session_lock_supported()) { + return; + } + parent::release_session_lock($rowid); + + $sql = "SELECT pg_advisory_unlock($rowid)"; + $this->query_start($sql, null, SQL_QUERY_AUX); + $result = pg_query($this->pgsql, $sql); + $this->query_end($result); + + if ($result) { + pg_free_result($result); + } + } + +/// transactions + + /** + * on DBs that support it, switch to transaction mode and begin a transaction + * you'll need to ensure you call commit_sql() or your changes *will* be lost. + * + * this is _very_ useful for massive updates + */ + public function begin_sql() { + if (!parent::begin_sql()) { + return false; + } + $sql = "BEGIN TRANSACTION"; // Will be using READ COMMITTED isolation + $this->query_start($sql, NULL, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + + return true; + } + + /** + * on DBs that support it, commit the transaction + */ + public function commit_sql() { + if (!parent::commit_sql()) { + return false; + } + $sql = "COMMIT TRANSACTION"; + $this->query_start($sql, NULL, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + + return true; + } + + /** + * on DBs that support it, rollback the transaction + */ + public function rollback_sql() { + if (!parent::rollback_sql()) { + return false; + } + $sql = "ROLLBACK TRANSACTION"; + $this->query_start($sql, NULL, SQL_QUERY_AUX); + $result = mssql_query($sql, $this->mssql); + $this->query_end($result); + + $this->free_result($result); + + return true; + } +} diff --git a/lib/dml/mssql_native_moodle_recordset.php b/lib/dml/mssql_native_moodle_recordset.php new file mode 100644 index 0000000000..6ed2568619 --- /dev/null +++ b/lib/dml/mssql_native_moodle_recordset.php @@ -0,0 +1,78 @@ +. + +/** + * MSSQL specific recorset. + * + * @package moodlecore + * @subpackage DML + * @copyright 2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com} + * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later + */ + +require_once($CFG->libdir.'/dml/moodle_recordset.php'); + +class mssql_native_moodle_recordset extends moodle_recordset { + + protected $rsrc; + protected $current; + + public function __construct($rsrc) { + $this->rsrc = $rsrc; + $this->current = $this->fetch_next(); + } + + public function __destruct() { + $this->close(); + } + + private function fetch_next() { + if ($row = mssql_fetch_assoc($this->rsrc)) { + $row = array_change_key_case($row, CASE_LOWER); + } + return $row; + } + + public function current() { + return (object)$this->current; + } + + public function key() { + /// return first column value as key + if (!$this->current) { + return false; + } + $key = reset($this->current); + return $key; + } + + public function next() { + $this->current = $this->fetch_next(); + } + + public function valid() { + return !empty($this->current); + } + + public function close() { + if ($this->rsrc) { + mssql_free_result($this->rsrc); + $this->rsrc = null; + } + $this->current = null; + } +} -- 2.39.5