Fixing last issues on QueryByExample on SQL databases. All tests passed successfully

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,36 +1,38 @@
+2005-03-23 Stefan Eilers <stefan@eilers-online.net>
+	* #1608 Finishing work on SQL backend. Datediff and other queries for QueryByExample now working as exprected. All tests passed successfully
 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 50421e2..175d62a 100644
--- a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
+++ b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
@@ -1,1092 +1,1096 @@
 /*
                              This file is part of the Opie Project
                              Copyright (C) Stefan Eilers <eilers.stefan@epost.de>
               =.             Copyright (C) The Opie Team <opie-devel@handhelds.org>
             .=l.
            .>+-=
  _;:,     .>    :=|.         This program is free software; you can
 .> <`_,   >  .   <=          redistribute it and/or  modify it under
 :`=1 )Y*s>-.--   :           the terms of the GNU Library General Public
 .="- .-=="i,     .._         License as published by the Free Software
  - .   .-<_>     .<>         Foundation; either version 2 of the License,
      ._= =}       :          or (at your option) any later version.
     .%`+i>       _;_.
     .i_,=:_.      -<s.       This program is distributed in the hope that
      +  .  -:.       =       it will be useful,  but WITHOUT ANY WARRANTY;
     : ..    .:,     . . .    without even the implied warranty of
     =_        +     =;=|`    MERCHANTABILITY or FITNESS FOR A
   _.=:.       :    :=>`:     PARTICULAR PURPOSE. See the GNU
 ..}^=.=       =       ;      Library General Public License for more
 ++=   -.     .`     .:       details.
  :     =  ...= . :.=-
  -.   .:....=;==+<;          You should have received a copy of the GNU
   -_. . .   )=.  =           Library General Public License along with
     --        :-=`           this library; see the file COPYING.LIB.
                              If not, write to the Free Software Foundation,
                              Inc., 59 Temple Place - Suite 330,
                              Boston, MA 02111-1307, USA.
 */
 /*
  * SQL Backend for the OPIE-Contact Database.
  */
 
 #include "ocontactaccessbackend_sql.h"
 
 /* OPIE */
 #include <opie2/opimcontact.h>
 #include <opie2/opimcontactfields.h>
 #include <opie2/opimdateconversion.h>
 #include <opie2/osqldriver.h>
 #include <opie2/osqlresult.h>
 #include <opie2/osqlmanager.h>
 #include <opie2/osqlquery.h>
 #include <opie2/odebug.h>
 
 #include <qpe/global.h>
 #include <qpe/recordfields.h>
 
 /* QT */
 #include <qarray.h>
 #include <qdatetime.h>
 #include <qstringlist.h>
 
 
 using namespace Opie;
 using namespace Opie::DB;
 
 
 /*
  * Implementation of used query types * CREATE query
  * LOAD query
  * INSERT
  * REMOVE
  * CLEAR
  */
 namespace {
     /**
      * CreateQuery for the Todolist Table
      */
     class CreateQuery : public OSQLQuery {
     public:
         CreateQuery();
         ~CreateQuery();
         QString query()const;
     };
 
     /**
      * Clears (delete) a Table
      */
     class ClearQuery : public OSQLQuery {
     public:
         ClearQuery();
         ~ClearQuery();
         QString query()const;
 
     };
 
 
     /**
      * LoadQuery
      * this one queries for all uids
      */
     class LoadQuery : public OSQLQuery {
     public:
         LoadQuery();
         ~LoadQuery();
         QString query()const;
     };
 
     /**
      * inserts/adds a OPimContact to the table
      */
     class InsertQuery : public OSQLQuery {
     public:
         InsertQuery(const OPimContact& );
         ~InsertQuery();
         QString query()const;
     private:
         OPimContact m_contact;
     };
 
 
     /**
      * removes one from the table
      */
     class RemoveQuery : public OSQLQuery {
     public:
         RemoveQuery(int uid );
         ~RemoveQuery();
         QString query()const;
     private:
         int m_uid;
     };
 
     /**
      * a find query for noncustom elements
      */
     class FindQuery : public OSQLQuery {
     public:
         FindQuery(int uid);
         FindQuery(const UIDArray& );
         ~FindQuery();
         QString query()const;
     private:
         QString single()const;
         QString multi()const;
         UIDArray m_uids;
         int m_uid;
     };
 
     /**
      * a find query for custom elements
      */
     class FindCustomQuery : public OSQLQuery {
     public:
         FindCustomQuery(int uid);
         FindCustomQuery(const UIDArray& );
         ~FindCustomQuery();
         QString query()const;
     private:
         QString single()const;
         QString multi()const;
         UIDArray m_uids;
         int m_uid;
     };
 
 
 
     // We using two tables to store the information:
     // 1. addressbook  : It contains General information about the contact (non custom)
     // 2. custom_data  : Not official supported entries
     // All tables are connected by the uid of the contact.
     // Maybe I should add a table for meta-information ?
     CreateQuery::CreateQuery() : OSQLQuery() {}
     CreateQuery::~CreateQuery() {}
     QString CreateQuery::query()const {
         QString qu;
 
         qu += "create table addressbook( uid PRIMARY KEY ";
 
         QStringList fieldList = OPimContactFields::untrfields( false );
         for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){
             qu += QString( ",\"%1\" VARCHAR(10)" ).arg( *it );
         }
         qu += " );";
 
         qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR(10), priority INTEGER, value VARCHAR(10), PRIMARY KEY /* identifier */ (uid, id) );";
 
         return qu;
     }
 
     ClearQuery::ClearQuery()
         : OSQLQuery() {}
     ClearQuery::~ClearQuery() {}
     QString ClearQuery::query()const {
         QString qu = "drop table addressbook;";
         qu += "drop table custom_data;";
 //      qu += "drop table dates;";
         return qu;
     }
 
 
     LoadQuery::LoadQuery() : OSQLQuery() {}
     LoadQuery::~LoadQuery() {}
     QString LoadQuery::query()const {
         QString qu;
         qu += "select uid from addressbook";
 
         return qu;
     }
 
 
     InsertQuery::InsertQuery( const OPimContact& contact )
         : OSQLQuery(), m_contact( contact ) {
     }
 
     InsertQuery::~InsertQuery() {
     }
 
     /*
      * converts from a OPimContact to a query
      */
     QString InsertQuery::query()const{
 
         QString qu;
         qu  += "insert into addressbook VALUES( " +
             QString::number( m_contact.uid() );
 
         // Get all information out of the contact-class
         // Remember: The category is stored in contactMap, too !
         QMap<int, QString> contactMap = m_contact.toMap();
 
         QStringList fieldList = OPimContactFields::untrfields( false );
         QMap<QString, int> translate = OPimContactFields::untrFieldsToId();
         for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){
             // Convert Column-String to Id and get value for this id..
             // Hmmm.. Maybe not very cute solution..
             int id = translate[*it];
             switch ( id ){
             case Qtopia::Birthday:
             case Qtopia::Anniversary:{
                 QDate day;
                 if ( id == Qtopia::Birthday ){
                     day = m_contact.birthday();
                 } else {
                     day = m_contact.anniversary();
                 }
                 // These entries should stored in a special format
                 // year-month-day
                 if ( day.isValid() ){
                     qu += QString(",\"%1-%2-%3\"")
                         .arg( QString::number( day.year() ).rightJustify( 4, '0' ) )
                         .arg( QString::number( day.month() ).rightJustify( 2, '0' ) )
                         .arg( QString::number( day.day() ).rightJustify( 2, '0' ) );
                 } else {
                     qu += ",\"\"";
                 }
             }
                 break;
             default:
                 qu += QString( ",\"%1\"" ).arg( contactMap[id] );
             }
         }
         qu += " );";
 
 
         // Now add custom data..
         int id = 0;
         id = 0;
         QMap<QString, QString> customMap = m_contact.toExtraMap();
         for( QMap<QString, QString>::Iterator it = customMap.begin();
              it != customMap.end(); ++it ){
             qu  += "insert into custom_data VALUES("
                 +  QString::number( m_contact.uid() )
                 + ","
                 +  QString::number( id++ )
                 + ",'"
                 + it.key()
                 + "',"
                 + "0" // Priority for future enhancements
                 + ",'"
                 + it.data()
                 + "');";
         }
         // qu  += "commit;";
         odebug << "add " << qu << "" << oendl;
         return qu;
     }
 
 
     RemoveQuery::RemoveQuery(int uid )
         : OSQLQuery(), m_uid( uid ) {}
 
     RemoveQuery::~RemoveQuery() {}
 
     QString RemoveQuery::query()const {
         QString qu = "DELETE from addressbook where uid = "
             + QString::number(m_uid) + ";";
         qu += "DELETE from custom_data where uid = "
             + QString::number(m_uid) + ";";
         return qu;
     }
 
 
     FindQuery::FindQuery(int uid)
         : OSQLQuery(), m_uid( uid ) {
     }
     FindQuery::FindQuery(const UIDArray& ints)
         : OSQLQuery(), m_uids( ints ){
     }
     FindQuery::~FindQuery() {
     }
     QString FindQuery::query()const{
            if ( m_uids.count() == 0 )
                    return single();
            else
                    return multi();
     }
 
 
 
     QString FindQuery::multi()const {
 	    QString qu = "select * from addressbook where";
 	    for (uint i = 0; i < m_uids.count(); i++ ) {
 		    qu += " uid = " + QString::number( m_uids[i] ) + " OR";
 	    }
 	    qu.remove( qu.length()-2, 2 ); // Hmmmm..
 
 	    odebug << "find query: " << qu << "" << oendl;
 	    return qu;
     }
 
     QString FindQuery::single()const{
 	    QString qu = "select *";
 	    qu += " from addressbook where uid = " + QString::number(m_uid);
 
 	    // owarn << "find query: " << qu << "" << oendl;
 	    return qu;
     }
 
 
     FindCustomQuery::FindCustomQuery(int uid)
         : OSQLQuery(), m_uid( uid ) {
     }
     FindCustomQuery::FindCustomQuery(const UIDArray& ints)
         : OSQLQuery(), m_uids( ints ){
     }
     FindCustomQuery::~FindCustomQuery() {
     }
     QString FindCustomQuery::query()const{
 //      if ( m_uids.count() == 0 )
             return single();
     }
     QString FindCustomQuery::single()const{
         QString qu = "select uid, type, value from custom_data where uid = ";
         qu += QString::number(m_uid);
         return qu;
     }
 
 };
 
 
 /* --------------------------------------------------------------------------- */
 
 namespace Opie {
 
 OPimContactAccessBackend_SQL::OPimContactAccessBackend_SQL ( const QString& /* appname */,
                                const QString& filename ):
     OPimContactAccessBackend(), m_changed(false), m_driver( NULL )
 {
     odebug << "C'tor OPimContactAccessBackend_SQL starts" << oendl;
     QTime t;
     t.start();
 
     /* Expecting to access the default filename if nothing else is set */
     if ( filename.isEmpty() ){
         m_fileName = Global::applicationFileName( "addressbook","addressbook.db" );
     } else
         m_fileName = filename;
 
     // Get the standart sql-driver from the OSQLManager..
     OSQLManager man;
     m_driver = man.standard();
     m_driver->setUrl( m_fileName );
 
     load();
 
     odebug << "C'tor OPimContactAccessBackend_SQL ends: " << t.elapsed() << " ms" << oendl;
 }
 
 OPimContactAccessBackend_SQL::~OPimContactAccessBackend_SQL ()
 {
     if( m_driver )
         delete m_driver;
 }
 
 bool OPimContactAccessBackend_SQL::load ()
 {
     if (!m_driver->open() )
         return false;
 
     // Don't expect that the database exists.
     // It is save here to create the table, even if it
     // do exist. ( Is that correct for all databases ?? )
     CreateQuery creat;
     OSQLResult res = m_driver->query( &creat );
 
     update();
 
     return true;
 
 }
 
 bool OPimContactAccessBackend_SQL::reload()
 {
     return load();
 }
 
 bool OPimContactAccessBackend_SQL::save()
 {
     return m_driver->close();  // Shouldn't m_driver->sync be better than close ? (eilers)
 }
 
 
 void OPimContactAccessBackend_SQL::clear ()
 {
     ClearQuery cle;
     OSQLResult res = m_driver->query( &cle );
 
     reload();
 }
 
 bool OPimContactAccessBackend_SQL::wasChangedExternally()
 {
     return false;
 }
 
 UIDArray OPimContactAccessBackend_SQL::allRecords() const
 {
 
     // FIXME: Think about cute handling of changed tables..
     // Thus, we don't have to call update here...
     if ( m_changed )
         ((OPimContactAccessBackend_SQL*)this)->update();
 
     return m_uids;
 }
 
 bool OPimContactAccessBackend_SQL::add ( const OPimContact &newcontact )
 {
     odebug << "add in contact SQL-Backend" << oendl;
     InsertQuery ins( newcontact );
     OSQLResult res = m_driver->query( &ins );
 
     if ( res.state() == OSQLResult::Failure )
         return false;
 
     int c = m_uids.count();
     m_uids.resize( c+1 );
     m_uids[c] = newcontact.uid();
 
     return true;
 }
 
 
 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";
+    QString searchQuery = "";
+    QString datediff_query = "";
+    QString uid_query = "";
 
     // 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 += " (";
+	    uid_query += " (";
 
 	    for ( uint i = 0; i < uidlist.count(); i++ ) {
-		    qu += " uid = " + QString::number( uidlist[i] ) + " OR";
+		    uid_query += " uid = " + QString::number( uidlist[i] ) + " OR";
 	    }
-	    qu.remove( qu.length()-2, 2 ); // Hmmmm..
-	    qu += " ) AND ";
+	    uid_query.remove( uid_query.length()-2, 2 ); // Hmmmm..
+	    uid_query += " ) AND ";
     }
 
