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 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