Database schema upgrade validation using denormalization
Some embodiments of a system and a method to validate database schema upgrade using denormalization have been presented. For instance, a database deployment engine may upgrade a database from a first version to a second version. The database upgraded stores a set of entities. By applying denormalization to both the upgraded database and a reference database (which contains a newly installed second version of the database), a database validation engine may reduce false positives during validation of the database schema upgrade scripts usable by customers to upgrade their database schema to the latest version.
1. A computer-implemented method comprising:
upgrading, by a processing device executing a database schema upgrade script, a database schema of a database from a first version to a second version, the database storing a plurality of entities;
creating, by the processing device, temporary denormalized views of each of the second version of the database and a reference database, the temporary denormalized views each comprising relations of a plurality of entities in the corresponding database without surrogate keys of the plurality of entities, wherein the reference database comprises a newly installed database of the second version; and
validating, by the processing device, the plurality of entities and the relations of the plurality of entities, the validating comprising:
comparing the plurality of entities in the temporary denormalized views for differences; and
comparing the relations of the plurality of entities in the temporary denormalized views to reduce false positives during validation of the database schema upgrade script.
2. The method of claim 1 , wherein comparing the relations of the plurality of entities in the temporary denormalized views further comprises:
executing, by the processing device, a set of one or more queries against the upgraded database to produce a result comprising the plurality of entities and relations of the plurality of entities, without surrogate keys of the plurality of entities; and
comparing, by the processing device, the relations of the plurality of entities in the result with relations of a corresponding plurality of entities in the reference database.
3. The method of claim 1 , wherein a first plurality of surrogate keys of the plurality of entities in the database after the upgrading are out of sync with a second plurality of surrogate keys of a corresponding plurality of entities in the reference database.
4. The method of claim 1 , wherein at least one of the plurality of entities is created after installation of the first version of the database, and before the upgrading of the database to the second version.
5. The method of claim 1 , wherein the database comprises a relational database.
6. An apparatus comprising:
a memory;
a processing device communicably coupled to the memory;
a database schema upgrade engine executable from the memory by the processing device, the database schema upgrade engine configured to upgrade a database schema of a database from a first version to a second version, the database storing a plurality of entities; and
a database validation engine executable from the memory by the processing device, the database validation upgrade engine configured to:
create temporary denormalized views of each of the second version of the database and a reference database, the temporary denormalized views each comprising relations of a plurality of entities in the corresponding database while disregarding surrogate keys of the plurality of entities, wherein the reference database comprises a newly installed database of the second version; and
validate the plurality of entities and the relations of the plurality of by:
comparing the plurality of entities in the temporary denormalized views for differences; and
comparing the relations of the plurality of entities in the temporary denormalized views to reduce false positives during validation of the database schema upgrade script.
7. The apparatus of claim 6 , wherein the database validation engine is further configured to:
execute a set of one or more queries against the upgraded database to produce a result comprising the plurality of entities and relations of the plurality of entities, but without surrogate keys of the plurality of entities; and
compare the relations of the plurality of entities in the result with relations of a corresponding plurality of entities in the reference database.
8. The apparatus of claim 6 , wherein the surrogate keys of the plurality of entities in the database after the upgrading are out of sync with a second plurality of surrogate keys of a corresponding plurality of entities in the reference database.
9. The apparatus of claim 6 , wherein at least one of the plurality of entities is created after installation of the first version of the database, and before the upgrading of the database to the second version.
10. The apparatus of claim 6 , wherein the database comprises a relational database.
11. A non-transitory computer-readable storage medium embodying instructions that, when executed by a processing device, will cause the processing device to perform a method comprising:
executing a database schema upgrade script to upgrade a database schema of a database from a first version to a second version, the database storing a plurality of entities;
creating, by the processing device, temporary denormalized views of each of the second version of the database and a reference database, the temporary denormalized views each comprising relations of a plurality of entities in the corresponding database without surrogate keys of the plurality of entities, wherein the reference database comprises a newly installed database of the second version; and
validating, by the processing device, the plurality of entities and the relations of the plurality of entities, the validating comprising:
comparing the plurality of entities in the temporary denormalized views for differences; and
comparing the relations of the plurality of entities in the temporary denormalized views to reduce false positives during validation of the database schema upgrade script.
12. The non-transitory computer-readable storage medium of claim 11 , wherein the method further comprises:
executing a set of one or more queries against the upgraded database to produce a result comprising the plurality of entities and relations of the plurality of entities, without surrogate keys of the plurality of entities; and
comparing the relations of the plurality of entities in the result with relations of a corresponding plurality of entities in the reference database.
13. The non-transitory computer-readable storage medium of claim 11 , wherein a first plurality of the surrogate keys of the plurality of entities in the database after the upgrading are out of sync with a second plurality of surrogate keys of a corresponding plurality of entities in the reference database.
14. The non-transitory computer-readable storage medium of claim 11 , wherein at least one of the plurality of entities is created after installation of the first version of the database, and before the upgrading of the database to the second version.
15. The non-transitory computer-readable storage medium of claim 11 , wherein the database comprises a relational database.