sql/Pg-upgrade2/requirement_specs.sql

   1 -- @tag: requirement_specs
   2 -- @description: Pflichtenhefte
   3 -- @depends: release_3_0_0
   4 -- @charset: utf-8
   5
   6 -- Nur für Entwicklungszwecke:
   7
   8 -- DELETE FROM schema_info WHERE tag = 'requirement_specs';
   9
  10 -- BEGIN;
  11 -- DROP TABLE requirement_spec_item_dependencies;
  12 -- DROP TABLE requirement_spec_items;
  13 -- DROP TABLE requirement_spec_text_blocks;
  14 -- DROP TABLE requirement_specs;
  15 -- DROP TABLE requirement_spec_versions;
  16 -- DROP TABLE requirement_spec_predefined_texts;
  17 -- DROP TABLE requirement_spec_types;
  18 -- DROP TABLE requirement_spec_statuses;
  19 -- DROP TABLE requirement_spec_risks;
  20 -- DROP TABLE requirement_spec_complexities;
  21 -- DROP TABLE requirement_spec_acceptance_statuses;
  22 -- ALTER TABLE customer DROP COLUMN hourly_rate;
  23 -- ALTER TABLE defaults DROP COLUMN requirement_spec_section_number_format;
  24 -- ALTER TABLE defaults DROP COLUMN requirement_spec_function_block_number_format;
  25
  26 CREATE TABLE requirement_spec_acceptance_statuses (
  27        id          SERIAL,
  28        name        TEXT      NOT NULL,
  29        description TEXT,
  30        position    INTEGER   NOT NULL,
  31        itime       TIMESTAMP DEFAULT now(),
  32        mtime       TIMESTAMP,
  33
  34        PRIMARY KEY (id),
  35        UNIQUE (name, description)
  36 );
  37 CREATE TRIGGER mtime_requirement_spec_acceptance_statuses BEFORE UPDATE ON requirement_spec_acceptance_statuses FOR EACH ROW EXECUTE PROCEDURE set_mtime();
  38
  39 INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted',                          'Abgenommen',                                1);
  40 INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted_with_defects',             'Mit Mängeln abgenommen',                    2);
  41 INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('accepted_with_defects_to_be_fixed', 'Mit noch zu behebenden Mängeln abgenommen', 3);
  42 INSERT INTO requirement_spec_acceptance_statuses (name, description, position) VALUES ('not_accepted',                      'Nicht abgenommen',                          4);
  43
  44
  45
  46 CREATE TABLE requirement_spec_complexities (
  47        id          SERIAL,
  48        description TEXT      NOT NULL,
  49        position    INTEGER   NOT NULL,
  50        itime       TIMESTAMP DEFAULT now(),
  51        mtime       TIMESTAMP,
  52
  53        PRIMARY KEY (id),
  54        UNIQUE (description)
  55 );
  56 CREATE TRIGGER mtime_requirement_spec_complexities BEFORE UPDATE ON requirement_spec_complexities FOR EACH ROW EXECUTE PROCEDURE set_mtime();
  57
  58 INSERT INTO requirement_spec_complexities (description, position) VALUES ('nicht bewertet',  1);
  59 INSERT INTO requirement_spec_complexities (description, position) VALUES ('nur Anforderung', 2);
  60 INSERT INTO requirement_spec_complexities (description, position) VALUES ('gering',          3);
  61 INSERT INTO requirement_spec_complexities (description, position) VALUES ('mittel',          4);
  62 INSERT INTO requirement_spec_complexities (description, position) VALUES ('hoch',            5);
  63
  64
  65
  66 CREATE TABLE requirement_spec_risks (
  67        id          SERIAL,
  68        description TEXT      NOT NULL,
  69        position    INTEGER   NOT NULL,
  70        itime       TIMESTAMP DEFAULT now(),
  71        mtime       TIMESTAMP,
  72
  73        PRIMARY KEY (id),
  74        UNIQUE (description)
  75 );
  76 CREATE TRIGGER mtime_requirement_spec_risks BEFORE UPDATE ON requirement_spec_risks FOR EACH ROW EXECUTE PROCEDURE set_mtime();
  77
  78 INSERT INTO requirement_spec_risks (description, position) VALUES ('nicht bewertet',  1);
  79 INSERT INTO requirement_spec_risks (description, position) VALUES ('nur Anforderung', 2);
  80 INSERT INTO requirement_spec_risks (description, position) VALUES ('gering',          3);
  81 INSERT INTO requirement_spec_risks (description, position) VALUES ('mittel',          4);
  82 INSERT INTO requirement_spec_risks (description, position) VALUES ('hoch',            5);
  83
  84
  85
  86 CREATE TABLE requirement_spec_statuses (
  87        id          SERIAL,
  88        name        TEXT      NOT NULL,
  89        description TEXT      NOT NULL,
  90        position    INTEGER   NOT NULL,
  91        itime       TIMESTAMP DEFAULT now(),
  92        mtime       TIMESTAMP,
  93
  94        PRIMARY KEY (id),
  95        UNIQUE (name, description)
  96 );
  97 CREATE TRIGGER mtime_requirement_spec_statuses BEFORE UPDATE ON requirement_spec_statuses FOR EACH ROW EXECUTE PROCEDURE set_mtime();
  98
  99 INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('planning', 'In Planung',     1);
 100 INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('running',  'In Bearbeitung', 2);
 101 INSERT INTO requirement_spec_statuses (name, description, position) VALUES ('done',     'Fertiggestellt', 3);
 102
 103
 104
 105 CREATE TABLE requirement_spec_types (
 106        id          SERIAL,
 107        description TEXT      NOT NULL,
 108        position    INTEGER   NOT NULL,
 109        itime       TIMESTAMP DEFAULT now(),
 110        mtime       TIMESTAMP,
 111
 112        PRIMARY KEY (id),
 113        UNIQUE (description)
 114 );
 115 CREATE TRIGGER mtime_requirement_spec_types BEFORE UPDATE ON requirement_spec_types FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 116
 117 INSERT INTO requirement_spec_types (description, position) VALUES ('Pflichtenheft', 1);
 118 INSERT INTO requirement_spec_types (description, position) VALUES ('Konzept',       2);
 119
 120
 121
 122 CREATE TABLE requirement_spec_predefined_texts (
 123        id          SERIAL,
 124        description TEXT      NOT NULL,
 125        title       TEXT      NOT NULL,
 126        text        TEXT      NOT NULL,
 127        position    INTEGER   NOT NULL,
 128        itime       TIMESTAMP DEFAULT now(),
 129        mtime       TIMESTAMP,
 130
 131        PRIMARY KEY (id),
 132        UNIQUE (description)
 133 );
 134 CREATE TRIGGER mtime_requirement_spec_predefined_texts BEFORE UPDATE ON requirement_spec_predefined_texts FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 135
 136
 137
 138 CREATE TABLE requirement_spec_versions (
 139        id             SERIAL,
 140        version_number INTEGER,
 141        description    TEXT NOT NULL,
 142        comment        TEXT,
 143        order_date     DATE,
 144        order_number   TEXT,
 145        order_id       INTEGER,
 146        itime          TIMESTAMP DEFAULT now(),
 147        mtime          TIMESTAMP,
 148
 149        PRIMARY KEY (id),
 150        FOREIGN KEY (order_id) REFERENCES oe (id)
 151 );
 152 CREATE TRIGGER mtime_requirement_spec_versions BEFORE UPDATE ON requirement_spec_versions FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 153
 154
 155
 156 CREATE TABLE requirement_specs (
 157        id                      SERIAL,
 158        type_id                 INTEGER        NOT NULL,
 159        status_id               INTEGER        NOT NULL,
 160        version_id              INTEGER,
 161        customer_id             INTEGER        NOT NULL,
 162        project_id              INTEGER,
 163        title                   TEXT           NOT NULL,
 164        hourly_rate             NUMERIC(8, 2)  NOT NULL DEFAULT 0,
 165        net_sum                 NUMERIC(12, 2) NOT NULL DEFAULT 0,
 166        working_copy_id         INTEGER,
 167        previous_section_number INTEGER        NOT NULL,
 168        previous_fb_number      INTEGER        NOT NULL,
 169        is_template             BOOLEAN                 DEFAULT FALSE,
 170        itime                   TIMESTAMP               DEFAULT now(),
 171        mtime                   TIMESTAMP,
 172
 173        PRIMARY KEY (id),
 174        FOREIGN KEY (type_id)         REFERENCES requirement_spec_types    (id),
 175        FOREIGN KEY (status_id)       REFERENCES requirement_spec_statuses (id),
 176        FOREIGN KEY (version_id)      REFERENCES requirement_spec_versions (id),
 177        FOREIGN KEY (working_copy_id) REFERENCES requirement_specs         (id),
 178        FOREIGN KEY (customer_id)     REFERENCES customer                  (id),
 179        FOREIGN KEY (project_id)      REFERENCES project                   (id)
 180 );
 181 CREATE TRIGGER mtime_requirement_specs BEFORE UPDATE ON requirement_specs FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 182
 183
 184
 185 CREATE TABLE requirement_spec_text_blocks (
 186        id                  SERIAL,
 187        requirement_spec_id INTEGER   NOT NULL,
 188        title               TEXT      NOT NULL,
 189        text                TEXT,
 190        position            INTEGER   NOT NULL,
 191        output_position     INTEGER   NOT NULL DEFAULT 1,
 192        itime               TIMESTAMP NOT NULL DEFAULT now(),
 193        mtime               TIMESTAMP,
 194
 195        PRIMARY KEY (id),
 196        FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id)
 197 );
 198 CREATE TRIGGER mtime_requirement_spec_text_blocks BEFORE UPDATE ON requirement_spec_text_blocks FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 199
 200
 201 CREATE TABLE requirement_spec_items (
 202        id                   SERIAL,
 203        requirement_spec_id  INTEGER        NOT NULL,
 204        item_type            TEXT           NOT NULL,
 205        parent_id            INTEGER,
 206        position             INTEGER        NOT NULL,
 207        fb_number            TEXT           NOT NULL,
 208        title                TEXT,
 209        description          TEXT,
 210        complexity_id        INTEGER,
 211        risk_id              INTEGER,
 212        time_estimation      NUMERIC(12, 2) NOT NULL DEFAULT 0,
 213        net_sum              NUMERIC(12, 2) NOT NULL DEFAULT 0,
 214        is_flagged           BOOLEAN        NOT NULL DEFAULT FALSE,
 215        acceptance_status_id INTEGER,
 216        acceptance_text      TEXT,
 217        itime                TIMESTAMP      NOT NULL DEFAULT now(),
 218        mtime                TIMESTAMP,
 219
 220        PRIMARY KEY (id),
 221        FOREIGN KEY (requirement_spec_id)  REFERENCES requirement_specs (id),
 222        FOREIGN KEY (parent_id)            REFERENCES requirement_spec_items (id),
 223        FOREIGN KEY (complexity_id)        REFERENCES requirement_spec_complexities (id),
 224        FOREIGN KEY (risk_id)              REFERENCES requirement_spec_risks (id),
 225        FOREIGN KEY (acceptance_status_id) REFERENCES requirement_spec_acceptance_statuses (id),
 226
 227        CONSTRAINT valid_item_type CHECK ((item_type = 'section') OR (item_type = 'function-block') OR (item_type = 'sub-function-block')),
 228        CONSTRAINT valid_parent_id_for_item_type CHECK (CASE
 229          WHEN (item_type = 'section') THEN parent_id IS NULL
 230          ELSE                              parent_id IS NOT NULL
 231        END)
 232 );
 233 CREATE TRIGGER mtime_requirement_spec_items BEFORE UPDATE ON requirement_spec_items FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 234
 235
 236
 237 CREATE TABLE requirement_spec_item_dependencies (
 238        depending_item_id INTEGER NOT NULL,
 239        depended_item_id  INTEGER NOT NULL,
 240
 241        PRIMARY KEY (depending_item_id, depended_item_id),
 242        FOREIGN KEY (depending_item_id) REFERENCES requirement_spec_items (id),
 243        FOREIGN KEY (depended_item_id)  REFERENCES requirement_spec_items (id)
 244 );
 245
 246 ALTER TABLE customer ADD COLUMN hourly_rate NUMERIC(8, 2);
 247
 248
 249 -- Trigger for updating time_estimation of function blocks from their
 250 -- children (not for sections, not for sub function blocks).
 251 CREATE OR REPLACE FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER) RETURNS BOOLEAN AS $$
 252   DECLARE
 253     item   RECORD;
 254     parent RECORD;
 255   BEGIN
 256     IF item_id IS NULL THEN
 257       RAISE DEBUG 'updateRSIE: item_id IS NULL';
 258       RETURN FALSE;
 259     END IF;
 260
 261     SELECT * INTO item FROM requirement_spec_items WHERE id = item_id;
 262     RAISE DEBUG 'updateRSIE: item_id % parent_id %', item_id, item.parent_id;
 263
 264     IF item.parent_id IS NULL THEN
 265       -- Don't do anything for sections.
 266       RAISE DEBUG 'updateRSIE: this is a section.';
 267       RETURN FALSE;
 268     END IF;
 269
 270     SELECT * INTO parent FROM requirement_spec_items WHERE id = item.parent_id;
 271     RAISE DEBUG 'updateRSIE: parent_id of parent of item: %', parent.parent_id;
 272
 273     IF parent.parent_id IS NOT NULL THEN
 274       -- Don't do anything for sub function blocks.
 275       RAISE DEBUG 'updateRSIE: this is sub function block.';
 276       RETURN FALSE;
 277     END IF;
 278
 279     RAISE DEBUG 'updateRSIE: will do stuff now';
 280
 281     UPDATE requirement_spec_items
 282       SET time_estimation = COALESCE((
 283         SELECT SUM(time_estimation)
 284         FROM requirement_spec_items
 285         WHERE parent_id = item_id
 286       ), 0)
 287       WHERE id = item_id;
 288
 289     RETURN TRUE;
 290   END;
 291 $$ LANGUAGE plpgsql;
 292
 293 CREATE OR REPLACE FUNCTION requirement_spec_item_time_estimation_updater_trigger() RETURNS trigger AS $$
 294   BEGIN
 295     IF ((TG_OP = 'UPDATE') OR (TG_OP = 'DELETE')) THEN
 296       PERFORM update_requirement_spec_item_time_estimation(OLD.parent_id);
 297     END IF;
 298     IF ((TG_OP = 'UPDATE') OR (TG_OP = 'INSERT')) THEN
 299       PERFORM update_requirement_spec_item_time_estimation(NEW.parent_id);
 300     END IF;
 301     RETURN NULL;
 302   END;
 303 $$ LANGUAGE plpgsql;
 304
 305 DROP TRIGGER IF EXISTS update_requirement_spec_item_time_estimation ON requirement_spec_items;
 306 CREATE TRIGGER update_requirement_spec_item_time_estimation
 307 AFTER INSERT OR UPDATE OR DELETE ON requirement_spec_items
 308 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_time_estimation_updater_trigger();
 309
 310
 311 -- Trigger for deleting depending stuff if a requirement spec item is deleted.
 312 CREATE OR REPLACE FUNCTION requirement_spec_item_before_delete_trigger() RETURNS trigger AS $$
 313   BEGIN
 314     RAISE DEBUG 'delete trig RSitem old id %', OLD.id;
 315     DELETE FROM requirement_spec_item_dependencies WHERE (depending_item_id = OLD.id) OR (depended_item_id = OLD.id);
 316     DELETE FROM requirement_spec_items             WHERE (parent_id         = OLD.id);
 317
 318     RETURN OLD;
 319   END;
 320 $$ LANGUAGE plpgsql;
 321
 322 DROP TRIGGER IF EXISTS delete_requirement_spec_item_dependencies ON requirement_spec_items;
 323 CREATE TRIGGER delete_requirement_spec_item_dependencies
 324 BEFORE DELETE ON requirement_spec_items
 325 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_before_delete_trigger();
 326
 327
 328 -- Trigger for deleting depending stuff if a requirement spec is deleted.
 329 CREATE OR REPLACE FUNCTION requirement_spec_delete_trigger() RETURNS trigger AS $$
 330   DECLARE
 331     tname TEXT;
 332   BEGIN
 333     tname := 'tmp_delete_reqspec' || OLD.id;
 334
 335     IF TG_WHEN = 'AFTER' THEN
 336       RAISE DEBUG 'after trigger on %; deleting from versions', OLD.id;
 337       EXECUTE 'DELETE FROM requirement_spec_versions ' ||
 338               'WHERE id IN (SELECT version_id FROM ' || tname || ')';
 339
 340       RAISE DEBUG '  dropping table';
 341       EXECUTE 'DROP TABLE ' || tname;
 342
 343       RETURN OLD;
 344     END IF;
 345
 346     RAISE DEBUG 'before delete trigger on %', OLD.id;
 347
 348     EXECUTE 'CREATE TEMPORARY TABLE ' || tname || ' AS ' ||
 349       'SELECT DISTINCT version_id '     ||
 350       'FROM requirement_specs '         ||
 351       'WHERE (version_id IS NOT NULL) ' ||
 352       '  AND ((id = ' || OLD.id || ') OR (working_copy_id = ' || OLD.id || '))';
 353
 354     RAISE DEBUG '  Updating version_id and items for %', OLD.id;
 355     UPDATE requirement_specs      SET version_id = NULL                        WHERE (id <> OLD.id) AND (working_copy_id = OLD.id);
 356     UPDATE requirement_spec_items SET item_type  = 'section', parent_id = NULL WHERE requirement_spec_id = OLD.id;
 357
 358     RAISE DEBUG '  Deleting stuff for %', OLD.id;
 359
 360     DELETE FROM requirement_spec_text_blocks WHERE (requirement_spec_id = OLD.id);
 361     DELETE FROM requirement_spec_items       WHERE (requirement_spec_id = OLD.id);
 362     DELETE FROM requirement_specs            WHERE (working_copy_id     = OLD.id);
 363
 364     RAISE DEBUG '  And we out for %', OLD.id;
 365
 366     RETURN OLD;
 367   END;
 368 $$ LANGUAGE plpgsql;
 369
 370 DROP TRIGGER IF EXISTS delete_requirement_spec_dependencies ON requirement_specs;
 371 CREATE TRIGGER delete_requirement_spec_dependencies
 372 BEFORE DELETE ON requirement_specs
 373 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();
 374
 375 DROP TRIGGER IF EXISTS after_delete_requirement_spec_dependencies ON requirement_specs;
 376 CREATE TRIGGER after_delete_requirement_spec_dependencies
 377 AFTER DELETE ON requirement_specs
 378 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();
 379
 380
 381 -- Add formats for section/function block numbers to defaults
 382 ALTER TABLE defaults ADD   COLUMN requirement_spec_section_number_format        TEXT;
 383 ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET DEFAULT 'A00';
 384 ALTER TABLE defaults ADD   COLUMN requirement_spec_function_block_number_format TEXT;
 385 ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET DEFAULT 'FB000';
 386
 387 UPDATE defaults SET requirement_spec_section_number_format        = 'A00';
 388 UPDATE defaults SET requirement_spec_function_block_number_format = 'FB000';
 389
 390 ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET NOT NULL;
 391 ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET NOT NULL;