diff options
Diffstat (limited to 'gems/gitlab-schema-validation/lib/gitlab')
31 files changed, 1362 insertions, 1 deletions
diff --git a/gems/gitlab-schema-validation/lib/gitlab/schema/validation.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation.rb index 41e88f21d67..5211358a197 100644 --- a/gems/gitlab-schema-validation/lib/gitlab/schema/validation.rb +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation.rb @@ -1,10 +1,66 @@ # frozen_string_literal: true -require_relative "validation/version" +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..de1351a3a02 --- /dev/null +++ b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/base.rb @@ -0,0 +1,30 @@ +# 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 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_indexes_spec.rb b/gems/gitlab-schema-validation/lib/gitlab/schema/validation/validators/extra_indexes_spec.rb new file mode 100644 index 00000000000..73169fa0403 --- /dev/null +++ b/gems/gitlab-schema-validation/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 do + include_examples 'index validators', described_class, ['extra_index'] +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 |