author | eilers <eilers> | 2005-03-20 17:47:23 (UTC) |
---|---|---|
committer | eilers <eilers> | 2005-03-20 17:47:23 (UTC) |
commit | 22d113c0b0dc0a9a71cb55f565c4df04272809e1 (patch) (side-by-side diff) | |
tree | 4decc50e15c93f1c6afc9131e3124c4f11115475 | |
parent | 5bc93a55c05c7292502c908fc0a99d5010a641c7 (diff) | |
download | opie-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..
-rw-r--r-- | libopie2/opiepim/ChangeLog | 4 | ||||
-rw-r--r-- | libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | 42 |
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,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 @@ -295,625 +295,651 @@ namespace { : OSQLQuery(), m_uid( uid ) { } FindQuery::FindQuery(const UIDArray& ints) : OSQLQuery(), m_uids( ints ){ } FindQuery::~FindQuery() { } QString FindQuery::query()const{ if ( m_uids.count() == 0 ) return single(); else return multi(); } QString FindQuery::multi()const { QString qu = "select * from addressbook where"; for (uint i = 0; i < m_uids.count(); i++ ) { qu += " uid = " + QString::number( m_uids[i] ) + " OR"; } qu.remove( qu.length()-2, 2 ); // Hmmmm.. odebug << "find query: " << qu << "" << oendl; return qu; } QString FindQuery::single()const{ QString qu = "select *"; qu += " from addressbook where uid = " + QString::number(m_uid); // owarn << "find query: " << qu << "" << oendl; return qu; } FindCustomQuery::FindCustomQuery(int uid) : OSQLQuery(), m_uid( uid ) { } FindCustomQuery::FindCustomQuery(const UIDArray& ints) : OSQLQuery(), m_uids( ints ){ } FindCustomQuery::~FindCustomQuery() { } QString FindCustomQuery::query()const{ // if ( m_uids.count() == 0 ) return single(); } QString FindCustomQuery::single()const{ QString qu = "select uid, type, value from custom_data where uid = "; qu += QString::number(m_uid); return qu; } }; /* --------------------------------------------------------------------------- */ namespace Opie { OPimContactAccessBackend_SQL::OPimContactAccessBackend_SQL ( const QString& /* appname */, const QString& filename ): OPimContactAccessBackend(), m_changed(false), m_driver( NULL ) { odebug << "C'tor OPimContactAccessBackend_SQL starts" << oendl; QTime t; t.start(); /* Expecting to access the default filename if nothing else is set */ if ( filename.isEmpty() ){ m_fileName = Global::applicationFileName( "addressbook","addressbook.db" ); } else m_fileName = filename; // Get the standart sql-driver from the OSQLManager.. OSQLManager man; m_driver = man.standard(); m_driver->setUrl( m_fileName ); load(); odebug << "C'tor OPimContactAccessBackend_SQL ends: " << t.elapsed() << " ms" << oendl; } OPimContactAccessBackend_SQL::~OPimContactAccessBackend_SQL () { if( m_driver ) delete m_driver; } bool OPimContactAccessBackend_SQL::load () { if (!m_driver->open() ) return false; // Don't expect that the database exists. // It is save here to create the table, even if it // do exist. ( Is that correct for all databases ?? ) CreateQuery creat; OSQLResult res = m_driver->query( &creat ); update(); return true; } bool OPimContactAccessBackend_SQL::reload() { return load(); } bool OPimContactAccessBackend_SQL::save() { return m_driver->close(); // Shouldn't m_driver->sync be better than close ? (eilers) } void OPimContactAccessBackend_SQL::clear () { ClearQuery cle; OSQLResult res = m_driver->query( &cle ); reload(); } bool OPimContactAccessBackend_SQL::wasChangedExternally() { return false; } UIDArray OPimContactAccessBackend_SQL::allRecords() const { // FIXME: Think about cute handling of changed tables.. // Thus, we don't have to call update here... if ( m_changed ) ((OPimContactAccessBackend_SQL*)this)->update(); return m_uids; } bool OPimContactAccessBackend_SQL::add ( const OPimContact &newcontact ) { odebug << "add in contact SQL-Backend" << oendl; InsertQuery ins( newcontact ); OSQLResult res = m_driver->query( &ins ); if ( res.state() == OSQLResult::Failure ) return false; int c = m_uids.count(); m_uids.resize( c+1 ); m_uids[c] = newcontact.uid(); return true; } 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 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; 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 { QTime t; t.start(); QString query = "SELECT uid FROM addressbook"; query += " WHERE ("; for ( uint i = 0; i < ar.count(); i++ ) { query += " uid = " + QString::number( ar[i] ) + " OR"; } query.remove( query.length()-2, 2 ); // Hmmmm.. query += ")"; if ( filter != OPimBase::FilterOff ){ if ( filter & OPimContactAccess::DoNotShowWithCategory ){ query += " AND ( \"Categories\" == '' )"; } else if ( filter & OPimBase::FilterCategory ){ query += " AND ("; for ( uint i = 0; i < categories.count(); i++ ){ query += "\"Categories\" LIKE"; query += QString( " '%" ) + QString::number( categories[i] ) + "%' OR"; } query.remove( query.length()-2, 2 ); // Hmmmm.. query += ")"; } if ( filter & OPimContactAccess::DoNotShowWithoutChildren ){ query += " AND ( \"Children\" != '' )"; } if ( filter & OPimContactAccess::DoNotShowWithoutAnniversary ){ query += " AND ( \"Anniversary\" != '' )"; } if ( filter & OPimContactAccess::DoNotShowWithoutBirthday ){ query += " AND ( \"Birthday\" != '' )"; } if ( filter & OPimContactAccess::DoNotShowWithoutHomeAddress ){ // Expect that no Street means no Address, too! (eilers) query += " AND ( \"Home Street\" != '' )"; } if ( filter & OPimContactAccess::DoNotShowWithoutBusinessAddress ){ // Expect that no Street means no Address, too! (eilers) query += " AND ( \"Business Street\" != '' )"; } } query += " ORDER BY"; switch ( sortOrder ) { case OPimContactAccess::SortSummary: query += " \"Notes\""; break; case OPimContactAccess::SortByCategory: query += " \"Categories\""; break; case OPimContactAccess::SortByDate: query += " \"\""; break; case OPimContactAccess::SortTitle: query += " \"Name Title\""; break; case OPimContactAccess::SortFirstName: query += " \"First Name\""; break; case OPimContactAccess::SortMiddleName: query += " \"Middle Name\""; break; case OPimContactAccess::SortLastName: query += " \"Last Name\""; break; case OPimContactAccess::SortFileAsName: query += " \"File As\""; break; case OPimContactAccess::SortSuffix: query += " \"Suffix\""; break; case OPimContactAccess::SortEmail: query += " \"Default Email\""; break; case OPimContactAccess::SortNickname: query += " \"Nickname\""; break; case OPimContactAccess::SortAnniversary: query += " \"Anniversary\""; break; case OPimContactAccess::SortBirthday: query += " \"Birthday\""; break; case OPimContactAccess::SortGender: query += " \"Gender\""; break; default: query += " \"Last Name\""; } if ( !asc ) query += " DESC"; odebug << "sorted query is: " << query << "" << oendl; OSQLRawQuery raw( query ); OSQLResult res = m_driver->query( &raw ); if ( res.state() != OSQLResult::Success ){ UIDArray empty; return empty; } UIDArray list = extractUids( res ); odebug << "sorted needed " << t.elapsed() << " ms!" << oendl; return list; } void OPimContactAccessBackend_SQL::update() { odebug << "Update starts" << oendl; QTime t; t.start(); // Now load the database set and extract the uid's // which will be held locally LoadQuery lo; OSQLResult res = m_driver->query(&lo); if ( res.state() != OSQLResult::Success ) return; m_uids = extractUids( res ); m_changed = false; odebug << "Update ends " << t.elapsed() << " ms" << oendl; } UIDArray OPimContactAccessBackend_SQL::extractUids( OSQLResult& res ) const { odebug << "extractUids" << oendl; QTime t; t.start(); OSQLResultItem::ValueList list = res.results(); OSQLResultItem::ValueList::Iterator it; UIDArray ints(list.count() ); odebug << " count = " << list.count() << "" << oendl; int i = 0; for (it = list.begin(); it != list.end(); ++it ) { ints[i] = (*it).data("uid").toInt(); i++; } odebug << "extractUids ready: count2 = " << i << " needs " << t.elapsed() << " ms" << oendl; return ints; } |