from datetime import datetime from time import sleep import sqlite3 class MosDb(): def __init__(self, dbfile = "mosobjects.db3"): self.idcache = {} self.ridcache = {} self.dbfile = dbfile self.conn = sqlite3.connect(self.dbfile) self.c = self.conn.cursor() self.c.execute("PRAGMA foreign_keys=on;") self.changes_made = False def commit(self): self.conn.commit() def finish(self): if self.changes_made: pass #self.c.execute("VACUUM") self.conn.commit() self.conn.close() def build_id_cache(self, table): self.idcache[table] = {} self.ridcache[table] = {} self.c.execute("SELECT * FROM " + table) for row in self.c.fetchall(): self.idcache[table][row[1]] = row[0] self.ridcache[table][row[0]] = row[1] def add_new_id(self, table, col, value): self.c.execute("INSERT INTO " + table + " (" + col + ") VALUES (?)", (value,)) self.conn.commit() self.changes_made = True rid = self.c.lastrowid self.idcache[table][value] = rid self.ridcache[table][rid] = value def get_id(self, table, col, value): if not table in self.idcache: self.build_id_cache(table) if not value in self.idcache[table]: # New value we didn't know yet, add it self.add_new_id(table, col, value) return self.idcache[table][value] def get_mos_id(self, mos_server): return self.get_id("mosservers", "mosServer", mos_server) def get_status_id(self, status): return self.get_id("mosstatus", "status", status) def get_objair_id(self, obj_air): return self.get_id("mosair", "objAir", obj_air) def get_objgroup_id(self, obj_group): return self.get_id("mosgroups", "objGroup", obj_group) def get_user_id(self, username): return self.get_id("mosusers", "name", username) def date_to_stamp(self, datestring): """Converts given local time to UTC unix stamp For SQLite queries, use strftime('%s', '2017-03-08T17:46:52', 'utc') """ if datestring is None: return None dt = datetime.strptime(datestring, "%Y-%m-%dT%H:%M:%S") return dt.timestamp() def stamp_to_date(self, stamp): """Converts given UTC unix stamp to local time """ dt = datetime.fromtimestamp(stamp) return dt.strftime("%Y-%m-%dT%H:%M:%S") def add_link(self, obj_id, planning_id): self.db_insert_or_replace("mosobjlinks", {"objId": obj_id, "planningId": planning_id}) def db_insert_or_replace(self, table, data): fieldsList = [] placeHolders = [] valuesTuple = () for k in data.keys(): fieldsList.append(k) placeHolders.append("?") valuesTuple += (data[k],) fieldsList = ", ".join(fieldsList) placeHolders = ", ".join(placeHolders) dataWrittenOk = False while not dataWrittenOk: try: self.c.execute( "INSERT OR REPLACE INTO " + table + " (" + fieldsList + ") VALUES (" + placeHolders + ")", valuesTuple ) dataWrittenOk = True except sqlite3.OperationalError as e: print("OpErr: {} - retrying.".format(e)) sleep(0.5) self.changes_made = True def mark_deleted(self, objData): """Sets the isDeleted column to 1 for the specified object. *objData* is a dictionary containing mosId and objId values. """ self.c.execute( "UPDATE mosobjects SET isDeleted=1 WHERE mosId=? AND objId=?", ( self.get_mos_id(objData["mosId"]), objData["objId"] ) ) self.changes_made = True def add_or_replace(self, objData): insertObj = { "mosId": self.get_mos_id(objData["mosId"]), "objId": objData["objId"], "statusId": self.get_status_id(objData["status"]), "isDeleted": objData["isDeleted"], "objSlug": objData["objSlug"], "objPath": objData["objPath"], "objProxyPath": objData["objProxyPath"], "description": objData["description"], "objDur": objData["objDur"], "objAir": self.get_objair_id(objData["objAir"]), "created": self.date_to_stamp(objData["created"]), "createdBy": self.get_user_id(objData["createdBy"]), "changed": self.date_to_stamp(objData["changed"]), "changedBy": self.get_user_id(objData["changedBy"]), "groupId": self.get_objgroup_id(objData["objGroup"]) } self.db_insert_or_replace("mosobjects", insertObj) if objData["planningId"] != "": self.add_link(objData["objId"], objData["planningId"]) def get_missing_lufs(self): """Finds matching objects without LUFS values """ self.c.execute( "SELECT mo.objId, mo.objPath \ FROM mosobjects mo \ LEFT JOIN moslufs lu ON mo.objId=lu.objId \ LEFT JOIN mosservers ms ON mo.mosId=ms.mosId \ LEFT JOIN mosgroups mg ON mo.groupId=mg.groupId \ WHERE mo.isDeleted=0 \ AND ms.mosServer LIKE \"StudioA%\" \ AND mg.objGroup=\"OA Material\" \ AND mo.objSlug LIKE \"%maz%\" \ AND mo.objPath <> \"\" \ AND lu.objId is NULL") result = [] for row in self.c.fetchall(): result.append( (row[0], row[1] )) return result def set_lufs(self, obj_id, lufs, lra, ldiff): """Adds given data to database """ self.db_insert_or_replace("moslufs", {"objId": obj_id, "lufs": lufs, "lra": lra, "ldiff": ldiff}) self.commit() def get_daily_oa(self, delta = "-0 days", days = 1): """Finds today's OA Material with LUFS values """ self.c.execute( "SELECT mo.objId, ml.planningId, mo.objSlug, po.created, mu.name, mu.email, lu.lufs, lu.lra, lu.ldiff \ FROM mosobjects mo \ LEFT JOIN moslufs lu ON mo.objId=lu.objId \ LEFT JOIN mosobjlinks ml ON mo.objId=ml.objId \ LEFT JOIN mosobjects po ON ml.planningId=po.objId \ LEFT JOIN mosservers ms ON mo.mosId=ms.mosId \ LEFT JOIN mosgroups mg ON mo.groupId=mg.groupId \ LEFT JOIN mosusers mu ON po.createdBy=mu.userId \ WHERE mo.isDeleted=0 \ AND ms.mosServer LIKE \"StudioA%\" \ AND mg.objGroup=\"OA Material\" \ AND mo.objSlug LIKE \"%maz%\" \ AND po.created >= strftime('%s', 'now', '" + delta + "', 'start of day', 'utc') \ AND po.created < strftime('%s', 'now', '" + delta + "', '+" + str(days) + "1 day', 'start of day', 'utc') \ ORDER BY po.created ASC" ) result = [] for row in self.c.fetchall(): result.append({ "objId": row[0], "planningId": row[1], "name": row[2], "created": self.stamp_to_date(row[3]), "createdStamp": row[3], "createdBy": row[4], "createdByEmail": row[5], "lu": row[6], "lra": row[7], "ldiff": row[8] }) return result