Module: ArJdbc::Oracle
- Defined in:
- lib/arjdbc/oracle/adapter.rb
Defined Under Namespace
Modules: Column
Class Method Summary collapse
Instance Method Summary collapse
- #_execute(sql, name = nil) ⇒ Object
- #adapter_name ⇒ Object
-
#add_column_options!(sql, options) ⇒ Object
:nodoc:.
-
#add_limit_offset!(sql, options) ⇒ Object
:nodoc:.
-
#add_order_by_for_association_limiting!(sql, options) ⇒ Object
ORDER BY clause for the passed order option.
-
#change_column(table_name, column_name, type, options = {}) ⇒ Object
:nodoc:.
-
#change_column_default(table_name, column_name, default) ⇒ Object
:nodoc:.
- #columns(table_name, name = nil) ⇒ Object
-
#create_table(name, options = {}) ⇒ Object
:nodoc:.
-
#current_database ⇒ Object
:nodoc:.
-
#default_sequence_name(table, column = nil) ⇒ Object
:nodoc:.
-
#distinct(columns, order_by) ⇒ Object
SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.
- #drop_database(name) ⇒ Object
-
#drop_table(name, options = {}) ⇒ Object
:nodoc:.
- #indexes(table, name = nil) ⇒ Object
-
#insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object
:nodoc:.
- #modify_types(tp) ⇒ Object
-
#quote(value, column = nil) ⇒ Object
:nodoc:.
-
#quote_column_name(name) ⇒ Object
Camelcase column names need to be quoted.
-
#quote_string(string) ⇒ Object
:nodoc:.
-
#quote_table_name(name) ⇒ Object
See ACTIVERECORD_JDBC-33 for details – better to not quote table names, esp.
-
#quoted_false ⇒ Object
:nodoc:.
-
#quoted_true ⇒ Object
:nodoc:.
- #recreate_database(name) ⇒ Object
-
#remove_column(table_name, column_name) ⇒ Object
:nodoc:.
-
#remove_index(table_name, options = {}) ⇒ Object
:nodoc:.
-
#rename_column(table_name, column_name, new_column_name) ⇒ Object
:nodoc:.
-
#rename_table(name, new_name) ⇒ Object
:nodoc:.
-
#structure_drop ⇒ Object
:nodoc:.
-
#structure_dump ⇒ Object
:nodoc:.
- #table_alias_length ⇒ Object
- #tables ⇒ Object
Class Method Details
.column_selector ⇒ Object
32 33 34 |
# File 'lib/arjdbc/oracle/adapter.rb', line 32 def self.column_selector [/oracle/i, lambda {|cfg,col| col.extend(::ArJdbc::Oracle::Column)}] end |
.extended(mod) ⇒ Object
7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
# File 'lib/arjdbc/oracle/adapter.rb', line 7 def self.extended(mod) unless @lob_callback_added ActiveRecord::Base.class_eval do def after_save_with_oracle_lob self.class.columns.select { |c| c.sql_type =~ /LOB\(|LOB$/i }.each do |c| value = self[c.name] value = value.to_yaml if unserializable_attribute?(c.name, c) next if value.nil? || (value == '') connection.write_large_object(c.type == :binary, c.name, self.class.table_name, self.class.primary_key, quote_value(id), value) end end end ActiveRecord::Base.after_save :after_save_with_oracle_lob @lob_callback_added = true end require 'arjdbc/jdbc/quoted_primary_key' ActiveRecord::Base.extend ArJdbc::QuotedPrimaryKeyExtension mod.class.class_eval do alias_chained_method :insert, :query_dirty, :insert alias_chained_method :columns, :query_cache, :columns end end |
.jdbc_connection_class ⇒ Object
36 37 38 |
# File 'lib/arjdbc/oracle/adapter.rb', line 36 def self.jdbc_connection_class ::ActiveRecord::ConnectionAdapters::OracleJdbcConnection end |
Instance Method Details
#_execute(sql, name = nil) ⇒ Object
170 171 172 173 174 175 176 177 |
# File 'lib/arjdbc/oracle/adapter.rb', line 170 def _execute(sql, name = nil) case sql.strip when /\A\(?\s*(select|show)/i then @connection.execute_query(sql) else @connection.execute_update(sql) end end |
#adapter_name ⇒ Object
108 109 110 |
# File 'lib/arjdbc/oracle/adapter.rb', line 108 def adapter_name 'Oracle' end |
#add_column_options!(sql, options) ⇒ Object
:nodoc:
211 212 213 214 215 216 217 |
# File 'lib/arjdbc/oracle/adapter.rb', line 211 def (sql, ) #:nodoc: # handle case of defaults for CLOB columns, which would otherwise get "quoted" incorrectly if () && (column = [:column]) && column.type == :text sql << " DEFAULT #{quote(.delete(:default))}" end super end |
#add_limit_offset!(sql, options) ⇒ Object
:nodoc:
189 190 191 192 193 194 195 196 197 |
# File 'lib/arjdbc/oracle/adapter.rb', line 189 def add_limit_offset!(sql, ) #:nodoc: offset = [:offset] || 0 if limit = [:limit] sql.replace "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_ where rownum <= #{offset+limit}) where raw_rnum_ > #{offset}" elsif offset > 0 sql.replace "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_) where raw_rnum_ > #{offset}" end end |
#add_order_by_for_association_limiting!(sql, options) ⇒ Object
ORDER BY clause for the passed order option.
Uses column aliases as defined by #distinct.
304 305 306 307 308 309 310 311 312 |
# File 'lib/arjdbc/oracle/adapter.rb', line 304 def add_order_by_for_association_limiting!(sql, ) return sql if [:order].blank? order = [:order].split(',').collect { |s| s.strip }.reject(&:blank?) order.map! {|s| $1 if s =~ / (.*)/} order = order.zip((0...order.size).to_a).map { |s,i| "alias_#{i}__ #{s}" }.join(', ') sql << "ORDER BY #{order}" end |
#change_column(table_name, column_name, type, options = {}) ⇒ Object
:nodoc:
219 220 221 222 223 |
# File 'lib/arjdbc/oracle/adapter.rb', line 219 def change_column(table_name, column_name, type, = {}) #:nodoc: change_column_sql = "ALTER TABLE #{table_name} MODIFY #{column_name} #{type_to_sql(type, [:limit])}" (change_column_sql, ) execute(change_column_sql) end |
#change_column_default(table_name, column_name, default) ⇒ Object
:nodoc:
207 208 209 |
# File 'lib/arjdbc/oracle/adapter.rb', line 207 def change_column_default(table_name, column_name, default) #:nodoc: execute "ALTER TABLE #{table_name} MODIFY #{column_name} DEFAULT #{quote(default)}" end |
#columns(table_name, name = nil) ⇒ Object
318 319 320 |
# File 'lib/arjdbc/oracle/adapter.rb', line 318 def columns(table_name, name=nil) @connection.columns_internal(table_name, name, oracle_schema) end |
#create_table(name, options = {}) ⇒ Object
:nodoc:
120 121 122 123 124 125 126 |
# File 'lib/arjdbc/oracle/adapter.rb', line 120 def create_table(name, = {}) #:nodoc: super(name, ) seq_name = [:sequence_name] || "#{name}_seq" start_value = [:sequence_start_value] || 10000 raise ActiveRecord::StatementInvalid.new("name #{seq_name} too long") if seq_name.length > table_alias_length execute "CREATE SEQUENCE #{seq_name} START WITH #{start_value}" unless [:id] == false end |
#current_database ⇒ Object
:nodoc:
199 200 201 |
# File 'lib/arjdbc/oracle/adapter.rb', line 199 def current_database #:nodoc: select_one("select sys_context('userenv','db_name') db from dual")["db"] end |
#default_sequence_name(table, column = nil) ⇒ Object
:nodoc:
116 117 118 |
# File 'lib/arjdbc/oracle/adapter.rb', line 116 def default_sequence_name(table, column = nil) #:nodoc: "#{table}_seq" end |
#distinct(columns, order_by) ⇒ Object
SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.
Oracle requires the ORDER BY columns to be in the SELECT list for DISTINCT queries. However, with those columns included in the SELECT DISTINCT list, you won’t actually get a distinct list of the column you want (presuming the column has duplicates with multiple values for the ordered-by columns. So we use the FIRST_VALUE function to get a single (first) value for each column, effectively making every row the same.
distinct("posts.id", "posts.created_at desc")
288 289 290 291 292 293 294 295 296 297 298 299 |
# File 'lib/arjdbc/oracle/adapter.rb', line 288 def distinct(columns, order_by) return "DISTINCT #{columns}" if order_by.blank? # construct a valid DISTINCT clause, ie. one that includes the ORDER BY columns, using # FIRST_VALUE such that the inclusion of these columns doesn't invalidate the DISTINCT order_columns = order_by.split(',').map { |s| s.strip }.reject(&:blank?) order_columns = order_columns.zip((0...order_columns.size).to_a).map do |c, i| "FIRST_VALUE(#{c.split.first}) OVER (PARTITION BY #{columns} ORDER BY #{c}) AS alias_#{i}__" end sql = "DISTINCT #{columns}, " sql << order_columns * ", " end |
#drop_database(name) ⇒ Object
143 144 145 |
# File 'lib/arjdbc/oracle/adapter.rb', line 143 def drop_database(name) recreate_database(name) end |
#drop_table(name, options = {}) ⇒ Object
:nodoc:
133 134 135 136 137 |
# File 'lib/arjdbc/oracle/adapter.rb', line 133 def drop_table(name, = {}) #:nodoc: super(name) seq_name = [:sequence_name] || "#{name}_seq" execute "DROP SEQUENCE #{seq_name}" rescue nil end |
#indexes(table, name = nil) ⇒ Object
166 167 168 |
# File 'lib/arjdbc/oracle/adapter.rb', line 166 def indexes(table, name = nil) @connection.indexes(table, name, @connection.connection..user_name) end |
#insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object
:nodoc:
147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 |
# File 'lib/arjdbc/oracle/adapter.rb', line 147 def insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc: if (id_value && !id_value.respond_to?(:to_sql)) || pk.nil? # Pre-assigned id or table without a primary key # Presence of #to_sql means an Arel literal bind variable # that should use #execute_id_insert below execute sql, name else # Assume the sql contains a bind-variable for the id # Extract the table from the insert sql. Yuck. table = sql.split(" ", 4)[2].gsub('"', '') sequence_name ||= default_sequence_name(table) id_value = select_one("select #{sequence_name}.nextval id from dual")['id'].to_i log(sql, name) do @connection.execute_id_insert(sql,id_value) end end id_value end |
#modify_types(tp) ⇒ Object
179 180 181 182 183 184 185 186 187 |
# File 'lib/arjdbc/oracle/adapter.rb', line 179 def modify_types(tp) tp[:primary_key] = "NUMBER(38) NOT NULL PRIMARY KEY" tp[:integer] = { :name => "NUMBER", :limit => 38 } tp[:datetime] = { :name => "DATE" } tp[:timestamp] = { :name => "DATE" } tp[:time] = { :name => "DATE" } tp[:date] = { :name => "DATE" } tp end |
#quote(value, column = nil) ⇒ Object
:nodoc:
346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 |
# File 'lib/arjdbc/oracle/adapter.rb', line 346 def quote(value, column = nil) #:nodoc: if column && [:text, :binary].include?(column.type) if /(.*?)\([0-9]+\)/ =~ column.sql_type %Q{empty_#{ $1.downcase }()} else %Q{empty_#{ column.sql_type.downcase rescue 'blob' }()} end else if column.respond_to?(:primary) && column.primary && column.klass != String return value.to_i.to_s end quoted = super if value.acts_like?(:date) quoted = %Q{DATE'#{quoted_date(value)}'} elsif value.acts_like?(:time) quoted = %Q{TIMESTAMP'#{quoted_date(value)}'} end quoted end end |
#quote_column_name(name) ⇒ Object
Camelcase column names need to be quoted. Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and “at” signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers. Source: download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements008.htm
338 339 340 |
# File 'lib/arjdbc/oracle/adapter.rb', line 338 def quote_column_name(name) #:nodoc: name.to_s =~ /^[a-z0-9_$#]+$/ ? name.to_s : "\"#{name}\"" end |
#quote_string(string) ⇒ Object
:nodoc:
342 343 344 |
# File 'lib/arjdbc/oracle/adapter.rb', line 342 def quote_string(string) #:nodoc: string.gsub(/'/, "''") end |
#quote_table_name(name) ⇒ Object
See ACTIVERECORD_JDBC-33 for details – better to not quote table names, esp. if they have schemas.
328 329 330 |
# File 'lib/arjdbc/oracle/adapter.rb', line 328 def quote_table_name(name) #:nodoc: name.to_s end |
#quoted_false ⇒ Object
:nodoc:
371 372 373 |
# File 'lib/arjdbc/oracle/adapter.rb', line 371 def quoted_false #:nodoc: '0' end |
#quoted_true ⇒ Object
:nodoc:
367 368 369 |
# File 'lib/arjdbc/oracle/adapter.rb', line 367 def quoted_true #:nodoc: '1' end |
#recreate_database(name) ⇒ Object
139 140 141 |
# File 'lib/arjdbc/oracle/adapter.rb', line 139 def recreate_database(name) tables.each{ |table| drop_table(table) } end |
#remove_column(table_name, column_name) ⇒ Object
:nodoc:
229 230 231 |
# File 'lib/arjdbc/oracle/adapter.rb', line 229 def remove_column(table_name, column_name) #:nodoc: execute "ALTER TABLE #{table_name} DROP COLUMN #{column_name}" end |
#remove_index(table_name, options = {}) ⇒ Object
:nodoc:
203 204 205 |
# File 'lib/arjdbc/oracle/adapter.rb', line 203 def remove_index(table_name, = {}) #:nodoc: execute "DROP INDEX #{index_name(table_name, )}" end |
#rename_column(table_name, column_name, new_column_name) ⇒ Object
:nodoc:
225 226 227 |
# File 'lib/arjdbc/oracle/adapter.rb', line 225 def rename_column(table_name, column_name, new_column_name) #:nodoc: execute "ALTER TABLE #{table_name} RENAME COLUMN #{column_name} to #{new_column_name}" end |
#rename_table(name, new_name) ⇒ Object
:nodoc:
128 129 130 131 |
# File 'lib/arjdbc/oracle/adapter.rb', line 128 def rename_table(name, new_name) #:nodoc: execute "RENAME #{name} TO #{new_name}" execute "RENAME #{name}_seq TO #{new_name}_seq" rescue nil end |
#structure_drop ⇒ Object
:nodoc:
268 269 270 271 272 273 274 275 276 |
# File 'lib/arjdbc/oracle/adapter.rb', line 268 def structure_drop #:nodoc: s = select_all("select sequence_name from user_sequences").inject("") do |drop, seq| drop << "drop sequence #{seq.to_a.first.last};\n\n" end select_all("select table_name from user_tables").inject(s) do |drop, table| drop << "drop table #{table.to_a.first.last} cascade constraints;\n\n" end end |
#structure_dump ⇒ Object
:nodoc:
233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 |
# File 'lib/arjdbc/oracle/adapter.rb', line 233 def structure_dump #:nodoc: s = select_all("select sequence_name from user_sequences").inject("") do |structure, seq| structure << "create sequence #{seq.to_a.first.last};\n\n" end select_all("select table_name from user_tables").inject(s) do |structure, table| ddl = "create table #{table.to_a.first.last} (\n " cols = select_all(%Q{ select column_name, data_type, data_length, data_precision, data_scale, data_default, nullable from user_tab_columns where table_name = '#{table.to_a.first.last}' order by column_id }).map do |row| row = row.inject({}) do |h,args| h[args[0].downcase] = args[1] h end col = "#{row['column_name'].downcase} #{row['data_type'].downcase}" if row['data_type'] =='NUMBER' and !row['data_precision'].nil? col << "(#{row['data_precision'].to_i}" col << ",#{row['data_scale'].to_i}" if !row['data_scale'].nil? col << ')' elsif row['data_type'].include?('CHAR') col << "(#{row['data_length'].to_i})" end col << " default #{row['data_default']}" if !row['data_default'].nil? col << ' not null' if row['nullable'] == 'N' col end ddl << cols.join(",\n ") ddl << ");\n\n" structure << ddl end end |
#table_alias_length ⇒ Object
112 113 114 |
# File 'lib/arjdbc/oracle/adapter.rb', line 112 def table_alias_length 30 end |
#tables ⇒ Object
314 315 316 |
# File 'lib/arjdbc/oracle/adapter.rb', line 314 def tables @connection.tables(nil, oracle_schema) end |