MDL-37420 add support for more PostgreSQL schemas
authorPetr Škoda <commits@skodak.org>
Tue, 8 Jan 2013 16:12:31 +0000 (17:12 +0100)
committerPetr Škoda <commits@skodak.org>
Tue, 8 Jan 2013 16:46:54 +0000 (17:46 +0100)
lib/ddl/postgres_sql_generator.php
lib/dml/pgsql_native_moodle_database.php

index 8d41086..1427141 100644 (file)
@@ -414,9 +414,11 @@ class postgres_sql_generator extends sql_generator {
         $tablename = $this->getTableName($xmldb_table);
         $sequencename = $tablename . '_id_seq';
 
         $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;
         }
                                         array($sequencename))) {
             $sequencename = false;
         }
@@ -442,9 +444,11 @@ class postgres_sql_generator extends sql_generator {
             case 'ix':
             case 'uix':
             case 'seq':
             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;
                     return true;
                 }
                 break;
@@ -452,9 +456,11 @@ class postgres_sql_generator extends sql_generator {
             case 'uk':
             case 'fk':
             case 'ck':
             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;
                     return true;
                 }
                 break;
index 0c9b18e..47d050b 100644 (file)
@@ -181,6 +181,11 @@ class pgsql_native_moodle_database extends moodle_database {
             $sql = "SET bytea_output = 'escape'; ";
         }
 
             $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);
         // 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);
         }
         $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 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);
         $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;
 
         $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);
 
         $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
 
         $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
                   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);
               ORDER BY a.attnum";
 
         $this->query_start($sql, null, SQL_QUERY_AUX);