summaryrefslogtreecommitdiff
authoreilers <eilers>2005-03-20 17:47:23 (UTC)
committer eilers <eilers>2005-03-20 17:47:23 (UTC)
commit22d113c0b0dc0a9a71cb55f565c4df04272809e1 (patch) (unidiff)
tree4decc50e15c93f1c6afc9131e3124c4f11115475
parent5bc93a55c05c7292502c908fc0a99d5010a641c7 (diff)
downloadopie-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..
Diffstat (more/less context) (show whitespace changes)
-rw-r--r--libopie2/opiepim/ChangeLog4
-rw-r--r--libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp42
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,4 +1,6 @@
12005-03.19 Stefan Eilers <stefan@eilers-online.net> 12005-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.
32005-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.
3 * Fixing uninitialized member variable, caused crash of backend 5 * Fixing uninitialized member variable, caused crash of backend
42005-03-18 Stefan Eilers <stefan@eilers-online.net> 62005-03-18 Stefan Eilers <stefan@eilers-online.net>
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
@@ -548,6 +548,7 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
548 } 548 }
549 549
550 QString searchQuery =""; 550 QString searchQuery ="";
551 QString temp_searchQuery = "";
551 552
552 QDate startDate; 553 QDate startDate;
553 554
@@ -585,20 +586,29 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
585 endDate = new QDate( query.anniversary() ); 586 endDate = new QDate( query.anniversary() );
586 587
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' ) )
592 .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) ) 602 .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) )
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' ) )
596 .arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ; 606 .arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ;
597 } 607 }
598 608
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
603 searchQuery += QString( " (\"%1\" LIKE '%2-%')" ) 613 searchQuery += QString( " (\"%1\" LIKE '%2-%')" )
604 .arg( *it ) 614 .arg( *it )
@@ -643,11 +653,20 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
643 653
644 delete endDate; 654 delete endDate;
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
652 odebug << "queryByExample query: " << qu << "" << oendl; 671 odebug << "queryByExample query: " << qu << "" << oendl;
653 672
@@ -661,6 +680,13 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
661 680
662 UIDArray list = extractUids( res ); 681 UIDArray list = extractUids( res );
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;
665} 691}
666 692