clean up database scripts, start readmes.

8 files changed, 111 insertions, 86 deletions

diff --git a/README.md b/README.md
new file mode 100644
index 0000000..6c0b743
--- /dev/null
+++ b/README.md
@@ -0,0 +1,17 @@
+# Water
+
+Water is a simple tracking application for the amount of water you and
+others drink. Complete with simple data visualizations for the daily consumptions,
+plus over the last seven days
+
+## Stack
+
+This project uses the following technologies:
+
+- [Svelte][fe] for the frontend
+- [Gin][api] for the backend
+- [Sqlite3][db] for the database
+
+[fe]: https://svelte.dev/
+[api]: https://gin-gonic.com/
+[db]: https://www.sqlite.org/index.html
\ No newline at end of file
diff --git a/db/README.md b/db/README.md
new file mode 100644
index 0000000..f36e555
--- /dev/null
+++ b/db/README.md
@@ -0,0 +1,3 @@
+# The Database
+
+This document describes how to set up the database for your instance of the water application.
\ No newline at end of file
diff --git a/db/scripts/init.sh b/db/scripts/init.sh
new file mode 100644
index 0000000..e55292b
--- /dev/null
+++ b/db/scripts/init.sh
@@ -0,0 +1,9 @@
+sqlite3 $DB_PATH < ../sql/tables.sql
+
+insert_user() {
+read -p "Enter a username: " username
+read -sp
+    }
+
+echo "Before continuing you must create users. The reset of the schema depends on them"
+
diff --git a/db/scripts/water_init.sql b/db/scripts/water_init.sql
deleted file mode 100644
index 1099d09..0000000
--- a/db/scripts/water_init.sql
+++ /dev/null
@@ -1,86 +0,0 @@
--- user table for users.
-CREATE TABLE IF NOT EXISTS Users (
-    id INTEGER PRIMARY KEY,
-    password TEXT UNIQUE NOT NULL,
-    uuid TEXT UNIQUE NOT NULL,
-    name TEXT UNIQUE NOT NULL
-);
-
--- statistics table for users to log their consumption
-CREATE TABLE IF NOT EXISTS Statistics (
-    id INTEGER PRIMARY KEY,
-    date DATETIME NOT NULL,
-    user_id INT NOT NULL,
-    quantity INT
-);
-
--- preferences table for a user.
-CREATE TABLE IF NOT EXISTS Preferences (
-    id INTEGER PRIMARY KEY,
-    color TEXT NOT NULL DEFAULT "#000000",
-    user_id INT UNIQUE NOT NULL,
-    size_id INT NOT NULL DEFAULT 1,
-    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
-    FOREIGN KEY(size_id) REFERENCES Sizes(id)
-);
-
--- lookup table for sizes.
-CREATE TABLE IF NOT EXISTS Sizes (
-    id INTEGER PRIMARY KEY,
-    size INT NOT NULL,
-    unit TEXT DEFAULT "oz"
-);
-
-CREATE TABLE IF NOT EXISTS APIToken (
-    id INTEGER PRIMARY KEY,
-    token TEXT NOT NULL,
-    user_id INTEGER NOT NULL,
-    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
-);
-
--- create default sizes for sizes lookup table.
-INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); 
-
--- create default preferences.
-INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2);
-
-CREATE TRIGGER IF NOT EXISTS enforce_size_id
-BEFORE INSERT ON Preferences
-BEGIN
-    SELECT
-        CASE
-            WHEN (
-                SELECT COUNT(*) FROM Sizes WHERE id = new.size_id
-            ) = 0
-            THEN RAISE(ABORT, 'Size does not exist')
-        END;
-END;
-
--- 
-CREATE VIEW IF NOT EXISTS aggregated_stats AS
-    SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id;
-
-CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
-SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
-FROM users
-LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
-LEFT JOIN preferences ON users.id = preferences.user_id
-GROUP BY users.name;
-
-
-CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS
-  WITH DateSequence(Dates) AS
-    (
-     SELECT Date(CURRENT_DATE, '-7 day')
-     UNION ALL
-     SELECT Date(Dates, '+1 day')
-     FROM DateSequence
-     WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE)
-    )
-SELECT DateSequence.Dates as 'date',
-  IFNULL(SUM(statistics.quantity), 0) AS 'total'
-FROM DateSequence
-LEFT JOIN statistics
-ON Date(statistics.date) = DateSequence.Dates
-GROUP BY DateSequence.Dates
-ORDER BY DateSequence.Dates;
\ No newline at end of file
diff --git a/db/sql/seed.sql b/db/sql/seed.sql
new file mode 100644
index 0000000..41d6e46
--- /dev/null
+++ b/db/sql/seed.sql
@@ -0,0 +1,5 @@
+-- create default sizes for sizes lookup table.
+INSERT OR IGNORE INTO Sizes (id, size) VALUES (1, 8), (2, 16), (3, 24), (4, 32), (5, 40), (6, 48); 
+
+-- create default preferences.
+INSERT OR IGNORE INTO Preferences (user_id) VALUES (1), (2);
diff --git a/db/sql/tables.sql b/db/sql/tables.sql
new file mode 100644
index 0000000..0fbfef8
--- /dev/null
+++ b/db/sql/tables.sql
@@ -0,0 +1,39 @@
+-- user table for users.
+CREATE TABLE IF NOT EXISTS Users (
+    id INTEGER PRIMARY KEY,
+    password TEXT UNIQUE NOT NULL,
+    uuid TEXT UNIQUE NOT NULL,
+    name TEXT UNIQUE NOT NULL
+);
+
+-- statistics table for users to log their consumption
+CREATE TABLE IF NOT EXISTS Statistics (
+    id INTEGER PRIMARY KEY,
+    date DATETIME NOT NULL,
+    user_id INT NOT NULL,
+    quantity INT
+);
+
+-- preferences table for a user.
+CREATE TABLE IF NOT EXISTS Preferences (
+    id INTEGER PRIMARY KEY,
+    color TEXT NOT NULL DEFAULT "#000000",
+    user_id INT UNIQUE NOT NULL,
+    size_id INT NOT NULL DEFAULT 1,
+    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE,
+    FOREIGN KEY(size_id) REFERENCES Sizes(id)
+);
+
+-- lookup table for sizes.
+CREATE TABLE IF NOT EXISTS Sizes (
+    id INTEGER PRIMARY KEY,
+    size INT NOT NULL,
+    unit TEXT DEFAULT "oz"
+);
+
+CREATE TABLE IF NOT EXISTS APIToken (
+    id INTEGER PRIMARY KEY,
+    token TEXT NOT NULL,
+    user_id INTEGER NOT NULL,
+    FOREIGN KEY(user_id) REFERENCES Users(id) ON DELETE CASCADE
+);
\ No newline at end of file
diff --git a/db/sql/triggers.sql b/db/sql/triggers.sql
new file mode 100644
index 0000000..3827642
--- /dev/null
+++ b/db/sql/triggers.sql
@@ -0,0 +1,11 @@
+CREATE TRIGGER IF NOT EXISTS enforce_size_id
+BEFORE INSERT ON Preferences
+BEGIN
+    SELECT
+        CASE
+            WHEN (
+                SELECT COUNT(*) FROM Sizes WHERE id = new.size_id
+            ) = 0
+            THEN RAISE(ABORT, 'Size does not exist')
+        END;
+END;
diff --git a/db/sql/views.sql b/db/sql/views.sql
new file mode 100644
index 0000000..c56286d
--- /dev/null
+++ b/db/sql/views.sql
@@ -0,0 +1,27 @@
+CREATE VIEW IF NOT EXISTS aggregated_stats AS
+    SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id;
+
+CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS
+SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color
+FROM users
+LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day')
+LEFT JOIN preferences ON users.id = preferences.user_id
+GROUP BY users.name;
+
+
+CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS
+  WITH DateSequence(Dates) AS
+    (
+     SELECT Date(CURRENT_DATE, '-7 day')
+     UNION ALL
+     SELECT Date(Dates, '+1 day')
+     FROM DateSequence
+     WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE)
+    )
+SELECT DateSequence.Dates as 'date',
+  IFNULL(SUM(statistics.quantity), 0) AS 'total'
+FROM DateSequence
+LEFT JOIN statistics
+ON Date(statistics.date) = DateSequence.Dates
+GROUP BY DateSequence.Dates
+ORDER BY DateSequence.Dates;
\ No newline at end of file