3 files changed, 31 insertions, 0 deletions

diff --git a/server/migrations/1_init_down.sql b/server/migrations/1_init_down.sql
new file mode 100644
index 0000000..795e617
--- /dev/null
+++ b/server/migrations/1_init_down.sql
@@ -0,0 +1 @@
+DROP TABLE migration;
diff --git a/server/migrations/1_init_up.sql b/server/migrations/1_init_up.sql
new file mode 100644
index 0000000..7edb398
--- /dev/null
+++ b/server/migrations/1_init_up.sql
@@ -0,0 +1,7 @@
+BEGIN;
+
+CREATE TABLE migration (version);
+
+INSERT INTO migration VALUES (1);
+
+COMMIT;
diff --git a/server/migrations/2_kaas_up.sql b/server/migrations/2_kaas_up.sql
new file mode 100644
index 0000000..0c7837a
--- /dev/null
+++ b/server/migrations/2_kaas_up.sql
@@ -0,0 +1,23 @@
+BEGIN;
+
+-- TODO enable strict mode so that primary key is automatically enforced to not be NULL
+CREATE TABLE received_route (
+       id INTEGER PRIMARY KEY,
+
+       received_route_source route_source NOT NULL,
+       recieved_route_strava_id TEXT,
+
+       -- ON DELETE what?
+       FOREIGN KEY (received_route_source_id) REFERENCES received_route_source (id),
+       CHECK (received_route_source IN ('gpx_upload', 'strava', 'rwgps')),
+       CHECK ((received_route_source = 'strava') = (received_route_strava_id IS NULL))
+);
+
+CREATE TABLE received_route_linestring (
+       received_route_id INT NOT NULL,
+       linestring BLOB NOT NULL,
+
+       FOREIGN KEY (received_route_id) REFERENCES received_route (id)
+);
+
+COMMIT;