204 lines
7.4 KiB
Python
204 lines
7.4 KiB
Python
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
|
|
|