From e1c06206b38d5c6fdc603073c239c95e05d978d2 Mon Sep 17 00:00:00 2001
From: Louie S <louie@example.com>
Date: Sat, 16 Sep 2023 09:36:14 -0400
Subject: CreateTables function implemented

---
 assignment-list.py |  6 ++++++
 db_sqlite.py       | 44 ++++++++++++++++++++++++++++++++++++++++++++
 2 files changed, 50 insertions(+)
 create mode 100644 db_sqlite.py

diff --git a/assignment-list.py b/assignment-list.py
index b336098..d5d436e 100755
--- a/assignment-list.py
+++ b/assignment-list.py
@@ -8,6 +8,7 @@ from add_group_form import addGroupForm
 from edit_group_form import editGroupForm
 from add_entry_form import addEntryForm
 Globals = __import__("globals")
+DB = __import__("db_sqlite")
 
 class AssignmentList(QMainWindow):
     def __init__(self):
@@ -19,6 +20,7 @@ class AssignmentList(QMainWindow):
         self.setWindowTitle("Assignment List")
         self.createMenu()
         self.createToolbar()
+        self.setupDB()
         self.displayWidgets()
         self.show()
 
@@ -47,6 +49,10 @@ class AssignmentList(QMainWindow):
 
         tool_bar.addAction(self.add_group_act)
 
+    def setupDB(self):
+        # TODO should check for existing db instead of blindly creating it
+        DB.CreateTables("/tmp/test.db")
+
     def displayWidgets(self):
         main_widget = QWidget(self)
         self.setCentralWidget(main_widget)
diff --git a/db_sqlite.py b/db_sqlite.py
new file mode 100644
index 0000000..9d47da8
--- /dev/null
+++ b/db_sqlite.py
@@ -0,0 +1,44 @@
+import sys
+from PyQt5.QtSql import QSqlDatabase, QSqlQuery
+Globals = __import__("globals")
+
+def CreateTables(db_path):
+    """
+    Create database at a specified directory
+    """
+    database = QSqlDatabase.addDatabase("QSQLITE") # SQlite version 3
+    database.setDatabaseName(db_path)
+
+    if not database.open():
+        print("Unable to open data source file.")
+        sys.exit(1) # Error out. TODO consider throwing a dialog instead
+
+    query = QSqlQuery()
+    # Erase database contents so that we don't have duplicates
+    query.exec_("DROP TABLE groups")
+    query.exec_("DROP TABLE entries")
+
+    query.exec_("""
+        CREATE TABLE groups (
+            id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
+            name VARCHAR(255) NOT NULL,
+            column TINYINT(1) DEFAULT FALSE,
+            link VARCHAR(255) NOT NULL,
+            hidden TINYINT(1) DEFAULT FALSE
+        )
+        """)
+
+    query.exec_("""
+        CREATE TABLE entries (
+            id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
+            parent_id REFERENCES groups (id),
+            description VARCHAR(255) NOT NULL,
+            due_date TEXT DEFAULT NULL,
+            alt_due_date VARCHAR(255) DEFAULT NULL,
+            link VARCHAR(255) DEFAULT NULL,
+            done TINYINT(1) DEFAULT FALSE,
+            hidden TINYINT(1) DEFAULT FALSE
+        )
+        """)
+        
+    database.close()
-- 
cgit