Set default db schema search path to public,pg_catalog

With the addition of the 'evergreen' schema, we discovered that
all of the unqualified database objects were being created in
the 'evergreen' schema if the database user name was 'evergreen'
(as the default search path is "$user",public we started matching
"$user").

Thomas Berezansky proposed a variation of the function provided
here for changing database settings. By creating the function in
the first schema file, the subsequent connections pick up the
new default search path and all works blissfully as it once did.

Long term, we should probably move towards explicitly qualified
database objects.

git-svn-id: svn://svn.open-ils.org/ILS/trunk@19759 dcc99617-32d9-48b4-a31d-7c20da2025e4

diff --git a/Open-ILS/src/sql/Pg/000.functions.general.sql b/Open-ILS/src/sql/Pg/000.functions.general.sql
new file mode 100644 (file)
index 0000000..f960341
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/000.functions.general.sql
@@ -0,0 +1,22 @@
+-- Rather than polluting the public schema with general Evergreen
+-- functions, carve out a dedicated schema
+
+DROP SCHEMA IF EXISTS evergreen CASCADE;
+
+BEGIN;
+
+CREATE SCHEMA evergreen;
+
+CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$
+    return lc(shift);
+$$ LANGUAGE PLPERLU STRICT IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION evergreen.change_db_setting(setting_name TEXT, settings TEXT[]) RETURNS VOID AS $$
+BEGIN
+EXECUTE 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET ' || quote_ident(setting_name) || ' = ' || array_to_string(settings, ',');
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT evergreen.change_db_setting('search_path', ARRAY['public','pg_catalog']);
+
+COMMIT;

diff --git a/Open-ILS/src/sql/Pg/002.functions.general.sql b/Open-ILS/src/sql/Pg/002.functions.general.sql
deleted file mode 100644 (file)
index f9407a5..0000000
--- a/Open-ILS/src/sql/Pg/002.functions.general.sql
+++ /dev/null
@@ -1,14 +0,0 @@
--- Rather than polluting the public schema with general Evergreen
--- functions, carve out a dedicated schema
-
-DROP SCHEMA IF EXISTS evergreen CASCADE;
-
-BEGIN;
-
-CREATE SCHEMA evergreen;
-
-CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$
-    return lc(shift);
-$$ LANGUAGE PLPERLU STRICT IMMUTABLE;
-
-COMMIT;

diff --git a/Open-ILS/src/sql/Pg/build-db.sh b/Open-ILS/src/sql/Pg/build-db.sh
index 0e8f6f8..ef9b7b9 100755 (executable)
--- a/Open-ILS/src/sql/Pg/build-db.sh
+++ b/Open-ILS/src/sql/Pg/build-db.sh
@@ -79,11 +79,12 @@ fi
 # This describes the order in which the SQL files will be eval'd by psql.
 # ---------------------------------------------------------------------------
 ordered_file_list="
+  000.functions.general.sql
+
   $fts_config_file
 
   001.schema.offline.sql
 
-  002.functions.general.sql
   002.schema.config.sql
   002.functions.aggregate.sql
   002.functions.config.sql