Schema Validations: Identify tables that do not match schema
requested to merge 388013-automatically-identify-database-tables-that-do-not-match-schema-2 into master
What does this MR do and why?
Add table and columns support to Gitlab::Database::SchemaValidation
framework
It adds the following validators:
-
DifferentDefinitionTables
: Check if tables have different column statements betweenstructure.sql
anddb
; -
ExtraTables
: Check if tables are present in thedb
, but not in thestructure.sql
file; -
ExtraTableColumns
: Check if table columns are present thedb
, but not in thestructure.sql
file; -
MissingTables
: Check if tables are present in thestructure.sql
file, but not in thedb
; -
MissingTableColumns
: Check if table columns are present 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 DifferentDefinitionTables
- Change some existent table definition in structure.sql, like
CREATE TABLE lfs_objects (id bigint NOT NULL, ...)
- Check if the
lfs_objects
table is present inDifferentDefinitionTables
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::DifferentDefinitionTables.new(structure_sql, database).execute
Output:
The table lfs_objects has a different column statement between structure.sql and database
Diff:
-id integer NOT NULL
+id bigint NOT NULL
Testing ExtraTables (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
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 ExtraTableColumns (that are present in the Database and not in the structure.sql)
- Remove some columns from any table definition in structure.sql, like
CREATE TABLE lfs_objects (id integer NOT NULL, ...)
- Check if the
lfs_objects
table and the removed columns are present inExtraTableColumns
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::ExtraTableColumns.new(structure_sql, database).execute
Output:
The table lfs_objects has columns present in the database, but not in the structure.sql file
Diff:
+id integer NOT NULL, oid character varying NOT NULL
Testing MissingTableColumns (that are present in the structure.sql and not in the Database)
- Change some columns from lfs_objects table, in the database:
ALTER TABLE lfs_objects DROP COLUMN created_at;
,ALTER TABLE lfs_objects RENAME COLUMN updated_at TO new_updated_at;
- Check if the
lfs_objects
table is present inMissingTableColumns
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::MissingTableColumns.new(structure_sql, database).execute
Output:
The table lfs_objects has columns missing from the database
Diff:
+created_at timestamp without time zone, updated_at timestamp without time zone
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
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