Skip to content

Instantly share code, notes, and snippets.

@praveend
Created February 2, 2010 10:57
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save praveend/292578 to your computer and use it in GitHub Desktop.
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