def create_xml(xml_total):
"""
Use the merged, parsed, cleaned, DB data to generate an XML used for file check in on the Dalet MAM.
"""
config = cfg.get_config()
rootpath = config['paths']['rootpath']
xml_checkin = config['paths']['xml_checkin_path']
os.chdir(rootpath)
xml_1_msg = f"START GORILLA-DIVA XML CREATION"
logger.info(xml_1_msg)
try:
conn = db.connect()
cur = conn.cursor()
sql = '''SELECT * FROM assets WHERE xml_created = 0'''
xml_count = 0
for row in cur.execute(sql).fetchall():
if xml_count >= int(xml_total):
break
else:
ROWID = row[0]
GUID = row[1]
NAME = row[2]
FILESIZE = row[3]
DATATAPEID = row[4]
OBJECTNM = row[5]
CONTENTLENGTH = row[6]
SOURCECREATEDT = row[7]
CREATEDT = row[8]
LASTMDYDT = row[9]
TIMECODEIN = row[10]
TIMECODEOUT = row[11]
ONAIRID = row[12]
RURI = row[13]
TITLETYPE = row[14]
FRAMERATE = row[15]
CODEC = row[16]
V_WIDTH = row[17]
V_HEIGHT = row[18]
TRAFFIC_CODE = row[19]
DURATION_MS = row[20]
XML_CREATED = row[21]
PROXY_COPIED = row[22]
CONTENT_TYPE = row[23]
FILENAME = row[24]
OC_COMPONENT_NAME = row[31]
if (DATATAPEID != 'unallocated' and DATATAPEID != 'NULL'
and OC_COMPONENT_NAME != 'NULL'):
guid = GUID
name = NAME
filename = FILENAME
datatapeid = DATATAPEID
timecodein = TIMECODEIN
folderpath = "T://DaletStorage/Video_Watch_Folder" + str(
OC_COMPONENT_NAME)
traffic_code = str(TRAFFIC_CODE).strip("=\"")
title_type = TITLETYPE
framerate = FRAMERATE
codec = CODEC
v_width = V_WIDTH
v_height = V_HEIGHT
duration = DURATION_MS
content_type = CONTENT_TYPE
conn.close()
os.chdir(xml_checkin)
xml_doc = str(guid) + '.xml'
with open(xml_doc, mode="w", encoding='utf-8-sig') as xdoc:
xml_body = f"\
<Titles>\
<Title><!-- title type video -->\
<key1>{guid}</key1>\
<itemcode>{guid}</itemcode>\
<title>{name}</title>\
<NGC_NGCITitle>{name}</NGC_NGCITitle>\
<NGC_NGCIFilename>{filename}</NGC_NGCIFilename>\
<NGC_DivaTapeID>{datatapeid}</NGC_DivaTapeID>\
<NGC_FolderPath>{folderpath}</NGC_FolderPath>\
<StartOfMaterial>{timecodein}</StartOfMaterial>\
<NGC_NGCITrafficCode>{traffic_code}</NGC_NGCITrafficCode>\
<titletype>{title_type}</titletype>\
<NGC_ContentType>{content_type}</NGC_ContentType>\
<AMFieldFromParsing_FrameRate>{framerate}</AMFieldFromParsing_FrameRate>\
<AMFieldFromParsing_Codec>{codec}</AMFieldFromParsing_Codec>\
<AMFieldFromParsing_Width>{v_width}</AMFieldFromParsing_Width>\
<AMFieldFromParsing_Hight>{v_height}</AMFieldFromParsing_Hight>\
<duration>{duration}</duration>\
\
<MediaInfos>\
<MediaInfo>\
<mediaFormatId>100002</mediaFormatId>\
<mediaStorageName>G_DIVA</mediaStorageName>\
<mediaStorageId>161</mediaStorageId>\
<mediaFileName>{guid}</mediaFileName>\
<mediaProcessStatus>Online</mediaProcessStatus>\
</MediaInfo>\
</MediaInfos>\
</Title>\
</Titles>"
xmlstr = minidom.parseString(xml_body).toprettyxml(
indent=" ")
xdoc.write(xmlstr)
xdoc.close()
os.chdir(rootpath)
update = db.update_column('assets', 'xml_created', 1,
ROWID)
xmlcreate_msg = (f"\n\
RowID: {str(ROWID)}\n\
xml_count: {xml_count}\n\
xml_doc: {str(xml_doc)}\n")
logger.info(xmlcreate_msg)
xml_count += 1
else:
xml_pass_msg = f"XML Creation skipped on {ROWID} for asset {GUID}. DATETAPEID = {DATATAPEID}"
logger.debug(xml_pass_msg)
pass
os.chdir(rootpath)
xml_2_msg = f"GORILLA-DIVA XML CREATION COMPLETED"
logger.info(xml_2_msg)
except Exception as e:
xml_excp_msg = f"\n\
Exception raised on the XML Creation.\n\
ROWID = {ROWID}\n\
Error Message: {str(e)} \n\
XML Count: {xml_count}\n\
"
logger.exception(xml_excp_msg)
def update_db(date, tablename):
"""
Start by creating a backup of the exisiting DB.
Then update the DB by comparing rows in new CSV export to rows in the existing DB.
Add new rows from the CSV into the DB, and remove rows from the DB if they do not exist in
the new CSV.
"""
config = cfg.get_config()
rootpath = config['paths']['rootpath']
csvpath = config['paths']['csvpath']
clean_csv = date + "_" + "gor_diva_merged_cleaned.csv"
if os.path.isfile(os.path.join(rootpath, 'database.db')) is not True:
return
else:
try:
shutil.copy2(
os.path.join(rootpath, 'database.db'),
os.path.join(rootpath, 'database_BKP_' + date + '.db'))
update_db_msg = f"BEGIN DB UPDATE"
logger.info(update_db_msg)
print(update_db_msg)
cca.crosscheck_assets(tablename)
os.chdir(csvpath)
with open(clean_csv, mode='r', encoding='utf-8-sig') as c_csv:
pd_reader = pd.read_csv(c_csv, header=0)
df = pd.DataFrame(pd_reader)
update_count = 0
update_index = []
drop_count = 0
drop_index = []
insert_count = 0
insert_index = []
mismatch_count = 0
mismatch_index = []
total_count = 0
none_count = 0
for index, row in df.iterrows():
os.chdir(rootpath)
guid = str(row['GUID'])
titletype = str(row['TITLETYPE'])
datatapeid = str(row['DATATAPEID'])
update_db_msg_01 = f"Updating DB for (Index, GUID): ({index}, {guid})"
logger.info(update_db_msg_01)
print(str(index) + " " + guid)
db_row_id = db.fetchone_guid(
guid
) #fetch db row based on GUID, row ID may not match db Row ID.
if db_row_id is not None:
db_row = db.select_row(db_row_id[0])
else:
db_row_msg = f"None value(s) found in db row, skipping this row. \n {db_row}"
none_count += 1
continue
db_datatapeid = db_row[4]
db_aoid = db_row[24]
db_titletype = db_row[14]
if (guid == db_row[1] and db_datatapeid == "NULL"
and db_aoid == "NULL"):
db.update_row("assets", index, row)
update_count += 1
update_index.append(index)
if (guid != db_row[1] and db.fetchone_guid(guid) is None):
db.drop_row('assets', index, guid)
drop_count += 1
drop_index.append(index)
if (db_row is None and row['_merge'] == 'both'):
db.insert_row(index, row)
insert_count += 1
insert_index.append(index)
if (titletype != db_titletype):
db.update_column("assets", 'TITLETYPE', titletype,
index)
update_count += 1
update_index.append(index)
if (guid != db_row[1] and db.fetchone_guid(guid) != None):
mismatch_msg = f"Mismatch in the db update: {db_row[1]} != {guid}"
logger.error(mismatch_msg)
mismatch_count += 1
mismatch_index.append(index)
pass
else:
nochange_msg = f"No change to {guid} at row index {index}."
logger.debug(nochange_msg)
# print(nochange_msg)
pass
total_count += 1
update_summary_msg = f"\n\
Update Count: {update_count}\n\
Drop Count: {drop_count}\n\
Insert Count: {insert_count}\n\
Mismatch Count: {mismatch_count}\n\
No Change Count: {total_count - (update_count + drop_count + insert_count + mismatch_count)}\n\
Total Count: {total_count}\n\
"
index_summary_msg = f"\n\
None Value Count = {none_count}\n\
update index: {update_index}\n\
drop index: {drop_index}\n\
insert index: {insert_index}\n\
mismatch index: {mismatch_index}\n\
"
logger.info(update_summary_msg)
logger.info(index_summary_msg)
print(update_summary_msg)
print("")
print(index_summary_msg)
db_update_complete_msg = f"DB UPDATE COMPLETE"
logger.info(db_update_complete_msg)
except Exception as e:
dbupdate_err_msg = f"Error updating the DB."
logger.exception(dbupdate_err_msg)
def basic_auto_migrate_relational_db(app, bind):
"""Inspired with http://stackoverflow.com/questions/2103274/"""
from sqlalchemy import Table
from sqlalchemy import MetaData
print('Performing very simple automigration in', bind, 'database...')
db.session.commit()
db.reflect()
db.session.commit()
db.create_all(bind=bind)
with app.app_context():
engine = db.get_engine(app, bind)
tables = db.get_tables_for_bind(bind=bind)
metadata = MetaData()
metadata.engine = engine
ddl = engine.dialect.ddl_compiler(engine.dialect, None)
for table in tables:
db_table = Table(table.name,
metadata,
autoload=True,
autoload_with=engine)
db_columns = get_column_names(db_table)
columns = get_column_names(table)
new_columns = columns - db_columns
unused_columns = db_columns - columns
existing_columns = columns.intersection(db_columns)
for column_name in new_columns:
column = getattr(table.c, column_name)
if column.constraints:
print('Column %s skipped due to existing constraints.' %
column_name)
continue
print('Creating column: %s' % column_name)
definition = ddl.get_column_specification(column)
add_column(engine, table.name, definition)
if engine.dialect.name == 'mysql':
sql = 'SHOW CREATE TABLE `%s`' % table.name
table_definition = engine.execute(sql)
columns_definitions = {}
to_replace = {
'TINYINT(1)':
'BOOL', # synonymous for MySQL and SQLAlchemy
'INT(11)': 'INTEGER',
'DOUBLE': 'FLOAT(53)',
' DEFAULT NULL': ''
}
for definition in table_definition.first()[1].split('\n'):
match = re.match(
'\s*`(?P<name>.*?)` (?P<definition>[^,]*),?',
definition)
if match:
name = match.group('name')
definition_string = match.group('definition').upper()
for mysql_explicit_definition, implicit_sqlalchemy in to_replace.items(
):
definition_string = definition_string.replace(
mysql_explicit_definition, implicit_sqlalchemy)
columns_definitions[
name] = name + ' ' + definition_string
columns_to_update = []
for column_name in existing_columns:
column = getattr(table.c, column_name)
old_definition = columns_definitions[column_name]
new_definition = ddl.get_column_specification(column)
if old_definition != new_definition:
columns_to_update.append(
[column_name, old_definition, new_definition])
if columns_to_update:
print(
'\nFollowing columns in `%s` table differ in definitions '
'from those in specified in models:' % table.name)
for column, old_definition, new_definition in columns_to_update:
answer = get_answer(
'Column: `%s`\n'
'Old definition: %s\n'
'New definition: %s\n'
'Update column definition?' %
(column, old_definition, new_definition))
if answer == 'y':
update_column(engine, table.name, new_definition)
print('Updated %s column definition' % column)
else:
print('Skipped %s column' % column)
if unused_columns:
print('\nFollowing columns in `%s` table are no longer used '
'and can be safely removed:' % table.name)
for column in unused_columns:
answer = get_answer('Column: `%s` - remove?' % column)
if answer == 'y':
drop_column(engine, table.name, column)
print('Removed column %s.' % column)
else:
print('Keeping column %s.' % column)
print('Automigration of', bind, 'database completed.')
def get_proxy(proxy_total):
"""
Get a set of files where titletype = video and proxy copy status is 0,
Copy the proxies a tmp location for checkin staging.
PROXY_COPIED values:
0 = proxy not copied
1 = proxy copied
2 = proxy path does not exist
3 = no proxy, content type is archive
"""
config = cfg.get_config()
conn = db.connect()
xmlpath = config['paths']['xmlpath']
proxypath = config['paths']['proxypath']
tmp_checkin = config['paths']['tmp']
rootpath = config['paths']['rootpath']
rows = db.fetchall_proxy('assets')
proxy_count = 0
for row in rows:
rowid = row[0]
guid = str(row[1])
proxy_copied = row[22]
guid_x = guid.replace("-", "")
guid_r = guid_x[24:]
proxy_fn = guid + '.mov'
"""
Use the parts GUID to generate a list that will be used to build the path to the proxy.
"""
n = 2
glist = [guid_r[i:i+n] for i in range(0, len(guid_r), n)]
proxy_fpath = os.path.join(
proxypath, glist[2], glist[3], guid, proxy_fn)
if (proxy_count < int(proxy_total)
and proxy_copied == 0
and os.path.exists(proxy_fpath) is True):
try:
pcopy = file_copy(proxy_fpath, tmp_checkin)
if len(pcopy) == 0:
row = db.fetchone_proxy(guid)
db.update_column('assets', 'proxy_copied', 1, rowid)
proxy_cp_msg = f"{proxy_fn} was copied to the dalet tmp."
logger.info(proxy_cp_msg)
proxy_count += 1
else:
pass
proxy_err_cp_msg = f"{proxy_fn} encountered an error on the copy to the dalet tmp."
logger.info(proxy_err_cp_msg)
except Exception as e:
proxy_excp_msg = f"\n\
Exception raised on the Proxy copy.\n\
Error Message: {str(e)} \n\
"
logger.exception(proxy_excp_msg)
break
else:
if os.path.exists(proxy_fpath) is not True:
proxy_err_msg = f"Proxy path does not exist. \n\
{proxy_fpath}"
logger.error(proxy_err_msg)
db.update_column('assets', 'proxy_copied', 2, rowid)
continue
os.chdir(rootpath)
proxy_complete_msg = f"PROXY COPY COMPLETE. \n\
{proxy_count} proxies copied \n"
logger.info(proxy_complete_msg)
return