summaryrefslogtreecommitdiff
Side-by-side diff
Diffstat (more/less context) (show whitespace changes)
-rw-r--r--libopie2/opiepim/ChangeLog2
-rw-r--r--libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp58
2 files changed, 33 insertions, 27 deletions
diff --git a/libopie2/opiepim/ChangeLog b/libopie2/opiepim/ChangeLog
index acb6cb1..ee063aa 100644
--- a/libopie2/opiepim/ChangeLog
+++ b/libopie2/opiepim/ChangeLog
@@ -1,16 +1,18 @@
+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
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
@@ -520,48 +520,48 @@ OPimContact OPimContactAccessBackend_SQL::find( int uid, const UIDArray& queryUi
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 ){
@@ -579,122 +579,126 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
// 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 " + "'"
+ queryStr.replace(QRegExp("\\*"),"%") + "'" + ")";
else
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 (";