summaryrefslogtreecommitdiff
Unidiff
Diffstat (more/less context) (ignore whitespace changes)
-rw-r--r--libopie2/opiepim/ChangeLog2
-rw-r--r--libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp62
2 files changed, 35 insertions, 29 deletions
diff --git a/libopie2/opiepim/ChangeLog b/libopie2/opiepim/ChangeLog
index acb6cb1..ee063aa 100644
--- a/libopie2/opiepim/ChangeLog
+++ b/libopie2/opiepim/ChangeLog
@@ -1,12 +1,14 @@
12005-03-23 Stefan Eilers <stefan@eilers-online.net>
2 * #1608 Finishing work on SQL backend. Datediff and other queries for QueryByExample now working as exprected. All tests passed successfully
12005-03-20 Stefan Eilers <stefan@eilers-online.net> 32005-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. 4 * #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> 52005-03-19 Stefan Eilers <stefan@eilers-online.net>
4 * Minor update for sorted(). Now ignoring any category search if "DoNotShowWithCategory" filter is activated. 6 * Minor update for sorted(). Now ignoring any category search if "DoNotShowWithCategory" filter is activated.
5 * Fixing uninitialized member variable, caused crash of backend 7 * Fixing uninitialized member variable, caused crash of backend
62005-03-18 Stefan Eilers <stefan@eilers-online.net> 82005-03-18 Stefan Eilers <stefan@eilers-online.net>
7 * Rewrote generic sorted filter and added filter for "DoNotShowWithCategory", needed by addressbook (other filters need to be added!) 9 * Rewrote generic sorted filter and added filter for "DoNotShowWithCategory", needed by addressbook (other filters need to be added!)
82005-01-16 Stefan Eilers <stefan@eilers-online.net> 102005-01-16 Stefan Eilers <stefan@eilers-online.net>
9 * Added new OPimEventSortVector class, improved OPimSortVector 11 * Added new OPimEventSortVector class, improved OPimSortVector
10 * OPimAccessBackend now supports generic sorting. 12 * OPimAccessBackend now supports generic sorting.
112005-01-03 Stefan Eilers <stefan@eilers-online.net> 132005-01-03 Stefan Eilers <stefan@eilers-online.net>
12 * Fixing bug in API documentation 14 * Fixing bug in API documentation
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
@@ -524,40 +524,40 @@ OPimContact OPimContactAccessBackend_SQL::find( int uid, const UIDArray& queryUi
524 searchList.resize( size ); 524 searchList.resize( size );
525 525
526 OPimContact retContact( requestContactsAndCache( uid, searchList ) ); 526 OPimContact retContact( requestContactsAndCache( uid, searchList ) );
527 527
528 odebug << "OPimContactAccessBackend_SQL::find( ..multi.. ) needed: " << t.elapsed() << " ms" << oendl; 528 odebug << "OPimContactAccessBackend_SQL::find( ..multi.. ) needed: " << t.elapsed() << " ms" << oendl;
529 return retContact; 529 return retContact;
530} 530}
531 531
532 532
533UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, const OPimContact &query, int settings, 533UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, const OPimContact &query, int settings,
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;
554 554
555 if ( qd.isValid() ) 555 if ( qd.isValid() )
556 startDate = qd.date(); 556 startDate = qd.date();
557 else 557 else
558 startDate = QDate::currentDate(); 558 startDate = QDate::currentDate();
559 559
560 560
561 QMap<int, QString> queryFields = query.toMap(); 561 QMap<int, QString> queryFields = query.toMap();
562 QStringList fieldList = OPimContactFields::untrfields( false ); 562 QStringList fieldList = OPimContactFields::untrfields( false );
563 QMap<QString, int> translate = OPimContactFields::untrFieldsToId(); 563 QMap<QString, int> translate = OPimContactFields::untrFieldsToId();
@@ -583,114 +583,118 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
583 // Fall through ! 583 // Fall through !
584 case Qtopia::Anniversary: 584 case Qtopia::Anniversary:
585 if ( endDate == 0l ) 585 if ( endDate == 0l )
586 endDate = new QDate( query.anniversary() ); 586 endDate = new QDate( query.anniversary() );
587 587
588 if ( settings & OPimContactAccess::DateDiff ) { 588 if ( settings & OPimContactAccess::DateDiff ) {
589 // To handle datediffs correctly, we need to remove the year information from 589 // To handle datediffs correctly, we need to remove the year information from
590 // the birthday and anniversary. 590 // the birthday and anniversary.
591 // To do this efficiently, we will create a temporary table which contains the 591 // To do this efficiently, we will create a temporary table which contains the
592 // information we need and do the query on it. 592 // information we need and do the query on it.
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' ) )
601 .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) ) 601 .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) )
602 .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) ) 602 .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) )
603 .arg( *it ) 603 .arg( *it )
604 //.arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) ) 604 //.arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) )
605 .arg( QString::number( startDate.month() ).rightJustify( 2, '0' ) ) 605 .arg( QString::number( startDate.month() ).rightJustify( 2, '0' ) )
606 .arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ; 606 .arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ;
607 } 607 }
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 )
615 .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ); 612 .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) );
616 } 613 }
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
623 searchQuery += QString( " (\"%1\" LIKE '%-%2-%')" ) 619 searchQuery += QString( " (\"%1\" LIKE '%-%2-%')" )
624 .arg( *it ) 620 .arg( *it )
625 .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) ); 621 .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) );
626 } 622 }
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";
633 628
634 searchQuery += QString( " (\"%1\" LIKE '%-%-%2')" ) 629 searchQuery += QString( " (\"%1\" LIKE '%-%-%2')" )
635 .arg( *it ) 630 .arg( *it )
636 .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) ); 631 .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) );
637 } 632 }
638 633
639 break; 634 break;
640 default: 635 default:
641 // Switching between case sensitive and insensitive... 636 // Switching between case sensitive and insensitive...
642 // LIKE is not case sensitive, GLOB is case sensitive 637 // LIKE is not case sensitive, GLOB is case sensitive
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
651 } 646 }
652 } 647 }
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;
669 } 673 }
670 674
671 odebug << "queryByExample query: " << qu << "" << oendl; 675 odebug << "queryByExample query: " << qu << "" << oendl;
672 676
673 // Execute query and return the received uid's 677 // Execute query and return the received uid's
674 OSQLRawQuery raw( qu ); 678 OSQLRawQuery raw( qu );
675 OSQLResult res = m_driver->query( &raw ); 679 OSQLResult res = m_driver->query( &raw );
676 if ( res.state() != OSQLResult::Success ){ 680 if ( res.state() != OSQLResult::Success ){
677 UIDArray empty; 681 UIDArray empty;
678 return empty; 682 return empty;
679 } 683 }
680 684
681 UIDArray list = extractUids( res ); 685 UIDArray list = extractUids( res );
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 );
687 OSQLResult res = m_driver->query( &raw ); 691 OSQLResult res = m_driver->query( &raw );
688 } 692 }
689 693
690 return list; 694 return list;
691} 695}
692 696
693UIDArray OPimContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const 697UIDArray OPimContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const
694{ 698{
695#if 0 699#if 0
696 QArray<int> nix(0); 700 QArray<int> nix(0);