1 -- @tag: requirement_specs_orders
2 -- @description: requirement_specs_orders
3 -- @depends: requirement_specs requirement_specs_section_templates
5 -- Remove unneeded columns
6 ALTER TABLE requirement_spec_versions DROP CONSTRAINT requirement_spec_versions_order_id_fkey;
8 ALTER TABLE requirement_spec_versions DROP COLUMN order_date;
9 ALTER TABLE requirement_spec_versions DROP COLUMN order_number;
10 ALTER TABLE requirement_spec_versions DROP COLUMN order_id;
12 -- Add new columns to existing tables
13 ALTER TABLE requirement_spec_items ADD COLUMN order_part_id INTEGER;
14 ALTER TABLE requirement_spec_items ADD FOREIGN KEY (order_part_id) REFERENCES parts (id) ON DELETE SET NULL;
16 ALTER TABLE defaults ADD COLUMN requirement_spec_section_order_part_id INTEGER;
17 ALTER TABLE defaults ADD FOREIGN KEY (requirement_spec_section_order_part_id) REFERENCES parts (id) ON DELETE SET NULL;
20 CREATE TABLE requirement_spec_orders (
22 requirement_spec_id INTEGER NOT NULL,
23 order_id INTEGER NOT NULL,
25 itime TIMESTAMP NOT NULL DEFAULT now(),
26 mtime TIMESTAMP NOT NULL DEFAULT now(),
29 FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id) ON DELETE CASCADE,
30 FOREIGN KEY (order_id) REFERENCES oe (id) ON DELETE CASCADE,
31 FOREIGN KEY (version_id) REFERENCES requirement_spec_versions (id) ON DELETE SET NULL,
32 CONSTRAINT requirement_spec_id_order_id_unique UNIQUE (requirement_spec_id, order_id)
35 CREATE TRIGGER mtime_requirement_spec_orders BEFORE UPDATE ON requirement_spec_orders FOR EACH ROW EXECUTE PROCEDURE set_mtime();