-    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 ) {
 		    // 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')" )
+		    datediff_query = "CREATE TEMP TABLE bs ( uid, \"Birthday\", \"Anniversary\" );";
+		    datediff_query += "INSERT INTO bs SELECT uid,substr(\"Birthday\", 6, 10),substr(\"Anniversary\", 6, 10) FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' );";
+		    datediff_query += QString( "SELECT uid FROM bs WHERE " ) + uid_query;
+                    datediff_query += QString( " (\"%1\" <= '%2-%3\' AND \"%4\" >= '%5-%6')" )
                         .arg( *it )
 			    //.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.month() ).rightJustify( 2, '0' ) )
                         .arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ;
                 }
 
                 if ( settings & OPimContactAccess::DateYear ){
-                    // 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 ) )
+                    if ( 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 )
+                    if ( ( 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 " + "'"
+                    searchQuery += " (\"" + *it + "\"" + " LIKE " + "'"
                         + queryStr.replace(QRegExp("\\*"),"%") + "'" + ")";
                 else
-                    searchQuery += "(\"" + *it + "\"" + " GLOB " + "'"
+                    searchQuery += " (\"" + *it + "\"" + " GLOB " + "'"
                         + queryStr + "'" + ")";
 
             }
         }
 
         delete endDate;
+
+	// The following if line is a replacement for 
+	// if ( searchQuery.endsWith( "AND" ) )
+	if ( searchQuery.findRev( "AND" ) == ( searchQuery.length() - 3 ) ){
+		odebug << "remove AND" << oendl;
+		searchQuery.remove( searchQuery.length()-3, 3 ); // Hmmmm..
+	}
+
     }
 
