How to upgrade MySQL database schema in respective to sprint releases?

31
February 11, 2019, at 07:00 AM

let say we have a Qt/qml based desktop application, this is using mysql as local db .

Note: We will be releasing the new build every 2 weeks.

Here the Problem is, There is a chance of changes in local db like new column added, removal of some fields, new table added, deleted. So due to this changes all the time we are updating entire DB. we can say deleting the entire database and updating with new schema. so every time we are losing old records, because we are not updating only having changes in schema level.

Direct straight forward answer: We can compare two schema with (old_release.sql - new_release.sql) their changes, based on the changes result we can create one alter query instead of entire table changes.This can be possible for current build to immediate next build( n to n+1 ) or current build immediate lower version build ( n to n-1 ). But not possible for [n to n+3] or [n to n-5]

But here the problem is, Let say one client is using version_1.0 build, we wants to upgrade him to version_1.1 means no problem every direct release we have alter query file.We can use this to solve. But if the client is using version_1.0 and we wants to upgrade him to version_2.5 means we does not have any alter query compatibility between version_1.0 - version_2.5 we only having version version_2.4 - version_2.5. That means we can have, n to n+1 n to n-1

not for, n to n+2.

Note: some point there is chance of revert also e.g - version_2.5 to version_1.0

So Question is, Is there any way to understand differences between two schema's ? Any tool ? Any algorithm ?

READ ALSO
Grant MySQL permissions on current database?

Grant MySQL permissions on current database?

I can grant permissions with a static database name in the GRANT statement

14
MySql Login error - Domain name added automatically with the user name

MySql Login error - Domain name added automatically with the user name

I am on Windows 10 desktop with a Cloudera VM running on itFrom inside the coudera vm Linux OS, I can login to MySQL with:

29
Using junction tables in PHP and MySQL to categorize and include and exclude categories

Using junction tables in PHP and MySQL to categorize and include and exclude categories

I'm trying to analyze tweets using manually assigned categoriesEverything's stored in a MySQL database

22