From c6a1e0329a3803145f7f779752cf074d923a8458 Mon Sep 17 00:00:00 2001 From: stronk7 Date: Mon, 28 Sep 2009 18:15:42 +0000 Subject: [PATCH] MDL-17491 oci native driver: adding support for local temp tables, via store --- lib/dml/oci_native_moodle_database.php | 85 ++++++++++++++++++++++-- lib/dml/oci_native_moodle_temptables.php | 68 +++++++++++++++++++ 2 files changed, 146 insertions(+), 7 deletions(-) create mode 100644 lib/dml/oci_native_moodle_temptables.php diff --git a/lib/dml/oci_native_moodle_database.php b/lib/dml/oci_native_moodle_database.php index 289600562c..d794caa280 100644 --- a/lib/dml/oci_native_moodle_database.php +++ b/lib/dml/oci_native_moodle_database.php @@ -27,6 +27,7 @@ require_once($CFG->libdir.'/dml/moodle_database.php'); require_once($CFG->libdir.'/dml/oci_native_moodle_recordset.php'); +require_once($CFG->libdir.'/dml/oci_native_moodle_temptables.php'); /** * Native oci class representing moodle database interface. @@ -37,12 +38,15 @@ require_once($CFG->libdir.'/dml/oci_native_moodle_recordset.php'); class oci_native_moodle_database extends moodle_database { protected $oci = null; + private $temptables; // Control existing temptables (oci_native_moodle_temptables object) private $last_stmt_error = null; // To store stmt errors and enable get_last_error() to detect them private $commit_status = OCI_COMMIT_ON_SUCCESS; // Autocommit ON by default. Switching to OFF (OCI_DEFAULT) // when playing with transactions - protected $last_error_reporting; // To handle oci driver default verbosity + private $last_error_reporting; // To handle oci driver default verbosity + private $unique_session_id; // To store unique_session_id. Needed for temp tables unique naming + /** * Detects if all needed PHP stuff installed. @@ -92,6 +96,30 @@ class oci_native_moodle_database extends moodle_database { return get_string('nativeoci', 'install'); // TODO: localise } + /** + * Returns sql generator used for db manipulation. + * Used mostly in upgrade.php scripts. oci overrides it + * in order to share the oci_native_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() @@ -172,9 +200,23 @@ class oci_native_moodle_database extends moodle_database { throw new dml_connection_exception($dberr); } + // get unique session id, to be used later for temp tables stuff + $sql = 'SELECT DBMS_SESSION.UNIQUE_SESSION_ID() FROM DUAL'; + $this->query_start($sql, null, SQL_QUERY_AUX); + $stmt = $this->parse_query($sql); + $result = oci_execute($stmt, $this->commit_status); + $this->query_end($result, $stmt); + $records = null; + oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW); + oci_free_statement($stmt); + $this->unique_session_id = reset($records[0]); + //note: do not send "ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'" ! // instead fix our PHP code to convert "," to "." properly! + // Connection stabilished and configured, going to instantiate the temptables controller + $this->temptables = new oci_native_moodle_temptables($this, $this->unique_session_id); + return true; } @@ -251,6 +293,27 @@ class oci_native_moodle_database extends moodle_database { return version_compare($server, $version, '>='); } + /** + * Converts short table name {tablename} to real table name + * supporting temp tables ($this->unique_session_id based) 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]; + if ($this->temptables->is_temptable($name)) { + $sql = str_replace($match, $this->temptables->get_correct_name($name), $sql); + } else { + $sql = str_replace($match, $this->prefix.$name, $sql); + } + } + } + return $sql; + } + /** * Returns supported query parameter types * @return bitmask @@ -316,6 +379,9 @@ class oci_native_moodle_database extends moodle_database { $this->tables[$tablename] = $tablename; } + // Add the currently available temptables + $this->tables = array_merge($this->tables, $this->temptables->get_temptables()); + return $this->tables; } @@ -375,13 +441,13 @@ class oci_native_moodle_database extends moodle_database { $this->columns[$table] = array(); - $tablename = strtoupper($this->prefix.$table); - $sql = "SELECT CNAME, COLTYPE, WIDTH, SCALE, PRECISION, NULLS, DEFAULTVAL FROM COL - WHERE TNAME='$tablename' + WHERE TNAME = UPPER('{" . $table . "}') ORDER BY COLNO"; + list($sql, $params, $type) = $this->fix_sql_params($sql, null); + $this->query_start($sql, null, SQL_QUERY_AUX); $stmt = $this->parse_query($sql); $result = oci_execute($stmt, $this->commit_status); @@ -1063,7 +1129,10 @@ class oci_native_moodle_database extends moodle_database { } $values = implode(',', $values); - $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES ($values) $returning"; + $sql = "INSERT INTO {" . $table . "} ($fields) VALUES ($values)"; + list($sql, $params, $type) = $this->fix_sql_params($sql, $params); + $sql .= $returning; + $id = null; $this->query_start($sql, $params, SQL_QUERY_INSERT); @@ -1178,7 +1247,8 @@ class oci_native_moodle_database extends moodle_database { } $sets = implode(',', $sets); - $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=:id"; + $sql = "UPDATE {" . $table . "} SET $sets WHERE id=:id"; + list($sql, $params, $type) = $this->fix_sql_params($sql, $params); $this->query_start($sql, $params, SQL_QUERY_UPDATE); $stmt = $this->parse_query($sql); @@ -1262,7 +1332,8 @@ class oci_native_moodle_database extends moodle_database { $params[$newfield] = $newvalue; $newsql = "$newfield = :$newfield"; } - $sql = "UPDATE {$this->prefix}$table SET $newsql $select"; + $sql = "UPDATE {" . $table . "} SET $newsql $select"; + list($sql, $params, $type) = $this->fix_sql_params($sql, $params); $this->query_start($sql, $params, SQL_QUERY_UPDATE); $stmt = $this->parse_query($sql); diff --git a/lib/dml/oci_native_moodle_temptables.php b/lib/dml/oci_native_moodle_temptables.php new file mode 100644 index 0000000000..bfbef80ba1 --- /dev/null +++ b/lib/dml/oci_native_moodle_temptables.php @@ -0,0 +1,68 @@ +. + +/** + * OCI specific temptables store. Needed because temporary tables + * in Oracle are global (to all sessions), so we need to rename them + * on the fly in order to get local (different for each session) table names. + * Also used to be able to retrieve temp table names included in the get_tables() + * method od the DB. + * + * @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_temptables.php'); + +class oci_native_moodle_temptables extends moodle_temptables { + + protected $unique_session_id; // To store unique_session_id. Needed for temp tables unique naming (upto 24cc) + protected $counter; // To get incrementally different temptable names on each add_temptable() request + + /** + * Creates new moodle_temptables instance + * @param object moodle_database instance + */ + public function __construct($mdb, $unique_session_id) { + $this->unique_session_id = $unique_session_id; + $this->counter = 1; + parent::__construct($mdb); + } + + /** + * Add one temptable to the store. + * + * Overriden because OCI only support global temptables, so we need to change completely the name, based + * in unique session identifier, to get local-like temp tables support + * tables before the prefix. + * + * Given one moodle temptable name (without prefix), add it to the store, with the + * key being the original moodle name and the value being the real db temptable name + * already prefixed + * + * Override and use this *only* if the database requires modification in the table name. + * + * @param string $tablename name without prefix of the table created as temptable + */ + public function add_temptable($tablename) { + // TODO: throw exception if exists: if ($this->is_temptable... + $this->temptables[$tablename] = $this->prefix . $this->unique_session_id . $this->counter; + $this->counter++; + } +} -- 2.39.5