diff options
Diffstat (limited to 'Models.py')
-rw-r--r-- | Models.py | 498 |
1 files changed, 498 insertions, 0 deletions
diff --git a/Models.py b/Models.py new file mode 100644 index 0000000..c504886 --- /dev/null +++ b/Models.py @@ -0,0 +1,498 @@ +import sqlite3 +import hashlib + +class ScorePadDB(): + + def __init__(self, nickname = ""): + + #self.scorepaddb = "/home/cnix/ThesisWorkspace2/ScorePad1.4/src/db/scorepaddb.sqlite" + + self.scorepaddb = "db/"+str(nickname)+"db.sqlite" + self.connection = sqlite3.connect(self.scorepaddb) + self.cursor = self.connection.cursor() + + 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)" + 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)" + + 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)" + + project_table = "CREATE TABLE project_table (project_id INTEGER PRIMARY KEY,\ + title VARCHAR(40) NOT NULL,\ + author VARCHAR(40) NOT NULL,\ + description TEXT,\ + subject VARCHAR(40),\ + publish_date TEXT,\ + is_owned INTEGER,\ + is_shared INTEGER,\ + rubric_id INTEGER NOT NULL REFERENCES rubric_table(rubric_id),\ + xo_name VARCHAR(50),\ + project_sha TEXT)" + + score_table = "CREATE TABLE score_table (score_id INTEGER PRIMARY KEY,\ + project_id INTEGER NOT NULL REFERENCES project_table(project_id),\ + rubric_id INTEGER NOT NULL REFERENCES rubric_table(rubric_id),\ + category_id INTEGER NOT NULL REFERENCES category_table(category_id),\ + level_id INTEGER NOT NULL REFERENCES level_table(level_id),\ + project_sha TEXT REFERENCES project_table(project_sha),\ + 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))" + + self.cursor.execute(rubric_table) + print "rubric table created" + self.cursor.execute(category_table) + print "category table created" + self.cursor.execute(level_table) + print "level table created" + self.cursor.execute(project_table) + print "project table created" + self.cursor.execute(score_table) + print "score table created" + self.cursor.execute(assessor_table) + print "assessor table created" + self.cursor.close() + + + def insert_rubric(self, rubric): + temp = (rubric.title, rubric.author, rubric.description, rubric.is_predefined, + rubric.xo_name, rubric.rubric_sha) + insert_str = "INSERT INTO rubric_table(title, author, description,is_predefined,\ + xo_name, rubric_sha)\ + VALUES(?,?,?,?,?,?)" + self.cursor.execute(insert_str,temp) + self.connection.commit() + + def query_maxrubric(self): + query_str = "SELECT MAX(rubric_id) from rubric_table" + self.cursor.execute(query_str) + rubric_id = self.cursor.fetchone()[0] + 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 (?,?,?)" + self.cursor.execute(insert_str, temp) + self.connection.commit() + + query_str = "SELECT MAX(category_id) from category_table" + 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(?,?,?,?,?)" + for i in range(len(levels)): + temp = (levels[i].name, levels[i].description,category_id ,\ + levels[i].rubric_id, levels[i].level_sha) + self.cursor.execute(insert_str, temp) + self.connection.commit() + + def insert_project(self, project): + 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) + + insert_str = "INSERT INTO project_table(title, author, description, subject,\ + publish_date, is_owned, is_shared, rubric_id,xo_name,\ + project_sha) VALUES (?,?,?,?,?,?,?,?,?,?)" + + self.cursor.execute(insert_str,temp) + self.connection.commit() + + def query_maxproject(self): + query_str = "SELECT MAX(project_id) from project_table" + self.cursor.execute(query_str) + project_id = self.cursor.fetchone()[0] + return project_id + + 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) + insert_str = "INSERT INTO score_table(project_id,rubric_id,category_id,level_id,\ + project_sha, rubric_sha, category_sha, level_sha, 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 (?,?,?)" + 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(?,?,?,?,?)" + self.cursor.execute(insert_str, temp) + self.connection.commit() + + + def query_maxscore(self): + query_str = "SELECT MAX(score_id) from score_table" + self.cursor.execute(query_str) + score_id = self.cursor.fetchone()[0] + return score_id + + def query_maxcategory(self): + query_str = "SELECT MAX(category_id) from category_table" + self.cursor.execute(query_str) + category_id = self.cursor.fetchone()[0] + return category_id + + def queryall_rubric(self, is_predefined): + query_str = "SELECT * from rubric_table where is_predefined = "+str(is_predefined) + self.cursor.execute(query_str) + + rubric_list = [] + for row in self.cursor: + rubric = Rubric(row[0], row[1], row[2], row[3], row[4], row[5], row[6]) + rubric_list.append(rubric) + return rubric_list + + def queryall_project(self, is_owned): + query_str = "SELECT * from project_table where is_owned = "+str(is_owned) + self.cursor.execute(query_str) + + 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]) + project_list.append(project) + return project_list + + def query_rubric(self, rubric_id): + query_str = "SELECT * from rubric_table where rubric_id = "+str(rubric_id) + 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]) + + return rubric + + def queryall_category(self, rubric_id): + query_str = "SELECT * from category_table where rubric_id = "+str(rubric_id) + self.cursor.execute(query_str) + + category_list = [] + for row in self.cursor: + category = Category(row[0], row[1], row[2], row[3]) + category_list.append(category) + return category_list + + def query_level(self,category_id): + + query_str = "SELECT * from level_table where category_id ="+str(category_id) + self.cursor.execute(query_str) + + level_list = [] + for row in self.cursor: + level = Level(row[0], row[1], row[2], row[3], row[4], row[5]) + level_list.append(level) + return level_list + + def querylevel_id(self, category_id, level_name): + temp = (category_id, level_name) + query_str = "SELECT level_id from level_table WHERE category_id = ? and name = ?" + self.cursor.execute(query_str,temp) + level_id = self.cursor.fetchone()[0] + return level_id + + + 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 = ? \ + and rubric_id = ? and category_id = ? and \ + level_id = ?" + self.cursor.execute(query_str, temp) + count = self.cursor.fetchone()[0] + return count + + def query_score2(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 category_id = ? and \ + level_id = ?" + + self.cursor.execute(query_str, temp) + for row in self.cursor: + score = Score(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]) + return score + + def query_project_sha(self, project_id): + query_str = "SELECT project_sha from project_table WHERE project_id = "+ str(project_id) + self.cursor.execute(query_str) + + project_sha = self.cursor.fetchone()[0] + return str(project_sha) + + def query_project(self, project_id): + query_str = "SELECT * from project_table WHERE project_id ="+ str(project_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]) + + return project + + def query_rubric_sha(self, rubric_id): + query_str = "SELECT rubric_sha from rubric_table WHERE rubric_id = "+ str(rubric_id) + self.cursor.execute(query_str) + + rubric_sha = self.cursor.fetchone()[0] + return str(rubric_sha) + + def query_category_sha(self, category_id): + query_str = "SELECT category_sha from category_table WHERE category_id = "+ str(category_id) + self.cursor.execute(query_str) + + category_sha = self.cursor.fetchone()[0] + return str(category_sha) + + def query_level_sha(self, level_id): + query_str = "SELECT level_sha from level_table WHERE level_id = "+ str(level_id) + self.cursor.execute(query_str) + + level_sha = self.cursor.fetchone()[0] + return str(level_sha) + + def query_score_id(self, project_sha, rubric_sha, category_sha, level_sha): + temp = (project_sha, rubric_sha, category_sha, level_sha) + query_str = "SELECT score_id from score_table WHERE project_sha = ? \ + and rubric_sha = ? and category_sha = ? and level_sha = ?" + self.cursor.execute(query_str, temp) + 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) + update_str = "UPDATE project_table SET title = ?,"+\ + "author = ?, "+\ + "description = ?,"+\ + "subject = ?,"+\ + "publish_date = ?,"+\ + "rubric_id = ?,"+\ + "project_sha = ?"+\ + "WHERE project_id = ?" + print update_str + 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 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 \ + category_id = ? and level_id = ?" + + self.cursor.execute(query_str, temp) + for row in self.cursor: + score = Score(row[0],row[1],row[2],row[3],row[4],row[5],row[6],\ + row[7],row[8],row[9]) + + try: + if score.score_id == None: + return False + else: + return True + 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 = ?" + + 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]) + + try: + if project.project_id == None: + return False + else: + return True + except: + return False + + 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]) + + try: + if rubric.rubric_id == None: + return None + else: + return rubric.rubric_id + except: + return None + + def query_score_attr(self, score_id): + query_str = "SELECT * from score_table WHERE score_id = " + str(score_id) + self.cursor.execute(query_str) + + for row in self.cursor: + score = Score(row[0],row[1],row[2],row[3],row[4],row[5],row[6],\ + row[7],row[8],row[9]) + + attr = [score.project_id, score.rubric_id, score.category_id, score.level_id] + return attr + + + def increment_scorecount(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 \ + category_id = ? and level_id = ?" + self.cursor.execute(query_str, temp) + + for row in self.cursor: + 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 = count +1 + temp = (count, score_id) + update_str = "UPDATE score_table SET count = ?"+\ + "WHERE score_id = ?" + self.cursor.execute(update_str, temp) + self.connection.commit() + return score_id + + +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 = ""): + self.project_id = project_id + self.title = title + self.author = author + self.description = description + self.subject = subject + self.publish_date = publish_date + self.is_owned = is_owned + self.is_shared = is_shared + self.rubric_id = rubric_id + self.xo_name = xo_name + self.project_sha = self.get_sha(xo_name, title, publish_date) + + + def get_sha(self, xo_name, title, publish_date): + text = xo_name + title + str(publish_date) + h = hashlib.sha1() + h.update(text) + project_sha = str(h.hexdigest()) + + return project_sha + + + +class Rubric(): + + def __init__(self, rubric_id = 0, title = "", author = "", description = "",\ + is_predefined =None, xo_name = "", rubric_sha = ""): + self.rubric_id = rubric_id + self.title = title + self.author = author + self.description = description + self.is_predefined = is_predefined + self.xo_name = xo_name + self.rubric_sha = self.get_sha(xo_name, title, author) + + def get_sha(self, xo_name, title, author): + text = xo_name + title + author + h = hashlib.sha1() + h.update(text) + rubric_sha = str(h.hexdigest()) + + return rubric_sha + + +class Category(): + + def __init__(self, category_id = None, name = "", rubric_id = None, + category_sha = ""): + self.category_id = category_id + self.name = name + self.rubric_id = rubric_id + self.category_sha = self.get_sha(name) + + def get_sha(self, name): + text = name + h = hashlib.sha1() + h.update(text) + category_sha = str(h.hexdigest()) + + return category_sha + + +class Level(): + + def __init__(self, level_id = None, name = "", description = "", + category_id = None, rubric_id = None, + level_sha = ""): + 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) + + def get_sha(self, name, description): + text = name + description + h = hashlib.sha1() + h.update(text) + level_sha = str(h.hexdigest()) + + return level_sha + +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): + self.score_id = score_id + self.project_id = project_id + self.rubric_id = rubric_id + self.category_id = category_id + self.level_id = level_id + self.project_sha = project_sha + self.rubric_sha = rubric_sha + self.category_sha = category_sha + self.level_sha = level_sha + self.count = count + |