Open-ILS/src/sql/Pg/upgrade/0304.schema.query-expr-xcase-left-operand.sql

   1 -- Add left_operand column to query.expr_xcase view
   2
   3 -- If the view doesn't exist, this drop will fail, and that's okay
   4 DROP VIEW query.expr_xcase CASCADE;
   5
   6 BEGIN;
   7
   8 INSERT INTO config.upgrade_log (version) VALUES ('0304'); -- Scott McKellar
   9
  10 CREATE OR REPLACE VIEW query.expr_xcase AS
  11     SELECT
  12                 id,
  13                 parenthesize,
  14                 parent_expr,
  15                 seq_no,
  16                 left_operand,
  17                 negate
  18     FROM
  19         query.expression
  20     WHERE
  21         type = 'xcase';
  22
  23 CREATE OR REPLACE RULE query_expr_xcase_insert_rule AS
  24     ON INSERT TO query.expr_xcase
  25     DO INSTEAD
  26     INSERT INTO query.expression (
  27                 id,
  28                 type,
  29                 parenthesize,
  30                 parent_expr,
  31                 seq_no,
  32                 left_operand,
  33                 negate
  34     ) VALUES (
  35         COALESCE(NEW.id, NEXTVAL('query.expression_id_seq'::REGCLASS)),
  36         'xcase',
  37         COALESCE(NEW.parenthesize, FALSE),
  38         NEW.parent_expr,
  39         COALESCE(NEW.seq_no, 1),
  40                 NEW.left_operand,
  41                 COALESCE(NEW.negate, false)
  42     );
  43
  44 CREATE OR REPLACE RULE query_expr_xcase_update_rule AS
  45     ON UPDATE TO query.expr_xcase
  46     DO INSTEAD
  47     UPDATE query.expression SET
  48         id = NEW.id,
  49         parenthesize = NEW.parenthesize,
  50         parent_expr = NEW.parent_expr,
  51         seq_no = NEW.seq_no,
  52                 left_operand = NEW.left_operand,
  53                 negate = NEW.negate
  54     WHERE
  55         id = OLD.id;
  56
  57 CREATE OR REPLACE RULE query_expr_xcase_delete_rule AS
  58     ON DELETE TO query.expr_xcase
  59     DO INSTEAD
  60     DELETE FROM query.expression WHERE id = OLD.id;
  61
  62 COMMIT;