System, method and computer program product for database change management
Database servers may maintain a database according to a database schema. A database change management system can include a profile service configured to collect database profile information and a simulation service configured to receive a set of changes to be simulated for the database and simulate an application of the set of changes to the database. A forecast service can be configured to receive a result of a simulation from the simulation service and database profile information and generate a report indicative of a prediction of a failure or success of an implementation of the set of changes.
1. A computer program product comprising a non-transitory, computer-readable medium storing computer-executable instructions, the computer-executable instructions comprising instructions for:
opening a connection to a target database;
collecting a snapshot of a current state of a database schema for the target database, the snapshot including a snapshot set of changes;
receiving a set of proposed changes to the database schema of the target database;
building an in-memory model of the database schema of the target database based on the snapshot, the in-memory model of the database schema comprising a set of model objects which are related according to the database schema, wherein building the in-memory model comprises loading an initial model in memory, the initial model initialized as an empty database model, and applying the snapshot set of changes to the in-memory model to model the current state of the target database, the applying starting with the initial model;
accessing a set of rules comprising rules applied by the target database;
simulating an application of the set of proposed changes to the target database to create an updated version of the in-memory model, the simulating comprising;
selecting a change from the set of proposed changes as a selected change, and
mapping the selected change to a command on the in-memory model to update the in-memory model according to the selected change;
applying the set of rules to the updated version of the in-memory model to determine if the set of proposed changes violates the set of rules and, based on a determination that the command violates the set of rules, logging an error in association with the selected change and a state of the in-memory model; and
determining if a result of simulating the application of the set of proposed changes includes at least one error indicative of failure, and generating a report indicative of a prediction of a failure if the result of simulating the application of the set of proposed changes includes at least one error indicative of failure, and deploying the set of proposed changes to a target environment if the result of simulating the application of the set of proposed changes does not include at least one error indicative of failure.
2. The computer program product of claim 1 , wherein the set of model objects comprises:
one or more table objects representing tables of the target database;
one or more column objects representing columns of the target database, each column object related in the in-memory model to a corresponding table object according to the database schema;
one or more primary key constraint objects representing primary key constraints, each primary key constraint object related in the in-memory model to a column object representing a column of the target database to which a corresponding primary key constraint applies;
one or more foreign key constraint objects representing foreign key constraints, each foreign key constraint object related in the in-memory model to a column object representing a column of the target database to which a corresponding foreign key constraint applies; and
one or more data constraint objects representing data constraints, each data constraint object related in the in-memory model to a column object representing a column to which a corresponding data constraint applies.
3. The computer program product of claim 1 , wherein the computer-executable instructions comprise instructions for collecting database profile information comprising row counts of tables, wherein the in-memory model of the database schema incorporates at least a portion of the database profile information.
4. The computer program product of claim 1 , wherein collecting the snapshot of the current state of the database schema for the target database comprises querying the target database for the current state of the database schema.
5. The computer program product of claim 1 , wherein the set of rules further comprises one or more additional user defined rules.
6. The computer program product of claim 1 , wherein simulating the application of the set of proposed changes further comprises manipulating the in-memory model according to the selected change to update the in-memory model of the database schema even if the application of the selected change would result in an error.
7. The computer program product of claim 1 , wherein simulating the application of the set of proposed changes to the in-memory model of the database schema further comprises iteratively repeating the selecting, mapping and determining for each change in the set of proposed changes until all the changes in the set of proposed changes have been used as the selected change.
8. The computer program product of claim 1 , wherein the computer-executable instructions comprise instructions for determining that the command violates the set of rules based on the command modifying the in-memory model of the database schema in violation of a set of constraints modeled in the in-memory model of the database schema.
9. The computer program product of claim 1 , wherein deploying the set of proposed changes to the target environment comprises issuing SQL commands to the target environment.
10. A method comprising:
opening a connection to a target database;
collecting a snapshot of a current state of a database schema for the target database, the snapshot including a snapshot set of changes;
receiving a set of proposed changes to the database schema of the target database;
building an in-memory model of the database schema of the target database based on the snapshot, the in-memory model of the database schema comprising a set of model objects which are related according to the database schema, wherein building the in-memory model comprises loading an initial model in memory, the initial model initialized as an empty database model, and applying the snapshot set of changes to the in-memory model to model the current state of the target database, the applying starting with the initial model;
accessing a set of rules comprising rules applied by the target database;
simulating an application of the set of proposed changes to the target database to create an updated version of the in-memory model, the simulating comprising;
selecting a change from the set of proposed changes as a selected change, and
mapping the selected change to a command on the in-memory model to update the in-memory model according to the selected change;
applying the set of rules to the updated version of the in-memory model to determine if the set of proposed changes violates the set of rules and, based on a determination that the command violates the set of rules, logging an error in association with the selected change and a state of the in-memory model; and
determining if a result of simulating the application of the set of proposed changes includes at least one error indicative of failure, and generating a report indicative of a prediction of a failure if the result of simulating the application of the set of proposed changes includes at least one error indicative of failure, and deploying the set of proposed changes to a target environment if the result of simulating the application of the set of proposed changes does not include at least one error indicative of failure.
11. The method of claim 10 , wherein the set of model objects comprises:
one or more table objects representing tables of the target database;
one or more column objects representing columns of the target database, each column object related in the in-memory model to a corresponding table object according to the database schema;
one or more primary key constraint objects representing primary key constraints, each primary key constraint object related in the in-memory model to a column object representing a column of the target database to which a corresponding primary key constraint applies;
one or more foreign key constraint objects representing foreign key constraints, each foreign key constraint object related in the in-memory model to a column object representing a column of the target database to which a corresponding foreign key constraint applies; and
one or more data constraint objects representing data constraints, each data constraint object related in the in-memory model to a column object representing a column to which a corresponding data constraint applies.
12. The method of claim 10 , further comprising collecting database profile information comprising row counts of tables, wherein the in-memory model of the database schema incorporates at least a portion of the database profile information.
13. The method of claim 10 , wherein collecting the snapshot of the current state of the database schema for the target database comprises querying the target database for the current state of the database schema.
14. The method of claim 10 , wherein the set of rules further comprises one or more additional user defined rules.
15. The method of claim 10 , wherein simulating the application of the set of proposed changes further comprises manipulating the in-memory model according to the selected change to update the in-memory model of the database schema even if the application of the selected change would result in an error.
16. The method of claim 10 , wherein simulating the application of the set of proposed changes to the in-memory model of the database schema further comprises iteratively repeating the selecting, mapping and determining for each change in the set of proposed changes until all the changes in the set of proposed changes have been used as the selected change.
17. The method of claim 10 , further comprising determining that the command violates the set of rules based on the command modifying the in-memory model of the database schema in violation of a set of constraints modeled in the in-memory model of the database schema.
18. The method of claim 10 , wherein deploying the set of proposed changes to the target environment comprises issuing SQL commands to the target environment.
19. A system comprising:
a processor; and
a non-transitory, computer-readable medium storing computer-executable instructions, the computer-executable instructions comprising instructions for:
opening a connection to a target database;
collecting a snapshot of a current state of a database schema for the target database, the snapshot including a snapshot set of changes;
receiving a set of proposed changes to the database schema of the target database;
building an in-memory model of the database schema of the target database based on the snapshot, the in-memory model of the database schema comprising a set of model objects which are related according to the database schema, wherein building the in-memory model comprises loading an initial model in memory, the initial model initialized as an empty database model, and applying the snapshot set of changes to the in-memory model to model the current state of the target database, the applying starting with the initial model;
accessing a set of rules comprising rules applied by the target database;
simulating an application of the set of proposed changes to the target database to create an updated version of the in-memory model, the simulating comprising;
selecting a change from the set of proposed changes as a selected change, and
mapping the selected change to a command on the in-memory model to update the in-memory model according to the selected change;
applying the set of rules to the updated version of the in-memory model to determine if the set of proposed changes violates the set of rules and, based on a determination that the command violates the set of rules, logging an error in association with the selected change and a state of the in-memory model; and
determining if a result of simulating the application of the set of proposed changes includes at least one error indicative of failure, and generating a report indicative of a prediction of a failure if the result of simulating the application of the set of proposed changes includes at least one error indicative of failure, and deploying the set of proposed changes to a target environment if the result of simulating the application of the set of proposed changes does not include at least one error indicative of failure.
20. The system of claim 19 , wherein collecting the snapshot of the current state of the database schema for the target database comprises querying the target database for the current state of the database schema.