X-Git-Url: http://wagnertech.de/git?p=kivitendo-erp.git;a=blobdiff_plain;f=sql%2FPg-upgrade2%2Ftime_recordings.sql;fp=sql%2FPg-upgrade2%2Ftime_recordings.sql;h=ccbf329cc567e84853ecff2ddf3d48a2947dc2c2;hp=0000000000000000000000000000000000000000;hb=53593baa211863fbf66540cf1bcc36c8fb37257f;hpb=deb4d2dbb676d7d6f69dfe7815d6e0cb09bd4a44

diff --git a/sql/Pg-upgrade2/time_recordings.sql b/sql/Pg-upgrade2/time_recordings.sql
new file mode 100644
index 000000000..ccbf329cc
--- /dev/null
+++ b/sql/Pg-upgrade2/time_recordings.sql
@@ -0,0 +1,35 @@
+-- @tag: time_recordings
+-- @description: Tabellen zur Zeiterfassung
+-- @depends: release_3_5_6_1
+
+CREATE TABLE time_recording_types (
+  id                 SERIAL,
+  abbreviation       TEXT     NOT NULL,
+  description        TEXT,
+  position           INTEGER  NOT NULL,
+  obsolete           BOOLEAN  NOT NULL DEFAULT false,
+  PRIMARY KEY (id)
+);
+
+CREATE TABLE time_recordings (
+  id                SERIAL,
+  customer_id       INTEGER   NOT NULL,
+  project_id        INTEGER,
+  start_time        TIMESTAMP NOT NULL,
+  end_time          TIMESTAMP,
+  type_id           INTEGER,
+  description       TEXT      NOT NULL,
+  staff_member_id   INTEGER   NOT NULL,
+  employee_id       INTEGER   NOT NULL,
+  itime             TIMESTAMP NOT NULL DEFAULT now(),
+  mtime             TIMESTAMP NOT NULL DEFAULT now(),
+
+  PRIMARY KEY (id),
+  FOREIGN KEY (customer_id)     REFERENCES customer (id),
+  FOREIGN KEY (staff_member_id) REFERENCES employee (id),
+  FOREIGN KEY (employee_id)     REFERENCES employee (id),
+  FOREIGN KEY (project_id)      REFERENCES project (id),
+  FOREIGN KEY (type_id)         REFERENCES time_recording_types (id)
+);
+
+CREATE TRIGGER mtime_time_recordings BEFORE UPDATE ON time_recordings FOR EACH ROW EXECUTE PROCEDURE set_mtime();