From a4c0a829cf58b18ad85c5575ce4a6d8bfe796008 Mon Sep 17 00:00:00 2001 From: dbs Date: Tue, 15 Jul 2008 04:46:15 +0000 Subject: [PATCH] More preparation for PostgreSQL 8.3 support: * quick_metarecord_map.sql - 8.3 no longer seems to support altering the table within a single transaction, so we break this into two transactions * 006.schema.permissions.sql and 020.schema.functions.sql - add explicit type casting where necessary git-svn-id: svn://svn.open-ils.org/ILS/trunk@10052 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- .../src/extras/import/quick_metarecord_map.sql | 4 ++++ Open-ILS/src/sql/Pg/006.schema.permissions.sql | 4 ++-- Open-ILS/src/sql/Pg/020.schema.functions.sql | 16 ++++++++-------- 3 files changed, 14 insertions(+), 10 deletions(-) diff --git a/Evergreen/src/extras/import/quick_metarecord_map.sql b/Evergreen/src/extras/import/quick_metarecord_map.sql index 01075b5199..f058a4dee4 100644 --- a/Evergreen/src/extras/import/quick_metarecord_map.sql +++ b/Evergreen/src/extras/import/quick_metarecord_map.sql @@ -18,6 +18,10 @@ INSERT INTO metabib.metarecord_source_map (metarecord,source) FROM biblio.record_entry b JOIN metabib.metarecord m ON (m.fingerprint = b.fingerprint); +COMMIT; + +BEGIN; + ALTER TABLE metabib.metarecord_source_map ADD CONSTRAINT metabib_metarecord_source_map_metarecord_fkey FOREIGN KEY (metarecord) REFERENCES metabib.metarecord (id) DEFERRABLE INITIALLY DEFERRED; COMMIT; diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql index a07bdf5694..e0e9980e3a 100644 --- a/Open-ILS/src/sql/Pg/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -78,9 +78,9 @@ CREATE TABLE permission.usr_grp_map ( CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permission.grp_tree AS $$ SELECT a.* - FROM connectby('permission.grp_tree','parent','id','name',$1,'100','.') + FROM connectby('permission.grp_tree'::text,'parent'::text,'id'::text,'name'::text,$1::text,100,'.'::text) AS t(keyid text, parent_keyid text, level int, branch text,pos int) - JOIN permission.grp_tree a ON a.id = t.keyid + JOIN permission.grp_tree a ON a.id::text = t.keyid::text ORDER BY CASE WHEN a.parent IS NULL THEN 0 diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index efc253d7b6..9528e1de17 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -134,30 +134,30 @@ $$ language 'plpgsql'; CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT ) RETURNS SETOF actor.org_unit AS $$ SELECT a.* - FROM connectby('actor.org_unit','id','parent_ou','name',$1,'100','.') + FROM connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text,$1::text,100,'.'::text) AS t(keyid text, parent_keyid text, level int, branch text,pos int) - JOIN actor.org_unit a ON a.id = t.keyid + JOIN actor.org_unit a ON a.id::text = t.keyid::text ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION actor.org_unit_ancestors ( INT ) RETURNS SETOF actor.org_unit AS $$ SELECT a.* - FROM connectby('actor.org_unit','parent_ou','id','name',$1,'100','.') + FROM connectby('actor.org_unit'::text,'parent_ou'::text,'id'::text,'name'::text,$1::text,100,'.'::text) AS t(keyid text, parent_keyid text, level int, branch text,pos int) - JOIN actor.org_unit a ON a.id = t.keyid + JOIN actor.org_unit a ON a.id::text = t.keyid::text ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION actor.org_unit_descendants ( INT,INT ) RETURNS SETOF actor.org_unit AS $$ SELECT a.* - FROM connectby('actor.org_unit','id','parent_ou','name', + FROM connectby('actor.org_unit'::text,'id'::text,'parent_ou'::text,'name'::text, (SELECT x.id FROM actor.org_unit_ancestors($1) x JOIN actor.org_unit_type y ON x.ou_type = y.id - WHERE y.depth = $2) - ,'100','.') + WHERE y.depth = $2)::text + ,100,'.'::text) AS t(keyid text, parent_keyid text, level int, branch text,pos int) - JOIN actor.org_unit a ON a.id = t.keyid + JOIN actor.org_unit a ON a.id::text = t.keyid::text ORDER BY CASE WHEN a.parent_ou IS NULL THEN 0 ELSE 1 END, a.name; $$ LANGUAGE SQL STABLE; -- 2.43.2