jdbc_derby.rb

require 'jdbc_adapter/missing_functionality_helper'

module ::JdbcSpec
  module ActiveRecordExtensions
    def derby_connection(config)
      config[:url] ||= "jdbc:derby:#{config[:database]};create=true"
      config[:driver] ||= "org.apache.derby.jdbc.EmbeddedDriver"
      embedded_driver(config)
    end
  end

  module Derby
    def self.column_selector
      [/derby/i, lambda {|cfg,col| col.extend(::JdbcSpec::Derby::Column)}]
    end

    def self.adapter_selector
      [/derby/i, lambda {|cfg,adapt| adapt.extend(::JdbcSpec::Derby)}]
    end

    def self.monkey_rails
      unless @already_monkeyd
        # Needed because Rails is broken wrt to quoting of
        # some values. Most databases are nice about it,
        # but not Derby. The real issue is that you can't
        # compare a CHAR value to a NUMBER column.
        ::ActiveRecord::Associations::ClassMethods.module_eval do
          private

          def select_limited_ids_list(options, join_dependency)
            connection.select_all(
                                  construct_finder_sql_for_association_limiting(options, join_dependency),
                                  "#{name} Load IDs For Limited Eager Loading"
                                  ).collect { |row| connection.quote(row[primary_key], columns_hash[primary_key]) }.join(", ")
          end
        end

        @already_monkeyd = true
      end
    end

    def self.extended(*args)
      monkey_rails
    end

    def self.included(*args)
      monkey_rails
    end

    module Column
      def value_to_binary(value)
        value.scan(/[0-9A-Fa-f]{2}/).collect {|v| v.to_i(16)}.pack("C*")
      end

      def cast_to_date_or_time(value)
        return value if value.is_a? Date
        return nil if value.blank?
        guess_date_or_time((value.is_a? Time) ? value : cast_to_time(value))
      end

      def cast_to_time(value)
        return value if value.is_a? Time
        time_array = ParseDate.parsedate value
        time_array[0] ||= 2000; time_array[1] ||= 1; time_array[2] ||= 1;
        Time.send(ActiveRecord::Base.default_timezone, *time_array) rescue nil
      end

      def guess_date_or_time(value)
        (value.hour == 0 and value.min == 0 and value.sec == 0) ?
        Date.new(value.year, value.month, value.day) : value
      end

      def simplified_type(field_type)
        return :boolean if field_type =~ /smallint/i
        return :float if field_type =~ /real/i
        super
      end
    end

    include JdbcSpec::MissingFunctionalityHelper

    def modify_types(tp)
      tp[:primary_key] = "int generated by default as identity NOT NULL PRIMARY KEY"
      tp[:integer][:limit] = nil
      tp[:string][:limit] = 256
      tp[:boolean] = {:name => "smallint"}
      tp
    end

    # Override default -- fix case where ActiveRecord passes :default => nil, :null => true
    def add_column_options!(sql, options)
      options.delete(:default) if options.has_key?(:default) && options[:default].nil?
      options.delete(:null) if options.has_key?(:null) && (options[:null].nil? || options[:null].true?)
      super
    end

    def classes_for_table_name(table)
      ActiveRecord::Base.send(:subclasses).select {|klass| klass.table_name == table}
    end

    # Set the sequence to the max value of the table's column.
    def reset_sequence!(table, column, sequence = nil)
      mpk = select_value("SELECT MAX(#{quote_column_name column}) FROM #{table}")
      execute("ALTER TABLE #{table} ALTER COLUMN #{quote_column_name column} RESTART WITH #{mpk.to_i + 1}")
    end

    def reset_pk_sequence!(table, pk = nil, sequence = nil)
      klasses = classes_for_table_name(table)
      klass   = klasses.nil? ? nil : klasses.first
      pk      = klass.primary_key unless klass.nil?
      if pk && klass.columns_hash[pk].type == :integer
        reset_sequence!(klass.table_name, pk)
      end
    end

    def primary_key(table_name) #:nodoc:
      primary_keys(table_name).first
    end

    def remove_index(table_name, options) #:nodoc:
      execute "DROP INDEX #{index_name(table_name, options)}"
    end

    def rename_table(name, new_name)
      execute "RENAME TABLE #{name} TO #{new_name}"
    end

    COLUMN_INFO_STMT = "SELECT C.COLUMNNAME, C.REFERENCEID, C.COLUMNNUMBER FROM SYS.SYSCOLUMNS C, SYS.SYSTABLES T WHERE T.TABLEID = '%s' AND T.TABLEID = C.REFERENCEID ORDER BY C.COLUMNNUMBER"

    COLUMN_TYPE_STMT = "SELECT COLUMNDATATYPE, COLUMNDEFAULT FROM SYS.SYSCOLUMNS WHERE REFERENCEID = '%s' AND COLUMNNAME = '%s'"

    AUTO_INC_STMT = "SELECT AUTOINCREMENTSTART, AUTOINCREMENTINC, COLUMNNAME, REFERENCEID, COLUMNDEFAULT FROM SYS.SYSCOLUMNS WHERE REFERENCEID = '%s' AND COLUMNNAME = '%s'"
    AUTO_INC_STMT2 = "SELECT AUTOINCREMENTSTART, AUTOINCREMENTINC, COLUMNNAME, REFERENCEID, COLUMNDEFAULT FROM SYS.SYSCOLUMNS WHERE REFERENCEID = (SELECT T.TABLEID FROM SYS.SYSTABLES T WHERE T.TABLENAME = '%s') AND COLUMNNAME = '%s'"

    def add_quotes(name)
      return name unless name
      %Q{"#{name}"}
    end

    def strip_quotes(str)
      return str unless str
      return str unless /^(["']).*\1$/ =~ str
      str[1..-2]
    end

    def expand_double_quotes(name)
      return name unless name && name['"']
      name.gsub(/"/,'""')
    end

    def reinstate_auto_increment(name, refid, coldef)
      stmt = AUTO_INC_STMT % [refid, strip_quotes(name)]
      data = execute(stmt).first
      if data
        start = data['autoincrementstart']
        if start
          coldef << " GENERATED " << (data['columndefault'].nil? ? "ALWAYS" : "BY DEFAULT ")
          coldef << "AS IDENTITY (START WITH "
          coldef << start
          coldef << ", INCREMENT BY "
          coldef << data['autoincrementinc']
          coldef << ")"
          return true
        end
      end
      false
    end

    def reinstate_auto_increment(name, refid, coldef)
      stmt = AUTO_INC_STMT % [refid, strip_quotes(name)]
      data = execute(stmt).first
      if data
        start = data['autoincrementstart']
        if start
          coldef << " GENERATED " << (data['columndefault'].nil? ? "ALWAYS" : "BY DEFAULT ")
          coldef << "AS IDENTITY (START WITH "
          coldef << start
          coldef << ", INCREMENT BY "
          coldef << data['autoincrementinc']
          coldef << ")"
          return true
        end
      end
      false
    end

    def auto_increment_stmt(tname, cname)
      stmt = AUTO_INC_STMT2 % [tname, strip_quotes(cname)]
      data = execute(stmt).first
      if data
        start = data['autoincrementstart']
        if start
          coldef = ""
          coldef << " GENERATED " << (data['columndefault'].nil? ? "ALWAYS" : "BY DEFAULT ")
          coldef << "AS IDENTITY (START WITH "
          coldef << start
          coldef << ", INCREMENT BY "
          coldef << data['autoincrementinc']
          coldef << ")"
          return coldef
        end
      end
      ""
    end


    def add_column(table_name, column_name, type, options = {})
      if option_not_null = options[:null] == false
        option_not_null = options.delete(:null)
      end
      add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
      add_column_options!(add_column_sql, options)
      execute(add_column_sql)
      if option_not_null
        alter_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} NOT NULL"
      end
    end

    # I don't think this method is ever called ??? (stepheneb)
    def create_column(name, refid, colno)
      stmt = COLUMN_TYPE_STMT % [refid, strip_quotes(name)]
      coldef = ""
      data = execute(stmt).first
      if data
        coldef << add_quotes(expand_double_quotes(strip_quotes(name)))
        coldef << " "
        coldef << data['columndatatype']
        if !reinstate_auto_increment(name, refid, coldef) && data['columndefault']
          coldef << " DEFAULT " << data['columndefault']
        end
      end
      coldef
    end

    SIZEABLE = %w(VARCHAR CLOB BLOB)

    def structure_dump #:nodoc:
      definition=""
      rs = @connection.connection.meta_data.getTables(nil,nil,nil,["TABLE"].to_java(:string))
      while rs.next
        tname = rs.getString(3)
        definition << "CREATE TABLE #{tname} (\n"
        rs2 = @connection.connection.meta_data.getColumns(nil,nil,tname,nil)
        first_col = true
        while rs2.next
          col_name = add_quotes(rs2.getString(4));
          default = ""
          d1 = rs2.getString(13)
          if d1 =~ /^GENERATED_/
            default = auto_increment_stmt(tname, col_name)
          elsif d1
            default = " DEFAULT #{d1}"
          end

          type = rs2.getString(6)
          col_size = rs2.getString(7)
          nulling = (rs2.getString(18) == 'NO' ? " NOT NULL" : "")
          create_col_string = add_quotes(expand_double_quotes(strip_quotes(col_name))) +
            " " +
            type +
            (SIZEABLE.include?(type) ? "(#{col_size})" : "") +
            nulling +
            default
          if !first_col
            create_col_string = ",\n #{create_col_string}"
          else
            create_col_string = " #{create_col_string}"
          end

          definition << create_col_string

          first_col = false
        end
        definition << ");\n\n"
      end
      definition
    end

    # Support for removing columns added via derby bug issue:
    # https://issues.apache.org/jira/browse/DERBY-1489
    #
    # This feature has not made it into a formal release and is not in Java 6.
    # If the normal strategy fails we fall back on a strategy by creating a new
    # table without the new column and there after moving the data to the new
    #
    def remove_column(table_name, column_name)
      begin
        execute "ALTER TABLE #{table_name} DROP COLUMN #{column_name} RESTRICT"
      rescue
        alter_table(table_name) do |definition|
          definition.columns.delete(definition[column_name])
        end
      end
    end

    # Notes about changing in Derby:
    #    http://db.apache.org/derby/docs/10.2/ref/rrefsqlj81859.html#rrefsqlj81859__rrefsqlj37860)
    #
    # We support changing columns using the strategy outlined in:
    #    https://issues.apache.org/jira/browse/DERBY-1515
    #
    # This feature has not made it into a formal release and is not in Java 6.  We will
    # need to conditionally support this somehow (supposed to arrive for 10.3.0.0)
    def change_column(table_name, column_name, type, options = {})
      # null/not nulling is easy, handle that separately
      if options.include?(:null)
        # This seems to only work with 10.2 of Derby
        if options.delete(:null) == false
          execute "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} NOT NULL"
        else
          execute "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} NULL"
        end
      end

      # anything left to do?
      unless options.empty?
        begin
          execute "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET DATA TYPE #{type_to_sql(type, options[:limit])}"
        rescue
          transaction do
            temp_new_column_name = "#{column_name}_newtype"
            # 1) ALTER TABLE t ADD COLUMN c1_newtype NEWTYPE;
            add_column table_name, temp_new_column_name, type, options
            # 2) UPDATE t SET c1_newtype = c1;
            execute "UPDATE #{table_name} SET #{temp_new_column_name} = CAST(#{column_name} AS #{type_to_sql(type, options[:limit])})"
            # 3) ALTER TABLE t DROP COLUMN c1;
            remove_column table_name, column_name
            # 4) ALTER TABLE t RENAME COLUMN c1_newtype to c1;
            rename_column table_name, temp_new_column_name, column_name
          end
        end
      end
    end

    # Support for renaming columns:
    # https://issues.apache.org/jira/browse/DERBY-1490
    #
    # This feature is expect to arrive in version 10.3.0.0:
    # http://wiki.apache.org/db-derby/DerbyTenThreeRelease)
    #
    def rename_column(table_name, column_name, new_column_name) #:nodoc:
      begin
        execute "ALTER TABLE #{table_name} ALTER RENAME COLUMN #{column_name} TO #{new_column_name}"
      rescue
        alter_table(table_name, :rename => {column_name => new_column_name})
      end
    end

    def primary_keys(table_name)
      @connection.primary_keys table_name.to_s.upcase
    end

    def recreate_database(db_name)
      tables.each do |t|
        drop_table t
      end
    end

    # For DDL it appears you can quote "" column names, but in queries (like insert it errors out?)
    def quote_column_name(name) #:nodoc:
      name = name.to_s
      if /^references$/i =~ name
        %Q{"#{name.upcase}"}
      elsif /[A-Z]/ =~ name && /[a-z]/ =~ name
        %Q{"#{name}"}
      elsif name =~ /\s/
        %Q{"#{name.upcase}"}
      elsif name =~ /^[_\d]/
        %Q{"#{name.upcase}"}
      else
        name
      end
    end

    def quoted_true
      '1'
    end

    def quoted_false
      '0'
    end
  end
end