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..

2 files changed, 39 insertions, 11 deletions

diff --git a/libopie2/opiepim/ChangeLog b/libopie2/opiepim/ChangeLog
index 84517fb..acb6cb1 100644
--- a/libopie2/opiepim/ChangeLog
+++ b/libopie2/opiepim/ChangeLog
@@ -1,34 +1,36 @@
-2005-03.19 Stefan Eilers <stefan@eilers-online.net>
+2005-03-20 Stefan Eilers <stefan@eilers-online.net>
+        * #1608 Quickfix for problem with DateDiff on SQL backend. I have to rethink this solution, but due to the short time, this should work.  
+2005-03-19 Stefan Eilers <stefan@eilers-online.net>
         * Minor update for sorted(). Now ignoring any category search if "DoNotShowWithCategory" filter is activated.
         * Fixing uninitialized member variable, caused crash of backend
 2005-03-18 Stefan Eilers <stefan@eilers-online.net>
         * Rewrote generic sorted filter and added filter for "DoNotShowWithCategory", needed by addressbook (other filters need to be added!)
 2005-01-16 Stefan Eilers <stefan@eilers-online.net>
         * Added new OPimEventSortVector class, improved OPimSortVector
         * OPimAccessBackend now supports generic sorting.
 2005-01-03 Stefan Eilers <stefan@eilers-online.net>
         * Fixing bug in API documentation
         * Moving hasQuerySettings() and querySettings() to OPimAccessTemplate to be available for all frontends
 2004-12-28  Stefan Eilers <stefan@eilers-online.net>
         * Make improved query by example accessable via frontend
         * Some API documentation improvement
         * Cleanup of backend api..
         * Fixing bug #1501
 2004-11-23  Stefan Eilers <stefan@eilers-online.net>
         * Implement fast and full featured version of sorted() for addressbook
         * Implement generic queryByExample for all Addressboook backends. It allows incremental search.
         * Update of API Documentation
 2004-11-18  Holger Freyther  <freyther@handhelds.org>
         * Every Access can give a set of Occurrences for a period or a datetime
         * QueryByExample, Find, Sort can be generically accessed by OPimBase 
           pointer interface
         * OPimBackendOccurrence gets split up to OPimOccurrences by 
           OPimTemplateBase
         * Add safeCast to various OPimRecords
         * Kill memleak in OPimTodo
         * Add SortVector implementations for OPimTodo and OPimContact
         
         2004-??-??The Opie Team <opie@handhelds.org>
         * Implemented some important modifications to allow to use OPimRecords as it is, without
           have to cast them. This makes it possible to write applications which handling pim
           data in a generic manner (see opimconvertion tool) (eilers)
