# frozen_string_literal: true require "cases/helper_sqlserver" class SchemaDumperTestSQLServer < ActiveRecord::TestCase before { all_tables } let(:all_tables) { ActiveRecord::Base.connection.tables } let(:schema) { @generated_schema } it "sst_datatypes" do generate_schema_for_table "sst_datatypes" assert_line :bigint, type: "bigint", limit: nil, precision: nil, scale: nil, default: 42 assert_line :int, type: "integer", limit: nil, precision: nil, scale: nil, default: 42 assert_line :smallint, type: "integer", limit: 2, precision: nil, scale: nil, default: 42 assert_line :tinyint, type: "integer", limit: 1, precision: nil, scale: nil, default: 42 assert_line :bit, type: "boolean", limit: nil, precision: nil, scale: nil, default: true assert_line :decimal_9_2, type: "decimal", limit: nil, precision: 9, scale: 2, default: 12345.01 assert_line :numeric_18_0, type: "decimal", limit: nil, precision: 18, scale: nil, default: 191 assert_line :numeric_36_2, type: "decimal", limit: nil, precision: 36, scale: 2, default: 12345678901234567890.01 assert_line :money, type: "money", limit: nil, precision: 19, scale: 4, default: 4.2 assert_line :smallmoney, type: "smallmoney", limit: nil, precision: 10, scale: 4, default: 4.2 # Approximate Numerics assert_line :float, type: "float", limit: nil, precision: nil, scale: nil, default: 123.00000001 assert_line :real, type: "real", limit: nil, precision: nil, scale: nil, default: 123.45 # Date and Time assert_line :date, type: "date", limit: nil, precision: nil, scale: nil, default: "01-01-0001" assert_line :datetime, type: "datetime", limit: nil, precision: nil, scale: nil, default: "01-01-1753 00:00:00.123" if connection_dblib_73? assert_line :datetime2_7, type: "datetime", limit: nil, precision: 7, scale: nil, default: "12-31-9999 23:59:59.9999999" assert_line :datetime2_3, type: "datetime", limit: nil, precision: 3, scale: nil, default: nil assert_line :datetime2_1, type: "datetime", limit: nil, precision: 1, scale: nil, default: nil end assert_line :smalldatetime, type: "smalldatetime", limit: nil, precision: nil, scale: nil, default: "01-01-1901 15:45:00.0" if connection_dblib_73? assert_line :time_7, type: "time", limit: nil, precision: 7, scale: nil, default: "04:20:00.2883215" assert_line :time_2, type: "time", limit: nil, precision: 2, scale: nil, default: nil assert_line :time_default, type: "time", limit: nil, precision: 7, scale: nil, default: "15:03:42.0621978" end # Character Strings assert_line :char_10, type: "char", limit: 10, precision: nil, scale: nil, default: "1234567890", collation: nil assert_line :varchar_50, type: "varchar", limit: 50, precision: nil, scale: nil, default: "test varchar_50", collation: nil assert_line :varchar_max, type: "varchar_max", limit: nil, precision: nil, scale: nil, default: "test varchar_max", collation: nil assert_line :text, type: "text_basic", limit: nil, precision: nil, scale: nil, default: "test text", collation: nil # Unicode Character Strings assert_line :nchar_10, type: "nchar", limit: 10, precision: nil, scale: nil, default: "12345678åå", collation: nil assert_line :nvarchar_50, type: "string", limit: 50, precision: nil, scale: nil, default: "test nvarchar_50 åå", collation: nil assert_line :nvarchar_max, type: "text", limit: nil, precision: nil, scale: nil, default: "test nvarchar_max åå", collation: nil assert_line :ntext, type: "ntext", limit: nil, precision: nil, scale: nil, default: "test ntext åå", collation: nil # Binary Strings assert_line :binary_49, type: "binary_basic", limit: 49, precision: nil, scale: nil, default: nil assert_line :varbinary_49, type: "varbinary", limit: 49, precision: nil, scale: nil, default: nil assert_line :varbinary_max, type: "binary", limit: nil, precision: nil, scale: nil, default: nil # Other Data Types assert_line :uniqueidentifier, type: "uuid", limit: nil, precision: nil, scale: nil, default: -> { "newid()" } assert_line :timestamp, type: "ss_timestamp", limit: nil, precision: nil, scale: nil, default: nil end it "sst_datatypes_migration" do columns = SSTestDatatypeMigration.columns_hash generate_schema_for_table "sst_datatypes_migration" # Simple Rails conventions _(columns["integer_col"].sql_type).must_equal "int(4)" _(columns["bigint_col"].sql_type).must_equal "bigint(8)" _(columns["boolean_col"].sql_type).must_equal "bit" _(columns["decimal_col"].sql_type).must_equal "decimal(18,0)" _(columns["float_col"].sql_type).must_equal "float" _(columns["string_col"].sql_type).must_equal "nvarchar(4000)" _(columns["text_col"].sql_type).must_equal "nvarchar(max)" _(columns["datetime_col"].sql_type).must_equal "datetime" _(columns["timestamp_col"].sql_type).must_equal "datetime" _(columns["time_col"].sql_type).must_equal "time(7)" _(columns["date_col"].sql_type).must_equal "date" _(columns["binary_col"].sql_type).must_equal "varbinary(max)" assert_line :integer_col, type: "integer", limit: nil, precision: nil, scale: nil, default: nil assert_line :bigint_col, type: "bigint", limit: nil, precision: nil, scale: nil, default: nil assert_line :boolean_col, type: "boolean", limit: nil, precision: nil, scale: nil, default: nil assert_line :decimal_col, type: "decimal", limit: nil, precision: 18, scale: nil, default: nil assert_line :float_col, type: "float", limit: nil, precision: nil, scale: nil, default: nil assert_line :string_col, type: "string", limit: nil, precision: nil, scale: nil, default: nil assert_line :text_col, type: "text", limit: nil, precision: nil, scale: nil, default: nil assert_line :datetime_col, type: "datetime", limit: nil, precision: nil, scale: nil, default: nil assert_line :timestamp_col, type: "datetime", limit: nil, precision: nil, scale: nil, default: nil assert_line :time_col, type: "time", limit: nil, precision: 7, scale: nil, default: nil assert_line :date_col, type: "date", limit: nil, precision: nil, scale: nil, default: nil assert_line :binary_col, type: "binary", limit: nil, precision: nil, scale: nil, default: nil # Our type methods. _(columns["real_col"].sql_type).must_equal "real" _(columns["money_col"].sql_type).must_equal "money" _(columns["smalldatetime_col"].sql_type).must_equal "smalldatetime" _(columns["datetime2_col"].sql_type).must_equal "datetime2(7)" _(columns["datetimeoffset"].sql_type).must_equal "datetimeoffset(7)" _(columns["smallmoney_col"].sql_type).must_equal "smallmoney" _(columns["char_col"].sql_type).must_equal "char(1)" _(columns["varchar_col"].sql_type).must_equal "varchar(8000)" _(columns["text_basic_col"].sql_type).must_equal "text" _(columns["nchar_col"].sql_type).must_equal "nchar(1)" _(columns["ntext_col"].sql_type).must_equal "ntext" _(columns["binary_basic_col"].sql_type).must_equal "binary(1)" _(columns["varbinary_col"].sql_type).must_equal "varbinary(8000)" _(columns["uuid_col"].sql_type).must_equal "uniqueidentifier" _(columns["sstimestamp_col"].sql_type).must_equal "timestamp" _(columns["json_col"].sql_type).must_equal "nvarchar(max)" assert_line :real_col, type: "real", limit: nil, precision: nil, scale: nil, default: nil assert_line :money_col, type: "money", limit: nil, precision: 19, scale: 4, default: nil assert_line :smalldatetime_col, type: "smalldatetime", limit: nil, precision: nil, scale: nil, default: nil assert_line :datetime2_col, type: "datetime", limit: nil, precision: 7, scale: nil, default: nil assert_line :datetimeoffset, type: "datetimeoffset", limit: nil, precision: 7, scale: nil, default: nil assert_line :smallmoney_col, type: "smallmoney", limit: nil, precision: 10, scale: 4, default: nil assert_line :char_col, type: "char", limit: 1, precision: nil, scale: nil, default: nil assert_line :varchar_col, type: "varchar", limit: nil, precision: nil, scale: nil, default: nil assert_line :text_basic_col, type: "text_basic", limit: nil, precision: nil, scale: nil, default: nil assert_line :nchar_col, type: "nchar", limit: 1, precision: nil, scale: nil, default: nil assert_line :ntext_col, type: "ntext", limit: nil, precision: nil, scale: nil, default: nil assert_line :binary_basic_col, type: "binary_basic", limit: 1, precision: nil, scale: nil, default: nil assert_line :varbinary_col, type: "varbinary", limit: nil, precision: nil, scale: nil, default: nil assert_line :uuid_col, type: "uuid", limit: nil, precision: nil, scale: nil, default: nil assert_line :sstimestamp_col, type: "ss_timestamp", limit: nil, precision: nil, scale: nil, default: nil assert_line :json_col, type: "text", limit: nil, precision: nil, scale: nil, default: nil end # Special Cases it "honor nonstandard primary keys" do generate_schema_for_table("movies") do |output| match = output.match(%r{create_table "movies"(.*)do}) assert_not_nil(match, "nonstandardpk table not found") assert_match %r(primary_key: "movieid"), match[1], "non-standard primary key not preserved" end end it "no id with model driven primary key" do output = generate_schema_for_table "sst_no_pk_data" _(output).must_match %r{create_table "sst_no_pk_data".*id:\sfalse.*do} assert_line :name, type: "string", limit: nil, default: nil, collation: nil end it "dumps field with unique key constraints only once" do output = generate_schema_for_table "unique_key_dumped_table" _(output.scan('t.integer "unique_field"').length).must_equal(1) end private def generate_schema_for_table(*table_names) require "stringio" stream = StringIO.new ActiveRecord::SchemaDumper.ignore_tables = all_tables - table_names ActiveRecord::SchemaDumper.dump(ActiveRecord::Base.connection, stream) @generated_schema = stream.string yield @generated_schema if block_given? @schema_lines = Hash.new type_matcher = /\A\s+t\.\w+\s+"(.*?)"[,\n]/ @generated_schema.each_line do |line| next unless line =~ type_matcher @schema_lines[Regexp.last_match[1]] = SchemaLine.new(line) end @generated_schema end def line(column_name) @schema_lines[column_name.to_s] end def assert_line(column_name, options = {}) line = line(column_name) assert line, "Could not find line with column name: #{column_name.inspect} in schema:\n#{schema}" [:type, :limit, :precision, :scale, :collation, :default].each do |key| next unless options.key?(key) actual = key == :type ? line.send(:type_method) : line.send(key) expected = options[key] message = "#{key.to_s.titleize} of #{expected.inspect} not found in:\n#{line}" if expected.nil? _(actual).must_be_nil message elsif expected.is_a?(Array) _(actual).must_include expected, message elsif expected.is_a?(Float) _(actual).must_be_close_to expected, 0.001 elsif expected.is_a?(Proc) _(actual.call).must_equal(expected.call) else _(actual).must_equal expected, message end end end class SchemaLine LINE_PARSER = %r{t\.(\w+)\s+"(.*?)"[,\s+](.*)} attr_reader :line, :type_method, :col_name, :options def self.option(method_name) define_method(method_name) { options.present? ? options[method_name.to_sym] : nil } end def initialize(line) @line = line @type_method, @col_name, @options = parse_line end option :limit option :precision option :scale option :default option :collation def to_s line.squish end def inspect "#" end private def parse_line _all, type_method, col_name, options = @line.match(LINE_PARSER).to_a options = parse_options(options) [type_method, col_name, options] end def parse_options(opts) if opts.present? eval "{#{opts}}" else {} end rescue SyntaxError {} end end end