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 --- .../connection_adapters/firebird_adapter.rb | 396 +++++++++++++++++++-- 1 file changed, 358 insertions(+), 38 deletions(-) (limited to 'activerecord/lib/active_record/connection_adapters') 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) -- cgit v1.2.3