--- /dev/null
+DROP SCHEMA IF EXISTS sharestuff CASCADE;
+
+BEGIN;
+
+CREATE SCHEMA sharestuff;
+
+CREATE OR REPLACE FUNCTION sharestuff.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 sharestuff.change_db_setting('search_path', ARRAY['sharestuff','public','pg_catalog']);
+
+CREATE OR REPLACE FUNCTION sharestuff.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT) RETURNS anyarray AS $$
+ SELECT ARRAY_ACCUM(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION sharestuff.lowercase( TEXT ) RETURNS TEXT AS $$
+ return lc(shift);
+$$ LANGUAGE PLPERLU STRICT IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION sharestuff.xml_escape(str TEXT) RETURNS text AS $$
+ SELECT REPLACE(REPLACE(REPLACE($1,
+ '&', '&'),
+ '<', '<'),
+ '>', '>');
+$$ LANGUAGE SQL IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION sharestuff.regexp_split_to_array(TEXT, TEXT)
+RETURNS TEXT[] AS $$
+ return encode_array_literal([split $_[1], $_[0]]);
+$$ LANGUAGE PLPERLU STRICT IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION sharestuff.is_json( TEXT ) RETURNS BOOL AS $f$
+ use JSON::XS;
+ my $json = shift();
+ eval { JSON::XS->new->allow_nonref->decode( $json ) };
+ return $@ ? 0 : 1;
+$f$ LANGUAGE PLPERLU;
+
+CREATE TABLE sharestuff.config_flag (
+ name TEXT PRIMARY KEY,
+ label TEXT UNIQUE NOT NULL,
+ value TEXT CHECK (sharestuff.is_json(value)),
+ enabled BOOL NOT NULL DEFAULT FALSE
+);
+INSERT INTO sharestuff.config_flag (name, label, value, enabled)
+ VALUES ('rating.new_rating_bump.days', 'Number of days during which new ratings for a package received increased weight', '30', TRUE)
+;
+
+COMMIT;
--- /dev/null
+/*
+ * Copyright (C) 2004-2008 Georgia Public Library Service
+ * Copyright (C) 2008-2011 Equinox Software, Inc.
+ * Mike Rylander <miker@esilibrary.com>
+ * Copyright (C) 2010 Merrimack Valley Library Consortium
+ * Jason Stephenson <jstephenson@mvlc.org>
+ * Copyright (C) 2010 Laurentian University
+ * Dan Scott <dscott@laurentian.ca>
+ *
+ * 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.
+ *
+ */
+
+
+
+DROP SCHEMA IF EXISTS config CASCADE;
+
+BEGIN;
+CREATE SCHEMA config;
+
+CREATE TABLE config.i18n_locale (
+ code TEXT PRIMARY KEY,
+ marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
+ name TEXT UNIQUE NOT NULL,
+ description TEXT
+);
+
+CREATE TABLE config.i18n_core (
+ id BIGSERIAL PRIMARY KEY,
+ fq_field TEXT NOT NULL,
+ identity_value TEXT NOT NULL,
+ translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ string TEXT NOT NULL
+);
+
+CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
+
+CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
+BEGIN
+
+ EXECUTE $$
+ UPDATE config.i18n_core
+ SET identity_value = $$ || quote_literal(new_ident) || $$
+ WHERE fq_field LIKE '$$ || hint || $$.%'
+ AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
+
+ RETURN;
+
+END;
+$_$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
+BEGIN
+ PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
+ RETURN NEW;
+END;
+$_$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
+BEGIN
+ PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
+ RETURN NEW;
+END;
+$_$ LANGUAGE PLPGSQL;
+
+CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
+
+CREATE TABLE config.usr_activity_type (
+ id SERIAL PRIMARY KEY,
+ ewho TEXT,
+ ewhat TEXT,
+ ehow TEXT,
+ label TEXT NOT NULL, -- i18n
+ egroup config.usr_activity_group NOT NULL,
+ enabled BOOL NOT NULL DEFAULT TRUE,
+ transient BOOL NOT NULL DEFAULT FALSE,
+ CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
+);
+
+CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
+ (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
+
+COMMIT;
--- /dev/null
+/*
+ * Copyright (C) 2005-2008 Equinox Software, Inc. / Georgia Public Library Service
+ * Mike Rylander <mrylander@gmail.com>
+ * Copyright (C) 2010 Laurentian University
+ * Dan Scott <dscott@laurentian.ca>
+ * Copyright (C) 2013Equinox Software, Inc.
+ * Mike Rylander <miker@esilibrary.com>
+ *
+ * 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.
+ */
+
+DROP SCHEMA IF EXISTS actor CASCADE;
+
+BEGIN;
+CREATE SCHEMA actor;
+
+CREATE TABLE actor.usr (
+ id SERIAL PRIMARY KEY,
+ profile INT NOT NULL, -- user group
+ email TEXT NOT NULL UNIQUE,
+ passwd TEXT NOT NULL,
+ photo_url TEXT,
+ prefix TEXT,
+ first_given_name TEXT NOT NULL,
+ second_given_name TEXT,
+ family_name TEXT NOT NULL,
+ suffix TEXT,
+ home_ou INT NOT NULL,
+ active BOOL NOT NULL DEFAULT TRUE,
+ super_user BOOL NOT NULL DEFAULT FALSE,
+ barred BOOL NOT NULL DEFAULT FALSE,
+ deleted BOOL NOT NULL DEFAULT FALSE
+);
+
+CREATE INDEX actor_usr_home_ou_idx ON actor.usr (home_ou);
+
+CREATE FUNCTION actor.crypt_pw_insert () RETURNS TRIGGER AS $$
+ BEGIN
+ NEW.passwd = MD5( NEW.id || NEW.passwd );
+ RETURN NEW;
+ END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE FUNCTION actor.crypt_pw_update () RETURNS TRIGGER AS $$
+ BEGIN
+ IF NEW.passwd <> OLD.passwd THEN
+ NEW.passwd = MD5( NEW.id || NEW.passwd );
+ END IF;
+ RETURN NEW;
+ END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE TRIGGER actor_crypt_pw_update_trigger
+ BEFORE UPDATE ON actor.usr FOR EACH ROW
+ EXECUTE PROCEDURE actor.crypt_pw_update ();
+
+CREATE TRIGGER actor_crypt_pw_insert_trigger
+ BEFORE INSERT ON actor.usr FOR EACH ROW
+ EXECUTE PROCEDURE actor.crypt_pw_insert ();
+
+CREATE RULE protect_user_delete AS ON DELETE TO actor.usr DO INSTEAD UPDATE actor.usr SET deleted = TRUE WHERE OLD.id = actor.usr.id;
+
+CREATE TABLE actor.usr_note (
+ id SERIAL PRIMARY KEY,
+ usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ creator INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ pub BOOL NOT NULL DEFAULT FALSE,
+ title TEXT NOT NULL,
+ value TEXT NOT NULL,
+ create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW()
+);
+CREATE INDEX actor_usr_note_usr_idx ON actor.usr_note (usr);
+CREATE INDEX actor_usr_note_creator_idx ON actor.usr_note ( creator );
+
+CREATE TABLE actor.settings_group (
+ name TEXT PRIMARY KEY,
+ label TEXT UNIQUE NOT NULL -- I18N
+);
+
+CREATE TABLE actor.org_unit_setting_type (
+ name TEXT PRIMARY KEY,
+ label TEXT UNIQUE NOT NULL,
+ grp TEXT REFERENCES actor.settings_group (name),
+ description TEXT,
+ datatype TEXT NOT NULL DEFAULT 'string',
+ fm_class TEXT,
+ view_perm INT,
+ update_perm INT,
+ --
+ -- define valid datatypes
+ --
+ CONSTRAINT coust_valid_datatype CHECK ( datatype IN
+ ( 'bool', 'integer', 'float', 'currency', 'interval',
+ 'date', 'string', 'object', 'array', 'link' ) ),
+ --
+ -- fm_class is meaningful only for 'link' datatype
+ --
+ CONSTRAINT coust_no_empty_link CHECK
+ ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
+ ( datatype <> 'link' AND fm_class IS NULL ) )
+);
+
+CREATE TABLE actor.usr_setting_type (
+ name TEXT PRIMARY KEY,
+ label TEXT UNIQUE NOT NULL,
+ description TEXT,
+ grp TEXT REFERENCES actor.settings_group (name),
+ datatype TEXT NOT NULL DEFAULT 'string',
+ fm_class TEXT,
+
+ --
+ -- define valid datatypes
+ --
+ CONSTRAINT coust_valid_datatype CHECK ( datatype IN
+ ( 'bool', 'integer', 'float', 'currency', 'interval',
+ 'date', 'string', 'object', 'array', 'link' ) ),
+
+ --
+ -- fm_class is meaningful only for 'link' datatype
+ --
+ CONSTRAINT coust_no_empty_link CHECK
+ ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
+ ( datatype <> 'link' AND fm_class IS NULL ) )
+
+);
+
+CREATE TABLE actor.usr_setting (
+ id SERIAL PRIMARY KEY,
+ usr INT NOT NULL REFERENCES actor.usr ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ name TEXT NOT NULL REFERENCES actor.usr_setting_type (name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ value TEXT NOT NULL,
+ CONSTRAINT usr_once_per_key UNIQUE (usr,name)
+);
+CREATE INDEX actor_usr_setting_usr_idx ON actor.usr_setting (usr);
+
+CREATE TABLE actor.org_unit (
+ id SERIAL PRIMARY KEY,
+ parent_ou INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
+ owner INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
+ shortname TEXT NOT NULL UNIQUE,
+ name TEXT NOT NULL UNIQUE,
+ email TEXT,
+ phone TEXT
+);
+CREATE INDEX actor_org_unit_parent_ou_idx ON actor.org_unit (parent_ou);
+
+CREATE VIEW actor.org_unit_depth (id, depth) AS
+WITH RECURSIVE t(id,depth) AS (
+ SELECT id, 0 FROM actor.org_unit WHERE parent_ou IS NULL
+ UNION ALL
+ SELECT a.id, depth + 1 FROM actor.org_unit a JOIN t ON (t.id = a.parent_ou)
+) SELECT * FROM t;
+
+CREATE OR REPLACE FUNCTION actor.org_unit_parent_protect () RETURNS TRIGGER AS $$
+ DECLARE
+ current_aou actor.org_unit%ROWTYPE;
+ seen_ous INT[];
+ depth_count INT;
+ BEGIN
+ current_aou := NEW;
+ depth_count := 0;
+ seen_ous := ARRAY[NEW.id];
+
+ IF (TG_OP = 'UPDATE') THEN
+ IF (NEW.parent_ou IS NOT DISTINCT FROM OLD.parent_ou) THEN
+ RETURN NEW; -- Doing an UPDATE with no change, just return it
+ END IF;
+ END IF;
+
+ LOOP
+ IF current_aou.parent_ou IS NULL THEN -- Top of the org tree?
+ RETURN NEW; -- No loop. Carry on.
+ END IF;
+ IF current_aou.parent_ou = ANY(seen_ous) THEN -- Parent is one we have seen?
+ RAISE 'OU LOOP: Saw % twice', current_aou.parent_ou; -- LOOP! ABORT!
+ END IF;
+ -- Get the next one!
+ SELECT INTO current_aou * FROM actor.org_unit WHERE id = current_aou.parent_ou;
+ seen_ous := seen_ous || current_aou.id;
+ depth_count := depth_count + 1;
+ IF depth_count = 100 THEN
+ RAISE 'OU CHECK TOO DEEP';
+ END IF;
+ END LOOP;
+
+ RETURN NEW;
+ END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE TRIGGER actor_org_unit_parent_protect_trigger
+ BEFORE INSERT OR UPDATE ON actor.org_unit FOR EACH ROW
+ EXECUTE PROCEDURE actor.org_unit_parent_protect ();
+
+CREATE TABLE actor.org_unit_setting (
+ id BIGSERIAL PRIMARY KEY,
+ org_unit INT NOT NULL REFERENCES actor.org_unit ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ name TEXT NOT NULL REFERENCES actor.org_unit_setting_type DEFERRABLE INITIALLY DEFERRED,
+ value TEXT NOT NULL,
+ CONSTRAINT ou_once_per_key UNIQUE (org_unit,name),
+ CONSTRAINT aous_must_be_json CHECK ( sharestuff.is_json(value) )
+);
+CREATE INDEX actor_org_unit_setting_usr_idx ON actor.org_unit_setting (org_unit);
+
+CREATE TABLE actor.usr_activity (
+ id BIGSERIAL PRIMARY KEY,
+ usr INT REFERENCES actor.usr (id) ON DELETE SET NULL,
+ etype INT NOT NULL REFERENCES config.usr_activity_type (id),
+ event_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+-- remove transient activity entries on insert of new entries
+CREATE OR REPLACE FUNCTION actor.usr_activity_transient_trg () RETURNS TRIGGER AS $$
+BEGIN
+ DELETE FROM actor.usr_activity act USING config.usr_activity_type atype
+ WHERE atype.transient AND
+ NEW.etype = atype.id AND
+ act.etype = atype.id AND
+ act.usr = NEW.usr;
+ RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE TRIGGER remove_transient_usr_activity
+ BEFORE INSERT ON actor.usr_activity
+ FOR EACH ROW EXECUTE PROCEDURE actor.usr_activity_transient_trg();
+
+-- given a set of activity criteria, find the most approprate activity type
+CREATE OR REPLACE FUNCTION actor.usr_activity_get_type (
+ ewho TEXT,
+ ewhat TEXT,
+ ehow TEXT
+ ) RETURNS SETOF config.usr_activity_type AS $$
+SELECT * FROM config.usr_activity_type
+ WHERE
+ enabled AND
+ (ewho IS NULL OR ewho = $1) AND
+ (ewhat IS NULL OR ewhat = $2) AND
+ (ehow IS NULL OR ehow = $3)
+ ORDER BY
+ -- BOOL comparisons sort false to true
+ COALESCE(ewho, '') != COALESCE($1, ''),
+ COALESCE(ewhat,'') != COALESCE($2, ''),
+ COALESCE(ehow, '') != COALESCE($3, '')
+ LIMIT 1;
+$$ LANGUAGE SQL;
+
+-- given a set of activity criteria, finds the best
+-- activity type and inserts the activity entry
+CREATE OR REPLACE FUNCTION actor.insert_usr_activity (
+ usr INT,
+ ewho TEXT,
+ ewhat TEXT,
+ ehow TEXT
+ ) RETURNS SETOF actor.usr_activity AS $$
+DECLARE
+ new_row actor.usr_activity%ROWTYPE;
+BEGIN
+ SELECT id INTO new_row.etype FROM actor.usr_activity_get_type(ewho, ewhat, ehow);
+ IF FOUND THEN
+ new_row.usr := usr;
+ INSERT INTO actor.usr_activity (usr, etype)
+ VALUES (usr, new_row.etype)
+ RETURNING * INTO new_row;
+ RETURN NEXT new_row;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+COMMIT;
--- /dev/null
+/*
+ * Copyright (C) 2013 Equinox Software, Inc.
+ * Mike Rylander <miker@esilibrary.com>
+ *
+ * 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 SCHEMA IF EXISTS depository;
+CREATE SCHEMA depository;
+
+CREATE TABLE depository.source_system ( name TEXT PRIMARY KEY );
+INSERT INTO depository.source_system (name)
+VALUES ('Evergreen-2.1')
+ ,('Evergreen-2.2')
+ ,('Evergreen-2.3')
+ ,('Evergreen-2.4')
+;
+
+CREATE TABLE depository.package (
+ id SERIAL PRIMARY KEY,
+ author INT NOT NULL REFERENCES actor.usr (id),
+ source TEXT NOT NULL REFERENCES depository.source_system (name),
+ pub BOOL NOT NULL DEFAULT TRUE,
+ title TEXT NOT NULL,
+ pkg_version TEXT NOT NULL,
+ created DATE NOT NULL DEFAULT NOW(),
+ description TEXT,
+ image BYTEA
+);
+
+CREATE TABLE depository.package_compat_map (
+ id SERIAL PRIMARY KEY,
+ package INT NOT NULL REFERENCES depository.package (id),
+ compat_system TEXT NOT NULL REFERENCES depository.source_system (name)
+);
+
+CREATE TABLE depository.rating (
+ id SERIAL PRIMARY KEY,
+ usr INT NOT NULL REFERENCES actor.usr (id),
+ package INT NOT NULL REFERENCES depository.package (id),
+ rating INT NOT NULL,
+ created TIMESTAMP NOT NULL DEFAULT NOW(),
+ comments TEXT
+);
+
+CREATE VIEW depository.derived_rating AS
+ WITH setting(max_age) AS (
+ SELECT COALESCE(NULLIF(value,'0'),'1')::INT::TEXT AS max_age
+ FROM sharestuff.config_flag
+ WHERE name = 'rating.new_rating_bump.days'
+ AND enabled
+ LIMIT 1
+ ), duplicate_list(id,dup_count) AS (
+ SELECT id,
+ setting.max_age::INT - DATE_PART('day', NOW() - created )::INT AS dup_count
+ FROM depository.rating, setting
+ WHERE created > NOW() - (setting.max_age || ' days')::interval
+ UNION
+ SELECT id,
+ 1 AS dup_count
+ FROM depository.rating, setting
+ WHERE created <= NOW() - (setting.max_age || ' days')::interval
+ ), sized_arrays(package,rating_array) AS (
+ SELECT package,
+ ARRAY_FILL( rating, ARRAY_APPEND(NULL::INT[], duplicate_list.dup_count) ) AS rating_array
+ FROM depository.rating
+ JOIN duplicate_list USING (id)
+ ), flattened_duplicated_ratings(package,rating) AS (
+ SELECT package, UNNEST( rating_array ) AS rating
+ FROM sized_arrays
+ )
+ SELECT p.id AS package, AVG( r.rating ) AS rating
+ FROM depository.package AS p
+ LEFT JOIN flattened_duplicated_ratings AS r ON p.id = r.package
+ GROUP BY 1;
+
+
+CREATE TABLE depository.base_object_type (
+ id SERIAL PRIMARY KEY,
+ label TEXT NOT NULL,
+ hint TEXT NOT NULL
+);
+
+CREATE TABLE depository.versioned_object_type (
+ id SERIAL PRIMARY KEY,
+ base_type INT NOT NULL REFERENCES depository.base_object_type (id),
+ system TEXT NOT NULL REFERENCES depository.source_system (name)
+);
+
+CREATE TABLE depository.versioned_object_type_property_ignore (
+ id SERIAL PRIMARY KEY,
+ otype INT NOT NULL REFERENCES depository.versioned_object_type (id),
+ property TEXT NOT NULL
+);
+
+CREATE TABLE depository.item (
+ id SERIAL PRIMARY KEY,
+ package INT NOT NULL REFERENCES depository.package (id),
+ obj_type INT NOT NULL REFERENCES depository.versioned_object_type (id),
+ data TEXT CHECK (sharestuff.is_json(data))
+);
+
+CREATE TABLE depository.item_field (
+ id SERIAL PRIMARY KEY,
+ item INT REFERENCES depository.item (id),
+ container INT REFERENCES depository.item_field (id),
+ property TEXT,
+ value TEXT CHECK (sharestuff.is_json(value)),
+ multival BOOL NOT NULL DEFAULT FALSE,
+ CONSTRAINT item_or_container CHECK (COALESCE(item,container) IS NOT NULL)
+);
+
+COMMIT;
+
--- /dev/null
+/*
+ * Copyright (C) 2004-2008 Georgia Public Library Service
+ * Copyright (C) 2008 Equinox Software, Inc.
+ * Mike Rylander <miker@esilibrary.com>
+ *
+ * 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.
+ *
+ */
+
+
+DROP SCHEMA IF EXISTS permission CASCADE;
+
+BEGIN;
+CREATE SCHEMA permission;
+
+CREATE TABLE permission.perm_list (
+ id SERIAL PRIMARY KEY,
+ code TEXT NOT NULL UNIQUE,
+ description TEXT
+);
+CREATE INDEX perm_list_code_idx ON permission.perm_list (code);
+CREATE TRIGGER maintain_perm_i18n_tgr
+ AFTER UPDATE ON permission.perm_list
+ FOR EACH ROW EXECUTE PROCEDURE oils_i18n_id_tracking('ppl');
+
+CREATE TABLE permission.grp_tree (
+ id SERIAL PRIMARY KEY,
+ name TEXT NOT NULL UNIQUE,
+ parent INT REFERENCES permission.grp_tree (id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
+ usergroup BOOL NOT NULL DEFAULT TRUE,
+ perm_interval INTERVAL DEFAULT '3 years'::interval NOT NULL,
+ description TEXT,
+ application_perm TEXT,
+ hold_priority INT NOT NULL DEFAULT 0
+);
+CREATE INDEX grp_tree_parent_idx ON permission.grp_tree (parent);
+
+CREATE TABLE permission.grp_perm_map (
+ id SERIAL PRIMARY KEY,
+ grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
+ depth INT NOT NULL,
+ grantable BOOL NOT NULL DEFAULT FALSE,
+ CONSTRAINT perm_grp_once UNIQUE (grp,perm)
+);
+
+CREATE TABLE permission.usr_perm_map (
+ id SERIAL PRIMARY KEY,
+ usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
+ depth INT NOT NULL,
+ grantable BOOL NOT NULL DEFAULT FALSE,
+ CONSTRAINT perm_usr_once UNIQUE (usr,perm)
+);
+
+CREATE TABLE permission.usr_object_perm_map (
+ id SERIAL PRIMARY KEY,
+ usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
+ object_type TEXT NOT NULL,
+ object_id TEXT NOT NULL,
+ grantable BOOL NOT NULL DEFAULT FALSE,
+ CONSTRAINT perm_usr_obj_once UNIQUE (usr,perm,object_type,object_id)
+);
+
+CREATE INDEX uopm_usr_idx ON permission.usr_object_perm_map (usr);
+
+CREATE TABLE permission.usr_grp_map (
+ id SERIAL PRIMARY KEY,
+ usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ CONSTRAINT usr_grp_once UNIQUE (usr,grp)
+);
+
+CREATE OR REPLACE FUNCTION permission.grp_ancestors( INT ) RETURNS SETOF permission.grp_tree AS $$
+ WITH RECURSIVE grp_ancestors_distance(id, distance) AS (
+ SELECT $1, 0
+ UNION
+ SELECT ou.parent, ouad.distance+1
+ FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad ON (ou.id = ouad.id)
+ WHERE ou.parent IS NOT NULL
+ )
+ SELECT ou.* FROM permission.grp_tree ou JOIN grp_ancestors_distance ouad USING (id) ORDER BY ouad.distance DESC;
+$$ LANGUAGE SQL ROWS 1;
+
+CREATE OR REPLACE FUNCTION permission.grp_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
+ WITH RECURSIVE grp_ancestors_distance(id, distance) AS (
+ SELECT $1, 0
+ UNION
+ SELECT pgt.parent, gad.distance+1
+ FROM permission.grp_tree pgt JOIN grp_ancestors_distance gad ON (pgt.id = gad.id)
+ WHERE pgt.parent IS NOT NULL
+ )
+ SELECT * FROM grp_ancestors_distance;
+$$ LANGUAGE SQL STABLE ROWS 1;
+
+CREATE OR REPLACE FUNCTION permission.grp_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$
+ WITH RECURSIVE grp_descendants_distance(id, distance) AS (
+ SELECT $1, 0
+ UNION
+ SELECT pgt.id, gdd.distance+1
+ FROM permission.grp_tree pgt JOIN grp_descendants_distance gdd ON (pgt.parent = gdd.id)
+ )
+ SELECT * FROM grp_descendants_distance;
+$$ LANGUAGE SQL STABLE ROWS 1;
+
+CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$
+ SELECT DISTINCT ON (usr,perm) *
+ FROM (
+ (SELECT * FROM permission.usr_perm_map WHERE usr = $1)
+ UNION ALL
+ (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
+ FROM permission.grp_perm_map p
+ WHERE p.grp IN (
+ SELECT (permission.grp_ancestors(
+ (SELECT profile FROM actor.usr WHERE id = $1)
+ )).id
+ )
+ )
+ UNION ALL
+ (SELECT -p.id, $1 AS usr, p.perm, p.depth, p.grantable
+ FROM permission.grp_perm_map p
+ WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1))
+ ) AS x
+ ORDER BY 2, 3, 1 DESC, 5 DESC ;
+$$ LANGUAGE SQL STABLE ROWS 10;
+
+CREATE TABLE permission.usr_work_ou_map (
+ id SERIAL PRIMARY KEY,
+ usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ work_ou INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
+ CONSTRAINT usr_work_ou_once UNIQUE (usr,work_ou)
+);
+
+CREATE OR REPLACE FUNCTION permission.usr_can_grant_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
+DECLARE
+ r_usr actor.usr%ROWTYPE;
+ r_perm permission.usr_perm_map%ROWTYPE;
+BEGIN
+
+ SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
+
+ IF r_usr.active = FALSE THEN
+ RETURN FALSE;
+ END IF;
+
+ IF r_usr.super_user = TRUE THEN
+ RETURN TRUE;
+ END IF;
+
+ FOR r_perm IN SELECT *
+ FROM permission.usr_perms(iuser) p
+ JOIN permission.perm_list l
+ ON (l.id = p.perm)
+ WHERE (l.code = tperm AND p.grantable IS TRUE)
+ LOOP
+
+ PERFORM *
+ FROM actor.org_unit_descendants(target_ou,r_perm.depth)
+ WHERE id = r_usr.home_ou;
+
+ IF FOUND THEN
+ RETURN TRUE;
+ ELSE
+ RETURN FALSE;
+ END IF;
+ END LOOP;
+
+ RETURN FALSE;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION permission.usr_has_home_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
+DECLARE
+ r_usr actor.usr%ROWTYPE;
+ r_perm permission.usr_perm_map%ROWTYPE;
+BEGIN
+
+ SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
+
+ IF r_usr.active = FALSE THEN
+ RETURN FALSE;
+ END IF;
+
+ IF r_usr.super_user = TRUE THEN
+ RETURN TRUE;
+ END IF;
+
+ FOR r_perm IN SELECT *
+ FROM permission.usr_perms(iuser) p
+ JOIN permission.perm_list l
+ ON (l.id = p.perm)
+ WHERE l.code = tperm
+ OR p.perm = -1 LOOP
+
+ PERFORM *
+ FROM actor.org_unit_descendants(target_ou,r_perm.depth)
+ WHERE id = r_usr.home_ou;
+
+ IF FOUND THEN
+ RETURN TRUE;
+ ELSE
+ RETURN FALSE;
+ END IF;
+ END LOOP;
+
+ RETURN FALSE;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION permission.usr_has_work_perm ( iuser INT, tperm TEXT, target_ou INT ) RETURNS BOOL AS $$
+DECLARE
+ r_woum permission.usr_work_ou_map%ROWTYPE;
+ r_usr actor.usr%ROWTYPE;
+ r_perm permission.usr_perm_map%ROWTYPE;
+BEGIN
+
+ SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
+
+ IF r_usr.active = FALSE THEN
+ RETURN FALSE;
+ END IF;
+
+ IF r_usr.super_user = TRUE THEN
+ RETURN TRUE;
+ END IF;
+
+ FOR r_perm IN SELECT *
+ FROM permission.usr_perms(iuser) p
+ JOIN permission.perm_list l
+ ON (l.id = p.perm)
+ WHERE l.code = tperm
+ OR p.perm = -1
+ LOOP
+
+ FOR r_woum IN SELECT *
+ FROM permission.usr_work_ou_map
+ WHERE usr = iuser
+ LOOP
+
+ PERFORM *
+ FROM actor.org_unit_descendants(target_ou,r_perm.depth)
+ WHERE id = r_woum.work_ou;
+
+ IF FOUND THEN
+ RETURN TRUE;
+ END IF;
+
+ END LOOP;
+
+ END LOOP;
+
+ RETURN FALSE;
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( iuser INT, tperm TEXT, obj_type TEXT, obj_id TEXT, target_ou INT ) RETURNS BOOL AS $$
+DECLARE
+ r_usr actor.usr%ROWTYPE;
+ res BOOL;
+BEGIN
+
+ SELECT * INTO r_usr FROM actor.usr WHERE id = iuser;
+
+ IF r_usr.active = FALSE THEN
+ RETURN FALSE;
+ END IF;
+
+ IF r_usr.super_user = TRUE THEN
+ RETURN TRUE;
+ END IF;
+
+ SELECT TRUE INTO res FROM permission.usr_object_perm_map WHERE usr = r_usr.id AND object_type = obj_type AND object_id = obj_id;
+
+ IF FOUND THEN
+ RETURN TRUE;
+ END IF;
+
+ IF target_ou > -1 THEN
+ RETURN permission.usr_has_perm( iuser, tperm, target_ou);
+ END IF;
+
+ RETURN FALSE;
+
+END;
+$$ LANGUAGE PLPGSQL;
+
+CREATE OR REPLACE FUNCTION permission.usr_has_object_perm ( INT, TEXT, TEXT, TEXT ) RETURNS BOOL AS $$
+ SELECT permission.usr_has_object_perm( $1, $2, $3, $4, -1 );
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION permission.usr_has_perm ( INT, TEXT, INT ) RETURNS BOOL AS $$
+ SELECT CASE
+ WHEN permission.usr_has_home_perm( $1, $2, $3 ) THEN TRUE
+ WHEN permission.usr_has_work_perm( $1, $2, $3 ) THEN TRUE
+ ELSE FALSE
+ END;
+$$ LANGUAGE SQL;
+
+
+CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_nd(
+ user_id IN INTEGER,
+ perm_code IN TEXT
+)
+RETURNS SETOF INTEGER AS $$
+--
+-- Return a set of all the org units for which a given user has a given
+-- permission, granted directly (not through inheritance from a parent
+-- org unit).
+--
+-- The permissions apply to a minimum depth of the org unit hierarchy,
+-- for the org unit(s) to which the user is assigned. (They also apply
+-- to the subordinates of those org units, but we don't report the
+-- subordinates here.)
+--
+-- For purposes of this function, the permission.usr_work_ou_map table
+-- defines which users belong to which org units. I.e. we ignore the
+-- home_ou column of actor.usr.
+--
+-- The result set may contain duplicates, which should be eliminated
+-- by a DISTINCT clause.
+--
+DECLARE
+ b_super BOOLEAN;
+ n_perm INTEGER;
+ n_min_depth INTEGER;
+ n_work_ou INTEGER;
+ n_curr_ou INTEGER;
+ n_depth INTEGER;
+ n_curr_depth INTEGER;
+BEGIN
+ --
+ -- Check for superuser
+ --
+ SELECT INTO b_super
+ super_user
+ FROM
+ actor.usr
+ WHERE
+ id = user_id;
+ --
+ IF NOT FOUND THEN
+ return; -- No user? No permissions.
+ ELSIF b_super THEN
+ --
+ -- Super user has all permissions everywhere
+ --
+ FOR n_work_ou IN
+ SELECT
+ id
+ FROM
+ actor.org_unit
+ WHERE
+ parent_ou IS NULL
+ LOOP
+ RETURN NEXT n_work_ou;
+ END LOOP;
+ RETURN;
+ END IF;
+ --
+ -- Translate the permission name
+ -- to a numeric permission id
+ --
+ SELECT INTO n_perm
+ id
+ FROM
+ permission.perm_list
+ WHERE
+ code = perm_code;
+ --
+ IF NOT FOUND THEN
+ RETURN; -- No such permission
+ END IF;
+ --
+ -- Find the highest-level org unit (i.e. the minimum depth)
+ -- to which the permission is applied for this user
+ --
+ -- This query is modified from the one in permission.usr_perms().
+ --
+ SELECT INTO n_min_depth
+ min( depth )
+ FROM (
+ SELECT depth
+ FROM permission.usr_perm_map upm
+ WHERE upm.usr = user_id
+ AND (upm.perm = n_perm OR upm.perm = -1)
+ UNION
+ SELECT gpm.depth
+ FROM permission.grp_perm_map gpm
+ WHERE (gpm.perm = n_perm OR gpm.perm = -1)
+ AND gpm.grp IN (
+ SELECT (permission.grp_ancestors(
+ (SELECT profile FROM actor.usr WHERE id = user_id)
+ )).id
+ )
+ UNION
+ SELECT p.depth
+ FROM permission.grp_perm_map p
+ WHERE (p.perm = n_perm OR p.perm = -1)
+ AND p.grp IN (
+ SELECT (permission.grp_ancestors(m.grp)).id
+ FROM permission.usr_grp_map m
+ WHERE m.usr = user_id
+ )
+ ) AS x;
+ --
+ IF NOT FOUND THEN
+ RETURN; -- No such permission for this user
+ END IF;
+ --
+ -- Identify the org units to which the user is assigned. Note that
+ -- we pay no attention to the home_ou column in actor.usr.
+ --
+ FOR n_work_ou IN
+ SELECT
+ work_ou
+ FROM
+ permission.usr_work_ou_map
+ WHERE
+ usr = user_id
+ LOOP -- For each org unit to which the user is assigned
+ SELECT INTO n_depth
+ ou.depth
+ FROM
+ actor.org_unit_depth ou
+ WHERE
+ ou.id = n_work_ou;
+ --
+ IF NOT FOUND THEN
+ CONTINUE; -- Maybe raise exception?
+ END IF;
+ --
+ -- Compare the depth of the work org unit to the
+ -- minimum depth, and branch accordingly
+ --
+ IF n_depth = n_min_depth THEN
+ --
+ -- The org unit is at the right depth, so return it.
+ --
+ RETURN NEXT n_work_ou;
+ ELSIF n_depth > n_min_depth THEN
+ --
+ -- Traverse the org unit tree toward the root,
+ -- until you reach the minimum depth determined above
+ --
+ n_curr_depth := n_depth;
+ n_curr_ou := n_work_ou;
+ WHILE n_curr_depth > n_min_depth LOOP
+ SELECT INTO n_curr_ou
+ parent_ou
+ FROM
+ actor.org_unit
+ WHERE
+ id = n_curr_ou;
+ --
+ IF FOUND THEN
+ n_curr_depth := n_curr_depth - 1;
+ ELSE
+ --
+ -- This can happen only if the hierarchy defined in
+ -- actor.org_unit is corrupted, or out of sync with
+ -- the depths defined in actor.org_unit_type.
+ -- Maybe we should raise an exception here, instead
+ -- of silently ignoring the problem.
+ --
+ n_curr_ou = NULL;
+ EXIT;
+ END IF;
+ END LOOP;
+ --
+ IF n_curr_ou IS NOT NULL THEN
+ RETURN NEXT n_curr_ou;
+ END IF;
+ ELSE
+ --
+ -- The permission applies only at a depth greater than the work org unit.
+ -- Use connectby() to find all dependent org units at the specified depth.
+ --
+ FOR n_work_ou IN
+ SELECT a.id FROM actor.org_unit_descendants( n_curr_ou, n_min_depth )
+ LOOP
+ RETURN NEXT n_curr_ou;
+ END LOOP;
+ END IF;
+ --
+ END LOOP;
+ --
+ RETURN;
+ --
+END;
+$$ LANGUAGE 'plpgsql' ROWS 1;
+
+
+CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd(
+ user_id IN INTEGER,
+ perm_code IN TEXT
+)
+RETURNS SETOF INTEGER AS $$
+--
+-- Return a set of all the org units for which a given user has a given
+-- permission, granted either directly or through inheritance from a parent
+-- org unit.
+--
+-- The permissions apply to a minimum depth of the org unit hierarchy, and
+-- to the subordinates of those org units, for the org unit(s) to which the
+-- user is assigned.
+--
+-- For purposes of this function, the permission.usr_work_ou_map table
+-- assigns users to org units. I.e. we ignore the home_ou column of actor.usr.
+--
+-- The result set may contain duplicates, which should be eliminated
+-- by a DISTINCT clause.
+--
+DECLARE
+ n_head_ou INTEGER;
+ n_child_ou INTEGER;
+BEGIN
+ FOR n_head_ou IN
+ SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( user_id, perm_code )
+ LOOP
+ --
+ -- The permission applies only at a depth greater than the work org unit.
+ -- Use connectby() to find all dependent org units at the specified depth.
+ --
+ FOR n_child_ou IN
+ SELECT a.id FROM actor.org_unit_descendants( n_head_ou )
+ LOOP
+ RETURN NEXT n_child_ou;
+ END LOOP;
+ END LOOP;
+ --
+ RETURN;
+ --
+END;
+$$ LANGUAGE 'plpgsql' ROWS 1;
+
+
+CREATE OR REPLACE FUNCTION permission.usr_has_perm_at(
+ user_id IN INTEGER,
+ perm_code IN TEXT
+)
+RETURNS SETOF INTEGER AS $$
+SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 );
+$$ LANGUAGE 'sql' ROWS 1;
+
+
+CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all(
+ user_id IN INTEGER,
+ perm_code IN TEXT
+)
+RETURNS SETOF INTEGER AS $$
+SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 );
+$$ LANGUAGE 'sql' ROWS 1;
+
+
+COMMIT;
+
projects / transitory.git / commitdiff