sql/Pg-upgrade2/warehouse.sql

   1 -- @tag: warehouse
   2 -- @description: Diverse neue Tabellen und Spalten zur Mehrlagerf&auml;higkeit
   3 -- @depends: release_2_4_3
   4
   5 -- Tabelle "bin" für Lagerplätze.
   6 CREATE TABLE bin (
   7   id integer NOT NULL DEFAULT nextval('id'),
   8   warehouse_id integer NOT NULL,
   9   description text,
  10   itime timestamp DEFAULT now(),
  11   mtime timestamp,
  12
  13   PRIMARY KEY (id),
  14   FOREIGN KEY (warehouse_id) REFERENCES warehouse (id)
  15 );
  16
  17 CREATE TRIGGER mtime_bin BEFORE UPDATE ON bin
  18     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
  19
  20 -- Tabelle "warehouse"
  21 ALTER TABLE warehouse ADD COLUMN sortkey integer;
  22 CREATE SEQUENCE tmp_counter;
  23 UPDATE warehouse SET sortkey = nextval('tmp_counter');
  24 DROP SEQUENCE tmp_counter;
  25
  26 ALTER TABLE warehouse ADD COLUMN invalid boolean;
  27 UPDATE warehouse SET invalid = 'f';
  28
  29 CREATE TRIGGER mtime_warehouse BEFORE UPDATE ON warehouse
  30     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
  31
  32 -- Tabelle "transfer_type"
  33 CREATE TABLE transfer_type (
  34   id integer NOT NULL DEFAULT nextval('id'),
  35   direction varchar(10) NOT NULL,
  36   description text,
  37   sortkey integer,
  38   itime timestamp DEFAULT now(),
  39   mtime timestamp,
  40
  41   PRIMARY KEY (id)
  42 );
  43
  44 CREATE TRIGGER mtime_transfer_type BEFORE UPDATE ON transfer_type
  45     FOR EACH ROW EXECUTE PROCEDURE set_mtime();
  46
  47 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'stock', 1);
  48 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'found', 2);
  49 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('in', 'correction', 3);
  50 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'used', 4);
  51 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'disposed', 5);
  52 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'back', 6);
  53 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'missing', 7);
  54 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('out', 'correction', 9);
  55 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'transfer', 10);
  56 INSERT INTO transfer_type (direction, description, sortkey) VALUES ('transfer', 'correction', 11);
  57
  58 -- Anpassungen an "inventory".
  59 DELETE FROM inventory;
  60
  61 ALTER TABLE inventory ADD COLUMN bin_id integer;
  62 ALTER TABLE inventory ADD FOREIGN KEY (bin_id) REFERENCES bin (id);
  63 ALTER TABLE inventory ALTER COLUMN bin_id SET NOT NULL;
  64
  65 ALTER TABLE inventory DROP COLUMN qty;
  66 ALTER TABLE inventory ADD COLUMN qty numeric(25, 5);
  67
  68 ALTER TABLE inventory ALTER COLUMN parts_id SET NOT NULL;
  69 ALTER TABLE inventory ADD FOREIGN KEY (parts_id) REFERENCES parts(id);
  70
  71 ALTER TABLE inventory ALTER COLUMN warehouse_id SET NOT NULL;
  72 ALTER TABLE inventory ADD FOREIGN KEY (warehouse_id) REFERENCES warehouse(id);
  73
  74 ALTER TABLE inventory ALTER COLUMN employee_id SET NOT NULL;
  75 ALTER TABLE inventory ADD FOREIGN KEY (employee_id) REFERENCES employee (id);
  76
  77 ALTER TABLE inventory ADD COLUMN trans_id integer;
  78 ALTER TABLE inventory ALTER COLUMN trans_id SET NOT NULL;
  79
  80 ALTER TABLE inventory ADD COLUMN trans_type_id integer;
  81 ALTER TABLE inventory ALTER COLUMN trans_type_id SET NOT NULL;
  82 ALTER TABLE inventory ADD FOREIGN KEY (trans_type_id) REFERENCES transfer_type (id);
  83
  84 ALTER TABLE inventory ADD COLUMN project_id integer;
  85 ALTER TABLE inventory ADD FOREIGN KEY (project_id) REFERENCES project (id);
  86
  87 ALTER TABLE inventory ADD COLUMN chargenumber text;
  88 ALTER TABLE inventory ADD COLUMN comment text;
  89
  90 -- "onhand" in "parts" über einen Trigger automatisch berechnen lassen.
  91 ALTER TABLE parts DROP COLUMN onhand;
  92 ALTER TABLE parts ADD COLUMN onhand numeric(25,5);
  93 UPDATE parts SET onhand = COALESCE((SELECT SUM(qty) FROM inventory WHERE inventory.parts_id = parts.id), 0);
  94
  95 ALTER TABLE parts ADD COLUMN stockable boolean;
  96 ALTER TABLE parts ALTER COLUMN stockable SET DEFAULT 'f';
  97 UPDATE parts SET stockable = 'f';
  98
  99 CREATE OR REPLACE FUNCTION update_onhand() RETURNS trigger AS '
 100 BEGIN
 101   IF tg_op = ''INSERT'' THEN
 102     UPDATE parts SET onhand = COALESCE(onhand, 0) + new.qty WHERE id = new.parts_id;
 103     RETURN new;
 104   ELSIF tg_op = ''DELETE'' THEN
 105     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty WHERE id = old.parts_id;
 106     RETURN old;
 107   ELSE
 108     UPDATE parts SET onhand = COALESCE(onhand, 0) - old.qty + new.qty WHERE id = old.parts_id;
 109     RETURN new;
 110   END IF;
 111 END;
 112 ' LANGUAGE plpgsql;
 113
 114 CREATE TRIGGER trig_update_onhand
 115   AFTER INSERT OR UPDATE OR DELETE ON inventory
 116   FOR EACH ROW EXECUTE PROCEDURE update_onhand();
 117