This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
From f1dea61ce483e01aa7e17b1fcdf1d9dbc0499525 Mon Sep 17 00:00:00 2001 | |
From: Praveen Devarao <praveen@falcon> | |
Date: Wed, 3 Feb 2010 22:48:27 +0530 | |
Subject: [PATCH] Current handling of LIMIT and OFFSET inclusion in select sql's will break standard compliant databases like DB2. Added a patch that generates the right sql "FETCH FIRST #{taken} ROWS ONLY" for specifying LIMIT and OFFSET for DB2 and Informix database(IBM_DB adapter). | |
--- | |
lib/arel/engines/sql/relations/relation.rb | 12 ++++++++++++ | |
lib/arel/engines/sql/relations/writes.rb | 13 ++++++++++--- | |
2 files changed, 22 insertions(+), 3 deletions(-) | |
diff --git a/lib/arel/engines/sql/relations/relation.rb b/lib/arel/engines/sql/relations/relation.rb | |
index 7850859..00effc1 100644 | |
--- a/lib/arel/engines/sql/relations/relation.rb | |
+++ b/lib/arel/engines/sql/relations/relation.rb | |
@@ -26,6 +26,18 @@ module Arel | |
("LIMIT #{taken}" unless taken.blank? ), | |
("OFFSET #{skipped}" unless skipped.blank? ) | |
+ elsif engine.adapter_name == "IBM_DB" | |
+ query = build_query \ | |
+ "SELECT #{select_clauses.join(', ')}", | |
+ "FROM #{from_clauses}", | |
+ (joins(self) unless joins(self).blank? ), | |
+ ("WHERE #{where_clauses.join(" AND ")}" unless wheres.blank? ), | |
+ ("GROUP BY #{group_clauses.join(', ')}" unless groupings.blank? ), | |
+ ("HAVING #{having_clauses.join(', ')}" unless havings.blank? ), | |
+ ("ORDER BY #{order_clauses.join(', ')}" unless orders.blank? ) | |
+ engine.add_limit_offset!(query,{:limit=>taken,:offset=>skipped}) unless taken.blank? | |
+ query << "#{locked}" unless locked.blank? | |
+ query | |
else | |
build_query \ | |
"SELECT #{select_clauses.join(', ')}", | |
diff --git a/lib/arel/engines/sql/relations/writes.rb b/lib/arel/engines/sql/relations/writes.rb | |
index 83c5fba..3a21303 100644 | |
--- a/lib/arel/engines/sql/relations/writes.rb | |
+++ b/lib/arel/engines/sql/relations/writes.rb | |
@@ -5,7 +5,7 @@ module Arel | |
"DELETE", | |
"FROM #{table_sql}", | |
("WHERE #{wheres.collect(&:to_sql).join(' AND ')}" unless wheres.blank? ), | |
- ("LIMIT #{taken}" unless taken.blank? ) | |
+ ("LIMIT #{taken} " unless engine.adapter_name == "IBM_DB" || taken.blank? ) #DB2 does not allow LIMIT in a DELETE sql. | |
end | |
end | |
@@ -67,7 +67,9 @@ module Arel | |
conditions << " ORDER BY #{order_clauses.join(', ')}" unless orders.blank? | |
unless taken.blank? | |
- conditions << " LIMIT #{taken}" | |
+ unless engine.adapter_name == "IBM_DB" | |
+ conditions << " LIMIT #{taken}" | |
+ end | |
if engine.adapter_name != "MySQL" | |
begin | |
@@ -76,7 +78,12 @@ module Arel | |
quote_primary_key = engine.quote_column_name("id") | |
end | |
- conditions = "WHERE #{quote_primary_key} IN (SELECT #{quote_primary_key} FROM #{engine.connection.quote_table_name table.name} #{conditions})" | |
+ conditions = "WHERE #{quote_primary_key} IN (SELECT #{quote_primary_key} FROM #{engine.connection.quote_table_name table.name} #{conditions} " #Note: ")" not added since "FETCH FIRST #{taken} ROWS ONLY" ('LIMIT') is to be added if adapter is IBM_DB | |
+ | |
+ if engine.adapter_name == "IBM_DB" | |
+ engine.add_limit_offset!(conditions,{:limit=>taken,:offset=>nil}) | |
+ end | |
+ conditions << ")" | |
end | |
end | |
-- | |
1.6.0 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment