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)