Systems and methods for synchronizing database schema

Patent metadata

Field	Value
Publication number	US-2018096043-A1
Application number	US-201715724333-A
Country	US
Kind code	A1
Filing date	Oct 4, 2017
Priority date	Oct 5, 2016
Publication date	Apr 5, 2018
Grant date	—

Abstract

Methods and systems for synchronizing a database schema (or data structure) of a source database to a target database are disclosed. A source data structure definition is generated, including definitions of each object in the source database structure. A target database structure is generated, including definitions of each object in the source database structure. Object definitions are compared between the source database structure and the target database structure. A data definition language change instruction sequence is generated that, when executed, will modify the target database structure to correspond to the data structure definition. The change instruction sequence is executed on the target database.

First claim

1 . A method for updating a target database structure of a target database based on a source data structure of a source database, the target database structure and the source database structure each having one or more objects, the method comprising:
generating a data structure definition, the data structure definition having an object definition for each of the one or more objects of the source data structure; comparing each object definition to the one or more objects in the target database structure; generating one or more creation actions for each object definition that does not have a corresponding object in the target database structure; generating one or more modification actions for each object definition that differs from a corresponding object in the target database structure; generating one or more deletion actions for each object in the target database structure that does not have a corresponding object definition; generating a data definition language change instruction sequence from the generated creation actions, modification actions, and deletion actions that, when executed, will modify the target database structure to correspond to the data structure definition; executing the change instruction sequence on the target database; and providing an output regarding a status of the executing of the change instruction sequence.

2 . The method of claim 1 , further comprising:
determining a set of data items in the target database that will be affected by the one or more change actions or one or more deletion actions.

3 . The method of claim 2 , further comprising:
reporting the set of data items in the target database that will be affected by the one or more change actions or one or more deletion actions to a user, and requesting confirmation from the user before generating the change instruction sequence.

4 . The method of claim 1 , further comprising:
determining a difference count from the change instruction sequence, wherein executing the change instruction sequence on the target database includes decrementing the difference count for each change instruction executed; and further wherein providing an output regarding the status of the execution of the change instruction sequences includes reporting the current different count.

5 . The method of claim 1 , further comprising:
generating a data definition language backout instruction sequence from the change instruction sequence whereby executing the backout instruction sequence will reverse any changes made by execution of the change instruction sequence.

6 . The method of claim 1 , wherein the change instruction sequence is generated in an optimized order to prevent failure of the execution of the change instruction sequence.

7 . The method of claim 1 , wherein each of the one or more object definitions includes one or more of a name, a type, a size, a storage space name, or a locale indicator.

8 . The method of claim 7 , wherein the modification instructions for each object definition that differs from a corresponding object in the target database by name include a temporary renaming action to modify the name of the corresponding object in the target database to a temporary name and a final renaming action to modify the name of the corresponding object in the target database to the name of the object definition.

9 . The method of claim 8 , wherein the change instruction sequence is generated to execute all temporary renaming actions before any final renaming actions.

10 . The method of claim 1 , wherein the data structure definition is associated with a database structure version.

11 . A database structure update system for updating a target database structure of a target database based on a source data structure of a source database, the target database structure and the source database structure each having one or more objects, the system comprising:
a data structure definition extractor communicatively coupled to the source database and configured to generate a data structure definition, the data structure definition having an object definition for each of the one or more objects of the source data structure; a comparator communicatively coupled to the target database and configured to receive the data structure definition, compare each object definition to the one or more objects in the target database structure, and to:
generate one or more creation actions for each object definition that does not have a corresponding object in the target database structure,
generate one or more modification actions for each object definition that differs from a corresponding object in the target database structure, and
generate one or more deletion actions for each object in the target database structure that does not have a corresponding object definition;
a data definition language generator configured to generate a data definition language change instruction sequence from the generated actions instructions, modification actions, and deletion actions that, when executed will modify the target database structure to correspond to the data structure definition; and a deployment engine communicatively coupled to the target database and to a user interface and configured to execute the change instruction sequence on the target database, and to provide an output to the user interface regarding the status of the execution of the change instruction sequence.

12 . The system of claim 11 , wherein the comparator is further configured to determine a set of data items in the target database that will be affected by the one or more change actions or one or more deletion actions.

13 . The system of claim 12 , wherein the comparator is further configured to report the set of data items in the target database that will be affected by the one or more change actions or one or more deletion actions to the user.

14 . The system of claim 11 , wherein the comparator is further configured to determine a difference count from the change instruction sequence, and
wherein the deployment engine is further configured to decrement the difference count for each change instruction executed; and further wherein providing an output regarding the status of the execution of the change instruction sequences includes reporting the current different count.

15 . The system of claim 11 , wherein the data definition language generator is further configured to:
generate a data definition language backout instruction sequence from the change instruction sequence whereby executing the backout instruction sequence will reverse any changes made by execution of the change instruction sequence.

16 . The system of claim 11 , wherein the change instruction sequence is generated in an optimized order to prevent failure of the execution of the change instruction sequence.

17 . The system of claim 11 , wherein each of the one or more object definitions includes one or more of a name, a type, a size, a storage space name, or a locale indicator.

18 . The system of claim 17 , wherein the modification instructions for each object definition that differs from a corresponding object in the target database by name include a temporary renaming action to modify the name of the corresponding object in the target database to a temporary name and a final renaming action to modify the name of the corresponding object in the target database to the name of the object definition.

19 . The system of claim 18 , wherein the data definition language generator is further configured to generate the change instructio

Assignees

• Wal Mart Stores Inc

Inventors

• Ledbetter Joshua
• Austin Daniel Bryan

Classifications

• G06F16/213

  with details for schema evolution support · CPC title

• G06F16/27 Primary

  Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor · CPC title

• G06F16/2379

  Updates performed during online database operations; commit processing · CPC title

• G06F17/30377

  Physics · mapped topic

• G06F17/30575 Primary

  Physics · mapped topic