Module: ArJdbc::Derby

Includes:
MissingFunctionalityHelper
Defined in:
lib/arjdbc/derby/adapter.rb

Defined Under Namespace

Modules: Column

Constant Summary collapse

ADAPTER_NAME = 
'Derby'
NATIVE_DATABASE_TYPES = 
{
  :primary_key => "int GENERATED BY DEFAULT AS identity NOT NULL PRIMARY KEY",
  :string => { :name => "varchar", :limit => 255 },
  :text => { :name => "clob" }, # 2,147,483,647
  :binary => { :name => "blob" }, # 2,147,483,647
  :float => { :name => "float", :limit => 8 }, # DOUBLE PRECISION
  :decimal => { :name => "decimal", :precision => 5, :scale => 0 }, # defaults
  :numeric => { :name => "decimal", :precision => 5, :scale => 0 }, # defaults
  :integer => { :name => "integer", :limit => 4 },
  :smallint => { :name => "smallint", :limit => 2 },
  :bigint => { :name => "bigint", :limit => 8 },
  :real => { :name => "real", :limit => 4 },
  :double => { :name => "double", :limit => 8 },
  :date => { :name => "date" },
  :time => { :name => "time" },
  :datetime => { :name => "timestamp" },
  :timestamp => { :name => "timestamp" },
  :boolean => { :name => "smallint" },
}
NO_LIMIT_TYPES =

in Derby, the following cannot specify a limit :

[ :integer, :boolean, :timestamp, :datetime, :date, :time ]
SIZEABLE =

:nodoc:

%w(VARCHAR CLOB BLOB)
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'"

Class Method Summary collapse

Instance Method Summary collapse

Methods included from MissingFunctionalityHelper

#alter_table, #copy_table, #copy_table_contents, #copy_table_indexes, #move_table

Class Method Details

.arel2_visitors(config) ⇒ Object 



76
77
78
79
80
81
82
 
# File 'lib/arjdbc/derby/adapter.rb', line 76

def self.arel2_visitors(config)
  require 'arel/visitors/derby'
  {
    'derby' => ::Arel::Visitors::Derby,
    'jdbcderby' => ::Arel::Visitors::Derby,
  }
end

.column_selector ⇒ Object 



15
16
17
 
# File 'lib/arjdbc/derby/adapter.rb', line 15

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

.extended(adapter) ⇒ Object 



6
7
8
9
 
# File 'lib/arjdbc/derby/adapter.rb', line 6

def self.extended(adapter)
  require 'arjdbc/derby/active_record_patch'
  adapter.configure_connection
end

.included(*args) ⇒ Object 



11
12
13
 
# File 'lib/arjdbc/derby/adapter.rb', line 11

def self.included(*args)
  require 'arjdbc/derby/active_record_patch'
end

Instance Method Details

#adapter_name ⇒ Object

:nodoc:



72
73
74
 
# File 'lib/arjdbc/derby/adapter.rb', line 72

def adapter_name # :nodoc:
  ADAPTER_NAME
end

#add_column(table_name, column_name, type, options = {}) ⇒ Object 



171
172
173
174
175
 
# File 'lib/arjdbc/derby/adapter.rb', line 171

def add_column(table_name, column_name, type, options = {})
  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)
end

#add_column_options!(sql, options) ⇒ Object

Override default – fix case where ActiveRecord passes :default => nil, :null => true



137
138
139
140
141
 
# File 'lib/arjdbc/derby/adapter.rb', line 137

def add_column_options!(sql, options)
  options.delete(:default) if options.has_key?(:default) && options[:default].nil?
  sql << " DEFAULT #{quote(options.delete(:default))}" if options.has_key?(:default)
  super
end

#add_limit_offset!(sql, options) ⇒ Object

:nodoc:



346
347
348
349
350
 
# File 'lib/arjdbc/derby/adapter.rb', line 346

def add_limit_offset!(sql, options) # :nodoc:
  sql << " OFFSET #{options[:offset]} ROWS" if options[:offset]
  # ROWS/ROW and FIRST/NEXT mean the same
  sql << " FETCH FIRST #{options[:limit]} ROWS ONLY" if options[:limit]
end

#change_column(table_name, column_name, type, options = {}) ⇒ Object

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 (supposed to arrive for 10.3.0.0).



291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
 
