require "spec_helper" describe DataMapper::Adapters::DataObjectsAdapter do context "querying by SQL" do before(:each) do @bob = User.create(:username => "Bob", :role => "Manager") @fred = User.create(:username => "Fred", :role => "Tea Boy") end context "with a basic SELECT statement" do before(:each) do @users = User.by_sql { |u| ["SELECT #{u.*} FROM #{u} WHERE #{u.role} = ?", "Manager"] } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "executes the original query" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" WHERE "users"."role" = ? ORDER BY "users"."id"} @bind_values.should == ["Manager"] end it "finds the matching resources" do @users.should include(@bob) end it "does not find incorrect resources" do @users.should_not include(@fred) end describe "chaining" do describe "to #all" do before(:each) do @jim = User.create(:username => "Jim", :role => "Manager") @users = @users.all(:username => "Jim") @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "merges the conditions with the original SQL" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" WHERE "users"."role" = ? AND "users"."username" = ? ORDER BY "users"."id"} @bind_values.should == ["Manager", "Jim"] end it "finds the matching resources" do @users.should include(@jim) end it "does not find incorrect resources" do @users.should_not include(@bob) end end end end describe "ordering" do context "with an ORDER BY clause" do before(:each) do @users = User.by_sql { |u| "SELECT #{u.*} FROM #{u} ORDER BY #{u.username} DESC" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "uses the order from the SQL" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."username" DESC} end it "loads the resources in the correct order" do @users.to_a.first.should == @fred @users.to_a.last.should == @bob end end context "with :order specified on the query" do before(:each) do @users = User.by_sql(:order => [:role.asc]) { |u| "SELECT #{u.*} FROM #{u}" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "uses the order from the options" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."role"} end it "loads the resources in the correct order" do @users.to_a.first.should == @bob @users.to_a.last.should == @fred end end context "with both :order and an ORDER BY clause" do before(:each) do @users = User.by_sql(:order => [:role.desc]) { |u| "SELECT #{u.*} FROM #{u} ORDER BY #{u.username} ASC" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "gives the :order option precendence" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."role" DESC} end end describe "chaining" do describe "overriding a previous :order option" do before(:each) do @users = User.by_sql(:order => [:role.desc]) { |u| "SELECT #{u.*} FROM #{u}" }.all(:order => [:id.asc]) @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end specify "the last :order specified is used" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id"} end end describe "overriding the order specified in the SQL" do before(:each) do @users = User.by_sql { |u| "SELECT #{u.*} FROM #{u} ORDER BY #{u.role} DESC" }.all(:order => [:id.asc]) @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end specify "the last :order specified is used" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id"} end end end end describe "limits" do context "with a limit specified by the SQL" do before(:each) do @users = User.by_sql { |u| "SELECT #{u.*} FROM #{u} LIMIT 1" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "uses the limit from the SQL" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id" LIMIT ?} @bind_values.should == [1] end it "finds the matching resources" do @users.to_a.should have(1).items @users.to_a.first.should == @bob end end context "with a :limit option to #by_sql" do before(:each) do @users = User.by_sql(:limit => 1) { |u| "SELECT #{u.*} FROM #{u}" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "uses the :limit option" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id" LIMIT ?} @bind_values.should == [1] end it "finds the matching resources" do @users.to_a.should have(1).items @users.to_a.first.should == @bob end end context "with both a :limit option and a LIMIT in the SQL" do before(:each) do @users = User.by_sql(:limit => 1) { |u| "SELECT #{u.*} FROM #{u} LIMIT 2" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "the :limit option takes precedence" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id" LIMIT ?} @bind_values.should == [1] end end context "with an OFFSET in the SQL" do before(:each) do @users = User.by_sql { |u| "SELECT #{u.*} FROM #{u} LIMIT 1 OFFSET 1" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "uses the offset from the SQL" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id" LIMIT ? OFFSET ?} @bind_values.should == [1, 1] end it "finds the matching resources" do @users.to_a.should have(1).items @users.to_a.first.should == @fred end end context "with an argument to LIMIT in the SQL" do before(:each) do @users = User.by_sql { |u| "SELECT #{u.*} FROM #{u} LIMIT 1, 2" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "interprets the offset in the SQL" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id" LIMIT ? OFFSET ?} @bind_values.should == [2, 1] end end context "with an :offset option to #by_sql" do before(:each) do @users = User.by_sql(:offset => 1) { |u| "SELECT #{u.*} FROM #{u} LIMIT 1" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "uses the offset from the options hash" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id" LIMIT ? OFFSET ?} @bind_values.should == [1, 1] end it "finds the matching resources" do @users.to_a.should have(1).items @users.to_a.first.should == @fred end end context "with both an OFFSET in the SQL and an :offset option" do before(:each) do @users = User.by_sql(:offset => 1) { |u| "SELECT #{u.*} FROM #{u} LIMIT 1 OFFSET 0" } @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "the :offset in the options takes precendence" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id" LIMIT ? OFFSET ?} @bind_values.should == [1, 1] end end describe "chaining" do describe "to override a previous :limit option" do before(:each) do @users = User.by_sql(:limit => 2) { |u| "SELECT #{u.*} FROM #{u}" }.all(:limit => 1) @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "the last :limit option takes precedence" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id" LIMIT ?} @bind_values.should == [1] end end describe "to override a limit applied in SQL" do before(:each) do @users = User.by_sql { |u| "SELECT #{u.*} FROM #{u} LIMIT 1" }.all(:limit => 2) @sql, @bind_values = User.repository.adapter.send(:select_statement, @users.query) end it "the last :limit option takes precedence" do @sql.should == %q{SELECT "users"."id", "users"."username", "users"."role" FROM "users" ORDER BY "users"."id" LIMIT ?} @bind_values.should == [2] end end end end context "with an INNER JOIN" do before(:each) do @bobs_post = @bob.posts.create(:title => "Bob can write posts") @freds_post = @fred.posts.create(:title => "Fred likes to write too") @posts = Post.by_sql(User) { |p, u| ["SELECT #{p.*} FROM #{p} INNER JOIN #{u} ON #{p.user_id} = #{u.id} WHERE #{u.id} = ?", @bob.id] } @sql, @bind_values = Post.repository.adapter.send(:select_statement, @posts.query) end it "executes the original query" do @sql.should == %q{SELECT "posts"."id", "posts"."title", "posts"."user_id" FROM "posts" INNER JOIN "users" ON "posts"."user_id" = "users"."id" WHERE "users"."id" = ? ORDER BY "posts"."id"} @bind_values.should == [@bob.id] end it "finds the matching resources" do @posts.should include(@bobs_post) end it "does not find incorrect resources" do @posts.should_not include(@freds_post) end context "to the same table" do before(:each) do @posts = Post.by_sql(Post) { |p1, p2| "SELECT #{p1.*} FROM #{p1} INNER JOIN #{p2} ON #{p1.id} = #{p2.id}" } @sql, @bind_values = Post.repository.adapter.send(:select_statement, @posts.query) end it "creates alises for the subsequent tables" do @sql.should == %q{SELECT "posts"."id", "posts"."title", "posts"."user_id" FROM "posts" INNER JOIN "posts" AS "posts_1" ON "posts"."id" = "posts_1"."id" ORDER BY "posts"."id"} end end end describe "aggregate queries" do it "supports calculating counts" do User.by_sql { |u| "SELECT #{u.*} FROM #{u}" }.count(:id).should == 2 end it "supports multiple aggregates" do User.by_sql { |u| "SELECT #{u.*} FROM #{u}" }.aggregate(:id.count, :id.min, :username.max).should == [2, 1, "Fred"] end end describe "with virtual attributes" do before(:each) do @bob.posts.create(:title => "Test") @users = User.by_sql(Post) { |u, p| "SELECT #{u.*}, COUNT(#{p.id}) AS #{u.post_count} FROM #{u} INNER JOIN #{p} ON #{p.user_id} = #{u.id}" } end it "loads the virtual attributes" do @users.to_a.first.post_count.should == 1 end end end end