1 -- @tag: requirement_spec_pictures
2 -- @description: Pflichtenhefte: Support für Bilder
3 -- @depends: requirement_specs
5 CREATE TABLE requirement_spec_pictures (
7 requirement_spec_id INTEGER NOT NULL,
8 text_block_id INTEGER NOT NULL,
9 position INTEGER NOT NULL,
12 picture_file_name TEXT NOT NULL,
13 picture_content_type TEXT NOT NULL,
14 picture_mtime TIMESTAMP NOT NULL DEFAULT now(),
15 picture_content BYTEA NOT NULL,
16 picture_width INTEGER NOT NULL,
17 picture_height INTEGER NOT NULL,
18 thumbnail_content_type TEXT NOT NULL,
19 thumbnail_content BYTEA NOT NULL,
20 thumbnail_width INTEGER NOT NULL,
21 thumbnail_height INTEGER NOT NULL,
22 itime TIMESTAMP NOT NULL DEFAULT now(),
26 FOREIGN KEY (requirement_spec_id) REFERENCES requirement_specs (id) ON DELETE CASCADE,
27 FOREIGN KEY (text_block_id) REFERENCES requirement_spec_text_blocks (id) ON DELETE CASCADE
30 CREATE TRIGGER mtime_requirement_spec_pictures BEFORE UPDATE ON requirement_spec_pictures FOR EACH ROW EXECUTE PROCEDURE set_mtime();
32 ALTER TABLE requirement_specs ADD COLUMN previous_picture_number INTEGER;
33 UPDATE requirement_specs SET previous_picture_number = 0;
34 ALTER TABLE requirement_specs ALTER COLUMN previous_picture_number SET NOT NULL;
35 ALTER TABLE requirement_specs ALTER COLUMN previous_picture_number SET DEFAULT 0;