# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1309947825 0
# Node ID 5f8444df19e47535d30c11bd3801d4ad8069f6ad
# Parent  1557cccb88f6e89a2277a753e5c2769371079634
Add relations for cross sections.

flys-backend/trunk@2287 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 1557cccb88f6 -r 5f8444df19e4 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Tue Jun 28 07:57:43 2011 +0000
+++ b/flys-backend/ChangeLog	Wed Jul 06 10:23:45 2011 +0000
@@ -1,3 +1,38 @@
+2011-07-06	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* doc/schema/postgresql.sql: Add relations for cross sections.
+	  To update existing databases:
+	     BEGIN;
+	     	
+	     CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
+	     CREATE TABLE cross_sections (
+	         id               int PRIMARY KEY NOT NULL,
+	         km               NUMERIC         NOT NULL,
+	         river_id         int             NOT NULL REFERENCES rivers(id),
+	         time_interval_id int             REFERENCES time_intervals(id),
+	         UNIQUE (km, river_id)
+	     );
+	     
+	     CREATE SEQUENCE POINTS3D_ID_SEQ;
+	     CREATE TABLE points3d (
+	         id int     PRIMARY KEY NOT NULL,
+	         x  NUMERIC NOT NULL,
+	         y  NUMERIC NOT NULL,
+	         z  NUMERIC NOT NULL
+	     );
+	     
+	     CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
+	     CREATE TABLE cross_section_points (
+	         id               int PRIMARY KEY NOT NULL,
+	         cross_section_id int NOT NULL REFERENCES cross_sections(id),
+	         point3d_id       int NOT NULL REFERENCES points3d(id),
+	         col_pos          int NOT NULL,
+	         UNIQUE (cross_section_id, point3d_id, col_pos),
+	         UNIQUE (cross_section_id, col_pos)
+	     );
+	     
+	     COMMIT;
+
 2011-06-28  Ingo Weinzierl <ingo@intevation.de>
 
 	Tagged RELEASE 2.4
diff -r 1557cccb88f6 -r 5f8444df19e4 flys-backend/doc/schema/postgresql.sql
--- a/flys-backend/doc/schema/postgresql.sql	Tue Jun 28 07:57:43 2011 +0000
+++ b/flys-backend/doc/schema/postgresql.sql	Wed Jul 06 10:23:45 2011 +0000
@@ -260,4 +260,38 @@
     JOIN wst_columns wc   ON wcqr.wst_column_id  = wc.id
     ORDER BY wc.position, wcqr.wst_column_id, r.a;
 
+-- data for the cross-sections
+
+CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
+
+CREATE TABLE cross_sections (
+    id               int PRIMARY KEY NOT NULL,
+    km               NUMERIC         NOT NULL,
+    river_id         int             NOT NULL REFERENCES rivers(id),
+    time_interval_id int             REFERENCES time_intervals(id),
+    UNIQUE (km, river_id)            -- XXX: Maybe too hard?
+);
+
+-- TODO: Use a geometry column in cross_section_points
+
+CREATE SEQUENCE POINTS3D_ID_SEQ;
+
+CREATE TABLE points3d (
+    id int     PRIMARY KEY NOT NULL,
+    x  NUMERIC NOT NULL,
+    y  NUMERIC NOT NULL,
+    z  NUMERIC NOT NULL
+);
+
+CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
+
+CREATE TABLE cross_section_points (
+    id               int PRIMARY KEY NOT NULL,
+    cross_section_id int NOT NULL REFERENCES cross_sections(id),
+    point3d_id       int NOT NULL REFERENCES points3d(id),
+    col_pos          int NOT NULL,
+    UNIQUE (cross_section_id, point3d_id, col_pos),
+    UNIQUE (cross_section_id, col_pos)
+);
+
 COMMIT;