diff options
Diffstat (limited to 'gems/gitlab-schema-validation')
71 files changed, 2585 insertions, 0 deletions
diff --git a/gems/gitlab-schema-validation/.gitignore b/gems/gitlab-schema-validation/.gitignore new file mode 100644 index 00000000000..b04a8c840df --- /dev/null +++ b/gems/gitlab-schema-validation/.gitignore @@ -0,0 +1,11 @@ +/.bundle/ +/.yardoc +/_yardoc/ +/coverage/ +/doc/ +/pkg/ +/spec/reports/ +/tmp/ + +# rspec failure tracking +.rspec_status diff --git a/gems/gitlab-schema-validation/.gitlab-ci.yml b/gems/gitlab-schema-validation/.gitlab-ci.yml new file mode 100644 index 00000000000..03db9e02b30 --- /dev/null +++ b/gems/gitlab-schema-validation/.gitlab-ci.yml @@ -0,0 +1,4 @@ +include: + - local: gems/gem.gitlab-ci.yml + inputs: + gem_name: "gitlab-schema-validation" diff --git a/gems/gitlab-schema-validation/.rspec b/gems/gitlab-schema-validation/.rspec new file mode 100644 index 00000000000..34c5164d9b5 --- /dev/null +++ b/gems/gitlab-schema-validation/.rspec @@ -0,0 +1,3 @@ +--format documentation +--color +--require spec_helper diff --git a/gems/gitlab-schema-validation/.rubocop.yml b/gems/gitlab-schema-validation/.rubocop.yml new file mode 100644 index 00000000000..1dc800520ca --- /dev/null +++ b/gems/gitlab-schema-validation/.rubocop.yml @@ -0,0 +1,8 @@ +inherit_from: + - ../config/rubocop.yml + +AllCops: + NewCops: enable + +RSpec/MultipleMemoizedHelpers: + Max: 25 diff --git a/gems/gitlab-schema-validation/Gemfile b/gems/gitlab-schema-validation/Gemfile new file mode 100644 index 00000000000..3fa25adbee1 --- /dev/null +++ b/gems/gitlab-schema-validation/Gemfile @@ -0,0 +1,6 @@ +# frozen_string_literal: true + +source "https://rubygems.org" + +# Specify your gem's dependencies in gitlab-schema-validation.gemspec +gemspec diff --git a/gems/gitlab-schema-validation/Gemfile.lock b/gems/gitlab-schema-validation/Gemfile.lock new file mode 100644 index 00000000000..5ad804d3660 --- /dev/null +++ b/gems/gitlab-schema-validation/Gemfile.lock @@ -0,0 +1,137 @@ +PATH + remote: . + specs: + gitlab-schema-validation (0.1.0) + diffy + pg_query + +GEM + remote: https://rubygems.org/ + specs: + activesupport (7.0.6) + concurrent-ruby (~> 1.0, >= 1.0.2) + i18n (>= 1.6, < 2) + minitest (>= 5.1) + tzinfo (~> 2.0) + ast (2.4.2) + benchmark-malloc (0.2.0) + benchmark-perf (0.6.0) + benchmark-trend (0.4.0) + binding_of_caller (1.0.0) + debug_inspector (>= 0.0.1) + coderay (1.1.3) + concurrent-ruby (1.2.2) + debug_inspector (1.1.0) + diff-lcs (1.5.0) + diffy (3.4.2) + gitlab-styles (10.1.0) + rubocop (~> 1.50.2) + rubocop-graphql (~> 0.18) + rubocop-performance (~> 1.15) + rubocop-rails (~> 2.17) + rubocop-rspec (~> 2.22) + google-protobuf (3.23.3) + i18n (1.14.1) + concurrent-ruby (~> 1.0) + json (2.6.3) + method_source (1.0.0) + minitest (5.18.1) + parallel (1.23.0) + parser (3.2.2.3) + ast (~> 2.4.1) + racc + pg_query (4.2.1) + google-protobuf (>= 3.22.3) + proc_to_ast (0.1.0) + coderay + parser + unparser + pry (0.14.2) + coderay (~> 1.1) + method_source (~> 1.0) + racc (1.7.1) + rack (3.0.8) + rainbow (3.1.1) + regexp_parser (2.8.1) + rexml (3.2.5) + rspec (3.12.0) + rspec-core (~> 3.12.0) + rspec-expectations (~> 3.12.0) + rspec-mocks (~> 3.12.0) + rspec-benchmark (0.6.0) + benchmark-malloc (~> 0.2) + benchmark-perf (~> 0.6) + benchmark-trend (~> 0.4) + rspec (>= 3.0) + rspec-core (3.12.2) + rspec-support (~> 3.12.0) + rspec-expectations (3.12.3) + diff-lcs (>= 1.2.0, < 2.0) + rspec-support (~> 3.12.0) + rspec-mocks (3.12.5) + diff-lcs (>= 1.2.0, < 2.0) + rspec-support (~> 3.12.0) + rspec-parameterized (1.0.0) + rspec-parameterized-core (< 2) + rspec-parameterized-table_syntax (< 2) + rspec-parameterized-core (1.0.0) + parser + proc_to_ast + rspec (>= 2.13, < 4) + unparser + rspec-parameterized-table_syntax (1.0.0) + binding_of_caller + rspec-parameterized-core (< 2) + rspec-support (3.12.1) + rubocop (1.50.2) + json (~> 2.3) + parallel (~> 1.10) + parser (>= 3.2.0.0) + rainbow (>= 2.2.2, < 4.0) + regexp_parser (>= 1.8, < 3.0) + rexml (>= 3.2.5, < 4.0) + rubocop-ast (>= 1.28.0, < 2.0) + ruby-progressbar (~> 1.7) + unicode-display_width (>= 2.4.0, < 3.0) + rubocop-ast (1.29.0) + parser (>= 3.2.1.0) + rubocop-capybara (2.18.0) + rubocop (~> 1.41) + rubocop-factory_bot (2.23.1) + rubocop (~> 1.33) + rubocop-graphql (0.19.0) + rubocop (>= 0.87, < 2) + rubocop-performance (1.18.0) + rubocop (>= 1.7.0, < 2.0) + rubocop-ast (>= 0.4.0) + rubocop-rails (2.20.2) + activesupport (>= 4.2.0) + rack (>= 1.1) + rubocop (>= 1.33.0, < 2.0) + rubocop-rspec (2.22.0) + rubocop (~> 1.33) + rubocop-capybara (~> 2.17) + rubocop-factory_bot (~> 2.22) + ruby-progressbar (1.13.0) + tzinfo (2.0.6) + concurrent-ruby (~> 1.0) + unicode-display_width (2.4.2) + unparser (0.6.8) + diff-lcs (~> 1.3) + parser (>= 3.2.0) + +PLATFORMS + ruby + +DEPENDENCIES + gitlab-schema-validation! + gitlab-styles (~> 10.1.0) + pry + rspec (~> 3.0) + rspec-benchmark (~> 0.6.0) + rspec-parameterized (~> 1.0) + rubocop (~> 1.50) + rubocop-rspec (~> 2.22) + +BUNDLED WITH + 2.4.14 diff --git a/gems/gitlab-schema-validation/gitlab-schema-validation.gemspec b/gems/gitlab-schema-validation/gitlab-schema-validation.gemspec new file mode 100644 index 00000000000..47ca8b65b5d --- /dev/null +++ b/gems/gitlab-schema-validation/gitlab-schema-validation.gemspec @@ -0,0 +1,32 @@ +# frozen_string_literal: true + +require_relative "lib/gitlab/schema/validation/version" + +Gem::Specification.new do |spec| + spec.name = "gitlab-schema-validation" + spec.version = Gitlab::Schema::Validation::Version::VERSION + spec.authors = ["group::database"] + spec.email = ["engineering@gitlab.com"] + + spec.summary = "Schema validation framework" + spec.description = "Compares the differences between a structure.sql file and a database + and reports the inconsistencies." + spec.homepage = "https://gitlab.com/gitlab-org/gitlab/-/tree/master/gems/gitlab-schema-validation" + spec.license = "MIT" + spec.required_ruby_version = ">= 3.0" + spec.metadata["rubygems_mfa_required"] = "true" + + spec.files = Dir['lib/**/*.rb'] + spec.require_paths = ["lib"] + + spec.add_runtime_dependency "diffy" + spec.add_runtime_dependency "pg_query" + + spec.add_development_dependency "gitlab-styles", "~> 10.1.0" + spec.add_development_dependency "pry" + spec.add_development_dependency "rspec", "~> 3.0" + spec.add_development_dependency "rspec-benchmark", "~> 0.6.0" + spec.add_development_dependency "rspec-parameterized", "~> 1.0" + spec.add_development_dependency "rubocop", "~> 1.50" + spec.add_development_dependency "rubocop-rspec", "~> 2.22" +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation.rb new file mode 100644 index 00000000000..5211358a197 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation.rb @@ -0,0 +1,66 @@ +# frozen_string_literal: true + +require 'pg_query' +require 'diffy' + +require_relative 'validation/version' +require_relative 'validation/inconsistency' +require_relative 'validation/pg_types' + +require_relative 'validation/validators/base' + +require_relative 'validation/validators/different_definition_indexes' +require_relative 'validation/validators/extra_indexes' +require_relative 'validation/validators/missing_indexes' + +require_relative 'validation/validators/extra_table_columns' +require_relative 'validation/validators/missing_table_columns' + +require_relative 'validation/validators/different_definition_foreign_keys' +require_relative 'validation/validators/extra_foreign_keys' +require_relative 'validation/validators/missing_foreign_keys' + +require_relative 'validation/validators/different_definition_tables' +require_relative 'validation/validators/extra_tables' +require_relative 'validation/validators/missing_tables' + +require_relative 'validation/validators/different_definition_triggers' +require_relative 'validation/validators/extra_triggers' +require_relative 'validation/validators/missing_triggers' + +require_relative 'validation/sources/structure_sql' +require_relative 'validation/sources/database' + +require_relative 'validation/schema_objects/base' +require_relative 'validation/schema_objects/column' +require_relative 'validation/schema_objects/index' +require_relative 'validation/schema_objects/table' +require_relative 'validation/schema_objects/trigger' +require_relative 'validation/schema_objects/foreign_key' + +require_relative 'validation/adapters/column_database_adapter' +require_relative 'validation/adapters/column_structure_sql_adapter' +require_relative 'validation/adapters/foreign_key_database_adapter' +require_relative 'validation/adapters/foreign_key_structure_sql_adapter' + +module Gitlab + module Schema + module Validation + class Runner + def initialize(structure_sql, database, validators:) + @structure_sql = structure_sql + @database = database + @validators = validators + end + + def execute + validators.flat_map { |c| c.new(structure_sql, database).execute } + end + + private + + attr_reader :structure_sql, :database, :validators + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/column_database_adapter.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/column_database_adapter.rb new file mode 100644 index 00000000000..8b4d07d2e79 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/column_database_adapter.rb @@ -0,0 +1,47 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Adapters + class ColumnDatabaseAdapter + def initialize(query_result) + @query_result = query_result + end + + def name + @name ||= query_result['column_name'] + end + + def table_name + query_result['table_name'] + end + + def data_type + query_result['data_type'] + end + + def default + return unless query_result['column_default'] + + return if name == 'id' || query_result['column_default'].include?('nextval') + + "DEFAULT #{query_result['column_default']}" + end + + def nullable + 'NOT NULL' if query_result['not_null'] + end + + def partition_key? + query_result['partition_key'] + end + + private + + attr_reader :query_result + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/column_structure_sql_adapter.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/column_structure_sql_adapter.rb new file mode 100644 index 00000000000..62e501bf16b --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/column_structure_sql_adapter.rb @@ -0,0 +1,139 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Adapters + UndefinedPGType = Class.new(StandardError) + + class ColumnStructureSqlAdapter + NOT_NULL_CONSTR = :CONSTR_NOTNULL + DEFAULT_CONSTR = :CONSTR_DEFAULT + + MAPPINGS = { + 't' => 'true', + 'f' => 'false' + }.freeze + + attr_reader :table_name + + def initialize(table_name, pg_query_stmt, partitioning_stmt) + @table_name = table_name + @pg_query_stmt = pg_query_stmt + @partitioning_stmt = partitioning_stmt + end + + def name + @name ||= pg_query_stmt.colname + end + + def data_type + type(pg_query_stmt.type_name) + end + + def default + return if name == 'id' + + value = parse_node(constraints.find { |node| node.constraint.contype == DEFAULT_CONSTR }) + + return unless value + + "DEFAULT #{value}" + end + + def nullable + 'NOT NULL' if constraints.any? { |node| node.constraint.contype == NOT_NULL_CONSTR } + end + + def partition_key? + partition_keys.include?(name) + end + + private + + attr_reader :pg_query_stmt, :partitioning_stmt + + def constraints + @constraints ||= pg_query_stmt.constraints + end + + # Returns the node type + # + # pg_type:: type alias, used internally by postgres, +int4+, +int8+, +bool+, +varchar+ + # type:: type name, like +integer+, +bigint+, +boolean+, +character varying+. + # array_ext:: adds the +[]+ extension for array types. + # precision_ext:: adds the precision, if have any, like +(255)+, +(6)+. + # + # @info +timestamp+ and +timestamptz+ have a particular case when precision is defined. + # In this case, the order of the statement needs to be re-arranged from + # timestamp without time zone(6) to timestamp(6) without a time zone. + def type(node) + pg_type = parse_node(node.names.last) + type = PgTypes::TYPES.fetch(pg_type).dup + array_ext = '[]' if node.array_bounds.any? + precision_ext = "(#{node.typmods.map { |typmod| parse_node(typmod) }.join(',')})" if node.typmods.any? + + if %w[timestamp timestamptz].include?(pg_type) + type.gsub!('timestamp', ['timestamp', precision_ext].compact.join) + precision_ext = nil + end + + [type, precision_ext, array_ext].compact.join + rescue KeyError => e + raise UndefinedPGType, e.message + end + + # Parses PGQuery nodes recursively + # + # :constraint:: nodes that groups column default info + # :partition_elem:: node that store partition key info + # :func_cal:: nodes that stores functions, like +now()+ + # :a_const:: nodes that stores constant values, like +t+, +f+, +0.0.0.0+, +255+, +1.0+ + # :type_cast:: nodes that stores casting values, like +'name'::text+, +'0.0.0.0'::inet+ + # else:: extract node values in the last iteration of the recursion, like +int4+, +1.0+, +now+, +255+ + # + # @note boolean types types are mapped from +t+, +f+ to +true+, +false+ + def parse_node(node) + return unless node + + case node.node + when :constraint + parse_node(node.constraint.raw_expr) + when :partition_elem + node.partition_elem.name + when :func_call + "#{parse_node(node.func_call.funcname.first)}()" + when :a_const + parse_a_const(node.a_const) + when :type_cast + value = parse_node(node.type_cast.arg) + type = type(node.type_cast.type_name) + separator = MAPPINGS.key?(value) ? '' : "::#{type}" + + [MAPPINGS.fetch(value, "'#{value}'"), separator].compact.join + else + get_value_from_key(node, key: node.node) + end + end + + def parse_a_const(a_const) + return unless a_const + + type = a_const.val + get_value_from_key(a_const, key: type) + end + + def get_value_from_key(node, key:) + node.to_h[key].values.last + end + + def partition_keys + return [] unless partitioning_stmt + + @partition_keys ||= partitioning_stmt.part_params.map { |key_stmt| parse_node(key_stmt) } + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/foreign_key_database_adapter.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/foreign_key_database_adapter.rb new file mode 100644 index 00000000000..ee5d5dc0ce9 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/foreign_key_database_adapter.rb @@ -0,0 +1,31 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Adapters + class ForeignKeyDatabaseAdapter + def initialize(query_result) + @query_result = query_result + end + + def name + "#{query_result['schema']}.#{query_result['foreign_key_name']}" + end + + def table_name + query_result['table_name'] + end + + def statement + query_result['foreign_key_definition'] + end + + private + + attr_reader :query_result + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/foreign_key_structure_sql_adapter.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/foreign_key_structure_sql_adapter.rb new file mode 100644 index 00000000000..730652c302d --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/adapters/foreign_key_structure_sql_adapter.rb @@ -0,0 +1,50 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Adapters + class ForeignKeyStructureSqlAdapter + STATEMENT_REGEX = /\bREFERENCES\s\K\S+\K\s\(/ + EXTRACT_REGEX = /\bFOREIGN KEY.*/ + + def initialize(parsed_stmt) + @parsed_stmt = parsed_stmt + end + + def name + "#{schema_name}.#{foreign_key_name}" + end + + def table_name + parsed_stmt.relation.relname + end + + # PgQuery parses FK statements with an extra space in the referenced table column. + # This extra space needs to be removed. + # + # @example REFERENCES ci_pipelines (id) => REFERENCES ci_pipelines(id) + def statement + deparse_stmt[EXTRACT_REGEX].gsub(STATEMENT_REGEX, '(') + end + + private + + attr_reader :parsed_stmt + + def schema_name + parsed_stmt.relation.schemaname + end + + def foreign_key_name + parsed_stmt.cmds.first.alter_table_cmd.def.constraint.conname + end + + def deparse_stmt + PgQuery.deparse_stmt(parsed_stmt) + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/inconsistency.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/inconsistency.rb new file mode 100644 index 00000000000..13799b8b9ff --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/inconsistency.rb @@ -0,0 +1,67 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + class Inconsistency + def initialize(validator_class, structure_sql_object, database_object) + @validator_class = validator_class + @structure_sql_object = structure_sql_object + @database_object = database_object + end + + def error_message + format(validator_class::ERROR_MESSAGE, object_name) + end + + def type + validator_class.name + end + + def object_type + object_type = structure_sql_object&.class&.name || database_object&.class&.name + + object_type&.gsub('Gitlab::Schema::Validation::SchemaObjects::', '') + end + + def table_name + structure_sql_object&.table_name || database_object&.table_name + end + + def object_name + structure_sql_object&.name || database_object&.name + end + + def diff + Diffy::Diff.new(structure_sql_statement, database_statement) + end + + def display + <<~MSG + #{'-' * 54} + #{error_message} + Diff: + #{diff.to_s(:color)} + #{'-' * 54} + MSG + end + + def structure_sql_statement + return unless structure_sql_object + + "#{structure_sql_object.statement}\n" + end + + def database_statement + return unless database_object + + "#{database_object.statement}\n" + end + + private + + attr_reader :validator_class, :structure_sql_object, :database_object + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/pg_types.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/pg_types.rb new file mode 100644 index 00000000000..335bbe94cfb --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/pg_types.rb @@ -0,0 +1,73 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + class PgTypes + TYPES = { + 'bool' => 'boolean', + 'bytea' => 'bytea', + 'char' => '"char"', + 'int8' => 'bigint', + 'int2' => 'smallint', + 'int4' => 'integer', + 'regproc' => 'regproc', + 'text' => 'text', + 'oid' => 'oid', + 'tid' => 'tid', + 'xid' => 'xid', + 'cid' => 'cid', + 'json' => 'json', + 'xml' => 'xml', + 'pg_node_tree' => 'pg_node_tree', + 'pg_ndistinct' => 'pg_ndistinct', + 'pg_dependencies' => 'pg_dependencies', + 'pg_mcv_list' => 'pg_mcv_list', + 'xid8' => 'xid8', + 'path' => 'path', + 'polygon' => 'polygon', + 'float4' => 'real', + 'float8' => 'double precision', + 'circle' => 'circle', + 'money' => 'money', + 'macaddr' => 'macaddr', + 'inet' => 'inet', + 'cidr' => 'cidr', + 'macaddr8' => 'macaddr8', + 'aclitem' => 'aclitem', + 'bpchar' => 'character', + 'varchar' => 'character varying', + 'date' => 'date', + 'time' => 'time without time zone', + 'timestamp' => 'timestamp without time zone', + 'timestamptz' => 'timestamp with time zone', + 'interval' => 'interval', + 'timetz' => 'time with time zone', + 'bit' => 'bit', + 'varbit' => 'bit varying', + 'numeric' => 'numeric', + 'refcursor' => 'refcursor', + 'regprocedure' => 'regprocedure', + 'regoper' => 'regoper', + 'regoperator' => 'regoperator', + 'regclass' => 'regclass', + 'regcollation' => 'regcollation', + 'regtype' => 'regtype', + 'regrole' => 'regrole', + 'regnamespace' => 'regnamespace', + 'uuid' => 'uuid', + 'pg_lsn' => 'pg_lsn', + 'tsvector' => 'tsvector', + 'gtsvector' => 'gtsvector', + 'tsquery' => 'tsquery', + 'regconfig' => 'regconfig', + 'regdictionary' => 'regdictionary', + 'jsonb' => 'jsonb', + 'jsonpath' => 'jsonpath', + 'txid_snapshot' => 'txid_snapshot', + 'pg_snapshot' => 'pg_snapshot' + }.freeze + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/base.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/base.rb new file mode 100644 index 00000000000..1af7a67ddb6 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/base.rb @@ -0,0 +1,31 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module SchemaObjects + class Base + def initialize(parsed_stmt) + @parsed_stmt = parsed_stmt + end + + def name + raise NoMethodError, "subclasses of #{self.class.name} must implement #{__method__}" + end + + def table_name + parsed_stmt.relation.relname + end + + def statement + @statement ||= PgQuery.deparse_stmt(parsed_stmt) + end + + private + + attr_reader :parsed_stmt + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/column.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/column.rb new file mode 100644 index 00000000000..0b3687fdb98 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/column.rb @@ -0,0 +1,33 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module SchemaObjects + class Column + def initialize(adapter) + @adapter = adapter + end + + attr_reader :adapter + + def name + adapter.name + end + + def table_name + adapter.table_name + end + + def partition_key? + adapter.partition_key? + end + + def statement + [adapter.name, adapter.data_type, adapter.default, adapter.nullable].compact.join(' ') + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/foreign_key.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/foreign_key.rb new file mode 100644 index 00000000000..41e2d30029a --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/foreign_key.rb @@ -0,0 +1,34 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module SchemaObjects + class ForeignKey + def initialize(adapter) + @adapter = adapter + end + + # Foreign key name should include the schema, as the same name could be used across different schemas + # + # @example public.foreign_key_name + def name + @name ||= adapter.name + end + + def table_name + @table_name ||= adapter.table_name + end + + def statement + @statement ||= adapter.statement + end + + private + + attr_reader :adapter + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/index.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/index.rb new file mode 100644 index 00000000000..9f99c6a6e6e --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/index.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module SchemaObjects + class Index < Base + def name + parsed_stmt.idxname + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/table.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/table.rb new file mode 100644 index 00000000000..591131cb220 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/table.rb @@ -0,0 +1,44 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module SchemaObjects + class Table + def initialize(name, columns) + @name = name + @columns = columns + end + + attr_reader :name, :columns + + def table_name + name + end + + def statement + format('CREATE TABLE %s (%s)', name, columns_statement) + end + + def fetch_column_by_name(column_name) + columns.find { |column| column.name == column_name } + end + + def column_exists?(column_name) + column = fetch_column_by_name(column_name) + + return false if column.nil? + + true + end + + private + + def columns_statement + columns.reject(&:partition_key?).map(&:statement).join(', ') + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/trigger.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/trigger.rb new file mode 100644 index 00000000000..7903985a963 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/schema_objects/trigger.rb @@ -0,0 +1,15 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module SchemaObjects + class Trigger < Base + def name + parsed_stmt.trigname + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/sources/database.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/sources/database.rb new file mode 100644 index 00000000000..8505d1f149a --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/sources/database.rb @@ -0,0 +1,192 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Sources + class Database + STATIC_PARTITIONS_SCHEMA = 'gitlab_partitions_static' + + def initialize(connection) + @connection = connection + end + + def fetch_index_by_name(index_name) + index_map[index_name] + end + + def fetch_trigger_by_name(trigger_name) + trigger_map[trigger_name] + end + + def fetch_foreign_key_by_name(foreign_key_name) + foreign_key_map[foreign_key_name] + end + + def fetch_table_by_name(table_name) + table_map[table_name] + end + + def index_exists?(index_name) + index = index_map[index_name] + + return false if index.nil? + + true + end + + def trigger_exists?(trigger_name) + trigger = trigger_map[trigger_name] + + return false if trigger.nil? + + true + end + + def foreign_key_exists?(foreign_key_name) + foreign_key = fetch_foreign_key_by_name(foreign_key_name) + + return false if foreign_key.nil? + + true + end + + def table_exists?(table_name) + table = fetch_table_by_name(table_name) + + return false if table.nil? + + true + end + + def indexes + index_map.values + end + + def triggers + trigger_map.values + end + + def foreign_keys + foreign_key_map.values + end + + def tables + table_map.values + end + + private + + attr_reader :connection + + def schemas + @schemas ||= [STATIC_PARTITIONS_SCHEMA, connection.current_schema] + end + + def trigger_map + @trigger_map ||= + fetch_triggers.transform_values! do |trigger_stmt| + SchemaObjects::Trigger.new(PgQuery.parse(trigger_stmt).tree.stmts.first.stmt.create_trig_stmt) + end + end + + def fetch_triggers + # rubocop:disable Rails/SquishedSQLHeredocs + sql = <<~SQL + SELECT triggers.tgname, pg_get_triggerdef(triggers.oid) + FROM pg_catalog.pg_trigger triggers + INNER JOIN pg_catalog.pg_class rel ON triggers.tgrelid = rel.oid + INNER JOIN pg_catalog.pg_namespace nsp ON nsp.oid = rel.relnamespace + WHERE triggers.tgisinternal IS FALSE + AND nsp.nspname IN ($1, $2) + SQL + # rubocop:enable Rails/SquishedSQLHeredocs + + connection.select_rows(sql, nil, schemas).to_h + end + + def table_map + @table_map ||= fetch_tables.transform_values! do |stmt| + columns = stmt.map { |column| SchemaObjects::Column.new(Adapters::ColumnDatabaseAdapter.new(column)) } + + SchemaObjects::Table.new(stmt.first['table_name'], columns) + end + end + + def fetch_tables + # rubocop:disable Rails/SquishedSQLHeredocs + sql = <<~SQL + SELECT + table_information.relname AS table_name, + col_information.attname AS column_name, + col_information.attnotnull AS not_null, + col_information.attnum = ANY(pg_partitioned_table.partattrs) as partition_key, + format_type(col_information.atttypid, col_information.atttypmod) AS data_type, + pg_get_expr(col_default_information.adbin, col_default_information.adrelid) AS column_default + FROM pg_attribute AS col_information + JOIN pg_class AS table_information ON col_information.attrelid = table_information.oid + JOIN pg_namespace AS schema_information ON table_information.relnamespace = schema_information.oid + LEFT JOIN pg_partitioned_table ON pg_partitioned_table.partrelid = table_information.oid + LEFT JOIN pg_attrdef AS col_default_information ON col_information.attrelid = col_default_information.adrelid + AND col_information.attnum = col_default_information.adnum + WHERE NOT col_information.attisdropped + AND col_information.attnum > 0 + AND table_information.relkind IN ('r', 'p') + AND schema_information.nspname IN ($1, $2) + SQL + # rubocop:enable Rails/SquishedSQLHeredocs + + connection.exec_query(sql, nil, schemas).group_by { |row| row['table_name'] } + end + + def fetch_indexes + # rubocop:disable Rails/SquishedSQLHeredocs + sql = <<~SQL + SELECT indexname, indexdef + FROM pg_indexes + WHERE indexname NOT LIKE '%_pkey' AND schemaname IN ($1, $2); + SQL + # rubocop:enable Rails/SquishedSQLHeredocs + + connection.select_rows(sql, nil, schemas).to_h + end + + def index_map + @index_map ||= + fetch_indexes.transform_values! do |index_stmt| + SchemaObjects::Index.new(PgQuery.parse(index_stmt).tree.stmts.first.stmt.index_stmt) + end + end + + def foreign_key_map + @foreign_key_map ||= fetch_fks.each_with_object({}) do |stmt, result| + adapter = Adapters::ForeignKeyDatabaseAdapter.new(stmt) + + result[adapter.name] = SchemaObjects::ForeignKey.new(adapter) + end + end + + def fetch_fks + # rubocop:disable Rails/SquishedSQLHeredocs + sql = <<~SQL + SELECT + pg_namespace.nspname::text AS schema, + pg_class.relname::text AS table_name, + pg_constraint.conname AS foreign_key_name, + pg_get_constraintdef(pg_constraint.oid) AS foreign_key_definition + FROM pg_constraint + INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid + INNER JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid + WHERE contype = 'f' + AND pg_namespace.nspname = $1 + AND pg_constraint.conparentid = 0 + SQL + # rubocop:enable Rails/SquishedSQLHeredocs + + connection.exec_query(sql, nil, [connection.current_schema]) + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/sources/structure_sql.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/sources/structure_sql.rb new file mode 100644 index 00000000000..b2e3fcd63c5 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/sources/structure_sql.rb @@ -0,0 +1,143 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Sources + class StructureSql + DEFAULT_SCHEMA = 'public' + + def initialize(structure_file_path, schema_name = DEFAULT_SCHEMA) + @structure_file_path = structure_file_path + @schema_name = schema_name + end + + def index_exists?(index_name) + index = indexes.find { |index| index.name == index_name } + + return false if index.nil? + + true + end + + def trigger_exists?(trigger_name) + trigger = triggers.find { |trigger| trigger.name == trigger_name } + + return false if trigger.nil? + + true + end + + def foreign_key_exists?(foreign_key_name) + foreign_key = foreign_keys.find { |fk| fk.name == foreign_key_name } + + return false if foreign_key.nil? + + true + end + + def table_exists?(table_name) + table = fetch_table_by_name(table_name) + + return false if table.nil? + + true + end + + def fetch_table_by_name(table_name) + tables.find { |table| table.name == table_name } + end + + def indexes + @indexes ||= map_with_default_schema(index_statements, SchemaObjects::Index) + end + + def triggers + @triggers ||= map_with_default_schema(trigger_statements, SchemaObjects::Trigger) + end + + def foreign_keys + @foreign_keys ||= foreign_key_statements.map do |stmt| + stmt.relation.schemaname = schema_name if stmt.relation.schemaname == '' + + SchemaObjects::ForeignKey.new(Adapters::ForeignKeyStructureSqlAdapter.new(stmt)) + end + end + + def tables + @tables ||= table_statements.map do |stmt| + table_name = stmt.relation.relname + partition_stmt = stmt.partspec + + columns = stmt.table_elts.select { |n| n.node == :column_def }.map do |column| + adapter = Adapters::ColumnStructureSqlAdapter.new(table_name, column.column_def, partition_stmt) + SchemaObjects::Column.new(adapter) + end + + SchemaObjects::Table.new(table_name, columns) + end + end + + private + + attr_reader :structure_file_path, :schema_name + + def index_statements + statements.filter_map { |s| s.stmt.index_stmt } + end + + def trigger_statements + statements.filter_map { |s| s.stmt.create_trig_stmt } + end + + def table_statements + statements.filter_map { |s| s.stmt.create_stmt } + end + + def foreign_key_statements + constraint_statements(:CONSTR_FOREIGN) + end + + # Filter constraint statement nodes + # + # @param constraint_type [Symbol] node type. One of CONSTR_PRIMARY, CONSTR_CHECK, CONSTR_EXCLUSION, + # CONSTR_UNIQUE or CONSTR_FOREIGN. + def constraint_statements(constraint_type) + alter_table_statements(:AT_AddConstraint).filter do |stmt| + stmt.cmds.first.alter_table_cmd.def.constraint.contype == constraint_type + end + end + + # Filter alter table statement nodes + # + # @param subtype [Symbol] node subtype +AT_AttachPartition+, +AT_ColumnDefault+ or +AT_AddConstraint+ + def alter_table_statements(subtype) + statements.filter_map do |statement| + node = statement.stmt.alter_table_stmt + + next unless node + + node if node.cmds.first.alter_table_cmd.subtype == subtype + end + end + + def statements + @statements ||= parsed_structure_file.tree.stmts + end + + def parsed_structure_file + PgQuery.parse(File.read(structure_file_path)) + end + + def map_with_default_schema(statements, validation_class) + statements.map do |statement| + statement.relation.schemaname = schema_name if statement.relation.schemaname == '' + + validation_class.new(statement) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/base.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/base.rb new file mode 100644 index 00000000000..151af4b61e6 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/base.rb @@ -0,0 +1,49 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class Base + ERROR_MESSAGE = 'A schema inconsistency has been found' + + def initialize(structure_sql, database) + @structure_sql = structure_sql + @database = database + end + + def self.all_validators + [ + ExtraTables, + ExtraTableColumns, + ExtraIndexes, + ExtraTriggers, + ExtraForeignKeys, + MissingTables, + MissingTableColumns, + MissingIndexes, + MissingTriggers, + MissingForeignKeys, + DifferentDefinitionTables, + DifferentDefinitionIndexes, + DifferentDefinitionTriggers, + DifferentDefinitionForeignKeys + ] + end + + def execute + raise NoMethodError, "subclasses of #{self.class.name} must implement #{__method__}" + end + + private + + attr_reader :structure_sql, :database + + def build_inconsistency(validator_class, structure_sql_object, database_object) + Inconsistency.new(validator_class, structure_sql_object, database_object) + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_foreign_keys.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_foreign_keys.rb new file mode 100644 index 00000000000..d8ea7807cc5 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_foreign_keys.rb @@ -0,0 +1,24 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class DifferentDefinitionForeignKeys < Base + ERROR_MESSAGE = "The %s foreign key has a different statement between structure.sql and database" + + def execute + structure_sql.foreign_keys.filter_map do |structure_sql_fk| + database_fk = database.fetch_foreign_key_by_name(structure_sql_fk.name) + + next if database_fk.nil? + next if database_fk.statement == structure_sql_fk.statement + + build_inconsistency(self.class, structure_sql_fk, database_fk) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_indexes.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_indexes.rb new file mode 100644 index 00000000000..032e7edd5ab --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_indexes.rb @@ -0,0 +1,24 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class DifferentDefinitionIndexes < Base + ERROR_MESSAGE = 'The %s index has a different statement between structure.sql and database' + + def execute + structure_sql.indexes.filter_map do |structure_sql_index| + database_index = database.fetch_index_by_name(structure_sql_index.name) + + next if database_index.nil? + next if database_index.statement == structure_sql_index.statement + + build_inconsistency(self.class, structure_sql_index, database_index) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_tables.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_tables.rb new file mode 100644 index 00000000000..f6892a76a12 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_tables.rb @@ -0,0 +1,50 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class DifferentDefinitionTables < Base + ERROR_MESSAGE = "The table %s has a different column statement between structure.sql and database" + + def execute + structure_sql.tables.filter_map do |structure_sql_table| + table_name = structure_sql_table.name + database_table = database.fetch_table_by_name(table_name) + + next unless database_table + + db_diffs, structure_diffs = column_diffs(database_table, structure_sql_table.columns) + + if db_diffs.any? + build_inconsistency(self.class, + SchemaObjects::Table.new(table_name, db_diffs), + SchemaObjects::Table.new(table_name, structure_diffs)) + end + end + end + + private + + def column_diffs(db_table, columns) + db_diffs = [] + structure_diffs = [] + + columns.each do |column| + db_column = db_table.fetch_column_by_name(column.name) + + next unless db_column + + next if db_column.statement == column.statement + + db_diffs << db_column + structure_diffs << column + end + + [db_diffs, structure_diffs] + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_triggers.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_triggers.rb new file mode 100644 index 00000000000..eed78924b02 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/different_definition_triggers.rb @@ -0,0 +1,24 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class DifferentDefinitionTriggers < Base + ERROR_MESSAGE = "The %s trigger has a different statement between structure.sql and database" + + def execute + structure_sql.triggers.filter_map do |structure_sql_trigger| + database_trigger = database.fetch_trigger_by_name(structure_sql_trigger.name) + + next if database_trigger.nil? + next if database_trigger.statement == structure_sql_trigger.statement + + build_inconsistency(self.class, structure_sql_trigger, nil) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_foreign_keys.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_foreign_keys.rb new file mode 100644 index 00000000000..81968318629 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_foreign_keys.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class ExtraForeignKeys < Base + ERROR_MESSAGE = "The foreign key %s is present in the database, but not in the structure.sql file" + + def execute + database.foreign_keys.filter_map do |database_fk| + next if structure_sql.foreign_key_exists?(database_fk.name) + + build_inconsistency(self.class, nil, database_fk) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_indexes.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_indexes.rb new file mode 100644 index 00000000000..4b5bd7c820b --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_indexes.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class ExtraIndexes < 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 + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_table_columns.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_table_columns.rb new file mode 100644 index 00000000000..517d6ae957f --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_table_columns.rb @@ -0,0 +1,32 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class ExtraTableColumns < Base + ERROR_MESSAGE = "The table %s has columns present in the database, but not in the structure.sql file" + + def execute + database.tables.filter_map do |database_table| + table_name = database_table.name + structure_sql_table = structure_sql.fetch_table_by_name(table_name) + + next unless structure_sql_table + + inconsistencies = database_table.columns.filter_map do |database_table_column| + next if structure_sql_table.column_exists?(database_table_column.name) + + database_table_column + end + + if inconsistencies.any? + build_inconsistency(self.class, nil, SchemaObjects::Table.new(table_name, inconsistencies)) + end + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_tables.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_tables.rb new file mode 100644 index 00000000000..d297464a01c --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_tables.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class ExtraTables < Base + ERROR_MESSAGE = "The table %s is present in the database, but not in the structure.sql file" + + def execute + database.tables.filter_map do |database_table| + next if structure_sql.table_exists?(database_table.name) + + build_inconsistency(self.class, nil, database_table) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_triggers.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_triggers.rb new file mode 100644 index 00000000000..d06747989fc --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_triggers.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class ExtraTriggers < Base + ERROR_MESSAGE = "The trigger %s is present in the database, but not in the structure.sql file" + + def execute + database.triggers.filter_map do |database_trigger| + next if structure_sql.trigger_exists?(database_trigger.name) + + build_inconsistency(self.class, nil, database_trigger) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_foreign_keys.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_foreign_keys.rb new file mode 100644 index 00000000000..daebd458282 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_foreign_keys.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class MissingForeignKeys < Base + ERROR_MESSAGE = "The foreign key %s is missing from the database" + + def execute + structure_sql.foreign_keys.filter_map do |structure_sql_fk| + next if database.foreign_key_exists?(structure_sql_fk.name) + + build_inconsistency(self.class, structure_sql_fk, nil) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_indexes.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_indexes.rb new file mode 100644 index 00000000000..655c462aeaa --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_indexes.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class MissingIndexes < Base + ERROR_MESSAGE = "The index %s is missing from the database" + + def execute + structure_sql.indexes.filter_map do |structure_sql_index| + next if database.index_exists?(structure_sql_index.name) + + build_inconsistency(self.class, structure_sql_index, nil) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_table_columns.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_table_columns.rb new file mode 100644 index 00000000000..8b441e19654 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_table_columns.rb @@ -0,0 +1,32 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class MissingTableColumns < Base + ERROR_MESSAGE = "The table %s has columns missing from the database" + + def execute + structure_sql.tables.filter_map do |structure_sql_table| + table_name = structure_sql_table.name + database_table = database.fetch_table_by_name(table_name) + + next unless database_table + + inconsistencies = structure_sql_table.columns.filter_map do |structure_table_column| + next if database_table.column_exists?(structure_table_column.name) + + structure_table_column + end + + if inconsistencies.any? + build_inconsistency(self.class, nil, SchemaObjects::Table.new(table_name, inconsistencies)) + end + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_tables.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_tables.rb new file mode 100644 index 00000000000..facf9135dfb --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_tables.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class MissingTables < Base + ERROR_MESSAGE = "The table %s is missing from the database" + + def execute + structure_sql.tables.filter_map do |structure_sql_table| + next if database.table_exists?(structure_sql_table.name) + + build_inconsistency(self.class, structure_sql_table, nil) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_triggers.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_triggers.rb new file mode 100644 index 00000000000..1640d4304c3 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/missing_triggers.rb @@ -0,0 +1,21 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Validators + class MissingTriggers < Base + ERROR_MESSAGE = "The trigger %s is missing from the database" + + def execute + structure_sql.triggers.filter_map do |structure_sql_trigger| + next if database.trigger_exists?(structure_sql_trigger.name) + + build_inconsistency(self.class, structure_sql_trigger, nil) + end + end + end + end + end + end +end diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation/version.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/version.rb new file mode 100644 index 00000000000..40220578c97 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/version.rb @@ -0,0 +1,11 @@ +# frozen_string_literal: true + +module Gitlab + module Schema + module Validation + module Version + VERSION = "0.1.0" + end + end + end +end diff --git a/gems/gitlab-schema-validation/spec/fixtures/structure.sql b/gems/gitlab-schema-validation/spec/fixtures/structure.sql new file mode 100644 index 00000000000..421fb6c3593 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/fixtures/structure.sql @@ -0,0 +1,108 @@ +CREATE INDEX missing_index ON events USING btree (created_at, author_id); + +CREATE UNIQUE INDEX wrong_index ON table_name (column_name, column_name_2); + +CREATE UNIQUE INDEX "index" ON achievements USING btree (namespace_id, lower(name)); + +CREATE INDEX index_namespaces_public_groups_name_id ON namespaces USING btree (name, id) WHERE (((type)::text = 'Group'::text) AND (visibility_level = 20)); + +CREATE UNIQUE INDEX index_on_deploy_keys_id_and_type_and_public ON keys USING btree (id, type) WHERE (public = true); + +CREATE INDEX index_users_on_public_email_excluding_null_and_empty ON users USING btree (public_email) WHERE (((public_email)::text <> ''::text) AND (public_email IS NOT NULL)); + +CREATE TABLE test_table ( + id bigint NOT NULL, + integer_column integer, + integer_with_default_column integer DEFAULT 1, + smallint_column smallint, + smallint_with_default_column smallint DEFAULT 0 NOT NULL, + numeric_column numeric NOT NULL, + numeric_with_default_column numeric DEFAULT 1.0 NOT NULL, + boolean_colum boolean, + boolean_with_default_colum boolean DEFAULT true NOT NULL, + double_precision_column double precision, + double_precision_with_default_column double precision DEFAULT 1.0, + varying_column character varying, + varying_with_default_column character varying DEFAULT 'DEFAULT'::character varying NOT NULL, + varying_with_limit_column character varying(255), + varying_with_limit_and_default_column character varying(255) DEFAULT 'DEFAULT'::character varying, + text_column text NOT NULL, + text_with_default_column text DEFAULT ''::text NOT NULL, + array_column character varying(255)[] NOT NULL, + array_with_default_column character varying(255)[] DEFAULT '{one,two}'::character varying[] NOT NULL, + jsonb_column jsonb, + jsonb_with_default_column jsonb DEFAULT '[]'::jsonb NOT NULL, + timestamptz_column timestamp with time zone, + timestamptz_with_default_column timestamp(6) with time zone DEFAULT now(), + timestamp_column timestamp(6) without time zone NOT NULL, + timestamp_with_default_column timestamp(6) without time zone DEFAULT '2022-01-23 00:00:00+00'::timestamp without time zone NOT NULL, + date_column date, + date_with_default_column date DEFAULT '2023-04-05', + inet_column inet NOT NULL, + inet_with_default_column inet DEFAULT '0.0.0.0'::inet NOT NULL, + macaddr_column macaddr, + macaddr_with_default_column macaddr DEFAULT '00-00-00-00-00-000'::macaddr NOT NULL, + uuid_column uuid NOT NULL, + uuid_with_default_column uuid DEFAULT '00000000-0000-0000-0000-000000000000'::uuid NOT NULL, + bytea_column bytea, + bytea_with_default_column bytea DEFAULT '\xDEADBEEF'::bytea, + unmapped_column_type anyarray, + partition_key bigint DEFAULT 1 NOT NULL, + created_at timestamp with time zone DEFAULT now() NOT NULL +) PARTITION BY HASH (partition_key, created_at); + +CREATE TABLE ci_project_mirrors ( + id bigint NOT NULL, + project_id integer NOT NULL, + namespace_id integer NOT NULL +); + +CREATE TABLE wrong_table ( + id bigint NOT NULL, + description character varying(255) NOT NULL +); + +CREATE TABLE extra_table_columns ( + id bigint NOT NULL, + name character varying(255) NOT NULL +); + +CREATE TABLE missing_table ( + id bigint NOT NULL, + description text NOT NULL +); + +CREATE TABLE missing_table_columns ( + id bigint NOT NULL, + email character varying(255) NOT NULL +); + +CREATE TABLE operations_user_lists ( + id bigint NOT NULL, + project_id bigint NOT NULL, + created_at timestamp with time zone NOT NULL, + updated_at timestamp with time zone NOT NULL, + iid integer NOT NULL, + name character varying(255) NOT NULL, + user_xids text DEFAULT ''::text NOT NULL +); + +CREATE TRIGGER trigger AFTER INSERT ON public.t1 FOR EACH ROW EXECUTE FUNCTION t1(); + +CREATE TRIGGER wrong_trigger BEFORE UPDATE ON public.t2 FOR EACH ROW EXECUTE FUNCTION my_function(); + +CREATE TRIGGER missing_trigger_1 BEFORE INSERT OR UPDATE ON public.t3 FOR EACH ROW EXECUTE FUNCTION t3(); + +CREATE TRIGGER projects_loose_fk_trigger AFTER DELETE ON projects REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records(); + +ALTER TABLE web_hooks + ADD CONSTRAINT web_hooks_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + +ALTER TABLE ONLY issues + ADD CONSTRAINT wrong_definition_fk FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL; + +ALTER TABLE ONLY issues + ADD CONSTRAINT missing_fk FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL; + +ALTER TABLE ONLY bulk_import_configurations + ADD CONSTRAINT fk_rails_536b96bff1 FOREIGN KEY (bulk_import_id) REFERENCES bulk_imports(id) ON DELETE CASCADE; diff --git a/gems/gitlab-schema-validation/spec/gitlab/schema/validation_spec.rb b/gems/gitlab-schema-validation/spec/gitlab/schema/validation_spec.rb new file mode 100644 index 00000000000..f4a06abab48 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/gitlab/schema/validation_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +RSpec.describe Gitlab::Schema::Validation do + it "has a version number" do + expect(Gitlab::Schema::Validation::Version::VERSION).not_to be_nil + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/column_database_adapter_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/column_database_adapter_spec.rb new file mode 100644 index 00000000000..ce16d8468b5 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/column_database_adapter_spec.rb @@ -0,0 +1,72 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Adapters::ColumnDatabaseAdapter, feature_category: :database do + subject(:adapter) { described_class.new(db_result) } + + let(:column_name) { 'email' } + let(:column_default) { "'no-reply@gitlab.com'::character varying" } + let(:not_null) { true } + let(:partition_key) { false } + let(:db_result) do + { + 'table_name' => 'projects', + 'column_name' => column_name, + 'data_type' => 'character varying', + 'column_default' => column_default, + 'not_null' => not_null, + 'partition_key' => partition_key + } + end + + describe '#name' do + it { expect(adapter.name).to eq('email') } + end + + describe '#table_name' do + it { expect(adapter.table_name).to eq('projects') } + end + + describe '#data_type' do + it { expect(adapter.data_type).to eq('character varying') } + end + + describe '#default' do + context "when there's no default value in the column" do + let(:column_default) { nil } + + it { expect(adapter.default).to be_nil } + end + + context 'when the column name is id' do + let(:column_name) { 'id' } + + it { expect(adapter.default).to be_nil } + end + + context 'when the column default includes nextval' do + let(:column_default) { "nextval('my_seq'::regclass)" } + + it { expect(adapter.default).to be_nil } + end + + it { expect(adapter.default).to eq("DEFAULT 'no-reply@gitlab.com'::character varying") } + end + + describe '#nullable' do + context 'when column is not null' do + it { expect(adapter.nullable).to eq('NOT NULL') } + end + + context 'when column is nullable' do + let(:not_null) { false } + + it { expect(adapter.nullable).to be_nil } + end + end + + describe '#partition_key?' do + it { expect(adapter.partition_key?).to be(false) } + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/column_structure_sql_adapter_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/column_structure_sql_adapter_spec.rb new file mode 100644 index 00000000000..ae0d635e8ca --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/column_structure_sql_adapter_spec.rb @@ -0,0 +1,78 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Adapters::ColumnStructureSqlAdapter, feature_category: :database do + subject(:adapter) { described_class.new(table_name, column_def, partition_stmt) } + + let(:table_name) { 'test_table' } + let(:file_path) { 'spec/fixtures/structure.sql' } + let(:table_stmts) { PgQuery.parse(File.read(file_path)).tree.stmts.filter_map { |s| s.stmt.create_stmt } } + let(:table) { table_stmts.find { |table| table.relation.relname == table_name } } + let(:partition_stmt) { table.partspec } + let(:column_stmts) { table.table_elts } + let(:column_def) { column_stmts.find { |col| col.column_def.colname == column_name }.column_def } + + where(:column_name, :data_type, :default_value, :nullable, :partition_key) do + [ + ['id', 'bigint', nil, 'NOT NULL', false], + ['integer_column', 'integer', nil, nil, false], + ['integer_with_default_column', 'integer', 'DEFAULT 1', nil, false], + ['smallint_with_default_column', 'smallint', 'DEFAULT 0', 'NOT NULL', false], + ['double_precision_with_default_column', 'double precision', 'DEFAULT 1.0', nil, false], + ['numeric_with_default_column', 'numeric', 'DEFAULT 1.0', 'NOT NULL', false], + ['boolean_with_default_colum', 'boolean', 'DEFAULT true', 'NOT NULL', false], + ['varying_with_default_column', 'character varying', "DEFAULT 'DEFAULT'::character varying", 'NOT NULL', false], + ['varying_with_limit_and_default_column', 'character varying(255)', "DEFAULT 'DEFAULT'::character varying", + nil, false], + ['text_with_default_column', 'text', "DEFAULT ''::text", 'NOT NULL', false], + ['array_with_default_column', 'character varying(255)[]', "DEFAULT '{one,two}'::character varying[]", + 'NOT NULL', false], + ['jsonb_with_default_column', 'jsonb', "DEFAULT '[]'::jsonb", 'NOT NULL', false], + ['timestamptz_with_default_column', 'timestamp(6) with time zone', 'DEFAULT now()', nil, false], + ['timestamp_with_default_column', 'timestamp(6) without time zone', + "DEFAULT '2022-01-23 00:00:00+00'::timestamp without time zone", 'NOT NULL', false], + ['date_with_default_column', 'date', 'DEFAULT 2023-04-05', nil, false], + ['inet_with_default_column', 'inet', "DEFAULT '0.0.0.0'::inet", 'NOT NULL', false], + ['macaddr_with_default_column', 'macaddr', "DEFAULT '00-00-00-00-00-000'::macaddr", 'NOT NULL', false], + ['uuid_with_default_column', 'uuid', "DEFAULT '00000000-0000-0000-0000-000000000000'::uuid", 'NOT NULL', false], + ['partition_key', 'bigint', 'DEFAULT 1', 'NOT NULL', true], + ['created_at', 'timestamp with time zone', 'DEFAULT now()', 'NOT NULL', true] + ] + end + + with_them do + describe '#name' do + it { expect(adapter.name).to eq(column_name) } + end + + describe '#table_name' do + it { expect(adapter.table_name).to eq(table_name) } + end + + describe '#data_type' do + it { expect(adapter.data_type).to eq(data_type) } + end + + describe '#nullable' do + it { expect(adapter.nullable).to eq(nullable) } + end + + describe '#default' do + it { expect(adapter.default).to eq(default_value) } + end + + describe '#partition_key?' do + it { expect(adapter.partition_key?).to eq(partition_key) } + end + end + + context 'when the data type is not mapped' do + let(:column_name) { 'unmapped_column_type' } + let(:error_class) { Gitlab::Schema::Validation::Adapters::UndefinedPGType } + + describe '#data_type' do + it { expect { adapter.data_type }.to raise_error(error_class) } + end + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/foreign_key_database_adapter_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/foreign_key_database_adapter_spec.rb new file mode 100644 index 00000000000..52689c0f0ec --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/foreign_key_database_adapter_spec.rb @@ -0,0 +1,28 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Adapters::ForeignKeyDatabaseAdapter, feature_category: :database do + subject(:adapter) { described_class.new(query_result) } + + let(:query_result) do + { + 'schema' => 'public', + 'foreign_key_name' => 'fk_2e88fb7ce9', + 'table_name' => 'members', + 'foreign_key_definition' => 'FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE' + } + end + + describe '#name' do + it { expect(adapter.name).to eq('public.fk_2e88fb7ce9') } + end + + describe '#table_name' do + it { expect(adapter.table_name).to eq('members') } + end + + describe '#statement' do + it { expect(adapter.statement).to eq('FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE') } + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/foreign_key_structure_sql_adapter_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/foreign_key_structure_sql_adapter_spec.rb new file mode 100644 index 00000000000..001786b9fbe --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/adapters/foreign_key_structure_sql_adapter_spec.rb @@ -0,0 +1,42 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Adapters::ForeignKeyStructureSqlAdapter, feature_category: :database do + subject(:adapter) { described_class.new(stmt) } + + let(:stmt) { PgQuery.parse(sql).tree.stmts.first.stmt.alter_table_stmt } + + where(:sql, :name, :table_name, :statement) do + [ + [ + 'ALTER TABLE ONLY public.issues ADD CONSTRAINT fk_05f1e72feb FOREIGN KEY (author_id) REFERENCES users (id) ' \ + 'ON DELETE SET NULL', + 'public.fk_05f1e72feb', + 'issues', + 'FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL' + ], + [ + 'ALTER TABLE public.import_failures ADD CONSTRAINT fk_9a9b9ba21c FOREIGN KEY (user_id) REFERENCES users(id) ' \ + 'ON DELETE CASCADE', + 'public.fk_9a9b9ba21c', + 'import_failures', + 'FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE' + ] + ] + end + + with_them do + describe '#name' do + it { expect(adapter.name).to eq(name) } + end + + describe '#table_name' do + it { expect(adapter.table_name).to eq(table_name) } + end + + describe '#statement' do + it { expect(adapter.statement).to eq(statement) } + end + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/inconsistency_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/inconsistency_spec.rb new file mode 100644 index 00000000000..268bb4556e3 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/inconsistency_spec.rb @@ -0,0 +1,96 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Inconsistency do + let(:validator) { Gitlab::Schema::Validation::Validators::DifferentDefinitionIndexes } + + let(:database_statement) { 'CREATE INDEX index_name ON public.achievements USING btree (namespace_id)' } + let(:structure_sql_statement) { 'CREATE INDEX index_name ON public.achievements USING btree (id)' } + + let(:structure_stmt) { PgQuery.parse(structure_sql_statement).tree.stmts.first.stmt.index_stmt } + let(:database_stmt) { PgQuery.parse(database_statement).tree.stmts.first.stmt.index_stmt } + + let(:structure_sql_object) { Gitlab::Schema::Validation::SchemaObjects::Index.new(structure_stmt) } + let(:database_object) { Gitlab::Schema::Validation::SchemaObjects::Index.new(database_stmt) } + + subject(:inconsistency) { described_class.new(validator, structure_sql_object, database_object) } + + describe '#object_name' do + it 'returns the index name' do + expect(inconsistency.object_name).to eq('index_name') + end + end + + describe '#diff' do + it 'returns a diff between the structure.sql and the database' do + expect(inconsistency.diff).to be_a(Diffy::Diff) + expect(inconsistency.diff.string1).to eq("#{structure_sql_statement}\n") + expect(inconsistency.diff.string2).to eq("#{database_statement}\n") + end + end + + describe '#error_message' do + it 'returns the error message' do + stub_const "#{validator}::ERROR_MESSAGE", 'error message %s' + + expect(inconsistency.error_message).to eq('error message index_name') + end + end + + describe '#type' do + it 'returns the type of the validator' do + expect(inconsistency.type).to eq('Gitlab::Schema::Validation::Validators::DifferentDefinitionIndexes') + end + end + + describe '#table_name' do + it 'returns the table name' do + expect(inconsistency.table_name).to eq('achievements') + end + end + + describe '#object_type' do + it 'returns the structure sql object type' do + expect(inconsistency.object_type).to eq('Index') + end + + context 'when the structure sql object is not available' do + subject(:inconsistency) { described_class.new(validator, nil, database_object) } + + it 'returns the database object type' do + expect(inconsistency.object_type).to eq('Index') + end + end + end + + describe '#structure_sql_statement' do + it 'returns structure sql statement' do + expect(inconsistency.structure_sql_statement).to eq("#{structure_sql_statement}\n") + end + end + + describe '#database_statement' do + it 'returns database statement' do + expect(inconsistency.database_statement).to eq("#{database_statement}\n") + end + end + + describe '#display' do + let(:expected_output) do + <<~MSG + ------------------------------------------------------ + The index_name index has a different statement between structure.sql and database + Diff: + \e[31m-CREATE INDEX index_name ON public.achievements USING btree (id)\e[0m + \e[32m+CREATE INDEX index_name ON public.achievements USING btree (namespace_id)\e[0m + + ------------------------------------------------------ + MSG + end + + it 'prints the inconsistency message' do + expect(inconsistency.display).to eql(expected_output) + end + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/column_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/column_spec.rb new file mode 100644 index 00000000000..c002903e765 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/column_spec.rb @@ -0,0 +1,25 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::SchemaObjects::Column, feature_category: :database do + subject(:column) { described_class.new(adapter) } + + let(:database_adapter) { 'Gitlab::Schema::Validation::Adapters::ColumnDatabaseAdapter' } + let(:adapter) do + instance_double(database_adapter, name: 'id', table_name: 'projects', + data_type: 'bigint', default: nil, nullable: 'NOT NULL') + end + + describe '#name' do + it { expect(column.name).to eq('id') } + end + + describe '#table_name' do + it { expect(column.table_name).to eq('projects') } + end + + describe '#statement' do + it { expect(column.statement).to eq('id bigint NOT NULL') } + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/foreign_key_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/foreign_key_spec.rb new file mode 100644 index 00000000000..bfe337b6e7c --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/foreign_key_spec.rb @@ -0,0 +1,25 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::SchemaObjects::ForeignKey, feature_category: :database do + subject(:foreign_key) { described_class.new(adapter) } + + let(:database_adapter) { 'Gitlab::Schema::Validation::Adapters::ForeignKeyDatabaseAdapter' } + let(:adapter) do + instance_double(database_adapter, name: 'public.fk_1d37cddf91', table_name: 'vulnerabilities', + statement: 'FOREIGN KEY (epic_id) REFERENCES epics(id) ON DELETE SET NULL') + end + + describe '#name' do + it { expect(foreign_key.name).to eq('public.fk_1d37cddf91') } + end + + describe '#table_name' do + it { expect(foreign_key.table_name).to eq('vulnerabilities') } + end + + describe '#statement' do + it { expect(foreign_key.statement).to eq('FOREIGN KEY (epic_id) REFERENCES epics(id) ON DELETE SET NULL') } + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/index_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/index_spec.rb new file mode 100644 index 00000000000..dfef440d99e --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/index_spec.rb @@ -0,0 +1,11 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::SchemaObjects::Index, feature_category: :database do + let(:statement) { 'CREATE INDEX index_name ON public.achievements USING btree (namespace_id)' } + let(:name) { 'index_name' } + let(:table_name) { 'achievements' } + + include_examples 'schema objects assertions for', 'index_stmt' +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/table_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/table_spec.rb new file mode 100644 index 00000000000..87555c88edf --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/table_spec.rb @@ -0,0 +1,45 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::SchemaObjects::Table, feature_category: :database do + subject(:table) { described_class.new(name, columns) } + + let(:name) { 'my_table' } + let(:column_class) { 'Gitlab::Schema::Validation::SchemaObjects::Column' } + let(:columns) do + [ + instance_double(column_class, name: 'id', statement: 'id bigint NOT NULL', partition_key?: false), + instance_double(column_class, name: 'col', statement: 'col text', partition_key?: false), + instance_double(column_class, name: 'partition', statement: 'partition integer DEFAULT 1', partition_key?: true) + ] + end + + describe '#name' do + it { expect(table.name).to eq('my_table') } + end + + describe '#table_name' do + it { expect(table.table_name).to eq('my_table') } + end + + describe '#statement' do + it { expect(table.statement).to eq('CREATE TABLE my_table (id bigint NOT NULL, col text)') } + + it 'ignores the partition column' do + expect(table.statement).not_to include('partition integer DEFAULT 1') + end + end + + describe '#fetch_column_by_name' do + it { expect(table.fetch_column_by_name('col')).not_to be_nil } + + it { expect(table.fetch_column_by_name('invalid')).to be_nil } + end + + describe '#column_exists?' do + it { expect(table.column_exists?('col')).to be(true) } + + it { expect(table.column_exists?('invalid')).to be(false) } + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/trigger_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/trigger_spec.rb new file mode 100644 index 00000000000..b6d0ba38ebb --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/schema_objects/trigger_spec.rb @@ -0,0 +1,11 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::SchemaObjects::Trigger, feature_category: :database do + let(:statement) { 'CREATE TRIGGER my_trigger BEFORE INSERT ON todos FOR EACH ROW EXECUTE FUNCTION trigger()' } + let(:name) { 'my_trigger' } + let(:table_name) { 'todos' } + + include_examples 'schema objects assertions for', 'create_trig_stmt' +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/sources/structure_sql_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/sources/structure_sql_spec.rb new file mode 100644 index 00000000000..7d4a23b1619 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/sources/structure_sql_spec.rb @@ -0,0 +1,66 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.shared_examples 'structure sql schema assertions for' do |object_exists_method, all_objects_method| + subject(:structure_sql) { described_class.new(structure_file_path, schema_name) } + + let(:structure_file_path) { 'spec/fixtures/structure.sql' } + let(:schema_name) { 'public' } + + describe "##{object_exists_method}" do + it 'returns true when schema object exists' do + expect(structure_sql.public_send(object_exists_method, valid_schema_object_name)).to be_truthy + end + + it 'returns false when schema object does not exists' do + expect(structure_sql.public_send(object_exists_method, 'invalid-object-name')).to be_falsey + end + end + + describe "##{all_objects_method}" do + it 'returns all the schema objects' do + schema_objects = structure_sql.public_send(all_objects_method) + + expect(schema_objects).to all(be_a(schema_object)) + expect(schema_objects.map(&:name)).to eq(expected_objects) + end + end +end + +RSpec.describe Gitlab::Schema::Validation::Sources::StructureSql, feature_category: :database do + let(:structure_file_path) { 'spec/fixtures/structure.sql' } + let(:schema_name) { 'public' } + + subject(:structure_sql) { described_class.new(structure_file_path, schema_name) } + + context 'when having indexes' do + let(:schema_object) { Gitlab::Schema::Validation::SchemaObjects::Index } + let(:valid_schema_object_name) { 'index' } + let(:expected_objects) do + %w[missing_index wrong_index index index_namespaces_public_groups_name_id + index_on_deploy_keys_id_and_type_and_public index_users_on_public_email_excluding_null_and_empty] + end + + include_examples 'structure sql schema assertions for', 'index_exists?', 'indexes' + end + + context 'when having triggers' do + let(:schema_object) { Gitlab::Schema::Validation::SchemaObjects::Trigger } + let(:valid_schema_object_name) { 'trigger' } + let(:expected_objects) { %w[trigger wrong_trigger missing_trigger_1 projects_loose_fk_trigger] } + + include_examples 'structure sql schema assertions for', 'trigger_exists?', 'triggers' + end + + context 'when having tables' do + let(:schema_object) { Gitlab::Schema::Validation::SchemaObjects::Table } + let(:valid_schema_object_name) { 'test_table' } + let(:expected_objects) do + %w[test_table ci_project_mirrors wrong_table extra_table_columns missing_table missing_table_columns + operations_user_lists] + end + + include_examples 'structure sql schema assertions for', 'table_exists?', 'tables' + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/base_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/base_spec.rb new file mode 100644 index 00000000000..50be1f1b373 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/base_spec.rb @@ -0,0 +1,39 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::Base, feature_category: :database do + describe '#execute' do + let(:structure_sql) { instance_double(Gitlab::Schema::Validation::Sources::StructureSql) } + let(:database) { instance_double(Gitlab::Schema::Validation::Sources::Database) } + + subject(:inconsistencies) { described_class.new(structure_sql, database).execute } + + describe '.all_validators' do + subject(:all_validators) { described_class.all_validators } + + it 'returns an array of all validators' do + expect(all_validators).to eq([ + Gitlab::Schema::Validation::Validators::ExtraTables, + Gitlab::Schema::Validation::Validators::ExtraTableColumns, + Gitlab::Schema::Validation::Validators::ExtraIndexes, + Gitlab::Schema::Validation::Validators::ExtraTriggers, + Gitlab::Schema::Validation::Validators::ExtraForeignKeys, + Gitlab::Schema::Validation::Validators::MissingTables, + Gitlab::Schema::Validation::Validators::MissingTableColumns, + Gitlab::Schema::Validation::Validators::MissingIndexes, + Gitlab::Schema::Validation::Validators::MissingTriggers, + Gitlab::Schema::Validation::Validators::MissingForeignKeys, + Gitlab::Schema::Validation::Validators::DifferentDefinitionTables, + Gitlab::Schema::Validation::Validators::DifferentDefinitionIndexes, + Gitlab::Schema::Validation::Validators::DifferentDefinitionTriggers, + Gitlab::Schema::Validation::Validators::DifferentDefinitionForeignKeys + ]) + end + end + + it 'raises an exception' do + expect { inconsistencies }.to raise_error(NoMethodError) + end + end +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/different_definition_indexes_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/different_definition_indexes_spec.rb new file mode 100644 index 00000000000..c1795a56063 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/different_definition_indexes_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::DifferentDefinitionIndexes do + include_examples 'index validators', described_class, ['wrong_index'] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/different_definition_tables_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/different_definition_tables_spec.rb new file mode 100644 index 00000000000..d1c9169a59a --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/different_definition_tables_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::DifferentDefinitionTables, feature_category: :database do + include_examples 'table validators', described_class, ['wrong_table'] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/different_definition_triggers_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/different_definition_triggers_spec.rb new file mode 100644 index 00000000000..a2597e3e55e --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/different_definition_triggers_spec.rb @@ -0,0 +1,8 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::DifferentDefinitionTriggers, + feature_category: :database do + include_examples 'trigger validators', described_class, ['wrong_trigger'] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_foreign_keys_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_foreign_keys_spec.rb new file mode 100644 index 00000000000..499f2578fd2 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_foreign_keys_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::ExtraForeignKeys, feature_category: :database do + include_examples 'foreign key validators', described_class, ['public.extra_fk'] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_indexes_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_indexes_spec.rb new file mode 100644 index 00000000000..01498444ba4 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_indexes_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::ExtraIndexes, feature_category: :database do + include_examples 'index validators', described_class, ['extra_index'] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_table_columns_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_table_columns_spec.rb new file mode 100644 index 00000000000..f5d06e9941a --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_table_columns_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::ExtraTableColumns, feature_category: :database do + include_examples 'table validators', described_class, ['extra_table_columns'] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_tables_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_tables_spec.rb new file mode 100644 index 00000000000..15c52fe4719 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_tables_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::ExtraTables, feature_category: :database do + include_examples 'table validators', described_class, ['extra_table'] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_triggers_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_triggers_spec.rb new file mode 100644 index 00000000000..97126aebf05 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/extra_triggers_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::ExtraTriggers, feature_category: :database do + include_examples 'trigger validators', described_class, ['extra_trigger'] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_foreign_keys_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_foreign_keys_spec.rb new file mode 100644 index 00000000000..6682c3f623d --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_foreign_keys_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::MissingForeignKeys, feature_category: :database do + include_examples 'foreign key validators', described_class, %w[public.fk_rails_536b96bff1 public.missing_fk] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_indexes_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_indexes_spec.rb new file mode 100644 index 00000000000..c1cb9a2416b --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_indexes_spec.rb @@ -0,0 +1,14 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::MissingIndexes, feature_category: :database do + missing_indexes = %w[ + missing_index + index_namespaces_public_groups_name_id + index_on_deploy_keys_id_and_type_and_public + index_users_on_public_email_excluding_null_and_empty + ] + + include_examples 'index validators', described_class, missing_indexes +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_table_columns_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_table_columns_spec.rb new file mode 100644 index 00000000000..3866bdce071 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_table_columns_spec.rb @@ -0,0 +1,7 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::MissingTableColumns, feature_category: :database do + include_examples 'table validators', described_class, ['missing_table_columns'] +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_tables_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_tables_spec.rb new file mode 100644 index 00000000000..8a73d67ab7d --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_tables_spec.rb @@ -0,0 +1,9 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::MissingTables, feature_category: :database do + missing_tables = %w[ci_project_mirrors missing_table operations_user_lists test_table] + + include_examples 'table validators', described_class, missing_tables +end diff --git a/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_triggers_spec.rb b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_triggers_spec.rb new file mode 100644 index 00000000000..82b9b034503 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/lib/gitlab/schema/validation/validators/missing_triggers_spec.rb @@ -0,0 +1,9 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::Schema::Validation::Validators::MissingTriggers, feature_category: :database do + missing_triggers = %w[missing_trigger_1 projects_loose_fk_trigger] + + include_examples 'trigger validators', described_class, missing_triggers +end diff --git a/gems/gitlab-schema-validation/spec/spec_helper.rb b/gems/gitlab-schema-validation/spec/spec_helper.rb new file mode 100644 index 00000000000..c11c5021e3b --- /dev/null +++ b/gems/gitlab-schema-validation/spec/spec_helper.rb @@ -0,0 +1,18 @@ +# frozen_string_literal: true + +require "gitlab/schema/validation" +require 'rspec-parameterized' + +RSpec.configure do |config| + # Enable flags like --only-failures and --next-failure + config.example_status_persistence_file_path = ".rspec_status" + + # Disable RSpec exposing methods globally on `Module` and `main` + config.disable_monkey_patching! + + Dir['./spec/support/**/*.rb'].each { |f| require f } + + config.expect_with :rspec do |c| + c.syntax = :expect + end +end diff --git a/gems/gitlab-schema-validation/spec/support/shared_examples/foreign_key_validators_shared_examples.rb b/gems/gitlab-schema-validation/spec/support/shared_examples/foreign_key_validators_shared_examples.rb new file mode 100644 index 00000000000..1f33c8bd760 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/support/shared_examples/foreign_key_validators_shared_examples.rb @@ -0,0 +1,46 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.shared_examples 'foreign key validators' do |validator, expected_result| + subject(:result) { validator.new(structure_file, database).execute } + + let(:structure_file_path) { 'spec/fixtures/structure.sql' } + let(:structure_file) { Gitlab::Schema::Validation::Sources::StructureSql.new(structure_file_path, schema) } + let(:inconsistency_type) { validator.to_s } + let(:database_name) { 'main' } + let(:schema) { 'public' } + # rubocop:disable RSpec/VerifiedDoubleReference + let(:connection) { instance_double('connection', exec_query: database_query, current_schema: 'public') } + # rubocop:enable RSpec/VerifiedDoubleReference + + let(:database) { Gitlab::Schema::Validation::Sources::Database.new(connection) } + + let(:database_query) do + [ + { + 'schema' => schema, + 'table_name' => 'web_hooks', + 'foreign_key_name' => 'web_hooks_project_id_fkey', + 'foreign_key_definition' => 'FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE' + }, + { + 'schema' => schema, + 'table_name' => 'issues', + 'foreign_key_name' => 'wrong_definition_fk', + 'foreign_key_definition' => 'FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE' + }, + { + 'schema' => schema, + 'table_name' => 'projects', + 'foreign_key_name' => 'extra_fk', + 'foreign_key_definition' => 'FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE' + } + ] + end + + it 'returns trigger inconsistencies' do + expect(result.map(&:object_name)).to match_array(expected_result) + expect(result.map(&:type)).to all(eql inconsistency_type) + end +end diff --git a/gems/gitlab-schema-validation/spec/support/shared_examples/index_validators_shared_examples.rb b/gems/gitlab-schema-validation/spec/support/shared_examples/index_validators_shared_examples.rb new file mode 100644 index 00000000000..cc20c0dc765 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/support/shared_examples/index_validators_shared_examples.rb @@ -0,0 +1,32 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.shared_examples 'index validators' do |validator, expected_result| + let(:structure_file_path) { 'spec/fixtures/structure.sql' } + let(:database_indexes) do + [ + ['wrong_index', 'CREATE UNIQUE INDEX wrong_index ON public.table_name (column_name)'], + ['extra_index', 'CREATE INDEX extra_index ON public.table_name (column_name)'], + ['index', 'CREATE UNIQUE INDEX "index" ON public.achievements USING btree (namespace_id, lower(name))'] + ] + end + + let(:inconsistency_type) { validator.name } + + # rubocop:disable RSpec/VerifiedDoubleReference + let(:connection) { instance_double('connection', select_rows: database_indexes, current_schema: 'public') } + # rubocop:enable RSpec/VerifiedDoubleReference + + let(:schema) { 'public' } + + let(:database) { Gitlab::Schema::Validation::Sources::Database.new(connection) } + let(:structure_file) { Gitlab::Schema::Validation::Sources::StructureSql.new(structure_file_path, schema) } + + subject(:result) { validator.new(structure_file, database).execute } + + it 'returns index inconsistencies' do + expect(result.map(&:object_name)).to match_array(expected_result) + expect(result.map(&:type)).to all(eql inconsistency_type) + end +end diff --git a/gems/gitlab-schema-validation/spec/support/shared_examples/schema_objects_shared_examples.rb b/gems/gitlab-schema-validation/spec/support/shared_examples/schema_objects_shared_examples.rb new file mode 100644 index 00000000000..994b30b0941 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/support/shared_examples/schema_objects_shared_examples.rb @@ -0,0 +1,26 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.shared_examples 'schema objects assertions for' do |stmt_name| + let(:stmt) { PgQuery.parse(statement).tree.stmts.first.stmt } + let(:schema_object) { described_class.new(stmt.public_send(stmt_name)) } + + describe '#name' do + it 'returns schema object name' do + expect(schema_object.name).to eq(name) + end + end + + describe '#statement' do + it 'returns schema object statement' do + expect(schema_object.statement).to eq(statement) + end + end + + describe '#table_name' do + it 'returns schema object table_name' do + expect(schema_object.table_name).to eq(table_name) + end + end +end diff --git a/gems/gitlab-schema-validation/spec/support/shared_examples/table_validators_shared_examples.rb b/gems/gitlab-schema-validation/spec/support/shared_examples/table_validators_shared_examples.rb new file mode 100644 index 00000000000..d2a51a9b202 --- /dev/null +++ b/gems/gitlab-schema-validation/spec/support/shared_examples/table_validators_shared_examples.rb @@ -0,0 +1,81 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.shared_examples "table validators" do |validator, expected_result| + subject(:result) { validator.new(structure_file, database).execute } + + let(:structure_file_path) { 'spec/fixtures/structure.sql' } + let(:inconsistency_type) { validator.to_s } + # rubocop:disable RSpec/VerifiedDoubleReference + let(:connection) { instance_double('connection', exec_query: database_tables, current_schema: 'public') } + # rubocop:enable RSpec/VerifiedDoubleReference + let(:schema) { 'public' } + let(:database) { Gitlab::Schema::Validation::Sources::Database.new(connection) } + let(:structure_file) { Gitlab::Schema::Validation::Sources::StructureSql.new(structure_file_path, schema) } + let(:database_tables) do + [ + { + 'table_name' => 'wrong_table', + 'column_name' => 'id', + 'not_null' => true, + 'data_type' => 'integer', + 'column_default' => "nextval('audit_events_id_seq'::regclass)" + }, + { + 'table_name' => 'wrong_table', + 'column_name' => 'description', + 'not_null' => true, + 'data_type' => 'character varying', + 'column_default' => nil + }, + { + 'table_name' => 'extra_table', + 'column_name' => 'id', + 'not_null' => true, + 'data_type' => 'integer', + 'column_default' => "nextval('audit_events_id_seq'::regclass)" + }, + { + 'table_name' => 'extra_table', + 'column_name' => 'email', + 'not_null' => true, + 'data_type' => 'character varying', + 'column_default' => nil + }, + { + 'table_name' => 'extra_table_columns', + 'column_name' => 'id', + 'not_null' => true, + 'data_type' => 'bigint', + 'column_default' => "nextval('audit_events_id_seq'::regclass)" + }, + { + 'table_name' => 'extra_table_columns', + 'column_name' => 'name', + 'not_null' => true, + 'data_type' => 'character varying(255)', + 'column_default' => nil + }, + { + 'table_name' => 'extra_table_columns', + 'column_name' => 'extra_column', + 'not_null' => true, + 'data_type' => 'character varying(255)', + 'column_default' => nil + }, + { + 'table_name' => 'missing_table_columns', + 'column_name' => 'id', + 'not_null' => true, + 'data_type' => 'bigint', + 'column_default' => 'NOT NULL' + } + ] + end + + it 'returns table inconsistencies' do + expect(result.map(&:object_name)).to match_array(expected_result) + expect(result.map(&:type)).to all(eql inconsistency_type) + end +end diff --git a/gems/gitlab-schema-validation/spec/support/shared_examples/trigger_validators_shared_examples.rb b/gems/gitlab-schema-validation/spec/support/shared_examples/trigger_validators_shared_examples.rb new file mode 100644 index 00000000000..45ed87082bb --- /dev/null +++ b/gems/gitlab-schema-validation/spec/support/shared_examples/trigger_validators_shared_examples.rb @@ -0,0 +1,31 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.shared_examples 'trigger validators' do |validator, expected_result| + subject(:result) { validator.new(structure_file, database).execute } + + let(:structure_file_path) { 'spec/fixtures/structure.sql' } + let(:structure_file) { Gitlab::Schema::Validation::Sources::StructureSql.new(structure_file_path, schema) } + let(:inconsistency_type) { validator.to_s } + let(:database_name) { 'main' } + let(:schema) { 'public' } + let(:database) { Gitlab::Schema::Validation::Sources::Database.new(connection) } + + # rubocop:disable RSpec/VerifiedDoubleReference + let(:connection) { instance_double('connection', select_rows: database_triggers, current_schema: 'public') } + # rubocop:enable RSpec/VerifiedDoubleReference + + let(:database_triggers) do + [ + ['trigger', 'CREATE TRIGGER trigger AFTER INSERT ON public.t1 FOR EACH ROW EXECUTE FUNCTION t1()'], + ['wrong_trigger', 'CREATE TRIGGER wrong_trigger BEFORE UPDATE ON public.t2 FOR EACH ROW EXECUTE FUNCTION t2()'], + ['extra_trigger', 'CREATE TRIGGER extra_trigger BEFORE INSERT ON public.t4 FOR EACH ROW EXECUTE FUNCTION t4()'] + ] + end + + it 'returns trigger inconsistencies' do + expect(result.map(&:object_name)).to match_array(expected_result) + expect(result.map(&:type)).to all(eql inconsistency_type) + end +end |