jro_apps/redmine Commit - r2052:9e1192a54de6

Fixed date filters accuracy with SQLite (#2221)....

Jean-Philippe Lang -

r2052:9e1192a54de6

parent child

Context file:

r2052:9e1192a54de6

Collapse all files

app/models/query.rb +19 -7

                   when "c"
                     sql = sql + "#{IssueStatus.table_name}.is_closed=#{connection.quoted_true}" if field == "status_id"
                   when ">t-"
-                    sql = sql + "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date((Date.today - v.first.to_i).to_time), connection.quoted_date((Date.today + 1).to_time)]
+                    sql = sql + date_range_clause(db_table, db_field, - v.first.to_i, 0)
                   when "<t-"
-                    sql = sql + "#{db_table}.#{db_field} <= '%s'" % connection.quoted_date((Date.today - v.first.to_i).to_time)
+                    sql = sql + date_range_clause(db_table, db_field, nil, - v.first.to_i)
                   when "t-"
-                    sql = sql + "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date((Date.today - v.first.to_i).to_time), connection.quoted_date((Date.today - v.first.to_i + 1).to_time)]
+                    sql = sql + date_range_clause(db_table, db_field, - v.first.to_i, - v.first.to_i)
                   when ">t+"
-                    sql = sql + "#{db_table}.#{db_field} >= '%s'" % connection.quoted_date((Date.today + v.first.to_i).to_time)
+                    sql = sql + date_range_clause(db_table, db_field, v.first.to_i, nil)
                   when "<t+"
-                    sql = sql + "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(Date.today.to_time), connection.quoted_date((Date.today + v.first.to_i + 1).to_time)]
+                    sql = sql + date_range_clause(db_table, db_field, 0, v.first.to_i)
                   when "t+"
-                    sql = sql + "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date((Date.today + v.first.to_i).to_time), connection.quoted_date((Date.today + v.first.to_i + 1).to_time)]
+                    sql = sql + date_range_clause(db_table, db_field, v.first.to_i, v.first.to_i)
                   when "t"
-                    sql = sql + "#{db_table}.#{db_field} BETWEEN '%s' AND '%s'" % [connection.quoted_date(Date.today.to_time), connection.quoted_date((Date.today+1).to_time)]
+                    sql = sql + date_range_clause(db_table, db_field, 0, 0)
                   when "w"
                     from = l(:general_first_day_of_week) == '7' ?
                       # week starts on sunday
                   @available_filters["cf_#{field.id}"] = options.merge({ :name => field.name })
                 end
               end
+              # Returns a SQL clause for a date or datetime field.
+              def date_range_clause(table, field, from, to)
+                s = []
+                if from
+                  s << ("#{table}.#{field} > '%s'" % [connection.quoted_date((Date.yesterday + from).to_time.end_of_day)])
+                end
+                if to
+                  s << ("#{table}.#{field} <= '%s'" % [connection.quoted_date((Date.today + to).to_time.end_of_day)])
+                end
+                s.join(' AND ')
+              end
             end

test/fixtures/issues.yml +17 0

@@ -91,4 +91,21 issues_006:
91	status_id: 1	91	status_id: 1
92	start_date: <%= Date.today.to_s(:db) %>	92	start_date: <%= Date.today.to_s(:db) %>
93	due_date: <%= 1.days.from_now.to_date.to_s(:db) %>	93	due_date: <%= 1.days.from_now.to_date.to_s(:db) %>
		94	issues_007:
		95	created_on: <%= 10.days.ago.to_date.to_s(:db) %>
		96	project_id: 1
		97	updated_on: <%= 10.days.ago.to_date.to_s(:db) %>
		98	priority_id: 3
		99	subject: Issue due today
		100	id: 7
		101	fixed_version_id:
		102	category_id:
		103	description: This is an issue that is due today
		104	tracker_id: 1
		105	assigned_to_id:
		106	author_id: 2
		107	status_id: 1
		108	start_date: <%= 10.days.ago.to_s(:db) %>
		109	due_date: <%= Date.today.to_s(:db) %>
		110	lock_version: 0
94	No newline at end of file	111

test/unit/query_test.rb +48 -9

             require File.dirname(__FILE__) + '/../test_helper'
             class QueryTest < Test::Unit::TestCase
-              fixtures :projects, :users, :members, :roles, :trackers, :issue_statuses, :issue_categories, :enumerations, :issues, :custom_fields, :custom_values, :queries
+              fixtures :projects, :enabled_modules, :users, :members, :roles, :trackers, :issue_statuses, :issue_categories, :enumerations, :issues, :custom_fields, :custom_values, :versions, :queries
               def test_custom_fields_for_all_projects_should_be_available_in_global_queries
                 query = Query.new(:project => nil, :name => '_')
               end
               def test_operator_in_more_than
+                Issue.find(7).update_attribute(:due_date, (Date.today + 15))
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', '>t+', ['15'])
-                assert query.statement.include?("#{Issue.table_name}.due_date >=")
+                issues = find_issues_with_query(query)
-                find_issues_with_query(query)
+                assert !issues.empty?
+                issues.each {|issue| assert(issue.due_date >= (Date.today + 15))}
               end
               def test_operator_in_less_than
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', '<t+', ['15'])
-                assert query.statement.include?("#{Issue.table_name}.due_date BETWEEN")
+                issues = find_issues_with_query(query)
-                find_issues_with_query(query)
+                assert !issues.empty?
+                issues.each {|issue| assert(issue.due_date >= Date.today && issue.due_date <= (Date.today + 15))}
+              end
+              def test_operator_less_than_ago
+                Issue.find(7).update_attribute(:due_date, (Date.today - 3))
+                query = Query.new(:project => Project.find(1), :name => '_')
+                query.add_filter('due_date', '>t-', ['3'])
+                issues = find_issues_with_query(query)
+                assert !issues.empty?
+                issues.each {|issue| assert(issue.due_date >= (Date.today - 3) && issue.due_date <= Date.today)}
+              end
+              def test_operator_more_than_ago
+                Issue.find(7).update_attribute(:due_date, (Date.today - 10))
+                query = Query.new(:project => Project.find(1), :name => '_')
+                query.add_filter('due_date', '<t-', ['10'])
+                assert query.statement.include?("#{Issue.table_name}.due_date <=")
+                issues = find_issues_with_query(query)
+                assert !issues.empty?
+                issues.each {|issue| assert(issue.due_date <= (Date.today - 10))}
+              end
+              def test_operator_in
+                Issue.find(7).update_attribute(:due_date, (Date.today + 2))
+                query = Query.new(:project => Project.find(1), :name => '_')
+                query.add_filter('due_date', 't+', ['2'])
+                issues = find_issues_with_query(query)
+                assert !issues.empty?
+                issues.each {|issue| assert_equal((Date.today + 2), issue.due_date)}
+              end
+              def test_operator_ago
+                Issue.find(7).update_attribute(:due_date, (Date.today - 3))
+                query = Query.new(:project => Project.find(1), :name => '_')
+                query.add_filter('due_date', 't-', ['3'])
+                issues = find_issues_with_query(query)
+                assert !issues.empty?
+                issues.each {|issue| assert_equal((Date.today - 3), issue.due_date)}
               end
               def test_operator_today
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', 't', [''])
-                assert query.statement.include?("#{Issue.table_name}.due_date BETWEEN")
+                issues = find_issues_with_query(query)
-                find_issues_with_query(query)
+                assert !issues.empty?
+                issues.each {|issue| assert_equal Date.today, issue.due_date}
               end
               def test_operator_this_week_on_date
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('due_date', 'w', [''])
-                assert query.statement.include?("#{Issue.table_name}.due_date BETWEEN")
                 find_issues_with_query(query)
               end
               def test_operator_this_week_on_datetime
                 query = Query.new(:project => Project.find(1), :name => '_')
                 query.add_filter('created_on', 'w', [''])
-                assert query.statement.include?("#{Issue.table_name}.created_on BETWEEN")
                 find_issues_with_query(query)
               end

General Comments 0

Write
Preview

You need to be logged in to leave comments. Login now

No TODOs yet

	Site-wide shortcuts
/	Use quick search box
g h	Goto home page
g g	Goto my private gists page
g G	Goto my public gists page
g 0-9	Goto bookmarked items from 0-9
n r	New repository page
n g	New gist page

	Repositories
g s	Goto summary page
g c	Goto changelog page
g f	Goto files page
g F	Goto files page with file search activated
g p	Goto pull requests page
g o	Goto repository settings
g O	Goto repository access permissions settings
t s	Toggle sidebar on some pages