From a3f459eecfec2beda2f51fb6d62eca4e5d5fbd70 Mon Sep 17 00:00:00 2001 From: Jeremy Kemper Date: Sat, 8 Jul 2006 17:13:21 +0000 Subject: Firebird migrations support. Closes #5337. git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@4594 5ecf4fe2-1ee6-0310-87b1-e25e094e27de --- activerecord/CHANGELOG | 2 + .../connection_adapters/firebird_adapter.rb | 396 +++++++++++++++++++-- .../fixtures/db_definitions/firebird3.drop.sql | 11 - .../test/fixtures/db_definitions/firebird3.sql | 49 --- .../test/fixtures/db_definitions/schema.rb | 12 +- activerecord/test/migration_test.rb | 28 +- activerecord/test/migration_test_firebird.rb | 124 +++++++ 7 files changed, 508 insertions(+), 114 deletions(-) delete mode 100644 activerecord/test/fixtures/db_definitions/firebird3.drop.sql delete mode 100644 activerecord/test/fixtures/db_definitions/firebird3.sql create mode 100644 activerecord/test/migration_test_firebird.rb diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index 5c22036ab2..d288d9c04b 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,5 +1,7 @@ *SVN* +* Firebird migrations support. #5337 [Ken Kunz ] + * PostgreSQL: create/drop as postgres user. #4790 [mail@matthewpainter.co.uk, mlaster@metavillage.com] * Update callbacks documentation. #3970 [Robby Russell ] diff --git a/activerecord/lib/active_record/connection_adapters/firebird_adapter.rb b/activerecord/lib/active_record/connection_adapters/firebird_adapter.rb index da3333d003..6a8ef03c8a 100644 --- a/activerecord/lib/active_record/connection_adapters/firebird_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/firebird_adapter.rb @@ -3,13 +3,20 @@ require 'active_record/connection_adapters/abstract_adapter' module FireRuby # :nodoc: all + NON_EXISTENT_DOMAIN_ERROR = "335544569" class Database - def self.new_from_params(database, host, port, service, charset) - host_string = [host, service, port].compact.first(2).join("/") if host - db_string = [host_string, database].join(":") - db = new(db_string) - db.character_set = charset - db + def self.db_string_for(config) + unless config.has_key?(:database) + raise ArgumentError, "No database specified. Missing argument: database." + end + host_string = config.values_at(:host, :service, :port).compact.first(2).join("/") if config[:host] + [host_string, config[:database]].join(":") + end + + def self.new_from_config(config) + db = new db_string_for(config) + db.character_set = config[:charset] + return db end end end @@ -24,12 +31,8 @@ module ActiveRecord 'to be running an older version -- please update FireRuby (gem install fireruby).' end config.symbolize_keys! - unless config.has_key?(:database) - raise ArgumentError, "No database specified. Missing argument: database." - end - db_params = config.values_at(:database, :host, :port, :service, :charset) + db = FireRuby::Database.new_from_config(config) connection_params = config.values_at(:username, :password) - db = FireRuby::Database.new_from_params(*db_params) connection = db.connect(*connection_params) ConnectionAdapters::FirebirdAdapter.new(connection, logger, connection_params) end @@ -42,9 +45,11 @@ module ActiveRecord def initialize(name, domain, type, sub_type, length, precision, scale, default_source, null_flag) @firebird_type = FireRuby::SQLType.to_base_type(type, sub_type).to_s + super(name.downcase, nil, @firebird_type, !null_flag) + @default = parse_default(default_source) if default_source - @limit = @firebird_type == 'BLOB' ? BLOB_MAX_LENGTH : length + @limit = decide_limit(length) @domain, @sub_type, @precision, @scale = domain, sub_type, precision, scale end @@ -58,19 +63,8 @@ module ActiveRecord end end - # Submits a _CAST_ query to the database, casting the default value to the specified SQL type. - # This enables Firebird to provide an actual value when context variables are used as column - # defaults (such as CURRENT_TIMESTAMP). def default - if @default - sql = "SELECT CAST(#{@default} AS #{column_def}) FROM RDB$DATABASE" - connection = ActiveRecord::Base.active_connections.values.detect { |conn| conn && conn.adapter_name == 'Firebird' } - if connection - type_cast connection.execute(sql).to_a.first['CAST'] - else - raise ConnectionNotEstablished, "No Firebird connections established." - end - end + type_cast(decide_default) if @default end def self.value_to_boolean(value) @@ -83,6 +77,35 @@ module ActiveRecord return $1 unless $1.upcase == "NULL" end + def decide_default + if @default =~ /^'?(\d*\.?\d+)'?$/ or + @default =~ /^'(.*)'$/ && [:text, :string, :binary, :boolean].include?(type) + $1 + else + firebird_cast_default + end + end + + # Submits a _CAST_ query to the database, casting the default value to the specified SQL type. + # This enables Firebird to provide an actual value when context variables are used as column + # defaults (such as CURRENT_TIMESTAMP). + def firebird_cast_default + sql = "SELECT CAST(#{@default} AS #{column_def}) FROM RDB$DATABASE" + if connection = Base.active_connections.values.detect { |conn| conn && conn.adapter_name == 'Firebird' } + connection.execute(sql).to_a.first['CAST'] + else + raise ConnectionNotEstablished, "No Firebird connections established." + end + end + + def decide_limit(length) + if text? or number? + length + elsif @firebird_type == 'BLOB' + BLOB_MAX_LENGTH + end + end + def column_def case @firebird_type when 'BLOB' then "VARCHAR(#{VARCHAR_MAX_LENGTH})" @@ -126,7 +149,7 @@ module ActiveRecord # Firebird 1.5 does not provide a native +BOOLEAN+ type. But you can easily # define a +BOOLEAN+ _domain_ for this purpose, e.g.: # - # CREATE DOMAIN D_BOOLEAN AS SMALLINT CHECK (VALUE IN (0, 1)); + # CREATE DOMAIN D_BOOLEAN AS SMALLINT CHECK (VALUE IN (0, 1) OR VALUE IS NULL); # # When the Firebird adapter encounters a column that is based on a domain # that includes "BOOLEAN" in the domain name, it will attempt to treat @@ -193,8 +216,23 @@ module ActiveRecord # as column names as well. # # === Migrations - # The Firebird adapter does not currently support Migrations. I hope to - # add this feature in the near future. + # The Firebird Adapter now supports Migrations. + # + # ==== Create/Drop Table and Sequence Generators + # Creating or dropping a table will automatically create/drop a + # correpsonding sequence generator, using the default naming convension. + # You can specify a different name using the :sequence option; no + # generator is created if :sequence is set to +false+. + # + # ==== Rename Table + # The Firebird #rename_table Migration should be used with caution. + # Firebird 1.5 lacks built-in support for this feature, so it is + # implemented by making a copy of the original table (including column + # definitions, indexes and data records), and then dropping the original + # table. Constraints and Triggers are _not_ properly copied, so avoid + # this method if your original table includes constraints (other than + # the primary key) or triggers. (Consider manually copying your table + # or using a view instead.) # # == Connection Options # The following options are supported by the Firebird adapter. None of the @@ -231,10 +269,12 @@ module ActiveRecord # Specifies the character set to be used by the connection. Refer to # Firebird documentation for valid options. class FirebirdAdapter < AbstractAdapter - @@boolean_domain = { :true => 1, :false => 0 } + TEMP_COLUMN_NAME = 'AR$TEMP_COLUMN' + + @@boolean_domain = { :name => "d_boolean", :type => "smallint", :true => 1, :false => 0 } cattr_accessor :boolean_domain - def initialize(connection, logger, connection_params=nil) + def initialize(connection, logger, connection_params = nil) super(connection, logger) @connection_params = connection_params end @@ -243,13 +283,33 @@ module ActiveRecord 'Firebird' end + def supports_migrations? # :nodoc: + true + end + + def native_database_types # :nodoc: + { + :primary_key => "BIGINT NOT NULL PRIMARY KEY", + :string => { :name => "varchar", :limit => 255 }, + :text => { :name => "blob sub_type text" }, + :integer => { :name => "bigint" }, + :float => { :name => "float" }, + :datetime => { :name => "timestamp" }, + :timestamp => { :name => "timestamp" }, + :time => { :name => "time" }, + :date => { :name => "date" }, + :binary => { :name => "blob sub_type 0" }, + :boolean => boolean_domain + } + end + # Returns true for Firebird adapter (since Firebird requires primary key # values to be pre-fetched before insert). See also #next_sequence_value. def prefetch_primary_key?(table_name = nil) true end - def default_sequence_name(table_name, primary_key) # :nodoc: + def default_sequence_name(table_name, primary_key = nil) # :nodoc: "#{table_name}_seq" end @@ -269,7 +329,7 @@ module ActiveRecord end def quote_column_name(column_name) # :nodoc: - %Q("#{ar_to_fb_case(column_name)}") + %Q("#{ar_to_fb_case(column_name.to_s)}") end def quoted_true # :nodoc: @@ -283,15 +343,15 @@ module ActiveRecord # CONNECTION MANAGEMENT ==================================== - def active? + def active? # :nodoc: not @connection.closed? end - def disconnect! + def disconnect! # :nodoc: @connection.close rescue nil end - def reconnect! + def reconnect! # :nodoc: disconnect! @connection = @connection.database.connect(*@connection_params) end @@ -304,8 +364,7 @@ module ActiveRecord end def select_one(sql, name = nil) # :nodoc: - result = select(sql, name) - result.nil? ? nil : result.first + select(sql, name).first end def execute(sql, name = nil, &block) # :nodoc: @@ -360,8 +419,37 @@ module ActiveRecord # SCHEMA STATEMENTS ======================================== + def current_database # :nodoc: + file = @connection.database.file.split(':').last + File.basename(file, '.*') + end + + def recreate_database! # :nodoc: + sql = "SELECT rdb$character_set_name FROM rdb$database" + charset = execute(sql).to_a.first[0].rstrip + disconnect! + @connection.database.drop(*@connection_params) + FireRuby::Database.create(@connection.database.file, + @connection_params[0], @connection_params[1], 4096, charset) + end + + def tables(name = nil) # :nodoc: + sql = "SELECT rdb$relation_name FROM rdb$relations WHERE rdb$system_flag = 0" + execute(sql, name).collect { |row| row[0].rstrip.downcase } + end + + def indexes(table_name, name = nil) # :nodoc: + index_metadata(table_name, false, name).inject([]) do |indexes, row| + if indexes.empty? or indexes.last.name != row[0] + indexes << IndexDefinition.new(table_name, row[0].rstrip.downcase, row[1] == 1, []) + end + indexes.last.columns << row[2].rstrip.downcase + indexes + end + end + def columns(table_name, name = nil) # :nodoc: - sql = <<-END_SQL + sql = <<-end_sql SELECT r.rdb$field_name, r.rdb$field_source, f.rdb$field_type, f.rdb$field_sub_type, f.rdb$field_length, f.rdb$field_precision, f.rdb$field_scale, COALESCE(r.rdb$default_source, f.rdb$default_source) rdb$default_source, @@ -370,7 +458,7 @@ module ActiveRecord JOIN rdb$fields f ON r.rdb$field_source = f.rdb$field_name WHERE r.rdb$relation_name = '#{table_name.to_s.upcase}' ORDER BY r.rdb$field_position - END_SQL + end_sql execute(sql, name).collect do |field| field_values = field.values.collect do |value| case value @@ -383,7 +471,125 @@ module ActiveRecord end end + def create_table(name, options = {}) # :nodoc: + begin + super + rescue StatementInvalid + raise unless non_existent_domain_error? + create_boolean_domain + super + end + unless options[:id] == false or options[:sequence] == false + sequence_name = options[:sequence] || default_sequence_name(name) + create_sequence(sequence_name) + end + end + + def drop_table(name, options = {}) # :nodoc: + super(name) + unless options[:sequence] == false + sequence_name = options[:sequence] || default_sequence_name(name) + drop_sequence(sequence_name) if sequence_exists?(sequence_name) + end + end + + def add_column(table_name, column_name, type, options = {}) # :nodoc: + super + rescue StatementInvalid + raise unless non_existent_domain_error? + create_boolean_domain + super + end + + def change_column(table_name, column_name, type, options = {}) # :nodoc: + change_column_type(table_name, column_name, type, options) + change_column_position(table_name, column_name, options[:position]) if options[:position] + change_column_default(table_name, column_name, options[:default]) if options.has_key?(:default) + end + + def change_column_default(table_name, column_name, default) # :nodoc: + table_name = table_name.to_s.upcase + sql = <<-end_sql + UPDATE rdb$relation_fields f1 + SET f1.rdb$default_source = + (SELECT f2.rdb$default_source FROM rdb$relation_fields f2 + WHERE f2.rdb$relation_name = '#{table_name}' + AND f2.rdb$field_name = '#{TEMP_COLUMN_NAME}'), + f1.rdb$default_value = + (SELECT f2.rdb$default_value FROM rdb$relation_fields f2 + WHERE f2.rdb$relation_name = '#{table_name}' + AND f2.rdb$field_name = '#{TEMP_COLUMN_NAME}') + WHERE f1.rdb$relation_name = '#{table_name}' + AND f1.rdb$field_name = '#{ar_to_fb_case(column_name.to_s)}' + end_sql + transaction do + add_column(table_name, TEMP_COLUMN_NAME, :string, :default => default) + execute sql + remove_column(table_name, TEMP_COLUMN_NAME) + end + end + + def rename_column(table_name, column_name, new_column_name) # :nodoc: + execute "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} TO #{new_column_name}" + end + + def remove_index(table_name, options) #:nodoc: + if Hash === options + index_name = options[:name] + else + index_name = "#{table_name}_#{options}_index" + end + execute "DROP INDEX #{index_name}" + end + + def rename_table(name, new_name) # :nodoc: + if table_has_constraints_or_dependencies?(name) + raise ActiveRecordError, + "Table #{name} includes constraints or dependencies that are not supported by " << + "the Firebird rename_table migration. Try explicitly removing the constraints/" << + "dependencies first, or manually renaming the table." + end + + transaction do + copy_table(name, new_name) + copy_table_indexes(name, new_name) + end + begin + copy_table_data(name, new_name) + copy_sequence_value(name, new_name) + rescue + drop_table(new_name) + raise + end + drop_table(name) + end + + def dump_schema_information # :nodoc: + super << ";\n" + end + + def type_to_sql(type, limit = nil) # :nodoc: + case type + when :integer then integer_sql_type(limit) + when :float then float_sql_type(limit) + when :string then super + else super(type) + end + end + private + def integer_sql_type(limit) + case limit + when (1..2) then 'smallint' + when (3..4) then 'integer' + else 'bigint' + end + end + + def float_sql_type(limit) + limit.to_i <= 4 ? 'float' : 'double precision' + end + def select(sql, name = nil) execute(sql, name).collect do |row| hashed_row = {} @@ -395,6 +601,120 @@ module ActiveRecord end end + def primary_key(table_name) + if pk_row = index_metadata(table_name, true).to_a.first + pk_row[2].rstrip.downcase + end + end + + def index_metadata(table_name, pk, name = nil) + sql = <<-end_sql + SELECT i.rdb$index_name, i.rdb$unique_flag, s.rdb$field_name + FROM rdb$indices i + JOIN rdb$index_segments s ON i.rdb$index_name = s.rdb$index_name + LEFT JOIN rdb$relation_constraints c ON i.rdb$index_name = c.rdb$index_name + WHERE i.rdb$relation_name = '#{table_name.to_s.upcase}' + end_sql + if pk + sql << "AND c.rdb$constraint_type = 'PRIMARY KEY'\n" + else + sql << "AND (c.rdb$constraint_type IS NULL OR c.rdb$constraint_type != 'PRIMARY KEY')\n" + end + sql << "ORDER BY i.rdb$index_name, s.rdb$field_position\n" + execute sql, name + end + + def change_column_type(table_name, column_name, type, options = {}) + sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} TYPE #{type_to_sql(type, options[:limit])}" + execute sql + rescue StatementInvalid + raise unless non_existent_domain_error? + create_boolean_domain + execute sql + end + + def change_column_position(table_name, column_name, position) + execute "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} POSITION #{position}" + end + + def copy_table(from, to) + table_opts = {} + if pk = primary_key(from) + table_opts[:primary_key] = pk + else + table_opts[:id] = false + end + create_table(to, table_opts) do |table| + from_columns = columns(from).reject { |col| col.name == table_opts[:primary_key] } + from_columns.each do |column| + col_opts = [:limit, :default, :null].inject({}) { |opts, opt| opts.merge(opt => column.send(opt)) } + table.column column.name, column.type, col_opts + end + end + end + + def copy_table_indexes(from, to) + indexes(from).each do |index| + unless index.name[from.to_s] + raise ActiveRecordError, + "Cannot rename index #{index.name}, because the index name does not include " << + "the original table name (#{from}). Try explicitly removing the index on the " << + "original table and re-adding it on the new (renamed) table." + end + options = {} + options[:name] = index.name.gsub(from.to_s, to.to_s) + options[:unique] = index.unique + add_index(to, index.columns, options) + end + end + + def copy_table_data(from, to) + execute "INSERT INTO #{to} SELECT * FROM #{from}", "Copy #{from} data to #{to}" + end + + def copy_sequence_value(from, to) + sequence_value = FireRuby::Generator.new(default_sequence_name(from), @connection).last + execute "SET GENERATOR #{default_sequence_name(to)} TO #{sequence_value}" + end + + def sequence_exists?(sequence_name) + FireRuby::Generator.exists?(sequence_name, @connection) + end + + def create_sequence(sequence_name) + FireRuby::Generator.create(sequence_name.to_s, @connection) + end + + def drop_sequence(sequence_name) + FireRuby::Generator.new(sequence_name.to_s, @connection).drop + end + + def create_boolean_domain + sql = <<-end_sql + CREATE DOMAIN #{boolean_domain[:name]} AS #{boolean_domain[:type]} + CHECK (VALUE IN (#{quoted_true}, #{quoted_false}) OR VALUE IS NULL) + end_sql + execute sql rescue nil + end + + def table_has_constraints_or_dependencies?(table_name) + table_name = table_name.to_s.upcase + sql = <<-end_sql + SELECT 1 FROM rdb$relation_constraints + WHERE rdb$relation_name = '#{table_name}' + AND rdb$constraint_type IN ('UNIQUE', 'FOREIGN KEY', 'CHECK') + UNION + SELECT 1 FROM rdb$dependencies + WHERE rdb$depended_on_name = '#{table_name}' + AND rdb$depended_on_type = 0 + end_sql + !select(sql).empty? + end + + def non_existent_domain_error? + $!.message.include? FireRuby::NON_EXISTENT_DOMAIN_ERROR + end + # Maps uppercase Firebird column names to lowercase for ActiveRecord; # mixed-case columns retain their original case. def fb_to_ar_case(column_name) diff --git a/activerecord/test/fixtures/db_definitions/firebird3.drop.sql b/activerecord/test/fixtures/db_definitions/firebird3.drop.sql deleted file mode 100644 index fc654212bd..0000000000 --- a/activerecord/test/fixtures/db_definitions/firebird3.drop.sql +++ /dev/null @@ -1,11 +0,0 @@ -DROP TABLE taggings; -DROP TABLE tags; -DROP TABLE categorizations; -DROP TABLE author_addresses; -DROP TABLE author_favorites; - -DROP GENERATOR taggings_seq; -DROP GENERATOR tags_seq; -DROP GENERATOR categorizations_seq; -DROP GENERATOR author_addresses_seq; -DROP GENERATOR author_favorites_seq; diff --git a/activerecord/test/fixtures/db_definitions/firebird3.sql b/activerecord/test/fixtures/db_definitions/firebird3.sql deleted file mode 100644 index 1d8e709d29..0000000000 --- a/activerecord/test/fixtures/db_definitions/firebird3.sql +++ /dev/null @@ -1,49 +0,0 @@ -CREATE TABLE taggings ( - id BIGINT NOT NULL, - tag_id BIGINT, - super_tag_id BIGINT, - taggable_type VARCHAR(255), - taggable_id BIGINT, - PRIMARY KEY (id) -); -CREATE GENERATOR taggings_seq; -SET GENERATOR taggings_seq TO 10000; - -CREATE TABLE tags ( - id BIGINT NOT NULL, - name VARCHAR(255), - taggings_count BIGINT DEFAULT 0, - PRIMARY KEY (id) -); -CREATE GENERATOR tags_seq; -SET GENERATOR tags_seq TO 10000; - -CREATE TABLE categorizations ( - id BIGINT NOT NULL, - category_id BIGINT, - post_id BIGINT, - author_id BIGINT, - PRIMARY KEY (id) -); -CREATE GENERATOR categorizations_seq; -SET GENERATOR categorizations_seq TO 10000; - -ALTER TABLE posts ADD taggings_count BIGINT DEFAULT 0; -ALTER TABLE authors ADD author_address_id BIGINT; - -CREATE TABLE author_addresses ( - id BIGINT NOT NULL, - author_address_id BIGINT, - PRIMARY KEY (id) -); -CREATE GENERATOR author_addresses_seq; -SET GENERATOR author_addresses_seq TO 10000; - -CREATE TABLE author_favorites ( - id BIGINT NOT NULL, - author_id BIGINT, - favorite_author_id BIGINT, - PRIMARY KEY (id) -); -CREATE GENERATOR author_favorites_seq; -SET GENERATOR author_favorites_seq TO 10000; diff --git a/activerecord/test/fixtures/db_definitions/schema.rb b/activerecord/test/fixtures/db_definitions/schema.rb index 7d10fbcadf..a5f2c9dc10 100644 --- a/activerecord/test/fixtures/db_definitions/schema.rb +++ b/activerecord/test/fixtures/db_definitions/schema.rb @@ -1,5 +1,15 @@ ActiveRecord::Schema.define do + # For Firebird, set the sequence values 10000 when create_table is called; + # this prevents primary key collisions between "normally" created records + # and fixture-based (YAML) records. + if adapter_name == "Firebird" + def create_table(*args, &block) + ActiveRecord::Base.connection.create_table(*args, &block) + ActiveRecord::Base.connection.execute "SET GENERATOR #{args.first}_seq TO 10000" + end + end + create_table :taggings, :force => true do |t| t.column :tag_id, :integer t.column :super_tag_id, :integer @@ -29,4 +39,4 @@ ActiveRecord::Schema.define do t.column :author_id, :integer t.column :favorite_author_id, :integer end -end \ No newline at end of file +end diff --git a/activerecord/test/migration_test.rb b/activerecord/test/migration_test.rb index 0429f10e5b..9514eb369d 100644 --- a/activerecord/test/migration_test.rb +++ b/activerecord/test/migration_test.rb @@ -43,13 +43,16 @@ if ActiveRecord::Base.connection.supports_migrations? Person.connection.remove_column("people", "favorite_day") rescue nil Person.connection.remove_column("people", "male") rescue nil Person.connection.remove_column("people", "administrator") rescue nil + Person.connection.remove_column("people", "first_name") rescue nil + Person.connection.add_column("people", "first_name", :string, :limit => 40) Person.reset_column_information end def test_add_index - Person.connection.add_column "people", "last_name", :string + # Limit size of last_name and key columns to support Firebird index limitations + Person.connection.add_column "people", "last_name", :string, :limit => 100 + Person.connection.add_column "people", "key", :string, :limit => 100 Person.connection.add_column "people", "administrator", :boolean - Person.connection.add_column "people", "key", :string assert_nothing_raised { Person.connection.add_index("people", "last_name") } assert_nothing_raised { Person.connection.remove_index("people", "last_name") } @@ -58,8 +61,9 @@ if ActiveRecord::Base.connection.supports_migrations? assert_nothing_raised { Person.connection.remove_index("people", "last_name") } # quoting - assert_nothing_raised { Person.connection.add_index("people", ["key"], :name => "key", :unique => true) } - assert_nothing_raised { Person.connection.remove_index("people", :name => "key", :unique => true) } + # Note: changed index name from "key" to "key_idx" since "key" is a Firebird reserved word + assert_nothing_raised { Person.connection.add_index("people", ["key"], :name => "key_idx", :unique => true) } + assert_nothing_raised { Person.connection.remove_index("people", :name => "key_idx", :unique => true) } # Sybase adapter does not support indexes on :boolean columns unless current_adapter?(:SybaseAdapter) @@ -170,14 +174,14 @@ if ActiveRecord::Base.connection.supports_migrations? end def test_add_column_not_null_with_default - Person.connection.create_table :testings, :id => false do |t| + Person.connection.create_table :testings do |t| t.column :foo, :string end - Person.connection.execute "insert into testings (foo) values ('hello')" + Person.connection.execute "insert into testings values (1, 'hello')" assert_nothing_raised {Person.connection.add_column :testings, :bar, :string, :null => false, :default => "default" } assert_raises(ActiveRecord::StatementInvalid) do - Person.connection.execute "insert into testings (foo, bar) values ('hello', NULL)" + Person.connection.execute "insert into testings values (2, 'hello', NULL)" end ensure Person.connection.drop_table :testings rescue nil @@ -294,14 +298,8 @@ if ActiveRecord::Base.connection.supports_migrations? end ActiveRecord::Base.connection.rename_table :octopuses, :octopi - assert_nothing_raised do - if current_adapter?(:OracleAdapter) - # Oracle requires the explicit sequence value for the pk - ActiveRecord::Base.connection.execute "INSERT INTO octopi (id, url) VALUES (1, 'http://www.foreverflying.com/octopus-black7.jpg')" - else - ActiveRecord::Base.connection.execute "INSERT INTO octopi (url) VALUES ('http://www.foreverflying.com/octopus-black7.jpg')" - end - end + # Using explicit id in insert for compatibility across all databases + assert_nothing_raised { ActiveRecord::Base.connection.execute "INSERT INTO octopi VALUES (1, 'http://www.foreverflying.com/octopus-black7.jpg')" } assert_equal 'http://www.foreverflying.com/octopus-black7.jpg', ActiveRecord::Base.connection.select_value("SELECT url FROM octopi WHERE id=1") diff --git a/activerecord/test/migration_test_firebird.rb b/activerecord/test/migration_test_firebird.rb new file mode 100644 index 0000000000..9d4b80c839 --- /dev/null +++ b/activerecord/test/migration_test_firebird.rb @@ -0,0 +1,124 @@ +require 'abstract_unit' +require 'fixtures/course' + +class FirebirdMigrationTest < Test::Unit::TestCase + self.use_transactional_fixtures = false + + def setup + # using Course connection for tests -- need a db that doesn't already have a BOOLEAN domain + @connection = Course.connection + @fireruby_connection = @connection.instance_variable_get(:@connection) + end + + def teardown + @connection.drop_table :foo rescue nil + @connection.execute("DROP DOMAIN D_BOOLEAN") rescue nil + end + + def test_create_table_with_custom_sequence_name + assert_nothing_raised do + @connection.create_table(:foo, :sequence => 'foo_custom_seq') do |f| + f.column :bar, :string + end + end + assert !sequence_exists?('foo_seq') + assert sequence_exists?('foo_custom_seq') + + assert_nothing_raised { @connection.drop_table(:foo, :sequence => 'foo_custom_seq') } + assert !sequence_exists?('foo_custom_seq') + ensure + FireRuby::Generator.new('foo_custom_seq', @fireruby_connection).drop rescue nil + end + + def test_create_table_without_sequence + assert_nothing_raised do + @connection.create_table(:foo, :sequence => false) do |f| + f.column :bar, :string + end + end + assert !sequence_exists?('foo_seq') + assert_nothing_raised { @connection.drop_table :foo } + + assert_nothing_raised do + @connection.create_table(:foo, :id => false) do |f| + f.column :bar, :string + end + end + assert !sequence_exists?('foo_seq') + assert_nothing_raised { @connection.drop_table :foo } + end + + def test_create_table_with_boolean_column + assert !boolean_domain_exists? + assert_nothing_raised do + @connection.create_table :foo do |f| + f.column :bar, :string + f.column :baz, :boolean + end + end + assert boolean_domain_exists? + end + + def test_add_boolean_column + assert !boolean_domain_exists? + @connection.create_table :foo do |f| + f.column :bar, :string + end + + assert_nothing_raised { @connection.add_column :foo, :baz, :boolean } + assert boolean_domain_exists? + assert_equal :boolean, @connection.columns(:foo).find { |c| c.name == "baz" }.type + end + + def test_change_column_to_boolean + assert !boolean_domain_exists? + # Manually create table with a SMALLINT column, which can be changed to a BOOLEAN + @connection.execute "CREATE TABLE foo (bar SMALLINT)" + assert_equal :integer, @connection.columns(:foo).find { |c| c.name == "bar" }.type + + assert_nothing_raised { @connection.change_column :foo, :bar, :boolean } + assert boolean_domain_exists? + assert_equal :boolean, @connection.columns(:foo).find { |c| c.name == "bar" }.type + end + + def test_rename_table_with_data_and_index + @connection.create_table :foo do |f| + f.column :baz, :string, :limit => 50 + end + 100.times { |i| @connection.execute "INSERT INTO foo VALUES (GEN_ID(foo_seq, 1), 'record #{i+1}')" } + @connection.add_index :foo, :baz + + assert_nothing_raised { @connection.rename_table :foo, :bar } + assert !@connection.tables.include?("foo") + assert @connection.tables.include?("bar") + assert_equal "bar_baz_index", @connection.indexes("bar").first.name + assert_equal 100, FireRuby::Generator.new("bar_seq", @fireruby_connection).last + assert_equal 100, @connection.select_one("SELECT COUNT(*) FROM bar")["count"] + ensure + @connection.drop_table :bar rescue nil + end + + def test_renaming_table_with_fk_constraint_raises_error + @connection.create_table :parent do |p| + p.column :name, :string + end + @connection.create_table :child do |c| + c.column :parent_id, :integer + end + @connection.execute "ALTER TABLE child ADD CONSTRAINT fk_child_parent FOREIGN KEY(parent_id) REFERENCES parent(id)" + assert_raise(ActiveRecord::ActiveRecordError) { @connection.rename_table :child, :descendant } + ensure + @connection.drop_table :child rescue nil + @connection.drop_table :descendant rescue nil + @connection.drop_table :parent rescue nil + end + + private + def boolean_domain_exists? + !@connection.select_one("SELECT 1 FROM rdb$fields WHERE rdb$field_name = 'D_BOOLEAN'").nil? + end + + def sequence_exists?(sequence_name) + FireRuby::Generator.exists?(sequence_name, @fireruby_connection) + end +end -- cgit v1.2.3