aboutsummaryrefslogtreecommitdiffstats
path: root/spec/engines/sql/unit/relations
diff options
context:
space:
mode:
Diffstat (limited to 'spec/engines/sql/unit/relations')
-rw-r--r--spec/engines/sql/unit/relations/alias_spec.rb53
-rw-r--r--spec/engines/sql/unit/relations/delete_spec.rb83
-rw-r--r--spec/engines/sql/unit/relations/from_spec.rb64
-rw-r--r--spec/engines/sql/unit/relations/group_spec.rb72
-rw-r--r--spec/engines/sql/unit/relations/having_spec.rb45
-rw-r--r--spec/engines/sql/unit/relations/insert_spec.rb143
-rw-r--r--spec/engines/sql/unit/relations/join_spec.rb180
-rw-r--r--spec/engines/sql/unit/relations/lock_spec.rb86
-rw-r--r--spec/engines/sql/unit/relations/order_spec.rb161
-rw-r--r--spec/engines/sql/unit/relations/project_spec.rb143
-rw-r--r--spec/engines/sql/unit/relations/skip_spec.rb41
-rw-r--r--spec/engines/sql/unit/relations/table_spec.rb129
-rw-r--r--spec/engines/sql/unit/relations/take_spec.rb49
-rw-r--r--spec/engines/sql/unit/relations/update_spec.rb203
-rw-r--r--spec/engines/sql/unit/relations/where_spec.rb72
15 files changed, 1524 insertions, 0 deletions
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
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-07 11:39:31 +0000