From 090c68483828eb5bab3b392256da345d11a744a5 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Petr=20S=CC=8Ckoda?= Date: Tue, 8 Jan 2013 17:12:31 +0100 Subject: [PATCH] MDL-37420 add support for more PostgreSQL schemas --- lib/ddl/postgres_sql_generator.php | 24 ++++++++++------- lib/dml/pgsql_native_moodle_database.php | 34 ++++++++++++------------ 2 files changed, 32 insertions(+), 26 deletions(-) diff --git a/lib/ddl/postgres_sql_generator.php b/lib/ddl/postgres_sql_generator.php index 8d410868e01..1427141589d 100644 --- a/lib/ddl/postgres_sql_generator.php +++ b/lib/ddl/postgres_sql_generator.php @@ -414,9 +414,11 @@ class postgres_sql_generator extends sql_generator { $tablename = $this->getTableName($xmldb_table); $sequencename = $tablename . '_id_seq'; - if (!$this->mdb->get_record_sql("SELECT * - FROM pg_class - WHERE relname = ? AND relkind = 'S'", + if (!$this->mdb->get_record_sql("SELECT c.* + FROM pg_catalog.pg_class c + JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace + WHERE c.relname = ? AND c.relkind = 'S' + AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", array($sequencename))) { $sequencename = false; } @@ -442,9 +444,11 @@ class postgres_sql_generator extends sql_generator { case 'ix': case 'uix': case 'seq': - if ($check = $this->mdb->get_records_sql("SELECT relname - FROM pg_class - WHERE lower(relname) = ?", array(strtolower($object_name)))) { + if ($check = $this->mdb->get_records_sql("SELECT c.relname + FROM pg_class c + JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace + WHERE lower(c.relname) = ? + AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", array(strtolower($object_name)))) { return true; } break; @@ -452,9 +456,11 @@ class postgres_sql_generator extends sql_generator { case 'uk': case 'fk': case 'ck': - if ($check = $this->mdb->get_records_sql("SELECT conname - FROM pg_constraint - WHERE lower(conname) = ?", array(strtolower($object_name)))) { + if ($check = $this->mdb->get_records_sql("SELECT c.conname + FROM pg_constraint c + JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.connamespace + WHERE lower(c.conname) = ? + AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())", array(strtolower($object_name)))) { return true; } break; diff --git a/lib/dml/pgsql_native_moodle_database.php b/lib/dml/pgsql_native_moodle_database.php index 0c9b18e21f8..47d050b2649 100644 --- a/lib/dml/pgsql_native_moodle_database.php +++ b/lib/dml/pgsql_native_moodle_database.php @@ -181,6 +181,11 @@ class pgsql_native_moodle_database extends moodle_database { $sql = "SET bytea_output = 'escape'; "; } + // Select schema if specified, otherwise the first one wins. + if (isset($this->dboptions['dbschema'])) { + $sql .= "SET search_path = '".$this->dboptions['dbschema']."'; "; + } + // Find out the bytea oid. $sql .= "SELECT oid FROM pg_type WHERE typname = 'bytea'"; $this->query_start($sql, null, SQL_QUERY_AUX); @@ -292,21 +297,12 @@ class pgsql_native_moodle_database extends moodle_database { } $this->tables = array(); $prefix = str_replace('_', '|_', $this->prefix); - if ($this->is_min_version('9.1')) { - // Use ANSI standard information_schema in recent versions where it is fast enough. - $sql = "SELECT table_name - FROM information_schema.tables - WHERE table_name LIKE '$prefix%' ESCAPE '|' - AND table_type IN ('BASE TABLE', 'LOCAL TEMPORARY')"; - } else { - // information_schema is horribly slow in <= 9.0, so use pg internals. - // Note the pg_is_other_temp_schema. We only want temp objects from our own session. - $sql = "SELECT c.relname - FROM pg_class c - WHERE c.relname LIKE '$prefix%' ESCAPE '|' + $sql = "SELECT c.relname + FROM pg_catalog.pg_class c + JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace + WHERE c.relname LIKE '$prefix%' ESCAPE '|' AND c.relkind = 'r' - AND NOT pg_is_other_temp_schema(c.relnamespace)"; - } + AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())"; $this->query_start($sql, null, SQL_QUERY_AUX); $result = pg_query($this->pgsql, $sql); $this->query_end($result); @@ -336,9 +332,11 @@ class pgsql_native_moodle_database extends moodle_database { $indexes = array(); $tablename = $this->prefix.$table; - $sql = "SELECT * - FROM pg_catalog.pg_indexes - WHERE tablename = '$tablename'"; + $sql = "SELECT i.* + FROM pg_catalog.pg_indexes i + JOIN pg_catalog.pg_namespace as ns ON ns.nspname = i.schemaname + WHERE i.tablename = '$tablename' + AND (i.schemaname = current_schema() OR ns.oid = pg_my_temp_schema())"; $this->query_start($sql, null, SQL_QUERY_AUX); $result = pg_query($this->pgsql, $sql); @@ -379,10 +377,12 @@ class pgsql_native_moodle_database extends moodle_database { $sql = "SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, d.adsrc FROM pg_catalog.pg_class c + JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid JOIN pg_catalog.pg_type t ON t.oid = a.atttypid LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = c.oid AND d.adnum = a.attnum) WHERE relkind = 'r' AND c.relname = '$tablename' AND c.reltype > 0 AND a.attnum > 0 + AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema()) ORDER BY a.attnum"; $this->query_start($sql, null, SQL_QUERY_AUX); -- 2.17.1