def compareTableStructure(schemaA, schemaB, tableNameA, tableNameB, dbList):
"""
Compares structure of tableA from schemaA with tableB from schemaB
@arg schemaA: connection string to the schema A
@arg schemaB: connection string to the schema B
@tableNameA: name of the table in schema A
@tableNameB: name of the table in schema B
@dbList: hash list of PysqlDb object (keys are A & B).
"""
tableDesc = {} # Store the current table desc for each schema (key is schema)
for schema, tableName in (("A", tableNameA), ("B", tableNameB)):
#BUG: format is ugly. use/merge with __displayTab algo ??
tableDesc[schema] = [" ".join([str(i) for i in line])
for line in desc(dbList[schema], tableName, None, False)[1]]
if not tableDesc[schema]:
raise PysqlException(_("Could not find table %s") % tableName)
if tableDesc["A"] == tableDesc["B"]:
result = None
else:
result = colorDiff(ndiff(tableDesc["A"], tableDesc["B"]))
return result
def compareTableData(schemaA, schemaB, tableNameA, tableNameB, dbList):
"""
Compares data of tableA from schemaA with tableB from schemaB
@arg schemaA: connection string to the schema A
@arg schemaB: connection string to the schema B
@tableNameA: name of the table in schema A
@tableNameB: name of the table in schema B
@dbList: hash list of PysqlDb object (keys are A & B).
"""
# Check that table structure (columns names & type) are similar
tableStruct = {} # Store table structure (columns names & tupe) for each schema (key is schema)
tablePK = {} # Store table primary key list for each schema (key is schema)
tableNCol = {} # Store table number of column for each schema (key is schema)
for schema, tableName in (("A", tableNameA), ("B", tableNameB)):
table = OraObject(dbList[schema].getUsername(), tableName)
table.guessInfos(dbList[schema])
if table.getType() == "TABLE":
# Get PK and number of columns
tablePK[schema] = table.getPrimaryKeys(dbList[schema])
tableNCol[schema] = table.getNumberOfColumns(dbList[schema])
# Get only column name (0) and column type (1)
tableStruct[schema] = [[i[0], i[1]] for i in table.getTableColumns(dbList[schema])]
else:
raise PysqlException(_("%s does not seem to be a table in %s") %
(tableName, dbList[schema].getConnectString()))
if tableStruct["A"] != tableStruct["B"]:
raise PysqlException(
_("Unable to compare data of tables that do not have same structure (columns name and type)"))
if tablePK["A"] == tablePK["B"] and tablePK["A"]: # identical and not None
order = "order by %s" % (", ".join(tablePK["A"]))
else:
order = "order by %s" % ", ".join(str(i + 1) for i in range(tableNCol["A"]))
for schema, tableName in (("A", tableNameA), ("B", tableNameB)):
# test cursor size. Should make a quick bench to choose the good one
dbList[schema].execute("select * from %s %s" % (tableName, order), fetch=False, cursorSize=10000)
result = {} # Store current fecth. Key is A or B
moreRows = {} # Flag to indicate there's more rows in cursor. Key is A or B
moreRows["A"] = True
moreRows["B"] = True
diff = [] # Store diff lines in this list
while moreRows["A"] and moreRows["B"]:
for schema in ("A", "B"):
result[schema], moreRows[schema] = dbList[schema].fetchNext()
if result[schema]:
#TODO: performance of this part is very very bad
result[schema] = [" ".join([str(i) for i in line])
for line in result[schema]]
for line in colorDiff(ndiff(result["A"], result["B"])):
if line[0] != " ":
if diff and line[2:] == diff[-1][2:]:
diff.pop() # simple double removing for one line decay only
else:
diff.append(line)
for sign, schema in (("-", "A"), ("+", "B")):
while moreRows[schema]:
result[schema], moreRows[schema] = dbList[schema].fetchNext()
result[schema] = [" ".join([str(i) for i in line])
for line in result[schema]] # This code should be factorised with above
diff.append("%s %s" % (sign, result[schema]))
# Make a second pass to remove doublon accross two resultset
#BUG: does not work in all case
oldSign = ""
newSign = ""
oldBuffer = []
newBuffer = []
newBlock = True # Flag to indicate we have to start a new matching block
i = 0
diff.append(" ") # Add a mark to allow final lines processing
toBeRemoved = [] # List of item index to be removed
for line in diff:
newSign = line[0]
if oldSign == newSign or newBlock:
# Append to new Buffer
newBuffer.append(line[2:])
newBlock = False
else:
if newBuffer == oldBuffer:
# Detect doublons
for j in range(len(newBuffer) * 2):
toBeRemoved.append(i - j - 1)
newBlock = True
# Changing to next block
oldBuffer = newBuffer
newBuffer = [line[2:]]
oldSign = newSign
i += 1
diff = [diff[i] for i in xrange(len(diff) - 1) if i not in toBeRemoved]
return diff
