summaryrefslogtreecommitdiff
Side-by-side diff
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,34 +1,36 @@
-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
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 2368865..50421e2 100644
--- a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
+++ b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
@@ -454,306 +454,332 @@ bool OPimContactAccessBackend_SQL::add ( const OPimContact &newcontact )
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";
// 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 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 );
- 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 );
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;
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
{