<commit_before>#import gallerylog.crypto
from pysqlcipher import dbapi2
import os.path

# db = DB("filename.db", "secretKey")
# if not db.successful():
#   ERRR
# db.closeDBFile()

class DB:
    def __init__(self, filename, key):
        self.filename = filename
        self.key = key
        self.openDBFile()

    def openDBFile(self):
        fileExists = os.path.isfile(self.filename)
        self.connection = dbapi2.connect(self.filename)
        self.cursor = self.connection.cursor()
        self.cursor.execute("PRAGMA KEY = '" + self.key + "';")
        if not fileExists:
            self.cursor.execute("CREATE TABLE testvalid(nothing);")
            self.cursor.execute("CREATE TABLE log(name NOT NULL, personType NOT NULL, direction NOT NULL, time INTEGER NOT NULL, room integer);")
            self.cursor.execute("CREATE TABLE status(name NOT NULL, personType NOT NULL, isHere NOT NULL DEFAULT 'f', currentRoom integer);")

    def successful(self):
        try:
            self.cursor.execute("SELECT * FROM testvalid;")
            self.cursor.execute("SELECT * FROM log;")
            self.cursor.execute("SELECT * FROM status;")
            return True
        except:
            return False

    def closeDBFile(self):
        self.connection.commit()
        self.connection.close()

    def getCurrentRoomForPerson(self, name, personType):
        self.cursor.execute("SELECT currentRoom FROM status WHERE name LIKE ? AND personType LIKE ?", (name, personType))
        ret = self.cursor.fetchone()
        if ret != None:
            ret = ret[0]
        return ret

    def isPersonInGallery(self, name, personType):
        self.cursor.execute("SELECT isHere FROM status WHERE name LIKE ? AND personType LIKE ?", (name, personType))
        ret = self.cursor.fetchone()
        if ret != None:
            ret = ret[0]
        return ret == "t"

    # for logread -R

    def getRoomsForPerson(self, name, personType):
        self.cursor.execute("SELECT room FROM log WHERE room IS NOT NULL AND direction LIKE 'A' AND personType LIKE ? AND name LIKE ? ORDER BY time ASC;", (personType, name))
        val = self.cursor.fetchall()
        val = map(lambda x: x[0], val)
        return val

    # for logappend

    def addLogEntry(self, name, personType, direction, time, room=None):
        self.cursor.execute("INSERT INTO log(name, personType, direction, time, room) VALUES (?, ?, ?, ?, ?);",
                            (name, personType, direction, time, room))
        self.cursor.execute("SELECT count(*) FROM status WHERE name LIKE ? AND personType LIKE ?", (name, personType))
        value = self.cursor.fetchone()[0]
        if value == 0:
            self.cursor.execute("INSERT INTO status(name, personType, currentRoom, isHere) VALUES (?, ?, ?, 't');",
                                (name, personType, room))
        else:
            if direction == "A":
                if room == None:
                    self.cursor.execute("UPDATE status SET isHere = 't' WHERE name LIKE ? AND personType LIKE ?;",
                                        (name, personType))
                else:
                    self.cursor.execute("UPDATE status SET currentRoom = ? WHERE name LIKE ? AND personType LIKE ?;",
                                        (room, name, personType))
            else: # departure
                if room == None:
                    self.cursor.execute("UPDATE status SET isHere = 'f' WHERE name LIKE ? AND personType LIKE ?;",
                                        (name, personType))
                else:
                    self.cursor.execute("UPDATE status SET currentRoom = NULL WHERE name LIKE ? AND personType LIKE ?;",
                                        (name, personType))
        return None

    # for logappend input verifications

    def lastLoggedTime(self):
        self.cursor.execute("SELECT MAX(time) FROM log;")
        v = self.cursor.fetchone()[0]
        if v == None:
            v = -1
        return v

    # for logread -S

    def getPeopleByRoom(self):
        self.cursor.execute("SELECT currentRoom, name FROM status WHERE currentRoom IS NOT NULL ORDER BY name ASC;")
        ret = dict()
        myl = self.cursor.fetchall()
        for row in myl:
            if not row[0] in ret.keys():
                ret[row[0]] = []
            ret[row[0]].append(row[1])
        return ret

    def getPeopleHereByType(self, personType):
        self.cursor.execute("SELECT name FROM status WHERE personType LIKE ? AND isHere = 't' ORDER BY name ASC;", (personType))
        val = self.cursor.fetchall()
        val = map(lambda x: x[0], val)
        return val

    # for additional functionality

    def getLogByPerson(self, name, personType):
        self.cursor.execute("SELECT time, direction, room FROM log WHERE personType LIKE ? AND name LIKE ? ORDER BY time ASC;", (personType, name))
        val = self.cursor.fetchall()
        return val

