Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Changeset 5426

Show
Ignore:
Timestamp:
11/05/06 02:01:31 (2 years ago)
Author:
bitsweat
Message:

SQLite: count(distinct) queries supported in >= 3.2.6, fix calculations workaround, remove count(distinct) query rewrite, cleanup test connection scripts. Closes #6544.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • trunk/activerecord/CHANGELOG

    r5416 r5426  
    11*SVN* 
     2 
     3* SQLite: fix calculations workaround, remove count(distinct) query rewrite, cleanup test connection scripts.  [Jeremy Kemper] 
     4 
     5* SQLite: count(distinct) queries supported in >= 3.2.6.  #6544 [Bob Silva] 
    26 
    37* Dynamically generate reader methods for serialized attributes.  #6362 [Stefan Kaes] 

  • trunk/activerecord/lib/active_record/calculations.rb

    r5204 r5426  
    77 
    88    module ClassMethods 
    9       # Count operates using three different approaches.  
     9      # Count operates using three different approaches. 
    1010      # 
    1111      # * Count all: By not passing any parameters to count, it will return a count of all the rows for the model. 
    …… 
    3737      #   Person.count(:conditions => "age > 26") 
    3838      #   Person.count(:conditions => "age > 26 AND job.salary > 60000", :include => :job) # because of the named association, it finds the DISTINCT count using LEFT OUTER JOIN. 
    39       #   Person.count(:conditions => "age > 26 AND job.salary > 60000", :joins => "LEFT JOIN jobs on jobs.person_id = person.id") # finds the number of rows matching the conditions and joins.  
     39      #   Person.count(:conditions => "age > 26 AND job.salary > 60000", :joins => "LEFT JOIN jobs on jobs.person_id = person.id") # finds the number of rows matching the conditions and joins. 
    4040      #   Person.count('id', :conditions => "age > 26") # Performs a COUNT(id) 
    4141      #   Person.count(:all, :conditions => "age > 26") # Performs a COUNT(*) (:all is an alias for '*') 
    …… 
    7575 
    7676      # This calculates aggregate values in the given column:  Methods for count, sum, average, minimum, and maximum have been added as shortcuts. 
    77       # Options such as :conditions, :order, :group, :having, and :joins can be passed to customize the query.   
     77      # Options such as :conditions, :order, :group, :having, and :joins can be passed to customize the query. 
    7878      # 
    7979      # There are two basic forms of output: 
    8080      #   * Single aggregate value: The single value is type cast to Fixnum for COUNT, Float for AVG, and the given column's type for everything else. 
    81       #   * Grouped values: This returns an ordered hash of the values and groups them by the :group option.  It takes either a column name, or the name  
     81      #   * Grouped values: This returns an ordered hash of the values and groups them by the :group option.  It takes either a column name, or the name 
    8282      #     of a belongs_to association. 
    8383      # 
    …… 
    158158 
    159159        def construct_calculation_sql(operation, column_name, options) #:nodoc: 
     160          operation = operation.to_s.downcase 
     161          options = options.symbolize_keys 
     162 
    160163          scope           = scope(:find) 
    161164          merged_includes = merge_includes(scope ? scope[:include] : [], options[:include]) 
    162165          aggregate_alias = column_alias_for(operation, column_name) 
    163           use_workaround  = !Base.connection.supports_count_distinct? && options[:distinct] && operation.to_s.downcase == 'count' 
    164           join_dependency = nil 
    165  
    166           if merged_includes.any? && operation.to_s.downcase == 'count' 
    167             options[:distinct] = true 
    168             column_name = options[:select] || [table_name, primary_key] * '.' 
    169           end 
    170  
    171           sql  = "SELECT #{operation}(#{'DISTINCT ' if options[:distinct]}#{column_name}) AS #{aggregate_alias}" 
     166 
     167          if operation == 'count' 
     168            if merged_includes.any? 
     169              options[:distinct] = true 
     170              column_name = options[:select] || [table_name, primary_key] * '.' 
     171            end 
     172 
     173            if options[:distinct] 
     174              use_workaround = !connection.supports_count_distinct? 
     175            end 
     176          end 
     177 
     178          sql = "SELECT #{operation}(#{'DISTINCT ' if options[:distinct]}#{column_name}) AS #{aggregate_alias}" 
    172179 
    173180          # A (slower) workaround if we're using a backend, like sqlite, that doesn't support COUNT DISTINCT. 
    174181          sql = "SELECT COUNT(*) AS #{aggregate_alias}" if use_workaround 
    175            
     182 
    176183          sql << ", #{options[:group_field]} AS #{options[:group_alias]}" if options[:group] 
    177184          sql << " FROM (SELECT DISTINCT #{column_name}" if use_workaround 
    …… 
    186193 
    187194          if options[:group] 
    188             group_key = Base.connection.adapter_name == 'FrontBase' ?  :group_alias : :group_field 
     195            group_key = connection.adapter_name == 'FrontBase' ?  :group_alias : :group_field 
    189196            sql << " GROUP BY #{options[group_key]} " 
    190197          end 
    …… 
    192199          if options[:group] && options[:having] 
    193200            # FrontBase requires identifiers in the HAVING clause and chokes on function calls 
    194             if Base.connection.adapter_name == 'FrontBase' 
     201            if connection.adapter_name == 'FrontBase' 
    195202              options[:having].downcase! 
    196203              options[:having].gsub!(/#{operation}\s*\(\s*#{column_name}\s*\)/, aggregate_alias) 
    197204            end 
    198                
     205 
    199206            sql << " HAVING #{options[:having]} " 
    200207          end 

  • trunk/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb

    r5353 r5426  
    7474              when "%"  then "%25" 
    7575            end 
    76           end                 
    77         end 
    78          
     76          end 
     77        end 
     78 
    7979        def binary_to_string(value) 
    8080          value.gsub(/%00|%25/) do |b| 
    …… 
    8383              when "%25" then "%" 
    8484            end 
    85           end                 
     85          end 
    8686        end 
    8787      end 
    …… 
    102102        true 
    103103      end 
    104        
     104 
    105105      def supports_count_distinct? #:nodoc: 
    106         false 
     106        sqlite_version >= '3.2.6' 
    107107      end 
    108108 
    …… 
    179179        catch_schema_changes { @connection.transaction } 
    180180      end 
    181        
     181 
    182182      def commit_db_transaction #:nodoc: 
    183183        catch_schema_changes { @connection.commit } 
    …… 
    226226        execute "DROP INDEX #{quote_column_name(index_name(table_name, options))}" 
    227227      end 
    228        
     228 
    229229      def rename_table(name, new_name) 
    230230        execute "ALTER TABLE #{name} RENAME TO #{new_name}" 
    …… 
    236236        execute "VACUUM" 
    237237      end 
    238        
     238 
    239239      def remove_column(table_name, column_name) #:nodoc: 
    240240        alter_table(table_name) do |definition| 
    …… 
    242242        end 
    243243      end 
    244        
     244 
    245245      def change_column_default(table_name, column_name, default) #:nodoc: 
    246246        alter_table(table_name) do |definition| 
    …… 
    262262        alter_table(table_name, :rename => {column_name => new_column_name}) 
    263263      end 
    264            
     264 
    265265 
    266266      protected 
    …… 
    270270          end 
    271271        end 
    272          
     272 
    273273        def alter_table(table_name, options = {}) #:nodoc: 
    274274          altered_table_name = "altered_#{table_name}" 
    …… 
    276276 
    277277          transaction do 
    278             move_table(table_name, altered_table_name,  
     278            move_table(table_name, altered_table_name, 
    279279              options.merge(:temporary => true)) 
    280280            move_table(altered_table_name, table_name, &caller) 
    281281          end 
    282282        end 
    283          
     283 
    284284        def move_table(from, to, options = {}, &block) #:nodoc: 
    285285          copy_table(from, to, options, &block) 
    286286          drop_table(from) 
    287287        end 
    288          
     288 
    289289        def copy_table(from, to, options = {}) #:nodoc: 
    290290          create_table(to, options) do |@definition| 
    …… 
    295295                 column.name) : column.name 
    296296 
    297               @definition.column(column_name, column.type,  
     297              @definition.column(column_name, column.type, 
    298298                :limit => column.limit, :default => column.default, 
    299299                :null => column.null) 
    …… 
    302302            yield @definition if block_given? 
    303303          end 
    304            
     304 
    305305          copy_table_indexes(from, to) 
    306           copy_table_contents(from, to,  
    307             @definition.columns.map {|column| column.name},  
     306          copy_table_contents(from, to, 
     307            @definition.columns.map {|column| column.name}, 
    308308            options[:rename] || {}) 
    309309        end 
    310          
     310 
    311311        def copy_table_indexes(from, to) #:nodoc: 
    312312          indexes(from).each do |index| 
    …… 
    317317              name = name[5..-1] 
    318318            end 
    319              
     319 
    320320            # index name can't be the same 
    321321            opts = { :name => name.gsub(/_(#{from})_/, "_#{to}_") } 
    …… 
    324324          end 
    325325        end 
    326          
     326 
    327327        def copy_table_contents(from, to, columns, rename = {}) #:nodoc: 
    328328          column_mappings = Hash[*columns.map {|name| [name, name]}.flatten] 
    …… 
    331331          columns = columns.find_all{|col| from_columns.include?(column_mappings[col])} 
    332332          @connection.execute "SELECT * FROM #{from}" do |row| 
    333             sql = "INSERT INTO #{to} ("+columns*','+") VALUES ("             
     333            sql = "INSERT INTO #{to} ("+columns*','+") VALUES (" 
    334334            sql << columns.map {|col| quote row[column_mappings[col]]} * ', ' 
    335335            sql << ')' 
    …… 
    337337          end 
    338338        end 
    339          
     339 
    340340        def catch_schema_changes 
    341341          return yield 
    …… 
    348348          end 
    349349        end 
    350     end 
    351      
     350 
     351        def sqlite_version 
     352          @sqlite_version ||= select_value('select sqlite_version(*)') 
     353        end 
     354    end 
     355 
    352356    class SQLite2Adapter < SQLiteAdapter # :nodoc: 
    353       # SQLite 2 does not support COUNT(DISTINCT) queries: 
    354       # 
    355       #   select COUNT(DISTINCT ArtistID) from CDs;     
    356       # 
    357       # In order to get  the number of artists we execute the following statement 
    358       #  
    359       #   SELECT COUNT(ArtistID) FROM (SELECT DISTINCT ArtistID FROM CDs); 
    360       def execute(sql, name = nil) #:nodoc: 
    361         super(rewrite_count_distinct_queries(sql), name) 
    362       end 
    363        
    364       def rewrite_count_distinct_queries(sql) 
    365         if sql =~ /count\(distinct ([^\)]+)\)( AS \w+)? (.*)/i 
    366           distinct_column = $1 
    367           distinct_query  = $3 
    368           column_name     = distinct_column.split('.').last 
    369           "SELECT COUNT(#{column_name}) FROM (SELECT DISTINCT #{distinct_column} #{distinct_query})" 
    370         else 
    371           sql 
    372         end 
    373       end 
    374        
     357      def supports_count_distinct? #:nodoc: 
     358        false 
     359      end 
     360 
    375361      def rename_table(name, new_name) 
    376362        move_table(name, new_name) 
    377363      end 
    378        
     364 
    379365      def add_column(table_name, column_name, type, options = {}) #:nodoc: 
    380366        alter_table(table_name) do |definition| 
    …… 
    382368        end 
    383369      end 
    384        
    385370    end 
    386371 

  • trunk/activerecord/Rakefile

    r5181 r5426  
    2424 
    2525desc "Default Task" 
    26 task :default => [ :test_mysql, :test_sqlite, :test_postgresql ] 
     26task :default => [ :test_mysql, :test_sqlite, :test_sqlite3, :test_postgresql ] 
    2727 
    2828# Run the unit tests 

  • trunk/activerecord/test/base_test.rb

    r5416 r5426  
    12201220    assert_equal res4, res5  
    12211221 
    1222     res6 = Post.count_by_sql "SELECT COUNT(DISTINCT p.id) FROM posts p, comments co WHERE p.#{QUOTED_TYPE} = 'Post' AND p.id=co.post_id" 
    1223     res7 = nil 
    1224     assert_nothing_raised do 
    1225       res7 = Post.count(:conditions => "p.#{QUOTED_TYPE} = 'Post' AND p.id=co.post_id", 
    1226                         :joins => "p, comments co", 
    1227                         :select => "p.id", 
    1228                         :distinct => true) 
    1229     end 
    1230     assert_equal res6, res7 
     1222    unless current_adapter?(:SQLite2Adapter, :DeprecatedSQLiteAdapter) 
     1223      res6 = Post.count_by_sql "SELECT COUNT(DISTINCT p.id) FROM posts p, comments co WHERE p.#{QUOTED_TYPE} = 'Post' AND p.id=co.post_id" 
     1224      res7 = nil 
     1225      assert_nothing_raised do 
     1226        res7 = Post.count(:conditions => "p.#{QUOTED_TYPE} = 'Post' AND p.id=co.post_id", 
     1227                          :joins => "p, comments co", 
     1228                          :select => "p.id", 
     1229                          :distinct => true) 
     1230      end 
     1231      assert_equal res6, res7 
     1232    end 
    12311233  end 
    12321234   

  • trunk/activerecord/test/connections/native_sqlite/connection.rb

    r4602 r5426  
    1111sqlite_test_db2 = "#{BASE_DIR}/fixture_database_2.sqlite" 
    1212 
    13 def make_connection(clazz, db_file, db_definitions_file) 
     13def make_connection(clazz, db_file) 
    1414  ActiveRecord::Base.configurations = { clazz.name => { :adapter => 'sqlite', :database => db_file } } 
    1515  unless File.exist?(db_file) 
    …… 
    1818    puts "Executing '#{sqlite_command}'" 
    1919    raise SqliteError.new("Seems that there is no sqlite executable available") unless system(sqlite_command) 
    20     clazz.establish_connection(clazz.name) 
    21     script = File.read("#{BASE_DIR}/db_definitions/#{db_definitions_file}") 
    22     # SQLite-Ruby has problems with semi-colon separated commands, so split and execute one at a time 
    23     script.split(';').each do 
    24       |command| 
    25       clazz.connection.execute(command) unless command.strip.empty? 
    26     end 
    27   else 
    28     clazz.establish_connection(clazz.name) 
    2920  end 
     21  clazz.establish_connection(clazz.name) 
    3022end 
    3123 
    32 make_connection(ActiveRecord::Base, sqlite_test_db, 'sqlite.sql') 
    33 make_connection(Course, sqlite_test_db2, 'sqlite2.sql') 
    34 load(File.join(BASE_DIR, 'db_definitions', 'schema.rb')) 
     24make_connection(ActiveRecord::Base, sqlite_test_db) 
     25make_connection(Course, sqlite_test_db2) 

  • trunk/activerecord/test/connections/native_sqlite3/connection.rb

    r5258 r5426  
    1111sqlite_test_db2 = "#{BASE_DIR}/fixture_database_2.sqlite3" 
    1212 
    13 def make_connection(clazz, db_file, db_definitions_file) 
     13def make_connection(clazz, db_file) 
    1414  ActiveRecord::Base.configurations = { clazz.name => { :adapter => 'sqlite3', :database => db_file, :timeout => 5000 } } 
    1515  unless File.exist?(db_file) 
    …… 
    1818    puts "Executing '#{sqlite_command}'" 
    1919    raise SqliteError.new("Seems that there is no sqlite3 executable available") unless system(sqlite_command) 
    20     clazz.establish_connection(clazz.name) 
    21     script = File.read("#{BASE_DIR}/db_definitions/#{db_definitions_file}") 
    22     # SQLite-Ruby has problems with semi-colon separated commands, so split and execute one at a time 
    23     script.split(';').each do 
    24       |command| 
    25       clazz.connection.execute(command) unless command.strip.empty? 
    26     end 
    27   else 
    28     clazz.establish_connection(clazz.name) 
    2920  end 
     21  clazz.establish_connection(clazz.name) 
    3022end 
    3123 
    32 make_connection(ActiveRecord::Base, sqlite_test_db, 'sqlite.sql') 
    33 make_connection(Course, sqlite_test_db2, 'sqlite2.sql') 
    34 load(File.join(BASE_DIR, 'db_definitions', 'schema.rb')) 
     24make_connection(ActiveRecord::Base, sqlite_test_db) 
     25make_connection(Course, sqlite_test_db2) 

  • trunk/activerecord/test/migration_test.rb

    r5260 r5426  
    166166    end 
    167167 
    168     # SQL Server and Sybase will not allow you to add a NOT NULL column 
    169     # to a table without specifying a default value, so the 
    170     # following test must be skipped 
    171     unless current_adapter?(:SQLServerAdapter, :SybaseAdapter) 
     168    # SQL Server, Sybase, and SQLite3 will not allow you to add a NOT NULL 
     169    # column to a table without a default value. 
     170    unless current_adapter?(:SQLServerAdapter, :SybaseAdapter, :SQLiteAdapter) 
    172171      def test_add_column_not_null_without_default 
    173172        Person.connection.create_table :testings do |t|