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 CREATE TABLE trigger_information (
 251        id    SERIAL PRIMARY KEY,
 252        key   TEXT   NOT NULL,
 253        value TEXT,
 254
 255        UNIQUE (key, value)
 256 );
 257
 258 -- Trigger for updating time_estimation of function blocks from their
 259 -- children (not for sections, not for sub function blocks).
 260 CREATE OR REPLACE FUNCTION update_requirement_spec_item_time_estimation(item_id INTEGER) RETURNS BOOLEAN AS $$
 261   DECLARE
 262     item RECORD;
 263   BEGIN
 264     IF item_id IS NULL THEN
 265       RAISE DEBUG 'updateRSIE: item_id IS NULL';
 266       RETURN FALSE;
 267     END IF;
 268
 269     IF EXISTS(
 270       SELECT *
 271       FROM trigger_information
 272       WHERE (key   = 'deleting_requirement_spec_item')
 273         AND (value = CAST(item_id AS TEXT))
 274       LIMIT 1
 275     ) THEN
 276       RAISE DEBUG 'updateRSIE: item_id % is about to be deleted; do not update', item_id;
 277       RETURN FALSE;
 278     END IF;
 279
 280     SELECT * INTO item FROM requirement_spec_items WHERE id = item_id;
 281     RAISE DEBUG 'updateRSIE: item_id % item_type %', item_id, item.item_type;
 282
 283     IF (item.item_type = 'section') OR (item.item_type = 'sub-function-block') THEN
 284       -- Don't do anything for sections and sub-function-blocks.
 285       RAISE DEBUG 'updateRSIE: this is a section/sub-function-block, not updating.';
 286       RETURN FALSE;
 287     END IF;
 288
 289     RAISE DEBUG 'updateRSIE: will do stuff now';
 290
 291     UPDATE requirement_spec_items
 292       SET time_estimation = COALESCE((
 293         SELECT SUM(time_estimation)
 294         FROM requirement_spec_items
 295         WHERE parent_id = item_id
 296       ), 0)
 297       WHERE id = item_id;
 298
 299     RETURN TRUE;
 300   END;
 301 $$ LANGUAGE plpgsql;
 302
 303 CREATE OR REPLACE FUNCTION requirement_spec_item_time_estimation_updater_trigger() RETURNS trigger AS $$
 304   DECLARE
 305     do_new BOOLEAN;
 306   BEGIN
 307     RAISE DEBUG 'updateRSITE op %', TG_OP;
 308     IF ((TG_OP = 'UPDATE') OR (TG_OP = 'DELETE')) THEN
 309       RAISE DEBUG 'UPDATE trigg op % OLD.id % OLD.parent_id %', TG_OP, OLD.id, OLD.parent_id;
 310       PERFORM update_requirement_spec_item_time_estimation(OLD.parent_id);
 311       RAISE DEBUG 'UPDATE trigg op % END %', TG_OP, OLD.id;
 312     END IF;
 313     do_new = FALSE;
 314
 315     IF (TG_OP = 'UPDATE') THEN
 316       do_new = OLD.parent_id <> NEW.parent_id;
 317     END IF;
 318
 319     IF (do_new OR (TG_OP = 'INSERT')) THEN
 320       RAISE DEBUG 'UPDATE trigg op % NEW.id % NEW.parent_id %', TG_OP, NEW.id, NEW.parent_id;
 321       PERFORM update_requirement_spec_item_time_estimation(NEW.parent_id);
 322       RAISE DEBUG 'UPDATE trigg op % END %', TG_OP, NEW.id;
 323     END IF;
 324
 325     RETURN NULL;
 326   END;
 327 $$ LANGUAGE plpgsql;
 328
 329 DROP TRIGGER IF EXISTS update_requirement_spec_item_time_estimation ON requirement_spec_items;
 330 CREATE TRIGGER update_requirement_spec_item_time_estimation
 331 AFTER INSERT OR UPDATE OR DELETE ON requirement_spec_items
 332 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_time_estimation_updater_trigger();
 333
 334
 335 -- Trigger for deleting depending stuff if a requirement spec item is deleted.
 336 CREATE OR REPLACE FUNCTION requirement_spec_item_before_delete_trigger() RETURNS trigger AS $$
 337   BEGIN
 338     RAISE DEBUG 'delete trig RSitem old id %', OLD.id;
 339     INSERT INTO trigger_information (key, value) VALUES ('deleting_requirement_spec_item', CAST(OLD.id AS TEXT));
 340     DELETE FROM requirement_spec_item_dependencies WHERE (depending_item_id = OLD.id) OR (depended_item_id = OLD.id);
 341     DELETE FROM requirement_spec_items             WHERE (parent_id         = OLD.id);
 342     DELETE FROM trigger_information                WHERE (key = 'deleting_requirement_spec_item') AND (value = CAST(OLD.id AS TEXT));
 343     RAISE DEBUG 'delete trig END %', OLD.id;
 344     RETURN OLD;
 345   END;
 346 $$ LANGUAGE plpgsql;
 347
 348 DROP TRIGGER IF EXISTS delete_requirement_spec_item_dependencies ON requirement_spec_items;
 349 CREATE TRIGGER delete_requirement_spec_item_dependencies
 350 BEFORE DELETE ON requirement_spec_items
 351 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_item_before_delete_trigger();
 352
 353
 354 -- Trigger for deleting depending stuff if a requirement spec is deleted.
 355 CREATE OR REPLACE FUNCTION requirement_spec_delete_trigger() RETURNS trigger AS $$
 356   DECLARE
 357     tname TEXT;
 358   BEGIN
 359     tname := 'tmp_delete_reqspec' || OLD.id;
 360
 361     IF TG_WHEN = 'AFTER' THEN
 362       RAISE DEBUG 'after trigger on %; deleting from versions', OLD.id;
 363       EXECUTE 'DELETE FROM requirement_spec_versions ' ||
 364               'WHERE id IN (SELECT version_id FROM ' || tname || ')';
 365
 366       RAISE DEBUG '  dropping table';
 367       EXECUTE 'DROP TABLE ' || tname;
 368
 369       RETURN OLD;
 370     END IF;
 371
 372     RAISE DEBUG 'before delete trigger on %', OLD.id;
 373
 374     EXECUTE 'CREATE TEMPORARY TABLE ' || tname || ' AS ' ||
 375       'SELECT DISTINCT version_id '     ||
 376       'FROM requirement_specs '         ||
 377       'WHERE (version_id IS NOT NULL) ' ||
 378       '  AND ((id = ' || OLD.id || ') OR (working_copy_id = ' || OLD.id || '))';
 379
 380     RAISE DEBUG '  Updating version_id and items for %', OLD.id;
 381     UPDATE requirement_specs      SET version_id = NULL                        WHERE (id <> OLD.id) AND (working_copy_id = OLD.id);
 382     UPDATE requirement_spec_items SET item_type  = 'section', parent_id = NULL WHERE requirement_spec_id = OLD.id;
 383
 384     RAISE DEBUG '  Deleting stuff for %', OLD.id;
 385
 386     DELETE FROM requirement_spec_text_blocks WHERE (requirement_spec_id = OLD.id);
 387     DELETE FROM requirement_spec_items       WHERE (requirement_spec_id = OLD.id);
 388     DELETE FROM requirement_specs            WHERE (working_copy_id     = OLD.id);
 389
 390     RAISE DEBUG '  And we out for %', OLD.id;
 391
 392     RETURN OLD;
 393   END;
 394 $$ LANGUAGE plpgsql;
 395
 396 DROP TRIGGER IF EXISTS delete_requirement_spec_dependencies ON requirement_specs;
 397 CREATE TRIGGER delete_requirement_spec_dependencies
 398 BEFORE DELETE ON requirement_specs
 399 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();
 400
 401 DROP TRIGGER IF EXISTS after_delete_requirement_spec_dependencies ON requirement_specs;
 402 CREATE TRIGGER after_delete_requirement_spec_dependencies
 403 AFTER DELETE ON requirement_specs
 404 FOR EACH ROW EXECUTE PROCEDURE requirement_spec_delete_trigger();
 405
 406
 407 -- Add formats for section/function block numbers to defaults
 408 ALTER TABLE defaults ADD   COLUMN requirement_spec_section_number_format        TEXT;
 409 ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET DEFAULT 'A00';
 410 ALTER TABLE defaults ADD   COLUMN requirement_spec_function_block_number_format TEXT;
 411 ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET DEFAULT 'FB000';
 412
 413 UPDATE defaults SET requirement_spec_section_number_format        = 'A00';
 414 UPDATE defaults SET requirement_spec_function_block_number_format = 'FB000';
 415
 416 ALTER TABLE defaults ALTER COLUMN requirement_spec_section_number_format        SET NOT NULL;
 417 ALTER TABLE defaults ALTER COLUMN requirement_spec_function_block_number_format SET NOT NULL;