\ No newline at end of file
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
@@ -454,306 +454,332 @@ bool OPimContactAccessBackend_SQL::add ( const OPimContact &newcontact )
 
 bool OPimContactAccessBackend_SQL::remove ( int uid )
 {
     RemoveQuery rem( uid );
     OSQLResult res = m_driver->query(&rem );
 
     if ( res.state() == OSQLResult::Failure )
         return false;
 
     m_changed = true;
 
     return true;
 }
 
 bool OPimContactAccessBackend_SQL::replace ( const OPimContact &contact )
 {
     if ( !remove( contact.uid() ) )
         return false;
 
     return add( contact );
 }
 
 
 OPimContact OPimContactAccessBackend_SQL::find ( int uid ) const
 {
     odebug << "OPimContactAccessBackend_SQL::find(" << uid << ")" << oendl;
     QTime t;
     t.start();
 
     OPimContact retContact( requestNonCustom( uid ) );
     retContact.setExtraMap( requestCustom( uid ) );
 
     odebug << "OPimContactAccessBackend_SQL::find() needed: " << t.elapsed() << " ms" << oendl;
     return retContact;
 }
 
 OPimContact OPimContactAccessBackend_SQL::find( int uid, const UIDArray& queryUids, uint current, Frontend::CacheDirection direction ) const
 {
     odebug << "OPimContactAccessBackend_SQL::find( ..multi.. )" << oendl;
     odebug << "searching for " << uid << "" << oendl;
 
     QTime t;
     t.start();
 
     uint numReadAhead = readAhead();
     QArray<int> searchList( numReadAhead );
 
     uint size =0;
 
     // Build an array with all elements which should be requested and cached
     // We will just request "numReadAhead" elements, starting from "current" position in
     // the list of many uids !
     switch( direction ) {
         /* forward */
     case Frontend::Forward:
         for ( uint i = current; i < queryUids.count() && size < numReadAhead; i++ ) {
             searchList[size] = queryUids[i];
             size++;
         }
         break;
         /* reverse */
     case Frontend::Reverse:
         for ( uint i = current; i != 0 && size <  numReadAhead; i-- ) {
             searchList[size] = queryUids[i];
             size++;
         }
         break;
     }
 
     //Shrink to real size..
     searchList.resize( size );
 
     OPimContact retContact( requestContactsAndCache( uid, searchList ) );
 
     odebug << "OPimContactAccessBackend_SQL::find( ..multi.. ) needed: " << t.elapsed() << " ms" << oendl;
     return retContact;
 }
 
 
 UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist, const OPimContact &query, int settings, 
                                                            const QDateTime& qd ) const
 {
     QString qu = "SELECT uid FROM addressbook WHERE";
 
     // 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();
 
     // Convert every filled field to a SQL-Query
 //  bool isAnyFieldSelected = false;
     for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){
 
         int id = translate[*it];
         QString queryStr = queryFields[id];
         QDate* endDate = 0l;
 
         if ( !queryStr.isEmpty() ){
             // If something is alredy stored in the query, add an "AND"
             // to the end of the string to prepare for the next ..
             if ( !searchQuery.isEmpty() )
                 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-%')" )
                         .arg( *it )
                         .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) );
                 }
 
                 if ( settings & OPimContactAccess::DateDay ){
                     if ( ( settings & OPimContactAccess::DateDiff )
                          || ( settings & OPimContactAccess::DateYear )
                          || ( settings & OPimContactAccess::DateMonth ) )
                         searchQuery += " AND";
 
                     searchQuery += QString( " (\"%1\" LIKE '%-%-%2')" )
                         .arg( *it )
                         .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) );
                 }
 
                 break;
             default:
                 // Switching between case sensitive and insensitive...
                 // LIKE is not case sensitive, GLOB is case sensitive
                 // Do exist a better solution to switch this ?
                 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;
 
     QStringList fieldList = OPimContactFields::untrfields( false );
     // QMap<QString, int> translate = OPimContactFields::untrFieldsToId();
     for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){
         if ( !searchlist.isEmpty() )
             searchlist += " OR ";
         searchlist += " rlike(\""+ r.pattern() + "\",\"" + *it + "\") ";
     }
 
     qu = qu + searchlist + ")";
 
     odebug << "query: " << qu << "" << oendl;
 
     OSQLRawQuery raw( qu );
     OSQLResult res = m_driver->query( &raw );
 
     return extractUids( res );
 
 
 #endif
 }
 
 const uint OPimContactAccessBackend_SQL::querySettings() const
 {
     return OPimContactAccess::IgnoreCase
         | OPimContactAccess::WildCards
         | OPimContactAccess::DateDiff
         | OPimContactAccess::DateYear
         | OPimContactAccess::DateMonth
         | OPimContactAccess::DateDay
         ;
 }
 
 bool OPimContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const
 {
     /* OPimContactAccess::IgnoreCase, DateDiff, DateYear, DateMonth, DateDay
      * may be added with any of the other settings. IgnoreCase should never used alone.
      * Wildcards, RegExp, ExactMatch should never used at the same time...
      */
 
     // Step 1: Check whether the given settings are supported by this backend
     if ( ( querySettings & (
                 OPimContactAccess::IgnoreCase
                 | OPimContactAccess::WildCards
                 | OPimContactAccess::DateDiff
                 | OPimContactAccess::DateYear
                 | OPimContactAccess::DateMonth
                 | OPimContactAccess::DateDay
 //              | OPimContactAccess::RegExp
 //              | OPimContactAccess::ExactMatch
                    ) ) != querySettings )
         return false;
 
     // Step 2: Check whether the given combinations are ok..
 
     // IngoreCase alone is invalid
     if ( querySettings == OPimContactAccess::IgnoreCase )
         return false;
 
     // WildCards, RegExp and ExactMatch should never used at the same time
     switch ( querySettings & ~( OPimContactAccess::IgnoreCase
                     | OPimContactAccess::DateDiff
                     | OPimContactAccess::DateYear
                     | OPimContactAccess::DateMonth
                     | OPimContactAccess::DateDay
                     )
          ){
     case OPimContactAccess::RegExp:
         return ( true );
     case OPimContactAccess::WildCards:
         return ( true );
     case OPimContactAccess::ExactMatch:
         return ( true );
     case 0: // one of the upper removed bits were set..
         return ( true );
     default:
         return ( false );
     }
 
 }
 
 UIDArray OPimContactAccessBackend_SQL::sorted( const UIDArray& ar, bool asc, int sortOrder,
                                   int filter, const QArray<int>& categories )const 
 {