From 007195f83beb33519d90133818a7d114ae82428b Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 17 May 2011 13:44:35 -0400 Subject: [PATCH] Convert/move SQL script for Lazy Circ Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- .../0536.schema.lazy_circ-barcode_lookup.sql | 117 ++++++++++++++++++++ Open-ILS/src/sql/Pg/upgrade/XXXX.lazy_circ.sql | 113 ------------------- 3 files changed, 118 insertions(+), 114 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0536.schema.lazy_circ-barcode_lookup.sql delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.lazy_circ.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 2f14205..741f4f1 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -86,7 +86,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0535', :eg_version); -- dbs +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0536', :eg_version); -- miker for tsbere CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/0536.schema.lazy_circ-barcode_lookup.sql b/Open-ILS/src/sql/Pg/upgrade/0536.schema.lazy_circ-barcode_lookup.sql new file mode 100644 index 0000000..5d86035 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0536.schema.lazy_circ-barcode_lookup.sql @@ -0,0 +1,117 @@ +-- Evergreen DB patch 0536.schema.lazy_circ-barcode_lookup.sql +-- +-- FIXME: insert description of change, if needed +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.update_deps_block_check('0536', :eg_version); + +INSERT INTO config.org_unit_setting_type ( name, label, description, datatype) VALUES ( 'circ.staff_client.actor_on_checkout', 'Load patron from Checkout', 'When scanning barcodes into Checkout auto-detect if a new patron barcode is scanned and auto-load the new patron.', 'bool'); + +CREATE TABLE config.barcode_completion ( + id SERIAL PRIMARY KEY, + active BOOL NOT NULL DEFAULT true, + org_unit INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + prefix TEXT, + suffix TEXT, + length INT NOT NULL DEFAULT 0, + padding TEXT, + padding_end BOOL NOT NULL DEFAULT false, + asset BOOL NOT NULL DEFAULT true, + actor BOOL NOT NULL DEFAULT true +); + +CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT); + +CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$ +DECLARE + cur_barcode TEXT; + barcode_len INT; + completion_len INT; + asset_barcodes TEXT[]; + actor_barcodes TEXT[]; + do_asset BOOL = false; + do_serial BOOL = false; + do_booking BOOL = false; + do_actor BOOL = false; + completion_set config.barcode_completion%ROWTYPE; +BEGIN + + IF position('asset' in type) > 0 THEN + do_asset = true; + END IF; + IF position('serial' in type) > 0 THEN + do_serial = true; + END IF; + IF position('booking' in type) > 0 THEN + do_booking = true; + END IF; + IF do_asset OR do_serial OR do_booking THEN + asset_barcodes = asset_barcodes || in_barcode; + END IF; + IF position('actor' in type) > 0 THEN + do_actor = true; + actor_barcodes = actor_barcodes || in_barcode; + END IF; + + barcode_len := length(in_barcode); + + FOR completion_set IN + SELECT * FROM config.barcode_completion + WHERE active + AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou) + LOOP + IF completion_set.prefix IS NULL THEN + completion_set.prefix := ''; + END IF; + IF completion_set.suffix IS NULL THEN + completion_set.suffix := ''; + END IF; + IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN + cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix; + ELSE + completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix); + IF completion_len >= barcode_len THEN + IF completion_set.padding_end THEN + cur_barcode = rpad(in_barcode, completion_len, completion_set.padding); + ELSE + cur_barcode = lpad(in_barcode, completion_len, completion_set.padding); + END IF; + cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix; + END IF; + END IF; + IF completion_set.actor THEN + actor_barcodes = actor_barcodes || cur_barcode; + END IF; + IF completion_set.asset THEN + asset_barcodes = asset_barcodes || cur_barcode; + END IF; + END LOOP; + + IF do_asset AND do_serial THEN + RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false; + RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false; + ELSIF do_asset THEN + RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false; + ELSIF do_serial THEN + RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false; + END IF; + IF do_booking THEN + RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes); + END IF; + IF do_actor THEN + RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE c.barcode = ANY(actor_barcodes) AND c.active AND NOT u.deleted ORDER BY usr; + END IF; + RETURN; +END; +$$ LANGUAGE plpgsql; + +COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$ +Given user input, find an appropriate barcode in the proper class. + +Will add prefix/suffix information to do so, and return all results. +$$; + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.lazy_circ.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.lazy_circ.sql deleted file mode 100644 index ab4692a..0000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.lazy_circ.sql +++ /dev/null @@ -1,113 +0,0 @@ -BEGIN; - -INSERT INTO config.upgrade_log (version) VALUES ('XXXX'); - -INSERT INTO config.org_unit_setting_type ( name, label, description, datatype) VALUES ( 'circ.staff_client.actor_on_checkout', 'Load patron from Checkout', 'When scanning barcodes into Checkout auto-detect if a new patron barcode is scanned and auto-load the new patron.', 'bool'); - -CREATE TABLE config.barcode_completion ( - id SERIAL PRIMARY KEY, - active BOOL NOT NULL DEFAULT true, - org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED, - prefix TEXT, - suffix TEXT, - length INT NOT NULL DEFAULT 0, - padding TEXT, - padding_end BOOL NOT NULL DEFAULT false, - asset BOOL NOT NULL DEFAULT true, - actor BOOL NOT NULL DEFAULT true -); - -CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT); - -CREATE OR REPLACE FUNCTION evergreen.get_barcodes(select_ou INT, type TEXT, in_barcode TEXT) RETURNS SETOF evergreen.barcode_set AS $$ -DECLARE - cur_barcode TEXT; - barcode_len INT; - completion_len INT; - asset_barcodes TEXT[]; - actor_barcodes TEXT[]; - do_asset BOOL = false; - do_serial BOOL = false; - do_booking BOOL = false; - do_actor BOOL = false; - completion_set config.barcode_completion%ROWTYPE; -BEGIN - - IF position('asset' in type) > 0 THEN - do_asset = true; - END IF; - IF position('serial' in type) > 0 THEN - do_serial = true; - END IF; - IF position('booking' in type) > 0 THEN - do_booking = true; - END IF; - IF do_asset OR do_serial OR do_booking THEN - asset_barcodes = asset_barcodes || in_barcode; - END IF; - IF position('actor' in type) > 0 THEN - do_actor = true; - actor_barcodes = actor_barcodes || in_barcode; - END IF; - - barcode_len := length(in_barcode); - - FOR completion_set IN - SELECT * FROM config.barcode_completion - WHERE active - AND org_unit IN (SELECT aou.id FROM actor.org_unit_ancestors(select_ou) aou) - LOOP - IF completion_set.prefix IS NULL THEN - completion_set.prefix := ''; - END IF; - IF completion_set.suffix IS NULL THEN - completion_set.suffix := ''; - END IF; - IF completion_set.length = 0 OR completion_set.padding IS NULL OR length(completion_set.padding) = 0 THEN - cur_barcode = completion_set.prefix || in_barcode || completion_set.suffix; - ELSE - completion_len = completion_set.length - length(completion_set.prefix) - length(completion_set.suffix); - IF completion_len >= barcode_len THEN - IF completion_set.padding_end THEN - cur_barcode = rpad(in_barcode, completion_len, completion_set.padding); - ELSE - cur_barcode = lpad(in_barcode, completion_len, completion_set.padding); - END IF; - cur_barcode = completion_set.prefix || cur_barcode || completion_set.suffix; - END IF; - END IF; - IF completion_set.actor THEN - actor_barcodes = actor_barcodes || cur_barcode; - END IF; - IF completion_set.asset THEN - asset_barcodes = asset_barcodes || cur_barcode; - END IF; - END LOOP; - - IF do_asset AND do_serial THEN - RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM ONLY asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false; - RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false; - ELSIF do_asset THEN - RETURN QUERY SELECT 'asset'::TEXT, id, barcode FROM asset.copy WHERE barcode = ANY(asset_barcodes) AND deleted = false; - ELSIF do_serial THEN - RETURN QUERY SELECT 'serial'::TEXT, id, barcode FROM serial.unit WHERE barcode = ANY(asset_barcodes) AND deleted = false; - END IF; - IF do_booking THEN - RETURN QUERY SELECT 'booking'::TEXT, id::BIGINT, barcode FROM booking.resource WHERE barcode = ANY(asset_barcodes); - END IF; - IF do_actor THEN - RETURN QUERY SELECT 'actor'::TEXT, c.usr::BIGINT, c.barcode FROM actor.card c JOIN actor.usr u ON c.usr = u.id WHERE c.barcode = ANY(actor_barcodes) AND c.active AND NOT u.deleted ORDER BY usr; - END IF; - RETURN; -END; -$$ LANGUAGE plpgsql; - -COMMENT ON FUNCTION evergreen.get_barcodes(INT, TEXT, TEXT) IS $$ -Given user input, find an appropriate barcode in the proper class. - -Will add prefix/suffix information to do so, and return all results. -$$; - -ALTER TABLE config.barcode_completion ADD CONSTRAINT config_barcode_completion_org_unit_fkey FOREIGN KEY (org_unit) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; - -COMMIT; -- 1.7.2.5