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        is_flagged          BOOLEAN   NOT NULL DEFAULT FALSE,
 193        itime               TIMESTAMP NOT NULL DEFAULT now(),
 194        mtime               TIMESTAMP,
 195
 196        PRIMARY KEY (id),
 197        FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id)
 198 );
 199 CREATE TRIGGER mtime_requirement_spec_text_blocks BEFORE UPDATE ON requirement_spec_text_blocks FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 200
 201
 202 CREATE TABLE requirement_spec_items (
 203        id                   SERIAL,
 204        requirement_spec_id  INTEGER        NOT NULL,
 205        item_type            TEXT           NOT NULL,
 206        parent_id            INTEGER,
 207        position             INTEGER        NOT NULL,
 208        fb_number            TEXT           NOT NULL,
 209        title                TEXT,
 210        description          TEXT,
 211        complexity_id        INTEGER,
 212        risk_id              INTEGER,
 213        time_estimation      NUMERIC(12, 2) NOT NULL DEFAULT 0,
 214        net_sum              NUMERIC(12, 2) NOT NULL DEFAULT 0,
 215        is_flagged           BOOLEAN        NOT NULL DEFAULT FALSE,
 216        acceptance_status_id INTEGER,
 217        acceptance_text      TEXT,
 218        itime                TIMESTAMP      NOT NULL DEFAULT now(),
 219        mtime                TIMESTAMP,
 220
 221        PRIMARY KEY (id),
 222        FOREIGN KEY (requirement_spec_id)  REFERENCES requirement_specs (id),
 223        FOREIGN KEY (parent_id)            REFERENCES requirement_spec_items (id),
 224        FOREIGN KEY (complexity_id)        REFERENCES requirement_spec_complexities (id),
 225        FOREIGN KEY (risk_id)              REFERENCES requirement_spec_risks (id),
 226        FOREIGN KEY (acceptance_status_id) REFERENCES requirement_spec_acceptance_statuses (id),
 227
 228        CONSTRAINT valid_item_type CHECK ((item_type = 'section') OR (item_type = 'function-block') OR (item_type = 'sub-function-block')),
 229        CONSTRAINT valid_parent_id_for_item_type CHECK (CASE
 230          WHEN (item_type = 'section') THEN parent_id IS NULL
 231          ELSE                              parent_id IS NOT NULL
 232        END)
 233 );
 234 CREATE TRIGGER mtime_requirement_spec_items BEFORE UPDATE ON requirement_spec_items FOR EACH ROW EXECUTE PROCEDURE set_mtime();
 235
 236
 237
 238 CREATE TABLE requirement_spec_item_dependencies (
 239        depending_item_id INTEGER NOT NULL,
 240        depended_item_id  INTEGER NOT NULL,
 241
 242        PRIMARY KEY (depending_item_id, depended_item_id),
 243        FOREIGN KEY (depending_item_id) REFERENCES requirement_spec_items (id),
 244        FOREIGN KEY (depended_item_id)  REFERENCES requirement_spec_items (id)
 245 );
 246
 247 ALTER TABLE customer ADD COLUMN hourly_rate NUMERIC(8, 2);
 248
 249
 250 -- Trigger for updating time_estimation of function blocks from their
 251 -- children (not for sections, not for sub function blocks).
 252 CREATE OR REPLACE FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER) RETURNS BOOLEAN AS $$
 253   DECLARE
 254     item   RECORD;
 255     parent RECORD;
 256   BEGIN
 257     IF item_id IS NULL THEN
 258       RAISE DEBUG 'updateRSIE: item_id IS NULL';
 259       RETURN FALSE;
 260     END IF;
 261
 262     SELECT * INTO item FROM requirement_spec_items WHERE id = item_id;
 263     RAISE DEBUG 'updateRSIE: item_id % parent_id %', item_id, item.parent_id;
 264
 265     IF item.parent_id IS NULL THEN
 266       -- Don't do anything for sections.
 267       RAISE DEBUG 'updateRSIE: this is a section.';
 268       RETURN FALSE;
 269     END IF;
 270
 271     SELECT * INTO parent FROM requirement_spec_items WHERE id = item.parent_id;
 272     RAISE DEBUG 'updateRSIE: parent_id of parent of item: %', parent.parent_id;
 273
 274     IF parent.parent_id IS NOT NULL THEN
 275       -- Don't do anything for sub function blocks.
 276       RAISE DEBUG 'updateRSIE: this is sub function block.';
 277       RETURN FALSE;
 278     END IF;
 279
 280     RAISE DEBUG 'updateRSIE: will do stuff now';
 281
 282     UPDATE requirement_spec_items
 283       SET time_estimation = COALESCE((
 284         SELECT SUM(time_estimation)
 285         FROM requirement_spec_items
 286         WHERE parent_id = item_id
 287       ), 0)
 288       WHERE id = item_id;
 289
 290     RETURN TRUE;
 291   END;
 292 $$ LANGUAGE plpgsql;
 293
 294 CREATE OR REPLACE FUNCTION requirement_spec_item_time_estimation_updater_trigger() RETURNS trigger AS $$
 295   BEGIN
 296     IF ((TG_OP = 'UPDATE') OR (TG_OP = 'DELETE')) THEN
 297       PERFORM update_requirement_spec_item_time_estimation(OLD.parent_id);
 298     END IF;
 299     IF ((TG_OP = 'UPDATE') OR (TG_OP = 'INSERT')) THEN
 300       PERFORM update_requirement_spec_item_time_estimation(NEW.parent_id);
 301     END IF;
 302     RETURN NULL;
 303   END;
 304 $$ LANGUAGE plpgsql;
 305
 306 DROP TRIGGER IF EXISTS update_requirement_spec_item_time_estimation ON requirement_spec_items;
 307 CREATE TRIGGER update_requirement_spec_item_time_estimation
 308 AFTER INSERT OR UPDATE OR DELETE ON requirement_spec_items
 309 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_time_estimation_updater_trigger();
 310
 311
 312 -- Trigger for deleting depending stuff if a requirement spec item is deleted.
 313 CREATE OR REPLACE FUNCTION requirement_spec_item_before_delete_trigger() RETURNS trigger AS $$
 314   BEGIN
 315     RAISE DEBUG 'delete trig RSitem old id %', OLD.id;
 316     DELETE FROM requirement_spec_item_dependencies WHERE (depending_item_id = OLD.id) OR (depended_item_id = OLD.id);
 317     DELETE FROM requirement_spec_items             WHERE (parent_id         = OLD.id);
 318
 319     RETURN OLD;
 320   END;
 321 $$ LANGUAGE plpgsql;
 322
 323 DROP TRIGGER IF EXISTS delete_requirement_spec_item_dependencies ON requirement_spec_items;
 324 CREATE TRIGGER delete_requirement_spec_item_dependencies
 325 BEFORE DELETE ON requirement_spec_items
 326 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_before_delete_trigger();
 327
 328
 329 -- Trigger for deleting depending stuff if a requirement spec is deleted.
 330 CREATE OR REPLACE FUNCTION requirement_spec_delete_trigger() RETURNS trigger AS $$
 331   DECLARE
 332     tname TEXT;
 333   BEGIN
 334     tname := 'tmp_delete_reqspec' || OLD.id;
 335
 336     IF TG_WHEN = 'AFTER' THEN
 337       RAISE DEBUG 'after trigger on %; deleting from versions', OLD.id;
 338       EXECUTE 'DELETE FROM requirement_spec_versions ' ||
 339               'WHERE id IN (SELECT version_id FROM ' || tname || ')';
 340
 341       RAISE DEBUG '  dropping table';
 342       EXECUTE 'DROP TABLE ' || tname;
 343
 344       RETURN OLD;
 345     END IF;
 346
 347     RAISE DEBUG 'before delete trigger on %', OLD.id;
 348
 349     EXECUTE 'CREATE TEMPORARY TABLE ' || tname || ' AS ' ||
 350       'SELECT DISTINCT version_id '     ||
 351       'FROM requirement_specs '         ||
 352       'WHERE (version_id IS NOT NULL) ' ||
 353       '  AND ((id = ' || OLD.id || ') OR (working_copy_id = ' || OLD.id || '))';
 354
 355     RAISE DEBUG '  Updating version_id and items for %', OLD.id;
 356     UPDATE requirement_specs      SET version_id = NULL                        WHERE (id <> OLD.id) AND (working_copy_id = OLD.id);
 357     UPDATE requirement_spec_items SET item_type  = 'section', parent_id = NULL WHERE requirement_spec_id = OLD.id;
 358
 359     RAISE DEBUG '  Deleting stuff for %', OLD.id;
 360
 361     DELETE FROM requirement_spec_text_blocks WHERE (requirement_spec_id = OLD.id);
 362     DELETE FROM requirement_spec_items       WHERE (requirement_spec_id = OLD.id);
 363     DELETE FROM requirement_specs            WHERE (working_copy_id     = OLD.id);
 364
 365     RAISE DEBUG '  And we out for %', OLD.id;
 366
 367     RETURN OLD;
 368   END;
 369 $$ LANGUAGE plpgsql;
 370
 371 DROP TRIGGER IF EXISTS delete_requirement_spec_dependencies ON requirement_specs;
 372 CREATE TRIGGER delete_requirement_spec_dependencies
 373 BEFORE DELETE ON requirement_specs
 374 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();
 375
 376 DROP TRIGGER IF EXISTS after_delete_requirement_spec_dependencies ON requirement_specs;
 377 CREATE TRIGGER after_delete_requirement_spec_dependencies
 378 AFTER DELETE ON requirement_specs
 379 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();
 380
 381
 382 -- Add formats for section/function block numbers to defaults
 383 ALTER TABLE defaults ADD   COLUMN requirement_spec_section_number_format        TEXT;
 384 ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET DEFAULT 'A00';
 385 ALTER TABLE defaults ADD   COLUMN requirement_spec_function_block_number_format TEXT;
 386 ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET DEFAULT 'FB000';
 387
 388 UPDATE defaults SET requirement_spec_section_number_format        = 'A00';
 389 UPDATE defaults SET requirement_spec_function_block_number_format = 'FB000';
 390
 391 ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET NOT NULL;
 392 ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET NOT NULL;