if __name__ == "__main__":
   import os

   logfile = "testlogdb"
   keytoken= "token"

   # delete it and start over each time
   if os.path.isfile(logfile):
       os.unlink(logfile)

   # NOTE: this DB is called db.DB outside this file
   sql = DB(logfile, keytoken)
   if not sql.successful():
       print ("invalid database or token")
       sys.exit(-1)

   def p():
       print "================================"
       print "Is here: " + str(sql.isPersonInGallery("Ryan", "E"))
       print "Current Room: " + str(sql.getCurrentRoomForPerson("Ryan", "E"))
       print "Last logged: " + str(sql.lastLoggedTime())
       print
       print "Status: " + str(sql.getPeopleByRoom())
       print "================================"
       print

   def q():
       print "================================"
       print "Employees: " + str(sql.getPeopleHereByType("E"))
       print "Guests: " + str(sql.getPeopleHereByType("G"))
       print "================================"
       print

   def s():
       print "================================"
       print "Bob log: " + str(sql.getRoomsForPerson("Bob", "E"))
       print "Joe log: " + str(sql.getRoomsForPerson("Joe", "G"))
       print "Ryan log: " + str(sql.getRoomsForPerson("Ryan", "E"))
       print
       print "Joe full log:"
       print str(sql.getLogByPerson("Joe", "G"))
       print "================================"
       print

   q()
   p()

   print "Into the gallery"
   sql.addLogEntry("Ryan", "E", "A", 52)
   p()

   print "Into room 101"
   sql.addLogEntry("Ryan", "E", "A", 55, 101)
   p()

   print "Bob and Joe appear"
   sql.addLogEntry("Bob", "E", "A", 70)
   sql.addLogEntry("Joe", "G", "A", 71)
   p()
   q()

   print "Out of room 101"
   sql.addLogEntry("Ryan", "E", "D", 95, 101)
   p()

   print "Out of this place"
   sql.addLogEntry("Ryan", "E", "D", 152)
   sql.addLogEntry("Ryan", "E", "A", 160, 123)
   sql.addLogEntry("Ryan", "E", "D", 161, 123)
   sql.addLogEntry("Ryan", "E", "A", 162, 123)
   sql.addLogEntry("Ryan", "E", "D", 163, 123)
   sql.addLogEntry("Ryan", "E", "A", 164, 101)
   sql.addLogEntry("Ryan", "E", "D", 165, 101)
   sql.addLogEntry("Joe", "G", "A", 166, 101)
   p()
   q()
   s()

   # always gracefully close the DB after it is open!
   sql.closeDBFile()
<commit_msg>Extra test data<commit_after>#import gallerylog.crypto
from pysqlcipher import dbapi2
import os.path

# db = DB("filename.db", "secretKey")
# if not db.successful():
#   ERRR
# db.closeDBFile()

class DB:
    def __init__(self, filename, key):
        self.filename = filename
        self.key = key
        self.openDBFile()

    def openDBFile(self):
        fileExists = os.path.isfile(self.filename)
        self.connection = dbapi2.connect(self.filename)
        self.cursor = self.connection.cursor()
        self.cursor.execute("PRAGMA KEY = '" + self.key + "';")
        if not fileExists:
            self.cursor.execute("CREATE TABLE testvalid(nothing);")
            self.cursor.execute("CREATE TABLE log(name NOT NULL, personType NOT NULL, direction NOT NULL, time INTEGER NOT NULL, room integer);")
            self.cursor.execute("CREATE TABLE status(name NOT NULL, personType NOT NULL, isHere NOT NULL DEFAULT 'f', currentRoom integer);")

    def successful(self):
        try:
            self.cursor.execute("SELECT * FROM testvalid;")
            self.cursor.execute("SELECT * FROM log;")
            self.cursor.execute("SELECT * FROM status;")
            return True
        except:
            return False

    def closeDBFile(self):
        self.connection.commit()
        self.connection.close()

    def getCurrentRoomForPerson(self, name, personType):
        self.cursor.execute("SELECT currentRoom FROM status WHERE name LIKE ? AND personType LIKE ?", (name, personType))
        ret = self.cursor.fetchone()
        if ret != None:
            ret = ret[0]
        return ret

    def isPersonInGallery(self, name, personType):
        self.cursor.execute("SELECT isHere FROM status WHERE name LIKE ? AND personType LIKE ?", (name, personType))
        ret = self.cursor.fetchone()
        if ret != None:
            ret = ret[0]
        return ret == "t"

    # for logread -R

    def getRoomsForPerson(self, name, personType):
        self.cursor.execute("SELECT room FROM log WHERE room IS NOT NULL AND direction LIKE 'A' AND personType LIKE ? AND name LIKE ? ORDER BY time ASC;", (personType, name))
        val = self.cursor.fetchall()
        val = map(lambda x: x[0], val)
        return val

    # for logappend

    def addLogEntry(self, name, personType, direction, time, room=None):
        self.cursor.execute("INSERT INTO log(name, personType, direction, time, room) VALUES (?, ?, ?, ?, ?);",
                            (name, personType, direction, time, room))
        self.cursor.execute("SELECT count(*) FROM status WHERE name LIKE ? AND personType LIKE ?", (name, personType))
        value = self.cursor.fetchone()[0]
        if value == 0:
            self.cursor.execute("INSERT INTO status(name, personType, currentRoom, isHere) VALUES (?, ?, ?, 't');",
                                (name, personType, room))
        else:
            if direction == "A":
                if room == None:
                    self.cursor.execute("UPDATE status SET isHere = 't' WHERE name LIKE ? AND personType LIKE ?;",
                                        (name, personType))
                else:
                    self.cursor.execute("UPDATE status SET currentRoom = ? WHERE name LIKE ? AND personType LIKE ?;",
                                        (room, name, personType))
            else: # departure
                if room == None:
                    self.cursor.execute("UPDATE status SET isHere = 'f' WHERE name LIKE ? AND personType LIKE ?;",
                                        (name, personType))
                else:
                    self.cursor.execute("UPDATE status SET currentRoom = NULL WHERE name LIKE ? AND personType LIKE ?;",
                                        (name, personType))
        return None

    # for logappend input verifications

    def lastLoggedTime(self):
        self.cursor.execute("SELECT MAX(time) FROM log;")
        v = self.cursor.fetchone()[0]
        if v == None:
            v = -1
        return v

    # for logread -S

    def getPeopleByRoom(self):
        self.cursor.execute("SELECT currentRoom, name FROM status WHERE currentRoom IS NOT NULL ORDER BY name ASC;")
        ret = dict()
        myl = self.cursor.fetchall()
        for row in myl:
            if not row[0] in ret.keys():
                ret[row[0]] = []
            ret[row[0]].append(row[1])
        return ret

    def getPeopleHereByType(self, personType):
        self.cursor.execute("SELECT name FROM status WHERE personType LIKE ? AND isHere = 't' ORDER BY name ASC;", (personType))
        val = self.cursor.fetchall()
        val = map(lambda x: x[0], val)
        return val

    # for additional functionality

    def getLogByPerson(self, name, personType):
        self.cursor.execute("SELECT time, direction, room FROM log WHERE personType LIKE ? AND name LIKE ? ORDER BY time ASC;", (personType, name))
        val = self.cursor.fetchall()
        return val

