diff options
Diffstat (limited to 'lib')
| -rw-r--r-- | lib/arel/engines/sql/compilers/oracle_compiler.rb | 101 | ||||
| -rw-r--r-- | lib/arel/engines/sql/engine.rb | 8 | ||||
| -rw-r--r-- | lib/arel/engines/sql/formatters.rb | 11 | ||||
| -rw-r--r-- | lib/arel/engines/sql/relations/compiler.rb | 76 | ||||
| -rw-r--r-- | lib/arel/engines/sql/relations/writes.rb | 64 |
5 files changed, 192 insertions, 68 deletions
diff --git a/lib/arel/engines/sql/compilers/oracle_compiler.rb b/lib/arel/engines/sql/compilers/oracle_compiler.rb new file mode 100644 index 0000000000..1f6b82dd2f --- /dev/null +++ b/lib/arel/engines/sql/compilers/oracle_compiler.rb @@ -0,0 +1,101 @@ +module Arel + module SqlCompiler + class OracleCompiler < GenericCompiler + + def select_sql + where_clauses_array = where_clauses + if limit_or_offset = !taken.blank? || !skipped.blank? + # if need to select first records without ORDER BY and GROUP BY + # then can use simple ROWNUM in WHERE clause + if skipped.blank? && groupings.blank? && orders.blank? + where_clauses_array << "ROWNUM <= #{taken}" if !taken.blank? && skipped.blank? && groupings.blank? && orders.blank? + limit_or_offset = false + end + end + + # when limit or offset subquery is used then cannot use FOR UPDATE directly + # and need to construct separate subquery for primary key + if use_subquery_for_lock = limit_or_offset && !locked.blank? + primary_key = begin + engine.quote_column_name(table.name.classify.constantize.primary_key) + rescue NameError + engine.quote_column_name("id") + end + select_attributes_string = primary_key + else + select_attributes_string = select_clauses.join(', ') + end + + # OracleEnhanced adapter workaround when ORDER BY is used with columns not + # present in DISTINCT columns list + order_clauses_array = if select_attributes_string =~ /DISTINCT.*FIRST_VALUE/ && !orders.blank? + order = order_clauses.join(', ').split(',').map { |s| s.strip }.reject(&:blank?) + order = order.zip((0...order.size).to_a).map { |s,i| "alias_#{i}__ #{'DESC' if s =~ /\bdesc$/i}" } + else + order_clauses + end + + query = build_query \ + "SELECT #{select_attributes_string}", + "FROM #{from_clauses}", + (joins(self) unless joins(self).blank? ), + ("WHERE #{where_clauses_array.join(" AND ")}" unless where_clauses_array.blank? ), + ("GROUP BY #{group_clauses.join(', ')}" unless groupings.blank? ), + ("HAVING #{having_clauses.join(', ')}" unless havings.blank? ), + ("ORDER BY #{order_clauses_array.join(', ')}" unless order_clauses_array.blank? ) + + # Use existing method from oracle_enhanced adapter to implement limit and offset using subqueries + engine.add_limit_offset!(query, :limit => taken, :offset => skipped) if limit_or_offset + + if use_subquery_for_lock + build_query \ + "SELECT #{select_clauses.join(', ')}", + "FROM #{from_clauses}", + "WHERE #{primary_key} IN (#{query})", + "#{locked}" + elsif !locked.blank? + build_query query, "#{locked}" + else + query + end + end + + def delete_sql + where_clauses_array = wheres.collect(&:to_sql) + where_clauses_array << "ROWNUM <= #{taken}" unless taken.blank? + build_query \ + "DELETE", + "FROM #{table_sql}", + ("WHERE #{where_clauses_array.join(' AND ')}" unless where_clauses_array.blank? ) + end + + protected + + def build_update_conditions_sql + conditions = "" + where_clauses_array = wheres.collect(&:to_sql) + # if need to select first records without ORDER BY + # then can use simple ROWNUM in WHERE clause + if !taken.blank? && orders.blank? + where_clauses_array << "ROWNUM <= #{taken}" + end + conditions << " WHERE #{where_clauses_array.join(' AND ')}" unless where_clauses_array.blank? + unless taken.blank? + conditions = limited_update_conditions(conditions, taken) + end + conditions + end + + def limited_update_conditions(conditions, taken) + # need to add ORDER BY only if just taken ones should be updated + conditions << " ORDER BY #{order_clauses.join(', ')}" unless orders.blank? + quoted_primary_key = engine.quote_column_name(primary_key) + subquery = "SELECT #{quoted_primary_key} FROM #{engine.connection.quote_table_name table.name} #{conditions}" + # Use existing method from oracle_enhanced adapter to get taken records when ORDER BY is used + engine.add_limit_offset!(subquery, :limit => taken) unless orders.blank? + "WHERE #{quoted_primary_key} IN (#{subquery})" + end + + end + end +end diff --git a/lib/arel/engines/sql/engine.rb b/lib/arel/engines/sql/engine.rb index f0991f0a0f..054de21a70 100644 --- a/lib/arel/engines/sql/engine.rb +++ b/lib/arel/engines/sql/engine.rb @@ -11,7 +11,13 @@ module Arel end def adapter_name - @adapter_name ||= connection.adapter_name + @adapter_name ||= case (name = connection.adapter_name) + # map OracleEnanced adapter to Oracle + when /Oracle/ + 'Oracle' + else + name + end end def method_missing(method, *args, &block) diff --git a/lib/arel/engines/sql/formatters.rb b/lib/arel/engines/sql/formatters.rb index 8da362ef3b..892f0c29ee 100644 --- a/lib/arel/engines/sql/formatters.rb +++ b/lib/arel/engines/sql/formatters.rb @@ -93,7 +93,7 @@ module Arel class TableReference < Formatter def select(select_sql, table) - "(#{select_sql}) AS #{quote_table_name(name_for(table))}" + "(#{select_sql})#{as_keyword}#{quote_table_name(name_for(table))}" end def table(table) @@ -101,9 +101,16 @@ module Arel table.name else quote_table_name(table.name) + - (table.name != name_for(table) ? " AS " + quote_table_name(name_for(table)) : '') + (table.name != name_for(table) ? as_keyword + quote_table_name(name_for(table)) : '') end end + + private + + def as_keyword + # AS keyword should not be used before table alias in Oracle + as_keyword = engine.adapter_name == "Oracle" ? " " : " AS " + end end class Attribute < WhereCondition diff --git a/lib/arel/engines/sql/relations/compiler.rb b/lib/arel/engines/sql/relations/compiler.rb index 597ed88683..fc8d484276 100644 --- a/lib/arel/engines/sql/relations/compiler.rb +++ b/lib/arel/engines/sql/relations/compiler.rb @@ -21,20 +21,55 @@ module Arel ("#{locked}" unless locked.blank?) end - def limited_update_conditions(conditions, taken) - conditions << " LIMIT #{taken}" - quoted_primary_key = engine.quote_table_name(primary_key) - "WHERE #{quoted_primary_key} IN (SELECT #{quoted_primary_key} FROM #{engine.connection.quote_table_name table.name} #{conditions})" + def delete_sql + build_query \ + "DELETE", + "FROM #{table_sql}", + ("WHERE #{wheres.collect(&:to_sql).join(' AND ')}" unless wheres.blank? ), + (add_limit_on_delete(taken) unless taken.blank? ) end def add_limit_on_delete(taken) "LIMIT #{taken}" end + def insert_sql(include_returning = true) + insertion_attributes_values_sql = if record.is_a?(Value) + record.value + else + attributes = record.keys.sort_by do |attribute| + attribute.name.to_s + end + + first = attributes.collect do |key| + engine.quote_column_name(key.name) + end.join(', ') + + second = attributes.collect do |key| + key.format(record[key]) + end.join(', ') + + build_query "(#{first})", "VALUES (#{second})" + end + + build_query \ + "INSERT", + "INTO #{table_sql}", + insertion_attributes_values_sql, + ("RETURNING #{engine.quote_column_name(primary_key)}" if include_returning && compiler.supports_insert_with_returning?) + end + def supports_insert_with_returning? false end + def update_sql + build_query \ + "UPDATE #{table_sql} SET", + assignment_sql, + build_update_conditions_sql + end + protected def method_missing(method, *args, &block) relation.send(method, *args, &block) @@ -44,6 +79,39 @@ module Arel parts.compact.join(" ") end + def assignment_sql + if assignments.respond_to?(:collect) + attributes = assignments.keys.sort_by do |attribute| + attribute.name.to_s + end + + attributes.map do |attribute| + value = assignments[attribute] + "#{engine.quote_column_name(attribute.name)} = #{attribute.format(value)}" + end.join(", ") + else + assignments.value + end + end + + def build_update_conditions_sql + conditions = "" + conditions << " WHERE #{wheres.collect(&:to_sql).join(' AND ')}" unless wheres.blank? + conditions << " ORDER BY #{order_clauses.join(', ')}" unless orders.blank? + + unless taken.blank? + conditions = limited_update_conditions(conditions, taken) + end + + conditions + end + + def limited_update_conditions(conditions, taken) + conditions << " LIMIT #{taken}" + quoted_primary_key = engine.quote_column_name(primary_key) + "WHERE #{quoted_primary_key} IN (SELECT #{quoted_primary_key} FROM #{engine.connection.quote_table_name table.name} #{conditions})" + end + end end diff --git a/lib/arel/engines/sql/relations/writes.rb b/lib/arel/engines/sql/relations/writes.rb index 54eaee9ead..4ed817f85d 100644 --- a/lib/arel/engines/sql/relations/writes.rb +++ b/lib/arel/engines/sql/relations/writes.rb @@ -1,77 +1,19 @@ module Arel class Deletion < Compound def to_sql - build_query \ - "DELETE", - "FROM #{table_sql}", - ("WHERE #{wheres.collect(&:to_sql).join(' AND ')}" unless wheres.blank? ), - (compiler.add_limit_on_delete(taken) unless taken.blank? ) + compiler.delete_sql end end class Insert < Compound def to_sql(include_returning = true) - insertion_attributes_values_sql = if record.is_a?(Value) - record.value - else - attributes = record.keys.sort_by do |attribute| - attribute.name.to_s - end - - first = attributes.collect do |key| - engine.quote_column_name(key.name) - end.join(', ') - - second = attributes.collect do |key| - key.format(record[key]) - end.join(', ') - - build_query "(#{first})", "VALUES (#{second})" - end - - build_query \ - "INSERT", - "INTO #{table_sql}", - insertion_attributes_values_sql, - ("RETURNING #{engine.quote_column_name(primary_key)}" if include_returning && compiler.supports_insert_with_returning?) + compiler.insert_sql(include_returning) end end class Update < Compound def to_sql - build_query \ - "UPDATE #{table_sql} SET", - assignment_sql, - build_update_conditions_sql - end - - protected - - def assignment_sql - if assignments.respond_to?(:collect) - attributes = assignments.keys.sort_by do |attribute| - attribute.name.to_s - end - - attributes.map do |attribute| - value = assignments[attribute] - "#{engine.quote_column_name(attribute.name)} = #{attribute.format(value)}" - end.join(", ") - else - assignments.value - end - end - - def build_update_conditions_sql - conditions = "" - conditions << " WHERE #{wheres.collect(&:to_sql).join(' AND ')}" unless wheres.blank? - conditions << " ORDER BY #{order_clauses.join(', ')}" unless orders.blank? - - unless taken.blank? - conditions = compiler.limited_update_conditions(conditions,taken) - end - - conditions + compiler.update_sql end end end |