_make_record(_item): Zahlenattribute nicht mit leeren Strings setzen
[kivitendo-erp.git] / 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;