Project

Complexity

Total Complexity

79

Size/Duplication

Total Lines	232
Duplicated Lines	0 %

Importance

Changes	3
Bugs	0	Features	0

19 Methods

Rating	Name	Duplication	Size	Complexity
A	__iter__()	0	2	1
A	get_number_of_entries()	0	4	1
F	__next__()	0	28	12
A	get_columns()	0	4	2
A	init_dict()	0	7	4
A	__init__()	0	12	2
A	__question_already_answered()	0	5	1
A	handleTSV()	0	10	3
A	all_tables()	0	5	2
F	init_db()	0	42	17
A	system_tables()	0	3	1
F	export()	0	41	16
A	__transform_column()	0	16	4
A	get_questions()	0	5	2
A	store_answer()	0	5	1
A	handleCSV()	0	9	3
A	custom_tables()	0	3	3
A	__reverse_transform_column()	0	7	3
A	get_whole_table()	0	5	1

from msquaredc.persistence import BackedUpDict
import random
import yaml
import os
import sqlite3
from itertools import repeat

class FileNotFoundError(IOError):
    pass



class Project(object):
    def __init__(self, path=".",file="project.db",coder=None, *args, **kwargs):
        # Project file doesn't already exist
        self.path = path
        self.file = file
        self.conn = sqlite3.connect(os.path.join(path, file))
        self.init_db(path,*args,**kwargs)
        self.current_coding_unit = None
        if coder is not None:
            self.coder = coder
        else:
            raise Exception("Please define the coder!")
        print("End init")

    def init_db(self,path,*args,**kwargs):
        c = self.conn.cursor()
        c.execute("""CREATE TABLE IF NOT EXISTS vars (key text, value text)""")
        c.execute("""CREATE TABLE IF NOT EXISTS translation (clear text, translated text, UNIQUE(clear, translated))""")
        self.conn.commit()
        if "data" in kwargs:
            with open(os.path.join(path,kwargs["data"])) as file:
                res = Project.handleTSV(file)
            if len(res):
                titles = list(res[0].keys())
                cquery = ["{} {}".format(self.__transform_column(i),"TEXT") for i in titles]
                cquery = ", ".join(cquery)
                c.execute("""CREATE TABLE IF NOT EXISTS individuals (id INTEGER PRIMARY KEY,{})""".format(cquery))
                self.conn.commit()
                for row in res:
                    columns = []
                    values = []
                    for column in row:
                        if len(row[column].strip()) != 0:
                            columns.append(self.__transform_column(column))
                            values.append((row[column]))
                    aquery = " AND ".join(i+"=?" for i in columns)

                    if len(values):
                        c.execute("""SELECT id FROM individuals WHERE {}""".format(aquery),values)
                        identifier = c.fetchone()
                        if not identifier:
                            c.execute("""INSERT INTO individuals ({}) VALUES ({})""".format(", ".join(columns)," ,".join("?" for _ in values)),values)
                            self.conn.commit()

        if "questions" in kwargs:
            c.execute("""CREATE TABLE IF NOT EXISTS question_assoc (question text, coding text)""")
            with open(os.path.join(path,kwargs["questions"])) as file:
                questions = yaml.load(file)
            for question in questions["questions"]:
                qquery = ", ".join([self.__transform_column(i["criteria"])+" TEXT" for i in question["coding"]]+["coder TEXT"])
                c.execute("""CREATE TABLE IF NOT EXISTS {} (id INTEGER PRIMARY KEY, {})""".format(self.__transform_column(question["text"]), qquery))
                for i in question["coding"]:
                    c.execute("""INSERT INTO question_assoc SELECT ?,? 
                                 WHERE NOT EXISTS(SELECT 1 FROM question_assoc WHERE question=? AND coding=?)""",
                              (question["text"],i["criteria"],question["text"],i["criteria"]))
                self.conn.commit()

    def get_questions(self,question):
        c = self.conn.cursor()
        c.execute("""SELECT coding FROM question_assoc WHERE question=?""",(question,))
        res = [i[0] for i in c.fetchall()]
        return res

    def __transform_column(self, column):
        column = column.strip()
        before = column
        for i in "?()-,;[].=":
            column = column.replace(i,"_")
        columns = column.split(" ")
        columns = list(map(str.lower,columns))
        kw = ["alter"]
        for i in range(len(columns)):
            if columns[i] in kw:
                columns[i] = "_".join([columns[i][:-1],columns[i][-1]])
        column = "_".join(columns)
        c = self.conn.cursor()
        c.execute("""INSERT OR IGNORE INTO translation (clear, translated) VALUES (?,?)""",(before,column))
        self.conn.commit()
        return column

    def __reverse_transform_column(self,column):
        c = self.conn.cursor()
        c.execute("""SELECT clear FROM translation WHERE translated=?""",(column,))
        res = c.fetchone()
        if res is not None and len(res) > 0:
            return str(res[0])
        return str(None)

    def init_dict(self,init_kwargs,**kwargs):
        for i in kwargs:
            if i not in self.state:
                if i in init_kwargs:
                    self.state[i] = init_kwargs[i]
                else:
                    self.state[i] = kwargs[i]

    @staticmethod
    def handleTSV(file):
        res = []
        titles = []
        for i,j in enumerate(file):
            if i == 0:
                titles = j.strip("\n").split("\t")
            else:
                res.append(dict(zip(titles,j.strip("\n").split("\t"))))
        return res

    @staticmethod
    def handleCSV(file):
        res =[]
        titles = []
        for i, j in enumerate(file):
            if i == 0:
                titles = j.split(",")
        print(titles)
        return res

    @property
    def all_tables(self):
        c = self.conn.cursor()
        c.execute("""SELECT name FROM sqlite_master WHERE type='table'""")
        return [self.__transform_column(i[0]) for i in c.fetchall()]

    @property
    def custom_tables(self):
        return [i for i in self.all_tables if i not in self.system_tables]

    @property
    def system_tables(self):
        return ["vars","individuals","question_assoc","translation"]

    def get_columns(self,table):
        c = self.conn.cursor()
        c.execute("""PRAGMA table_info({})""".format(table))
        return [i[1] for i in c.fetchall()]

    def get_number_of_entries(self,table):
        c = self.conn.cursor()
        c.execute("""SELECT count(*) FROM individuals""".format(self.__transform_column(table)))
        return c.fetchall()[0][0]

    def get_whole_table(self,table):
        colums = self.get_columns(table)
        c = self.conn.cursor()
        c.execute("""SELECT {} FROM {}""".format(", ".join(colums),table))
        return c.fetchall()

    def __iter__(self):
        return self

    def __next__(self):
        if self.current_coding_unit is not None:
            if not self.current_coding_unit.isFinished():
                return self.current_coding_unit
        amount_of_individuals = self.get_number_of_entries("individuals")
        for table in self.custom_tables:
            c = self.conn.cursor()
            c.execute("""SELECT * FROM {}""".format(table))

            ids_in_table = c.fetchall()
            ids_in_table[:] = [i for i in ids_in_table if None not in i]
            if amount_of_individuals > len(ids_in_table):
                entries = set(range(amount_of_individuals)) - set([i[0] for i in ids_in_table])
                entry = random.choice(list(entries))
                c.execute("""SELECT {} FROM individuals""".format(table))
                coding_answer = c.fetchall()[entry][0]
                coding_question = self.__reverse_transform_column(table)
                questions = self.get_questions(coding_question)
                questions[:] = [i for i in questions if not self.__question_already_answered(coding_question,i,entry)]
                if len(questions) == 0:
                    c.execute("""SELECT * FROM question_assoc""")
                    res = c.fetchall()
                    for i in res:
                        print(i, len(i[0]),len(coding_question),repr(coding_question))
                    raise Exception("This should not happen")
                self.current_coding_unit = CodingUnit(self,coding_question,coding_answer,questions,entry)
                return self.current_coding_unit
        raise StopIteration

    def __question_already_answered(self,coding_question,question,id_):
        c = self.conn.cursor()
        c.execute("""SELECT {} FROM {} WHERE id=?""".format(self.__transform_column(question),self.__transform_column(coding_question)),(id_,))
        res = c.fetchone()
        return res is not None and res[0] is not None

    def store_answer(self,coding_question, question, answer,id_):
        c = self.conn.cursor()
        c.execute("""INSERT OR IGNORE INTO {} (id,{}) VALUES (?,?)""".format(self.__transform_column(coding_question),self.__transform_column(question)),(id_,answer))
        c.execute("""UPDATE {} SET id=?,{}=? WHERE id=?""".format(self.__transform_column(coding_question),self.__transform_column(question)),(id_,answer,id_))
        self.conn.commit()

    def export(self, filename="out.txt"):
        with open(os.path.join(self.path, filename), "w") as file:
            file.write("\t".join([self.__reverse_transform_column(i) for i in self.get_columns("individuals") if i not in self.custom_tables + ["id"]]
                                 +["Question to participant","Participant Answer","Coder","Coding Questions","Coding Answer","\n"]))
            for individual in self.get_whole_table("individuals"):
                column = self.get_columns("individuals")
                individual = dict(zip(column,individual))
                for question in self.custom_tables:
                    for i in self.get_whole_table(question):
                        column = self.get_columns(question)
                        coding_questions = dict(zip(column,i))
                        if coding_questions["id"] == individual["id"]:
                            for coding_question in coding_questions:
                                if coding_question != "id" and coding_question != "coder":
                                    file.write("\t".join([str(individual[i]) for i in self.get_columns("individuals") if i not in self.custom_tables +["id"]]
                                                         +[str(self.__reverse_transform_column(question)),str(individual[question]),coding_questions["coder"],self.__reverse_transform_column(coding_question),coding_questions[coding_question],"\n"]))


        titles = list()
        for i in ["individuals"] + self.custom_tables :
            for j in self.get_columns(i):
                titles.append((i, j))
        """
        columns = [i[1] for i in titles if i[0] == "individuals" and i[1] not in [self.__transform_column(i[0]) for i in self.custom_tables]]
        print(len(columns),columns)
        """
        command = """SELECT {} FROM individuals\n""".format(", ".join(list(map(".".join,titles))))+"".join(["""INNER JOIN {} ON {}\n""".format(i,"""{}.id = individuals.id""".format(i)) for i in self.custom_tables])
        """
        print(command)
        c = self.conn.cursor()
        c.execute(command)
        res = c.fetchall()
        print(res)
        for i in res:
            print(i)
        print( titles )
        print([(i[0],self.__reverse_transform_column(i[1])) for i in titles])

        #with open(os.path.join(self.path,filename)):
        #    pass
        """

class CodingUnit(object):
    def __init__(self, project, question, answer, coding_questions,id_):
        self.question = question
        self.answer = answer
        self.coding_questions = coding_questions
        self.coding_answers = dict()
        self.id = id_
        self.project = project
        self["coder"] = project.coder

    def isFinished(self):
        res = True
        res &= all(i in self.coding_answers.keys() for i in self.coding_questions)
        res &= all(self.coding_answers[i] is not None for i in self.coding_answers)
        return res

    def __setitem__(self, key, value):
        self.coding_answers[key] = value
        self.project.store_answer(self.question,key,value,self.id)

    def __repr__(self):
        return "\n".join(["Coding unit: {} -> {}".format(self.question,self.answer)]+["-{}\n\t-> {}".format(i, self.coding_answers.get(i,None)) for i in self.coding_questions])