require 'helper' module Arel class EngineProxy attr_reader :executed attr_reader :connection_pool attr_reader :spec attr_reader :config def initialize engine @engine = engine @executed = [] @connection_pool = self @spec = self @config = { :adapter => 'sqlite3' } end def with_connection yield self end def connection self end def quote_table_name thing; @engine.connection.quote_table_name thing end def quote_column_name thing; @engine.connection.quote_column_name thing end def quote thing, column; @engine.connection.quote thing, column end def columns table, message = nil @engine.connection.columns table, message end def columns_hash @engine.connection.columns_hash end def table_exists? name @engine.connection.table_exists? name end def tables @engine.connection.tables end def execute sql, name = nil, *args @executed << sql end alias :update :execute alias :delete :execute alias :insert :execute end 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 end describe 'backwards compatibility' do describe 'project' do it 'accepts symbols as sql literals' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.project :id manager.from table manager.to_sql.must_be_like %{ SELECT id FROM "users" } end end describe 'order' do it 'accepts symbols' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.project SqlLiteral.new '*' manager.from table manager.order :foo manager.to_sql.must_be_like %{ SELECT * FROM "users" ORDER BY foo } end end describe 'group' do it 'takes a symbol' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from table manager.group :foo manager.to_sql.must_be_like %{ SELECT FROM "users" GROUP BY foo } end end describe 'as' do it 'makes an AS node by grouping the AST' do manager = Arel::SelectManager.new Table.engine as = manager.as(Arel.sql('foo')) assert_kind_of Arel::Nodes::Grouping, as.left assert_equal manager.ast, as.left.expr assert_equal 'foo', as.right end it 'converts right to SqlLiteral if a string' do manager = Arel::SelectManager.new Table.engine as = manager.as('foo') assert_kind_of Arel::Nodes::SqlLiteral, as.right end it 'can make a subselect' do manager = Arel::SelectManager.new Table.engine manager.project Arel.star manager.from Arel.sql('zomg') as = manager.as(Arel.sql('foo')) manager = Arel::SelectManager.new Table.engine manager.project Arel.sql('name') manager.from as manager.to_sql.must_be_like "SELECT name FROM (SELECT * FROM zomg ) foo" end end describe 'from' do it 'ignores strings when table of same name exists' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from table manager.from 'users' manager.project table['id'] manager.to_sql.must_be_like 'SELECT "users"."id" FROM users' end it 'should support any ast' do table = Table.new :users manager1 = Arel::SelectManager.new Table.engine manager2 = Arel::SelectManager.new Table.engine manager2.project(Arel.sql('*')) manager2.from table manager1.project Arel.sql('lol') as = manager2.as Arel.sql('omg') manager1.from(as) manager1.to_sql.must_be_like %{ SELECT lol FROM (SELECT * FROM "users" ) omg } end end describe 'having' do it 'converts strings to SQLLiterals' do table = Table.new :users mgr = table.from table mgr.having 'foo' mgr.to_sql.must_be_like %{ SELECT FROM "users" HAVING foo } end it 'can have multiple items specified separately' do table = Table.new :users mgr = table.from table mgr.having 'foo' mgr.having 'bar' mgr.to_sql.must_be_like %{ SELECT FROM "users" HAVING foo AND bar } end it 'can have multiple items specified together' do table = Table.new :users mgr = table.from table mgr.having 'foo', 'bar' 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 it 'creates new cores' do table = Table.new :users, :engine => Table.engine, :as => 'foo' mgr = table.from table m2 = mgr.clone m2.project "foo" mgr.to_sql.wont_equal m2.to_sql end it 'makes updates to the correct copy' do table = Table.new :users, :engine => Table.engine, :as => 'foo' mgr = table.from table m2 = mgr.clone m3 = m2.clone m2.project "foo" mgr.to_sql.wont_equal m2.to_sql m3.to_sql.must_equal mgr.to_sql end end describe 'initialize' do it 'uses alias in sql' do table = Table.new :users, :engine => Table.engine, :as => 'foo' mgr = table.from table mgr.skip 10 mgr.to_sql.must_be_like %{ SELECT FROM "users" foo OFFSET 10 } end end describe 'skip' do it 'should add an offset' do table = Table.new :users mgr = table.from table mgr.skip 10 mgr.to_sql.must_be_like %{ SELECT FROM "users" OFFSET 10 } end it 'should chain' do table = Table.new :users mgr = table.from table mgr.skip(10).to_sql.must_be_like %{ SELECT FROM "users" OFFSET 10 } end end describe 'offset' do it 'should add an offset' do table = Table.new :users mgr = table.from table mgr.offset = 10 mgr.to_sql.must_be_like %{ SELECT FROM "users" OFFSET 10 } end it 'should remove an offset' do table = Table.new :users mgr = table.from table mgr.offset = 10 mgr.to_sql.must_be_like %{ SELECT FROM "users" OFFSET 10 } mgr.offset = nil mgr.to_sql.must_be_like %{ SELECT FROM "users" } end it 'should return the offset' do table = Table.new :users mgr = table.from table mgr.offset = 10 assert_equal 10, mgr.offset end end describe 'exists' do it 'should create an exists clause' do table = Table.new(:users) manager = Arel::SelectManager.new Table.engine, table manager.project SqlLiteral.new '*' m2 = Arel::SelectManager.new(manager.engine) m2.project manager.exists m2.to_sql.must_be_like %{ SELECT EXISTS (#{manager.to_sql}) } end it 'can be aliased' do table = Table.new(:users) manager = Arel::SelectManager.new Table.engine, table manager.project SqlLiteral.new '*' m2 = Arel::SelectManager.new(manager.engine) m2.project manager.exists.as('foo') m2.to_sql.must_be_like %{ SELECT EXISTS (#{manager.to_sql}) AS foo } 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 mgr = table.from table ast = mgr.ast mgr.visitor.accept(ast).must_equal mgr.to_sql end end describe 'taken' do it 'should return limit' do manager = Arel::SelectManager.new Table.engine manager.take 10 manager.taken.must_equal 10 end end describe 'insert' do it 'uses the select FROM' do engine = EngineProxy.new Table.engine table = Table.new :users manager = Arel::SelectManager.new engine manager.from table manager.insert 'VALUES(NULL)' engine.executed.last.must_be_like %{ INSERT INTO "users" VALUES(NULL) } end end describe 'lock' do # This should fail on other databases it 'adds a lock node' do table = Table.new :users mgr = table.from table mgr.lock.to_sql.must_be_like %{ SELECT FROM "users" } end end describe 'orders' do it 'returns order clauses' do table = Table.new :users manager = Arel::SelectManager.new Table.engine order = table[:id] manager.order table[:id] manager.orders.must_equal [order] end end describe 'order' do it 'generates order clauses' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.project SqlLiteral.new '*' manager.from table manager.order table[:id] manager.to_sql.must_be_like %{ SELECT * FROM "users" ORDER BY "users"."id" } end # FIXME: I would like to deprecate this it 'takes *args' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.project SqlLiteral.new '*' manager.from table manager.order table[:id], table[:name] manager.to_sql.must_be_like %{ SELECT * FROM "users" ORDER BY "users"."id", "users"."name" } end it 'chains' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.order(table[:id]).must_equal manager end end describe 'on' do it 'takes two params' do left = Table.new :users right = left.alias predicate = left[:id].eq(right[:id]) manager = Arel::SelectManager.new Table.engine manager.from left manager.join(right).on(predicate, predicate) manager.to_sql.must_be_like %{ SELECT FROM "users" INNER JOIN "users" "users_2" ON "users"."id" = "users_2"."id" AND "users"."id" = "users_2"."id" } end it 'takes three params' do left = Table.new :users right = left.alias predicate = left[:id].eq(right[:id]) manager = Arel::SelectManager.new Table.engine manager.from left manager.join(right).on( predicate, predicate, left[:name].eq(right[:name]) ) manager.to_sql.must_be_like %{ SELECT FROM "users" INNER JOIN "users" "users_2" ON "users"."id" = "users_2"."id" AND "users"."id" = "users_2"."id" AND "users"."name" = "users_2"."name" } end end it 'should hand back froms' do relation = Arel::SelectManager.new Table.engine assert_equal [], relation.froms end it 'should create and nodes' do relation = Arel::SelectManager.new Table.engine children = ['foo', 'bar', 'baz'] clause = relation.create_and children assert_kind_of Arel::Nodes::And, clause assert_equal children, clause.children end it 'should create insert managers' do relation = Arel::SelectManager.new Table.engine insert = relation.create_insert assert_kind_of Arel::InsertManager, insert end it 'should create join nodes' do relation = Arel::SelectManager.new Table.engine join = relation.create_join 'foo', 'bar' assert_kind_of Arel::Nodes::InnerJoin, join assert_equal 'foo', join.left assert_equal 'bar', join.right end it 'should create join nodes with a klass' do relation = Arel::SelectManager.new Table.engine join = relation.create_join 'foo', 'bar', Arel::Nodes::OuterJoin assert_kind_of Arel::Nodes::OuterJoin, join assert_equal 'foo', join.left assert_equal 'bar', join.right end describe 'join' do it 'responds to join' do left = Table.new :users right = left.alias predicate = left[:id].eq(right[:id]) manager = Arel::SelectManager.new Table.engine manager.from left manager.join(right).on(predicate) manager.to_sql.must_be_like %{ SELECT FROM "users" INNER JOIN "users" "users_2" ON "users"."id" = "users_2"."id" } end it 'takes a class' do left = Table.new :users right = left.alias predicate = left[:id].eq(right[:id]) manager = Arel::SelectManager.new Table.engine manager.from left manager.join(right, Nodes::OuterJoin).on(predicate) manager.to_sql.must_be_like %{ SELECT FROM "users" LEFT OUTER JOIN "users" "users_2" ON "users"."id" = "users_2"."id" } end it 'noops on nil' do manager = Arel::SelectManager.new Table.engine manager.join(nil).must_equal manager end end describe 'joins' do it 'returns join sql' do table = Table.new :users aliaz = table.alias manager = Arel::SelectManager.new Table.engine manager.from Nodes::InnerJoin.new(aliaz, table[:id].eq(aliaz[:id])) manager.join_sql.must_be_like %{ INNER JOIN "users" "users_2" "users"."id" = "users_2"."id" } end it 'returns outer join sql' do table = Table.new :users aliaz = table.alias manager = Arel::SelectManager.new Table.engine manager.from Nodes::OuterJoin.new(aliaz, table[:id].eq(aliaz[:id])) manager.join_sql.must_be_like %{ LEFT OUTER JOIN "users" "users_2" "users"."id" = "users_2"."id" } end it 'returns string join sql' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from Nodes::StringJoin.new('hello') manager.join_sql.must_be_like %{ 'hello' } end it 'returns nil join sql' do manager = Arel::SelectManager.new Table.engine manager.join_sql.must_be_nil end end describe 'order_clauses' do it 'returns order clauses as a list' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from table manager.order table[:id] manager.order_clauses.first.must_be_like %{ "users"."id" } end end describe 'group' do it 'takes an attribute' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from table manager.group table[:id] manager.to_sql.must_be_like %{ SELECT FROM "users" GROUP BY "users"."id" } end it 'chains' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.group(table[:id]).must_equal manager end it 'takes multiple args' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from table manager.group table[:id], table[:name] manager.to_sql.must_be_like %{ SELECT FROM "users" GROUP BY "users"."id", "users"."name" } end # FIXME: backwards compat it 'makes strings literals' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from table manager.group 'foo' manager.to_sql.must_be_like %{ SELECT FROM "users" GROUP BY foo } end end describe 'delete' do it "copies from" do engine = EngineProxy.new Table.engine table = Table.new :users manager = Arel::SelectManager.new engine manager.from table stmt = manager.compile_delete stmt.to_sql.must_be_like %{ DELETE FROM "users" } end it "copies where" do engine = EngineProxy.new Table.engine table = Table.new :users manager = Arel::SelectManager.new engine manager.from table manager.where table[:id].eq 10 stmt = manager.compile_delete stmt.to_sql.must_be_like %{ DELETE FROM "users" WHERE "users"."id" = 10 } end end describe 'where_sql' do it 'gives me back the where sql' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from table manager.where table[:id].eq 10 manager.where_sql.must_be_like %{ WHERE "users"."id" = 10 } end it 'returns nil when there are no wheres' do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from table manager.where_sql.must_be_nil end end describe 'update' do it 'copies limits' do engine = EngineProxy.new Table.engine table = Table.new :users manager = Arel::SelectManager.new engine manager.from table manager.take 1 stmt = manager.compile_update(SqlLiteral.new('foo = bar')) stmt.key = table['id'] stmt.to_sql.must_be_like %{ UPDATE "users" SET foo = bar WHERE "users"."id" IN (SELECT "users"."id" FROM "users" LIMIT 1) } end it 'copies order' do engine = EngineProxy.new Table.engine table = Table.new :users manager = Arel::SelectManager.new engine manager.from table manager.order :foo stmt = manager.compile_update(SqlLiteral.new('foo = bar')) stmt.key = table['id'] stmt.to_sql.must_be_like %{ UPDATE "users" SET foo = bar WHERE "users"."id" IN (SELECT "users"."id" FROM "users" ORDER BY foo) } end it 'takes a string' do engine = EngineProxy.new Table.engine table = Table.new :users manager = Arel::SelectManager.new engine manager.from table stmt = manager.compile_update(SqlLiteral.new('foo = bar')) stmt.to_sql.must_be_like %{ UPDATE "users" SET foo = bar } end it 'copies where clauses' do engine = EngineProxy.new Table.engine table = Table.new :users manager = Arel::SelectManager.new engine manager.where table[:id].eq 10 manager.from table stmt = manager.compile_update(table[:id] => 1) stmt.to_sql.must_be_like %{ UPDATE "users" SET "id" = 1 WHERE "users"."id" = 10 } end it 'copies where clauses when nesting is triggered' do engine = EngineProxy.new Table.engine table = Table.new :users manager = Arel::SelectManager.new engine manager.where table[:foo].eq 10 manager.take 42 manager.from table stmt = manager.compile_update(table[:id] => 1) stmt.to_sql.must_be_like %{ UPDATE "users" SET "id" = 1 WHERE "users"."id" IN (SELECT "users"."id" FROM "users" WHERE "users"."foo" = 10 LIMIT 42) } end it 'executes an update statement' do engine = EngineProxy.new Table.engine table = Table.new :users manager = Arel::SelectManager.new engine manager.from table stmt = manager.compile_update(table[:id] => 1) stmt.to_sql.must_be_like %{ UPDATE "users" SET "id" = 1 } end end describe 'project' do it 'takes multiple args' do manager = Arel::SelectManager.new Table.engine manager.project Nodes::SqlLiteral.new('foo'), Nodes::SqlLiteral.new('bar') manager.to_sql.must_be_like %{ SELECT foo, bar } end it 'takes strings' do manager = Arel::SelectManager.new Table.engine manager.project Nodes::SqlLiteral.new('*') manager.to_sql.must_be_like %{ SELECT * } end it "takes sql literals" do manager = Arel::SelectManager.new Table.engine manager.project Nodes::SqlLiteral.new '*' manager.to_sql.must_be_like %{ SELECT * } end end describe 'take' do it "knows take" do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from(table).project(table['id']) manager.where(table['id'].eq(1)) manager.take 1 manager.to_sql.must_be_like %{ SELECT "users"."id" FROM "users" WHERE "users"."id" = 1 LIMIT 1 } end it "chains" do manager = Arel::SelectManager.new Table.engine manager.take(1).must_equal manager end it 'removes LIMIT when nil is passed' do manager = Arel::SelectManager.new Table.engine manager.limit = 10 assert_match('LIMIT', manager.to_sql) manager.limit = nil refute_match('LIMIT', manager.to_sql) end end describe 'where' do it "knows where" do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from(table).project(table['id']) manager.where(table['id'].eq(1)) manager.to_sql.must_be_like %{ SELECT "users"."id" FROM "users" WHERE "users"."id" = 1 } end it "chains" do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from(table) manager.project(table['id']).where(table['id'].eq 1).must_equal manager end end describe "join" do it "joins itself" do left = Table.new :users right = left.alias predicate = left[:id].eq(right[:id]) mgr = left.join(right) mgr.project Nodes::SqlLiteral.new('*') mgr.on(predicate).must_equal mgr mgr.to_sql.must_be_like %{ SELECT * FROM "users" INNER JOIN "users" "users_2" ON "users"."id" = "users_2"."id" } end end describe 'from' do it "makes sql" do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from table manager.project table['id'] manager.to_sql.must_be_like 'SELECT "users"."id" FROM "users"' end it "chains" do table = Table.new :users manager = Arel::SelectManager.new Table.engine manager.from(table).project(table['id']).must_equal manager manager.to_sql.must_be_like 'SELECT "users"."id" FROM "users"' end end end end