def getPKwithMostCount(schema, table, pKeys):
if len(pKeys) <= 0:
print "%s ERROR: No Primary Keys Found for %s" % (common.whoami(),
table)
return -1
curBiggestPKcount = 0
mainPK = ""
for pk in pKeys:
query = "SELECT COUNT(DISTINCT %s) FROM %s" % (pk, table)
PKcount = bdb.GetDBResultset(query, schema)
try:
# print "%s %s.%s count = %s" % (common.whoami(), table, pk, PKcount)
if PKcount > curBiggestPKcount:
curBiggestPKcount = PKcount
mainPK = pk
# print "Main PK is now (%s)" % (mainPK)
except:
print "%s ERROR: Crash happened in counting for %s" % (
common.whoami(), pk)
#Return the mainPK
return mainPK
def updateTableData(ws, schema, table, batchRows=200, insType="ignore"):
#Get the Data Update from Web Socket Server
dataUpdate = masyncGD.getDataUpdateList(ws, schema, table, batchRows, True)
try:
#Handle mismatched table construction
if dataUpdate[0] == 1146:
print "%s: Dropping and Creating a NEW %s table" % (
common.whoami(), table)
#Drop the current table
bdb.DropTable(schema, table)
#Create the new table based from Server
createTableFromWSS(ws, schema, table)
return
returnedRows = len(dataUpdate)
if returnedRows > 0:
#Push new data to Client's Database Table
retMsg = bdb.PushDBjson(dataUpdate, table, schema, batchRows,
"ignore")
#Check if there was an error in pushing the data to the target table
try:
#Handle "Unknown Column" in "field list"
if (retMsg[0] == 1054) or (retMsg[0] == 1146):
print "%s: Dropping and Creating a NEW %s table" % (
common.whoami(), table)
#Drop the current table
bdb.DropTable(schema, table)
#Create the new table based from Server
createTableFromWSS(ws, schema, table)
#Update Table
updateTableData(ws, schema, table, batchRows, insType)
#Handle "Syntax Error"
elif retMsg[0] == 1064:
pass
except Exception as e:
pass
if returnedRows >= batchRows:
updateTableData(ws, schema, table, batchRows, insType)
else:
return
else:
print "%s: Empty or Null returned rows" % (common.whoami())
except:
return
def getDataUpdateList(ws=None,
schema=None,
table=None,
limit=10,
withKey=True):
if not ws or not schema or not table:
print "%s ERROR: No ws|updateCmd value passed" % (common.whoami())
return None
latestPKval = getLatestPKValue(schema, table)
try:
#Catch mismatched table construction
if latestPKval[0] == 1146:
return latestPKval
except Exception as e:
pass
#TEMPORARY: catch if no PKval was returned
if latestPKval == -1:
print "%s TESTING: multiple primary keys in a table" % (
common.whoami())
return None
updateCmd = masyncSR.getDataUpdateCommand(schema, table, latestPKval,
limit)
# print "%s: %s" % (common.whoami(), updateCmd)
if updateCmd:
ws.send(updateCmd)
result = ws.recv()
# print "%s: Received '%s\n\n'" % (common.whoami(), result)
# Search for a smaller return limit in case buffer from server side is
# unable to handle the data amount returned
if result.find("false") == 0:
dataUpdate = getDataUpdateList(ws, schema, table, limit / 2,
withKey)
# Return data update
return dataUpdate
else:
dataUpdate = common.parseBasicList(result, withKey)
# Return data update
return dataUpdate
else:
print "%s ERROR: No request message passed" % (common.whoami())
return None
def checkPageLoaded(user):
result = ActionResult(whoami())
try:
find(user.driver, locator["appPortal"])
except:
result.fail("could not find appPortal element")
return result
def createTableFromWSS(ws, schema, table):
#Request SQL command for generating missing tables on local
# database of client
tableCreationCommand = masyncGD.getTableCreationCmd(ws, schema, table)
#Create Table
print "%s: Creating Table (%s)..." % (common.whoami(), table)
bdb.ExecuteQuery(tableCreationCommand, schema)
def getSchemaList(ws=None):
if not ws:
print "%s ERROR: No ws value passed" % (common.whoami())
return None
requestMsg = masyncSR.showSchemas()
if requestMsg:
ws.send(requestMsg)
result = ws.recv()
# print "%s: Received '%s'" % (common.whoami(), result)
schemaList = common.parseBasicList(result)
return schemaList
else:
print "%s ERROR: No request message passed" % (common.whoami())
return None
def getTableList(ws=None, schema=None):
if not ws or not schema:
print "%s ERROR: No ws|schema value passed" % (common.whoami())
return None
requestMsg = masyncSR.showTables(schema)
if requestMsg:
ws.send(requestMsg)
result = ws.recv()
# print "%s: Received '%s\n\n'" % (common.whoami(), result)
tableList = common.parseBasicList(result)
return tableList
else:
print "%s ERROR: No request message passed" % (common.whoami())
return None
def getTableCreationCmd(ws=None, schema=None, table=None):
if not ws or not schema or not table:
print "%s ERROR: No ws|schema|table value passed" % (common.whoami())
return None
requestMsg = masyncSR.getTableConstructionCommand(schema, table)
if requestMsg:
ws.send(requestMsg)
result = ws.recv()
# print "%s: Received '%s\n\n'" % (common.whoami(), result)
tableCreationCommand = common.parseTableCreationCommand(result)
return tableCreationCommand
else:
print "%s ERROR: No request message passed" % (common.whoami())
return None
def checkPageLoaded(user):
result = ActionResult(whoami())
try:
find(user.driver, locator["appPortal"])
except:
result.fail("could not find appPortal element")
return result
def findTableExistence(ws=None, schema=None, table=None):
if not ws or not schema or not table:
print "%s ERROR: No ws|schema|table value passed" % (common.whoami())
return None
requestMsg = masyncSR.findTable(schema, table)
if requestMsg:
ws.send(requestMsg)
result = ws.recv()
# print "%s: Received '%s\n\n'" % (common.whoami(), result)
doesTableExist = len(common.parseBasicList(result))
return doesTableExist
else:
print "%s ERROR: No request message passed" % (common.whoami())
return None
def showTables(schema=None):
if not schema:
msgError = "%s ERROR: No schema selected" % (common.whoami())
print msgError
return None
request = """{"dir":0,"action":"read","query":"SHOW TABLES",
"schema":"%s"}""" % (schema)
return request
def showPrimaryKey(schema, table):
if (not schema) or (not table):
msgError = "%s ERROR: No schema or table selected" % (common.whoami())
print msgError
return None
request = """{"dir":0,"action":"read",
"query":"SHOW INDEX FROM %s",
"schema":"%s"}""" % (table, schema)
return request
def modifierQuery(schema, query):
if (not schema) or (not query):
msgError = "%s ERROR: No schema or query selected" % (common.whoami())
print msgError
return None
request = """{"dir":1,"action":"modify",
"query":"%s",
"schema":"%s"}""" % (query, schema)
return request
def findTable(schema, table):
if (not schema) or (not table):
msgError = "%s ERROR: No schema or table selected" % (common.whoami())
print msgError
return None
request = """{"dir":0,"action":"read",
"query":"SHOW TABLES LIKE '%s'",
"schema":"%s"}""" % (table, schema)
return request
def getTableConstructionCommand(schema, table):
if (not schema) or (not table):
msgError = "%s ERROR: No schema or table selected" % (common.whoami())
print msgError
return None
request = """{"dir":0,"action":"read",
"query":"SHOW CREATE TABLE %s",
"schema":"%s"}""" % (table, schema)
return request
def compReadQuery(schema, query):
if (not schema) or (not query):
msgError = "%s ERROR: No schema or query selected" % (common.whoami())
print msgError
return None
request = """{"dir":0,"action":"read",
"query":"%s",
"schema":"%s"}""" % (query, schema)
# print request
return request
def syncRealTime(host, port):
url = "ws://%s:%s/" % (host, port)
delay = 5
print "%s: Starting Real Time Sync" % (common.whoami())
while True:
try:
result = ws.recv()
print "%s: Received '%s'" % (common.whoami(), result)
delay = 5
except Exception, e:
try:
print "%s: Connecting to Websocket Server (%s)..." % (
common.whoami(), url)
ws = create_connection(url)
except Exception:
print "%s: Disconnected! will attempt reconnection in %s seconds..." % (
common.whoami(), delay)
time.sleep(delay)
if delay < 10:
delay += 1
def syncSpecialClientToWSS(host, port, batchRows=200):
url = "ws://%s:%s/" % (host, port)
print "%s: Starting Start Up Sync" % (common.whoami())
ws = create_connection(url)
#List of blocked schemas
schemasBlocked = [
"information_schema", "mysql", "performance_schema", "phpmyadmin"
]
# TODO: Check all schemas allowed by WSS for syncing from Special Client
# Get list of tables from local database
queryShowLocalTables = "SHOW TABLES;"
allowedSchema = "senslopedb"
schema = allowedSchema
returnedRows = bdb.GetDBResultset(queryShowLocalTables, schema)
# Iterate through the list of tables
for row in returnedRows:
table = row[0]
updateTableOfWSS(ws, schema, table, batchRows)
# if table in ["agbsb","gndmeas","smsoutbox","lootb"]:
# # if table in ["agbsb","parta","sinb","sintb","tueta"]:
# # if table in ["agbsb","parta","sinb"]:
# # print "%s: %s" % (schema, table)
# # Check if table target exists on WSS
# doesExist = masyncGD.findTableExistence(ws, schema, table)
# if doesExist:
# print "\nEXISTS on WSS: %s" % (table)
# else:
# print "DOES NOT exist on WSS: %s" % (table)
# # Create table on WSS if target doesn't exist
# ret = masyncPD.pushTableCreation(ws, schema, table)
# # Collect latest data to be transferred to WSS from Special Client
# masyncGD.getInsertQueryForServerTX(ws, schema, table, batchRows)
# print "\nExisting: "
# print tablesExisting
# print "\nNon-existent: "
# print tablesNonExistent
# print "\n\n"
ws.close()
def interfaceUpdateTableOfWSS(host=None,
port=None,
schema=None,
table=None,
batchRows=1000,
rankOffset=None):
if (host == None) or (port == None) or (schema == None) or (table == None):
print "Error (%s): Please check your input values for host, port, schema or table" % (
common.whoami())
return -1
url = "ws://%s:%s/" % (host, port)
ws = create_connection(url)
# Update the selected database table from the selected schema
updateTableOfWSS(ws, schema, table, batchRows, rankOffset)
def handle_raw_input(self, input_data, META, content_length, boundary, encoding=None):
if 'X-Progress-ID' in self.request.GET:
self.logger.debug("Starting proccessing file with ID = %s in %s" % (self.request.GET['X-Progress-ID'],
whoami()) )
self.content_length = content_length
if 'X-Progress-ID' in self.request.GET:
self.progress_id = self.request.GET['X-Progress-ID']
self.logger.debug('X-Progress-ID exists and equals = %s' % (self.progress_id))
elif 'X-Progress-ID' in self.request.META:
self.progress_id = self.request.META['X-Progress-ID']
self.logger.debug('X-Progress-ID exists and equals = %s' % (self.progress_id))
if self.progress_id:
self.cache_key = "%s_%s" % (self.request.META['REMOTE_ADDR'], self.progress_id )
cache.set(self.cache_key, {
'length': self.content_length,
'uploaded' : 0
})
self.logger.debug('Cache key is set to %s and data uploaded 0', self.cache_key)
def constructPKjson(schema, table, pKey):
#Get the latest value of Main PK
query = """
SELECT %s
FROM %s
ORDER BY %s DESC
LIMIT 1""" % (pKey, table, pKey)
pkLatestValues = bdb.GetDBResultset(query, schema)
#Construct json string
try:
jsonPKandValstring = '{"%s":"%s"}' % (pKey, pkLatestValues[0][0])
except IndexError:
jsonPKandValstring = '{"%s":null}' % (pKey)
#Return JSON PK and Value/s
jsonPKandVal = json.loads(jsonPKandValstring)
print "%s: %s" % (common.whoami(), jsonPKandVal)
return jsonPKandVal
def pushTableCreation(ws=None, schema=None, table=None):
if not ws or not schema or not table:
print "%s ERROR: No ws|schema|table value passed" % (common.whoami())
return false
qShowCreateTable = "SHOW CREATE TABLE %s" % (table)
qTableCreation = (bdb.GetDBResultset(qShowCreateTable, schema))[0][1]
# print qTableCreation
requestMsg = masyncSR.modifierQuery(schema, qTableCreation)
# print requestMsg
if requestMsg:
ws.send(requestMsg)
result = ws.recv()
# print "Result: %s" % (result)
if result == "false":
print "Table (%s) creation on Web Server Failed" % (table)
return False
elif result == "true":
print "Table (%s) creation on Web Server SUCCEEDED!" % (table)
return True
def getDataUpdateCommand(schema, table, PKeysValsJson, limit = 1000):
if (not schema) or (not table):
msgError = "%s ERROR: No schema or table selected" % (common.whoami())
print msgError
return None
#TODO: Error message if PKeysValsJson is None or not a JSON
keys = []
values = []
for key,value in PKeysValsJson.iteritems():
# print("key: {} | value: {}".format(key, value))
keys.append(key)
values.append(value)
if not values[0]:
query = """
SELECT *
FROM %s
ORDER BY %s asc
LIMIT %s
""" % (table, keys[0], limit)
else:
query = """
SELECT *
FROM %s
WHERE %s >= '%s'
ORDER BY %s asc
LIMIT %s
""" % (table, keys[0], values[0], keys[0], limit)
# print "%s Query: %s" % (common.whoami(), query)
request = """{"dir":0,"action":"read",
"query":"%s",
"schema":"%s"}""" % (query, schema)
# print "%s Request: %s" % (common.whoami(), request)
return request
def syncStartUp(host, port, batchRows=200):
url = "ws://%s:%s/" % (host, port)
print "%s: Starting Start Up Sync" % (common.whoami())
ws = create_connection(url)
#List of blocked schemas
schemasBlocked = [
"information_schema", "mysql", "performance_schema", "phpmyadmin",
"bugtracker2"
]
#Get names of all schemas
schemas = masyncGD.getSchemaList(ws)
for schema in schemas:
if schema in schemasBlocked:
print "This is one of the blocked schemas"
continue
print schema
#Create schema if it is non-existent
if not bdb.DoesDatabaseSchemaExist(schema):
print "%s: Creating Schema (%s)..." % (common.whoami(), schema)
bdb.CreateSchema(schema)
#Get all table names per available schema
tables = masyncGD.getTableList(ws, schema)
tablesExisting = []
tablesNonExistent = []
for table in tables:
if bdb.DoesTableExist(schema, table):
# print "Table Exists: %s" % (table)
tablesExisting.append(table)
else:
# print "Table does NOT Exist: %s" % (table)
tablesNonExistent.append(table)
createTableFromWSS(ws, schema, table)
# if table in ["agbsb","blcb","gndmeas","lut_activities","membership","narratives","rain_noah"]:
# if table in ["agbsb","parta","sinb","sintb","tueta"]:
#if table in ["membership","narratives"]:
# updateTableData(ws, schema, table, batchRows, "ignore")
# #TEMPORARY: To be deleted after test
# if table == "smsinbox":
# updateTableData(ws, schema, table, batchRows, "ignore")
#TEMPORARY: To be deleted after test
# if table == "smsoutbox":
# start_time = timeit.default_timer()
# updateTableData(ws, schema, table, batchRows, "ignore")
# elapsed = timeit.default_timer() - start_time
# print "%s: Execution Time: %s" % (common.whoami(), elapsed)
# #TEMPORARY: To be deleted after test
# if table == "public_alert":
# updateTableData(ws, schema, table, batchRows, "ignore")
# #TEMPORARY: to be deleted after test
# if table == "agbsb":
# updateTableData(ws, schema, table, batchRows, "ignore")
# if table == "gndmeas":
# updateTableData(ws, schema, table, batchRows, "ignore")
# if table == "bartaw":
# updateTableData(ws, schema, table, batchRows, "ignore")
# if table == "rain_noah_812":
# updateTableData(ws, schema, table, batchRows, "ignore")
# Update Current Table
updateTableData(ws, schema, table, batchRows, "ignore")
# print "\nExisting: "
# print tablesExisting
# print "\nNon-existent: "
# print tablesNonExistent
# print "\n\n"
ws.close()
def getLatestPKValue(schema, table):
primaryKeys = bdb.GetTablePKs(schema, table)
numPKs = len(primaryKeys)
print "\n%s %s: Number of Primary Keys: %s" % (common.whoami(), table,
numPKs)
print "%s:" % (table),
PKs = []
try:
for pk in primaryKeys:
print "%s" % (pk[4]),
PKs.append(pk[4])
except:
errorDetails = primaryKeys
return errorDetails
if numPKs == 1:
return constructPKjson(schema, table, PKs[0])
elif numPKs > 1 and numPKs < 4:
#Identify the Main Primary Key (usually the timestamp)
mainPK = getPKwithMostCount(schema, table, PKs)
return constructPKjson(schema, table, mainPK)
else:
#There is a different procedure for tables with multiple PKs greater than 3
# print "\n(TODO) %s: %s Number of Primary Keys: %s" % (common.whoami(), table, numPKs)
# return -1
countTS = 0
countID = 0
pkTS = []
pkID = []
for pk in PKs:
#Check if there is a key with the word "timestamp" on it and use it as PK
if pk.find("timestamp") >= 0:
# print "%s: %s Use %s as Primary Key" % (common.whoami(), table, pk)
#contruct the PK Json using the timestamp as primary key
pkTS.append(pk)
#Check if there is a key with the word "id" on it and use it as PK
if pk.find("id") >= 0:
# print "%s: %s Use %s as Primary Key" % (common.whoami(), table, pk)
#contruct the PK Json using the id as primary key
pkTS.append(pk)
if len(pkTS) > 0:
#Identify the Main Primary Key (usually the timestamp)
mainPK = getPKwithMostCount(schema, table, pkTS)
mainPKjson = constructPKjson(schema, table, mainPK)
# print "%s: Main Primary Key JSON (%s)" % (common.whoami(), mainPKjson)
return mainPKjson
elif len(pkID) > 0:
#Identify the Main Primary Key (use ID if the timestamp is unavailable)
mainPK = getPKwithMostCount(schema, table, pkID)
mainPKjson = constructPKjson(schema, table, mainPK)
# print "%s: Main Primary Key JSON (%s)" % (common.whoami(), mainPKjson)
return mainPKjson
else:
print "%s ERROR: No Main Primary Key Found for %s!" % (
common.whoami(), table)
return -1