summaryrefslogtreecommitdiff
authoreilers <eilers>2005-03-20 17:47:23 (UTC)
committer eilers <eilers>2005-03-20 17:47:23 (UTC)
commit22d113c0b0dc0a9a71cb55f565c4df04272809e1 (patch) (side-by-side diff)
tree4decc50e15c93f1c6afc9131e3124c4f11115475
parent5bc93a55c05c7292502c908fc0a99d5010a641c7 (diff)
downloadopie-22d113c0b0dc0a9a71cb55f565c4df04272809e1.zip
opie-22d113c0b0dc0a9a71cb55f565c4df04272809e1.tar.gz
opie-22d113c0b0dc0a9a71cb55f565c4df04272809e1.tar.bz2
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..
Diffstat (more/less context) (ignore whitespace changes)
-rw-r--r--libopie2/opiepim/ChangeLog4
-rw-r--r--libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp46
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,7 +1,9 @@
-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
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
@@ -544,13 +544,14 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
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
@@ -582,26 +583,35 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
// 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' ) );
}
@@ -640,17 +650,26 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
}
}
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 );
@@ -658,12 +677,19 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
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