author | eilers <eilers> | 2005-03-20 17:47:23 (UTC) |
---|---|---|
committer | eilers <eilers> | 2005-03-20 17:47:23 (UTC) |
commit | 22d113c0b0dc0a9a71cb55f565c4df04272809e1 (patch) (unidiff) | |
tree | 4decc50e15c93f1c6afc9131e3124c4f11115475 | |
parent | 5bc93a55c05c7292502c908fc0a99d5010a641c7 (diff) | |
download | opie-22d113c0b0dc0a9a71cb55f565c4df04272809e1.zip opie-22d113c0b0dc0a9a71cb55f565c4df04272809e1.tar.gz opie-22d113c0b0dc0a9a71cb55f565c4df04272809e1.tar.bz2 |
Fixing #1608, second issue: QueryByExample-Datediff does not work properly.
This solution is not the best. I will rethink it as I have enough time. But it should
work..
-rw-r--r-- | libopie2/opiepim/ChangeLog | 4 | ||||
-rw-r--r-- | libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | 42 |
2 files changed, 37 insertions, 9 deletions
diff --git a/libopie2/opiepim/ChangeLog b/libopie2/opiepim/ChangeLog index 84517fb..acb6cb1 100644 --- a/libopie2/opiepim/ChangeLog +++ b/libopie2/opiepim/ChangeLog | |||
@@ -1,2 +1,4 @@ | |||
1 | 2005-03.19 Stefan Eilers <stefan@eilers-online.net> | 1 | 2005-03-20 Stefan Eilers <stefan@eilers-online.net> |
2 | * #1608 Quickfix for problem with DateDiff on SQL backend. I have to rethink this solution, but due to the short time, this should work. | ||
3 | 2005-03-19 Stefan Eilers <stefan@eilers-online.net> | ||
2 | * Minor update for sorted(). Now ignoring any category search if "DoNotShowWithCategory" filter is activated. | 4 | * Minor update for sorted(). Now ignoring any category search if "DoNotShowWithCategory" filter is activated. |
diff --git a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp index 2368865..50421e2 100644 --- a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp +++ b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | |||
@@ -550,2 +550,3 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
550 | QString searchQuery =""; | 550 | QString searchQuery =""; |
551 | QString temp_searchQuery = ""; | ||
551 | 552 | ||
@@ -587,5 +588,14 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
587 | if ( settings & OPimContactAccess::DateDiff ) { | 588 | if ( settings & OPimContactAccess::DateDiff ) { |
588 | searchQuery += QString( " (\"%1\" <= '%2-%3-%4\' AND \"%5\" >= '%6-%7-%8')" ) | 589 | // To handle datediffs correctly, we need to remove the year information from |
590 | // the birthday and anniversary. | ||
591 | // To do this efficiently, we will create a temporary table which contains the | ||
592 | // information we need and do the query on it. | ||
593 | // This table is just visible for this process and will be removed | ||
594 | // automatically after using. | ||
595 | temp_searchQuery = "CREATE TEMP TABLE bs ( uid, \"Birthday\", \"Anniversary\" );"; | ||
596 | temp_searchQuery += "INSERT INTO bs SELECT uid,substr(\"Birthday\", 6, 10),substr(\"Anniversary\", 6, 10) FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' );"; | ||
597 | |||
598 | temp_searchQuery += QString( "SELECT uid FROM bs WHERE (\"%1\" <= '%2-%3\' AND \"%4\" >= '%5-%6')" ) | ||
589 | .arg( *it ) | 599 | .arg( *it ) |
590 | .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ) | 600 | //.arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ) |
591 | .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) ) | 601 | .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) ) |
@@ -593,3 +603,3 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
593 | .arg( *it ) | 603 | .arg( *it ) |
594 | .arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) ) | 604 | //.arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) ) |
595 | .arg( QString::number( startDate.month() ).rightJustify( 2, '0' ) ) | 605 | .arg( QString::number( startDate.month() ).rightJustify( 2, '0' ) ) |
@@ -599,4 +609,4 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
599 | if ( settings & OPimContactAccess::DateYear ){ | 609 | if ( settings & OPimContactAccess::DateYear ){ |
600 | if ( settings & OPimContactAccess::DateDiff ) | 610 | // if ( settings & OPimContactAccess::DateDiff ) |
601 | searchQuery += " AND"; | 611 | // searchQuery += " AND"; |
602 | 612 | ||
@@ -645,7 +655,16 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
645 | } | 655 | } |
646 | // Skip trailing "AND" | ||
647 | // if ( isAnyFieldSelected ) | ||
648 | // qu = qu.left( qu.length() - 4 ); | ||
649 | 656 | ||
657 | // The following is very ugly! (eilers) | ||
658 | if ( !temp_searchQuery.isEmpty() && !searchQuery.isEmpty() ){ | ||
659 | // If we use DateDiff, we have to intersect two queries. | ||
660 | qu = temp_searchQuery + QString( " INTERSECT " ) + qu + searchQuery; | ||
661 | } else if ( temp_searchQuery.isEmpty() && !searchQuery.isEmpty() ){ | ||
650 | qu += searchQuery; | 662 | qu += searchQuery; |
663 | } else if ( !temp_searchQuery.isEmpty() && searchQuery.isEmpty() ){ | ||
664 | // This will cause wrong results!! Uid filter is not used here! | ||
665 | qu = temp_searchQuery; | ||
666 | } else if ( temp_searchQuery.isEmpty() && searchQuery.isEmpty() ){ | ||
667 | UIDArray empty; | ||
668 | return empty; | ||
669 | } | ||
651 | 670 | ||
@@ -663,2 +682,9 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
663 | 682 | ||
683 | // Remove temp table if created | ||
684 | if ( !temp_searchQuery.isEmpty( ) ){ | ||
685 | qu = "DROP TABLE bs"; | ||
686 | OSQLRawQuery raw( qu ); | ||
687 | OSQLResult res = m_driver->query( &raw ); | ||
688 | } | ||
689 | |||
664 | return list; | 690 | return list; |