From 5b77fb054cd6da9d70fde4bc9b87ca177b877855 Mon Sep 17 00:00:00 2001 From: erickson Date: Mon, 23 Jun 2008 02:35:46 +0000 Subject: [PATCH] Merged revisions 9906,9908-9909 via svnmerge from svn://svn.open-ils.org/ILS/trunk ........ r9906 | miker | 2008-06-21 19:49:38 -0400 (Sat, 21 Jun 2008) | 1 line by request of Jason, accept comma separated list of fields to --trash ........ r9908 | dbs | 2008-06-22 14:04:55 -0400 (Sun, 22 Jun 2008) | 2 lines Fix typo from 9880 for upgrade SQL as well ........ r9909 | dbs | 2008-06-22 14:19:05 -0400 (Sun, 22 Jun 2008) | 3 lines Move aggregate functions into their own file and add DROP statements. Resolves dependencies and makes the schema creation more robust. ........ git-svn-id: svn://svn.open-ils.org/ILS/branches/acq-experiment@9910 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/extras/import/marc2bre.pl | 2 + Open-ILS/src/sql/Pg/002.functions.aggregate.sql | 89 ++++++++++++++++++++++ Open-ILS/src/sql/Pg/020.schema.functions.sql | 54 +------------ Open-ILS/src/sql/Pg/1.2.2.1-1.2.2.2-upgrade-db.sql | 4 +- Open-ILS/src/sql/Pg/build-db.sh | 1 + 5 files changed, 95 insertions(+), 55 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/002.functions.aggregate.sql diff --git a/Open-ILS/src/extras/import/marc2bre.pl b/Open-ILS/src/extras/import/marc2bre.pl index b6c658b3d7..1d958b7e95 100755 --- a/Open-ILS/src/extras/import/marc2bre.pl +++ b/Open-ILS/src/extras/import/marc2bre.pl @@ -50,6 +50,8 @@ GetOptions( 'quiet' => \$quiet ); +@trash_fields = split(/,/,join(',',@trash_fields)); + if ($enc) { MARC::Charset->ignore_errors(1); MARC::Charset->assume_encoding($enc); diff --git a/Open-ILS/src/sql/Pg/002.functions.aggregate.sql b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql new file mode 100644 index 0000000000..b6d308f77b --- /dev/null +++ b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql @@ -0,0 +1,89 @@ +/* + * Copyright (C) 2004-2008 Georgia Public Library Service + * Copyright (C) 2008 Equinox Software, Inc. + * Mike Rylander + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; either version 2 + * of the License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + */ + +BEGIN; + +DROP AGGREGATE IF EXISTS array_accum(anyelement) CASCADE; + +CREATE AGGREGATE array_accum ( + sfunc = array_append, + basetype = anyelement, + stype = anyarray, + initcond = '{}' +); + +CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ + SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END; +$$ LANGUAGE SQL STABLE; + +DROP AGGREGATE IF EXISTS public.first(anyelement) CASCADE; + +CREATE AGGREGATE public.first ( + sfunc = public.first_agg, + basetype = anyelement, + stype = anyelement +); + +CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ + SELECT $2; +$$ LANGUAGE SQL STABLE; + +DROP AGGREGATE IF EXISTS public.last(anyelement) CASCADE; + +CREATE AGGREGATE public.last ( + sfunc = public.last_agg, + basetype = anyelement, + stype = anyelement +); + +CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$ +SELECT + CASE WHEN $1 IS NULL + THEN $2 + WHEN $2 IS NULL + THEN $1 + ELSE $1 || ' ' || $2 + END; +$$ LANGUAGE SQL STABLE; + +DROP AGGREGATE IF EXISTS public.agg_text(text) CASCADE; + +CREATE AGGREGATE public.agg_text ( + sfunc = public.text_concat, + basetype = text, + stype = text +); + +CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$ +SELECT + CASE WHEN $1 IS NULL + THEN $2 + WHEN $2 IS NULL + THEN $1 + ELSE $1 || ' ' || $2 + END; +$$ LANGUAGE SQL STABLE; + +DROP AGGREGATE IF EXISTS public.agg_tsvector(tsvector) CASCADE; + +CREATE AGGREGATE public.agg_tsvector ( + sfunc = public.tsvector_concat, + basetype = tsvector, + stype = tsvector +); + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index 7796c01577..9ea4e8d092 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -125,59 +125,7 @@ CREATE OR REPLACE FUNCTION public.call_number_dewey( TEXT, INT ) RETURNS TEXT AS SELECT SUBSTRING(call_number_dewey($1) FROM 1 FOR $2); $$ LANGUAGE SQL STRICT IMMUTABLE; -CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ - SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END; -$$ LANGUAGE SQL STABLE; - -CREATE AGGREGATE public.first ( - sfunc = public.first_agg, - basetype = anyelement, - stype = anyelement -); - -CREATE OR REPLACE FUNCTION public.last_agg ( anyelement, anyelement ) RETURNS anyelement AS $$ - SELECT $2; -$$ LANGUAGE SQL STABLE; - -CREATE AGGREGATE public.last ( - sfunc = public.last_agg, - basetype = anyelement, - stype = anyelement -); - -CREATE OR REPLACE FUNCTION public.text_concat ( TEXT, TEXT ) RETURNS TEXT AS $$ -SELECT - CASE WHEN $1 IS NULL - THEN $2 - WHEN $2 IS NULL - THEN $1 - ELSE $1 || ' ' || $2 - END; -$$ LANGUAGE SQL STABLE; - -CREATE AGGREGATE public.agg_text ( - sfunc = public.text_concat, - basetype = text, - stype = text -); - -CREATE OR REPLACE FUNCTION public.tsvector_concat ( tsvector, tsvector ) RETURNS tsvector AS $$ -SELECT - CASE WHEN $1 IS NULL - THEN $2 - WHEN $2 IS NULL - THEN $1 - ELSE $1 || ' ' || $2 - END; -$$ LANGUAGE SQL STABLE; - -CREATE AGGREGATE public.agg_tsvector ( - sfunc = public.tsvector_concat, - basetype = tsvector, - stype = tsvector -); - -CREATE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$ +CREATE OR REPLACE FUNCTION tableoid2name ( oid ) RETURNS TEXT AS $$ BEGIN RETURN $1::regclass; END; diff --git a/Open-ILS/src/sql/Pg/1.2.2.1-1.2.2.2-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.2.2.1-1.2.2.2-upgrade-db.sql index cb6adb3e4f..eb47a0011f 100644 --- a/Open-ILS/src/sql/Pg/1.2.2.1-1.2.2.2-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/1.2.2.1-1.2.2.2-upgrade-db.sql @@ -18,7 +18,7 @@ BEGIN; CREATE SCHEMA extend_reporter; -CREATE TABLE extend_reporter.legcay_circ_count ( +CREATE TABLE extend_reporter.legacy_circ_count ( id BIGSERIAL PRIMARY KEY REFERENCES asset.copy (id) circ_count INT NOT NULL DEFAULT 0 ); @@ -26,7 +26,7 @@ CREATE TABLE extend_reporter.legcay_circ_count ( CREATE VIEW extend_reporter.full_circ_count AS SELECT cp.id, COALESCE(sum(c.circ_count), 0::bigint) + COALESCE(count(circ.id), 0::bigint) AS circ_count FROM asset."copy" cp - LEFT JOIN extend_reporter.legcay_circ_count c USING (id) + LEFT JOIN extend_reporter.legacy_circ_count c USING (id) LEFT JOIN "action".circulation circ ON circ.target_copy = c.id GROUP BY cp.id; diff --git a/Open-ILS/src/sql/Pg/build-db.sh b/Open-ILS/src/sql/Pg/build-db.sh index 1f6ede8355..b0d73d7f70 100755 --- a/Open-ILS/src/sql/Pg/build-db.sh +++ b/Open-ILS/src/sql/Pg/build-db.sh @@ -6,6 +6,7 @@ PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 000.english.pg$6.fts-config.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 001.schema.offline.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 002.schema.config.sql +PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 002.functions.aggregate.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 002.functions.config.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 005.schema.actors.sql PGPASSWORD=$5 PGUSER=$4 PGHOST=$1 PGPORT=$2 PGDATABASE=$3 psql -f 006.schema.permissions.sql -- 2.11.0