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

2 files changed, 22 insertions, 5 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
diff --git a/activerecord/test/base_test.rb b/activerecord/test/base_test.rb
index d35f876002..35b00b878a 100755
--- a/activerecord/test/base_test.rb
+++ b/activerecord/test/base_test.rb
@@ -1095,9 +1095,7 @@ class BasicsTest < Test::Unit::TestCase
 
     assert_equal res4, res5 
 
-    unless ActiveRecord::Base.connection.raw_connection.class.name == 'SQLite::Database'
-      res6 = Post.count_by_sql "SELECT COUNT(DISTINCT p.id) FROM posts p, comments c WHERE p.#{QUOTED_TYPE} = 'Post' AND p.id=c.post_id"
-    end
+    res6 = Post.count_by_sql "SELECT COUNT(DISTINCT p.id) FROM posts p, comments c WHERE p.#{QUOTED_TYPE} = 'Post' AND p.id=c.post_id"
     res7 = nil
     assert_nothing_raised do
       res7 = Post.count(:conditions => "p.#{QUOTED_TYPE} = 'Post' AND p.id=c.post_id",