-rw-r--r-- | libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | 13 |
1 files changed, 2 insertions, 11 deletions
diff --git a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp index 175d62a..6aaa14c 100644 --- a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp +++ b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | |||
@@ -547,195 +547,186 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, | |||
547 | } | 547 | } |
548 | uid_query.remove( uid_query.length()-2, 2 ); // Hmmmm.. | 548 | uid_query.remove( uid_query.length()-2, 2 ); // Hmmmm.. |
549 | uid_query += " ) AND "; | 549 | uid_query += " ) AND "; |
550 | } | 550 | } |
551 | 551 | ||
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(); |
564 | 564 | ||
565 | // Convert every filled field to a SQL-Query | 565 | // Convert every filled field to a SQL-Query |
566 | // bool isAnyFieldSelected = false; | 566 | // bool isAnyFieldSelected = false; |
567 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | 567 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ |
568 | 568 | ||
569 | int id = translate[*it]; | 569 | int id = translate[*it]; |
570 | QString queryStr = queryFields[id]; | 570 | QString queryStr = queryFields[id]; |
571 | QDate* endDate = 0l; | 571 | QDate* endDate = 0l; |
572 | 572 | ||
573 | if ( !queryStr.isEmpty() ){ | 573 | if ( !queryStr.isEmpty() ){ |
574 | // If something is alredy stored in the query, add an "AND" | 574 | // If something is alredy stored in the query, add an "AND" |
575 | // to the end of the string to prepare for the next .. | 575 | // to the end of the string to prepare for the next .. |
576 | if ( !searchQuery.isEmpty() ) | 576 | if ( !searchQuery.isEmpty() ) |
577 | searchQuery += " AND"; | 577 | searchQuery += " AND"; |
578 | 578 | ||
579 | // isAnyFieldSelected = true; | 579 | // isAnyFieldSelected = true; |
580 | switch( id ){ | 580 | switch( id ){ |
581 | case Qtopia::Birthday: | 581 | case Qtopia::Birthday: |
582 | endDate = new QDate( query.birthday() ); | 582 | endDate = new QDate( query.birthday() ); |
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 | datediff_query = "CREATE TEMP TABLE bs ( uid, \"Birthday\", \"Anniversary\" );"; | 595 | datediff_query = "SELECT uid,substr(\"Birthday\", 6, 10) as \"BirthdayMD\", substr(\"Anniversary\", 6, 10) as \"AnniversaryMD\" FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' ) AND "; |
596 | datediff_query += "INSERT INTO bs SELECT uid,substr(\"Birthday\", 6, 10),substr(\"Anniversary\", 6, 10) FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' );"; | 596 | datediff_query += QString( " (\"%1MD\" <= '%2-%3\' AND \"%4MD\" >= '%5-%6')" ) |
597 | datediff_query += QString( "SELECT uid FROM bs WHERE " ) + uid_query; | ||
598 | datediff_query += QString( " (\"%1\" <= '%2-%3\' AND \"%4\" >= '%5-%6')" ) | ||
599 | .arg( *it ) | 597 | .arg( *it ) |
600 | //.arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ) | 598 | //.arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ) |
601 | .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) ) | 599 | .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) ) |
602 | .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) ) | 600 | .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) ) |
603 | .arg( *it ) | 601 | .arg( *it ) |
604 | //.arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) ) | 602 | //.arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) ) |
605 | .arg( QString::number( startDate.month() ).rightJustify( 2, '0' ) ) | 603 | .arg( QString::number( startDate.month() ).rightJustify( 2, '0' ) ) |
606 | .arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ; | 604 | .arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ; |
607 | } | 605 | } |
608 | 606 | ||
609 | if ( settings & OPimContactAccess::DateYear ){ | 607 | if ( settings & OPimContactAccess::DateYear ){ |
610 | searchQuery += QString( " (\"%1\" LIKE '%2-%')" ) | 608 | searchQuery += QString( " (\"%1\" LIKE '%2-%')" ) |
611 | .arg( *it ) | 609 | .arg( *it ) |
612 | .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ); | 610 | .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ); |
613 | } | 611 | } |
614 | 612 | ||
615 | if ( settings & OPimContactAccess::DateMonth ){ | 613 | if ( settings & OPimContactAccess::DateMonth ){ |
616 | if ( settings & OPimContactAccess::DateYear ) | 614 | if ( settings & OPimContactAccess::DateYear ) |
617 | searchQuery += " AND"; | 615 | searchQuery += " AND"; |
618 | 616 | ||
619 | searchQuery += QString( " (\"%1\" LIKE '%-%2-%')" ) | 617 | searchQuery += QString( " (\"%1\" LIKE '%-%2-%')" ) |
620 | .arg( *it ) | 618 | .arg( *it ) |
621 | .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) ); | 619 | .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) ); |
622 | } | 620 | } |
623 | 621 | ||
624 | if ( settings & OPimContactAccess::DateDay ){ | 622 | if ( settings & OPimContactAccess::DateDay ){ |
625 | if ( ( settings & OPimContactAccess::DateYear ) | 623 | if ( ( settings & OPimContactAccess::DateYear ) |
626 | || ( settings & OPimContactAccess::DateMonth ) ) | 624 | || ( settings & OPimContactAccess::DateMonth ) ) |
627 | searchQuery += " AND"; | 625 | searchQuery += " AND"; |
628 | 626 | ||
629 | searchQuery += QString( " (\"%1\" LIKE '%-%-%2')" ) | 627 | searchQuery += QString( " (\"%1\" LIKE '%-%-%2')" ) |
630 | .arg( *it ) | 628 | .arg( *it ) |
631 | .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) ); | 629 | .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) ); |
632 | } | 630 | } |
633 | 631 | ||
634 | break; | 632 | break; |
635 | default: | 633 | default: |
636 | // Switching between case sensitive and insensitive... | 634 | // Switching between case sensitive and insensitive... |
637 | // LIKE is not case sensitive, GLOB is case sensitive | 635 | // LIKE is not case sensitive, GLOB is case sensitive |
638 | // Do exist a better solution to switch this ? | 636 | // Do exist a better solution to switch this ? |
639 | if ( settings & OPimContactAccess::IgnoreCase ) | 637 | if ( settings & OPimContactAccess::IgnoreCase ) |
640 | searchQuery += " (\"" + *it + "\"" + " LIKE " + "'" | 638 | searchQuery += " (\"" + *it + "\"" + " LIKE " + "'" |
641 | + queryStr.replace(QRegExp("\\*"),"%") + "'" + ")"; | 639 | + queryStr.replace(QRegExp("\\*"),"%") + "'" + ")"; |
642 | else | 640 | else |
643 | searchQuery += " (\"" + *it + "\"" + " GLOB " + "'" | 641 | searchQuery += " (\"" + *it + "\"" + " GLOB " + "'" |
644 | + queryStr + "'" + ")"; | 642 | + queryStr + "'" + ")"; |
645 | 643 | ||
646 | } | 644 | } |
647 | } | 645 | } |
648 | 646 | ||
649 | delete endDate; | 647 | delete endDate; |
650 | 648 | ||
651 | // The following if line is a replacement for | 649 | // The following if line is a replacement for |
652 | // if ( searchQuery.endsWith( "AND" ) ) | 650 | // if ( searchQuery.endsWith( "AND" ) ) |
653 | if ( searchQuery.findRev( "AND" ) == ( searchQuery.length() - 3 ) ){ | 651 | if ( searchQuery.findRev( "AND" ) == ( searchQuery.length() - 3 ) ){ |
654 | odebug << "remove AND" << oendl; | 652 | odebug << "remove AND" << oendl; |
655 | searchQuery.remove( searchQuery.length()-3, 3 ); // Hmmmm.. | 653 | searchQuery.remove( searchQuery.length()-3, 3 ); // Hmmmm.. |
656 | } | 654 | } |
657 | 655 | ||
658 | } | 656 | } |
659 | 657 | ||
660 | // Now compose the complete query | 658 | // Now compose the complete query |
661 | QString qu = "SELECT uid FROM addressbook WHERE " + uid_query; | 659 | QString qu = "SELECT uid FROM addressbook WHERE " + uid_query; |
662 | 660 | ||
663 | if ( !datediff_query.isEmpty() && !searchQuery.isEmpty() ){ | 661 | if ( !datediff_query.isEmpty() && !searchQuery.isEmpty() ){ |
664 | // If we use DateDiff, we have to intersect two queries. | 662 | // If we use DateDiff, we have to intersect two queries. |
665 | qu = datediff_query + QString( " INTERSECT " ) + qu + searchQuery; | 663 | qu = datediff_query + QString( " INTERSECT " ) + qu + searchQuery; |
666 | } else if ( datediff_query.isEmpty() && !searchQuery.isEmpty() ){ | 664 | } else if ( datediff_query.isEmpty() && !searchQuery.isEmpty() ){ |
667 | qu += searchQuery; | 665 | qu += searchQuery; |
668 | } else if ( !datediff_query.isEmpty() && searchQuery.isEmpty() ){ | 666 | } else if ( !datediff_query.isEmpty() && searchQuery.isEmpty() ){ |
669 | qu = datediff_query; | 667 | qu = datediff_query; |
670 | } else if ( datediff_query.isEmpty() && searchQuery.isEmpty() ){ | 668 | } else if ( datediff_query.isEmpty() && searchQuery.isEmpty() ){ |
671 | UIDArray empty; | 669 | UIDArray empty; |
672 | return empty; | 670 | return empty; |
673 | } | 671 | } |
674 | 672 | ||
675 | odebug << "queryByExample query: " << qu << "" << oendl; | 673 | odebug << "queryByExample query: " << qu << "" << oendl; |
676 | 674 | ||
677 | // Execute query and return the received uid's | 675 | // Execute query and return the received uid's |
678 | OSQLRawQuery raw( qu ); | 676 | OSQLRawQuery raw( qu ); |
679 | OSQLResult res = m_driver->query( &raw ); | 677 | OSQLResult res = m_driver->query( &raw ); |
680 | if ( res.state() != OSQLResult::Success ){ | 678 | if ( res.state() != OSQLResult::Success ){ |
681 | UIDArray empty; | 679 | UIDArray empty; |
682 | return empty; | 680 | return empty; |
683 | } | 681 | } |
684 | 682 | ||
685 | UIDArray list = extractUids( res ); | 683 | UIDArray list = extractUids( res ); |
686 | 684 | ||
687 | // Remove temp table if created | ||
688 | if ( !datediff_query.isEmpty( ) ){ | ||
689 | qu = "DROP TABLE bs"; | ||
690 | OSQLRawQuery raw( qu ); | ||
691 | OSQLResult res = m_driver->query( &raw ); | ||
692 | } | ||
693 | |||
694 | return list; | 685 | return list; |
695 | } | 686 | } |
696 | 687 | ||
697 | UIDArray OPimContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const | 688 | UIDArray OPimContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const |
698 | { | 689 | { |
699 | #if 0 | 690 | #if 0 |
700 | QArray<int> nix(0); | 691 | QArray<int> nix(0); |
701 | return nix; | 692 | return nix; |
702 | 693 | ||
703 | #else | 694 | #else |
704 | QString qu = "SELECT uid FROM addressbook WHERE ("; | 695 | QString qu = "SELECT uid FROM addressbook WHERE ("; |
705 | QString searchlist; | 696 | QString searchlist; |
706 | 697 | ||
707 | QStringList fieldList = OPimContactFields::untrfields( false ); | 698 | QStringList fieldList = OPimContactFields::untrfields( false ); |
708 | // QMap<QString, int> translate = OPimContactFields::untrFieldsToId(); | 699 | // QMap<QString, int> translate = OPimContactFields::untrFieldsToId(); |
709 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | 700 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ |
710 | if ( !searchlist.isEmpty() ) | 701 | if ( !searchlist.isEmpty() ) |
711 | searchlist += " OR "; | 702 | searchlist += " OR "; |
712 | searchlist += " rlike(\""+ r.pattern() + "\",\"" + *it + "\") "; | 703 | searchlist += " rlike(\""+ r.pattern() + "\",\"" + *it + "\") "; |
713 | } | 704 | } |
714 | 705 | ||
715 | qu = qu + searchlist + ")"; | 706 | qu = qu + searchlist + ")"; |
716 | 707 | ||
717 | odebug << "query: " << qu << "" << oendl; | 708 | odebug << "query: " << qu << "" << oendl; |
718 | 709 | ||
719 | OSQLRawQuery raw( qu ); | 710 | OSQLRawQuery raw( qu ); |
720 | OSQLResult res = m_driver->query( &raw ); | 711 | OSQLResult res = m_driver->query( &raw ); |
721 | 712 | ||
722 | return extractUids( res ); | 713 | return extractUids( res ); |
723 | 714 | ||
724 | 715 | ||
725 | #endif | 716 | #endif |
726 | } | 717 | } |
727 | 718 | ||
728 | const uint OPimContactAccessBackend_SQL::querySettings() const | 719 | const uint OPimContactAccessBackend_SQL::querySettings() const |
729 | { | 720 | { |
730 | return OPimContactAccess::IgnoreCase | 721 | return OPimContactAccess::IgnoreCase |
731 | | OPimContactAccess::WildCards | 722 | | OPimContactAccess::WildCards |
732 | | OPimContactAccess::DateDiff | 723 | | OPimContactAccess::DateDiff |
733 | | OPimContactAccess::DateYear | 724 | | OPimContactAccess::DateYear |
734 | | OPimContactAccess::DateMonth | 725 | | OPimContactAccess::DateMonth |
735 | | OPimContactAccess::DateDay | 726 | | OPimContactAccess::DateDay |
736 | ; | 727 | ; |
737 | } | 728 | } |
738 | 729 | ||
739 | bool OPimContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const | 730 | bool OPimContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const |
740 | { | 731 | { |
741 | /* OPimContactAccess::IgnoreCase, DateDiff, DateYear, DateMonth, DateDay | 732 | /* OPimContactAccess::IgnoreCase, DateDiff, DateYear, DateMonth, DateDay |