RCS Zetta: Background Recorder Timestamper

ZettaBackgroundRecorderTimestamper.py

"""
Zetta Background Recorder Timestamper
This script appends the last-modified timestamp string to assets that are updated by the background recorder.

Written by Anthony Eden (https://mediarealm.com.au)
"""

import pypyodbc
import time
import datetime

# Setup Zetta SQL DB Connection:
SQLConnectionString = 'DSN=ZettaDB;'
dbConn = pypyodbc.connect(SQLConnectionString)
cur = dbConn.cursor()

def utc2local(utc):
    # From https://stackoverflow.com/a/19238551
    epoch = time.mktime(utc.timetuple())
    offset = datetime.datetime.fromtimestamp(epoch) - datetime.datetime.utcfromtimestamp(epoch)
    return utc + offset

def listBackgroundRecorderAssets():
	assetsQuery = '''
        SELECT
                RecordEvent.AssetID,
                Asset.infoModifiedDate,
                Asset.Title,
                [Resource].infoModifiedDate
        FROM
                [RecordEvent],
                [Asset],
                [AssetToResource],
                [Resource]
        WHERE RefId IS NULL
        AND RecordEvent.AssetID IS NOT NULL
        AND RecordEvent.AssetID = Asset.AssetID
        AND AssetToResource.AssetID = Asset.AssetID
        AND AssetToResource.IsPrimaryResource = 1
        AND AssetToResource.ResourceID = [Resource].ResourceID
	'''
	
	cur.execute(assetsQuery)
	assets = []

	rows = cur.fetchall()
	for d in rows:
		localTime = utc2local(d[3])
		assets.append({
			"AssetID": d[0],
			"Title": d[2],
      "LastModified": d[3],
			"LastModifiedLocal": localTime,
      "AppendDateString": "[" + localTime.strftime("%d/%m/%Y %I:%M:%S %p") + "]"
		})
	
	return assets

def updateAssetTitle(assetId, title):
  # Sets a new title for an asset
	assetQuery = ''' UPDATE TOP(1) Asset SET Title = ? WHERE AssetID = ? '''
	cur.execute(assetQuery, (title, assetId))
	dbConn.commit()


if __name__ == "__main__":
	
	while True:
		try:
      # Loop over all background recorder assets
			for x in listBackgroundRecorderAssets():
      
        # Does this asset already have square braces in the title?
				if "[" in x['Title'] and "]" in x['Title'][x['Title'].index("["):]:
          # Replace existing square braces with datetime stamp
					newTitle = x['Title'][:x['Title'].index("[")] + x['AppendDateString'] + x['Title'][x['Title'].index("]", x['Title'].index("[")) + 1:]

					if newTitle != x['Title']:
						print "Update title", newTitle, "for Asset ID", x['AssetID']
						updateAssetTitle(x['AssetID'], newTitle)
				else:
          # Append the datetime stamp to the existing title
					newTitle = x['Title'] + " " + x['AppendDateString']
					print "New title", newTitle, "for Asset ID", x['AssetID']
					updateAssetTitle(x['AssetID'], newTitle)
	
			time.sleep(20)
		except Exception, e:
			print "Exception!!!", e
			time.sleep(120)