kivitendo/sql/Pg-upgrade2/follow_ups.sql @ 7fede255
7a7f33b5 | Moritz Bunkus | -- @tag: follow_ups
|
|
-- @description: Tabellenstruktur für Wiedervorlagen und allgemeine Notizen
|
|||
-- @depends: release_2_4_3
|
|||
CREATE SEQUENCE note_id;
|
|||
CREATE TABLE notes (
|
|||
id integer NOT NULL DEFAULT nextval('note_id'),
|
|||
subject text,
|
|||
body text,
|
|||
created_by integer NOT NULL,
|
|||
trans_id integer,
|
|||
trans_module varchar(10),
|
|||
itime timestamp DEFAULT now(),
|
|||
mtime timestamp,
|
|||
PRIMARY KEY (id),
|
|||
FOREIGN KEY (created_by) REFERENCES employee (id)
|
|||
);
|
|||
CREATE TRIGGER mtime_notes
|
|||
BEFORE UPDATE ON notes
|
|||
FOR EACH ROW
|
|||
EXECUTE PROCEDURE set_mtime();
|
|||
CREATE SEQUENCE follow_up_id;
|
|||
CREATE TABLE follow_ups (
|
|||
id integer NOT NULL DEFAULT nextval('follow_up_id'),
|
|||
follow_up_date date NOT NULL,
|
|||
created_for_user integer NOT NULL,
|
|||
done boolean DEFAULT FALSE,
|
|||
note_id integer NOT NULL,
|
|||
created_by integer NOT NULL,
|
|||
itime timestamp DEFAULT now(),
|
|||
mtime timestamp,
|
|||
PRIMARY KEY (id),
|
|||
FOREIGN KEY (created_for_user) REFERENCES employee (id),
|
|||
FOREIGN KEY (created_by) REFERENCES employee (id),
|
|||
FOREIGN KEY (note_id) REFERENCES notes (id)
|
|||
);
|
|||
CREATE TRIGGER mtime_follow_ups
|
|||
BEFORE UPDATE ON follow_ups
|
|||
FOR EACH ROW
|
|||
EXECUTE PROCEDURE set_mtime();
|
|||
CREATE SEQUENCE follow_up_link_id;
|
|||
CREATE TABLE follow_up_links (
|
|||
id integer NOT NULL DEFAULT nextval('follow_up_link_id'),
|
|||
follow_up_id integer NOT NULL,
|
|||
trans_id integer NOT NULL,
|
|||
trans_type text NOT NULL,
|
|||
trans_info text,
|
|||
itime timestamp DEFAULT now(),
|
|||
mtime timestamp,
|
|||
PRIMARY KEY (id),
|
|||
FOREIGN KEY (follow_up_id) REFERENCES follow_ups (id)
|
|||
);
|
|||
CREATE TRIGGER mtime_follow_up_links
|
|||
BEFORE UPDATE ON follow_up_links
|
|||
FOR EACH ROW
|
|||
EXECUTE PROCEDURE set_mtime();
|
|||
CREATE TABLE follow_up_access (
|
|||
who integer NOT NULL,
|
|||
what integer NOT NULL,
|
|||
FOREIGN KEY (who) REFERENCES employee (id),
|
|||
FOREIGN KEY (what) REFERENCES employee (id)
|
|||
);
|