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
-
#jdbc_oracle_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:.
- #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
34 35 36 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 34 def self.adapter_matcher(name, *) name =~ /oracle/i ? self : false end |
.column_selector ⇒ Object
38 39 40 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 38 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 |
# 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 mod.class_eval do alias_chained_method :insert, :query_dirty, :jdbc_oracle_insert end end |
Instance Method Details
#_execute(sql, name = nil) ⇒ Object
160 161 162 163 164 165 166 167 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 160 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
103 104 105 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 103 def adapter_name 'oracle' end |
#add_column_options!(sql, options) ⇒ Object
:nodoc:
201 202 203 204 205 206 207 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 201 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:
179 180 181 182 183 184 185 186 187 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 179 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.
294 295 296 297 298 299 300 301 302 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 294 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:
209 210 211 212 213 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 209 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:
197 198 199 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 197 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
308 309 310 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 308 def columns(table_name, name=nil) @connection.columns_internal(table_name, name, oracle_schema) end |
#create_table(name, options = {}) ⇒ Object
:nodoc:
115 116 117 118 119 120 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 115 def create_table(name, = {}) #:nodoc: super(name, ) seq_name = [:sequence_name] || "#{name}_seq" raise ActiveRecord::StatementInvalid.new("name #{seq_name} too long") if seq_name.length > table_alias_length execute "CREATE SEQUENCE #{seq_name} START WITH 10000" unless [:id] == false end |
#current_database ⇒ Object
:nodoc:
189 190 191 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 189 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:
111 112 113 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 111 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")
278 279 280 281 282 283 284 285 286 287 288 289 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 278 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
137 138 139 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 137 def drop_database(name) recreate_database(name) end |
#drop_table(name, options = {}) ⇒ Object
:nodoc:
127 128 129 130 131 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 127 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
156 157 158 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 156 def indexes(table, name = nil) @connection.indexes(table, name, @connection.connection..user_name) end |
#jdbc_oracle_insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) ⇒ Object
:nodoc:
141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 141 def jdbc_oracle_insert(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc: if id_value || pk.nil? # Pre-assigned id or table without a primary key 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
169 170 171 172 173 174 175 176 177 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 169 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:
330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 330 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 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
322 323 324 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 322 def quote_column_name(name) #:nodoc: name.to_s =~ /^[a-z0-9_$#]+$/ ? name.to_s : "\"#{name}\"" end |
#quote_string(string) ⇒ Object
:nodoc:
326 327 328 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 326 def quote_string(string) #:nodoc: string.gsub(/'/, "''") end |
#quoted_date(value) ⇒ Object
349 350 351 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 349 def quoted_date(value) %Q{TIMESTAMP'#{value.strftime("%Y-%m-%d %H:%M:%S")}'} end |
#quoted_false ⇒ Object
:nodoc:
357 358 359 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 357 def quoted_false #:nodoc: '0' end |
#quoted_true ⇒ Object
:nodoc:
353 354 355 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 353 def quoted_true #:nodoc: '1' end |
#recreate_database(name) ⇒ Object
133 134 135 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 133 def recreate_database(name) tables.each{ |table| drop_table(table) } end |
#remove_column(table_name, column_name) ⇒ Object
:nodoc:
219 220 221 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 219 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:
193 194 195 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 193 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:
215 216 217 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 215 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:
122 123 124 125 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 122 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:
258 259 260 261 262 263 264 265 266 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 258 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:
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 253 254 255 256 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 223 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
107 108 109 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 107 def table_alias_length 30 end |
#tables ⇒ Object
304 305 306 |
# File 'lib/jdbc_adapter/jdbc_oracle.rb', line 304 def tables @connection.tables(nil, oracle_schema) end |