-    // The following is very ugly! (eilers)
-    if ( !temp_searchQuery.isEmpty() && !searchQuery.isEmpty() ){
+    // Now compose the complete query
+    QString qu = "SELECT uid FROM addressbook WHERE " + uid_query; 
+
+    if ( !datediff_query.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 = datediff_query + QString( " INTERSECT " ) + qu + searchQuery;
+    } else if ( datediff_query.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() ){
+    } else if ( !datediff_query.isEmpty() && searchQuery.isEmpty() ){
+	    qu = datediff_query;
+    } else if ( datediff_query.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( ) ){
+    if ( !datediff_query.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 
 {
     QTime t;
     t.start();
 
     QString query = "SELECT uid FROM addressbook";
 
     query += " WHERE (";
     for ( uint i = 0; i < ar.count(); i++ ) {
 	    query += " uid = " + QString::number( ar[i] ) + " OR";
     }
     query.remove( query.length()-2, 2 ); // Hmmmm..
     query += ")";
 
 
     if ( filter != OPimBase::FilterOff ){
 	    if ( filter & OPimContactAccess::DoNotShowWithCategory ){
 		    query += " AND ( \"Categories\" == '' )";
 	    } else if ( filter & OPimBase::FilterCategory ){
 		    query += " AND (";
 		    for ( uint i = 0; i < categories.count(); i++ ){
 			    query += "\"Categories\" LIKE";
 			    query += QString( " '%" ) + QString::number( categories[i] ) + "%' OR";
 		    }
 		    query.remove( query.length()-2, 2 ); // Hmmmm..
 		    query += ")";
 	    }
 
 	    if ( filter & OPimContactAccess::DoNotShowWithoutChildren ){
 		    query += " AND ( \"Children\" != '' )";
 	    }
 
 	    if ( filter & OPimContactAccess::DoNotShowWithoutAnniversary ){
 		    query += " AND ( \"Anniversary\" != '' )";
 	    }
 
 	    if ( filter & OPimContactAccess::DoNotShowWithoutBirthday ){
 		    query += " AND ( \"Birthday\" != '' )";
 	    }
 
 	    if ( filter & OPimContactAccess::DoNotShowWithoutHomeAddress ){
 		    // Expect that no Street means no Address, too! (eilers)
 		    query += " AND ( \"Home Street\" != '' )";
 	    }
 
 	    if ( filter & OPimContactAccess::DoNotShowWithoutBusinessAddress ){
 		    // Expect that no Street means no Address, too! (eilers)
 		    query += " AND ( \"Business Street\" != '' )";
 	    } 
 
     }
    
     query += " ORDER BY";
     
     switch ( sortOrder ) {
     case OPimContactAccess::SortSummary:
 	    query += " \"Notes\"";
 	    break;
     case OPimContactAccess::SortByCategory:
 	    query += " \"Categories\"";
 	    break;
     case OPimContactAccess::SortByDate:
 	    query += " \"\"";
 	    break;
     case OPimContactAccess::SortTitle:
 	    query += " \"Name Title\"";
 	    break;
     case OPimContactAccess::SortFirstName:
 	    query += " \"First Name\"";
 	    break;
     case OPimContactAccess::SortMiddleName:
 	    query += " \"Middle Name\"";
 	    break;
     case OPimContactAccess::SortLastName:
 	    query += " \"Last Name\"";
 	    break;
    case OPimContactAccess::SortFileAsName:
 	    query += " \"File As\"";
 	    break;
     case OPimContactAccess::SortSuffix:
 	    query += " \"Suffix\"";
 	    break;
     case OPimContactAccess::SortEmail:
 	    query += " \"Default Email\"";
 	    break;
     case OPimContactAccess::SortNickname:
 	    query += " \"Nickname\"";
 	    break;
     case OPimContactAccess::SortAnniversary:
 	    query += " \"Anniversary\"";
 	    break;
     case OPimContactAccess::SortBirthday:
 	    query += " \"Birthday\"";
 	    break;
     case OPimContactAccess::SortGender:
 	    query += " \"Gender\"";
 	    break;
     default:
 	   query += " \"Last Name\"";
     }
 
     if ( !asc )
         query += " DESC";
 
 
     odebug << "sorted query is: " << query << "" << oendl;
 
     OSQLRawQuery raw( query );
     OSQLResult res = m_driver->query( &raw );
     if ( res.state() != OSQLResult::Success ){
         UIDArray empty;
         return empty;
     }
 
     UIDArray list = extractUids( res );
 
     odebug << "sorted needed " << t.elapsed() << " ms!" << oendl;
     return list;
 }
 
 
 void OPimContactAccessBackend_SQL::update()
 {
     odebug << "Update starts" << oendl;
     QTime t;
     t.start();
 
     // Now load the database set and extract the uid's
     // which will be held locally
 
     LoadQuery lo;
     OSQLResult res = m_driver->query(&lo);
     if ( res.state() != OSQLResult::Success )
         return;
 
     m_uids = extractUids( res );
 
     m_changed = false;
 
     odebug << "Update ends " << t.elapsed() << " ms" << oendl;
 }
 
 UIDArray OPimContactAccessBackend_SQL::extractUids( OSQLResult& res ) const
 {
     odebug << "extractUids" << oendl;
     QTime t;
     t.start();
     OSQLResultItem::ValueList list = res.results();
     OSQLResultItem::ValueList::Iterator it;
     UIDArray ints(list.count() );
     odebug << " count = " << list.count() << "" << oendl;
 
     int i = 0;
     for (it = list.begin(); it != list.end(); ++it ) {
         ints[i] =  (*it).data("uid").toInt();
         i++;
     }
     odebug << "extractUids ready: count2 = " << i << " needs " << t.elapsed() << " ms" << oendl;
 
     return ints;
 
 }
 
 QMap<int, QString>  OPimContactAccessBackend_SQL::requestNonCustom( int uid ) const
 {
     QTime t;
     t.start();
 
     int t2needed = 0;
     int t3needed = 0;
     QTime t2;
     t2.start();
     FindQuery query( uid );
     OSQLResult res_noncustom = m_driver->query( &query );
     t2needed = t2.elapsed();
 
     OSQLResultItem resItem = res_noncustom.first();
 
     QMap<int, QString> nonCustomMap;
     QTime t3;
     t3.start();
     nonCustomMap = fillNonCustomMap( resItem );
     t3needed = t3.elapsed();
 
 
     // odebug << "Adding UID: " << resItem.data( "uid" ) << "" << oendl;
     odebug << "RequestNonCustom needed: insg.:" << t.elapsed() << " ms, query: " << t2needed
            << " ms, mapping: " << t3needed << " ms" << oendl;
 
     return nonCustomMap;
 }
 
 /* Returns contact requested by uid and fills cache with contacts requested by uids in the cachelist */
 OPimContact OPimContactAccessBackend_SQL::requestContactsAndCache( int uid, const UIDArray& uidlist )const
 {
 	// We want to get all contacts with one query.
 	// We don't have to add the given uid to the uidlist, it is expected to be there already (see opimrecordlist.h).
 	// All contacts will be stored in the cache, afterwards the contact with the user id "uid" will be returned
 	// by using the cache..
 	UIDArray cachelist = uidlist;
 	OPimContact retContact;
 
 	odebug << "Reqest and cache" << cachelist.size() << "elements !" << oendl;
 
 	QTime t;
 	t.start();
 
 	int t2needed = 0;
 	int t3needed = 0;
 	QTime t2;
 	t2.start();
 	FindQuery query( cachelist );
 	OSQLResult res_noncustom = m_driver->query( &query );
 	t2needed = t2.elapsed();
 
 	QMap<int, QString> nonCustomMap;
 	QTime t3;
 	t3.start();
 	OSQLResultItem resItem = res_noncustom.first();
 	do {
 		OPimContact contact( fillNonCustomMap( resItem ) );
 		contact.setExtraMap( requestCustom( contact.uid() ) );
 		odebug << "Caching uid: " << contact.uid() << oendl;
 		cache( contact );
 		if ( contact.uid() == uid )
 			retContact = contact;
 		resItem = res_noncustom.next();
 	} while ( ! res_noncustom.atEnd() ); //atEnd() is true if we are past(!) the list !!
 	t3needed = t3.elapsed();
 
 
 	// odebug << "Adding UID: " << resItem.data( "uid" ) << "" << oendl;
 	odebug << "RequestContactsAndCache needed: insg.:" << t.elapsed() << " ms, query: " << t2needed
 	       << " ms, mapping: " << t3needed << " ms" << oendl;
 
 	return retContact;
 }
 
 QMap<int, QString> OPimContactAccessBackend_SQL::fillNonCustomMap( const OSQLResultItem& resultItem ) const
 {
     QMap<int, QString> nonCustomMap;
 
     // Now loop through all columns
     QStringList fieldList = OPimContactFields::untrfields( false );
     QMap<QString, int> translate = OPimContactFields::untrFieldsToId();
     for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){
         // Get data for the selected column and store it with the
         // corresponding id into the map..
 
         int id =  translate[*it];
         QString value = resultItem.data( (*it) );
 
         // odebug << "Reading " << (*it) << "... found: " << value << "" << oendl;
 
         switch( id ){
         case Qtopia::Birthday:
         case Qtopia::Anniversary:{
             // Birthday and Anniversary are encoded special ( yyyy-mm-dd )
             QStringList list = QStringList::split( '-', value );
             QStringList::Iterator lit = list.begin();
             int year  = (*lit).toInt();
             int month = (*(++lit)).toInt();
             int day   = (*(++lit)).toInt();
             if ( ( day != 0 ) && ( month != 0 ) && ( year != 0 ) ){
                  QDate date( year, month, day );
                  nonCustomMap.insert( id, OPimDateConversion::dateToString( date ) );
             }
         }
             break;
         case Qtopia::AddressCategory:
             odebug << "Category is: " << value << "" << oendl;
         default:
             nonCustomMap.insert( id, value );
         }
     }
 
     nonCustomMap.insert( Qtopia::AddressUid, resultItem.data( "uid" ) );
 
     return nonCustomMap;
 }
 
 
 QMap<QString, QString>  OPimContactAccessBackend_SQL::requestCustom( int uid ) const
 {
     QTime t;
     t.start();
 
     QMap<QString, QString> customMap;
 
     FindCustomQuery query( uid );
     OSQLResult res_custom = m_driver->query( &query );
 
     if ( res_custom.state() == OSQLResult::Failure ) {
         owarn << "OSQLResult::Failure in find query !!" << oendl;
         QMap<QString, QString> empty;
         return empty;
     }
 
     OSQLResultItem::ValueList list = res_custom.results();
     OSQLResultItem::ValueList::Iterator it = list.begin();
     for ( ; it != list.end(); ++it ) {
         customMap.insert( (*it).data( "type" ), (*it).data( "value" ) );
     }
 
     odebug << "RequestCustom needed: " << t.elapsed() << " ms" << oendl;
     return customMap;
 }
 
 }