3 INSERT INTO permission.perm_list (id,code,description) VALUES (592,'CONTAINER_BATCH_UPDATE','Allow batch update via buckets');
5 INSERT INTO container.user_bucket_type (code,label) SELECT code,label FROM container.copy_bucket_type where code = 'staff_client';
7 CREATE TABLE action.fieldset_group (
10 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
11 complete_time TIMESTAMPTZ,
12 container INT, -- Points to a container of some type ...
13 container_type TEXT, -- One of 'biblio_record_entry', 'user', 'call_number', 'copy'
14 can_rollback BOOL DEFAULT TRUE,
15 rollback_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
16 rollback_time TIMESTAMPTZ,
17 creator INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
18 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
21 ALTER TABLE action.fieldset ADD COLUMN fieldset_group INT REFERENCES action.fieldset_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
22 ALTER TABLE action.fieldset ADD COLUMN error_msg TEXT;
23 ALTER TABLE container.biblio_record_entry_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
24 ALTER TABLE container.user_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
25 ALTER TABLE container.call_number_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
26 ALTER TABLE container.copy_bucket ADD COLUMN owning_lib INT REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
28 UPDATE query.stored_query SET id = id + 1000 WHERE id < 1000;
29 UPDATE query.from_relation SET id = id + 1000 WHERE id < 1000;
30 UPDATE query.expression SET id = id + 1000 WHERE id < 1000;
32 SELECT SETVAL('query.stored_query_id_seq', 1, FALSE);
33 SELECT SETVAL('query.from_relation_id_seq', 1, FALSE);
34 SELECT SETVAL('query.expression_id_seq', 1, FALSE);
36 INSERT INTO query.bind_variable (name,type,description,label)
37 SELECT 'bucket','number','ID of the bucket to pull items from','Bucket ID'
38 WHERE NOT EXISTS (SELECT 1 FROM query.bind_variable WHERE name = 'bucket');
40 -- Assumes completely empty 'query' schema
41 INSERT INTO query.stored_query (type, use_distinct) VALUES ('SELECT', TRUE); -- 1
43 INSERT INTO query.from_relation (type, table_name, class_name, table_alias) VALUES ('RELATION', 'container.user_bucket_item', 'cubi', 'cubi'); -- 1
44 UPDATE query.stored_query SET from_clause = 1;
46 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'target_user'); -- 1
47 INSERT INTO query.select_item (stored_query,seq_no,expression) VALUES (1,1,1);
49 INSERT INTO query.expr_xcol (table_alias, column_name) VALUES ('cubi', 'bucket'); -- 2
50 INSERT INTO query.expr_xbind (bind_variable) VALUES ('bucket'); -- 3
52 INSERT INTO query.expr_xop (left_operand, operator, right_operand) VALUES (2, '=', 3); -- 4
53 UPDATE query.stored_query SET where_clause = 4;
55 SELECT SETVAL('query.stored_query_id_seq', 1000, TRUE) FROM query.stored_query;
56 SELECT SETVAL('query.from_relation_id_seq', 1000, TRUE) FROM query.from_relation;
57 SELECT SETVAL('query.expression_id_seq', 10000, TRUE) FROM query.expression;
59 CREATE OR REPLACE FUNCTION action.apply_fieldset(
60 fieldset_id IN INT, -- id from action.fieldset
61 table_name IN TEXT, -- table to be updated
62 pkey_name IN TEXT, -- name of primary key column in that table
63 query IN TEXT -- query constructed by qstore (for query-based
64 -- fieldsets only; otherwise null
80 fs_obj action.fieldset%ROWTYPE;
81 fs_group action.fieldset_group%ROWTYPE;
85 IF fieldset_id IS NULL THEN
86 RETURN 'Fieldset ID parameter is NULL';
88 IF table_name IS NULL THEN
89 RETURN 'Table name parameter is NULL';
91 IF pkey_name IS NULL THEN
92 RETURN 'Primary key name parameter is NULL';
97 quote_literal( pkey_value )
107 -- Build the WHERE clause. This differs according to whether it's a
108 -- single-row fieldset or a query-based fieldset.
110 IF query IS NULL AND fs_pkey_value IS NULL THEN
111 RETURN 'Incomplete fieldset: neither a primary key nor a query available';
112 ELSIF query IS NOT NULL AND fs_pkey_value IS NULL THEN
113 fs_query := rtrim( query, ';' );
114 where_clause := 'WHERE ' || pkey_name || ' IN ( '
116 ELSIF query IS NULL AND fs_pkey_value IS NOT NULL THEN
117 where_clause := 'WHERE ' || pkey_name || ' = ';
118 IF pkey_name = 'id' THEN
119 where_clause := where_clause || fs_pkey_value;
120 ELSIF pkey_name = 'code' THEN
121 where_clause := where_clause || quote_literal(fs_pkey_value);
123 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
125 ELSE -- both are not null
126 RETURN 'Ambiguous fieldset: both a primary key and a query provided';
129 IF fs_status IS NULL THEN
130 RETURN 'No fieldset found for id = ' || fieldset_id;
131 ELSIF fs_status = 'APPLIED' THEN
132 RETURN 'Fieldset ' || fieldset_id || ' has already been applied';
135 SELECT * INTO fs_obj FROM action.fieldset WHERE id = fieldset_id;
136 SELECT * INTO fs_group FROM action.fieldset_group WHERE id = fs_obj.fieldset_group;
138 IF fs_group.can_rollback THEN
139 -- This is part of a non-rollback group. We need to record the current values for future rollback.
141 INSERT INTO action.fieldset_group (can_rollback, name, creator, owning_lib, container, container_type)
142 VALUES (FALSE, 'ROLLBACK: '|| fs_group.name, fs_group.creator, fs_group.owning_lib, fs_group.container, fs_group.container_type);
144 fsg_id := CURRVAL('action.fieldset_group_id_seq');
146 FOR rb_row IN EXECUTE 'SELECT * FROM ' || table_name || ' ' || where_clause LOOP
147 IF pkey_name = 'id' THEN
148 fs_pkey_value := rb_row.id;
149 ELSIF pkey_name = 'code' THEN
150 fs_pkey_value := rb_row.code;
152 RETURN 'Only know how to handle "id" and "code" pkeys currently, received ' || pkey_name;
154 INSERT INTO action.fieldset (fieldset_group,owner,owning_lib,status,classname,name,pkey_value)
155 VALUES (fsg_id, fs_obj.owner, fs_obj.owning_lib, 'PENDING', fs_obj.classname, fs_obj.name || ' ROLLBACK FOR ' || fs_pkey_value, fs_pkey_value);
157 fs_id := CURRVAL('action.fieldset_id_seq');
161 FROM action.fieldset_col_val
162 WHERE fieldset = fieldset_id
164 EXECUTE 'INSERT INTO action.fieldset_col_val (fieldset, col, val) ' ||
165 'SELECT '|| fs_id || ', '||quote_literal(cv.col)||', '||cv.col||' FROM '||table_name||' WHERE '||pkey_name||' = '||fs_pkey_value;
170 statement := 'UPDATE ' || table_name || ' SET';
176 FROM action.fieldset_col_val
177 WHERE fieldset = fieldset_id
179 statement := statement || sep || ' ' || cv.col
180 || ' = ' || coalesce( quote_literal( cv.val ), 'NULL' );
185 RETURN 'Fieldset ' || fieldset_id || ' has no column values defined';
187 statement := statement || ' ' || where_clause;
190 -- Execute the update
194 GET DIAGNOSTICS update_count = ROW_COUNT;
196 IF update_count = 0 THEN
197 RAISE data_exception;
200 IF fsg_id IS NOT NULL THEN
201 UPDATE action.fieldset_group SET rollback_group = fsg_id WHERE id = fs_group.id;
204 IF fs_group.id IS NOT NULL THEN
205 UPDATE action.fieldset_group SET complete_time = now() WHERE id = fs_group.id;
208 UPDATE action.fieldset SET status = 'APPLIED', applied_time = now() WHERE id = fieldset_id;
210 EXCEPTION WHEN data_exception THEN
211 msg := 'No eligible rows found for fieldset ' || fieldset_id;
212 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;
219 EXCEPTION WHEN OTHERS THEN
220 msg := 'Unable to apply fieldset ' || fieldset_id || ': ' || sqlerrm;
221 UPDATE action.fieldset SET status = 'ERROR', applied_time = now() WHERE id = fieldset_id;