import os import shutil import logging import time import re import sys from datetime import datetime from datetime import timedelta import mysql.connector as mariadb import epg_grace as service import random import system_defines logging.basicConfig(format='%(asctime)s %(levelname)-8s %(message)s', filename="/var/www/epg/log.txt", level=logging.DEBUG, datefmt='%Y-%m-%d %H:%M:%S') class Provider: def __init__(self): self.id = "" self.name = "" class Channel: def __init__(self): self.id = "" self.callSign = "" self.affiliateCallSign = "" self.name = "" self.number = "" # has to handle various dot notations just in cast self.imageUrl = "" class Program: def __init__(self): self.title = "" self.startTime = None self.durationMins = 0 self.description = "NA" self.imageUrl = "NA" self.genres = "NA" self.cast = "NA" def updateDb(): mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") createDbTables() # Modify/add here.... # sql = ("ALTER TABLE selectedChannels ADD COLUMN `contentUrl` varchar(132) default ''") # try: # cursor.execute(sql) # except mariadb.Error: # pass mariadb_connection.commit() cursor.close() def resetDb(): timestr = time.strftime("%Y%m%d-%H%M%S") os.system("mysqldump -u root -p{} zvepg > zvepg_save_{}.sql".format(system_defines.mariaDbPassword, timestr)) mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) # mariadb_connection.atocommit=True; # using explicit connection.commit() calls cursor = mariadb_connection.cursor(buffered=True) try: cursor.execute("drop database zvepg") except mariadb.Error: pass cursor.execute("CREATE DATABASE zvepg CHARACTER SET ='utf8'") cursor.execute("use zvepg") createDbTables() def createDbTables(): mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") # Map mso/provider, country, postalcode ==> providers sql = ("CREATE TABLE `providers` (" "`providerName` varchar(64), `country` varchar(64), `postalCode` char(32), `providerId` varchar(64), " "constraint mapId primary key (country, postalCode, providerId))") try: cursor.execute(sql) except mariadb.Error as e: if e.errno != 1050: # error 1050 is table already exists -- which is fine sys.exit("sql error: {}".format(e)) # More than one "provider" can points to the same channel -- like DTV national vs local. # BUT, the channel number for each provider MAY be different (that's why it's in this table) # sql = ("CREATE TABLE `providerChannelMap` (" "`providerId` varchar(64), `channelId` varchar(64), `channelNumber` varchar(10), " "constraint mapId primary key (providerId, channelId))") try: cursor.execute(sql) except mariadb.Error as e: if e.errno != 1050: sys.exit("sql error: {}".format(e)) # List of all channels, the common superset of all channels contained in all providers sql = ("CREATE TABLE `channels` (" "`channelId` varchar(64) primary key not null, `callSign` varchar(64), " "`affiliateCallSign` varchar(64), `name` varchar(64), `imageUrl` varchar(64) default '')") try: cursor.execute(sql) except mariadb.Error as e: if e.errno != 1050: sys.exit("sql error: {}".format(e)) sql = ("CREATE TABLE `integrators` (" "`name` varchar(64) primary key not null, `password` varchar(32))") try: cursor.execute(sql) except mariadb.Error as e: if e.errno != 1050: sys.exit("sql error: {}".format(e)) sql = ("CREATE TABLE `epgs` (" "`integrator` varchar(64),`name` varchar(64), `type` char(32) default 'IP', `country` varchar(64), `postalCode` char(32), `epgId` char(32), " "`backgroundColor` char(16) default '#101010', `evenRowColor` char(16) default '#303030', " "`oddRowColor` char(16) default '#505050', `fontColor` char(16) default '#b0b0b0', " "constraint mapId primary key (epgId))") try: cursor.execute(sql) except mariadb.Error as e: if e.errno != 1050: sys.exit("sql error: {}".format(e)) sql = ("CREATE TABLE `epgProviderMap` (" "`epgId` char(32), `providerId` varchar(64), constraint mapId primary key (epgId, providerId))") try: cursor.execute(sql) except mariadb.Error as e: if e.errno != 1050: sys.exit("sql error: {}".format(e)) sql = ("CREATE TABLE `selectedChannels` (" "`epgId` char(32), `channelId` varchar(64), `providerId` varchar(64), `localChannelNumber` varchar(10) default '', " "`contentUrl` varchar(132) default '', ""constraint mapId primary key (epgId, providerId, channelId))") try: cursor.execute(sql) except mariadb.Error as e: if e.errno != 1050: sys.exit("sql error: {}".format(e)) sql = ("CREATE TABLE `selectedChannelsLastChangeId` (" "`epgId` char(32) primary key not null, `lastChangeId` int)") try: cursor.execute(sql) except mariadb.Error as e: if e.errno != 1050: sys.exit("sql error: {}".format(e)) sql = ("CREATE TABLE `channelPrograms` (`id` int primary key auto_increment, " "`channelId` varchar(64), `name` varchar(64), `startTime` datetime, " "`durationMins` int, `description` varchar(512), `imageUrl` varchar(64), " "`genres` varchar(64), `cast` varchar(64))") try: cursor.execute(sql) except mariadb.Error as e: if e.errno != 1050: sys.exit("sql error: {}".format(e)) mariadb_connection.commit() cursor.close() def addIntegrator(name, password): response = {"error": "Success"} if len(password) > 32: response["error"] = "ErrMsg" response["errorMsg"] = "Password too long" return response mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT * FROM integrators where name='{}'".format(name) cursor.execute(sql) if cursor.rowcount: response["error"] = "ErrMsg" response["errorMsg"] = "Inegrator already exists" return response sql = (u"insert into integrators(name, password) " "values('{}', '{}')").format(name, password) cursor.execute(sql) mariadb_connection.commit() cursor.close() return response def checkIntegratorPassword(name, password): response = {"error": "Success", "authType": "User"} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT password FROM integrators where name='{}'".format(name) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "ErrMsg" response["errorMsg"] = "Integrator does not exist" return response row = cursor.fetchone() if password == "ZvPass": response["authType"] = "Admin" elif password != row[0]: response["error"] = "ErrMsg" response["errorMsg"] = "Invalid password" return response mariadb_connection.commit() cursor.close() return response def changeIntegratorPassword(name, password): response = {"error": "Success"} if len(password) > 31: response["error"] = "ErrMsg" response["errorMsg"] = "Password too long" return response mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT * FROM integrators WHERE name='{}'".format(name) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "ErrMsg" response["errorMsg"] = "Inegrator does not exist" return response sql = (u"UPDATE integrators SET password = '{}' WHERE name='{}'").format(password, name) cursor.execute(sql) mariadb_connection.commit() cursor.close() return response def delIntegrator(integrator): response = {"error": "Success"} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT * FROM integrators where name='{}'".format(integrator) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "ErrMsg" response["errorMsg"] = "Inegrator does not exist" return response sql = (u"CREATE TEMPORARY TABLE delEpgs " "(SELECT epgId FROM epgs WHERE integrator='{}' )").format(integrator) cursor.execute(sql) sql = (u"DELETE FROM selectedChannels WHERE EXISTS " "(SELECT 1 FROM delEpgs WHERE selectedChannels.epgId=delEpgs.epgId)") cursor.execute(sql) sql = u"DELETE FROM epgs where integrator='{}'".format(integrator) cursor.execute(sql) sql = u"DELETE FROM integrators where name='{}'".format(integrator) cursor.execute(sql) mariadb_connection.commit() cursor.close() return response def getIntegrators(): response = {"error": "Success", "integrators": []} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT name FROM integrators" cursor.execute(sql) for (name) in cursor: response["integrators"].append(name) mariadb_connection.commit() cursor.close() return response def getCountries(): response = {"error": "Success", "countries": ["USA", "CAN", "GBR"]} return response def getProviders(country, postalCode): response = {"error": "Success", "country": country, "postalCode": postalCode, "providers": []} # annoyingly, grace returns a set of providers even for illegal postal codes if country == "USA": if not re.search(r"^\d{5}(?:[-\s]\d{4})?$", postalCode): response["error"] = "ErrMsg" response["errorMsg"] = "Invalid USA postal code" return response elif country == "CAN": if not re.search(r"^[ABCEGHJ-NPRSTVXY][0-9][ABCEGHJ-NPRSTV-Z] [0-9][ABCEGHJ-NPRSTV-Z][0-9]$", postalCode, re.IGNORECASE): response["errorMsg"] = "ErrMsg" response["errorMsg"] = "Invalid Canadiean postal code" return response elif country == "GBR": if False: # not re.search(r"^([Gg][Ii][Rr] 0[Aa]{2})|((([A-Za-z][0-9]{1,2})|(([A-Za-z][A-Ha-hJ-Yj-y]" # "[0-9]{1,2})|(([A-Za-z][0-9][A-Za-z])|([A-Za-z][A-Ha-hJ-Yj-y][0-9]?[A-Za-z])))) [0-9][A-Za-z]{2})$", postalCode): response["error"] = "ErrMsg" response["errorMsg"] = "Invalid Great Britain postal code" return response elif len(postalCode) > 32: response["error"] = "ErrMsg" response["errorMsg"] = "Postal Code too long" return response mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") # If already in DB, use it, else request it and then update db sql = u"SELECT providerId, providerName FROM providers where country=%s AND postalCode=%s" cursor.execute(sql, (country, postalCode)) if cursor.rowcount > 0: for providerId, providerName in cursor: # can't use Provider since this has to be json encoded provider = {} provider["providerId"] = providerId provider["providerName"] = providerName response["providers"].append(provider) else: providersSrv = service.getProviders(country, postalCode) if len(providersSrv) == 0: response["error"] = "ErrMsg" response["errorMsg"] = "Invalid postal code" return response for providerSrv in providersSrv: sql = u"INSERT INTO providers (providerName, country, postalCode, providerId) VALUES (%s, %s, %s, %s)" cursor.execute(sql, (providerSrv.name, country, postalCode, providerSrv.id)) # can't use Provider since this has to be json encoded provider = {} provider["providerId"] = providerSrv.id provider["providerName"] = providerSrv.name response["providers"].append(provider) mariadb_connection.commit() cursor.close() return response def changeEpg(epgId, integrator, name, type, country, postalCode, providerIdsStr, newProviderOk): return addChangeEpg(epgId, integrator, name, type, country, postalCode, providerIdsStr, newProviderOk, True) def addEpg(epgId, integrator, name, type, country, postalCode, providerIdsStr, newProviderOk): return addChangeEpg(epgId, integrator, name, type, country, postalCode, providerIdsStr, newProviderOk, False) def addChangeEpg(epgId, integrator, name, type, country, postalCode, providerIdsStr, newProviderOk, change): response = {"error": "Success", "name": name, "type": type, "country": country, "postalCode": postalCode, "epgId": epgId} logging.debug("EPG_DB.PY: entering addChangeEpg") if len(name) > 63: response["error"] = "ErrMsg" response["errorMsg"] = "Name > 63 characters" return response if type != "IP" and type != "RF": response["error"] = "ErrMsg" response["errorMsg"] = "EPG type not RF or IP" return response countries = getCountries() if country not in countries["countries"]: response["error"] = "ErrMsg" response["errorMsg"] = "Country " + country + " not supported" return response providerIds = providerIdsStr.split(";") if (len(providerIds) == 0): response["error"] = "ErrMsg" response["errorMsg"] = "No providerIds provided" return response mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") if not change: sql = u"select * from epgs WHERE name=%s" cursor.execute(sql, (name, )) if cursor.rowcount > 0: response["error"] = "ErrMsg" response["errorMsg"] = "Name exists" return response newProviders = [] allProviders = getProviders(country, postalCode)["providers"] epgProviders = [] for providerId in providerIds: if providerId == "none": continue providerMatch = False for provider in allProviders: if providerId == provider["providerId"]: providerMatch = True epgProviders.append(provider) break if providerMatch: # if this is a new provider, it can take a really long time to get its channel info # so caller must say this is ok (usually just resubmit the same request with newProviderOk = true) sql = u"select * from epgProviderMap WHERE providerId=%s" cursor.execute(sql, (providerId, )) if cursor.rowcount == 0 and newProviderOk == "false": newProviders.append(provider) else: response["error"] = "ErrMsg" response["errorMsg"] = "Invalid providerId, {}".format(providerId) return response if len(newProviders) > 0: response["error"] = "ErrNewProvider" response["errorMsg"] = "New Provider and newProviderOk false" response["newProviders"] = newProviders return response sql = u"SELECT * FROM integrators where name='{}'".format(integrator) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "ErrMsg" response["errorMsg"] = "Inegrator does not exists" return response if epgId == "": # come up with a random epg id, but make sure it's not already in use while True: epgId = "{:0>3}-{:0>3}-{:0>3}".format(random.randint(0, 999), random.randint(0, 999), random.randint(0, 999)) try: sql = u"SELECT * FROM epgs where epgId={}".format(epgId) cursor.execute(sql) if cursor.rowcount == 0: break except mariadb.Error: break else: try: sql = u"SELECT * FROM epgs where epgId={}".format(epgId) cursor.execute(sql) if cursor.rowcount != 0: response["error"] = "ErrMsg" response["errorMsg"] = "Duplicate EPG ID" return response except mariadb.Error: pass if (change): # Note that epg selected channels are not deleted on a change. That can mean that # there are selected channels in the epg not in the provider list. But they just won't be shown; no harm. sql = (u"UPDATE epgs SET integrator = %s, name = %s, type = %s, country = %s, postalCode = %s WHERE epgId = %s") cursor.execute(sql, (integrator, name, type, country, postalCode, epgId)) sql = u"DELETE FROM epgProviderMap where epgId='{}'".format(epgId) cursor.execute(sql) logging.debug("EPG_DB.PY: addChangeEpg -- change deleting existing ") else: sql = (u"insert into epgs(integrator, name, type, country, postalCode, epgId) VALUES (%s, %s, %s, %s, %s, %s)") cursor.execute(sql, (integrator, name, type, country, postalCode, epgId)) shutil.copyfile('art/ZeeVee-Logo.png', "art/epgIcons/epg_" + epgId + "_icon") # See if this provider's channels have been retrieved, and if not, get them # for provider in epgProviders: logging.debug("EPG_DB.PY: addChangeEpg -- adding provider '{}'".format(epgId)) sql = u"INSERT INTO epgProviderMap VALUES (%s, %s)" try: cursor.execute(sql, (epgId, provider["providerId"])) except mariadb.Error: pass # Only check if there is one provider->channel map entry for this provider, assume all channels for this provider exist. # sql = u"SELECT 1 FROM providerChannelMap WHERE providerId=%s LIMIT 1" cursor.execute(sql, (provider["providerId"], )) if cursor.rowcount == 0: # Get the list of channels for this provider from whatever service we're using channels = service.getProviderChannels(provider["providerId"]) if len(channels) == 0: cursor.close() response["error"] = "ErrMsg" response["errorMsg"] = "Failed to contact service" return response # Add the channel to the db, AND map this provider to this channel for channel in channels: # most of these will fail since most channels are in every provider. sql = (u"INSERT INTO channels(channelId, callSign, affiliateCallSign, name) VALUES (%s, %s, %s, %s)") try: cursor.execute(sql, (channel.id, channel.callSign, channel.affiliateCallSign, channel.name[:63])) except mariadb.Error: pass # Map this provider to the channel (that may have already existed in the db) # ALSO, the one (and only) thing that is different per channel per provider is the channel number sql = (u"INSERT INTO providerChannelMap(providerId, channelId, channelNumber) VALUES (%s, %s, %s)") try: cursor.execute(sql, (provider["providerId"], channel.id, channel.number)) except mariadb.Error: pass incrementLastChangeId(mariadb_connection, epgId) mariadb_connection.commit() cursor.close() response["providers"] = epgProviders response["numChannelsToUpdate"] = startUpdateAllChannelsInfo() response["epgId"] = epgId return response def validateEpg(epgId): response = {"error": "Success", "authType": "User"} if len(epgId) > 11: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT epgId FROM epgs where epgId='{}'".format(epgId) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response mariadb_connection.commit() cursor.close() return response def delEpg(epgId): response = {"error": "Success"} # Only delete the direct epg related data. # Leave all provider and channel info in db since others may use it (now or in the future) # mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT epgId FROM epgs where epgId='{}'".format(epgId) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response sql = u"DELETE FROM epgs where epgId='{}'".format(epgId) cursor.execute(sql) # Only delete associated local channels. Provider channels may be referenced by other providers, and anyway, can't hurt to keep them. providerId = "Local_" + epgId sql = u"DELETE FROM channels WHERE channelId IN (SELECT channelId FROM providerChannelMap WHERE providerId = '{}')".format(providerId) cursor.execute(sql) sql = u"DELETE FROM epgProviderMap where epgId='{}'".format(epgId) cursor.execute(sql) sql = u"DELETE FROM selectedChannels where epgId='{}'".format(epgId) cursor.execute(sql) mariadb_connection.commit() cursor.close() return response def getEpgs(integrator): response = {"error": "Success", "epgs": []} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") cursor2 = mariadb_connection.cursor(buffered=True) cursor2.execute("use zvepg") sql = (u"SELECT name, type, country, postalCode, epgId FROM epgs WHERE integrator='{}' ORDER BY name, type, country, postalCode").format(integrator) try: cursor.execute(sql) except mariadb.Error: response["error"] = "ErrMsg" response["errorMsg"] = "Integrator does not exist" return response for (name, type, country, postalCode, epgId) in cursor: sql = u"SELECT epgProviderMap.providerId, providerName FROM epgProviderMap JOIN providers ON epgProviderMap.providerId = providers.providerId WHERE epgId=%s" cursor2.execute(sql, (epgId, )) epg = {} epg["name"] = name epg["type"] = type epg["country"] = country epg["postalCode"] = postalCode providers = [{"providerId": providerId, "providerName": providerName} for providerId, providerName in cursor2] epg["providers"] = providers epg["epgId"] = epgId response["epgs"].append(epg) mariadb_connection.commit() cursor.close() return response def addLocalChannel(epgId, callSign, name): response = {"error": "Success"} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT epgId FROM epgs where epgId='{}'".format(epgId) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response if len(callSign) > 63: response["error"] = "ErrMsg" response["errorMsg"] = "CallSign > 63 characters" return response if len(name) > 63: response["error"] = "ErrMsg" response["errorMsg"] = "Name > 63 characters" return response channelId = "Local_{}_{}_{}".format(epgId, callSign, random.randint(0, 99999)) channelId = channelId.replace(" ", "_") response["channelId"] = channelId sql = u"SELECT channelId FROM channels where channelId=%s" cursor.execute(sql, (channelId, )) if cursor.rowcount == 1: response["error"] = "ErrMsg" response["errorMsg"] = "Local channel already exists" return response # NOTE! cannot check if image file exists since being called from php, which has not yet # copied the file because the channelId is the base of the file name, and THIS function generates that!! imageUrl = "art/localChannelIcons/" + channelId + "_icon" logging.debug("EPG_DB.PY: ImageURL {}".format(imageUrl)) response["imageUrl"] = imageUrl sql = (u"insert into channels(channelId, callSign, affiliateCallSign, name, imageUrl) values(%s, %s, %s, %s, %s)") cursor.execute(sql, (channelId, callSign, "", name, imageUrl)) providerId = "Local_" + epgId sql = (u"insert into providerChannelMap(providerId, channelId, channelNumber) values(%s, %s, 'Local')") cursor.execute(sql, (providerId, channelId)) incrementLastChangeId(mariadb_connection, epgId) mariadb_connection.commit() cursor.close() return response def changeLocalChannel(epgId, channelId, callSign, name): response = {"error": "Success"} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT epgId FROM epgs where epgId='{}'".format(epgId) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response if len(callSign) > 63: response["error"] = "ErrMsg" response["errorMsg"] = "CallSign > 63 characters" return response if len(name) > 63: response["error"] = "ErrMsg" response["errorMsg"] = "Name > 63 characters" return response response["channelId"] = channelId sql = u"SELECT channelId FROM channels where channelId=%s" cursor.execute(sql, (channelId, )) if cursor.rowcount == 0: response["error"] = "ChanNotExist" response["errorMsg"] = "Local channel does not exist" return response # NOTE! cannot check if image file exists since being called from php, which has not yet # copied the file because the channelId is the base of the file name, and THIS function generates that!! imageUrl = "art/localChannelIcons/" + channelId + "_icon" logging.debug("EPG_DB.PY: ImageURL {}".format(imageUrl)) response["imageUrl"] = imageUrl sql = u"UPDATE channels SET callSign = %s, name = %s WHERE channelId = %s" cursor.execute(sql, (callSign, name, channelId)) updateChannelPrograms(mariadb_connection, channelId) incrementLastChangeId(mariadb_connection, epgId) mariadb_connection.commit() cursor.close() return response def deleteLocalChannel(epgId, channelId): response = {"error": "Success"} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT epgId FROM epgs where epgId='{}'".format(epgId) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response sql = u"SELECT channelId FROM channels where channelId=%s" cursor.execute(sql, (channelId, )) if cursor.rowcount == 0: response["error"] = "ErrMsg" response["errorMsg"] = "Local channel does not exist" return response sql = u"DELETE FROM channels WHERE channelId=%s" cursor.execute(sql, (channelId, )) sql = u"DELETE FROM selectedChannels WHERE channelId=%s" cursor.execute(sql, (channelId, )) # technically should include where epgId too, but local channelId is unique. sql = u"DELETE FROM providerChannelMap WHERE channelId=%s" cursor.execute(sql, (channelId, )) incrementLastChangeId(mariadb_connection, epgId) mariadb_connection.commit() cursor.close() return response def getEpgChannels(epgId): response = {"error": "Success", "channels": []} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") cursor.execute("set innodb_lock_wait_timeout=10") sql = (u"SELECT type, backgroundColor, evenRowColor, oddRowColor, fontColor FROM epgs WHERE epgId='{}'").format(epgId) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response row = cursor.fetchone() response["epgId"] = epgId response["epgType"] = row[0] response["epgIconUrl"] = "art/epgIcons/epg_" + epgId + "_icon" response["epgBackgroundColor"] = row[1] response["epgEvenRowColor"] = row[2] response["epgOddRowColor"] = row[3] response["epgFontColor"] = row[4] tmpIcon = "art/epgIcons/epg_" + epgId + "_icon_temp" try: os.remove(tmpIcon) except OSError: pass sql = (u"select epgId, epgProviderMap.providerId, providerName FROM epgProviderMap " "JOIN providers ON epgProviderMap.providerId = providers.providerId WHERE epgId=%s") providers = {} cursor.execute(sql, (epgId, )) if (cursor.rowcount > 0): for (epgId, providerId, providerName) in cursor: try: if providers[providerId]: pass except: getProviderChannels(mariadb_connection, epgId, providerName, providerId, response) providers[providerId] = True providerId = "Local_" + epgId getProviderChannels(mariadb_connection, epgId, "Local", providerId, response) mariadb_connection.commit() cursor.close() return response def getProviderChannels(mariadb_connection, epgId, providerName, providerId, response): cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") cursor.execute("set innodb_lock_wait_timeout=10") # Based on epg providers, get all available channels; service provider AND local sql = (u"CREATE TEMPORARY TABLE epgChannels SELECT channels.channelId, callSign, affiliateCallSign, name, channelNumber, imageUrl FROM channels " "JOIN providerChannelMap ON providerChannelMap.channelId = channels.channelId WHERE providerId=%s") try: cursor.execute(sql, (providerId, )) except mariadb.Error as e: # timeout response["channels"] = [] response["error"] = "ErrMsg" response["errorMsg"] = "SQL error {}".format(e) return response # using the above temp table... add columns for selected channels in this epg sql = (u"SELECT epgChannels.channelId, callSign, affiliateCallSign, name, channelNumber, imageUrl, " "selectedChannels.localChannelNumber, selectedChannels.contentUrl FROM epgChannels " "LEFT JOIN selectedChannels " "ON epgChannels.channelId = selectedChannels.channelId AND selectedChannels.epgId=%s AND selectedChannels.providerId = %s") cursor.execute(sql, (epgId, providerId)) # Can't use Channel class since it needs to be translated to json format for (channelId, callSign, affiliateCallSign, name, channelNumber, imageUrl, localChannelNumber, contentUrl) in cursor: channel = {} if localChannelNumber is None: # due to the LEFT join, and rows with no entry from the selectedChannel table channel["localChannelNumber"] = "" else: if (localChannelNumber == ""): # odd case where contentURL exists, but no localChan num continue channel["localChannelNumber"] = localChannelNumber if contentUrl is None: # due to the LEFT join, and rows with no entry from the selectedChannel table channel["contentUrl"] = "" else: channel["contentUrl"] = contentUrl channel["callSign"] = callSign channel["number"] = channelNumber channel["provider"] = {"providerId": providerId, "providerName": providerName} channel["id"] = channelId channel["affiliateCallSign"] = affiliateCallSign channel["name"] = name channel["imageUrl"] = imageUrl response["channels"].append(channel) sql = u"DROP TABLE epgChannels" cursor.execute(sql) mariadb_connection.commit() cursor.close() def setEpgChannels(epgId, selectedChannelInfo): response = {"error": "Success"} # selectedChannelInfo is an array containing strings of the format ;;; # The following makes sure that this is the case and converts the string into an array of 2-tuples localNums = [] # This is JUST to check the string to see if the format/values are correct contentUrls = [] try: channelInfo = [x.split(";") for x in selectedChannelInfo] # creates an array of arrays localNums = [float(val[2]) for val in channelInfo if len(val) > 2] # get array of channel numbers contentUrls = [val[3] for val in channelInfo if len(val) == 4] # get array of content urls except ValueError as e: logging.debug("EPG_DB.PY: Set epg channels syntax error on input: {}".format(e)) else: localNums = sorted(set(localNums)) # again, only done to check for uniqueness if len(channelInfo) != len(localNums) or len(channelInfo) != len(contentUrls): response["error"] = "ErrMsg" response["errorMsg"] = "Incorrect format of ;;[] where localChannelNumber is non-zero and unique" return response mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = (u"SELECT name FROM epgs WHERE epgId='{}'").format(epgId) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response sql = (u"DELETE FROM selectedChannels WHERE epgId='{}'").format(epgId) cursor.execute(sql) # NOTE! not using the float value of localNums here!! Storing as string for providerId, channelId, localChannelNumber, contentUrl in channelInfo: logging.debug("EPG_DB.PY: providerId=" + providerId + "chanId=" + channelId + " locChan=" + localChannelNumber + " url=" + contentUrl) sql = u"INSERT INTO selectedChannels (epgId, providerId, channelId, localChannelNumber, contentUrl) VALUES (%s, %s, %s, %s, %s)" try: cursor.execute(sql, (epgId, providerId, channelId, localChannelNumber, contentUrl)) except mariadb.Error as e: logging.debug("EPG_DB.PY: Set selectedChannels error {}".format(e)) else: # get program info for this channel, but only do this if channel added to db (could be dups?) updateChannelPrograms(mariadb_connection, channelId) incrementLastChangeId(mariadb_connection, epgId) mariadb_connection.commit() cursor.close() return response def incrementLastChangeId(mariadb_connection, epgId): cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") # update lastChangeId used by browsers to know the provider has changed # sql = u"SELECT lastChangeId FROM selectedChannelsLastChangeId WHERE epgId=%s " cursor.execute(sql, (epgId, )) if cursor.rowcount == 0: lastChangeId = 1 sql = u"INSERT INTO selectedChannelsLastChangeId (epgId, lastChangeId) values (%s, %s)" cursor.execute(sql, (epgId, lastChangeId)) else: row = cursor.fetchone() lastChangeId = int(row[0]) + 1 sql = u"UPDATE selectedChannelsLastChangeId SET lastChangeId = %s WHERE epgId=%s " cursor.execute(sql, (lastChangeId, epgId)) return lastChangeId def startUpdateAllChannelsInfo(): mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") logging.debug("EPG_DB.PY: startingUpdateAllChannelsInfo -- checking if some need updates") sql = u"SELECT channelId FROM channels WHERE name = '' or imageUrl = ''" cursor.execute(sql) if cursor.rowcount > 0: # NOTE!! # -- This reqruies /etc/passwd user www-data have a real shell (not nologin) # -- AND /etc/at.deny must NOT have www-data in it!! # Also, piping into "at"; "at" is a deamon! Will run any command at some time!! logging.debug("EPG_DB.PY: Starting update on needed channels ") os.system("echo '/usr/bin/python /var/www/epg/epg.py --update_all_channels_info' | at now &> daemon.txt") return cursor.rowcount def getUpdateAllChannelsInfoStatus(): response = {"error": "Success"} logging.debug("EPG_DB.PY: updateAllChannelProgs starting... ") mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT channelId FROM channels WHERE name = '' or imageUrl = ''" cursor.execute(sql) response["numChannelsToUpdate"] = cursor.rowcount return response def updateAllChannelsInfo(): response = {"error": "Success"} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor2 = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") cursor2.execute("use zvepg") logging.debug("EPG_DB.PY: UPDATE all channels!!") sql = u"SELECT channelId FROM channels WHERE name = '' or imageUrl = ''" cursor.execute(sql) commitSkipCount = 0 for (channelId,) in cursor: if channelId.find("Local") >= 0: continue print "{}".format(channelId) channel = service.getChannelInfo(channelId) name = channel.name imageUrl = channel.imageUrl logging.debug(u"EPG_DB.PY: UPDATE channel={}, name={}, imageURL={}".format(channelId, name, imageUrl)) # print (u"Have info {} {}").format(name, imageUrl) sql = u"UPDATE channels SET name = %s, imageUrl=%s WHERE channelId=%s" cursor2.execute(sql, (name[:63], imageUrl[:63], channelId)) commitSkipCount += 1 if commitSkipCount % 10 == 0: mariadb_connection.commit() mariadb_connection.commit() cursor.close() return response def createLocalPrograms(mariadb_connection, channelId): cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = u"SELECT name FROM channels WHERE channelId=%s" cursor.execute(sql, (channelId,)) channel = cursor.fetchone() oneHour = timedelta(hours=1) beforeHours = timedelta(hours=12) startDate = datetime.utcnow().replace(microsecond=0, second=0, minute=0) - beforeHours programs = [] for i in range(0, 48 + 3): program = Program() program.title = channel[0] program.startTime = startDate.strftime("%Y-%m-%dT%H:%MZ") program.durationMins = 60 program.description = "Local Programming" program.imageUrl = "" program.genres = "" program.cast = "" programs.append(program) startDate += oneHour return programs def updateChannelPrograms(mariadb_connection, channelId): cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") logging.debug("EPG_DB.PY: entering updateChannelPrograms") # All channels should already have their info (name, image), but make sure of that now updateAllChannelsInfo() # Get 48 + 3 hours of programming; BUT... this is done from GMT NOW - 12 to now plus 48 + 3 hours # Normally this is done GMT 00:00, but when a new channel is added, it's done then if channelId.find("Local") >= 0: programs = createLocalPrograms(mariadb_connection, channelId) else: programs = service.getPrograms(channelId) sql = (u"DELETE FROM channelPrograms WHERE channelId='{}'").format(channelId) try: cursor.execute(sql) except mariadb.Error as e: logging.debug("EPG_DB.PY: updateChannelProgs delete error: {}".format(e)) for prog in programs: startTime = datetime.strptime(prog.startTime, "%Y-%m-%dT%H:%MZ") description = prog.description[:509] + "..." if len(prog.description) > 512 else prog.description sql = (u"INSERT INTO channelPrograms (channelId, name, startTime, durationMins, " "description, imageUrl, genres, cast) values (%s, \"%s\", %s, %s, \"%s\", %s, \"%s\", \"%s\")") cursor.execute(sql, (channelId, prog.title[:62], startTime, prog.durationMins, description[:509], prog.imageUrl[:62], prog.genres[:62], prog.cast[:62])) def updateAllChannelPrograms(): response = {"error": "Success"} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor2 = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") cursor2.execute("use zvepg") logging.debug("EPG_DB.PY: updateAllChannelProgs starting... ") sql = u"SELECT DISTINCT channelId FROM selectedChannels" cursor.execute(sql) for (channelId, ) in cursor: print "chan {}".format(channelId) updateChannelPrograms(mariadb_connection, channelId) sql = u"SELECT lastChangeId, epgId FROM selectedChannelsLastChangeId" cursor.execute(sql) for lastChangeId, epgId in cursor: sql = u"UPDATE selectedChannelsLastChangeId SET lastChangeId = %s WHERE epgId = %s" cursor2.execute(sql, (lastChangeId + 1, epgId)) mariadb_connection.commit() cursor.close() logging.debug("EPG_DB.PY: updateAllChannelProgs complete") return response def saveEpgConfig(epgId, backgroundColor, fontColor, evenRowColor, oddRowColor): response = {"error": "Success"} mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") tmpIcon = "art/epgIcons/epg_" + epgId + "_icon_temp" permIcon = "art/epgIcons/epg_" + epgId + "_icon" try: os.rename(tmpIcon, permIcon) except OSError: pass sql = (u"SELECT epgId FROM epgs WHERE epgId='{}'").format(epgId) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response sql = u"UPDATE epgs SET backgroundColor = %s, evenRowColor = %s, oddRowColor = %s, fontColor = %s WHERE epgId=%s" cursor.execute(sql, (backgroundColor[:15], fontColor[:15], evenRowColor[:15], oddRowColor[:15], epgId)) incrementLastChangeId(mariadb_connection, epgId) mariadb_connection.commit() cursor.close() return response def getEpgPrograms(epgId, lastChangeIdReqStr): response = {"error": "Success", "channels": []} try: lastChangeIdReq = int(lastChangeIdReqStr) except ValueError: response["error"] = "ErrMsg" response["errorMsg"] = "lastChangeId not valid integer value" return response mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = (u"SELECT type, backgroundColor, evenRowColor, oddRowColor, fontColor FROM epgs WHERE epgId='{}'").format(epgId) cursor.execute(sql) if cursor.rowcount == 0: response["error"] = "EpgNotExist" response["errorMsg"] = "EPG does not exist" return response row = cursor.fetchone() response["epgId"] = epgId response["epgType"] = row[0] response["epgIconUrl"] = "art/epgIcons/epg_" + epgId + "_icon" response["epgBackgroundColor"] = row[1] response["epgEvenRowColor"] = row[2] response["epgOddRowColor"] = row[3] response["epgFontColor"] = row[4] lastChangeId = 0 sql = u"SELECT lastChangeId FROM selectedChannelsLastChangeId WHERE epgId=%s " cursor.execute(sql, (epgId, )) if cursor.rowcount == 0: if lastChangeIdReq == 0: lastChangeId = 1 else: lastChangeId = lastChangeIdReq sql = u"INSERT INTO selectedChannelsLastChangeId (epgId, lastChangeId) values (%s, %s)" cursor.execute(sql, (epgId, lastChangeId)) else: row = cursor.fetchone() lastChangeId = int(row[0]) if lastChangeIdReq == lastChangeId: response["error"] = "NoChange" response["errorMsg"] = "" mariadb_connection.commit() cursor.close() return response response["lastChangeId"] = lastChangeId logging.debug("EPG_DB.PY: getEpgPrograms for {}".format(epgId)) sql = (u"CREATE TEMPORARY TABLE custChannels " "(SELECT selectedChannels.epgId, selectedChannels.channelId, selectedChannels.localChannelNumber, selectedChannels.contentUrl, " "channels.name, channels.callSign, channels.affiliateCallSign, channels.imageUrl FROM selectedChannels " "JOIN channels ON selectedChannels.channelId = channels.channelId WHERE selectedChannels.epgId = %s)") cursor.execute(sql, (epgId,)) # Add temp table containing channel info with program data sql = (u"SELECT custChannels.channelId, custChannels.name, custChannels.callSign, custChannels.affiliateCallSign, custChannels.imageUrl," # from channels "localChannelNumber, contentUrl, " # from selectedEpgChannels "channelPrograms.name, startTime, durationMins, description, channelPrograms.imageUrl, genres, cast " # rest from channelPrograms "FROM channelPrograms JOIN custChannels ON channelPrograms.channelId = custChannels.channelId " "WHERE epgId = %s ORDER BY localChannelNumber, startTime") cursor.execute(sql, (epgId,)) channels = [] prevChannel = 0 channel = {} for (channelId, channelName, callSign, affiliateCallSign, channelImageUrl, localChannelNumber, contentUrl, progName, startTime, durationMins, description, progImageUrl, genres, cast) in cursor: if localChannelNumber != prevChannel: if prevChannel != 0: channels.append(channel) prevChannel = localChannelNumber channel = {} channel["name"] = channelName channel["callSign"] = callSign channel["imageUrl"] = channelImageUrl channel["localChannelNumber"] = localChannelNumber channel["contentUrl"] = contentUrl channel["channelId"] = channelId channel["programs"] = [] program = {} program["name"] = progName program["startTime"] = startTime.strftime("%Y-%m-%dT%H:%MZ") program["durationMins"] = durationMins program["description"] = description program["imageUrl"] = progImageUrl program["genres"] = genres program["cast"] = cast channel["programs"].append(program) # append last channel... if there's at least one if cursor.rowcount > 0: channels.append(channel) response["channels"] = channels mariadb_connection.commit() cursor.close() return response def showEpgs(): mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = (u"SELECT integrator, name, type, country, postalCode, epgId FROM epgs") cursor.execute(sql) if cursor.rowcount == 0: print "There are no epgs" else: print row_format = "{:>15}" * 4 header = row_format.format("Integrator", "Name", "Type", "Country", "Postal-code", "epgID") print header print "{}".format("=" * len(header)) for (integrator, name, type, country, postalCode, epgId) in cursor: print(row_format.format(integrator, name, type, country, postalCode, epgId)) mariadb_connection.commit() cursor.close() def showChannels(): mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = (u"SELECT callSign, channelId FROM channels") cursor.execute(sql) for (callSign, channelId) in cursor: print("callSign={}, id={}".format(callSign, channelId)) mariadb_connection.commit() cursor.close() def showProviders(): mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = (u"SELECT providerId, providerName, country, postalCode FROM providers") cursor.execute(sql) for (providerId, providerName, country, postalCode) in cursor: print("providerId={} name={} countr={} postalCode={}".format(providerId, providerName, country, postalCode)) mariadb_connection.commit() cursor.close() def showProviderChannels(providerId): mariadb_connection = mariadb.connect(user='root', password=system_defines.mariaDbPassword) cursor = mariadb_connection.cursor(buffered=True) cursor.execute("use zvepg") sql = (u"SELECT providerId, channels.channelId, callSign, affiliateCallSign, name, channelNumber, imageUrl FROM providerChannelMap " "JOIN channels ON providerChannelMap.channelId = channels.channelId WHERE providerId='{}' ").format(providerId) print sql cursor.execute(sql) for (providerId, channelId, callSign, affiliateCallSign, name, channelNumber, imageUrl) in cursor: print("channelId={}, callSign={}, affiliate={}, name={}, channelNumber={}, image={}".format(channelId, callSign, affiliateCallSign, name, channelNumber, imageUrl)) mariadb_connection.commit() cursor.close()