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	91	status_id: 1
92	92	start_date: <%= Date.today.to_s(:db) %>
93	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	111	No newline at end of file

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 >=")
-                 find_issues_with_query(query)
+                 issues = 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")
-                 find_issues_with_query(query)
+                 issues = 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")
-                 find_issues_with_query(query)
+                 issues = 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