-rw-r--r-- | libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | 307 | ||||
-rw-r--r-- | libopie2/opiepim/backend/otodoaccesssql.cpp | 90 | ||||
-rw-r--r-- | libopie2/opiepim/ocontactfields.cpp | 10 |
3 files changed, 366 insertions, 41 deletions
diff --git a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp index 4afa5f3..132c9fc 100644 --- a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp +++ b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp | |||
@@ -15,4 +15,9 @@ | |||
15 | * History: | 15 | * History: |
16 | * $Log$ | 16 | * $Log$ |
17 | * Revision 1.2 2003/09/29 07:44:26 eilers | ||
18 | * Improvement of PIM-SQL Databases, but search queries are still limited. | ||
19 | * Addressbook: Changed table layout. Now, we just need 1/3 of disk-space. | ||
20 | * Todo: Started to add new attributes. Some type conversions missing. | ||
21 | * | ||
17 | * Revision 1.1 2003/09/22 14:31:16 eilers | 22 | * Revision 1.1 2003/09/22 14:31:16 eilers |
18 | * Added first experimental incarnation of sql-backend for addressbook. | 23 | * Added first experimental incarnation of sql-backend for addressbook. |
@@ -38,4 +43,20 @@ | |||
38 | #include <opie2/osqlquery.h> | 43 | #include <opie2/osqlquery.h> |
39 | 44 | ||
45 | |||
46 | |||
47 | |||
48 | // If defined, we use a horizontal table ( uid, attr1, attr2, attr3, ..., attrn ) instead | ||
49 | // vertical like "uid, type, value". | ||
50 | // DON'T DEACTIVATE THIS DEFINE IN PRODUCTIVE ENVIRONMENTS !! | ||
51 | #define __STORE_HORIZONTAL_ | ||
52 | |||
53 | // Distinct loading is not very fast. If I expect that every person has just | ||
54 | // one (and always one) 'Last Name', I can request all uid's for existing lastnames, | ||
55 | // which is faster.. | ||
56 | // But this may not be true for all entries, like company contacts.. | ||
57 | // The current AddressBook application handles this problem, but other may not.. (eilers) | ||
58 | #define __USE_SUPERFAST_LOADQUERY | ||
59 | |||
60 | |||
40 | /* | 61 | /* |
41 | * Implementation of used query types | 62 | * Implementation of used query types |
@@ -141,6 +162,5 @@ namespace { | |||
141 | // We using three tables to store the information: | 162 | // We using three tables to store the information: |
142 | // 1. addressbook : It contains General information about the contact (non custom) | 163 | // 1. addressbook : It contains General information about the contact (non custom) |
143 | // 2. dates : Stuff like birthdays, anniversaries, etc. | 164 | // 2. custom_data : Not official supported entries |
144 | // 3. custom_data : Not official supported entries | ||
145 | // All tables are connected by the uid of the contact. | 165 | // All tables are connected by the uid of the contact. |
146 | // Maybe I should add a table for meta-information ? | 166 | // Maybe I should add a table for meta-information ? |
@@ -149,7 +169,23 @@ namespace { | |||
149 | QString CreateQuery::query()const { | 169 | QString CreateQuery::query()const { |
150 | QString qu; | 170 | QString qu; |
171 | #ifdef __STORE_HORIZONTAL_ | ||
172 | |||
173 | qu += "create table addressbook( uid PRIMARY KEY "; | ||
174 | |||
175 | QStringList fieldList = OContactFields::untrfields( false ); | ||
176 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | ||
177 | qu += QString( ",\"%1\" VARCHAR(10)" ).arg( *it ); | ||
178 | } | ||
179 | qu += " );"; | ||
180 | |||
181 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; | ||
182 | |||
183 | #else | ||
184 | |||
151 | qu += "create table addressbook( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id));"; | 185 | qu += "create table addressbook( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id));"; |
152 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; | 186 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; |
153 | // qu += "create table dates( uid PRIMARY KEY, type, day, month, year, hour, minute, second );"; | 187 | // qu += "create table dates( uid PRIMARY KEY, type, day, month, year, hour, minute, second );"; |
188 | |||
189 | #endif // __STORE_HORIZONTAL_ | ||
154 | return qu; | 190 | return qu; |
155 | } | 191 | } |
@@ -161,14 +197,8 @@ namespace { | |||
161 | QString qu = "drop table addressbook;"; | 197 | QString qu = "drop table addressbook;"; |
162 | qu += "drop table custom_data;"; | 198 | qu += "drop table custom_data;"; |
163 | qu += "drop table dates;"; | 199 | // qu += "drop table dates;"; |
164 | return qu; | 200 | return qu; |
165 | } | 201 | } |
166 | 202 | ||
167 | // Distinct loading is not very fast. If I expect that every person has just | ||
168 | // one (and always one) 'Last Name', I can request all uid's for existing lastnames, | ||
169 | // which is faster.. | ||
170 | // But this may not be true for all entries, like company contacts.. | ||
171 | // The current AddressBook application handles this problem, but other may not.. (eilers) | ||
172 | #define __USE_SUPERFAST_LOADQUERY | ||
173 | 203 | ||
174 | LoadQuery::LoadQuery() : OSQLQuery() {} | 204 | LoadQuery::LoadQuery() : OSQLQuery() {} |
@@ -176,9 +206,13 @@ namespace { | |||
176 | QString LoadQuery::query()const { | 206 | QString LoadQuery::query()const { |
177 | QString qu; | 207 | QString qu; |
208 | #ifdef __STORE_HORIZONTAL_ | ||
209 | qu += "select uid from addressbook"; | ||
210 | #else | ||
178 | #ifndef __USE_SUPERFAST_LOADQUERY | 211 | #ifndef __USE_SUPERFAST_LOADQUERY |
179 | qu += "select distinct uid from addressbook"; | 212 | qu += "select distinct uid from addressbook"; |
180 | #else | 213 | #else |
181 | qu += "select uid from addressbook where type = 'Last Name'"; | 214 | qu += "select uid from addressbook where type = 'Last Name'"; |
182 | #endif | 215 | # endif // __USE_SUPERFAST_LOADQUERY |
216 | #endif // __STORE_HORIZONTAL_ | ||
183 | 217 | ||
184 | return qu; | 218 | return qu; |
@@ -198,8 +232,60 @@ namespace { | |||
198 | QString InsertQuery::query()const{ | 232 | QString InsertQuery::query()const{ |
199 | 233 | ||
234 | #ifdef __STORE_HORIZONTAL_ | ||
235 | QString qu; | ||
236 | qu += "insert into addressbook VALUES( " + | ||
237 | QString::number( m_contact.uid() ); | ||
238 | |||
239 | // Get all information out of the contact-class | ||
240 | // Remember: The category is stored in contactMap, too ! | ||
241 | QMap<int, QString> contactMap = m_contact.toMap(); | ||
242 | |||
243 | QStringList fieldList = OContactFields::untrfields( false ); | ||
244 | QMap<QString, int> translate = OContactFields::untrFieldsToId(); | ||
245 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | ||
246 | // Convert Column-String to Id and get value for this id.. | ||
247 | // Hmmm.. Maybe not very cute solution.. | ||
248 | int id = translate[*it]; | ||
249 | switch ( id ){ | ||
250 | case Qtopia::Birthday:{ | ||
251 | // These entries should stored in a special format | ||
252 | // year-month-day | ||
253 | QDate day = m_contact.birthday(); | ||
254 | if ( day.isValid() ){ | ||
255 | qu += QString(",\"%1-%2-%3\"") | ||
256 | .arg( day.year() ) | ||
257 | .arg( day.month() ) | ||
258 | .arg( day.day() ); | ||
259 | } else { | ||
260 | qu += ",\"\""; | ||
261 | } | ||
262 | } | ||
263 | break; | ||
264 | case Qtopia::Anniversary:{ | ||
265 | // These entries should stored in a special format | ||
266 | // year-month-day | ||
267 | QDate day = m_contact.anniversary(); | ||
268 | if ( day.isValid() ){ | ||
269 | qu += QString(",\"%1-%2-%3\"") | ||
270 | .arg( day.year() ) | ||
271 | .arg( day.month() ) | ||
272 | .arg( day.day() ); | ||
273 | } else { | ||
274 | qu += ",\"\""; | ||
275 | } | ||
276 | } | ||
277 | break; | ||
278 | |||
279 | default: | ||
280 | qu += QString( ",\"%1\"" ).arg( contactMap[id] ); | ||
281 | } | ||
282 | } | ||
283 | qu += " );"; | ||
284 | |||
285 | |||
286 | #else | ||
200 | // Get all information out of the contact-class | 287 | // Get all information out of the contact-class |
201 | // Remember: The category is stored in contactMap, too ! | 288 | // Remember: The category is stored in contactMap, too ! |
202 | QMap<int, QString> contactMap = m_contact.toMap(); | 289 | QMap<int, QString> contactMap = m_contact.toMap(); |
203 | QMap<QString, QString> customMap = m_contact.toExtraMap(); | ||
204 | 290 | ||
205 | QMap<QString, QString> addressbook_db; | 291 | QMap<QString, QString> addressbook_db; |
@@ -262,6 +348,11 @@ namespace { | |||
262 | } | 348 | } |
263 | 349 | ||
350 | #endif //__STORE_HORIZONTAL_ | ||
264 | // Now add custom data.. | 351 | // Now add custom data.. |
352 | #ifdef __STORE_HORIZONTAL_ | ||
353 | int id = 0; | ||
354 | #endif | ||
265 | id = 0; | 355 | id = 0; |
356 | QMap<QString, QString> customMap = m_contact.toExtraMap(); | ||
266 | for( QMap<QString, QString>::Iterator it = customMap.begin(); | 357 | for( QMap<QString, QString>::Iterator it = customMap.begin(); |
267 | it != customMap.end(); ++it ){ | 358 | it != customMap.end(); ++it ){ |
@@ -278,5 +369,4 @@ namespace { | |||
278 | + "');"; | 369 | + "');"; |
279 | } | 370 | } |
280 | |||
281 | // qu += "commit;"; | 371 | // qu += "commit;"; |
282 | qWarning("add %s", qu.latin1() ); | 372 | qWarning("add %s", qu.latin1() ); |
@@ -291,6 +381,4 @@ namespace { | |||
291 | QString qu = "DELETE from addressbook where uid = " | 381 | QString qu = "DELETE from addressbook where uid = " |
292 | + QString::number(m_uid) + ";"; | 382 | + QString::number(m_uid) + ";"; |
293 | qu += "DELETE from dates where uid = " | ||
294 | + QString::number(m_uid) + ";"; | ||
295 | qu += "DELETE from custom_data where uid = " | 383 | qu += "DELETE from custom_data where uid = " |
296 | + QString::number(m_uid) + ";"; | 384 | + QString::number(m_uid) + ";"; |
@@ -326,4 +414,13 @@ namespace { | |||
326 | } | 414 | } |
327 | */ | 415 | */ |
416 | #ifdef __STORE_HORIZONTAL_ | ||
417 | QString FindQuery::single()const{ | ||
418 | QString qu = "select *"; | ||
419 | qu += " from addressbook where uid = " + QString::number(m_uid); | ||
420 | |||
421 | // qWarning("find query: %s", qu.latin1() ); | ||
422 | return qu; | ||
423 | } | ||
424 | #else | ||
328 | QString FindQuery::single()const{ | 425 | QString FindQuery::single()const{ |
329 | QString qu = "select uid, type, value from addressbook where uid = "; | 426 | QString qu = "select uid, type, value from addressbook where uid = "; |
@@ -331,4 +428,5 @@ namespace { | |||
331 | return qu; | 428 | return qu; |
332 | } | 429 | } |
430 | #endif | ||
333 | 431 | ||
334 | 432 | ||
@@ -376,5 +474,5 @@ OContactAccessBackend_SQL::OContactAccessBackend_SQL ( const QString& /* appname | |||
376 | load(); | 474 | load(); |
377 | 475 | ||
378 | qWarning("C'tor OContactAccessBackend_SQL ends: %d", t.elapsed() ); | 476 | qWarning("C'tor OContactAccessBackend_SQL ends: %d ms", t.elapsed() ); |
379 | } | 477 | } |
380 | 478 | ||
@@ -479,5 +577,5 @@ OContact OContactAccessBackend_SQL::find ( int uid ) const | |||
479 | retContact.setExtraMap( requestCustom( uid ) ); | 577 | retContact.setExtraMap( requestCustom( uid ) ); |
480 | 578 | ||
481 | qWarning("OContactAccessBackend_SQL::find() needed: %d", t.elapsed() ); | 579 | qWarning("OContactAccessBackend_SQL::find() needed: %d ms", t.elapsed() ); |
482 | return retContact; | 580 | return retContact; |
483 | } | 581 | } |
@@ -487,6 +585,49 @@ OContact OContactAccessBackend_SQL::find ( int uid ) const | |||
487 | QArray<int> OContactAccessBackend_SQL::queryByExample ( const OContact &query, int settings, const QDateTime& d = QDateTime() ) | 585 | QArray<int> OContactAccessBackend_SQL::queryByExample ( const OContact &query, int settings, const QDateTime& d = QDateTime() ) |
488 | { | 586 | { |
489 | QArray<int> nix(0); | 587 | QString qu = "SELECT uid FROM addressbook WHERE"; |
490 | return nix; | 588 | |
589 | QMap<int, QString> queryFields = query.toMap(); | ||
590 | QStringList fieldList = OContactFields::untrfields( false ); | ||
591 | QMap<QString, int> translate = OContactFields::untrFieldsToId(); | ||
592 | |||
593 | // Convert every filled field to a SQL-Query | ||
594 | bool isAnyFieldSelected = false; | ||
595 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | ||
596 | int id = translate[*it]; | ||
597 | QString queryStr = queryFields[id]; | ||
598 | if ( !queryStr.isEmpty() ){ | ||
599 | isAnyFieldSelected = true; | ||
600 | switch( id ){ | ||
601 | default: | ||
602 | // Switching between case sensitive and insensitive... | ||
603 | // LIKE is not case sensitive, GLOB is case sensitive | ||
604 | // Do exist a better solution to switch this ? | ||
605 | if ( settings & OContactAccess::IgnoreCase ) | ||
606 | qu += "(\"" + *it + "\"" + " LIKE " + "'" | ||
607 | + queryStr.replace(QRegExp("\\*"),"%") + "'" + ") AND "; | ||
608 | else | ||
609 | qu += "(\"" + *it + "\"" + " GLOB " + "'" | ||
610 | + queryStr + "'" + ") AND "; | ||
611 | |||
612 | } | ||
613 | } | ||
614 | } | ||
615 | // Skip trailing "AND" | ||
616 | if ( isAnyFieldSelected ) | ||
617 | qu = qu.left( qu.length() - 4 ); | ||
618 | |||
619 | qWarning( "queryByExample query: %s", qu.latin1() ); | ||
620 | |||
621 | // Execute query and return the received uid's | ||
622 | OSQLRawQuery raw( qu ); | ||
623 | OSQLResult res = m_driver->query( &raw ); | ||
624 | if ( res.state() != OSQLResult::Success ){ | ||
625 | QArray<int> empty; | ||
626 | return empty; | ||
627 | } | ||
628 | |||
629 | QArray<int> list = extractUids( res ); | ||
630 | |||
631 | return list; | ||
491 | } | 632 | } |
492 | 633 | ||
@@ -499,10 +640,54 @@ QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const | |||
499 | const uint OContactAccessBackend_SQL::querySettings() | 640 | const uint OContactAccessBackend_SQL::querySettings() |
500 | { | 641 | { |
501 | return 0; | 642 | return OContactAccess::IgnoreCase |
643 | || OContactAccess::WildCards; | ||
502 | } | 644 | } |
503 | 645 | ||
504 | bool OContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const | 646 | bool OContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const |
505 | { | 647 | { |
648 | /* OContactAccess::IgnoreCase, DateDiff, DateYear, DateMonth, DateDay | ||
649 | * may be added with any of the other settings. IgnoreCase should never used alone. | ||
650 | * Wildcards, RegExp, ExactMatch should never used at the same time... | ||
651 | */ | ||
652 | |||
653 | // Step 1: Check whether the given settings are supported by this backend | ||
654 | if ( ( querySettings & ( | ||
655 | OContactAccess::IgnoreCase | ||
656 | | OContactAccess::WildCards | ||
657 | // | OContactAccess::DateDiff | ||
658 | // | OContactAccess::DateYear | ||
659 | // | OContactAccess::DateMonth | ||
660 | // | OContactAccess::DateDay | ||
661 | // | OContactAccess::RegExp | ||
662 | // | OContactAccess::ExactMatch | ||
663 | ) ) != querySettings ) | ||
506 | return false; | 664 | return false; |
665 | |||
666 | // Step 2: Check whether the given combinations are ok.. | ||
667 | |||
668 | // IngoreCase alone is invalid | ||
669 | if ( querySettings == OContactAccess::IgnoreCase ) | ||
670 | return false; | ||
671 | |||
672 | // WildCards, RegExp and ExactMatch should never used at the same time | ||
673 | switch ( querySettings & ~( OContactAccess::IgnoreCase | ||
674 | | OContactAccess::DateDiff | ||
675 | | OContactAccess::DateYear | ||
676 | | OContactAccess::DateMonth | ||
677 | | OContactAccess::DateDay | ||
678 | ) | ||
679 | ){ | ||
680 | case OContactAccess::RegExp: | ||
681 | return ( true ); | ||
682 | case OContactAccess::WildCards: | ||
683 | return ( true ); | ||
684 | case OContactAccess::ExactMatch: | ||
685 | return ( true ); | ||
686 | case 0: // one of the upper removed bits were set.. | ||
687 | return ( true ); | ||
688 | default: | ||
689 | return ( false ); | ||
690 | } | ||
691 | |||
507 | } | 692 | } |
508 | 693 | ||
@@ -512,12 +697,16 @@ QArray<int> OContactAccessBackend_SQL::sorted( bool asc, int , int , int ) | |||
512 | t.start(); | 697 | t.start(); |
513 | 698 | ||
514 | // Not implemented.. | 699 | #ifdef __STORE_HORIZONTAL_ |
700 | QString query = "SELECT uid FROM addressbook "; | ||
701 | query += "ORDER BY \"Last Name\" "; | ||
702 | #else | ||
515 | QString query = "SELECT uid FROM addressbook WHERE type = 'Last Name' "; | 703 | QString query = "SELECT uid FROM addressbook WHERE type = 'Last Name' "; |
704 | query += "ORDER BY upper( value )"; | ||
705 | #endif | ||
516 | 706 | ||
517 | query += "ORDER BY value "; | ||
518 | if ( !asc ) | 707 | if ( !asc ) |
519 | query += "DESC"; | 708 | query += "DESC"; |
520 | 709 | ||
521 | qWarning("sorted query is: %s", query.latin1() ); | 710 | // qWarning("sorted query is: %s", query.latin1() ); |
522 | 711 | ||
523 | OSQLRawQuery raw( query ); | 712 | OSQLRawQuery raw( query ); |
@@ -553,5 +742,5 @@ void OContactAccessBackend_SQL::update() | |||
553 | m_changed = false; | 742 | m_changed = false; |
554 | 743 | ||
555 | qWarning("Update ends %d", t.elapsed() ); | 744 | qWarning("Update ends %d ms", t.elapsed() ); |
556 | } | 745 | } |
557 | 746 | ||
@@ -577,4 +766,70 @@ QArray<int> OContactAccessBackend_SQL::extractUids( OSQLResult& res ) const | |||
577 | } | 766 | } |
578 | 767 | ||
768 | #ifdef __STORE_HORIZONTAL_ | ||
769 | QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const | ||
770 | { | ||
771 | QTime t; | ||
772 | t.start(); | ||
773 | |||
774 | QMap<int, QString> nonCustomMap; | ||
775 | |||
776 | int t2needed = 0; | ||
777 | int t3needed = 0; | ||
778 | QTime t2; | ||
779 | t2.start(); | ||
780 | FindQuery query( uid ); | ||
781 | OSQLResult res_noncustom = m_driver->query( &query ); | ||
782 | t2needed = t2.elapsed(); | ||
783 | |||
784 | OSQLResultItem resItem = res_noncustom.first(); | ||
785 | |||
786 | QTime t3; | ||
787 | t3.start(); | ||
788 | // Now loop through all columns | ||
789 | QStringList fieldList = OContactFields::untrfields( false ); | ||
790 | QMap<QString, int> translate = OContactFields::untrFieldsToId(); | ||
791 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | ||
792 | // Get data for the selected column and store it with the | ||
793 | // corresponding id into the map.. | ||
794 | |||
795 | int id = translate[*it]; | ||
796 | QString value = resItem.data( (*it) ); | ||
797 | |||
798 | // qWarning("Reading %s... found: %s", (*it).latin1(), value.latin1() ); | ||
799 | |||
800 | switch( id ){ | ||
801 | case Qtopia::Birthday: | ||
802 | case Qtopia::Anniversary:{ | ||
803 | // Birthday and Anniversary are encoded special ( yyyy-mm-dd ) | ||
804 | QStringList list = QStringList::split( '-', value ); | ||
805 | QStringList::Iterator lit = list.begin(); | ||
806 | int year = (*lit).toInt(); | ||
807 | int month = (*(++lit)).toInt(); | ||
808 | int day = (*(++lit)).toInt(); | ||
809 | if ( ( day != 0 ) && ( month != 0 ) && ( year != 0 ) ){ | ||
810 | QDate date( year, month, day ); | ||
811 | nonCustomMap.insert( id, OConversion::dateToString( date ) ); | ||
812 | } | ||
813 | } | ||
814 | break; | ||
815 | case Qtopia::AddressCategory: | ||
816 | qWarning("Category is: %s", value.latin1() ); | ||
817 | default: | ||
818 | nonCustomMap.insert( id, value ); | ||
819 | } | ||
820 | } | ||
821 | |||
822 | // First insert uid | ||
823 | nonCustomMap.insert( Qtopia::AddressUid, resItem.data( "uid" ) ); | ||
824 | t3needed = t3.elapsed(); | ||
825 | |||
826 | // qWarning("Adding UID: %s", resItem.data( "uid" ).latin1() ); | ||
827 | qWarning("RequestNonCustom needed: insg.:%d ms, query: %d ms, mapping: %d ms", | ||
828 | t.elapsed(), t2needed, t3needed ); | ||
829 | |||
830 | return nonCustomMap; | ||
831 | } | ||
832 | #else | ||
833 | |||
579 | QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const | 834 | QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const |
580 | { | 835 | { |
@@ -634,8 +889,10 @@ QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const | |||
634 | t3needed = t3.elapsed(); | 889 | t3needed = t3.elapsed(); |
635 | 890 | ||
636 | qWarning("RequestNonCustom needed: ins:%d, query: %d, mapping: %d", t.elapsed(), t2needed, t3needed ); | 891 | qWarning("RequestNonCustom needed: insg.:%d ms, query: %d ms, mapping: %d ms", t.elapsed(), t2needed, t3needed ); |
637 | return nonCustomMap; | 892 | return nonCustomMap; |
638 | } | 893 | } |
639 | 894 | ||
895 | #endif // __STORE_HORIZONTAL_ | ||
896 | |||
640 | QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) const | 897 | QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) const |
641 | { | 898 | { |
@@ -660,5 +917,5 @@ QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) cons | |||
660 | } | 917 | } |
661 | 918 | ||
662 | qWarning("RequestCustom needed: %d", t.elapsed() ); | 919 | qWarning("RequestCustom needed: %d ms", t.elapsed() ); |
663 | return customMap; | 920 | return customMap; |
664 | } | 921 | } |
diff --git a/libopie2/opiepim/backend/otodoaccesssql.cpp b/libopie2/opiepim/backend/otodoaccesssql.cpp index 23e0c3e..d255c66 100644 --- a/libopie2/opiepim/backend/otodoaccesssql.cpp +++ b/libopie2/opiepim/backend/otodoaccesssql.cpp | |||
@@ -119,6 +119,7 @@ namespace { | |||
119 | QString CreateQuery::query()const { | 119 | QString CreateQuery::query()const { |
120 | QString qu; | 120 | QString qu; |
121 | qu += "create table todolist( uid, categories, completed, progress, "; | 121 | qu += "create table todolist( uid PRIMARY KEY, categories, completed, "; |
122 | qu += "summary, DueDate, priority, description )"; | 122 | qu += "description, summary, priority, DueDate, progress , state, "; |
123 | qu += "Recurrence, notifiers, maintainer, startdate, completeddate)"; | ||
123 | return qu; | 124 | return qu; |
124 | } | 125 | } |
@@ -128,5 +129,7 @@ namespace { | |||
128 | QString LoadQuery::query()const { | 129 | QString LoadQuery::query()const { |
129 | QString qu; | 130 | QString qu; |
130 | qu += "select distinct uid from todolist"; | 131 | // We do not need "distinct" here. The primary key is always unique.. |
132 | //qu += "select distinct uid from todolist"; | ||
133 | qu += "select uid from todolist"; | ||
131 | 134 | ||
132 | return qu; | 135 | return qu; |
@@ -152,9 +155,43 @@ namespace { | |||
152 | day = date.day(); | 155 | day = date.day(); |
153 | } | 156 | } |
157 | int sYear = 0, sMonth = 0, sDay = 0; | ||
158 | if( m_todo.hasStartDate() ){ | ||
159 | QDate sDate = m_todo.startDate(); | ||
160 | sYear = sDate.year(); | ||
161 | sMonth= sDate.month(); | ||
162 | sDay = sDate.day(); | ||
163 | } | ||
164 | |||
165 | int eYear = 0, eMonth = 0, eDay = 0; | ||
166 | if( m_todo.hasCompletedDate() ){ | ||
167 | QDate eDate = m_todo.completedDate(); | ||
168 | eYear = eDate.year(); | ||
169 | eMonth= eDate.month(); | ||
170 | eDay = eDate.day(); | ||
171 | } | ||
154 | QString qu; | 172 | QString qu; |
155 | qu = "insert into todolist VALUES(" + QString::number( m_todo.uid() ) + ",'" + m_todo.idsToString( m_todo.categories() ) + "',"; | 173 | qu = "insert into todolist VALUES(" |
156 | qu += QString::number( m_todo.isCompleted() ) + "," + QString::number( m_todo.progress() ) + ","; | 174 | + QString::number( m_todo.uid() ) + "," |
157 | qu += "'"+m_todo.summary()+"','"+QString::number(year)+"-"+QString::number(month)+"-"+QString::number(day)+"',"; | 175 | + "'" + m_todo.idsToString( m_todo.categories() ) + "'" + "," |
158 | qu += QString::number(m_todo.priority() ) +",'" + m_todo.description() + "')"; | 176 | + QString::number( m_todo.isCompleted() ) + "," |
177 | + "'" + m_todo.description() + "'" + "," | ||
178 | + "'" + m_todo.summary() + "'" + "," | ||
179 | + QString::number(m_todo.priority() ) + "," | ||
180 | + "'" + QString::number(year) + "-" | ||
181 | + QString::number(month) | ||
182 | + "-" + QString::number( day ) + "'" + "," | ||
183 | + QString::number( m_todo.progress() ) + "," | ||
184 | + "''" + "," // state (conversion needed) | ||
185 | // + QString::number( m_todo.state() ) + "," | ||
186 | + "''" + "," // Recurrence (conversion needed) | ||
187 | + "''" + "," // Notifiers (conversion needed) | ||
188 | + "''" + "," // Maintainers (conversion needed) | ||
189 | + "'" + QString::number(sYear) + "-" | ||
190 | + QString::number(sMonth) | ||
191 | + "-" + QString::number(sDay) + "'" + "," | ||
192 | + "'" + QString::number(eYear) + "-" | ||
193 | + QString::number(eMonth) | ||
194 | + "-"+QString::number(eDay) + "'" | ||
195 | + ")"; | ||
159 | 196 | ||
160 | qWarning("add %s", qu.latin1() ); | 197 | qWarning("add %s", qu.latin1() ); |
@@ -193,11 +230,9 @@ namespace { | |||
193 | } | 230 | } |
194 | QString FindQuery::single()const{ | 231 | QString FindQuery::single()const{ |
195 | QString qu = "select uid, categories, completed, progress, summary, "; | 232 | QString qu = "select * from todolist where uid = " + QString::number(m_uid); |
196 | qu += "DueDate, priority, description from todolist where uid = " + QString::number(m_uid); | ||
197 | return qu; | 233 | return qu; |
198 | } | 234 | } |
199 | QString FindQuery::multi()const { | 235 | QString FindQuery::multi()const { |
200 | QString qu = "select uid, categories, completed, progress, summary, "; | 236 | QString qu = "select * from todolist where "; |
201 | qu += "DueDate, priority, description from todolist where "; | ||
202 | for (uint i = 0; i < m_uids.count(); i++ ) { | 237 | for (uint i = 0; i < m_uids.count(); i++ ) { |
203 | qu += " UID = " + QString::number( m_uids[i] ) + " OR"; | 238 | qu += " UID = " + QString::number( m_uids[i] ) + " OR"; |
@@ -289,5 +324,5 @@ OTodo OTodoAccessBackendSQL::find(int uid ) const{ | |||
289 | OTodo OTodoAccessBackendSQL::find( int uid, const QArray<int>& ints, | 324 | OTodo OTodoAccessBackendSQL::find( int uid, const QArray<int>& ints, |
290 | uint cur, Frontend::CacheDirection dir ) const{ | 325 | uint cur, Frontend::CacheDirection dir ) const{ |
291 | int CACHE = readAhead(); | 326 | uint CACHE = readAhead(); |
292 | qWarning("searching for %d", uid ); | 327 | qWarning("searching for %d", uid ); |
293 | QArray<int> search( CACHE ); | 328 | QArray<int> search( CACHE ); |
@@ -473,12 +508,28 @@ OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{ | |||
473 | OTodo OTodoAccessBackendSQL::todo( OSQLResultItem& item )const { | 508 | OTodo OTodoAccessBackendSQL::todo( OSQLResultItem& item )const { |
474 | qWarning("todo"); | 509 | qWarning("todo"); |
475 | bool has = false; QDate da = QDate::currentDate(); | 510 | bool hasDueDate = false; QDate dueDate = QDate::currentDate(); |
476 | has = date( da, item.data("DueDate") ); | 511 | hasDueDate = date( dueDate, item.data("DueDate") ); |
477 | QStringList cats = QStringList::split(";", item.data("categories") ); | 512 | QStringList cats = QStringList::split(";", item.data("categories") ); |
478 | 513 | ||
479 | OTodo to( (bool)item.data("completed").toInt(), item.data("priority").toInt(), | 514 | OTodo to( (bool)item.data("completed").toInt(), item.data("priority").toInt(), |
480 | cats, item.data("summary"), item.data("description"), | 515 | cats, item.data("summary"), item.data("description"), |
481 | item.data("progress").toUShort(), has, da, | 516 | item.data("progress").toUShort(), hasDueDate, dueDate, |
482 | item.data("uid").toInt() ); | 517 | item.data("uid").toInt() ); |
518 | |||
519 | bool isOk; | ||
520 | int prioInt = QString( item.data("priority") ).toInt( &isOk ); | ||
521 | if ( isOk ) | ||
522 | to.setPriority( prioInt ); | ||
523 | |||
524 | bool hasStartDate = false; QDate startDate = QDate::currentDate(); | ||
525 | hasStartDate = date( startDate, item.data("startdate") ); | ||
526 | bool hasCompletedDate = false; QDate completedDate = QDate::currentDate(); | ||
527 | hasCompletedDate = date( completedDate, item.data("completeddate") ); | ||
528 | |||
529 | if ( hasStartDate ) | ||
530 | to.setStartDate( startDate ); | ||
531 | if ( hasCompletedDate ) | ||
532 | to.setCompletedDate( completedDate ); | ||
533 | |||
483 | return to; | 534 | return to; |
484 | } | 535 | } |
@@ -571,5 +622,12 @@ QArray<int> OTodoAccessBackendSQL::matchRegexp( const QRegExp &r ) const | |||
571 | QBitArray OTodoAccessBackendSQL::supports()const { | 622 | QBitArray OTodoAccessBackendSQL::supports()const { |
572 | 623 | ||
573 | static QBitArray ar = sup(); | 624 | QBitArray ar( OTodo::CompletedDate + 1 ); |
625 | ar.fill( true ); | ||
626 | ar[OTodo::CrossReference] = false; | ||
627 | ar[OTodo::State ] = false; | ||
628 | ar[OTodo::Reminders] = false; | ||
629 | ar[OTodo::Notifiers] = false; | ||
630 | ar[OTodo::Maintainer] = false; | ||
631 | |||
574 | return ar; | 632 | return ar; |
575 | } | 633 | } |
diff --git a/libopie2/opiepim/ocontactfields.cpp b/libopie2/opiepim/ocontactfields.cpp index 831a596..7206f0d 100644 --- a/libopie2/opiepim/ocontactfields.cpp +++ b/libopie2/opiepim/ocontactfields.cpp | |||
@@ -174,4 +174,7 @@ QStringList OContactFields::untrfields( bool sorted ) | |||
174 | QMap<int, QString> mapIdToStr = idToUntrFields(); | 174 | QMap<int, QString> mapIdToStr = idToUntrFields(); |
175 | 175 | ||
176 | list.append( mapIdToStr[ Qtopia::AddressUid ] ); | ||
177 | list.append( mapIdToStr[ Qtopia::AddressCategory ] ); | ||
178 | |||
176 | list.append( mapIdToStr[ Qtopia::Title ] ); | 179 | list.append( mapIdToStr[ Qtopia::Title ] ); |
177 | list.append( mapIdToStr[ Qtopia::FirstName ] ); | 180 | list.append( mapIdToStr[ Qtopia::FirstName ] ); |
@@ -212,4 +215,7 @@ QMap<int, QString> OContactFields::idToTrFields() | |||
212 | QMap<int, QString> ret_map; | 215 | QMap<int, QString> ret_map; |
213 | 216 | ||
217 | ret_map.insert( Qtopia::AddressUid, QObject::tr( "User Id" ) ); | ||
218 | ret_map.insert( Qtopia::AddressCategory, QObject::tr( "Categories" ) ); | ||
219 | |||
214 | ret_map.insert( Qtopia::Title, QObject::tr( "Name Title") ); | 220 | ret_map.insert( Qtopia::Title, QObject::tr( "Name Title") ); |
215 | ret_map.insert( Qtopia::FirstName, QObject::tr( "First Name" ) ); | 221 | ret_map.insert( Qtopia::FirstName, QObject::tr( "First Name" ) ); |
@@ -275,4 +281,7 @@ QMap<int, QString> OContactFields::idToUntrFields() | |||
275 | QMap<int, QString> ret_map; | 281 | QMap<int, QString> ret_map; |
276 | 282 | ||
283 | ret_map.insert( Qtopia::AddressUid, "User Id" ); | ||
284 | ret_map.insert( Qtopia::AddressCategory, "Categories" ); | ||
285 | |||
277 | ret_map.insert( Qtopia::Title, "Name Title" ); | 286 | ret_map.insert( Qtopia::Title, "Name Title" ); |
278 | ret_map.insert( Qtopia::FirstName, "First Name" ); | 287 | ret_map.insert( Qtopia::FirstName, "First Name" ); |
@@ -329,4 +338,5 @@ QMap<int, QString> OContactFields::idToUntrFields() | |||
329 | // other | 338 | // other |
330 | ret_map.insert( Qtopia::Notes, "Notes" ); | 339 | ret_map.insert( Qtopia::Notes, "Notes" ); |
340 | ret_map.insert( Qtopia::Groups, "Groups" ); | ||
331 | 341 | ||
332 | 342 | ||