author | eilers <eilers> | 2005-03-23 09:25:18 (UTC) |
---|---|---|
committer | eilers <eilers> | 2005-03-23 09:25:18 (UTC) |
commit | cd1e107bcc03cbe2ff5179d4367225e4b0e47005 (patch) (unidiff) | |
tree | 0d748542c77a491c68a610b2f611981278133d3b /libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | |
parent | 7d82b94d669746cac36dcabf026428bdc9286c72 (diff) | |
download | opie-cd1e107bcc03cbe2ff5179d4367225e4b0e47005.zip opie-cd1e107bcc03cbe2ff5179d4367225e4b0e47005.tar.gz opie-cd1e107bcc03cbe2ff5179d4367225e4b0e47005.tar.bz2 |
Fixing last issues on QueryByExample on SQL databases. All tests passed successfully
Diffstat (limited to 'libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp') (more/less context) (ignore whitespace changes)
-rw-r--r-- | libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | 62 |
1 files changed, 33 insertions, 29 deletions
diff --git a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp index 50421e2..175d62a 100644 --- a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp +++ b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | |||
@@ -534,20 +534,20 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
534 | const QDateTime& qd ) const | 534 | const QDateTime& qd ) const |
535 | { | 535 | { |
536 | QString qu = "SELECT uid FROM addressbook WHERE"; | 536 | QString searchQuery = ""; |
537 | QString datediff_query = ""; | ||
538 | QString uid_query = ""; | ||
537 | 539 | ||
538 | // Just add uid's selection if we really try to search in a subset of all uids! Otherwise this would | 540 | // Just add uid's selection if we really try to search in a subset of all uids! Otherwise this would |
539 | // just take time and memory! | 541 | // just take time and memory! |
540 | if ( uidlist.count() != m_uids.count() ) { | 542 | if ( uidlist.count() != m_uids.count() ) { |
541 | qu += " ("; | 543 | uid_query += " ("; |
542 | 544 | ||
543 | for ( uint i = 0; i < uidlist.count(); i++ ) { | 545 | for ( uint i = 0; i < uidlist.count(); i++ ) { |
544 | qu += " uid = " + QString::number( uidlist[i] ) + " OR"; | 546 | uid_query += " uid = " + QString::number( uidlist[i] ) + " OR"; |
545 | } | 547 | } |
546 | qu.remove( qu.length()-2, 2 ); // Hmmmm.. | 548 | uid_query.remove( uid_query.length()-2, 2 ); // Hmmmm.. |
547 | qu += " ) AND "; | 549 | uid_query += " ) AND "; |
548 | } | 550 | } |
549 | 551 | ||
550 | QString searchQuery = ""; | ||
551 | QString temp_searchQuery = ""; | ||
552 | 552 | ||
553 | QDate startDate; | 553 | QDate startDate; |
@@ -593,8 +593,8 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
593 | // This table is just visible for this process and will be removed | 593 | // This table is just visible for this process and will be removed |
594 | // automatically after using. | 594 | // automatically after using. |
595 | temp_searchQuery = "CREATE TEMP TABLE bs ( uid, \"Birthday\", \"Anniversary\" );"; | 595 | datediff_query = "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\" != '' );"; | 596 | datediff_query += "INSERT INTO bs SELECT uid,substr(\"Birthday\", 6, 10),substr(\"Anniversary\", 6, 10) FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' );"; |
597 | 597 | datediff_query += QString( "SELECT uid FROM bs WHERE " ) + uid_query; | |
598 | temp_searchQuery += QString( "SELECT uid FROM bs WHERE (\"%1\" <= '%2-%3\' AND \"%4\" >= '%5-%6')" ) | 598 | datediff_query += QString( " (\"%1\" <= '%2-%3\' AND \"%4\" >= '%5-%6')" ) |
599 | .arg( *it ) | 599 | .arg( *it ) |
600 | //.arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ) | 600 | //.arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ) |
@@ -608,7 +608,4 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
608 | 608 | ||
609 | if ( settings & OPimContactAccess::DateYear ){ | 609 | if ( settings & OPimContactAccess::DateYear ){ |
610 | // if ( settings & OPimContactAccess::DateDiff ) | ||
611 | // searchQuery += " AND"; | ||
612 | |||
613 | searchQuery += QString( " (\"%1\" LIKE '%2-%')" ) | 610 | searchQuery += QString( " (\"%1\" LIKE '%2-%')" ) |
614 | .arg( *it ) | 611 | .arg( *it ) |
@@ -617,6 +614,5 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
617 | 614 | ||
618 | if ( settings & OPimContactAccess::DateMonth ){ | 615 | if ( settings & OPimContactAccess::DateMonth ){ |
619 | if ( ( settings & OPimContactAccess::DateDiff ) | 616 | if ( settings & OPimContactAccess::DateYear ) |
620 | || ( settings & OPimContactAccess::DateYear ) ) | ||
621 | searchQuery += " AND"; | 617 | searchQuery += " AND"; |
622 | 618 | ||
@@ -627,6 +623,5 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
627 | 623 | ||
628 | if ( settings & OPimContactAccess::DateDay ){ | 624 | if ( settings & OPimContactAccess::DateDay ){ |
629 | if ( ( settings & OPimContactAccess::DateDiff ) | 625 | if ( ( settings & OPimContactAccess::DateYear ) |
630 | || ( settings & OPimContactAccess::DateYear ) | ||
631 | || ( settings & OPimContactAccess::DateMonth ) ) | 626 | || ( settings & OPimContactAccess::DateMonth ) ) |
632 | searchQuery += " AND"; | 627 | searchQuery += " AND"; |
@@ -643,8 +638,8 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
643 | // Do exist a better solution to switch this ? | 638 | // Do exist a better solution to switch this ? |
644 | if ( settings & OPimContactAccess::IgnoreCase ) | 639 | if ( settings & OPimContactAccess::IgnoreCase ) |
645 | searchQuery += "(\"" + *it + "\"" + " LIKE " + "'" | 640 | searchQuery += " (\"" + *it + "\"" + " LIKE " + "'" |
646 | + queryStr.replace(QRegExp("\\*"),"%") + "'" + ")"; | 641 | + queryStr.replace(QRegExp("\\*"),"%") + "'" + ")"; |
647 | else | 642 | else |
648 | searchQuery += "(\"" + *it + "\"" + " GLOB " + "'" | 643 | searchQuery += " (\"" + *it + "\"" + " GLOB " + "'" |
649 | + queryStr + "'" + ")"; | 644 | + queryStr + "'" + ")"; |
650 | 645 | ||
@@ -653,16 +648,25 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
653 | 648 | ||
654 | delete endDate; | 649 | delete endDate; |
650 | |||
651 | // The following if line is a replacement for | ||
652 | // if ( searchQuery.endsWith( "AND" ) ) | ||
653 | if ( searchQuery.findRev( "AND" ) == ( searchQuery.length() - 3 ) ){ | ||
654 | odebug << "remove AND" << oendl; | ||
655 | searchQuery.remove( searchQuery.length()-3, 3 ); // Hmmmm.. | ||
656 | } | ||
657 | |||
655 | } | 658 | } |
656 | 659 | ||
657 | // The following is very ugly! (eilers) | 660 | // Now compose the complete query |
658 | if ( !temp_searchQuery.isEmpty() && !searchQuery.isEmpty() ){ | 661 | QString qu = "SELECT uid FROM addressbook WHERE " + uid_query; |
662 | |||
663 | if ( !datediff_query.isEmpty() && !searchQuery.isEmpty() ){ | ||
659 | // If we use DateDiff, we have to intersect two queries. | 664 | // If we use DateDiff, we have to intersect two queries. |
660 | qu = temp_searchQuery + QString( " INTERSECT " ) + qu + searchQuery; | 665 | qu = datediff_query + QString( " INTERSECT " ) + qu + searchQuery; |
661 | } else if ( temp_searchQuery.isEmpty() && !searchQuery.isEmpty() ){ | 666 | } else if ( datediff_query.isEmpty() && !searchQuery.isEmpty() ){ |
662 | qu += searchQuery; | 667 | qu += searchQuery; |
663 | } else if ( !temp_searchQuery.isEmpty() && searchQuery.isEmpty() ){ | 668 | } else if ( !datediff_query.isEmpty() && searchQuery.isEmpty() ){ |
664 | // This will cause wrong results!! Uid filter is not used here! | 669 | qu = datediff_query; |
665 | qu = temp_searchQuery; | 670 | } else if ( datediff_query.isEmpty() && searchQuery.isEmpty() ){ |
666 | } else if ( temp_searchQuery.isEmpty() && searchQuery.isEmpty() ){ | ||
667 | UIDArray empty; | 671 | UIDArray empty; |
668 | return empty; | 672 | return empty; |
@@ -682,5 +686,5 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
682 | 686 | ||
683 | // Remove temp table if created | 687 | // Remove temp table if created |
684 | if ( !temp_searchQuery.isEmpty( ) ){ | 688 | if ( !datediff_query.isEmpty( ) ){ |
685 | qu = "DROP TABLE bs"; | 689 | qu = "DROP TABLE bs"; |
686 | OSQLRawQuery raw( qu ); | 690 | OSQLRawQuery raw( qu ); |