sql/Pg-upgrade2/requirement_specs.sql

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