Module: JdbcSpec::Oracle
- Defined in:
- lib/jdbc_adapter/jdbc_oracle.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_date(value) ⇒ Object
-
#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
.adapter_matcher(name) ⇒ Object
36 37 38 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 36 def self.adapter_matcher(name, *) name =~ /oracle/i ? self : false end |
.column_selector ⇒ Object
40 41 42 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 40 def self.column_selector [/oracle/i, lambda {|cfg,col| col.extend(::JdbcSpec::Oracle::Column)}] end |
.extended(mod) ⇒ Object
12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 12 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 ActiveRecord::Base.extend JdbcSpec::QuotedPrimaryKeyExtension mod.class.class_eval do alias_chained_method :insert, :query_dirty, :insert alias_chained_method :columns, :query_cache, :columns end end |
Instance Method Details
#_execute(sql, name = nil) ⇒ Object
174 175 176 177 178 179 180 181 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 174 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
112 113 114 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 112 def adapter_name 'Oracle' end |
#add_column_options!(sql, options) ⇒ Object
:nodoc:
215 216 217 218 219 220 221 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 215 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:
193 194 195 196 197 198 199 200 201 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 193 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.
308 309 310 311 312 313 314 315 316 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 308 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:
223 224 225 226 227 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 223 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:
211 212 213 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 211 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
322 323 324 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 322 def columns(table_name, name=nil) @connection.columns_internal(table_name, name, oracle_schema) end |
#create_table(name, options = {}) ⇒ Object
:nodoc:
124 125 126 127 128 129 130 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 124 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:
203 204 205 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 203 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:
120 121 122 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 120 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")
292 293 294 295 296 297 298 299 300 301 302 303 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 292 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
147 148 149 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 147 def drop_database(name) recreate_database(name) end |
#drop_table(name, options = {}) ⇒ Object
:nodoc:
137 138 139 140 141 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 137 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
170 171 172 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 170 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:
151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 151 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
183 184 185 186 187 188 189 190 191 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 183 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:
350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 350 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) || value.acts_like?(:time) quoted = "#{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
342 343 344 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 342 def quote_column_name(name) #:nodoc: name.to_s =~ /^[a-z0-9_$#]+$/ ? name.to_s : "\"#{name}\"" end |
#quote_string(string) ⇒ Object
:nodoc:
346 347 348 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 346 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.
332 333 334 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 332 def quote_table_name(name) #:nodoc: name.to_s end |
#quoted_date(value) ⇒ Object
369 370 371 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 369 def quoted_date(value) %Q{TIMESTAMP'#{super}'} end |
#quoted_false ⇒ Object
:nodoc:
377 378 379 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 377 def quoted_false #:nodoc: '0' end |
#quoted_true ⇒ Object
:nodoc:
373 374 375 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 373 def quoted_true #:nodoc: '1' end |
#recreate_database(name) ⇒ Object
143 144 145 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 143 def recreate_database(name) tables.each{ |table| drop_table(table) } end |
#remove_column(table_name, column_name) ⇒ Object
:nodoc:
233 234 235 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 233 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:
207 208 209 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 207 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:
229 230 231 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 229 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:
132 133 134 135 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 132 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:
272 273 274 275 276 277 278 279 280 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 272 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:
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 267 268 269 270 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 237 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
116 117 118 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 116 def table_alias_length 30 end |
#tables ⇒ Object
318 319 320 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 318 def tables @connection.tables(nil, oracle_schema) end |