From 9d1668c5e4820213406d18278116aad98fe83331 Mon Sep 17 00:00:00 2001
From: jwansek <eddie.atten.ea29@gmail.com>
Date: Fri, 25 Feb 2022 17:05:49 +0000
Subject: finished database datatype declaration

---
 .gitignore           |  1 +
 database.py          | 97 ++++++++++++++++++++++++++++++++++++++++++++++++++++
 smarker.conf         |  4 +--
 smarker.conf.example | 17 +++++++++
 4 files changed, 117 insertions(+), 2 deletions(-)
 create mode 100644 database.py
 create mode 100644 smarker.conf.example

diff --git a/.gitignore b/.gitignore
index b8bb272..d7b0b3f 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,5 +1,6 @@
 *_report.*
 *.zip
+smarker.conf
 
 # Byte-compiled / optimized / DLL files
 __pycache__/
diff --git a/database.py b/database.py
new file mode 100644
index 0000000..a0e3640
--- /dev/null
+++ b/database.py
@@ -0,0 +1,97 @@
+from dataclasses import dataclass
+import pymysql
+
+@dataclass
+class SmarkerDatabase:
+    host:str 
+    user:str 
+    passwd:str 
+    db:str 
+    port:int = 3306
+    
+    def __enter__(self):
+        try:
+            self.__connection = self.__get_connection()
+        except pymysql.err.OperationalError as e:
+            if e.args[0] == 1049:
+                self.__build_db()
+        return self
+
+    def __exit__(self, type, value, traceback):
+        self.__connection.close()
+
+    def __get_connection(self):
+        return pymysql.connect(
+            host = self.host,
+            port = self.port,
+            user = self.user,
+            passwd = self.passwd,
+            charset = "utf8mb4",
+            database = self.db
+        )
+
+    def __build_db(self):
+        self.__connection = pymysql.connect(
+            host = self.host,
+            port = self.port,
+            user = self.user,
+            passwd = self.passwd,
+            charset = "utf8mb4",
+        )
+        with self.__connection.cursor() as cursor:
+            # unsafe:
+            cursor.execute("CREATE DATABASE %s" % self.db)
+            cursor.execute("USE %s" % self.db)
+            cursor.execute("""
+            CREATE TABLE students(
+                student_no VARCHAR(10) PRIMARY KEY NOT NULL,
+                name TEXT NOT NULL,
+                email VARCHAR(50) NOT NULL
+            );
+            """)
+            cursor.execute("""
+            CREATE TABLE assessment(
+                assessment_name VARCHAR(30) PRIMARY KEY NOT NULL,
+                yaml_path TEXT NOT NULL,
+                num_enrolled INT UNSIGNED NULL
+            );
+            """)
+            cursor.execute("""
+            CREATE TABLE submissions(
+                submission_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+                student_no VARCHAR(10) NOT NULL,
+                assessment_name VARCHAR(30) NOT NULL,
+                submission_dt DATETIME NOT NULL default CURRENT_TIMESTAMP,
+                submission_zip_path TEXT NOT NULL,
+                FOREIGN KEY (student_no) REFERENCES students(student_no),
+                FOREIGN KEY (assessment_name) REFERENCES assessment(assessment_name)
+            );
+            """)
+            cursor.execute("""
+            CREATE TABLE assessment_file(
+                file_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+                assessment_name VARCHAR(30) NOT NULL,
+                file_name VARCHAR(30) NOT NULL,
+                FOREIGN KEY (assessment_name) REFERENCES assessment(assessment_name)
+            );
+            """)
+            cursor.execute("""
+            CREATE TABLE submitted_files(
+                submission_id INT UNSIGNED NOT NULL,
+                file_id INT UNSIGNED NOT NULL,
+                file_text TEXT NOT NULL,
+                FOREIGN KEY (submission_id) REFERENCES submissions(submission_id),
+                FOREIGN KEY (file_id) REFERENCES assessment_file(file_id),
+                PRIMARY KEY(submission_id, file_id)
+            );
+            """)
+                
+
+        self.__connection.commit()
+        return self.__connection
+
+
+    def get_tables(self):
+        with self.__connection.cursor() as cursor:
+            cursor.execute("SHOW TABLES;")
+            return cursor.fetchall()
diff --git a/smarker.conf b/smarker.conf
index e8db25c..62180b5 100644
--- a/smarker.conf
+++ b/smarker.conf
@@ -1,8 +1,8 @@
 [mysql]
 host = 192.168.1.92
 port = 3306
-user = smarker
-passwd = smarkerPassword
+user = root
+passwd = *************
 
 [md]
 show_full_docs = False
diff --git a/smarker.conf.example b/smarker.conf.example
new file mode 100644
index 0000000..e8db25c
--- /dev/null
+++ b/smarker.conf.example
@@ -0,0 +1,17 @@
+[mysql]
+host = 192.168.1.92
+port = 3306
+user = smarker
+passwd = smarkerPassword
+
+[md]
+show_full_docs = False
+show_source = False
+show_all_regex_occurrences = True
+show_all_run_output = False
+
+[txt]
+show_full_docs = False
+show_source = False
+show_all_regex_occurrences = True
+show_all_run_output = False
\ No newline at end of file
-- 
cgit v1.2.3