store database in user spaceHEADmaster

5 files changed, 99 insertions, 1 deletions

diff --git a/data_model/data.db b/data_model/data.db
deleted file mode 100644
index b3b0221..0000000
--- a/data_model/data.db
+++ /dev/null
diff --git a/data_model/db_update.gd b/data_model/db_update.gd
new file mode 100644
index 0000000..595f1bc
--- /dev/null
+++ b/data_model/db_update.gd
@@ -0,0 +1,23 @@
+class_name db_update
+
+static func update(DBMS):
+    while true:
+        var version = get_version(DBMS)
+        match version:
+            [0,1,0]: break
+            _: exec_sql_file(DBMS, "res://data_model/schema.sql")
+    #insert_test_data(DBMS)
+
+static func get_version(DBMS):
+    var version = DBMS.query("select * from Version", [])
+    return [0,0,0] if version.size() == 0 else [version[0]['major'],version[0]['minor'],version[0]['patch']]
+
+static func exec_sql_file(DBMS, file):
+    var f = File.new()
+    f.open(file, File.READ)
+    var sql = f.get_as_text()
+    f.close()
+    DBMS.query(sql,[])
+
+static func insert_test_data(DBMS):
+    exec_sql_file(DBMS, "res://data_model/test_data.sql")
diff --git a/data_model/dbms.gd b/data_model/dbms.gd
index 43c514d..0508887 100644
--- a/data_model/dbms.gd
+++ b/data_model/dbms.gd
@@ -2,7 +2,7 @@
 extends Node
 
 const sqlite = preload("res://addons/godot-sqlite/bin/gdsqlite.gdns")
-var db_name = "res://data_model/data.db"
+var db_name = "user://data.db"
 var db
 
 func _init():
@@ -10,6 +10,7 @@ func _init():
     db.path = db_name
     #db.verbosity_level = 3
     db.open_db()
+    db_update.update(self)
 
 func query(sql, args):
     db.query_with_bindings(sql,args)
diff --git a/data_model/schema.sql b/data_model/schema.sql
new file mode 100644
index 0000000..c4a554d
--- /dev/null
+++ b/data_model/schema.sql
@@ -0,0 +1,54 @@
+BEGIN TRANSACTION;
+DROP TABLE IF EXISTS "Schedules";
+CREATE TABLE IF NOT EXISTS "Schedules" (
+	"id"	INTEGER,
+	"name"	TEXT NOT NULL,
+	PRIMARY KEY("id")
+);
+DROP TABLE IF EXISTS "Tasks";
+CREATE TABLE IF NOT EXISTS "Tasks" (
+	"id"	INTEGER,
+	"name"	TEXT NOT NULL,
+	"description"	TEXT,
+	"frequency"	INTEGER NOT NULL,
+	"estimated_length"	INTEGER,
+	"date_completed"	INTEGER,
+	PRIMARY KEY("id")
+);
+DROP TABLE IF EXISTS "TimeSlotTasks";
+CREATE TABLE IF NOT EXISTS "TimeSlotTasks" (
+	"timeslot_id"	INTEGER NOT NULL,
+	"task_id"	INTEGER NOT NULL,
+	PRIMARY KEY("timeslot_id","task_id"),
+	FOREIGN KEY("task_id") REFERENCES "Tasks"("id") ON UPDATE CASCADE ON DELETE CASCADE,
+	FOREIGN KEY("timeslot_id") REFERENCES "TimeSlots"("id") ON UPDATE CASCADE ON DELETE CASCADE
+);
+DROP TABLE IF EXISTS "SubTasks";
+CREATE TABLE IF NOT EXISTS "SubTasks" (
+	"id"	INTEGER,
+	"task_id"	INTEGER NOT NULL,
+	"name"	TEXT NOT NULL,
+	"date_completed"	INTEGER,
+	PRIMARY KEY("id"),
+	FOREIGN KEY("task_id") REFERENCES "Tasks"("id") ON UPDATE CASCADE ON DELETE CASCADE
+);
+DROP TABLE IF EXISTS "TimeSlots";
+CREATE TABLE IF NOT EXISTS "TimeSlots" (
+	"id"	INTEGER,
+	"schedule_id"	INTEGER NOT NULL,
+	"start_time"	INTEGER NOT NULL,
+	"end_time"	INTEGER NOT NULL,
+	"name"	TEXT NOT NULL,
+	"date_completed"	INTEGER,
+	PRIMARY KEY("id"),
+	FOREIGN KEY("schedule_id") REFERENCES "Schedules"("id") ON UPDATE CASCADE ON DELETE CASCADE
+);
+DROP TABLE IF EXISTS "Version";
+CREATE TABLE IF NOT EXISTS "Version" (
+	"major"	INTEGER NOT NULL,
+	"minor"	INTEGER NOT NULL,
+	"patch"	INTEGER NOT NULL
+);
+INSERT INTO "Schedules" VALUES (1,'default');
+INSERT INTO "Version" VALUES (0,1,0);
+COMMIT;
diff --git a/data_model/test_data.sql b/data_model/test_data.sql
new file mode 100644
index 0000000..78ece96
--- /dev/null
+++ b/data_model/test_data.sql
@@ -0,0 +1,20 @@
+BEGIN TRANSACTION;
+INSERT INTO "Tasks" VALUES (1,'Trash','Take out the trash.',2,5,NULL),
+ (2,'Laundry','Do the Laundry',4,120,NULL),
+ (3,'Pre-Workout Stretch',NULL,1,10,NULL);
+INSERT INTO "TimeSlotTasks" VALUES (1,1),
+ (1,2),
+ (2,3);
+INSERT INTO "SubTasks" VALUES (1,3,'arm circles',NULL),
+ (2,3,'heel raises into toe raises',NULL),
+ (3,3,'open hips',NULL),
+ (4,3,'open hips (side)',NULL),
+ (5,3,'karaoke',NULL),
+ (6,3,'knee to chest',NULL),
+ (7,3,'quad pull',NULL),
+ (8,3,'frankenstein',NULL),
+ (9,3,'lunge walk',NULL);
+INSERT INTO "TimeSlots" VALUES (1,1,86400,88200,'Chore Block',NULL),
+ (2,1,45000,48600,'Workout',NULL),
+ (3,1,14000,20000,'test',NULL);
+COMMIT;