-rw-r--r-- | libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | 46 |
1 files changed, 36 insertions, 10 deletions
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 @@ -538,25 +538,26 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, // Just add uid's selection if we really try to search in a subset of all uids! Otherwise this would // just take time and memory! if ( uidlist.count() != m_uids.count() ) { qu += " ("; for ( uint i = 0; i < uidlist.count(); i++ ) { qu += " uid = " + QString::number( uidlist[i] ) + " OR"; } qu.remove( qu.length()-2, 2 ); // Hmmmm.. qu += " ) AND "; } - QString searchQuery =""; + QString searchQuery = ""; + QString temp_searchQuery = ""; QDate startDate; if ( qd.isValid() ) startDate = qd.date(); else startDate = QDate::currentDate(); QMap<int, QString> queryFields = query.toMap(); QStringList fieldList = OPimContactFields::untrfields( false ); QMap<QString, int> translate = OPimContactFields::untrFieldsToId(); @@ -576,38 +577,47 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, searchQuery += " AND"; // isAnyFieldSelected = true; switch( id ){ case Qtopia::Birthday: endDate = new QDate( query.birthday() ); // Fall through ! case Qtopia::Anniversary: if ( endDate == 0l ) endDate = new QDate( query.anniversary() ); if ( settings & OPimContactAccess::DateDiff ) { - searchQuery += QString( " (\"%1\" <= '%2-%3-%4\' AND \"%5\" >= '%6-%7-%8')" ) + // To handle datediffs correctly, we need to remove the year information from + // the birthday and anniversary. + // To do this efficiently, we will create a temporary table which contains the + // information we need and do the query on it. + // This table is just visible for this process and will be removed + // automatically after using. + temp_searchQuery = "CREATE TEMP TABLE bs ( uid, \"Birthday\", \"Anniversary\" );"; + temp_searchQuery += "INSERT INTO bs SELECT uid,substr(\"Birthday\", 6, 10),substr(\"Anniversary\", 6, 10) FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' );"; + + temp_searchQuery += QString( "SELECT uid FROM bs WHERE (\"%1\" <= '%2-%3\' AND \"%4\" >= '%5-%6')" ) .arg( *it ) - .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ) + //.arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ) .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) ) .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) ) .arg( *it ) - .arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) ) + //.arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) ) .arg( QString::number( startDate.month() ).rightJustify( 2, '0' ) ) .arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ; } if ( settings & OPimContactAccess::DateYear ){ - if ( settings & OPimContactAccess::DateDiff ) - searchQuery += " AND"; + // if ( settings & OPimContactAccess::DateDiff ) + // searchQuery += " AND"; searchQuery += QString( " (\"%1\" LIKE '%2-%')" ) .arg( *it ) .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) ); } if ( settings & OPimContactAccess::DateMonth ){ if ( ( settings & OPimContactAccess::DateDiff ) || ( settings & OPimContactAccess::DateYear ) ) searchQuery += " AND"; searchQuery += QString( " (\"%1\" LIKE '%-%2-%')" ) @@ -634,42 +644,58 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, if ( settings & OPimContactAccess::IgnoreCase ) searchQuery += "(\"" + *it + "\"" + " LIKE " + "'" + queryStr.replace(QRegExp("\\*"),"%") + "'" + ")"; else searchQuery += "(\"" + *it + "\"" + " GLOB " + "'" + queryStr + "'" + ")"; } } delete endDate; } - // Skip trailing "AND" -// if ( isAnyFieldSelected ) -// qu = qu.left( qu.length() - 4 ); - qu += searchQuery; + // The following is very ugly! (eilers) + if ( !temp_searchQuery.isEmpty() && !searchQuery.isEmpty() ){ + // If we use DateDiff, we have to intersect two queries. + qu = temp_searchQuery + QString( " INTERSECT " ) + qu + searchQuery; + } else if ( temp_searchQuery.isEmpty() && !searchQuery.isEmpty() ){ + qu += searchQuery; + } else if ( !temp_searchQuery.isEmpty() && searchQuery.isEmpty() ){ + // This will cause wrong results!! Uid filter is not used here! + qu = temp_searchQuery; + } else if ( temp_searchQuery.isEmpty() && searchQuery.isEmpty() ){ + UIDArray empty; + return empty; + } odebug << "queryByExample query: " << qu << "" << oendl; // Execute query and return the received uid's OSQLRawQuery raw( qu ); OSQLResult res = m_driver->query( &raw ); if ( res.state() != OSQLResult::Success ){ UIDArray empty; return empty; } UIDArray list = extractUids( res ); + // Remove temp table if created + if ( !temp_searchQuery.isEmpty( ) ){ + qu = "DROP TABLE bs"; + OSQLRawQuery raw( qu ); + OSQLResult res = m_driver->query( &raw ); + } + return list; } UIDArray OPimContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const { #if 0 QArray<int> nix(0); return nix; #else QString qu = "SELECT uid FROM addressbook WHERE ("; QString searchlist; |