Schema Validations: Identify tables that do not match schema
requested to merge 388013-automatically-identify-database-tables-that-do-not-match-schema-mr-1 into master
What does this MR do and why?
I'm splitting MR !115066 (closed).
Adds table support to Gitlab::Database::SchemaValidation
framework
It adds the two new validators:
-
ExtraTables
: Check if tables are present in thedb
, but not in thestructure.sql
file; -
MissingTables
: Check if tables are present in thestructure.sql
file, but not in thedb
;
Code is not DRY. We're still validating the framework. We're planing a refactoring soon as we validate the idea.
See: !113317 (comment 1304807919)
How to set up and validate locally
Testing ExtraTables (tables that are present in the Database and not in the structure.sql)
- Remove a table definition from structure.sql, like
CREATE TABLE lfs_objects (id integer NOT NULL, ...)
- Check if the
lfs_objects
table is present inExtraTables
output:
structure_sql = Gitlab::Database::SchemaValidation::StructureSql.new(Rails.root.join('db/structure.sql'), 'public')
database = Gitlab::Database::SchemaValidation::Database.new(ActiveRecord::Base.connection)
Gitlab::Database::SchemaValidation::Validators::ExtraTables.new(structure_sql, database).execute
Check the output
The table lfs_objects is present in the database, but not in the structure.sql file
Diff:
+id integer NOT NULL, oid character varying NOT NULL, size bigint NOT NULL, new_updated_at timestamp without time zone, file character varying, file_store integer DEFAULT 1
Testing MissingTables (that are present in the structure.sql and not in the Database)
- Drop a table from database, like
DROP TABLE lfs_objects CASCADE;
- Check if the
lfs_objects
table is present inMissingTables
output:
structure_sql = Gitlab::Database::SchemaValidation::StructureSql.new(Rails.root.join('db/structure.sql'), 'public')
database = Gitlab::Database::SchemaValidation::Database.new(ActiveRecord::Base.connection)
Gitlab::Database::SchemaValidation::Validators::MissingTables.new(structure_sql, database).execute
Check the output
The table lfs_objects is missing from the database
Diff:
-id integer NOT NULL, oid character varying NOT NULL, size bigint NOT NULL, created_at timestamp without time zone, updated_at timestamp without time zone, file character varying, file_store integer DEFAULT 1
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #388013 (closed)
Edited by Leonardo da Rosa