add support for COUNT(DISTINCT id) queries in sqlite2 (closes #4300) [Rick Olson]

git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@3979 5ecf4fe2-1ee6-0310-87b1-e25e094e27de

1 files changed, 21 insertions, 2 deletions

diff --git a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb
index 579bc471d2..bc874ee58b 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb
@@ -36,7 +36,7 @@ module ActiveRecord
 
           # "Downgrade" deprecated sqlite API
           if SQLite.const_defined?(:Version)
-            ConnectionAdapters::SQLiteAdapter.new(db, logger)
+            ConnectionAdapters::SQLite2Adapter.new(db, logger)
           else
             ConnectionAdapters::DeprecatedSQLiteAdapter.new(db, logger)
           end
@@ -331,8 +331,27 @@ module ActiveRecord
           end
         end
     end
+    
+    class SQLite2Adapter < SQLiteAdapter # :nodoc:
+      # SQLite 2 does not support COUNT(DISTINCT) queries:
+      #
+      #   select COUNT(DISTINCT ArtistID) from CDs;    
+      #
+      # In order to get  the number of artists we execute the following statement
+      # 
+      #   SELECT COUNT(ArtistID) FROM (SELECT DISTINCT ArtistID FROM CDs);
+      def execute(sql, name = nil) #:nodoc:
+        if sql =~ /count\(distinct ([^\)]+)\)( AS \w+)? (.*)/i
+          distinct_column = $1
+          distinct_query  = $3
+          column_name     = distinct_column.split('.').last
+          sql             = "SELECT COUNT(#{column_name}) FROM (SELECT DISTINCT #{distinct_column} #{distinct_query})"
+        end
+        log(sql, name) { @connection.execute(sql) }
+      end
+    end
 
-    class DeprecatedSQLiteAdapter < SQLiteAdapter # :nodoc:
+    class DeprecatedSQLiteAdapter < SQLite2Adapter # :nodoc:
       def insert(sql, name = nil, pk = nil, id_value = nil)
         execute(sql, name = nil)
         id_value || @connection.last_insert_rowid