if __name__ == "__main__":
   import os

   logfile = "testlogdb"
   keytoken= "token"

   # delete it and start over each time
   if os.path.isfile(logfile):
       os.unlink(logfile)

   # NOTE: this DB is called db.DB outside this file
   sql = DB(logfile, keytoken)
   if not sql.successful():
       print ("invalid database or token")
       sys.exit(-1)

   def p():
       print "================================"
       print "Is here: " + str(sql.isPersonInGallery("Ryan", "E"))
       print "Current Room: " + str(sql.getCurrentRoomForPerson("Ryan", "E"))
       print "Last logged: " + str(sql.lastLoggedTime())
       print
       print "Status: " + str(sql.getPeopleByRoom())
       print "================================"
       print

   def q():
       print "================================"
       print "Employees: " + str(sql.getPeopleHereByType("E"))
       print "Guests: " + str(sql.getPeopleHereByType("G"))
       print "================================"
       print

   def s():
       print "================================"
       print "Bob log: " + str(sql.getRoomsForPerson("Bob", "E"))
       print "Joe log: " + str(sql.getRoomsForPerson("Joe", "G"))
       print "Ryan log: " + str(sql.getRoomsForPerson("Ryan", "E"))
       print
       print "Joe full log:"
       print str(sql.getLogByPerson("Joe", "G"))
       print "================================"
       print

   q()
   p()

   print "Into the gallery"
   sql.addLogEntry("Ryan", "E", "A", 52)
   p()

   print "Into room 101"
   sql.addLogEntry("Ryan", "E", "A", 55, 101)
   p()

   print "Bob and Joe appear"
   sql.addLogEntry("Bob", "E", "A", 70)
   sql.addLogEntry("Joe", "G", "A", 71)
   p()
   q()

   print "Out of room 101"
   sql.addLogEntry("Ryan", "E", "D", 95, 101)
   p()

   print "Out of this place"
   sql.addLogEntry("Ryan", "E", "D", 152)
   sql.addLogEntry("Ryan", "E", "A", 160, 123)
   sql.addLogEntry("Ryan", "E", "D", 161, 123)
   sql.addLogEntry("Ryan", "E", "A", 162, 123)
   sql.addLogEntry("Ryan", "E", "D", 163, 123)
   sql.addLogEntry("Ryan", "E", "A", 164, 101)
   sql.addLogEntry("Ryan", "E", "D", 165, 101)
   sql.addLogEntry("Joe", "G", "A", 166, 101)
   sql.addLogEntry("Mary", "E", "A", 170)
   sql.addLogEntry("Sally", "E", "A", 171)
   sql.addLogEntry("Ben", "E", "A", 172)
   sql.addLogEntry("Sally", "E", "A", 173, 144)
   sql.addLogEntry("Ben", "E", "A", 174, 144)
   p()
   q()
   s()

   # always gracefully close the DB after it is open!
   sql.closeDBFile()

{'fraction_non_alphanumeric': 0.15606307775965197, 'fraction_numerical': 0.012574768896139206, 'mean_word_length': 2.3234696182516377, 'pattern_counts': {'":': 4, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '475646', 'n_tokens_mistral': 4591, 'n_tokens_neox': 4236, 'n_words': 1272}