def addVersionerDatabase(dbConnection: Database, dbName, destinationFolder, logFile = None, logFilePosition = 0, actualVersion = 0):
"""
Add database to the versioned databases
Make record in the databaseVersioner database with actual version 0
If database has already versioned then delete old records
:param dbConnection: Database connetion
:param dbName: name of database to versioned
:param destinationFolder: folder to save log files, only abs path.
:param logFile: str of logFile for this database
:param logFilePosition: int number which declare last position in logFile
:return: VersionedDatabase
"""
if logFile is None:
logs = dbConnection.executeSimpleSQL("SHOW BINARY LOGS")
lastLog = logs[-1]
logFile = lastLog.get('Log_name')
if logFile is None:
raise BinnaryLogException('No binnary log file find')
if not dbConnection.isDatabaseExist(dbName):
raise DatabaseException("Database: '" + dbName + "' is not exist")
if not isDirectoryExist(destinationFolder):
raise FileException("Directory '" + destinationFolder + "' is not exist")
dataNewDatabase = {'db_name': dbName, 'actual_version': actualVersion, 'log_file': logFile,'log_file_position':logFilePosition, 'destination_folder': destinationFolder}
tablesForDatabase = dbConnection.executeSimpleSQL("select table_name from information_schema.tables where TABLE_SCHEMA='" + dbName + "'");
byNameInDb = dbConnection.executeSimpleSQL("SELECT id FROM " + VersionedDatabase.TABLE_NAME + " WHERE `db_name` = %s", (dbName), True)
try:
dbConnection.begin()
# delete od records about vesioning
if byNameInDb.get("id") is not None:
dbConnection.executeSQL("DELETE FROM " + VersionedTable.TABLE_NAME + " WHERE `fk_ver_databases_id` = " + str(byNameInDb.get("id")))
dbConnection.executeSQL("DELETE FROM " + VersionedDatabase.TABLE_NAME + " WHERE `id` = " + str(byNameInDb.get("id")))
# save data about versioning into database
retId = dbConnection.saveIntoTable(VersionedDatabase.TABLE_NAME, dataNewDatabase, withTransaction=False)
if retId is None:
raise DatabaseException('Save record into table \''+VersionedDatabase.TABLE_NAME+'\' faild')
for table in tablesForDatabase:
tableName = table['table_name'];
data = {'name': tableName, 'actual_version': 0, 'fk_ver_databases_id': retId}
dbConnection.saveIntoTable(VersionedTable.TABLE_NAME,data, withTransaction=False)
except Exception as e:
dbConnection.rollback()
raise e
dbConnection.commit()
versionedDatabase = VersionedDatabase(dbConnection, retId)
return versionedDatabase
def delete(self):
"""
Delete this record in table
In error raise DatabaseException
:return:
"""
if self._id is None:
raise DatabaseException('id of record is not set')
ret = self._db.deleteRecord(self._tableName, self._id)
if ret == False:
raise DatabaseException('Delete record with id \''+self._id+'\' into table \''+self._tableName+'\' faild')
def processSnapshot(self, dbName, destination):
"""
Dump all database into one file
:param dbName: name of dumped file
:param destination: path to save file
"""
destination = os.path.abspath(destination)
if not self.dbConnection.isDatabaseExist(dbName):
raise DatabaseException("Database: '" + dbName + "' is not exist")
isExist = isFileExist(destination)
if isExist:
result = TerminalCommand.runDialogYorN(
'File ' + destination + ' is exist. Do you want rewrite it')
if not result:
return
#get dump of all db
dump = DatabaseDump(dbName, self.dbConnection)
allSql = dump.dumpDatabase()
#get dir path and check path
dirPath = getDirectoryPathFromFilename(destination)
#write into file
dumpFile = DumpFile(destination)
dumpFile.writeIntoFile(allSql)
def getAllTables(self):
"""
Return all tables of this versioned database
:return: dict{tableName: VersionedTable}
"""
if self._id is not None:
return self.__tables
else:
raise DatabaseException('Versioned database is not exists')
def update(self):
"""
Update value by id
In error raise DatabaseException
"""
if self._id is None:
raise DatabaseException('id of record is not set')
self._data['id'] = self._id
ret = self._db.updateInTableById(self._tableName, self._id, self._data)
def addTable(self, tableName, version=None):
"""
Add table to versioned database
Method suppose that table exist
:param tableName: name of added table
:param version: version of added table, if not set then it is use actual verison of database
:return:
"""
if self.__tables.get(tableName) is not None:
raise DatabaseException('table \''+tableName+'\' are existed')
if version is None:
version = self.getActualVersion()
if self._id is None:
raise DatabaseException('database (fk_ver_databases_id) is not set')
data = {'name': tableName, 'actual_version': version, 'fk_ver_databases_id': self._id}
table = VersionedTable(self._db)
table.setValues(data)
table.save()
self.__tables[tableName] = table
def getTableByName(self, name):
"""
Return table class by table name
:param name: strng
:return: VersionedTable
"""
if self._id is not None:
return self.__tables.get(name)
else:
raise DatabaseException('Versioned element is not exists')
def getActualVersion(self):
"""
Get actual verion of database
in error raise DatabaseException
:return: int
"""
actVersion = (self.getValue('actual_version'))
try:
return int(actVersion);
except ValueError:
raise DatabaseException('Database actual version is not integer');
def processAddNonExist(self, dbName, destFolder):
"""
Add database to database versioner and made dump this database
:param dbName: str database name
:param destFolder:
"""
destFolder = os.path.abspath(destFolder)
if not self.dbConnection.isDatabaseExist(dbName):
raise DatabaseException("Database: '" + dbName + "' is not exist")
#check if dababase has been versioned
isExist = database.VersionedDatabase.isDatabaseVersioned(
self.dbConnection, dbName)
if isExist:
result = TerminalCommand.runDialogYorN(
"Database '" + dbName +
"' has been versioned. Are you want redeclare it?")
if not result:
return
# get last binary log and last position
binnaryLogNames = BinnaryLogNames(self.dbConnection)
lastBinnaryLogName = binnaryLogNames.getLastBinnaryLogName()
binnarylogs = BinnaryLogs([lastBinnaryLogName], dbName)
logDataString = binnarylogs.getAllLogData()
binnlogParser = BinnaryLogParser(logDataString)
lastBinnaryLogPosition = binnlogParser.getLastPosition()
#make and set new database
newVersion = 1
versionedDatabaseRecord = database.VersionedDatabase.addVersionerDatabase(
self.dbConnection, dbName, destFolder, lastBinnaryLogName,
lastBinnaryLogPosition, newVersion)
# clear and dump new database into logfiles
#todo: dump everything not only tables
LogFileBasic.removeAllFilesInDirectory(destFolder)
self.verDatabaseConnection = Database(
Config.getConfig('databaseHost'), Config.getConfig('databasePort'),
Config.getConfig("databaseUser"),
Config.getConfig("databasePassword"), dbName)
dump = DatabaseDump(dbName, self.verDatabaseConnection)
tablesDump = dump.dumpTables(versionedDatabaseRecord.getAllTables())
for tableName in tablesDump.keys():
print("Process : " + tableName)
logFileName = LogFile.makeLogFilePath(destFolder, tableName)
logFile = LogFile(logFileName)
logFile.insertVersionIntoFile([tablesDump.get(tableName)],
newVersion)
def __init__(self,dbConnection: Database, id = None, dbName = None):
"""
Constructor
if is set dbName then param id is ignored
:param dbConnection: Database connection
:param id: integer of record id
:param dbName: name of database
"""
if dbName != None:
myId = dbConnection.executeSimpleSQL("SELECT id FROM "+self.TABLE_NAME+" WHERE `db_name` = %s", (dbName), True)
id = myId.get('id')
if id is None:
raise DatabaseException("Database '"+dbName+"' is not versioned")
DatabaseTableRecord.__init__(self, dbConnection, self.TABLE_NAME, id)
self.__tables = {}
if id is not None:
ids = self._db.executeSimpleSQL("SELECT id, name FROM "+VersionedTable.TABLE_NAME+" WHERE `fk_ver_databases_id` = %s", (id))
for id in ids:
new = VersionedTable(dbConnection, id.get('id'))
self.__tables[id['name']] = new
def processMerge(self, dbName, fromVersion):
"""
Merge in log files
Merge fromVersion to lastest version into lastest version in log files
:param dbName: str name of db
:param fromVersion: str from version you want to merger
:return:
"""
if not self.dbConnection.isDatabaseExist(dbName):
raise DatabaseException("Database: '" + dbName + "' is not exist")
try:
fromVersion = int(fromVersion)
except ValueError:
raise Exception('Version is not a integer')
if fromVersion <= 0:
raise LogFileException("Version number can not be less than zero")
self.verDatabaseConnection = Database(
Config.getConfig('databaseHost'), Config.getConfig('databasePort'),
Config.getConfig("databaseUser"),
Config.getConfig("databasePassword"), dbName)
versionedDatabaseRecord = database.VersionedDatabase(self.dbConnection,
dbName=dbName)
if versionedDatabaseRecord.getActualVersion() < fromVersion:
pass
#raise LogFileException("Version number can not be bigger than actual version of database")
files = LogFile.getAllVerFileNameInDirectory(
versionedDatabaseRecord.getDestinationFolder())
#proces for every versed files
for fileName in files.keys():
elementName = files[fileName]
filePath = LogFile.makeLogFilePath(
versionedDatabaseRecord.getDestinationFolder(), elementName)
logFile = LogFile(filePath)
logFile.mergeFromVersion(fromVersion)
def __init__(self, host, port, user, password, db):
"""
Constructor of class Database
Connection is not autocommit
:param host: database host
:param port: number of connect port
:param user: databsae user
:param password: password
:param db: selected database name
"""
self.password = password
self.user = user
self.databaseName = db
self.port = port
self.host = host
self.__connection = pymysql.connect(host=host, port=port, user=user, passwd=password, db=db, autocommit=False, charset='utf8')
# don use bin log and foreign key check for this session
try:
self.executeSQL("SET sql_log_bin=0")
self.executeSQL("SET FOREIGN_KEY_CHECKS=0")
except:
DatabaseException("SET database variables faild")
pass
def processForceMake(self, dbName):
"""
Clear all log files and save into it dump of every element in lastest version
:param dbName:
:return:
"""
if not self.dbConnection.isDatabaseExist(dbName):
raise DatabaseException("Database: '" + dbName + "' is not exist")
self.verDatabaseConnection = Database(
Config.getConfig('databaseHost'), Config.getConfig('databasePort'),
Config.getConfig("databaseUser"),
Config.getConfig("databasePassword"), dbName)
versionedDatabaseRecord = database.VersionedDatabase(self.dbConnection,
dbName=dbName)
# get last binary log and last position
binnaryLogNames = BinnaryLogNames(self.dbConnection)
lastBinnaryLogName = binnaryLogNames.getLastBinnaryLogName()
binnarylogs = BinnaryLogs([lastBinnaryLogName], dbName)
logDataString = binnarylogs.getAllLogData()
binnlogParser = BinnaryLogParser(logDataString)
lastBinnaryLogPosition = binnlogParser.getLastPosition()
#version and des folder
destFolder = versionedDatabaseRecord.getDestinationFolder()
newVersion = versionedDatabaseRecord.getActualVersion()
versionedDatabaseRecord.updateMetaDataAboutTables()
# data to transaction
transData = {}
fileNames = LogFile.getAllVerFileNameInDirectory(destFolder)
for fileName in fileNames.keys():
path = LogFile.makeLogFilePath(
versionedDatabaseRecord.getDestinationFolder(),
fileNames[fileName])
logFile = LogFile(path)
transData[fileNames.get(fileName)] = logFile.getAllContent()
try:
#process
LogFile.removeAllVerFilesInDirecotry(destFolder)
dump = DatabaseDump(dbName, self.verDatabaseConnection)
tablesDump = dump.dumpTables(
versionedDatabaseRecord.getAllTables())
for tableName in tablesDump.keys():
logFileName = LogFile.makeLogFilePath(destFolder, tableName)
logFile = LogFile(logFileName)
logFile.insertVersionIntoFile([tablesDump.get(tableName)],
newVersion)
except Exception as e:
#On exception - transaction
LogFile.removeAllVerFilesInDirecotry(destFolder)
for name in transData.keys():
logFileName = LogFile.makeLogFilePath(destFolder, name)
logFile = LogFile(logFileName)
logFile.writeIntoFile(transData[name])
raise
versionedDatabaseRecord.setSomeData(lastBinnaryLogName,
lastBinnaryLogPosition, newVersion)
def processForceSet(self, dbName):
"""
Cleat database and Import everything in log files into database
Edited data will be overwrite
:param dbName: name of force set database
:return:
"""
if not self.dbConnection.isDatabaseExist(dbName):
raise DatabaseException("Database: '" + dbName + "' is not exist")
self.verDatabaseConnection = Database(
Config.getConfig('databaseHost'), Config.getConfig('databasePort'),
Config.getConfig("databaseUser"),
Config.getConfig("databasePassword"), dbName)
versionedDatabaseRecord = database.VersionedDatabase(self.dbConnection,
dbName=dbName)
#get ALL sql to process and get last version
files = LogFile.getAllVerFileNameInDirectory(
versionedDatabaseRecord.getDestinationFolder())
newLastVersion = 0
allSql = []
for fileName in files.keys():
elementName = files[fileName]
filePath = LogFile.makeLogFilePath(
versionedDatabaseRecord.getDestinationFolder(), elementName)
logFile = LogFile(filePath)
allSql.append(logFile.getAllSql())
if logFile.getLastVersion() > newLastVersion:
newLastVersion = logFile.getLastVersion()
#get last position of binnary logs and last name
binnaryLogNames = BinnaryLogNames(self.dbConnection)
lastBinnaryLogName = binnaryLogNames.getLastBinnaryLogName()
binnarylogs = BinnaryLogs([lastBinnaryLogName], dbName)
logDataString = binnarylogs.getAllLogData()
binnlogParser = BinnaryLogParser(logDataString)
lastBinnaryLogPosition = binnlogParser.getLastPosition()
# make databaseDump to transaction
dbDump = DatabaseDump(dbName, self.verDatabaseConnection)
dump = dbDump.dumpDatabase()
#proces in into transaction
try:
print("Importing Database...")
self.verDatabaseConnection.clearDatabase()
for sql in allSql:
self.verDatabaseConnection.executeSimpleSQL(sql)
except Exception as e:
self.verDatabaseConnection.clearDatabase()
self.verDatabaseConnection.executeSimpleSQL(dump)
raise
# update data in versioned databases
try:
versionedDatabaseRecord = VersionedDatabase.addVersionerDatabase(
self.dbConnection, dbName,
versionedDatabaseRecord.getDestinationFolder(),
lastBinnaryLogName, lastBinnaryLogPosition, newLastVersion)
except Exception as e:
self.verDatabaseConnection.clearDatabase()
self.verDatabaseConnection.executeSimpleSQL(dump)
raise
def processImport(self, dbName):
"""
Import logs into database
Local adjustments stay - dont delete it
:param dbName: name of imported database
"""
if not self.dbConnection.isDatabaseExist(dbName):
raise DatabaseException("Database: '" + dbName + "' is not exist")
self.verDatabaseConnection = Database(
Config.getConfig('databaseHost'), Config.getConfig('databasePort'),
Config.getConfig("databaseUser"),
Config.getConfig("databasePassword"), dbName)
versionedDatabaseRecord = database.VersionedDatabase(self.dbConnection,
dbName=dbName)
#import slq data
files = LogFile.getAllVerFileNameInDirectory(
versionedDatabaseRecord.getDestinationFolder())
newLastVersion = 0
for fileName in files.keys():
elementName = files[fileName]
filePath = LogFile.makeLogFilePath(
versionedDatabaseRecord.getDestinationFolder(), elementName)
logFile = LogFile(filePath)
if fileName == LogFile.OTHER_LOG_NAME_FULL:
#this is other file
newSql = logFile.getSqlFromVersion(
versionedDatabaseRecord.getActualVersion() + 1)
self.verDatabaseConnection.executeSimpleSQL(newSql)
continue
versionedTable = versionedDatabaseRecord.getTableByName(
elementName)
if versionedTable is None:
#this is new tables
newSql = logFile.getAllSql()
if not newSql:
continue
try:
self.verDatabaseConnection.executeSimpleSQL(newSql)
versionedDatabaseRecord.addTable(elementName,
logFile.getLastVersion())
except Exception as e:
self.verDatabaseConnection.deleteElementbyName(elementName)
versionedDatabaseRecord.removeTable(elementName)
raise
else:
#this element has been already in database
newSql = logFile.getSqlFromVersion(
versionedDatabaseRecord.getActualVersion() + 1)
try:
self.verDatabaseConnection.executeSimpleSQL(newSql)
except Exception as e:
for fileName in files.keys():
elementName = files[fileName]
self.__revertByElementName(elementName,
versionedDatabaseRecord)
raise
if logFile.getLastVersion() > newLastVersion:
newLastVersion = logFile.getLastVersion()
#find last version
newVersion = versionedDatabaseRecord.getActualVersion()
if newLastVersion > versionedDatabaseRecord.getActualVersion():
newVersion = newLastVersion
# save metadata
#save only new version
versionedDatabaseRecord.setSomeData(version=newVersion)
def processUp(self, dbName):
"""
Make new database revision
:return:
"""
if not self.dbConnection.isDatabaseExist(dbName):
raise DatabaseException("Database: '" + dbName + "' is not exist")
self.verDatabaseConnection = Database(
Config.getConfig('databaseHost'), Config.getConfig('databasePort'),
Config.getConfig("databaseUser"),
Config.getConfig("databasePassword"), dbName)
versionedDatabaseRecord = database.VersionedDatabase(self.dbConnection,
dbName=dbName)
binnaryLogNames = BinnaryLogNames(self.dbConnection)
listOfBinneryLogNames = binnaryLogNames.getBinnaryLogsNamesOlderThan(
versionedDatabaseRecord.getLogFile())
lastBinnaryLogName = binnaryLogNames.getLastBinnaryLogName()
lastBinnaryLogPosition = versionedDatabaseRecord.getLogFilePosition()
#check if first log name have name like versionedDatabaseRecord bin name
if versionedDatabaseRecord.getLogFile() not in listOfBinneryLogNames:
lastBinnaryLogPosition = 0
print(
'Warning: Binnary Logs is out of date. Prease increase expire_logs_days'
)
res = TerminalCommand.runDialogYorN(
'All data for version is unavailable. Some data in new version can be missing. Do you want continue'
)
if not res:
return
#check if version in logFiles is same in db
destination = versionedDatabaseRecord.getDestinationFolder()
actualVersion = versionedDatabaseRecord.getActualVersion()
fileNames = LogFile.getAllVerFileNameInDirectory(destination)
for name in fileNames:
logFile = LogFile(
LogFile.makeLogFilePath(destination, fileNames[name]))
fileVersion = logFile.getLastVersion()
if fileVersion > actualVersion:
raise LogFileException(
'Versined files in directory \'' + destination +
"' have newer version then data in database. Please import data into datababe before make new version"
)
#get data from logs
binnarylogs = BinnaryLogs(listOfBinneryLogNames, dbName)
binnarylogs.setStartPositionFirstLog(lastBinnaryLogPosition)
logDataString = binnarylogs.getAllLogData()
#parse data
binnlogParser = BinnaryLogParser(logDataString)
lastBinnaryLogPosition = binnlogParser.getLastPosition()
#increment actual version - dont save
actualVersion = versionedDatabaseRecord.getActualVersion()
actualVersion = actualVersion + 1
#flush sql into log file
result = binnlogParser.getLogParserResult()
#save to make transaction
logFileData = {}
toDelete = []
try:
#process
for elementName in result.getAllUpdatedElementNames():
elementData = versionedDatabaseRecord.getTableByName(
elementName)
logFileName = LogFile.makeLogFilePath(
versionedDatabaseRecord.getDestinationFolder(),
elementName)
if elementName in result.getCreated():
logFile = LogFile(logFileName)
elif result.getRenameOldNameByNewName(elementName) is not None:
oldName = result.getRenameOldNameByNewName(elementName)
oldLogFileName = LogFile.makeLogFilePath(
versionedDatabaseRecord.getDestinationFolder(),
oldName)
oldLogFile = LogFile(oldLogFileName)
logFileData[oldLogFileName] = oldLogFile.getAllContent()
logFile = LogFile(logFileName)
toDelete.append(logFileName)
logFile.writeIntoFile(oldLogFile.getAllContent())
oldLogFile.deleteFile()
elif versionedDatabaseRecord.getTableByName(elementName):
logFile = LogFile(logFileName)
logFileData[logFileName] = logFile.getAllContent()
else:
#someting wrong
continue
logFile.appendVersionIntoFile(
result.getAllQueriesByName(elementName), actualVersion)
#do others
if result.getOtherQueries():
logFileName = LogFile.makeLogFilePath(
versionedDatabaseRecord.getDestinationFolder(),
LogFile.OTHER_LOG_NAME)
logFile = LogFile(logFileName)
logFileData[logFileName] = logFile.getAllContent()
logFile.appendVersionIntoFile(result.getOtherQueries(),
actualVersion)
#save metadata
if (result.haveSomeData()):
versionedDatabaseRecord.setSomeData(lastBinnaryLogName,
lastBinnaryLogPosition,
actualVersion)
versionedDatabaseRecord.updateMetaDataAboutTables()
else:
versionedDatabaseRecord.setSomeData(lastBinnaryLogName,
lastBinnaryLogPosition)
versionedDatabaseRecord.updateMetaDataAboutTables()
print(
"No data to flush into log files. New version was not make"
)
except Exception as e:
#make transaction
for fileName in toDelete:
logFile = LogFile(fileName)
logFile.deleteFile()
for fileName in logFileData.keys():
logFile = LogFile(fileName)
logFile.writeIntoFile(logFileData[fileName])
raise
def processAddExists(self, dbName, sourceFolder):
"""
Import exist versioned logs into local database
:param dbName:
:param sourceFolder: str path to folder where is logs
:return:
"""
destFolder = os.path.abspath(sourceFolder)
if self.dbConnection.isDatabaseExist(dbName) is False:
raise DatabaseException('Database \'' + dbName +
'\' is not exist. You have to make it')
if not isDirectoryExist(destFolder):
raise FileException("Directory '" + destFolder + "' is not exist")
self.verDatabaseConnection = Database(
Config.getConfig('databaseHost'), Config.getConfig('databasePort'),
Config.getConfig("databaseUser"),
Config.getConfig("databasePassword"), dbName)
# check if dababase has been versioned
isExist = database.VersionedDatabase.isDatabaseVersioned(
self.dbConnection, dbName)
if isExist:
result = TerminalCommand.runDialogYorN(
"Database '" + dbName +
"' has been versioned. Are you want redeclare it?")
if not result:
return
# get last binary log and last position
binnaryLogNames = BinnaryLogNames(self.dbConnection)
lastBinnaryLogName = binnaryLogNames.getLastBinnaryLogName()
binnarylogs = BinnaryLogs([lastBinnaryLogName], dbName)
logDataString = binnarylogs.getAllLogData()
binnlogParser = BinnaryLogParser(logDataString)
lastBinnaryLogPosition = binnlogParser.getLastPosition()
#make dump of database
dbDump = DatabaseDump(dbName, self.verDatabaseConnection)
dump = dbDump.dumpDatabase()
#cleart db
self.verDatabaseConnection.clearDatabase()
#make and import database
logFilesNames = LogFile.getAllVerFileNameInDirectory(destFolder)
lastVersion = 0
otherSql = ""
for fileName in logFilesNames.keys():
print("Process : " + logFilesNames[fileName])
logFile = LogFile(
LogFile.makeLogFilePath(destFolder, logFilesNames[fileName]))
if logFile.getLastVersion() > lastVersion:
lastVersion = logFile.getLastVersion()
if LogFile.OTHER_LOG_NAME_FULL in fileName:
otherSql = logFile.getAllSql()
else:
#import data into database
try:
allSql = logFile.getAllSql()
if allSql:
self.verDatabaseConnection.executeSimpleSQL(allSql)
except Exception as e:
#special transtaction for ddl
self.verDatabaseConnection.clearDatabase()
self.verDatabaseConnection.executeSimpleSQL(dump)
raise
#do OtherSql
try:
self.verDatabaseConnection.executeSimpleSQL(otherSql)
except Exception as e:
self.verDatabaseConnection.clearDatabase()
self.verDatabaseConnection.executeSimpleSQL(dump)
raise e
#set database to versioning
versionedDatabaseRecord = database.VersionedDatabase.addVersionerDatabase(
self.dbConnection, dbName, destFolder, lastBinnaryLogName,
lastBinnaryLogPosition, lastVersion)