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 getAllSMSoutbox(send_status='SENT', limit=20):
try:
query = """SELECT sms_id, timestamp_written, timestamp_sent, recepients
FROM smsoutbox
WHERE send_status = '%s'
AND timestamp_written IS NOT NULL
AND timestamp_sent IS NOT NULL
ORDER BY sms_id ASC LIMIT %d""" % (send_status, limit)
print query
result = bdb.GetDBResultset(query)
return result
except MySQLdb.OperationalError:
print 'getAllSMSoutbox DB Error',
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 getAllSMSinbox(web_flag='W', read_status='READ-SUCCESS', limit=20):
try:
#sms_id, timestamp, sender, message
query = """SELECT sms_id, timestamp, sim_num, sms_msg
FROM smsinbox
WHERE web_flag = '%s'
AND read_status = '%s'
ORDER BY sms_id ASC LIMIT %d """ % (web_flag, read_status,
limit)
print query
result = bdb.GetDBResultset(query)
return result
except MySQLdb.OperationalError:
print 'getAllSMSinbox DB Error',
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 getInsertQueryForServerTX(ws=None,
schema=None,
table=None,
limit=10,
rankOffset=None):
# Compare PK Values of Special Client (localhost) and Websocket Server
wssPKandVal = comparePKValuesSCandWSS(ws, schema, table, rankOffset)
# Collect latest data to be transferred to WSS from Special Client
if wssPKandVal:
mainPK = None
pkValLocalMax = None
for key, value in wssPKandVal.iteritems():
mainPK = key
pkValLocalMax = value
if pkValLocalMax:
qGetLocalData = "SELECT * FROM %s WHERE %s >= '%s' LIMIT %s" % (
table, mainPK, pkValLocalMax, limit)
else:
qGetLocalData = "SELECT * FROM %s LIMIT %s" % (table, limit)
# print "Query: %s" % (qGetLocalData)
result = bdb.GetDBResultset(qGetLocalData, schema)
# Compose SQL to be sent to the WSS from Local Data Gathered
queryHeader = "REPLACE INTO %s " % (table)
queryValues = "VALUES "
ctrRow = 0
numRows = len(result)
for data in result:
ctr = 0
numElements = len(data)
queryValues = queryValues + "("
for value in data:
try:
# TODO: Make sure to escape special characters
# test = "%s" % (value)
# esc_value = json.dumps(test)
# queryValues = queryValues + esc_value
queryValues = queryValues + "'%s'" % (value)
except TypeError:
queryValues = queryValues + "null"
ctr = ctr + 1
if ctr < numElements:
queryValues = queryValues + ","
else:
queryValues = queryValues + ")"
ctrRow = ctrRow + 1
if ctrRow < numRows:
queryValues = queryValues + ","
# Compose data insertion query
query = queryHeader + queryValues
# print query
# Transfer data from Special Client to WSS
requestMsg = masyncSR.modifierQuery(schema, query)
# print requestMsg
if requestMsg:
ws.send(requestMsg)
result = ws.recv()
# print "Result: %s" % (result)
if result == "false":
print "Table (%s) writing data on Web Server Failed" % (table)
# return False
elif result == "true":
print "Table (%s) writing data on Web Server SUCCEEDED!" % (
table)
# return True
# Repeat until latest of Special Client and WSS are the same
getInsertQueryForServerTX(ws, schema, table, limit)