Simplify SQL query to not need a temporary SQL table

Patch courtsey Marcin Juszkiewicz (okayed by eilers)

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
@@ -563,68 +563,66 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
     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.
-		    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')" )
+				datediff_query = "SELECT uid,substr(\"Birthday\", 6, 10) as \"BirthdayMD\", substr(\"Anniversary\", 6, 10) as \"AnniversaryMD\" FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' ) AND ";
+				datediff_query += QString( " (\"%1MD\" <= '%2-%3\' AND \"%4MD\" >= '%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 ){
                     searchQuery += QString( " (\"%1\" LIKE '%2-%')" )
                         .arg( *it )
                         .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) );
                 }
 
                 if ( settings & OPimContactAccess::DateMonth ){
                     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::DateYear )
                          || ( settings & OPimContactAccess::DateMonth ) )
                         searchQuery += " AND";
 
                     searchQuery += QString( " (\"%1\" LIKE '%-%-%2')" )
                         .arg( *it )
@@ -655,71 +653,64 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
 		searchQuery.remove( searchQuery.length()-3, 3 ); // Hmmmm..
 	}
 
     }
 
     // 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 = datediff_query + QString( " INTERSECT " ) + qu + searchQuery;
     } else if ( datediff_query.isEmpty() && !searchQuery.isEmpty() ){
 	    qu += searchQuery;
     } 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 ( !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