Add schema validation project

How to use the Schema Validation gem

1. Clone the schema validation project
   git clone https://gitlab.com/gitlab-org/database-team/schema-validation.git

2. Add the schema-validation gem to the GitLab gemfile.\

cd gitlab-development-kit/gitlab/

gem 'schema-validation', path: "YOUR_PATH/schema-validation/"`

3. Open the GitLab rails console
   rails console

4. Create a database object

connection = ActiveRecord::Base.connection
database = Schema::Validation::Sources::Database.new(connection)

Database object available methods:

• database.fetch_index_by_name(INDEX_NAME): returns an index object

• database.fetch_trigger_by_name(TRIGGER_NAME): returns a trigger object

• database.fetch_foreign_key_by_name(FOREIGN_KEY_NAME): returns a foreign key object

• database.fetch_table_by_name(TABLE_NAME): returns a table object

• database.index_exists?(INDEX_NAME): returns a boolean

• database.trigger_exists?(TRIGGER_NAME): returns a boolean

• database.foreign_key_exists?(FOREIGN_KEY_NAME): returns a boolean

• database.table_exists?(TABLE_NAME): returns a boolean

• database.indexes: returns a list of index objects present in the database

• database.triggers: returns a list of index triggers present in the database

• database.tables: returns a list of table objects present in the database

• database.foreign_keys: returns a list of foreign keys objects present in the database

Index object methods:

• index.name: returns the index name
• index.statement: returns the index statement
• index.table_name: returns the table name

Trigger object methods:

• trigger.name: returns the trigger name
• trigger.statement: returns the trigger statement
• trigger.table_name: return the trigger table name

Table object methods:

• table.name: returns the table name
• table.statement: returns the table statement
• table.columns: returns a list of column objects

Foreign key methods:

• foreign_key.name: returns the foreign key name
• foreign_key.statement: returns the foreign key statement
• foreign_key.table_name: returns the foreign key table name

Column methods:

• column.name: returns the column name
• column.table_name: returns the column table name
• column.partition_key?: returns a boolean
• column.statement: returns the column statement

5. Create a structure.sql object

file_path = Rails.root.join('db/structure.sql')
structure_sql = Schema::Validation::Sources::StructureSql.new(file_path)

Structure sql object available methods:

• structure_sql.fetch_index_by_name(INDEX_NAME): returns an index object

• structure_sql.fetch_trigger_by_name(TRIGGER_NAME): returns a trigger object

• structure_sql.fetch_foreign_key_by_name(FOREIGN_KEY_NAME): returns a foreign key object

• structure_sql.fetch_table_by_name(TABLE_NAME): returns a table object

• structure_sql.index_exists?(INDEX_NAME): returns a boolean

• structure_sql.trigger_exists?(TRIGGER_NAME): returns a boolean

• structure_sql.foreign_key_exists?(FOREIGN_KEY_NAME): returns a boolean

• structure_sql.table_exists?(TABLE_NAME): returns a boolean

• structure_sql.indexes: returns a list of index objects present in the database

• structure_sql.triggers: returns a list of index triggers present in the database

• structure_sql.tables: returns a list of table objects present in the database

• structure_sql.foreign_keys: returns a list of foreign keys objects present in the database

Index object methods:

• index.name: returns the index name
• index.statement: returns the index statement
• index.table_name: returns the table name

Trigger object methods:

• trigger.name: returns the trigger name
• trigger.statement: returns the trigger statement
• trigger.table_name: return the trigger table name

Table object methods:

• table.name: returns the table name
• table.statement: returns the table statement
• table.columns: returns a list of column objects

Foreign key methods:

• foreign_key.name: returns the foreign key name
• foreign_key.statement: returns the foreign key statement
• foreign_key.table_name: returns the foreign key table name

Column methods:

• column.name: returns the column name
• column.table_name: returns the column table name
• column.partition_key?: returns a boolean
• column.statement: returns the column statement

6. Create a new validator
   In this case, we wanna see the indexes that are present in the database, but not in the structure sql file.

class Validator < Schema::Validation::Validators::Base
  ERROR_MESSAGE = 'The index %s is present in the database, but not in the structure.sql file'
  
  def execute
    database.indexes.filter_map do |database_index|
      next if structure_sql.index_exists?(database_index.name)

      build_inconsistency(self.class, nil, database_index)
    end
  end
end

7. Execute the schema validation framework

inconsistencies = Schema::Validation::Runner.new(structure_sql, database, validators: [Validator]).execute

#print result
inconsistencies.each{ |inconsistency| puts inconsistency.display }

This will return an array of inconsistency objects.

Inconsistency object available methods:

• inconsistency.error_message: inconsistency error message (String defined in the validator - ERROR_MESSAGE)
• inconsistency.type: returns the validator name
• inconsistency.object_type: returns the object type (example: trigger, index, table, etc)
• inconsistency.table_name: returns the table name
• inconsistency.object_name: returns the object name
• inconsistency.diff: returns a diffy object
• inconsistency.display: returns a display message
• inconsistency.structure_sql_statement: returns the statement present in the structure sql file
• inconsistency.database_statement: returns the statement present in the database