Module: ArJdbc::Derby

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

Defined Under Namespace

Modules: Column

Constant Summary collapse

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

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

.column_selector ⇒ Object 



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

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

.extended(*args) ⇒ Object 



30
31
32
 
# File 'lib/arjdbc/derby/adapter.rb', line 30

def self.extended(*args)
  monkey_rails
end

.included(*args) ⇒ Object 



34
35
36
 
# File 'lib/arjdbc/derby/adapter.rb', line 34

def self.included(*args)
  monkey_rails
end

.monkey_rails ⇒ Object 



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'lib/arjdbc/derby/adapter.rb', line 9

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

Instance Method Details

#adapter_name ⇒ Object

:nodoc:



54
55
56
 
# File 'lib/arjdbc/derby/adapter.rb', line 54

def adapter_name #:nodoc:
  'Derby'
end

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



194
195
196
197
198
199
200
201
202
203
204
 
# File 'lib/arjdbc/derby/adapter.rb', line 194

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

#add_column_options!(sql, options) ⇒ Object

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



82
83
84
85
86
 
# File 'lib/arjdbc/derby/adapter.rb', line 82

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

#add_limit_offset!(sql, options) ⇒ Object

:nodoc:



413
414
415
416
417
418
419
420
421
 
# File 'lib/arjdbc/derby/adapter.rb', line 413

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

#add_quotes(name) ⇒ Object 



122
123
124
125
 
# File 'lib/arjdbc/derby/adapter.rb', line 122

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

#auto_increment_stmt(tname, cname) ⇒ Object 



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

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

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



326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/arjdbc/derby/adapter.rb', line 326

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

#classes_for_table_name(table) ⇒ Object 



88
89
90
 
# File 'lib/arjdbc/derby/adapter.rb', line 88

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

#columns(table_name, name = nil) ⇒ Object 



375
376
377
 
# File 'lib/arjdbc/derby/adapter.rb', line 375

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

#create_column(name, refid, colno) ⇒ Object

I don’t think this method is ever called ??? (stepheneb)



242
243
244
245
246
247
248
249
250
251
252
253
254
255
 
# File 'lib/arjdbc/derby/adapter.rb', line 242

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

#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



226
227
228
229
230
231
232
233
234
235
236
237
238
239
 
# File 'lib/arjdbc/derby/adapter.rb', line 226

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.split(',').collect { |s| s.split.first }
  order_columns.delete_if(&: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) ⇒ Object 



206
207
208
209
210
211
212
213
214
215
216
 
# File 'lib/arjdbc/derby/adapter.rb', line 206

def execute(sql, name = nil)
  if sql =~ /^\s*(UPDATE|INSERT)/i
    i = sql =~ /\swhere\s/im
    if i
      sql[i..-1] = sql[i..-1].gsub(/!=\s*NULL/, 'IS NOT NULL').gsub(/=\sNULL/i, 'IS NULL')
    end
  else
    sql.gsub!(/= NULL/i, 'IS NULL')
  end
  super
end

#expand_double_quotes(name) ⇒ Object 



133
134
135
136
 
# File 'lib/arjdbc/derby/adapter.rb', line 133

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

#index_name_length ⇒ Object 



60
61
62
 
# File 'lib/arjdbc/derby/adapter.rb', line 60

def index_name_length
  128
end

#modify_types(tp) ⇒ Object 



73
74
75
76
77
78
79
 
# File 'lib/arjdbc/derby/adapter.rb', line 73

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

#primary_keys(table_name) ⇒ Object 



371
372
373
 
# File 'lib/arjdbc/derby/adapter.rb', line 371

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

#quote_column_name(name) ⇒ Object

For DDL it appears you can quote “” column names, but in queries (like insert it errors out?)



390
391
392
393
394
395
396
397
398
399
400
401
402
403
 
# File 'lib/arjdbc/derby/adapter.rb', line 390

def quote_column_name(name) #:nodoc:
  name = name.to_s
  if /^(references|integer|key|group|year)$/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

#quoted_false ⇒ Object 



409
410
411
 
# File 'lib/arjdbc/derby/adapter.rb', line 409

def quoted_false
  '0'
end

#quoted_true ⇒ Object 



405
406
407
 
# File 'lib/arjdbc/derby/adapter.rb', line 405

def quoted_true
  '1'
end

#recreate_database(db_name) ⇒ Object 



383
384
385
386
387
 
# File 'lib/arjdbc/derby/adapter.rb', line 383

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

#reinstate_auto_increment(name, refid, coldef) ⇒ Object 



138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
 
# File 'lib/arjdbc/derby/adapter.rb', line 138

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

#remove_column(table_name, column_name) ⇒ Object

Support for removing columns added via derby bug issue: 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



308
309
310
311
312
313
314
315
316
 
# File 'lib/arjdbc/derby/adapter.rb', line 308

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

#remove_index(table_name, options) ⇒ Object

:nodoc:



107
108
109
 
# File 'lib/arjdbc/derby/adapter.rb', line 107

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

Support for renaming columns: issues.apache.org/jira/browse/DERBY-1490

This feature is expect to arrive in version 10.3.0.0: wiki.apache.org/db-derby/DerbyTenThreeRelease)



363
364
365
366
367
368
369
 
# File 'lib/arjdbc/derby/adapter.rb', line 363

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

#rename_table(name, new_name) ⇒ Object 



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

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

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



98
99
100
101
102
103
104
105
 
# File 'lib/arjdbc/derby/adapter.rb', line 98

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.



93
94
95
96
 
# File 'lib/arjdbc/derby/adapter.rb', line 93

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

#strip_quotes(str) ⇒ Object 



127
128
129
130
131
 
# File 'lib/arjdbc/derby/adapter.rb', line 127

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

#structure_dump ⇒ Object

:nodoc:



259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
 
# File 'lib/arjdbc/derby/adapter.rb', line 259

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

#tables ⇒ Object 



379
380
381
 
# File 'lib/arjdbc/derby/adapter.rb', line 379

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

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

Convert the speficied column type to a SQL string. In Derby, :integers cannot specify a limit.



66
67
68
69
70
71
 
# File 'lib/arjdbc/derby/adapter.rb', line 66

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  return super unless type == :integer

  native = native_database_types[type.to_s.downcase.to_sym]
  native.is_a?(Hash) ? native[:name] : native
end