From ce5e6d8f7b58683b493e8a6326186f914bc45151 Mon Sep 17 00:00:00 2001
From: dbs <dbs@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Wed, 16 Mar 2011 03:46:43 +0000
Subject: [PATCH] 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
---
 Open-ILS/src/sql/Pg/000.functions.general.sql | 22 +++++++++++++++++++
 Open-ILS/src/sql/Pg/002.functions.general.sql | 14 ------------
 Open-ILS/src/sql/Pg/build-db.sh               |  3 ++-
 3 files changed, 24 insertions(+), 15 deletions(-)
 create mode 100644 Open-ILS/src/sql/Pg/000.functions.general.sql
 delete mode 100644 Open-ILS/src/sql/Pg/002.functions.general.sql

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
index 0000000000..f9603412dd
--- /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
index f9407a5961..0000000000
--- 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 0e8f6f8745..ef9b7b93f4 100755
--- 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
-- 
2.43.2