# File 'lib/arjdbc/derby/adapter.rb', line 291

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 #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} NOT NULL"
    else
      execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} NULL"
    end
  end

  # anything left to do?
  unless options.empty?
    begin
      execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(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 #{quote_table_name(table_name)} SET #{quote_column_name(temp_new_column_name)} = CAST(#{quote_column_name(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

#columns(table_name, name = nil) ⇒ Object 



330
331
332
 
# File 'lib/arjdbc/derby/adapter.rb', line 330

def columns(table_name, name = nil)
  @connection.columns_internal(table_name.to_s, name, derby_schema)
end

#configure_connection ⇒ Object 



19
20
21
22
 
# File 'lib/arjdbc/derby/adapter.rb', line 19

def configure_connection
  execute("SET ISOLATION = SERIALIZABLE")
  # must be done or SELECT...FOR UPDATE won't work how we expect
end

#distinct(columns, order_by) ⇒ Object

SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.

Derby requires the ORDER BY columns in the select list for distinct queries, and requires that the ORDER BY include the distinct column.

distinct("posts.id", "posts.created_at desc")

Based on distinct method for PostgreSQL Adapter



200
201
202
203
204
205
206
207
208
209
210
211
212
 
# File 'lib/arjdbc/derby/adapter.rb', line 200

def distinct(columns, order_by)
  return "DISTINCT #{columns}" if order_by.blank?

  # construct a clean list of column names from the ORDER BY clause, removing
  # any asc/desc modifiers
  order_columns = [order_by].flatten.map{|o| o.split(',').collect { |s| s.split.first } }.flatten.reject(&:blank?)
  order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" }

  # return a DISTINCT clause that's distinct on the columns we want but includes
  # all the required columns for the ORDER BY to work properly
  sql = "DISTINCT #{columns}, #{order_columns * ', '}"
  sql
end

#execute(sql, name = nil, binds = []) ⇒ Object 



177
178
179
180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/arjdbc/derby/adapter.rb', line 177

def execute(sql, name = nil, binds = [])
  sql = to_sql(sql)
  if sql =~ /\A\s*(UPDATE|INSERT)/i
    if ( i = sql =~ /\sWHERE\s/im )
      where_part = sql[i..-1]; sql = sql.dup
      where_part.gsub!(/!=\s*NULL/, 'IS NOT NULL')
      where_part.gsub!(/=\sNULL/i, 'IS NULL')
      sql[i..-1] = where_part
    end
  else
    sql = sql.gsub(/=\sNULL/i, 'IS NULL')
  end
  super(sql, name, binds)
end

#index_name_length ⇒ Object 



86
87
88
 
# File 'lib/arjdbc/derby/adapter.rb', line 86

def index_name_length
  128
end

#modify_types(types) ⇒ Object 



114
115
116
117
118
119
120
121
122
123
 
# File 'lib/arjdbc/derby/adapter.rb', line 114

def modify_types(types)
  super(types)
  types[:primary_key] = NATIVE_DATABASE_TYPES[:primary_key]
  [ :string, :float, :decimal, :numeric, :integer, 
    :smallint, :bigint, :real, :double ].each do |type|
    types[type] = NATIVE_DATABASE_TYPES[type].dup
  end
  types[:boolean] = NATIVE_DATABASE_TYPES[:boolean].dup
  types
end

#native_database_types ⇒ Object 



110
111
112
 
# File 'lib/arjdbc/derby/adapter.rb', line 110

def native_database_types
  super.merge NATIVE_DATABASE_TYPES
end

#primary_keys(table_name) ⇒ Object 



326
327
328
 
# File 'lib/arjdbc/derby/adapter.rb', line 326

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

#quote_column_name(name) ⇒ Object

:nodoc:



342
343
344
 
# File 'lib/arjdbc/derby/adapter.rb', line 342

def quote_column_name(name) # :nodoc:
  %Q{"#{name.to_s.upcase.gsub(/\"/, '""')}"}
end

#recreate_database(db_name, options = {}) ⇒ Object 



338
339
340
 
# File 'lib/arjdbc/derby/adapter.rb', line 338

def recreate_database(db_name, options = {})
  tables.each { |table| drop_table table }
end

#remove_column(table_name, *column_names) ⇒ Object

:nodoc:



254
255
256
257
258
 
# File 'lib/arjdbc/derby/adapter.rb', line 254

def remove_column(table_name, *column_names) # :nodoc:
  for column_name in column_names.flatten
    execute "ALTER TABLE #{quote_table_name(table_name)} DROP COLUMN #{quote_column_name(column_name)} RESTRICT"
  end
end

#remove_index(table_name, options) ⇒ Object

:nodoc:



163
164
165
 
# File 'lib/arjdbc/derby/adapter.rb', line 163

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

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



322
323
324
 
# File 'lib/arjdbc/derby/adapter.rb', line 322

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  execute "RENAME COLUMN #{quote_table_name(table_name)}.#{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}"
end

#rename_table(name, new_name) ⇒ Object 



167
168
169
 
# File 'lib/arjdbc/derby/adapter.rb', line 167

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

#reset_pk_sequence!(table, pk = nil, sequence = nil) ⇒ Object 



149
150
151
152
153
154
155
156
 
# File 'lib/arjdbc/derby/adapter.rb', line 149

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

#reset_sequence!(table, column, sequence = nil) ⇒ Object

Set the sequence to the max value of the table’s column.



144
145
146
147
 
# File 'lib/arjdbc/derby/adapter.rb', line 144

def reset_sequence!(table, column, sequence = nil)
  mpk = select_value("SELECT MAX(#{quote_column_name(column)}) FROM #{quote_table_name(table)}")
  execute("ALTER TABLE #{quote_table_name(table)} ALTER COLUMN #{quote_column_name(column)} RESTART WITH #{mpk.to_i + 1}")
end

#structure_dump ⇒ Object

:nodoc:



216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
 
# File 'lib/arjdbc/derby/adapter.rb', line 216

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

      type = columns_rs.getString(6)
      column_size = columns_rs.getString(7)
      nulling = (columns_rs.getString(18) == 'NO' ? " NOT NULL" : "")
      create_column = add_quotes(expand_double_quotes(strip_quotes(column_name)))
      create_column << " #{type}"
      create_column << ( SIZEABLE.include?(type) ? "(#{column_size})" : "" )
      create_column << nulling
      create_column << default
      
      create_column = first_col ? " #{create_column}" : ",\n #{create_column}"
      definition << create_column

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

#tables ⇒ Object 



334
335
336
 
# File 'lib/arjdbc/derby/adapter.rb', line 334

def tables
  @connection.tables(nil, derby_schema)
end

#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object

Convert the specified column type to a SQL string.



129
130
131
132
133
134
 
# File 'lib/arjdbc/derby/adapter.rb', line 129

def type_to_sql(type, limit = nil, precision = nil, scale = nil) # :nodoc:
  return super unless NO_LIMIT_TYPES.include?(t = type.to_s.downcase.to_sym)

  native_type = NATIVE_DATABASE_TYPES[t]
  native_type.is_a?(Hash) ? native_type[:name] : native_type
end