diff options
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | database.py | 97 | ||||
| -rw-r--r-- | smarker.conf | 4 | ||||
| -rw-r--r-- | smarker.conf.example | 17 |
4 files changed, 117 insertions, 2 deletions
@@ -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 |