diff options
| -rw-r--r-- | History.txt | 4 | ||||
| -rw-r--r-- | Manifest.txt | 1 | ||||
| -rw-r--r-- | arel.gemspec | 12 | ||||
| -rw-r--r-- | lib/arel/nodes.rb | 1 | ||||
| -rw-r--r-- | lib/arel/nodes/binary.rb | 4 | ||||
| -rw-r--r-- | lib/arel/nodes/named_function.rb | 2 | ||||
| -rw-r--r-- | lib/arel/nodes/node.rb | 2 | ||||
| -rw-r--r-- | lib/arel/nodes/select_statement.rb | 13 | ||||
| -rw-r--r-- | lib/arel/nodes/with.rb | 10 | ||||
| -rw-r--r-- | lib/arel/predications.rb | 2 | ||||
| -rw-r--r-- | lib/arel/select_manager.rb | 33 | ||||
| -rw-r--r-- | lib/arel/visitors/mssql.rb | 7 | ||||
| -rw-r--r-- | lib/arel/visitors/oracle.rb | 4 | ||||
| -rw-r--r-- | lib/arel/visitors/to_sql.rb | 87 | ||||
| -rw-r--r-- | test/attributes/test_attribute.rb | 16 | ||||
| -rw-r--r-- | test/nodes/test_not.rb | 11 | ||||
| -rw-r--r-- | test/support/fake_record.rb | 13 | ||||
| -rw-r--r-- | test/test_select_manager.rb | 152 | ||||
| -rw-r--r-- | test/visitors/test_mssql.rb | 9 | ||||
| -rw-r--r-- | test/visitors/test_oracle.rb | 9 | ||||
| -rw-r--r-- | test/visitors/test_postgres.rb | 1 | ||||
| -rw-r--r-- | test/visitors/test_to_sql.rb | 6 |
22 files changed, 332 insertions, 67 deletions
diff --git a/History.txt b/History.txt index 51e950512f..aa5bbd77e2 100644 --- a/History.txt +++ b/History.txt @@ -7,6 +7,10 @@ * SQL Literals may be used as Attribute names * Added Arel::Nodes::NamedFunction for representing generic SQL functions +* Bug fixes + + * MSSQL adds TOP to sub selects + * Deprecations * Calls to `insert` are deprecated. Please use `compile_insert` then call diff --git a/Manifest.txt b/Manifest.txt index 8ecf21693e..5545ddf0cf 100644 --- a/Manifest.txt +++ b/Manifest.txt @@ -41,6 +41,7 @@ lib/arel/nodes/unary.rb lib/arel/nodes/unqualified_column.rb lib/arel/nodes/update_statement.rb lib/arel/nodes/values.rb +lib/arel/nodes/with.rb lib/arel/predications.rb lib/arel/relation.rb lib/arel/select_manager.rb diff --git a/arel.gemspec b/arel.gemspec index f6e89a4f16..5df1dd918a 100644 --- a/arel.gemspec +++ b/arel.gemspec @@ -2,15 +2,15 @@ Gem::Specification.new do |s| s.name = %q{arel} - s.version = "2.0.7.beta.20110105143137" + s.version = "2.0.7.beta.20110121165657" s.required_rubygems_version = Gem::Requirement.new("> 1.3.1") if s.respond_to? :required_rubygems_version= s.authors = ["Aaron Patterson", "Bryan Halmkamp", "Emilio Tagua", "Nick Kallen"] - s.date = %q{2011-01-05} + s.date = %q{2011-01-21} s.description = %q{Arel is a Relational Algebra for Ruby. It 1) simplifies the generation complex of SQL queries and it 2) adapts to various RDBMS systems. It is intended to be a framework framework; that is, you can build your own ORM with it, focusing on innovative object and collection modeling as opposed to database compatibility and query generation.} s.email = ["aaron@tenderlovemaking.com", "bryan@brynary.com", "miloops@gmail.com", "nick@example.org"] s.extra_rdoc_files = ["History.txt", "MIT-LICENSE.txt", "Manifest.txt", "README.markdown"] - s.files = [".autotest", "History.txt", "MIT-LICENSE.txt", "Manifest.txt", "README.markdown", "Rakefile", "arel.gemspec", "lib/arel.rb", "lib/arel/attributes.rb", "lib/arel/attributes/attribute.rb", "lib/arel/compatibility/wheres.rb", "lib/arel/crud.rb", "lib/arel/delete_manager.rb", "lib/arel/deprecated.rb", "lib/arel/expression.rb", "lib/arel/expressions.rb", "lib/arel/factory_methods.rb", "lib/arel/insert_manager.rb", "lib/arel/nodes.rb", "lib/arel/nodes/and.rb", "lib/arel/nodes/binary.rb", "lib/arel/nodes/count.rb", "lib/arel/nodes/delete_statement.rb", "lib/arel/nodes/equality.rb", "lib/arel/nodes/function.rb", "lib/arel/nodes/in.rb", "lib/arel/nodes/inner_join.rb", "lib/arel/nodes/insert_statement.rb", "lib/arel/nodes/join_source.rb", "lib/arel/nodes/lock.rb", "lib/arel/nodes/named_function.rb", "lib/arel/nodes/node.rb", "lib/arel/nodes/ordering.rb", "lib/arel/nodes/outer_join.rb", "lib/arel/nodes/select_core.rb", "lib/arel/nodes/select_statement.rb", "lib/arel/nodes/sql_literal.rb", "lib/arel/nodes/string_join.rb", "lib/arel/nodes/table_alias.rb", "lib/arel/nodes/unary.rb", "lib/arel/nodes/unqualified_column.rb", "lib/arel/nodes/update_statement.rb", "lib/arel/nodes/values.rb", "lib/arel/predications.rb", "lib/arel/relation.rb", "lib/arel/select_manager.rb", "lib/arel/sql/engine.rb", "lib/arel/sql_literal.rb", "lib/arel/table.rb", "lib/arel/tree_manager.rb", "lib/arel/update_manager.rb", "lib/arel/visitors.rb", "lib/arel/visitors/depth_first.rb", "lib/arel/visitors/dot.rb", "lib/arel/visitors/join_sql.rb", "lib/arel/visitors/mssql.rb", "lib/arel/visitors/mysql.rb", "lib/arel/visitors/oracle.rb", "lib/arel/visitors/order_clauses.rb", "lib/arel/visitors/postgresql.rb", "lib/arel/visitors/sqlite.rb", "lib/arel/visitors/to_sql.rb", "lib/arel/visitors/visitor.rb", "lib/arel/visitors/where_sql.rb", "test/attributes/test_attribute.rb", "test/helper.rb", "test/nodes/test_as.rb", "test/nodes/test_count.rb", "test/nodes/test_delete_statement.rb", "test/nodes/test_equality.rb", "test/nodes/test_insert_statement.rb", "test/nodes/test_named_function.rb", "test/nodes/test_node.rb", "test/nodes/test_not.rb", "test/nodes/test_or.rb", "test/nodes/test_select_core.rb", "test/nodes/test_select_statement.rb", "test/nodes/test_sql_literal.rb", "test/nodes/test_sum.rb", "test/nodes/test_update_statement.rb", "test/support/fake_record.rb", "test/test_activerecord_compat.rb", "test/test_attributes.rb", "test/test_crud.rb", "test/test_delete_manager.rb", "test/test_factory_methods.rb", "test/test_insert_manager.rb", "test/test_select_manager.rb", "test/test_table.rb", "test/test_update_manager.rb", "test/visitors/test_depth_first.rb", "test/visitors/test_dot.rb", "test/visitors/test_join_sql.rb", "test/visitors/test_mssql.rb", "test/visitors/test_mysql.rb", "test/visitors/test_oracle.rb", "test/visitors/test_postgres.rb", "test/visitors/test_sqlite.rb", "test/visitors/test_to_sql.rb"] + s.files = [".autotest", "History.txt", "MIT-LICENSE.txt", "Manifest.txt", "README.markdown", "Rakefile", "arel.gemspec", "lib/arel.rb", "lib/arel/attributes.rb", "lib/arel/attributes/attribute.rb", "lib/arel/compatibility/wheres.rb", "lib/arel/crud.rb", "lib/arel/delete_manager.rb", "lib/arel/deprecated.rb", "lib/arel/expression.rb", "lib/arel/expressions.rb", "lib/arel/factory_methods.rb", "lib/arel/insert_manager.rb", "lib/arel/nodes.rb", "lib/arel/nodes/and.rb", "lib/arel/nodes/binary.rb", "lib/arel/nodes/count.rb", "lib/arel/nodes/delete_statement.rb", "lib/arel/nodes/equality.rb", "lib/arel/nodes/function.rb", "lib/arel/nodes/in.rb", "lib/arel/nodes/inner_join.rb", "lib/arel/nodes/insert_statement.rb", "lib/arel/nodes/join_source.rb", "lib/arel/nodes/lock.rb", "lib/arel/nodes/named_function.rb", "lib/arel/nodes/node.rb", "lib/arel/nodes/ordering.rb", "lib/arel/nodes/outer_join.rb", "lib/arel/nodes/select_core.rb", "lib/arel/nodes/select_statement.rb", "lib/arel/nodes/sql_literal.rb", "lib/arel/nodes/string_join.rb", "lib/arel/nodes/table_alias.rb", "lib/arel/nodes/unary.rb", "lib/arel/nodes/unqualified_column.rb", "lib/arel/nodes/update_statement.rb", "lib/arel/nodes/values.rb", "lib/arel/nodes/with.rb", "lib/arel/predications.rb", "lib/arel/relation.rb", "lib/arel/select_manager.rb", "lib/arel/sql/engine.rb", "lib/arel/sql_literal.rb", "lib/arel/table.rb", "lib/arel/tree_manager.rb", "lib/arel/update_manager.rb", "lib/arel/visitors.rb", "lib/arel/visitors/depth_first.rb", "lib/arel/visitors/dot.rb", "lib/arel/visitors/join_sql.rb", "lib/arel/visitors/mssql.rb", "lib/arel/visitors/mysql.rb", "lib/arel/visitors/oracle.rb", "lib/arel/visitors/order_clauses.rb", "lib/arel/visitors/postgresql.rb", "lib/arel/visitors/sqlite.rb", "lib/arel/visitors/to_sql.rb", "lib/arel/visitors/visitor.rb", "lib/arel/visitors/where_sql.rb", "test/attributes/test_attribute.rb", "test/helper.rb", "test/nodes/test_as.rb", "test/nodes/test_count.rb", "test/nodes/test_delete_statement.rb", "test/nodes/test_equality.rb", "test/nodes/test_insert_statement.rb", "test/nodes/test_named_function.rb", "test/nodes/test_node.rb", "test/nodes/test_not.rb", "test/nodes/test_or.rb", "test/nodes/test_select_core.rb", "test/nodes/test_select_statement.rb", "test/nodes/test_sql_literal.rb", "test/nodes/test_sum.rb", "test/nodes/test_update_statement.rb", "test/support/fake_record.rb", "test/test_activerecord_compat.rb", "test/test_attributes.rb", "test/test_crud.rb", "test/test_delete_manager.rb", "test/test_factory_methods.rb", "test/test_insert_manager.rb", "test/test_select_manager.rb", "test/test_table.rb", "test/test_update_manager.rb", "test/visitors/test_depth_first.rb", "test/visitors/test_dot.rb", "test/visitors/test_join_sql.rb", "test/visitors/test_mssql.rb", "test/visitors/test_mysql.rb", "test/visitors/test_oracle.rb", "test/visitors/test_postgres.rb", "test/visitors/test_sqlite.rb", "test/visitors/test_to_sql.rb"] s.homepage = %q{http://github.com/rails/arel} s.rdoc_options = ["--main", "README.markdown"] s.require_paths = ["lib"] @@ -24,18 +24,18 @@ Gem::Specification.new do |s| s.specification_version = 3 if Gem::Version.new(Gem::VERSION) >= Gem::Version.new('1.2.0') then - s.add_development_dependency(%q<minitest>, [">= 1.6.0"]) + s.add_development_dependency(%q<minitest>, [">= 2.0.0"]) s.add_development_dependency(%q<hoe>, [">= 2.1.0"]) s.add_development_dependency(%q<minitest>, [">= 1.6.0"]) s.add_development_dependency(%q<hoe>, [">= 2.8.0"]) else - s.add_dependency(%q<minitest>, [">= 1.6.0"]) + s.add_dependency(%q<minitest>, [">= 2.0.0"]) s.add_dependency(%q<hoe>, [">= 2.1.0"]) s.add_dependency(%q<minitest>, [">= 1.6.0"]) s.add_dependency(%q<hoe>, [">= 2.8.0"]) end else - s.add_dependency(%q<minitest>, [">= 1.6.0"]) + s.add_dependency(%q<minitest>, [">= 2.0.0"]) s.add_dependency(%q<hoe>, [">= 2.1.0"]) s.add_dependency(%q<minitest>, [">= 1.6.0"]) s.add_dependency(%q<hoe>, [">= 2.8.0"]) diff --git a/lib/arel/nodes.rb b/lib/arel/nodes.rb index 64f2cc13fd..cbd10c31e0 100644 --- a/lib/arel/nodes.rb +++ b/lib/arel/nodes.rb @@ -9,6 +9,7 @@ require 'arel/nodes/update_statement' # unary require 'arel/nodes/unary' require 'arel/nodes/unqualified_column' +require 'arel/nodes/with' # binary require 'arel/nodes/binary' diff --git a/lib/arel/nodes/binary.rb b/lib/arel/nodes/binary.rb index 0d02554199..bcd46db398 100644 --- a/lib/arel/nodes/binary.rb +++ b/lib/arel/nodes/binary.rb @@ -29,6 +29,10 @@ module Arel NotEqual NotIn Or + Union + UnionAll + Intersect + Except }.each do |name| const_set name, Class.new(Binary) end diff --git a/lib/arel/nodes/named_function.rb b/lib/arel/nodes/named_function.rb index 56669bf858..5fca33e323 100644 --- a/lib/arel/nodes/named_function.rb +++ b/lib/arel/nodes/named_function.rb @@ -3,6 +3,8 @@ module Arel class NamedFunction < Arel::Nodes::Function attr_accessor :name + include Arel::Predications + def initialize name, expr, aliaz = nil super(expr, aliaz) @name = name diff --git a/lib/arel/nodes/node.rb b/lib/arel/nodes/node.rb index 711fa34b6d..1a5bc27856 100644 --- a/lib/arel/nodes/node.rb +++ b/lib/arel/nodes/node.rb @@ -10,7 +10,7 @@ module Arel # Factory method to create a Nodes::Not node that has the recipient of # the caller as a child. def not - Nodes::Not.new Nodes::Grouping.new self + Nodes::Not.new self end ### diff --git a/lib/arel/nodes/select_statement.rb b/lib/arel/nodes/select_statement.rb index c9a0cde4e0..c99842f22f 100644 --- a/lib/arel/nodes/select_statement.rb +++ b/lib/arel/nodes/select_statement.rb @@ -2,15 +2,16 @@ module Arel module Nodes class SelectStatement < Arel::Nodes::Node attr_reader :cores - attr_accessor :limit, :orders, :lock, :offset + attr_accessor :limit, :orders, :lock, :offset, :with def initialize cores = [SelectCore.new] #puts caller - @cores = cores - @orders = [] - @limit = nil - @lock = nil - @offset = nil + @cores = cores + @orders = [] + @limit = nil + @lock = nil + @offset = nil + @with = nil end def initialize_copy other diff --git a/lib/arel/nodes/with.rb b/lib/arel/nodes/with.rb new file mode 100644 index 0000000000..7f08abe47d --- /dev/null +++ b/lib/arel/nodes/with.rb @@ -0,0 +1,10 @@ +module Arel + module Nodes + class With < Arel::Nodes::Unary + alias children expr + end + + class WithRecursive < With; end + end +end + diff --git a/lib/arel/predications.rb b/lib/arel/predications.rb index 58f02a2b53..920a9ee374 100644 --- a/lib/arel/predications.rb +++ b/lib/arel/predications.rb @@ -163,6 +163,7 @@ module Arel private def grouping_any method_id, others + others = others.dup first = send method_id, others.shift Nodes::Grouping.new others.inject(first) { |memo,expr| @@ -171,6 +172,7 @@ module Arel end def grouping_all method_id, others + others = others.dup first = send method_id, others.shift Nodes::Grouping.new others.inject(first) { |memo,expr| diff --git a/lib/arel/select_manager.rb b/lib/arel/select_manager.rb index fce6ca17d8..249ad680a7 100644 --- a/lib/arel/select_manager.rb +++ b/lib/arel/select_manager.rb @@ -31,7 +31,7 @@ module Arel def where_clauses if $VERBOSE - warn "(#{caller.first}) where_clauses is deprecated and will be removed in arel 3.0.0 with no replacement" + warn "(#{caller.first}) where_clauses is deprecated and will be removed in arel 3.0.0 with no replacement" end to_sql = Visitors::ToSql.new @engine @ctx.wheres.map { |c| to_sql.accept c } @@ -134,6 +134,37 @@ module Arel Nodes::SqlLiteral.new viz.accept @ctx end + def union operation, other = nil + if other + node_class = Nodes.const_get("Union#{operation.to_s.capitalize}") + else + other = operation + node_class = Nodes::Union + end + + node_class.new self.ast, other.ast + end + + def intersect other + Nodes::Intersect.new ast, other.ast + end + + def except other + Nodes::Except.new ast, other.ast + end + alias :minus :except + + def with *subqueries + if subqueries.first.is_a? Symbol + node_class = Nodes.const_get("With#{subqueries.shift.to_s.capitalize}") + else + node_class = Nodes::With + end + @ast.with = node_class.new(subqueries.flatten) + + self + end + def take limit @ast.limit = Nodes::Limit.new(limit) @ctx.top = Nodes::Top.new(limit) diff --git a/lib/arel/visitors/mssql.rb b/lib/arel/visitors/mssql.rb index 9b0e77c19b..ea7ab6394c 100644 --- a/lib/arel/visitors/mssql.rb +++ b/lib/arel/visitors/mssql.rb @@ -3,6 +3,13 @@ module Arel class MSSQL < Arel::Visitors::ToSql private + def build_subselect key, o + stmt = super + core = stmt.cores.first + core.top = Nodes::Top.new(o.limit.expr) if o.limit + stmt + end + def visit_Arel_Nodes_Limit o "" end diff --git a/lib/arel/visitors/oracle.rb b/lib/arel/visitors/oracle.rb index aaf0324fd7..3beea287a6 100644 --- a/lib/arel/visitors/oracle.rb +++ b/lib/arel/visitors/oracle.rb @@ -61,6 +61,10 @@ module Arel "raw_rnum_ > #{visit o.expr}" end + def visit_Arel_Nodes_Except o + "( #{visit o.left} MINUS #{visit o.right} )" + end + ### # Hacks for the order clauses specific to Oracle def order_hacks o diff --git a/lib/arel/visitors/to_sql.rb b/lib/arel/visitors/to_sql.rb index d5534384f8..70e10a5e0f 100644 --- a/lib/arel/visitors/to_sql.rb +++ b/lib/arel/visitors/to_sql.rb @@ -11,14 +11,6 @@ module Arel @last_column = nil @quoted_tables = {} @quoted_columns = {} - @column_cache = Hash.new { |h,pool| - h[pool] = Hash.new { |conn_h,column| - conn_h[column] = {} - } - } - @table_exists = Hash.new { |h,pool| - h[pool] = {} - } end def accept object @@ -38,6 +30,17 @@ module Arel ].compact.join ' ' end + # FIXME: we should probably have a 2-pass visitor for this + def build_subselect key, o + stmt = Nodes::SelectStatement.new + core = stmt.cores.first + core.froms = o.relation + core.projections = [key] + stmt.limit = o.limit + stmt.orders = o.orders + stmt + end + def visit_Arel_Nodes_UpdateStatement o if o.orders.empty? && o.limit.nil? wheres = o.wheres @@ -48,19 +51,11 @@ module Arel (#{caller.first}) Using UpdateManager without setting UpdateManager#key is deprecated and support will be removed in ARel 3.0.0. Please set the primary key on UpdateManager using UpdateManager#key= -eowarn + eowarn key = o.relation.primary_key end - wheres = o.wheres - stmt = Nodes::SelectStatement.new - core = stmt.cores.first - core.froms = o.relation - core.projections = [key] - stmt.limit = o.limit - stmt.orders = o.orders - - wheres = [Nodes::In.new(key, [stmt])] + wheres = [Nodes::In.new(key, [build_subselect(key, o)])] end [ @@ -75,8 +70,8 @@ eowarn "INSERT INTO #{visit o.relation}", ("(#{o.columns.map { |x| - quote_column_name x.name - }.join ', '})" unless o.columns.empty?), + quote_column_name x.name + }.join ', '})" unless o.columns.empty?), (visit o.values if o.values), ].compact.join ' ' @@ -88,37 +83,20 @@ eowarn end def table_exists? name - return true if table_exists.key? name - - @connection.tables.each do |table| - table_exists[table] = true - end - - table_exists.key? name - end - - def table_exists - @table_exists[@pool] + @pool.table_exists? name end def column_for attr - name = attr.name.to_sym + name = attr.name.to_s table = attr.relation.name return nil unless table_exists? table - # If we don't have this column cached, get a list of columns and - # cache them for this table - unless column_cache.key? table - columns = @connection.columns(table, "#{table}(#{name}) Columns") - column_cache[table] = Hash[columns.map { |c| [c.name.to_sym, c] }] - end - column_cache[table][name] end def column_cache - @column_cache[@pool] + @pool.columns_hash end def visit_Arel_Nodes_Values o @@ -129,6 +107,7 @@ eowarn def visit_Arel_Nodes_SelectStatement o [ + (visit(o.with) if o.with), o.cores.map { |x| visit_Arel_Nodes_SelectCore x }.join, ("ORDER BY #{o.orders.map { |x| visit x }.join(', ')}" unless o.orders.empty?), (visit(o.limit) if o.limit), @@ -149,6 +128,30 @@ eowarn ].compact.join ' ' end + def visit_Arel_Nodes_With o + "WITH #{o.children.map { |x| visit x }.join(', ')}" + end + + def visit_Arel_Nodes_WithRecursive o + "WITH RECURSIVE #{o.children.map { |x| visit x }.join(', ')}" + end + + def visit_Arel_Nodes_Union o + "( #{visit o.left} UNION #{visit o.right} )" + end + + def visit_Arel_Nodes_UnionAll o + "( #{visit o.left} UNION ALL #{visit o.right} )" + end + + def visit_Arel_Nodes_Intersect o + "( #{visit o.left} INTERSECT #{visit o.right} )" + end + + def visit_Arel_Nodes_Except o + "( #{visit o.left} EXCEPT #{visit o.right} )" + end + def visit_Arel_Nodes_Having o "HAVING #{visit o.expr}" end @@ -286,11 +289,11 @@ eowarn end def visit_Arel_Nodes_In o - "#{visit o.left} IN (#{visit o.right})" + "#{visit o.left} IN (#{visit o.right})" end def visit_Arel_Nodes_NotIn o - "#{visit o.left} NOT IN (#{visit o.right})" + "#{visit o.left} NOT IN (#{visit o.right})" end def visit_Arel_Nodes_And o diff --git a/test/attributes/test_attribute.rb b/test/attributes/test_attribute.rb index df7dc69621..352774071a 100644 --- a/test/attributes/test_attribute.rb +++ b/test/attributes/test_attribute.rb @@ -366,6 +366,14 @@ module Arel SELECT "users"."id" FROM "users" WHERE ("users"."id" = 1 OR "users"."id" = 2) } end + + it 'should not eat input' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + values = [1,2] + mgr.where relation[:id].eq_any(values) + values.must_equal [1,2] + end end describe '#eq_all' do @@ -382,6 +390,14 @@ module Arel SELECT "users"."id" FROM "users" WHERE ("users"."id" = 1 AND "users"."id" = 2) } end + + it 'should not eat input' do + relation = Table.new(:users) + mgr = relation.project relation[:id] + values = [1,2] + mgr.where relation[:id].eq_all(values) + values.must_equal [1,2] + end end describe '#matches' do diff --git a/test/nodes/test_not.rb b/test/nodes/test_not.rb index d02a9bad74..c5bb0088c8 100644 --- a/test/nodes/test_not.rb +++ b/test/nodes/test_not.rb @@ -6,13 +6,10 @@ module Arel describe '#not' do it 'makes a NOT node' do attr = Table.new(:users)[:id] - left = attr.eq(10) - right = attr.eq(11) - node = left.or right - node.expr.left.must_equal left - node.expr.right.must_equal right - - node.or(right).not + expr = attr.eq(10) + node = expr.not + node.must_be_kind_of Not + node.expr.must_equal expr end end end diff --git a/test/support/fake_record.rb b/test/support/fake_record.rb index 376ed40f2d..54f73489c9 100644 --- a/test/support/fake_record.rb +++ b/test/support/fake_record.rb @@ -3,7 +3,7 @@ module FakeRecord end class Connection - attr_reader :tables + attr_reader :tables, :columns_hash def initialize @tables = %w{ users photos developers } @@ -15,6 +15,9 @@ module FakeRecord Column.new('created_at', :date), ] } + @columns_hash = { + 'users' => Hash[@columns['users'].map { |x| [x.name, x] }] + } @primary_keys = { 'users' => 'id' } @@ -75,6 +78,14 @@ module FakeRecord def with_connection yield connection end + + def table_exists? name + connection.tables.include? name.to_s + end + + def columns_hash + connection.columns_hash + end end class Base diff --git a/test/test_select_manager.rb b/test/test_select_manager.rb index 271a8ae0b0..4b4733db97 100644 --- a/test/test_select_manager.rb +++ b/test/test_select_manager.rb @@ -30,6 +30,10 @@ module Arel @engine.connection.columns table, message end + def columns_hash + @engine.connection.columns_hash + end + def table_exists? name @engine.connection.table_exists? name end @@ -48,9 +52,9 @@ module Arel describe 'select manager' do def test_join_sources - manager = Arel::SelectManager.new Table.engine - manager.join_sources << Arel::Nodes::StringJoin.new('foo') - assert_equal "SELECT FROM 'foo'", manager.to_sql + manager = Arel::SelectManager.new Table.engine + manager.join_sources << Arel::Nodes::StringJoin.new('foo') + assert_equal "SELECT FROM 'foo'", manager.to_sql end describe 'backwards compatibility' do @@ -122,6 +126,24 @@ module Arel mgr.to_sql.must_be_like %{ SELECT FROM "users" HAVING foo AND bar } end end + + describe 'on' do + it 'converts to sqlliterals' do + table = Table.new :users + right = table.alias + mgr = table.from table + mgr.join(right).on("omg") + mgr.to_sql.must_be_like %{ SELECT FROM "users" INNER JOIN "users" "users_2" ON omg } + end + + it 'converts to sqlliterals' do + table = Table.new :users + right = table.alias + mgr = table.from table + mgr.join(right).on("omg", "123") + mgr.to_sql.must_be_like %{ SELECT FROM "users" INNER JOIN "users" "users_2" ON omg AND 123 } + end + end end describe 'clone' do @@ -178,6 +200,130 @@ module Arel end end + describe 'union' do + before do + table = Table.new :users + @m1 = Arel::SelectManager.new Table.engine, table + @m1.project Arel.star + @m1.where(table[:age].lt(18)) + + @m2 = Arel::SelectManager.new Table.engine, table + @m2.project Arel.star + @m2.where(table[:age].gt(99)) + + + end + + it 'should union two managers' do + # FIXME should this union "managers" or "statements" ? + # FIXME this probably shouldn't return a node + node = @m1.union @m2 + + # maybe FIXME: decide when wrapper parens are needed + node.to_sql.must_be_like %{ + ( SELECT * FROM "users" WHERE "users"."age" < 18 UNION SELECT * FROM "users" WHERE "users"."age" > 99 ) + } + end + + it 'should union all' do + node = @m1.union :all, @m2 + + node.to_sql.must_be_like %{ + ( SELECT * FROM "users" WHERE "users"."age" < 18 UNION ALL SELECT * FROM "users" WHERE "users"."age" > 99 ) + } + end + + end + + describe 'intersect' do + before do + table = Table.new :users + @m1 = Arel::SelectManager.new Table.engine, table + @m1.project Arel.star + @m1.where(table[:age].gt(18)) + + @m2 = Arel::SelectManager.new Table.engine, table + @m2.project Arel.star + @m2.where(table[:age].lt(99)) + + + end + + it 'should interect two managers' do + # FIXME should this intersect "managers" or "statements" ? + # FIXME this probably shouldn't return a node + node = @m1.intersect @m2 + + # maybe FIXME: decide when wrapper parens are needed + node.to_sql.must_be_like %{ + ( SELECT * FROM "users" WHERE "users"."age" > 18 INTERSECT SELECT * FROM "users" WHERE "users"."age" < 99 ) + } + end + + end + + describe 'except' do + before do + table = Table.new :users + @m1 = Arel::SelectManager.new Table.engine, table + @m1.project Arel.star + @m1.where(table[:age].in(18..60)) + + @m2 = Arel::SelectManager.new Table.engine, table + @m2.project Arel.star + @m2.where(table[:age].in(40..99)) + + + end + + it 'should except two managers' do + # FIXME should this except "managers" or "statements" ? + # FIXME this probably shouldn't return a node + node = @m1.except @m2 + + # maybe FIXME: decide when wrapper parens are needed + node.to_sql.must_be_like %{ + ( SELECT * FROM "users" WHERE "users"."age" BETWEEN 18 AND 60 EXCEPT SELECT * FROM "users" WHERE "users"."age" BETWEEN 40 AND 99 ) + } + end + + end + + describe 'with' do + + it "should support WITH RECURSIVE" do + comments = Table.new(:comments) + comments_id = comments[:id] + comments_parent_id = comments[:parent_id] + + replies = Table.new(:replies) + replies_id = replies[:id] + + recursive_term = Arel::SelectManager.new Table.engine + recursive_term.from(comments).project(comments_id, comments_parent_id).where(comments_id.eq 42) + + non_recursive_term = Arel::SelectManager.new Table.engine + non_recursive_term.from(comments).project(comments_id, comments_parent_id).join(replies).on(comments_parent_id.eq replies_id) + + union = recursive_term.union(non_recursive_term) + + as_statement = Arel::Nodes::As.new replies, union + + manager = Arel::SelectManager.new Table.engine + manager.with(:recursive, as_statement).from(replies).project(Arel.star) + + sql = manager.to_sql + sql.must_be_like %{ + WITH RECURSIVE "replies" AS ( + SELECT "comments"."id", "comments"."parent_id" FROM "comments" WHERE "comments"."id" = 42 + UNION + SELECT "comments"."id", "comments"."parent_id" FROM "comments" INNER JOIN "replies" ON "comments"."parent_id" = "replies"."id" + ) + SELECT * FROM "replies" + } + end + end + describe 'ast' do it 'should return the ast' do table = Table.new :users diff --git a/test/visitors/test_mssql.rb b/test/visitors/test_mssql.rb index b658e46828..ccaea395fe 100644 --- a/test/visitors/test_mssql.rb +++ b/test/visitors/test_mssql.rb @@ -13,6 +13,15 @@ module Arel sql = @visitor.accept(stmt) sql.must_be_like "SELECT TOP 1" end + + it 'uses TOP in updates with a limit' do + stmt = Nodes::UpdateStatement.new + stmt.limit = Nodes::Limit.new(1) + stmt.key = 'id' + sql = @visitor.accept(stmt) + sql.must_be_like "UPDATE NULL WHERE 'id' IN (SELECT TOP 1 'id' )" + end + end end end diff --git a/test/visitors/test_oracle.rb b/test/visitors/test_oracle.rb index c6fcb7134c..87b94409e3 100644 --- a/test/visitors/test_oracle.rb +++ b/test/visitors/test_oracle.rb @@ -134,6 +134,15 @@ module Arel end end + + it 'modified except to be minus' do + left = Nodes::SqlLiteral.new("SELECT * FROM users WHERE age > 10") + right = Nodes::SqlLiteral.new("SELECT * FROM users WHERE age > 20") + sql = @visitor.accept Nodes::Except.new(left, right) + sql.must_be_like %{ + ( SELECT * FROM users WHERE age > 10 MINUS SELECT * FROM users WHERE age > 20 ) + } + end end end end diff --git a/test/visitors/test_postgres.rb b/test/visitors/test_postgres.rb index ec55e555a6..9ed42b1806 100644 --- a/test/visitors/test_postgres.rb +++ b/test/visitors/test_postgres.rb @@ -31,6 +31,7 @@ module Arel assert_match(/LIMIT 'omg'/, sql) assert_equal 1, sql.scan(/LIMIT/).length, 'should have one limit' end + end end end diff --git a/test/visitors/test_to_sql.rb b/test/visitors/test_to_sql.rb index 0c7baab923..c8ad40e242 100644 --- a/test/visitors/test_to_sql.rb +++ b/test/visitors/test_to_sql.rb @@ -20,6 +20,12 @@ module Arel assert_equal 'omg(*)', @visitor.accept(function) end + it 'should chain predications on named functions' do + function = Nodes::NamedFunction.new('omg', [Arel.star]) + sql = @visitor.accept(function.eq(2)) + sql.must_be_like %{ omg(*) = 2 } + end + it 'works with lists' do function = Nodes::NamedFunction.new('omg', [Arel.star, Arel.star]) assert_equal 'omg(*, *)', @visitor.accept(function) |