summaryrefslogtreecommitdiff
Side-by-side diff
Diffstat (more/less context) (show whitespace changes)
-rw-r--r--libopie2/opiepim/ChangeLog4
-rw-r--r--libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp42
2 files changed, 37 insertions, 9 deletions
diff --git a/libopie2/opiepim/ChangeLog b/libopie2/opiepim/ChangeLog
index 84517fb..acb6cb1 100644
--- a/libopie2/opiepim/ChangeLog
+++ b/libopie2/opiepim/ChangeLog
@@ -1,25 +1,27 @@
-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
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
@@ -527,161 +527,187 @@ OPimContact OPimContactAccessBackend_SQL::find( int uid, const UIDArray& queryUi
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 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 );
+ // 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;