Module: ArJdbc::DB2
- Defined in:
- lib/arjdbc/db2/adapter.rb
Defined Under Namespace
Modules: Column
Constant Summary collapse
- HAVE_LIMIT =
only record precision and scale for types that can set them via CREATE TABLE: publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000927.html
%w(FLOAT DECFLOAT CHAR VARCHAR CLOB BLOB NCHAR NCLOB DBCLOB GRAPHIC VARGRAPHIC)
- HAVE_PRECISION =
TIMESTAMP
%w(DECIMAL NUMERIC)
- HAVE_SCALE =
%w(DECIMAL NUMERIC)
Class Method Summary collapse
Instance Method Summary collapse
- #_execute(sql, name = nil) ⇒ Object
- #adapter_name ⇒ Object
- #add_limit_offset!(sql, options) ⇒ Object
- #add_quotes(name) ⇒ Object
- #change_column(table_name, column_name, type, options = {}) ⇒ Object
- #change_column_default(table_name, column_name, default) ⇒ Object
- #change_column_null(table_name, column_name, null) ⇒ Object
- #columns(table_name, name = nil) ⇒ Object
-
#execute_and_auto_confirm(sql) ⇒ Object
holy moly batman! all this to tell AS400 “yes i am sure”.
- #expand_double_quotes(name) ⇒ Object
- #indexes(table_name, name = nil) ⇒ Object
- #last_insert_id(sql) ⇒ Object
- #modify_types(tp) ⇒ Object
- #pk_and_sequence_for(table) ⇒ Object
-
#quote(value, column = nil) ⇒ Object
:nodoc:.
- #quote_column_name(column_name) ⇒ Object
- #quote_string(string) ⇒ Object
- #quoted_false ⇒ Object
- #quoted_true ⇒ Object
- #recreate_database(name) ⇒ Object
- #remove_column(table_name, column_name) ⇒ Object
- #remove_index(table_name, options = { }) ⇒ Object
-
#rename_column(table_name, column_name, new_column_name) ⇒ Object
publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020130.html …not supported on IBM i, so we raise in this case.
- #rename_table(name, new_name) ⇒ Object
- #reorg_table(table_name) ⇒ Object
- #strip_quotes(str) ⇒ Object
-
#structure_dump ⇒ Object
:nodoc:.
- #tables ⇒ Object
Class Method Details
Instance Method Details
#_execute(sql, name = nil) ⇒ Object
89 90 91 92 93 94 95 96 97 |
# File 'lib/arjdbc/db2/adapter.rb', line 89 def _execute(sql, name = nil) if ActiveRecord::ConnectionAdapters::JdbcConnection::select?(sql) @connection.execute_query(sql) elsif ActiveRecord::ConnectionAdapters::JdbcConnection::insert?(sql) (@connection.execute_insert(sql) or last_insert_id(sql)).to_i else @connection.execute_update(sql) end end |
#adapter_name ⇒ Object
137 138 139 |
# File 'lib/arjdbc/db2/adapter.rb', line 137 def adapter_name 'DB2' end |
#add_limit_offset!(sql, options) ⇒ Object
141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
# File 'lib/arjdbc/db2/adapter.rb', line 141 def add_limit_offset!(sql, ) limit, offset = [:limit], [:offset] if limit && !offset if limit == 1 sql << " FETCH FIRST ROW ONLY" else sql << " FETCH FIRST #{sanitize_limit(limit)} ROWS ONLY" end elsif limit && offset sql.gsub!(/SELECT/i, 'SELECT B.* FROM (SELECT A.*, row_number() over () AS internal$rownum FROM (SELECT') sql << ") A ) B WHERE B.internal$rownum > #{offset} AND B.internal$rown um <= #{sanitize_limit(limit) + offset}" end end |
#add_quotes(name) ⇒ Object
309 310 311 312 |
# File 'lib/arjdbc/db2/adapter.rb', line 309 def add_quotes(name) return name unless name %Q{"#{name}"} end |
#change_column(table_name, column_name, type, options = {}) ⇒ Object
243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 |
# File 'lib/arjdbc/db2/adapter.rb', line 243 def change_column(table_name, column_name, type, = {}) data_type = type_to_sql(type, [:limit], [:precision], [:scale]) sql = "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET DATA TYPE #{data_type}" as400? ? execute_and_auto_confirm(sql) : execute(sql) reorg_table(table_name) if .include?(:default) and .include?(:null) # which to run first? if [:null] or [:default].nil? change_column_null(table_name, column_name, [:null]) change_column_default(table_name, column_name, [:default]) else change_column_default(table_name, column_name, [:default]) change_column_null(table_name, column_name, [:null]) end elsif .include?(:default) change_column_default(table_name, column_name, [:default]) elsif .include?(:null) change_column_null(table_name, column_name, [:null]) end end |
#change_column_default(table_name, column_name, default) ⇒ Object
234 235 236 237 238 239 240 241 |
# File 'lib/arjdbc/db2/adapter.rb', line 234 def change_column_default(table_name, column_name, default) if default.nil? execute_and_auto_confirm "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} DROP DEFAULT" else execute_and_auto_confirm "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET WITH DEFAULT #{quote(default)}" end reorg_table(table_name) end |
#change_column_null(table_name, column_name, null) ⇒ Object
225 226 227 228 229 230 231 232 |
# File 'lib/arjdbc/db2/adapter.rb', line 225 def change_column_null(table_name, column_name, null) if null execute_and_auto_confirm "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} DROP NOT NULL" else execute_and_auto_confirm "ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET NOT NULL" end reorg_table(table_name) end |
#columns(table_name, name = nil) ⇒ Object
290 291 292 293 294 295 296 297 298 299 300 301 302 303 |
# File 'lib/arjdbc/db2/adapter.rb', line 290 def columns(table_name, name = nil) cols = @connection.columns(table_name, name, db2_schema) # scrub out sizing info when CREATE TABLE doesn't support it # but JDBC reports it (doh!) for col in cols base_sql_type = col.sql_type.sub(/\(.*/, "").upcase col.limit = nil unless HAVE_LIMIT.include?(base_sql_type) col.precision = nil unless HAVE_PRECISION.include?(base_sql_type) #col.scale = nil unless HAVE_SCALE.include?(base_sql_type) end cols end |
#execute_and_auto_confirm(sql) ⇒ Object
holy moly batman! all this to tell AS400 “yes i am sure”
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 |
# File 'lib/arjdbc/db2/adapter.rb', line 100 def execute_and_auto_confirm(sql) begin @connection.execute_update "call qsys.qcmdexc('QSYS/CHGJOB INQMSGRPY(*SYSRPYL)',0000000031.00000)" @connection.execute_update "call qsys.qcmdexc('ADDRPYLE SEQNBR(9876) MSGID(CPA32B2) RPY(''I'')',0000000045.00000)" rescue Exception => e raise "Could not call CHGJOB INQMSGRPY(*SYSRPYL) and ADDRPYLE SEQNBR(9876) MSGID(CPA32B2) RPY('I').\n" + "Do you have authority to do this?\n\n" + e.to_s end r = execute sql begin @connection.execute_update "call qsys.qcmdexc('QSYS/CHGJOB INQMSGRPY(*DFT)',0000000027.00000)" @connection.execute_update "call qsys.qcmdexc('RMVRPYLE SEQNBR(9876)',0000000021.00000)" rescue Exception => e raise "Could not call CHGJOB INQMSGRPY(*DFT) and RMVRPYLE SEQNBR(9876).\n" + "Do you have authority to do this?\n\n" + e.to_s end r end |
#expand_double_quotes(name) ⇒ Object
320 321 322 323 |
# File 'lib/arjdbc/db2/adapter.rb', line 320 def (name) return name unless name && name['"'] name.gsub(/"/,'""') end |
#indexes(table_name, name = nil) ⇒ Object
305 306 307 |
# File 'lib/arjdbc/db2/adapter.rb', line 305 def indexes(table_name, name = nil) @connection.indexes(table_name, name, db2_schema) end |
#last_insert_id(sql) ⇒ Object
121 122 123 124 125 126 127 |
# File 'lib/arjdbc/db2/adapter.rb', line 121 def last_insert_id(sql) table_name = sql.split(/\s/)[2] result = select(ActiveRecord::Base.send(:sanitize_sql, %[select IDENTITY_VAL_LOCAL() as last_insert_id from #{table_name}], nil)) result.last['last_insert_id'] end |
#modify_types(tp) ⇒ Object
129 130 131 132 133 134 135 |
# File 'lib/arjdbc/db2/adapter.rb', line 129 def modify_types(tp) tp[:primary_key] = 'int not null generated by default as identity (start with 1) primary key' tp[:string][:limit] = 255 tp[:integer] = {:limit => nil} tp[:boolean] = {:name => "smallint"} tp end |
#pk_and_sequence_for(table) ⇒ Object
156 157 158 159 160 161 162 163 164 |
# File 'lib/arjdbc/db2/adapter.rb', line 156 def pk_and_sequence_for(table) # In JDBC/DB2 side, only upcase names of table and column are handled. keys = super(table.upcase) if keys && keys[0] # In ActiveRecord side, only downcase names of table and column are handled. keys[0] = keys[0].downcase end keys end |
#quote(value, column = nil) ⇒ Object
:nodoc:
170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 |
# File 'lib/arjdbc/db2/adapter.rb', line 170 def quote(value, column = nil) # :nodoc: if column && column.respond_to?(:primary) && column.primary && column.klass != String return value.to_i.to_s end if column && (column.type == :decimal || column.type == :integer) && value return value.to_s end case value when String if column && column.type == :binary "BLOB('#{quote_string(value)}')" else "'#{quote_string(value)}'" end else super end end |
#quote_column_name(column_name) ⇒ Object
166 167 168 |
# File 'lib/arjdbc/db2/adapter.rb', line 166 def quote_column_name(column_name) column_name end |
#quote_string(string) ⇒ Object
188 189 190 |
# File 'lib/arjdbc/db2/adapter.rb', line 188 def quote_string(string) string.gsub(/'/, "''") # ' (for ruby-mode) end |
#quoted_false ⇒ Object
196 197 198 |
# File 'lib/arjdbc/db2/adapter.rb', line 196 def quoted_false '0' end |
#quoted_true ⇒ Object
192 193 194 |
# File 'lib/arjdbc/db2/adapter.rb', line 192 def quoted_true '1' end |
#recreate_database(name) ⇒ Object
206 207 208 |
# File 'lib/arjdbc/db2/adapter.rb', line 206 def recreate_database(name) tables.each {|table| drop_table("#{db2_schema}.#{table}")} end |
#remove_column(table_name, column_name) ⇒ Object
266 267 268 269 270 271 |
# File 'lib/arjdbc/db2/adapter.rb', line 266 def remove_column(table_name, column_name) #:nodoc: sql = "ALTER TABLE #{table_name} DROP COLUMN #{column_name}" as400? ? execute_and_auto_confirm(sql) : execute(sql) reorg_table(table_name) end |
#remove_index(table_name, options = { }) ⇒ Object
210 211 212 |
# File 'lib/arjdbc/db2/adapter.rb', line 210 def remove_index(table_name, = { }) execute "DROP INDEX #{quote_column_name(index_name(table_name, ))}" end |
#rename_column(table_name, column_name, new_column_name) ⇒ Object
publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020130.html …not supported on IBM i, so we raise in this case
216 217 218 219 220 221 222 223 |
# File 'lib/arjdbc/db2/adapter.rb', line 216 def rename_column(table_name, column_name, new_column_name) #:nodoc: if as400? raise NotImplementedError, "rename_column is not supported on IBM i" else execute "ALTER TABLE #{table_name} RENAME COLUMN #{column_name} TO #{new_column_name}" reorg_table(table_name) end end |
#rename_table(name, new_name) ⇒ Object
274 275 276 277 |
# File 'lib/arjdbc/db2/adapter.rb', line 274 def rename_table(name, new_name) #:nodoc: execute "RENAME TABLE #{name} TO #{new_name}" reorg_table(new_name) end |
#reorg_table(table_name) ⇒ Object
200 201 202 203 204 |
# File 'lib/arjdbc/db2/adapter.rb', line 200 def reorg_table(table_name) unless as400? @connection.execute_update "call sysproc.admin_cmd ('REORG TABLE #{table_name}')" end end |
#strip_quotes(str) ⇒ Object
314 315 316 317 318 |
# File 'lib/arjdbc/db2/adapter.rb', line 314 def strip_quotes(str) return str unless str return str unless /^(["']).*\1$/ =~ str str[1..-2] end |
#structure_dump ⇒ Object
:nodoc:
325 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 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 |
# File 'lib/arjdbc/db2/adapter.rb', line 325 def structure_dump #:nodoc: definition="" rs = @connection.connection..getTables(nil,db2_schema.upcase,nil,["TABLE"].to_java(:string)) while rs.next tname = rs.getString(3) definition << "CREATE TABLE #{tname} (\n" rs2 = @connection.connection..getColumns(nil,db2_schema.upcase,tname,nil) first_col = true while rs2.next col_name = add_quotes(rs2.getString(4)); default = "" d1 = rs2.getString(13) # IBM i (as400 toolbox driver) will return an empty string if there is no default if @config[:url] =~ /^jdbc:as400:/ default = !d1.blank? ? " DEFAULT #{d1}" : "" else default = d1 ? " DEFAULT #{d1}" : "" end type = rs2.getString(6) col_precision = rs2.getString(7) col_scale = rs2.getString(9) col_size = "" if HAVE_SCALE.include?(type) and col_scale col_size = "(#{col_precision},#{col_scale})" elsif (HAVE_LIMIT + HAVE_PRECISION).include?(type) and col_precision col_size = "(#{col_precision})" end nulling = (rs2.getString(18) == 'NO' ? " NOT NULL" : "") create_col_string = add_quotes((strip_quotes(col_name))) + " " + 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
279 280 281 |
# File 'lib/arjdbc/db2/adapter.rb', line 279 def tables @connection.tables(nil, db2_schema, nil, ["TABLE"]) end |