diff options
Diffstat (limited to 'Models.py')
| -rw-r--r-- | Models.py | 208 |
1 files changed, 138 insertions, 70 deletions
@@ -1,35 +1,51 @@ import sqlite3 import hashlib +import logging +import os class ScorePadDB(): - - def __init__(self, nickname = ""): - - #self.scorepaddb = "/home/cnix/ThesisWorkspace2/ScorePad1.4/src/db/scorepaddb.sqlite" + + def connect_db(self, nickname = ""): + is_newlycreated = False + self.scorepaddb = str(nickname)+"db.sqlite" + if not os.path.isfile(self.scorepaddb): + logging.debug("the database already exist") - self.scorepaddb = "db/"+str(nickname)+"db.sqlite" self.connection = sqlite3.connect(self.scorepaddb) + logging.debug("ScorepadDB-->connect") self.cursor = self.connection.cursor() + try: + self.create_tables() + is_newlycreated = True + logging.debug("ScorepadDB -->create_tables") + except: + logging.debug("Exception here") + return is_newlycreated + def create_tables(self): + rubric_table = "CREATE TABLE rubric_table (rubric_id INTEGER PRIMARY KEY,\ title VARCHAR(40) NOT NULL,\ author VARCHAR(40) NOT NULL,\ description TEXT,\ is_predefined INTEGER,\ xo_name VARCHAR(50),\ - rubric_sha TEXT)" + rubric_sha TEXT,\ + enable_points INTERGER)" category_table = "CREATE TABLE category_table (category_id INTEGER PRIMARY KEY,\ name VARCHAR(40) NOT NULL,\ rubric_id INTEGER NOT NULL REFERENCES rubric_table(rubric_id),\ - category_sha TEXT)" + category_sha TEXT,\ + percentage FLOAT)" level_table = "CREATE TABLE level_table (level_id INTEGER PRIMARY KEY,\ name VARCHAR(40) NOT NULL,\ description TEXT NOT NULL,\ category_id INTEGER NOT NULL REFERENCES category_table(category_id),\ rubric_id INTEGER NOT NULL REFERENCES rubric_table(rubric_id),\ - level_sha TEXT)" + level_sha TEXT,\ + points INTEGER)" project_table = "CREATE TABLE project_table (project_id INTEGER PRIMARY KEY,\ title VARCHAR(40) NOT NULL,\ @@ -41,7 +57,8 @@ class ScorePadDB(): is_shared INTEGER,\ rubric_id INTEGER NOT NULL REFERENCES rubric_table(rubric_id),\ xo_name VARCHAR(50),\ - project_sha TEXT)" + project_sha TEXT,\ + total_score FLOAT)" score_table = "CREATE TABLE score_table (score_id INTEGER PRIMARY KEY,\ project_id INTEGER NOT NULL REFERENCES project_table(project_id),\ @@ -52,33 +69,26 @@ class ScorePadDB(): rubric_sha TEXT REFERENCES rubric_table(rubric_sha),\ category_sha TEXT REFERENCES category_table(category_sha),\ level_sha TEXT REFERENCES level_table(level_sha),\ - count INTEGER NOT NULL)" - - assessor_table = "CREATE TABLE assessor_table (assessor_id INTEGER PRIMARY KEY,\ - name TEXT NOT NULL, \ - score_id INTEGER NOT NULL REFERENCES score_table(score_id))" + score_count INTEGER NOT NULL)" self.cursor.execute(rubric_table) - print "rubric table created" + logging.debug("rubric table created") self.cursor.execute(category_table) - print "category table created" + logging.debug("category table created") self.cursor.execute(level_table) - print "level table created" + logging.debug("level table created") self.cursor.execute(project_table) - print "project table created" + logging.debug("project table created") self.cursor.execute(score_table) - print "score table created" - self.cursor.execute(assessor_table) - print "assessor table created" - self.cursor.close() - + logging.debug("score table created") + def insert_rubric(self, rubric): temp = (rubric.title, rubric.author, rubric.description, rubric.is_predefined, - rubric.xo_name, rubric.rubric_sha) + rubric.xo_name, rubric.rubric_sha, rubric.enable_points) insert_str = "INSERT INTO rubric_table(title, author, description,is_predefined,\ - xo_name, rubric_sha)\ - VALUES(?,?,?,?,?,?)" + xo_name, rubric_sha, enable_points)\ + VALUES(?,?,?,?,?,?,?)" self.cursor.execute(insert_str,temp) self.connection.commit() @@ -89,8 +99,8 @@ class ScorePadDB(): return rubric_id def insert_criteria(self, category, levels): - temp = (category.name, category.rubric_id, category.category_sha) - insert_str = "INSERT INTO category_table(name,rubric_id,category_sha) VALUES (?,?,?)" + temp = (category.name, category.rubric_id, category.category_sha, category.percentage) + insert_str = "INSERT INTO category_table(name,rubric_id,category_sha,percentage) VALUES (?,?,?,?)" self.cursor.execute(insert_str, temp) self.connection.commit() @@ -98,11 +108,11 @@ class ScorePadDB(): self.cursor.execute(query_str) category_id = self.cursor.fetchone()[0] - insert_str = "INSERT INTO level_table(name,description,category_id,rubric_id,level_sha)\ - VALUES(?,?,?,?,?)" + insert_str = "INSERT INTO level_table(name,description,category_id,rubric_id,level_sha,points)\ + VALUES(?,?,?,?,?,?)" for i in range(len(levels)): temp = (levels[i].name, levels[i].description,category_id ,\ - levels[i].rubric_id, levels[i].level_sha) + levels[i].rubric_id,levels[i].level_sha,levels[i].points) self.cursor.execute(insert_str, temp) self.connection.commit() @@ -110,11 +120,11 @@ class ScorePadDB(): temp = (project.title, project.author, project.description, project.subject,\ project.publish_date, project.is_owned,\ project.is_shared, project.rubric_id,project.xo_name,\ - project.project_sha) + project.project_sha, project.total_score) insert_str = "INSERT INTO project_table(title, author, description, subject,\ publish_date, is_owned, is_shared, rubric_id,xo_name,\ - project_sha) VALUES (?,?,?,?,?,?,?,?,?,?)" + project_sha,total_score) VALUES (?,?,?,?,?,?,?,?,?,?,?)" self.cursor.execute(insert_str,temp) self.connection.commit() @@ -127,23 +137,23 @@ class ScorePadDB(): def insert_score(self, score): temp = (score.project_id, score.rubric_id, score.category_id, score.level_id, - score.project_sha, score.rubric_sha, score.category_sha, score.level_sha, score.count) + score.project_sha, score.rubric_sha, score.category_sha, score.level_sha, score.score_count) insert_str = "INSERT INTO score_table(project_id,rubric_id,category_id,level_id,\ - project_sha, rubric_sha, category_sha, level_sha, count)\ + project_sha, rubric_sha, category_sha, level_sha, score_count)\ VALUES (?,?,?,?,?,?,?,?,?)" self.cursor.execute(insert_str,temp) self.connection.commit() def insert_category(self, category): - temp = (category.name, category.rubric_id, category.category_sha) - insert_str = "INSERT INTO category_table(name,rubric_id,category_sha) VALUES (?,?,?)" + temp = (category.name, category.rubric_id, category.category_sha, category.percentage) + insert_str = "INSERT INTO category_table(name,rubric_id,category_sha,percentage) VALUES (?,?,?,?)" self.cursor.execute(insert_str, temp) self.connection.commit() def insert_level(self, level): - temp = (level.name, level.description, level.category_id, level.rubric_id, level.level_sha) - insert_str = "INSERT INTO level_table(name, description, category_id, rubric_id, level_sha) \ - VALUES(?,?,?,?,?)" + temp = (level.name, level.description, level.category_id, level.rubric_id, level.level_sha,level.points) + insert_str = "INSERT INTO level_table(name, description, category_id, rubric_id,level_sha,points) \ + VALUES(?,?,?,?,?,?)" self.cursor.execute(insert_str, temp) self.connection.commit() @@ -166,7 +176,7 @@ class ScorePadDB(): rubric_list = [] for row in self.cursor: - rubric = Rubric(row[0], row[1], row[2], row[3], row[4], row[5], row[6]) + rubric = Rubric(row[0], row[1], row[2], row[3], row[4], row[5], row[6],row[7]) rubric_list.append(rubric) return rubric_list @@ -177,7 +187,7 @@ class ScorePadDB(): project_list = [] for row in self.cursor: project = Project(row[0],row[1],row[2],row[3],row[4], - row[5],row[6],row[7],row[8], row[9],row[10]) + row[5],row[6],row[7],row[8], row[9],row[10],row[11]) project_list.append(project) return project_list @@ -186,7 +196,7 @@ class ScorePadDB(): self.cursor.execute(query_str) for row in self.cursor: - rubric = Rubric(row[0],row[1],row[2],row[3],row[4],row[5],row[6]) + rubric = Rubric(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7]) return rubric @@ -196,7 +206,7 @@ class ScorePadDB(): category_list = [] for row in self.cursor: - category = Category(row[0], row[1], row[2], row[3]) + category = Category(row[0], row[1], row[2], row[3],row[4]) category_list.append(category) return category_list @@ -207,7 +217,7 @@ class ScorePadDB(): level_list = [] for row in self.cursor: - level = Level(row[0], row[1], row[2], row[3], row[4], row[5]) + level = Level(row[0], row[1], row[2], row[3], row[4], row[5],row[6]) level_list.append(level) return level_list @@ -221,7 +231,7 @@ class ScorePadDB(): def query_score(self, project_id, rubric_id, category_id, level_id): temp = (project_id, rubric_id, category_id, level_id) - query_str = "SELECT count from score_table WHERE project_id = ? \ + query_str = "SELECT score_count from score_table WHERE project_id = ? \ and rubric_id = ? and category_id = ? and \ level_id = ?" self.cursor.execute(query_str, temp) @@ -251,7 +261,7 @@ class ScorePadDB(): self.cursor.execute(query_str) for row in self.cursor: - project = Project(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10]) + project = Project(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11]) return project @@ -284,32 +294,50 @@ class ScorePadDB(): score_id = self.cursor.fetchone()[0] return score_id - def update_project(self, modified_project): temp = (modified_project.title,modified_project.author,modified_project.description,\ modified_project.subject,\ modified_project.publish_date,\ modified_project.rubric_id,modified_project.project_sha,\ - modified_project.project_id) + modified_project.total_score,modified_project.project_id) update_str = "UPDATE project_table SET title = ?,"+\ "author = ?, "+\ "description = ?,"+\ "subject = ?,"+\ "publish_date = ?,"+\ "rubric_id = ?,"+\ - "project_sha = ?"+\ + "project_sha = ?,"+\ + "total_score = ?"+\ "WHERE project_id = ?" print update_str self.cursor.execute(update_str,temp) self.connection.commit() - + def update_projectshare(self, project_id, is_shared): + temp = (is_shared, project_id) + update_str = "UPDATE project_table SET is_shared = ?"+\ + "WHERE project_id = ?" + self.cursor.execute(update_str,temp) + self.connection.commit() + def delete_project(self, project_id): delete_str = "DELETE FROM project_table WHERE project_id ="+ str(project_id) self.cursor.execute(delete_str) self.connection.commit() + def delete_rubric(self, rubric_id): + delete_str = "DELETE FROM rubric_table WHERE rubric_id ="+ str(rubric_id) + self.cursor.execute(delete_str) + + delete_str = "DELETE FROM category_table WHERE rubric_id ="+ str(rubric_id) + self.cursor.execute(delete_str) + + delete_str = "DELETE FROM level_table WHERE rubric_id ="+ str(rubric_id) + self.cursor.execute(delete_str) + + self.connection.commit() + def score_exists(self, project_id, rubric_id, category_id, level_id): temp = (project_id, rubric_id, category_id, level_id) query_str = "SELECT * FROM score_table WHERE project_id = ? and rubric_id = ? and \ @@ -328,14 +356,14 @@ class ScorePadDB(): except: return False - def project_exists(self, project_sha, author): - temp = (project_sha, author) - query_str = "SELECT * FROM project_table WHERE project_sha = ? and author = ?" + def project_exists(self, title, author): + temp = (title, author) + query_str = "SELECT * FROM project_table WHERE title = ? and author = ?" self.cursor.execute(query_str, temp) for row in self.cursor: project = Project(row[0],row[1],row[2],row[3],row[4],row[5],row[6],\ - row[7],row[8],row[9],row[10]) + row[7],row[8],row[9],row[10],row[11]) try: if project.project_id == None: @@ -344,14 +372,23 @@ class ScorePadDB(): return True except: return False - + + def is_ownedproject(self, project_sha): + query_str = "SELECT is_owned FROM project_table WHERE project_sha = \'" + str(project_sha) +"\'" + self.cursor.execute(query_str) + is_owned = self.cursor.fetchone()[0] + + if is_owned == None: + return 0 + return is_owned + def rubric_exists(self, rubric_sha, description): temp = (rubric_sha, description) query_str = "SELECT * FROM rubric_table WHERE rubric_sha = ? and description = ?" self.cursor.execute(query_str, temp) for row in self.cursor: - rubric = Rubric(row[0],row[1],row[2],row[3],row[4],row[5],row[6]) + rubric = Rubric(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7]) try: if rubric.rubric_id == None: @@ -361,6 +398,22 @@ class ScorePadDB(): except: return None + def check_relatedproject(self, rubric_id): + query_str = "SELECT * FROM project_table WHERE rubric_id = "+str(rubric_id) + self.cursor.execute(query_str) + + for row in self.cursor: + project = Project(row[0],row[1],row[2],row[3],row[4],row[5],row[6],\ + row[7],row[8],row[9],row[10],row[11]) + + try: + if project.project_id == None: + return False + else: + return True + except: + return False + def query_score_attr(self, score_id): query_str = "SELECT * from score_table WHERE score_id = " + str(score_id) self.cursor.execute(query_str) @@ -383,22 +436,36 @@ class ScorePadDB(): score = Score(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]) score_id = score.score_id - count = score.count + count = score.score_count count = count +1 temp = (count, score_id) - update_str = "UPDATE score_table SET count = ?"+\ + update_str = "UPDATE score_table SET score_count = ?"+\ "WHERE score_id = ?" self.cursor.execute(update_str, temp) self.connection.commit() return score_id - + + + #def count_assessor(self, category_id): + # query_str = "SELECT COUNT(*) FROM level_table WHERE category_id = " + str(category_id) + # self.cursor.execute(query_str) + # count = self.cursor.fetchone()[0] + # return count + + def get_scorecount(self, project_id, category_id, level_id): + temp = (project_id, category_id, level_id) + query_str = "SELECT count FROM score_table WHERE project_id = ? AND" +\ + "category_id = ? AND level_id = ?" + self.cursor.execute(query_str, temp) + count = self.cursor.fetchone()[0] + return count class Project(): def __init__(self, project_id = 0, title = "", author = "", description = "", subject = "", publish_date = "", - is_owned = 1, is_shared = 1, rubric_id = None, xo_name = "", - project_sha = ""): + is_owned = 1, is_shared = 0, rubric_id = None, xo_name = "", + project_sha = "",total_score = 0): self.project_id = project_id self.title = title self.author = author @@ -410,7 +477,7 @@ class Project(): self.rubric_id = rubric_id self.xo_name = xo_name self.project_sha = self.get_sha(xo_name, title, publish_date) - + self.total_score = total_score def get_sha(self, xo_name, title, publish_date): text = xo_name + title + str(publish_date) @@ -420,12 +487,10 @@ class Project(): return project_sha - - class Rubric(): def __init__(self, rubric_id = 0, title = "", author = "", description = "",\ - is_predefined =None, xo_name = "", rubric_sha = ""): + is_predefined =None, xo_name = "", rubric_sha = "",enable_points = 0): self.rubric_id = rubric_id self.title = title self.author = author @@ -433,6 +498,7 @@ class Rubric(): self.is_predefined = is_predefined self.xo_name = xo_name self.rubric_sha = self.get_sha(xo_name, title, author) + self.enable_points = enable_points def get_sha(self, xo_name, title, author): text = xo_name + title + author @@ -446,11 +512,12 @@ class Rubric(): class Category(): def __init__(self, category_id = None, name = "", rubric_id = None, - category_sha = ""): + category_sha = "",percentage = 0): self.category_id = category_id self.name = name self.rubric_id = rubric_id self.category_sha = self.get_sha(name) + self.percentage = percentage def get_sha(self, name): text = name @@ -465,13 +532,14 @@ class Level(): def __init__(self, level_id = None, name = "", description = "", category_id = None, rubric_id = None, - level_sha = ""): + level_sha = "",points = 0): self.level_id = level_id self.name = name self.description = description self.category_id = category_id self.rubric_id = rubric_id self.level_sha = self.get_sha(name,description) + self.points = points def get_sha(self, name, description): text = name + description @@ -484,7 +552,7 @@ class Level(): class Score(): def __init__(self, score_id = 0, project_id = None, rubric_id = None, category_id = None,level_id = None, - project_sha = "", rubric_sha = "", category_sha = "", level_sha = "",count = 0): + project_sha = "", rubric_sha = "", category_sha = "", level_sha = "",score_count = 0): self.score_id = score_id self.project_id = project_id self.rubric_id = rubric_id @@ -494,5 +562,5 @@ class Score(): self.rubric_sha = rubric_sha self.category_sha = category_sha self.level_sha = level_sha - self.count = count + self.score_count = score_count |