1

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 ?

Ragavan Ashok
  • 21
  • 2
  • What is the problem when you apply the patch from version N to N+1, and do this step for all intermediate versions? So to get from N to N+3 you just do the N to N+1 step three times (N to N+1, N+1 to N+2, N+2 to N+3). – Progman Feb 10 '19 at 10:08
  • Thanks for your comment,It will be very difficult to have all the patches in hand for example one of the user using v_1.0 it might be very old build let say our new build is v_3.0 that means we needs to have v_1.0,v_1.1,v_1.2, v_1.3........v_3.0.Sometimes build can downgrade to one version to another. – Ragavan Ashok Feb 10 '19 at 18:00
  • But what would be the problem? You have the SQL scripts to get from one version to the next one and you keep applying the SQL scripts until you reach your destination version. – Progman Feb 10 '19 at 18:16
  • Its not possible to have all SQL scripts in hand. I can have current version of SQL scripts and destination version SQL script.I am expecting some algorithm to compare two DB schema and finding the difference as sql patch file – Ragavan Ashok Feb 11 '19 at 04:26

0 Answers0