diff options
Diffstat (limited to 'spec/engines')
36 files changed, 3083 insertions, 0 deletions
diff --git a/spec/engines/memory/integration/joins/cross_engine_spec.rb b/spec/engines/memory/integration/joins/cross_engine_spec.rb new file mode 100644 index 0000000000..606f3154c7 --- /dev/null +++ b/spec/engines/memory/integration/joins/cross_engine_spec.rb @@ -0,0 +1,52 @@ +require 'spec_helper' + +module Arel + describe Join do + before do + @users = Array.new([ + [1, 'bryan' ], + [2, 'emilio' ], + [3, 'nick'] + ], [[:id, Attributes::Integer], [:name, Attributes::String]]) + @photos = Table.new(:photos) + @photos.delete + @photos.insert(@photos[:id] => 1, @photos[:user_id] => 1, @photos[:camera_id] => 6) + @photos.insert(@photos[:id] => 2, @photos[:user_id] => 2, @photos[:camera_id] => 42) + # Oracle adapter returns database integers as Ruby integers and not strings + @adapter_returns_integer = false + adapter_is :oracle do + @adapter_returns_integer = true + end + end + + describe 'when the in memory relation is on the left' do + it 'joins across engines' do + @users \ + .join(@photos) \ + .on(@users[:id].eq(@photos[:user_id])) \ + .project(@users[:name], @photos[:camera_id]) \ + .let do |relation| + relation.call.should == [ + Row.new(relation, ['bryan', @adapter_returns_integer ? 6 : '6']), + Row.new(relation, ['emilio', @adapter_returns_integer ? 42 : '42']) + ] + end + end + end + + describe 'when the in memory relation is on the right' do + it 'joins across engines' do + @photos \ + .join(@users) \ + .on(@users[:id].eq(@photos[:user_id])) \ + .project(@users[:name], @photos[:camera_id]) \ + .let do |relation| + relation.call.should == [ + Row.new(relation, ['bryan', @adapter_returns_integer ? 6 : '6']), + Row.new(relation, ['emilio', @adapter_returns_integer ? 42 : '42']) + ] + end + end + end + end +end diff --git a/spec/engines/memory/unit/relations/array_spec.rb b/spec/engines/memory/unit/relations/array_spec.rb new file mode 100644 index 0000000000..dcec2afa19 --- /dev/null +++ b/spec/engines/memory/unit/relations/array_spec.rb @@ -0,0 +1,32 @@ +require 'spec_helper' + +module Arel + describe Array do + before do + @relation = Array.new([ + [1, 'duck' ], + [2, 'duck' ], + [3, 'goose'] + ], [[:id, Attributes::Integer], [:name, Attributes::String]]) + end + + describe '#attributes' do + it 'manufactures attributes corresponding to the names given on construction' do + @relation.attributes.should == [ + Attribute.new(@relation, :id), + Attribute.new(@relation, :name) + ] + end + end + + describe '#call' do + it "manufactures an array of hashes of attributes to values" do + @relation.call.should == [ + Row.new(@relation, [1, 'duck']), + Row.new(@relation, [2, 'duck']), + Row.new(@relation, [3, 'goose']) + ] + end + end + end +end diff --git a/spec/engines/memory/unit/relations/insert_spec.rb b/spec/engines/memory/unit/relations/insert_spec.rb new file mode 100644 index 0000000000..987e708e0b --- /dev/null +++ b/spec/engines/memory/unit/relations/insert_spec.rb @@ -0,0 +1,28 @@ +require 'spec_helper' + +module Arel + describe Insert do + before do + @relation = Array.new([ + [1, 'duck' ], + [2, 'duck' ], + [3, 'goose'] + ], [[:id, Attributes::Integer], [:name, Attributes::String]]) + end + + describe '#call' do + it "manufactures an array of hashes of attributes to values" do + @relation \ + .insert(@relation[:id] => 4, @relation[:name] => 'guinea fowl') \ + do |relation| + relation.should == [ + Row.new(relation, [1, 'duck']), + Row.new(relation, [2, 'duck']), + Row.new(relation, [3, 'goose']), + Row.new(relation, [4, 'guinea fowl']) + ] + end + end + end + end +end diff --git a/spec/engines/memory/unit/relations/join_spec.rb b/spec/engines/memory/unit/relations/join_spec.rb new file mode 100644 index 0000000000..ed5fe89ef0 --- /dev/null +++ b/spec/engines/memory/unit/relations/join_spec.rb @@ -0,0 +1,31 @@ +require 'spec_helper' + +module Arel + describe Join do + before do + @relation1 = Array.new([ + [1, 'duck' ], + [2, 'duck' ], + [3, 'goose'] + ], [[:id, Attributes::Integer], [:name, Attributes::String]]) + @relation2 = @relation1.alias + end + + describe InnerJoin do + describe '#call' do + it 'combines the two tables where the predicate obtains' do + @relation1 \ + .join(@relation2) \ + .on(@relation1[:id].eq(@relation2[:id])) \ + .let do |relation| + relation.call.should == [ + Row.new(relation, [1, 'duck', 1, 'duck' ]), + Row.new(relation, [2, 'duck', 2, 'duck' ]), + Row.new(relation, [3, 'goose', 3, 'goose']) + ] + end + end + end + end + end +end diff --git a/spec/engines/memory/unit/relations/order_spec.rb b/spec/engines/memory/unit/relations/order_spec.rb new file mode 100644 index 0000000000..9546449bfb --- /dev/null +++ b/spec/engines/memory/unit/relations/order_spec.rb @@ -0,0 +1,27 @@ +require 'spec_helper' + +module Arel + describe Order do + before do + @relation = Array.new([ + [1, 'duck' ], + [2, 'duck' ], + [3, 'goose'] + ], [[:id, Attributes::Integer], [:name, Attributes::String]]) + end + + describe '#call' do + it 'sorts the relation with the provided ordering' do + @relation \ + .order(@relation[:id].desc) \ + .let do |relation| + relation.call.should == [ + Row.new(relation, [3, 'goose']), + Row.new(relation, [2, 'duck' ]), + Row.new(relation, [1, 'duck' ]) + ] + end + end + end + end +end diff --git a/spec/engines/memory/unit/relations/project_spec.rb b/spec/engines/memory/unit/relations/project_spec.rb new file mode 100644 index 0000000000..92ed9fa74b --- /dev/null +++ b/spec/engines/memory/unit/relations/project_spec.rb @@ -0,0 +1,27 @@ +require 'spec_helper' + +module Arel + describe Project do + before do + @relation = Array.new([ + [1, 'duck' ], + [2, 'duck' ], + [3, 'goose'] + ], [[:id, Attributes::Integer], [:name, Attributes::String]]) + end + + describe '#call' do + it 'retains only the attributes that are provided' do + @relation \ + .project(@relation[:id]) \ + .let do |relation| + relation.call.should == [ + Row.new(relation, [1]), + Row.new(relation, [2]), + Row.new(relation, [3]) + ] + end + end + end + end +end diff --git a/spec/engines/memory/unit/relations/skip_spec.rb b/spec/engines/memory/unit/relations/skip_spec.rb new file mode 100644 index 0000000000..089db24cea --- /dev/null +++ b/spec/engines/memory/unit/relations/skip_spec.rb @@ -0,0 +1,26 @@ +require 'spec_helper' + +module Arel + describe Skip do + before do + @relation = Array.new([ + [1, 'duck' ], + [2, 'duck' ], + [3, 'goose'] + ], [[:id, Attributes::Integer], [:name, Attributes::String]]) + end + + describe '#call' do + it 'removes the first n rows' do + @relation \ + .skip(1) \ + .let do |relation| + relation.call.should == [ + Row.new(relation, [2, 'duck']), + Row.new(relation, [3, 'goose']), + ] + end + end + end + end +end diff --git a/spec/engines/memory/unit/relations/take_spec.rb b/spec/engines/memory/unit/relations/take_spec.rb new file mode 100644 index 0000000000..16b99872c5 --- /dev/null +++ b/spec/engines/memory/unit/relations/take_spec.rb @@ -0,0 +1,26 @@ +require 'spec_helper' + +module Arel + describe Take do + before do + @relation = Array.new([ + [1, 'duck' ], + [2, 'duck' ], + [3, 'goose'] + ], [[:id, Attributes::Integer], [:name, Attributes::String]]) + end + + describe '#call' do + it 'removes the rows after the first n' do + @relation \ + .take(2) \ + .let do |relation| + relation.call.should == [ + Row.new(relation, [1, 'duck']), + Row.new(relation, [2, 'duck']), + ] + end + end + end + end +end diff --git a/spec/engines/memory/unit/relations/where_spec.rb b/spec/engines/memory/unit/relations/where_spec.rb new file mode 100644 index 0000000000..b45c009d83 --- /dev/null +++ b/spec/engines/memory/unit/relations/where_spec.rb @@ -0,0 +1,39 @@ +require 'spec_helper' + +module Arel + describe Where do + before do + @relation = Array.new([ + [1, 'duck' ], + [2, 'duck' ], + [3, 'goose'] + ], [[:id, Attributes::Integer], [:name, Attributes::String]]) + end + + describe '#call' do + it 'filters the relation with the provided predicate' do + @relation \ + .where(@relation[:id].lt(3)) \ + .let do |relation| + relation.call.should == [ + Row.new(relation, [1, 'duck']), + Row.new(relation, [2, 'duck']), + ] + end + end + + describe 'when filtering a where relation' do + it 'further filters the already-filtered relation with the provided predicate' do + @relation \ + .where(@relation[:id].gt(1)) \ + .where(@relation[:id].lt(3)) \ + .let do |relation| + relation.call.should == [ + Row.new(relation, [2, 'duck']) + ] + end + end + end + end + end +end diff --git a/spec/engines/sql/integration/joins/with_adjacency_spec.rb b/spec/engines/sql/integration/joins/with_adjacency_spec.rb new file mode 100644 index 0000000000..1a0e28838d --- /dev/null +++ b/spec/engines/sql/integration/joins/with_adjacency_spec.rb @@ -0,0 +1,258 @@ +require 'spec_helper' + +module Arel + describe Join do + before do + @relation1 = Table(:users) + @relation2 = @relation1.alias + @predicate = @relation1[:id].eq(@relation2[:id]) + end + + describe 'when joining a relation to itself' do + describe '#to_sql' do + it 'manufactures sql aliasing the table and attributes properly in the join predicate and the where clause' do + sql = @relation1.join(@relation2).on(@predicate).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `users_2`.`id`, `users_2`.`name` + FROM `users` + INNER JOIN `users` `users_2` + ON `users`.`id` = `users_2`.`id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME" + FROM "USERS" + INNER JOIN "USERS" "USERS_2" + ON "USERS"."ID" = "USERS_2"."ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name" + FROM "users" + INNER JOIN "users" "users_2" + ON "users"."id" = "users_2"."id" + }) + end + end + + describe 'when joining with a where on the same relation' do + it 'manufactures sql aliasing the tables properly' do + sql = @relation1 \ + .join(@relation2.where(@relation2[:id].eq(1))) \ + .on(@predicate) \ + .to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `users_2`.`id`, `users_2`.`name` + FROM `users` + INNER JOIN `users` `users_2` + ON `users`.`id` = `users_2`.`id` AND `users_2`.`id` = 1 + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME" + FROM "USERS" + INNER JOIN "USERS" "USERS_2" + ON "USERS"."ID" = "USERS_2"."ID" AND "USERS_2"."ID" = 1 + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name" + FROM "users" + INNER JOIN "users" "users_2" + ON "users"."id" = "users_2"."id" AND "users_2"."id" = 1 + }) + end + end + + describe 'when the where occurs before the alias' do + it 'manufactures sql aliasing the predicates properly' do + relation2 = @relation1.where(@relation1[:id].eq(1)).alias + + sql = @relation1 \ + .join(relation2) \ + .on(relation2[:id].eq(@relation1[:id])) \ + .to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `users_2`.`id`, `users_2`.`name` + FROM `users` + INNER JOIN `users` `users_2` + ON `users_2`.`id` = `users`.`id` AND `users_2`.`id` = 1 + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME" + FROM "USERS" + INNER JOIN "USERS" "USERS_2" + ON "USERS_2"."ID" = "USERS"."ID" AND "USERS_2"."ID" = 1 + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name" + FROM "users" + INNER JOIN "users" "users_2" + ON "users_2"."id" = "users"."id" AND "users_2"."id" = 1 + }) + end + end + end + end + + describe 'when joining the relation to itself multiple times' do + before do + @relation3 = @relation1.alias + end + + describe 'when joining left-associatively' do + it 'manufactures sql aliasing the tables properly' do + sql = @relation1 \ + .join(@relation2 \ + .join(@relation3) \ + .on(@relation2[:id].eq(@relation3[:id]))) \ + .on(@relation1[:id].eq(@relation2[:id])) \ + .to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `users_2`.`id`, `users_2`.`name`, `users_3`.`id`, `users_3`.`name` + FROM `users` + INNER JOIN `users` `users_2` + ON `users`.`id` = `users_2`.`id` + INNER JOIN `users` `users_3` + ON `users_2`.`id` = `users_3`.`id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME", "USERS_3"."ID", "USERS_3"."NAME" + FROM "USERS" + INNER JOIN "USERS" "USERS_2" + ON "USERS"."ID" = "USERS_2"."ID" + INNER JOIN "USERS" "USERS_3" + ON "USERS_2"."ID" = "USERS_3"."ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name", "users_3"."id", "users_3"."name" + FROM "users" + INNER JOIN "users" "users_2" + ON "users"."id" = "users_2"."id" + INNER JOIN "users" "users_3" + ON "users_2"."id" = "users_3"."id" + }) + end + end + end + + describe 'when joining right-associatively' do + it 'manufactures sql aliasing the tables properly' do + sql = @relation1 \ + .join(@relation2).on(@relation1[:id].eq(@relation2[:id])) \ + .join(@relation3).on(@relation2[:id].eq(@relation3[:id])) \ + .to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `users_2`.`id`, `users_2`.`name`, `users_3`.`id`, `users_3`.`name` + FROM `users` + INNER JOIN `users` `users_2` + ON `users`.`id` = `users_2`.`id` + INNER JOIN `users` `users_3` + ON `users_2`.`id` = `users_3`.`id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME", "USERS_3"."ID", "USERS_3"."NAME" + FROM "USERS" + INNER JOIN "USERS" "USERS_2" + ON "USERS"."ID" = "USERS_2"."ID" + INNER JOIN "USERS" "USERS_3" + ON "USERS_2"."ID" = "USERS_3"."ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name", "users_3"."id", "users_3"."name" + FROM "users" + INNER JOIN "users" "users_2" + ON "users"."id" = "users_2"."id" + INNER JOIN "users" "users_3" + ON "users_2"."id" = "users_3"."id" + }) + end + end + end + end + end + + describe '[]' do + describe 'when given an attribute belonging to both sub-relations' do + it 'disambiguates the relation that serves as the ancestor to the attribute' do + @relation1 \ + .join(@relation2) \ + .on(@predicate) \ + .should disambiguate_attributes(@relation1[:id], @relation2[:id]) + end + + describe 'when both relations are compound and only one is an alias' do + it 'disambiguates the relation that serves as the ancestor to the attribute' do + compound1 = @relation1.where(@predicate) + compound2 = compound1.alias + compound1 \ + .join(compound2) \ + .on(@predicate) \ + .should disambiguate_attributes(compound1[:id], compound2[:id]) + end + end + + describe 'when the left relation is extremely compound' do + it 'disambiguates the relation that serves as the ancestor to the attribute' do + @relation1 \ + .where(@predicate) \ + .where(@predicate) \ + .join(@relation2) \ + .on(@predicate) \ + .should disambiguate_attributes(@relation1[:id], @relation2[:id]) + end + end + + describe 'when the right relation is extremely compound' do + it 'disambiguates the relation that serves as the ancestor to the attribute' do + @relation1 \ + .join( \ + @relation2 \ + .where(@predicate) \ + .where(@predicate) \ + .where(@predicate)) \ + .on(@predicate) \ + .should disambiguate_attributes(@relation1[:id], @relation2[:id]) + end + end + end + end + end + end +end diff --git a/spec/engines/sql/integration/joins/with_aggregations_spec.rb b/spec/engines/sql/integration/joins/with_aggregations_spec.rb new file mode 100644 index 0000000000..62e1b8baea --- /dev/null +++ b/spec/engines/sql/integration/joins/with_aggregations_spec.rb @@ -0,0 +1,221 @@ +require 'spec_helper' + +module Arel + describe Join do + before do + @relation1 = Table(:users) + @relation2 = Table(:photos) + @predicate = @relation1[:id].eq(@relation2[:user_id]) + end + + describe 'when joining aggregated relations' do + before do + @aggregation = @relation2 \ + .group(@relation2[:user_id]) \ + .project(@relation2[:user_id], @relation2[:id].count.as(:cnt)) \ + end + + describe '#to_sql' do + # CLEANUP + it '' do + sql = @relation1.join(@relation2.take(3)).on(@predicate).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `photos_external`.`id`, `photos_external`.`user_id`, `photos_external`.`camera_id` + FROM `users` + INNER JOIN (SELECT `photos`.`id`, `photos`.`user_id`, `photos`.`camera_id` FROM `photos` LIMIT 3) `photos_external` + ON `users`.`id` = `photos_external`.`user_id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS_EXTERNAL"."ID", "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CAMERA_ID" + FROM "USERS" + INNER JOIN (SELECT "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID" FROM "PHOTOS" WHERE ROWNUM <= 3) "PHOTOS_EXTERNAL" + ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "photos_external"."id", "photos_external"."user_id", "photos_external"."camera_id" + FROM "users" + INNER JOIN (SELECT "photos"."id", "photos"."user_id", "photos"."camera_id" FROM "photos" LIMIT 3) "photos_external" + ON "users"."id" = "photos_external"."user_id" + }) + end + end + + describe 'with the aggregation on the right' do + it 'manufactures sql joining the left table to a derived table' do + sql = @relation1.join(@aggregation).on(@predicate).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `photos_external`.`user_id`, `photos_external`.`cnt` + FROM `users` + INNER JOIN (SELECT `photos`.`user_id`, COUNT(`photos`.`id`) AS `cnt` FROM `photos` GROUP BY `photos`.`user_id`) `photos_external` + ON `users`.`id` = `photos_external`.`user_id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT" + FROM "USERS" + INNER JOIN (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL" + ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "photos_external"."user_id", "photos_external"."cnt" + FROM "users" + INNER JOIN (SELECT "photos"."user_id", COUNT("photos"."id") AS "cnt" FROM "photos" GROUP BY "photos"."user_id") "photos_external" + ON "users"."id" = "photos_external"."user_id" + }) + end + end + end + + describe 'with the aggregation on the left' do + it 'manufactures sql joining the right table to a derived table' do + sql = @aggregation.join(@relation1).on(@predicate).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `photos_external`.`user_id`, `photos_external`.`cnt`, `users`.`id`, `users`.`name` + FROM (SELECT `photos`.`user_id`, COUNT(`photos`.`id`) AS `cnt` FROM `photos` GROUP BY `photos`.`user_id`) `photos_external` + INNER JOIN `users` + ON `users`.`id` = `photos_external`.`user_id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT", "USERS"."ID", "USERS"."NAME" + FROM (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL" + INNER JOIN "USERS" + ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "photos_external"."user_id", "photos_external"."cnt", "users"."id", "users"."name" + FROM (SELECT "photos"."user_id", COUNT("photos"."id") AS "cnt" FROM "photos" GROUP BY "photos"."user_id") "photos_external" + INNER JOIN "users" + ON "users"."id" = "photos_external"."user_id" + }) + end + end + end + + describe 'with the aggregation on both sides' do + it 'it properly aliases the aggregations' do + aggregation2 = @aggregation.alias + sql = @aggregation.join(aggregation2).on(aggregation2[:user_id].eq(@aggregation[:user_id])).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `photos_external`.`user_id`, `photos_external`.`cnt`, `photos_external_2`.`user_id`, `photos_external_2`.`cnt` + FROM (SELECT `photos`.`user_id`, COUNT(`photos`.`id`) AS `cnt` FROM `photos` GROUP BY `photos`.`user_id`) `photos_external` + INNER JOIN (SELECT `photos`.`user_id`, COUNT(`photos`.`id`) AS `cnt` FROM `photos` GROUP BY `photos`.`user_id`) `photos_external_2` + ON `photos_external_2`.`user_id` = `photos_external`.`user_id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT", "PHOTOS_EXTERNAL_2"."USER_ID", "PHOTOS_EXTERNAL_2"."CNT" + FROM (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL" + INNER JOIN (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL_2" + ON "PHOTOS_EXTERNAL_2"."USER_ID" = "PHOTOS_EXTERNAL"."USER_ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "photos_external"."user_id", "photos_external"."cnt", "photos_external_2"."user_id", "photos_external_2"."cnt" + FROM (SELECT "photos"."user_id", COUNT("photos"."id") AS "cnt" FROM "photos" GROUP BY "photos"."user_id") "photos_external" + INNER JOIN (SELECT "photos"."user_id", COUNT("photos"."id") AS "cnt" FROM "photos" GROUP BY "photos"."user_id") "photos_external_2" + ON "photos_external_2"."user_id" = "photos_external"."user_id" + }) + end + end + end + + describe 'when the aggration has a where' do + describe 'with the aggregation on the left' do + it "manufactures sql keeping wheres on the aggregation within the derived table" do + sql = @relation1.join(@aggregation.where(@aggregation[:user_id].eq(1))).on(@predicate).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `photos_external`.`user_id`, `photos_external`.`cnt` + FROM `users` + INNER JOIN (SELECT `photos`.`user_id`, COUNT(`photos`.`id`) AS `cnt` FROM `photos` WHERE `photos`.`user_id` = 1 GROUP BY `photos`.`user_id`) `photos_external` + ON `users`.`id` = `photos_external`.`user_id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT" + FROM "USERS" + INNER JOIN (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" WHERE "PHOTOS"."USER_ID" = 1 GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL" + ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "photos_external"."user_id", "photos_external"."cnt" + FROM "users" + INNER JOIN (SELECT "photos"."user_id", COUNT("photos"."id") AS "cnt" FROM "photos" WHERE "photos"."user_id" = 1 GROUP BY "photos"."user_id") "photos_external" + ON "users"."id" = "photos_external"."user_id" + }) + end + end + end + + describe 'with the aggregation on the right' do + it "manufactures sql keeping wheres on the aggregation within the derived table" do + sql = @aggregation.where(@aggregation[:user_id].eq(1)).join(@relation1).on(@predicate).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `photos_external`.`user_id`, `photos_external`.`cnt`, `users`.`id`, `users`.`name` + FROM (SELECT `photos`.`user_id`, COUNT(`photos`.`id`) AS `cnt` FROM `photos` WHERE `photos`.`user_id` = 1 GROUP BY `photos`.`user_id`) `photos_external` + INNER JOIN `users` + ON `users`.`id` = `photos_external`.`user_id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT", "USERS"."ID", "USERS"."NAME" + FROM (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" WHERE "PHOTOS"."USER_ID" = 1 GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL" + INNER JOIN "USERS" + ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "photos_external"."user_id", "photos_external"."cnt", "users"."id", "users"."name" + FROM (SELECT "photos"."user_id", COUNT("photos"."id") AS "cnt" FROM "photos" WHERE "photos"."user_id" = 1 GROUP BY "photos"."user_id") "photos_external" + INNER JOIN "users" + ON "users"."id" = "photos_external"."user_id" + }) + end + end + end + end + end + end + end +end diff --git a/spec/engines/sql/integration/joins/with_compounds_spec.rb b/spec/engines/sql/integration/joins/with_compounds_spec.rb new file mode 100644 index 0000000000..65fe49d128 --- /dev/null +++ b/spec/engines/sql/integration/joins/with_compounds_spec.rb @@ -0,0 +1,137 @@ +require 'spec_helper' + +module Arel + describe Join do + before do + @relation1 = Table(:users) + @relation2 = Table(:photos) + @predicate = @relation1[:id].eq(@relation2[:user_id]) + end + + describe '#to_sql' do + describe 'when the join contains a where' do + describe 'and the where is given a string' do + it 'does not escape the string' do + sql = @relation1 \ + .join(@relation2.where("asdf")) \ + .on(@predicate) \ + .to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `photos`.`id`, `photos`.`user_id`, `photos`.`camera_id` + FROM `users` + INNER JOIN `photos` + ON `users`.`id` = `photos`.`user_id` AND asdf + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID" + FROM "USERS" + INNER JOIN "PHOTOS" + ON "USERS"."ID" = "PHOTOS"."USER_ID" AND asdf + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id" + FROM "users" + INNER JOIN "photos" + ON "users"."id" = "photos"."user_id" AND asdf + }) + end + end + end + end + + describe 'when a compound contains a join' do + describe 'and the compound is a where' do + it 'manufactures sql disambiguating the tables' do + sql = @relation1 \ + .where(@relation1[:id].eq(1)) \ + .join(@relation2) \ + .on(@predicate) \ + .where(@relation1[:id].eq(1)) \ + .to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `photos`.`id`, `photos`.`user_id`, `photos`.`camera_id` + FROM `users` + INNER JOIN `photos` + ON `users`.`id` = `photos`.`user_id` + WHERE `users`.`id` = 1 + AND `users`.`id` = 1 + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID" + FROM "USERS" + INNER JOIN "PHOTOS" + ON "USERS"."ID" = "PHOTOS"."USER_ID" + WHERE "USERS"."ID" = 1 + AND "USERS"."ID" = 1 + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id" + FROM "users" + INNER JOIN "photos" + ON "users"."id" = "photos"."user_id" + WHERE "users"."id" = 1 + AND "users"."id" = 1 + }) + end + end + end + + describe 'and the compound is a group' do + it 'manufactures sql disambiguating the tables' do + sql = @relation1 \ + .join(@relation2) \ + .on(@predicate) \ + .group(@relation1[:id]) \ + .to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `photos`.`id`, `photos`.`user_id`, `photos`.`camera_id` + FROM `users` + INNER JOIN `photos` + ON `users`.`id` = `photos`.`user_id` + GROUP BY `users`.`id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID" + FROM "USERS" + INNER JOIN "PHOTOS" + ON "USERS"."ID" = "PHOTOS"."USER_ID" + GROUP BY "USERS"."ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id" + FROM "users" + INNER JOIN "photos" + ON "users"."id" = "photos"."user_id" + GROUP BY "users"."id" + }) + end + end + end + end + end + end +end diff --git a/spec/engines/sql/unit/engine_spec.rb b/spec/engines/sql/unit/engine_spec.rb new file mode 100644 index 0000000000..f782f56938 --- /dev/null +++ b/spec/engines/sql/unit/engine_spec.rb @@ -0,0 +1,45 @@ +require 'spec_helper' + +module Arel + describe Sql::Engine do + before do + @users = Table.new(:users) + @users.delete + end + + describe "CRUD" do + describe "#create" do + it "inserts into the relation" do + @users.insert @users[:name] => "Bryan" + @users.first[@users[:name]].should == "Bryan" + end + end + + describe "#read" do + it "reads from the relation" do + @users.insert @users[:name] => "Bryan" + + @users.each do |row| + row[@users[:name]].should == "Bryan" + end + end + end + + describe "#update" do + it "updates the relation" do + @users.insert @users[:name] => "Nick" + @users.update @users[:name] => "Bryan" + @users.first[@users[:name]].should == "Bryan" + end + end + + describe "#delete" do + it "deletes from the relation" do + @users.insert @users[:name] => "Bryan" + @users.delete + @users.first.should == nil + end + end + end + end +end diff --git a/spec/engines/sql/unit/predicates/binary_spec.rb b/spec/engines/sql/unit/predicates/binary_spec.rb new file mode 100644 index 0000000000..72c8e44888 --- /dev/null +++ b/spec/engines/sql/unit/predicates/binary_spec.rb @@ -0,0 +1,140 @@ +require 'spec_helper' + +module Arel + module Predicates + describe Binary do + class ConcreteBinary < Binary + def predicate_sql + "<=>" + end + end + + before do + @relation = Arel::Table.new(:users) + @attribute1 = @relation[:id] + @attribute2 = @relation[:name] + end + + describe "with compound predicates" do + before do + @operand1 = ConcreteBinary.new(@attribute1, 1) + @operand2 = ConcreteBinary.new(@attribute2, "name") + end + + describe Or do + describe "#to_sql" do + it "manufactures sql with an OR operation" do + sql = Or.new(@operand1, @operand2).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{(`users`.`id` <=> 1 OR `users`.`name` <=> 'name')}) + end + + adapter_is :postgresql do + sql.should be_like(%Q{("users"."id" <=> 1 OR "users"."name" <=> E'name')}) + end + + adapter_is :sqlite3 do + sql.should be_like(%Q{("users"."id" <=> 1 OR "users"."name" <=> 'name')}) + end + + adapter_is :oracle do + sql.should be_like(%Q{("USERS"."ID" <=> 1 OR "USERS"."NAME" <=> 'name')}) + end + end + end + end + + describe And do + describe "#to_sql" do + it "manufactures sql with an AND operation" do + sql = And.new(@operand1, @operand2).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{(`users`.`id` <=> 1 AND `users`.`name` <=> 'name')}) + end + + adapter_is :sqlite3 do + sql.should be_like(%Q{("users"."id" <=> 1 AND "users"."name" <=> 'name')}) + end + + adapter_is :postgresql do + sql.should be_like(%Q{("users"."id" <=> 1 AND "users"."name" <=> E'name')}) + end + + adapter_is :oracle do + sql.should be_like(%Q{("USERS"."ID" <=> 1 AND "USERS"."NAME" <=> 'name')}) + end + end + end + end + end + + describe '#to_sql' do + describe 'when relating two attributes' do + it 'manufactures sql with a binary operation' do + sql = ConcreteBinary.new(@attribute1, @attribute2).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id` <=> `users`.`name`}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID" <=> "USERS"."NAME"}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id" <=> "users"."name"}) + end + end + end + + describe 'when relating an attribute and a value' do + before do + @value = "1-asdf" + end + + describe 'when relating to an integer attribute' do + it 'formats values as integers' do + sql = ConcreteBinary.new(@attribute1, @value).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id` <=> 1}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID" <=> 1}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id" <=> 1}) + end + end + end + + describe 'when relating to a string attribute' do + it 'formats values as strings' do + sql = ConcreteBinary.new(@attribute2, @value).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`name` <=> '1-asdf'}) + end + + adapter_is :sqlite3 do + sql.should be_like(%Q{"users"."name" <=> '1-asdf'}) + end + + adapter_is :postgresql do + sql.should be_like(%Q{"users"."name" <=> E'1-asdf'}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."NAME" <=> '1-asdf'}) + end + end + end + end + end + end + end +end diff --git a/spec/engines/sql/unit/predicates/equality_spec.rb b/spec/engines/sql/unit/predicates/equality_spec.rb new file mode 100644 index 0000000000..bfd61185f2 --- /dev/null +++ b/spec/engines/sql/unit/predicates/equality_spec.rb @@ -0,0 +1,75 @@ +require 'spec_helper' + +module Arel + module Predicates + describe Equality do + before do + @relation1 = Arel::Table.new(:users) + @relation2 = Arel::Table.new(:photos) + @attribute1 = @relation1[:id] + @attribute2 = @relation2[:user_id] + end + + describe '#to_sql' do + describe 'when relating to a non-nil value' do + it "manufactures an equality predicate" do + sql = Equality.new(@attribute1, @attribute2).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id` = `photos`.`user_id`}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID" = "PHOTOS"."USER_ID"}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id" = "photos"."user_id"}) + end + end + end + + describe 'when relation to a nil value' do + before do + @nil = nil + end + + it "manufactures an is null predicate" do + sql = Equality.new(@attribute1, @nil).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id` IS NULL}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID" IS NULL}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id" IS NULL}) + end + end + end + + describe "when relating to a nil Value" do + it "manufactures an IS NULL predicate" do + value = nil.bind(@relation1) + sql = Equality.new(@attribute1, value).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id` IS NULL}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID" IS NULL}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id" IS NULL}) + end + end + end + end + end + end +end diff --git a/spec/engines/sql/unit/predicates/in_spec.rb b/spec/engines/sql/unit/predicates/in_spec.rb new file mode 100644 index 0000000000..5d9b2cdcaa --- /dev/null +++ b/spec/engines/sql/unit/predicates/in_spec.rb @@ -0,0 +1,161 @@ +require 'spec_helper' + +module Arel + module Predicates + describe In do + before do + @relation = Arel::Table.new(:users) + @attribute = @relation[:id] + end + + describe '#to_sql' do + describe 'when relating to an array' do + describe 'when the array\'s elements are the same type as the attribute' do + before do + @array = [1, 2, 3] + end + + it 'manufactures sql with a comma separated list' do + sql = In.new(@attribute, @array).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id` IN (1, 2, 3)}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID" IN (1, 2, 3)}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id" IN (1, 2, 3)}) + end + end + end + + describe 'when the array\'s elements are not same type as the attribute' do + before do + @array = ['1-asdf', 2, 3] + end + + it 'formats values in the array as the type of the attribute' do + sql = In.new(@attribute, @array).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id` IN (1, 2, 3)}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID" IN (1, 2, 3)}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id" IN (1, 2, 3)}) + end + end + end + + describe 'when the array is empty' do + before do + @array = [] + end + + it 'manufactures sql with a comma separated list' do + sql = In.new(@attribute, @array).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id` IN (NULL)}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID" IN (NULL)}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id" IN (NULL)}) + end + end + end + + end + + describe 'when relating to a range' do + before do + @range = 1..2 + end + + it 'manufactures sql with a between' do + sql = In.new(@attribute, @range).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id` BETWEEN 1 AND 2}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID" BETWEEN 1 AND 2}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id" BETWEEN 1 AND 2}) + end + end + end + + describe 'when relating to a time range' do + before do + @relation = Arel::Table.new(:developers) + @attribute = @relation[:created_at] + @range = Time.mktime(2010, 01, 01)..Time.mktime(2010, 02, 01) + end + + it 'manufactures sql with a between' do + sql = In.new(@attribute, @range).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`developers`.`created_at` BETWEEN '2010-01-01 00:00:00' AND '2010-02-01 00:00:00'}) + end + + adapter_is :sqlite3 do + if RUBY_VERSION < '1.9' + sql.should be_like(%Q{"developers"."created_at" BETWEEN '2010-01-01 00:00:00.000000' AND '2010-02-01 00:00:00.000000'}) + else + sql.should be_like(%Q{"developers"."created_at" BETWEEN '2010-01-01 00:00:00' AND '2010-02-01 00:00:00'}) + end + end + + adapter_is :postgresql do + sql.should be_like(%Q{"developers"."created_at" BETWEEN '2010-01-01 00:00:00.000000' AND '2010-02-01 00:00:00.000000'}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"DEVELOPERS"."CREATED_AT" BETWEEN TO_TIMESTAMP('2010-01-01 00:00:00:000000','YYYY-MM-DD HH24:MI:SS:FF6') AND TO_TIMESTAMP('2010-02-01 00:00:00:000000','YYYY-MM-DD HH24:MI:SS:FF6')}) + end + end + end + + describe 'when relating to a relation' do + it 'manufactures sql with a subselect' do + sql = In.new(@attribute, @relation).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + `users`.`id` IN (SELECT `users`.`id`, `users`.`name` FROM `users`) + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + "USERS"."ID" IN (SELECT "USERS"."ID", "USERS"."NAME" FROM "USERS") + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + "users"."id" IN (SELECT "users"."id", "users"."name" FROM "users") + }) + end + end + end + end + end + end +end diff --git a/spec/engines/sql/unit/predicates/predicates_spec.rb b/spec/engines/sql/unit/predicates/predicates_spec.rb new file mode 100644 index 0000000000..e6130cf267 --- /dev/null +++ b/spec/engines/sql/unit/predicates/predicates_spec.rb @@ -0,0 +1,79 @@ +require 'spec_helper' + +module Arel + module Predicates + describe Predicate do + before do + @relation = Arel::Table.new(:users) + @attribute1 = @relation[:id] + @attribute2 = @relation[:name] + @operand1 = Arel::Predicates::Equality.new(@attribute1, 1) + @operand2 = Arel::Predicates::Equality.new(@attribute2, "name") + end + + describe "when being combined with another predicate with AND logic" do + describe "#to_sql" do + it "manufactures sql with an AND operation" do + sql = @operand1.and(@operand2).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + (`users`.`id` = 1 AND `users`.`name` = 'name') + }) + end + + adapter_is :sqlite3 do + sql.should be_like(%Q{ + ("users"."id" = 1 AND "users"."name" = 'name') + }) + end + + adapter_is :postgresql do + sql.should be_like(%Q{ + ("users"."id" = 1 AND "users"."name" = E'name') + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + ("USERS"."ID" = 1 AND "USERS"."NAME" = 'name') + }) + end + end + end + end + + describe "when being combined with another predicate with OR logic" do + describe "#to_sql" do + it "manufactures sql with an OR operation" do + sql = @operand1.or(@operand2).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + (`users`.`id` = 1 OR `users`.`name` = 'name') + }) + end + + adapter_is :sqlite3 do + sql.should be_like(%Q{ + ("users"."id" = 1 OR "users"."name" = 'name') + }) + end + + adapter_is :postgresql do + sql.should be_like(%Q{ + ("users"."id" = 1 OR "users"."name" = E'name') + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + ("USERS"."ID" = 1 OR "USERS"."NAME" = 'name') + }) + end + end + end + end + end + end +end diff --git a/spec/engines/sql/unit/primitives/attribute_spec.rb b/spec/engines/sql/unit/primitives/attribute_spec.rb new file mode 100644 index 0000000000..c467d902ad --- /dev/null +++ b/spec/engines/sql/unit/primitives/attribute_spec.rb @@ -0,0 +1,55 @@ +require 'spec_helper' + +module Arel + describe Attribute do + before do + @relation = Table.new(:users) + @attribute = @relation[:id] + end + + describe '#column' do + it "returns the corresponding column in the relation" do + @attribute.column.should == @relation.column_for(@attribute) + end + end + + describe '#to_sql' do + describe 'for a simple attribute' do + it "manufactures sql with an alias" do + sql = @attribute.to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`id`}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."ID"}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."id"}) + end + end + end + + describe 'for an inexistent attribute' do + it "manufactures sql" do + sql = @relation[:does_not_exist].to_sql + + adapter_is :mysql do + sql.should be_like(%Q{`users`.`does_not_exist`}) + end + + adapter_is :oracle do + sql.should be_like(%Q{"USERS"."DOEST_NOT_EXIST"}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{"users"."does_not_exist"}) + end + end + end + + end + end +end diff --git a/spec/engines/sql/unit/primitives/expression_spec.rb b/spec/engines/sql/unit/primitives/expression_spec.rb new file mode 100644 index 0000000000..3b6a7314a2 --- /dev/null +++ b/spec/engines/sql/unit/primitives/expression_spec.rb @@ -0,0 +1,28 @@ +require 'spec_helper' + +module Arel + describe Expression do + before do + @relation = Table.new(:users) + @attribute = @relation[:id] + end + + describe '#to_sql' do + it "manufactures sql with the expression and alias" do + sql = Count.new(@attribute, :alias).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{COUNT(`users`.`id`) AS `alias`}) + end + + adapter_is :oracle do + sql.should be_like(%Q{COUNT("USERS"."ID") AS "ALIAS"}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{COUNT("users"."id") AS "alias"}) + end + end + end + end +end diff --git a/spec/engines/sql/unit/primitives/literal_spec.rb b/spec/engines/sql/unit/primitives/literal_spec.rb new file mode 100644 index 0000000000..3bf60100f1 --- /dev/null +++ b/spec/engines/sql/unit/primitives/literal_spec.rb @@ -0,0 +1,43 @@ +require 'spec_helper' + +module Arel + describe SqlLiteral do + before do + @relation = Table.new(:users) + end + + describe '#to_sql' do + it "manufactures sql with a literal SQL fragment" do + sql = @relation.project(Count.new(SqlLiteral.new("*"))).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{SELECT COUNT(*) AS count_id FROM `users`}) + end + + adapter_is :oracle do + sql.should be_like(%Q{SELECT COUNT(*) AS count_id FROM "USERS"}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{SELECT COUNT(*) AS count_id FROM "users"}) + end + end + + it "manufactures expressions on literal SQL fragment" do + sql = @relation.project(SqlLiteral.new("2 * credit_limit").sum).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{SELECT SUM(2 * credit_limit) AS sum_id FROM `users`}) + end + + adapter_is :oracle do + sql.should be_like(%Q{SELECT SUM(2 * credit_limit) AS sum_id FROM "USERS"}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{SELECT SUM(2 * credit_limit) AS sum_id FROM "users"}) + end + end + end + end +end diff --git a/spec/engines/sql/unit/primitives/value_spec.rb b/spec/engines/sql/unit/primitives/value_spec.rb new file mode 100644 index 0000000000..807090e31a --- /dev/null +++ b/spec/engines/sql/unit/primitives/value_spec.rb @@ -0,0 +1,29 @@ +require 'spec_helper' + +module Arel + describe Value do + before do + @relation = Table.new(:users) + end + + describe '#to_sql' do + it "appropriately quotes the value" do + Value.new(1, @relation).to_sql.should be_like('1') + + adapter_is_not :postgresql do + Value.new('asdf', @relation).to_sql.should be_like("'asdf'") + end + + adapter_is :postgresql do + Value.new('asdf', @relation).to_sql.should be_like("E'asdf'") + end + end + end + + describe '#format' do + it "returns the sql of the provided object" do + Value.new(1, @relation).format(@relation[:id]).should == @relation[:id].to_sql + end + end + end +end diff --git a/spec/engines/sql/unit/relations/alias_spec.rb b/spec/engines/sql/unit/relations/alias_spec.rb new file mode 100644 index 0000000000..a6fd7ab036 --- /dev/null +++ b/spec/engines/sql/unit/relations/alias_spec.rb @@ -0,0 +1,53 @@ +require 'spec_helper' + +module Arel + describe Alias do + before do + @relation = Table.new(:users) + end + + describe '#to_sql' do + describe 'when there is no ambiguity' do + it 'does not alias table names anywhere a table name can appear' do + sql = @relation \ + .where(@relation[:id].eq(1)) \ + .order(@relation[:id]) \ + .project(@relation[:id]) \ + .group(@relation[:id]) \ + .alias \ + .to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id` + FROM `users` + WHERE `users`.`id` = 1 + GROUP BY `users`.`id` + ORDER BY `users`.`id` ASC + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID" + FROM "USERS" + WHERE "USERS"."ID" = 1 + GROUP BY "USERS"."ID" + ORDER BY "USERS"."ID" ASC + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id" + FROM "users" + WHERE "users"."id" = 1 + GROUP BY "users"."id" + ORDER BY "users"."id" ASC + }) + end + end + end + end + end +end diff --git a/spec/engines/sql/unit/relations/delete_spec.rb b/spec/engines/sql/unit/relations/delete_spec.rb new file mode 100644 index 0000000000..302a13c688 --- /dev/null +++ b/spec/engines/sql/unit/relations/delete_spec.rb @@ -0,0 +1,83 @@ +require 'spec_helper' + +module Arel + describe Deletion do + before do + @relation = Table.new(:users) + end + + describe '#to_sql' do + it 'manufactures sql deleting a table relation' do + sql = Deletion.new(@relation).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{DELETE FROM `users`}) + end + + adapter_is :oracle do + sql.should be_like(%Q{DELETE FROM "USERS"}) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{DELETE FROM "users"}) + end + end + + it 'manufactures sql deleting a where relation' do + sql = Deletion.new(@relation.where(@relation[:id].eq(1))).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + DELETE + FROM `users` + WHERE `users`.`id` = 1 + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + DELETE + FROM "USERS" + WHERE "USERS"."ID" = 1 + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + DELETE + FROM "users" + WHERE "users"."id" = 1 + }) + end + end + + it "manufactures sql deleting a ranged relation" do + sql = Deletion.new(@relation.take(1)).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + DELETE + FROM `users` + LIMIT 1 + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + DELETE + FROM "USERS" + WHERE ROWNUM <= 1 + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + DELETE + FROM "users" + LIMIT 1 + }) + end + end + end + end +end diff --git a/spec/engines/sql/unit/relations/from_spec.rb b/spec/engines/sql/unit/relations/from_spec.rb new file mode 100644 index 0000000000..0be3ac0f9a --- /dev/null +++ b/spec/engines/sql/unit/relations/from_spec.rb @@ -0,0 +1,64 @@ +require 'spec_helper' + +module Arel + describe Table do + before do + @relation = Table.new(:users) + end + + describe '#to_sql' do + it "manufactures a simple select query" do + sql = @relation.from("workers").to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM workers + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM workers + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM workers + }) + end + end + end + + describe '#to_sql' do + it "overrides and use last from clause given " do + sql = @relation.from("workers").from("users").to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM users + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM users + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM users + }) + end + end + end + + end +end diff --git a/spec/engines/sql/unit/relations/group_spec.rb b/spec/engines/sql/unit/relations/group_spec.rb new file mode 100644 index 0000000000..72a9f4e99e --- /dev/null +++ b/spec/engines/sql/unit/relations/group_spec.rb @@ -0,0 +1,72 @@ +require 'spec_helper' + +module Arel + describe Group do + before do + @relation = Table.new(:users) + @attribute = @relation[:id] + end + + describe '#to_sql' do + describe 'when given a predicate' do + it "manufactures sql with where clause conditions" do + sql = Group.new(@relation, @attribute).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + GROUP BY `users`.`id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + GROUP BY "USERS"."ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + GROUP BY "users"."id" + }) + end + end + end + + describe 'when given a string' do + it "passes the string through to the where clause" do + sql = Group.new(@relation, 'asdf').to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + GROUP BY asdf + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + GROUP BY asdf + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + GROUP BY asdf + }) + end + end + end + end + end +end diff --git a/spec/engines/sql/unit/relations/having_spec.rb b/spec/engines/sql/unit/relations/having_spec.rb new file mode 100644 index 0000000000..fe6f3cc520 --- /dev/null +++ b/spec/engines/sql/unit/relations/having_spec.rb @@ -0,0 +1,45 @@ +require 'spec_helper' + +module Arel + describe Having do + before do + @relation = Table.new(:developers) + end + + describe '#to_sql' do + describe 'when given a predicate' do + it "manufactures sql with where clause conditions" do + sql = @relation.group(@relation[:department]).having("MIN(salary) > 1000").to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `developers`.`id`, `developers`.`name`, `developers`.`salary`, `developers`.`department`, `developers`.`created_at` + FROM `developers` + GROUP BY `developers`.`department` + HAVING MIN(salary) > 1000 + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "DEVELOPERS"."ID", "DEVELOPERS"."NAME", "DEVELOPERS"."SALARY", "DEVELOPERS"."DEPARTMENT", "DEVELOPERS"."CREATED_AT" + FROM "DEVELOPERS" + GROUP BY "DEVELOPERS"."DEPARTMENT" + HAVING MIN(salary) > 1000 + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "developers"."id", "developers"."name", "developers"."salary", "developers"."department", "developers"."created_at" + FROM "developers" + GROUP BY "developers"."department" + HAVING MIN(salary) > 1000 + }) + end + end + end + end + end +end + diff --git a/spec/engines/sql/unit/relations/insert_spec.rb b/spec/engines/sql/unit/relations/insert_spec.rb new file mode 100644 index 0000000000..4b412093e4 --- /dev/null +++ b/spec/engines/sql/unit/relations/insert_spec.rb @@ -0,0 +1,143 @@ +require 'spec_helper' + +module Arel + describe Insert do + before do + @relation = Table.new(:users) + end + + describe '#to_sql' do + it 'manufactures sql inserting data when given multiple rows' do + pending 'it should insert multiple rows' do + @insertion = Insert.new(@relation, [@relation[:name] => "nick", @relation[:name] => "bryan"]) + + @insertion.to_sql.should be_like(" + INSERT + INTO `users` + (`name`) VALUES ('nick'), ('bryan') + ") + end + end + + it 'manufactures sql inserting data when given multiple values' do + @insertion = Insert.new(@relation, @relation[:id] => "1", @relation[:name] => "nick") + + adapter_is :mysql do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO `users` + (`id`, `name`) VALUES (1, 'nick') + }) + end + + adapter_is :sqlite3 do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO "users" + ("id", "name") VALUES (1, 'nick') + }) + end + + adapter_is :postgresql do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO "users" + ("id", "name") VALUES (1, E'nick') + RETURNING "id" + }) + end + + adapter_is :oracle do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO "USERS" + ("ID", "NAME") VALUES (1, 'nick') + }) + end + end + + describe 'when given values whose types correspond to the types of the attributes' do + before do + @insertion = Insert.new(@relation, @relation[:name] => "nick") + end + + it 'manufactures sql inserting data' do + adapter_is :mysql do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO `users` + (`name`) VALUES ('nick') + }) + end + + adapter_is :sqlite3 do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO "users" + ("name") VALUES ('nick') + }) + end + + adapter_is :postgresql do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO "users" + ("name") VALUES (E'nick') + RETURNING "id" + }) + end + + adapter_is :oracle do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO "USERS" + ("NAME") VALUES ('nick') + }) + end + end + end + + describe 'when given values whose types differ from from the types of the attributes' do + before do + @insertion = Insert.new(@relation, @relation[:id] => '1-asdf') + end + + it 'manufactures sql inserting data' do + adapter_is :mysql do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO `users` + (`id`) VALUES (1) + }) + end + + adapter_is :sqlite3 do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO "users" + ("id") VALUES (1) + }) + end + + adapter_is :postgresql do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO "users" + ("id") VALUES (1) + RETURNING "id" + }) + end + + adapter_is :oracle do + @insertion.to_sql.should be_like(%Q{ + INSERT + INTO "USERS" + ("ID") VALUES (1) + }) + end + + end + end + end + end +end diff --git a/spec/engines/sql/unit/relations/join_spec.rb b/spec/engines/sql/unit/relations/join_spec.rb new file mode 100644 index 0000000000..cbbcb18244 --- /dev/null +++ b/spec/engines/sql/unit/relations/join_spec.rb @@ -0,0 +1,180 @@ +require 'spec_helper' + +module Arel + describe Join do + before do + @relation1 = Table.new(:users) + @relation2 = Table.new(:photos) + @predicate1 = @relation1[:id].eq(@relation2[:user_id]) + + @relation3 = Table.new(:users, :as => :super_users) + @relation4 = Table.new(:photos, :as => :super_photos) + + @predicate2 = @relation3[:id].eq(@relation2[:user_id]) + @predicate3 = @relation3[:id].eq(@relation4[:user_id]) + end + + describe '#to_sql' do + + describe 'when joining with another relation' do + it 'manufactures sql joining the two tables on the predicate' do + sql = InnerJoin.new(@relation1, @relation2, @predicate1).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name`, `photos`.`id`, `photos`.`user_id`, `photos`.`camera_id` + FROM `users` + INNER JOIN `photos` ON `users`.`id` = `photos`.`user_id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID" + FROM "USERS" + INNER JOIN "PHOTOS" ON "USERS"."ID" = "PHOTOS"."USER_ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id" + FROM "users" + INNER JOIN "photos" ON "users"."id" = "photos"."user_id" + }) + end + end + + describe 'when joining with another relation with an aliased table' do + it 'manufactures sql joining the two tables on the predicate respecting table aliasing' do + sql = InnerJoin.new(@relation3, @relation2, @predicate2).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `super_users`.`id`, `super_users`.`name`, `photos`.`id`, `photos`.`user_id`, `photos`.`camera_id` + FROM `users` `super_users` + INNER JOIN `photos` ON `super_users`.`id` = `photos`.`user_id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "SUPER_USERS"."ID", "SUPER_USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID" + FROM "USERS" "SUPER_USERS" + INNER JOIN "PHOTOS" ON "SUPER_USERS"."ID" = "PHOTOS"."USER_ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "super_users"."id", "super_users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id" + FROM "users" "super_users" + INNER JOIN "photos" ON "super_users"."id" = "photos"."user_id" + }) + end + end + end + + describe 'when joining with two relations with aliased tables' do + it 'manufactures sql joining the two tables on the predicate respecting table aliasing' do + sql = InnerJoin.new(@relation3, @relation4, @predicate3).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `super_users`.`id`, `super_users`.`name`, `super_photos`.`id`, `super_photos`.`user_id`, `super_photos`.`camera_id` + FROM `users` `super_users` + INNER JOIN `photos` `super_photos` ON `super_users`.`id` = `super_photos`.`user_id` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "SUPER_USERS"."ID", "SUPER_USERS"."NAME", "SUPER_PHOTOS"."ID", "SUPER_PHOTOS"."USER_ID", "SUPER_PHOTOS"."CAMERA_ID" + FROM "USERS" "SUPER_USERS" + INNER JOIN "PHOTOS" "SUPER_PHOTOS" ON "SUPER_USERS"."ID" = "SUPER_PHOTOS"."USER_ID" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "super_users"."id", "super_users"."name", "super_photos"."id", "super_photos"."user_id", "super_photos"."camera_id" + FROM "users" "super_users" + INNER JOIN "photos" "super_photos" ON "super_users"."id" = "super_photos"."user_id" + }) + end + end + end + + end + + describe 'when joining with a string' do + it "passes the string through to the where clause" do + sql = StringJoin.new(@relation1, "INNER JOIN asdf ON fdsa").to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + INNER JOIN asdf ON fdsa + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + INNER JOIN asdf ON fdsa + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + INNER JOIN asdf ON fdsa + }) + end + end + + it "passes the string when there are multiple string joins" do + relation = StringJoin.new(@relation1, "INNER JOIN asdf ON fdsa") + relation = StringJoin.new(relation, "INNER JOIN lifo ON fifo") + sql = StringJoin.new(relation, "INNER JOIN hatful ON hallow").to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + INNER JOIN asdf ON fdsa + INNER JOIN lifo ON fifo + INNER JOIN hatful ON hallow + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + INNER JOIN asdf ON fdsa + INNER JOIN lifo ON fifo + INNER JOIN hatful ON hallow + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + INNER JOIN asdf ON fdsa + INNER JOIN lifo ON fifo + INNER JOIN hatful ON hallow + }) + end + end + + end + + + end + end +end diff --git a/spec/engines/sql/unit/relations/lock_spec.rb b/spec/engines/sql/unit/relations/lock_spec.rb new file mode 100644 index 0000000000..72a8a2e457 --- /dev/null +++ b/spec/engines/sql/unit/relations/lock_spec.rb @@ -0,0 +1,86 @@ +require 'spec_helper' + +module Arel + describe Lock do + before do + @relation = Table.new(:users) + end + + describe '#to_sql' do + it "manufactures a simple select query lock" do + sql = @relation.lock.to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` FOR UPDATE + }) + end + + adapter_is :postgresql do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" FOR UPDATE + }) + end + + adapter_is :sqlite3 do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" FOR UPDATE + }) + + sql_with_order_by = @relation.order(@relation[:id]).take(1).lock.to_sql + sql_with_order_by.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + WHERE "ID" IN (select * from + (SELECT "ID" FROM "USERS" ORDER BY "USERS"."ID" ASC) + where rownum <= 1) + FOR UPDATE + }) + + end + end + + it "manufactures a select query locking with a given lock" do + sql = @relation.lock("LOCK IN SHARE MODE").to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` LOCK IN SHARE MODE + }) + end + + adapter_is :postgresql do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" LOCK IN SHARE MODE + }) + end + + adapter_is :sqlite3 do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" LOCK IN SHARE MODE + }) + end + end + end + end +end diff --git a/spec/engines/sql/unit/relations/order_spec.rb b/spec/engines/sql/unit/relations/order_spec.rb new file mode 100644 index 0000000000..3c9d9ef598 --- /dev/null +++ b/spec/engines/sql/unit/relations/order_spec.rb @@ -0,0 +1,161 @@ +require 'spec_helper' + +module Arel + describe Order do + before do + @relation = Table.new(:users) + @attribute = @relation[:id] + end + + describe '#to_sql' do + describe "when given an attribute" do + it "manufactures sql with an order clause populated by the attribute" do + sql = Order.new(@relation, @attribute).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + ORDER BY `users`.`id` ASC + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + ORDER BY "USERS"."ID" ASC + }) + + distinct_attributes = ActiveRecord::Base.connection.distinct('"USERS"."NAME"', '"USERS"."ID"') + @relation.project(distinct_attributes).order(@relation[:id]).to_sql.should be_like(%Q{ + SELECT DISTINCT "USERS"."NAME", + FIRST_VALUE("USERS"."ID") OVER (PARTITION BY "USERS"."NAME" ORDER BY "USERS"."ID") AS alias_0__ + FROM "USERS" + ORDER BY alias_0__ + }) + + distinct_attributes = ActiveRecord::Base.connection.distinct('"USERS"."NAME"', '"USERS"."ID" DESC') + @relation.project(distinct_attributes).order('"USERS"."ID" DESC').to_sql.should be_like(%Q{ + SELECT DISTINCT "USERS"."NAME", + FIRST_VALUE("USERS"."ID") OVER (PARTITION BY "USERS"."NAME" ORDER BY "USERS"."ID" DESC) AS alias_0__ + FROM "USERS" + ORDER BY alias_0__ DESC + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + ORDER BY "users"."id" ASC + }) + end + end + end + + describe "when given multiple attributes" do + before do + @another_attribute = @relation[:name] + end + + it "manufactures sql with an order clause populated by comma-separated attributes" do + sql = Order.new(@relation, @attribute, @another_attribute).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + ORDER BY `users`.`id` ASC, `users`.`name` ASC + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + ORDER BY "USERS"."ID" ASC, "USERS"."NAME" ASC + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + ORDER BY "users"."id" ASC, "users"."name" ASC + }) + end + end + end + + describe "when given a string" do + before do + @string = "asdf" + end + + it "passes the string through to the order clause" do + sql = Order.new(@relation, @string).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + ORDER BY asdf + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + ORDER BY asdf + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + ORDER BY asdf + }) + end + end + end + + describe "when ordering an ordered relation" do + before do + @ordered_relation = Order.new(@relation, @attribute) + @another_attribute = @relation[:name] + end + + it "manufactures sql with the order clause of the last ordering preceding the first ordering" do + sql = Order.new(@ordered_relation, @another_attribute).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + ORDER BY `users`.`name` ASC, `users`.`id` ASC + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + ORDER BY "USERS"."NAME" ASC, "USERS"."ID" ASC + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + ORDER BY "users"."name" ASC, "users"."id" ASC + }) + end + end + end + end + end +end diff --git a/spec/engines/sql/unit/relations/project_spec.rb b/spec/engines/sql/unit/relations/project_spec.rb new file mode 100644 index 0000000000..e73c7775a1 --- /dev/null +++ b/spec/engines/sql/unit/relations/project_spec.rb @@ -0,0 +1,143 @@ +require 'spec_helper' + +module Arel + describe Project do + before do + @relation = Table.new(:users) + @attribute = @relation[:id] + end + + describe '#to_sql' do + describe 'when given an attribute' do + it "manufactures sql with a limited select clause" do + sql = Project.new(@relation, @attribute).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id` + FROM `users` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID" + FROM "USERS" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id" + FROM "users" + }) + end + end + end + + describe 'when given a relation' do + before do + @scalar_relation = Project.new(@relation, @relation[:name]) + end + + it "manufactures sql with scalar selects" do + sql = Project.new(@relation, @scalar_relation).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT (SELECT `users`.`name` FROM `users`) AS `users` FROM `users` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT (SELECT "USERS"."NAME" FROM "USERS") AS "USERS" FROM "USERS" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT (SELECT "users"."name" FROM "users") AS "users" FROM "users" + }) + end + end + end + + describe 'when given a string' do + it "passes the string through to the select clause" do + sql = Project.new(@relation, 'asdf').to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT asdf FROM `users` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT asdf FROM "USERS" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT asdf FROM "users" + }) + end + end + end + + describe 'when given an expression' do + it 'manufactures sql with expressions' do + sql = @relation.project(@attribute.count).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT COUNT(`users`.`id`) AS count_id + FROM `users` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT COUNT("USERS"."ID") AS count_id + FROM "USERS" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT COUNT("users"."id") AS count_id + FROM "users" + }) + end + end + + it 'manufactures sql with distinct expressions' do + sql = @relation.project(@attribute.count(true)).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT COUNT(DISTINCT `users`.`id`) AS count_id + FROM `users` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT COUNT(DISTINCT "USERS"."ID") AS count_id + FROM "USERS" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT COUNT(DISTINCT "users"."id") AS count_id + FROM "users" + }) + end + end + end + end + end +end diff --git a/spec/engines/sql/unit/relations/skip_spec.rb b/spec/engines/sql/unit/relations/skip_spec.rb new file mode 100644 index 0000000000..41b80d12d8 --- /dev/null +++ b/spec/engines/sql/unit/relations/skip_spec.rb @@ -0,0 +1,41 @@ +require 'spec_helper' + +module Arel + describe Skip do + before do + @relation = Table.new(:users) + @skipped = 4 + end + + describe '#to_sql' do + it "manufactures sql with limit and offset" do + sql = Skip.new(@relation, @skipped).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + OFFSET 4 + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + select * from (select raw_sql_.*, rownum raw_rnum_ from + (SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS") raw_sql_) + where raw_rnum_ > 4 + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + OFFSET 4 + }) + end + end + end + end +end diff --git a/spec/engines/sql/unit/relations/table_spec.rb b/spec/engines/sql/unit/relations/table_spec.rb new file mode 100644 index 0000000000..6afd9964a4 --- /dev/null +++ b/spec/engines/sql/unit/relations/table_spec.rb @@ -0,0 +1,129 @@ +require 'spec_helper' + +module Arel + describe Table do + before do + @relation = Table.new(:users) + end + + describe '#to_sql' do + it "manufactures a simple select query" do + sql = @relation.to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + }) + end + end + end + + describe '#as' do + it "manufactures a simple select query using aliases" do + sql = @relation.as(:super_users).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `super_users`.`id`, `super_users`.`name` + FROM `users` `super_users` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "SUPER_USERS"."ID", "SUPER_USERS"."NAME" + FROM "USERS" "SUPER_USERS" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "super_users"."id", "super_users"."name" + FROM "users" "super_users" + }) + end + end + + it "does not apply alias if it's same as the table name" do + sql = @relation.as(:users).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + }) + end + end + + end + + describe '#column_for' do + it "returns the column corresponding to the attribute" do + @relation.column_for(@relation[:id]).should == @relation.columns.detect { |c| c.name == 'id' } + end + end + + describe '#attributes' do + it 'manufactures attributes corresponding to columns in the table' do + @relation.attributes.should == [ + Attribute.new(@relation, :id), + Attribute.new(@relation, :name) + ] + end + + describe '#reset' do + it "reloads columns from the database" do + lambda { @relation.engine.stub!(:columns => []) }.should_not change { @relation.attributes } + lambda { @relation.reset }.should change { @relation.attributes } + end + end + end + + describe 'hashing' do + it "implements hash equality" do + Table.new(:users).should hash_the_same_as(Table.new(:users)) + Table.new(:users).should_not hash_the_same_as(Table.new(:photos)) + end + end + + describe '#engine' do + it "defaults to global engine" do + Table.engine = engine = Sql::Engine.new + Table.new(:users).engine.should == engine + end + + it "can be specified" do + Table.new(:users, engine = Sql::Engine.new).engine.should == engine + end + end + end +end diff --git a/spec/engines/sql/unit/relations/take_spec.rb b/spec/engines/sql/unit/relations/take_spec.rb new file mode 100644 index 0000000000..9f2967a0bd --- /dev/null +++ b/spec/engines/sql/unit/relations/take_spec.rb @@ -0,0 +1,49 @@ +require 'spec_helper' + +module Arel + describe Take do + before do + @relation = Table.new(:users) + @taken = 4 + end + + describe '#to_sql' do + it "manufactures sql with limit and offset" do + sql = Take.new(@relation, @taken).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + LIMIT 4 + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + WHERE ROWNUM <= 4 + }) + + sql_with_order_by = Take.new(@relation.order(@relation[:id]), @taken).to_sql + sql_with_order_by.should be_like(%Q{ + select * from + (SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + ORDER BY "USERS"."ID" ASC) + where rownum <= 4 + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + LIMIT 4 + }) + end + end + end + end +end diff --git a/spec/engines/sql/unit/relations/update_spec.rb b/spec/engines/sql/unit/relations/update_spec.rb new file mode 100644 index 0000000000..cc2ad9913b --- /dev/null +++ b/spec/engines/sql/unit/relations/update_spec.rb @@ -0,0 +1,203 @@ +require 'spec_helper' + +class User + def self.primary_key + "id" + end +end + +module Arel + describe Update do + before do + @relation = Table.new(:users) + end + + describe '#to_sql' do + it "manufactures sql updating attributes when given multiple attributes" do + sql = Update.new(@relation, @relation[:id] => 1, @relation[:name] => "nick").to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + UPDATE `users` + SET `id` = 1, `name` = 'nick' + }) + end + + adapter_is :sqlite3 do + sql.should be_like(%Q{ + UPDATE "users" + SET "id" = 1, "name" = 'nick' + }) + end + + adapter_is :postgresql do + sql.should be_like(%Q{ + UPDATE "users" + SET "id" = 1, "name" = E'nick' + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + UPDATE "USERS" + SET "ID" = 1, "NAME" = 'nick' + }) + end + end + + it "manufactures sql updating attributes when given a ranged relation" do + sql = Update.new(@relation.take(1), @relation[:name] => "nick").to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + UPDATE `users` + SET `name` = 'nick' + LIMIT 1 + }) + end + + adapter_is :sqlite3 do + sql.should be_like(%Q{ + UPDATE "users" SET + "name" = 'nick' + WHERE "id" IN (SELECT "id" FROM "users" LIMIT 1) + }) + end + + adapter_is :postgresql do + sql.should be_like(%Q{ + UPDATE "users" SET + "name" = E'nick' + WHERE "id" IN (SELECT "id" FROM "users" LIMIT 1) + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + UPDATE "USERS" SET + "NAME" = 'nick' + WHERE "ID" IN (SELECT "ID" FROM "USERS" WHERE ROWNUM <= 1) + }) + + sql_with_order_by = Update.new(@relation.order(@relation[:id]).take(1), @relation[:name] => "nick").to_sql + sql_with_order_by.should be_like(%Q{ + UPDATE "USERS" SET + "NAME" = 'nick' + WHERE "ID" IN (select * from + (SELECT "ID" FROM "USERS" ORDER BY "USERS"."ID" ASC) + where rownum <= 1) + }) + end + end + + describe 'when given values whose types correspond to the types of the attributes' do + before do + @update = Update.new(@relation, @relation[:name] => "nick") + end + + it 'manufactures sql updating attributes' do + adapter_is :mysql do + @update.to_sql.should be_like(%Q{ + UPDATE `users` + SET `name` = 'nick' + }) + end + + adapter_is :sqlite3 do + @update.to_sql.should be_like(%Q{ + UPDATE "users" + SET "name" = 'nick' + }) + end + + adapter_is :postgresql do + @update.to_sql.should be_like(%Q{ + UPDATE "users" + SET "name" = E'nick' + }) + end + + adapter_is :oracle do + @update.to_sql.should be_like(%Q{ + UPDATE "USERS" + SET "NAME" = 'nick' + }) + end + end + end + + describe 'when given values whose types differ from from the types of the attributes' do + before do + @update = Update.new(@relation, @relation[:id] => '1-asdf') + end + + it 'manufactures sql updating attributes' do + adapter_is :mysql do + @update.to_sql.should be_like(%Q{ + UPDATE `users` + SET `id` = 1 + }) + end + + adapter_is :oracle do + @update.to_sql.should be_like(%Q{ + UPDATE "USERS" + SET "ID" = 1 + }) + end + + adapter_is_not :mysql, :oracle do + @update.to_sql.should be_like(%Q{ + UPDATE "users" + SET "id" = 1 + }) + end + end + end + + describe 'when the relation is a where' do + before do + @update = Update.new( + @relation.where(@relation[:id].eq(1)), + @relation[:name] => "nick" + ) + end + + it 'manufactures sql updating a where relation' do + adapter_is :mysql do + @update.to_sql.should be_like(%Q{ + UPDATE `users` + SET `name` = 'nick' + WHERE `users`.`id` = 1 + }) + end + + adapter_is :sqlite3 do + @update.to_sql.should be_like(%Q{ + UPDATE "users" + SET "name" = 'nick' + WHERE "users"."id" = 1 + }) + end + + adapter_is :postgresql do + @update.to_sql.should be_like(%Q{ + UPDATE "users" + SET "name" = E'nick' + WHERE "users"."id" = 1 + }) + end + + adapter_is :oracle do + @update.to_sql.should be_like(%Q{ + UPDATE "USERS" + SET "NAME" = 'nick' + WHERE "USERS"."ID" = 1 + }) + end + end + end + end + + end +end diff --git a/spec/engines/sql/unit/relations/where_spec.rb b/spec/engines/sql/unit/relations/where_spec.rb new file mode 100644 index 0000000000..5f559efad3 --- /dev/null +++ b/spec/engines/sql/unit/relations/where_spec.rb @@ -0,0 +1,72 @@ +require 'spec_helper' + +module Arel + describe Where do + before do + @relation = Table.new(:users) + @predicate = @relation[:id].eq(1) + end + + describe '#to_sql' do + describe 'when given a predicate' do + it "manufactures sql with where clause conditions" do + sql = Where.new(@relation, @predicate).to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + WHERE `users`.`id` = 1 + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + WHERE "USERS"."ID" = 1 + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + WHERE "users"."id" = 1 + }) + end + end + end + + describe 'when given a string' do + it "passes the string through to the where clause" do + sql = Where.new(@relation, 'asdf').to_sql + + adapter_is :mysql do + sql.should be_like(%Q{ + SELECT `users`.`id`, `users`.`name` + FROM `users` + WHERE asdf + }) + end + + adapter_is :oracle do + sql.should be_like(%Q{ + SELECT "USERS"."ID", "USERS"."NAME" + FROM "USERS" + WHERE asdf + }) + end + + adapter_is_not :mysql, :oracle do + sql.should be_like(%Q{ + SELECT "users"."id", "users"."name" + FROM "users" + WHERE asdf + }) + end + end + end + end + end +end |