- added database schema

git-svn-id: https://svn.spreadspace.org/mur.sat@98 7de4ea59-55d0-425e-a1af-a3118ea81d4c

1 files changed, 51 insertions, 0 deletions

diff --git a/tools/mmd/create b/tools/mmd/create
new file mode 100644
index 0000000..8f0b3b7
--- /dev/null
+++ b/tools/mmd/create
@@ -0,0 +1,51 @@
+--
+-- create MMD teables
+--
+
+DROP TABLE IF EXISTS session;
+
+CREATE TABLE session (
+    id TEXT PRIMARY KEY NOT NULL,
+    email TEXT NOT NULL,
+    status TEXT,
+    expires INTEGER NOT NULL,
+    renewal INTEGER NOT NULL
+);
+
+DROP TABLE IF EXISTS user;
+
+CREATE TABLE user (
+    id TEXT PRIMARY KEY NOT NULL,
+    firstname TEXT NOT NULL,
+    lastname TEXT NOT NULL,
+    callsign TEXT,
+    email TEXT NOT NULL UNIQUE,
+    password TEXT NOT NULL,
+    code TEXT,
+    regtimeout INTEGER,
+    status TEXT DEFAULT 'pending'
+);
+
+DROP TABLE IF EXISTS location;
+
+CREATE TABLE location (
+    id TEXT PRIMARY KEY NOT NULL,
+    longitude REAL,
+    latitude REAL,
+    name TEXT,
+    is_default TEXT,
+    user_id TEXT,
+    FOREIGN KEY (user_id) REFERENCES user (id)
+);
+DROP TABLE IF EXISTS report;
+
+CREATE TABLE report (
+    id TEXT PRIMARY KEY NOT NULL,
+    timestamp INTEGER,
+    raw TEXT,
+    audio TEXT,
+    location_id TEXT,
+    FOREIGN KEY (location_id) REFERENCES location (id)
+);
+
+