summaryrefslogtreecommitdiff
Unidiff
Diffstat (more/less context) (ignore whitespace changes)
-rw-r--r--libopie/pim/ocontactaccessbackend_sql.cpp313
-rw-r--r--libopie/pim/ocontactfields.cpp10
-rw-r--r--libopie/pim/otodoaccesssql.cpp92
-rw-r--r--libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp313
-rw-r--r--libopie2/opiepim/backend/otodoaccesssql.cpp92
-rw-r--r--libopie2/opiepim/ocontactfields.cpp10
6 files changed, 740 insertions, 90 deletions
diff --git a/libopie/pim/ocontactaccessbackend_sql.cpp b/libopie/pim/ocontactaccessbackend_sql.cpp
index 4afa5f3..132c9fc 100644
--- a/libopie/pim/ocontactaccessbackend_sql.cpp
+++ b/libopie/pim/ocontactaccessbackend_sql.cpp
@@ -11,12 +11,17 @@
11 * ===================================================================== 11 * =====================================================================
12 * ===================================================================== 12 * =====================================================================
13 * Version: $Id$ 13 * Version: $Id$
14 * ===================================================================== 14 * =====================================================================
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.
19 * Some modifications to be able to compile the todo sql-backend. 24 * Some modifications to be able to compile the todo sql-backend.
20 * A lot of changes fill follow... 25 * A lot of changes fill follow...
21 * 26 *
22 */ 27 */
@@ -34,12 +39,28 @@
34#include <opie/oconversion.h> 39#include <opie/oconversion.h>
35#include <opie2/osqldriver.h> 40#include <opie2/osqldriver.h>
36#include <opie2/osqlresult.h> 41#include <opie2/osqlresult.h>
37#include <opie2/osqlmanager.h> 42#include <opie2/osqlmanager.h>
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
42 * CREATE query 63 * CREATE query
43 * LOAD query 64 * LOAD query
44 * INSERT 65 * INSERT
45 * REMOVE 66 * REMOVE
@@ -137,52 +158,65 @@ namespace {
137 }; 158 };
138 159
139 160
140 161
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 ?
147 CreateQuery::CreateQuery() : OSQLQuery() {} 167 CreateQuery::CreateQuery() : OSQLQuery() {}
148 CreateQuery::~CreateQuery() {} 168 CreateQuery::~CreateQuery() {}
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 }
156 192
157 ClearQuery::ClearQuery() 193 ClearQuery::ClearQuery()
158 : OSQLQuery() {} 194 : OSQLQuery() {}
159 ClearQuery::~ClearQuery() {} 195 ClearQuery::~ClearQuery() {}
160 QString ClearQuery::query()const { 196 QString ClearQuery::query()const {
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() {}
175 LoadQuery::~LoadQuery() {} 205 LoadQuery::~LoadQuery() {}
176 QString LoadQuery::query()const { 206 QString LoadQuery::query()const {
177 QString qu; 207 QString qu;
178#ifndef __USE_SUPERFAST_LOADQUERY 208#ifdef __STORE_HORIZONTAL_
179 qu += "select distinct uid from addressbook"; 209 qu += "select uid from addressbook";
180#else 210#else
211# ifndef __USE_SUPERFAST_LOADQUERY
212 qu += "select distinct uid from addressbook";
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;
185 } 219 }
186 220
187 221
188 InsertQuery::InsertQuery( const OContact& contact ) 222 InsertQuery::InsertQuery( const OContact& contact )
@@ -194,16 +228,68 @@ namespace {
194 228
195 /* 229 /*
196 * converts from a OContact to a query 230 * converts from a OContact to a query
197 */ 231 */
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;
206 292
207 // Get the translation from the ID to the String 293 // Get the translation from the ID to the String
208 QMap<int, QString> transMap = OContactFields::idToUntrFields(); 294 QMap<int, QString> transMap = OContactFields::idToUntrFields();
209 295
@@ -258,14 +344,19 @@ namespace {
258 + "0" // Priority for future enhancements 344 + "0" // Priority for future enhancements
259 + ",'" 345 + ",'"
260 + it.data() //.latin1() 346 + it.data() //.latin1()
261 + "');"; 347 + "');";
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 ){
268 qu += "insert into custom_data VALUES(" 359 qu += "insert into custom_data VALUES("
269 + QString::number( m_contact.uid() ) 360 + QString::number( m_contact.uid() )
270 + "," 361 + ","
271 + QString::number( id++ ) 362 + QString::number( id++ )
@@ -274,27 +365,24 @@ namespace {
274 + "'," 365 + "',"
275 + "0" // Priority for future enhancements 366 + "0" // Priority for future enhancements
276 + ",'" 367 + ",'"
277 + it.data() //.latin1() 368 + it.data() //.latin1()
278 + "');"; 369 + "');";
279 } 370 }
280
281 // qu += "commit;"; 371 // qu += "commit;";
282 qWarning("add %s", qu.latin1() ); 372 qWarning("add %s", qu.latin1() );
283 return qu; 373 return qu;
284 } 374 }
285 375
286 376
287 RemoveQuery::RemoveQuery(int uid ) 377 RemoveQuery::RemoveQuery(int uid )
288 : OSQLQuery(), m_uid( uid ) {} 378 : OSQLQuery(), m_uid( uid ) {}
289 RemoveQuery::~RemoveQuery() {} 379 RemoveQuery::~RemoveQuery() {}
290 QString RemoveQuery::query()const { 380 QString RemoveQuery::query()const {
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) + ";";
297 return qu; 385 return qu;
298 } 386 }
299 387
300 388
@@ -322,17 +410,27 @@ namespace {
322 qu += " UID = " + QString::number( m_uids[i] ) + " OR"; 410 qu += " UID = " + QString::number( m_uids[i] ) + " OR";
323 } 411 }
324 qu.remove( qu.length()-2, 2 ); // Hmmmm.. 412 qu.remove( qu.length()-2, 2 ); // Hmmmm..
325 return qu; 413 return qu;
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 = ";
330 qu += QString::number(m_uid); 427 qu += QString::number(m_uid);
331 return qu; 428 return qu;
332 } 429 }
430#endif
333 431
334 432
335 FindCustomQuery::FindCustomQuery(int uid) 433 FindCustomQuery::FindCustomQuery(int uid)
336 : OSQLQuery(), m_uid( uid ) { 434 : OSQLQuery(), m_uid( uid ) {
337 } 435 }
338 FindCustomQuery::FindCustomQuery(const QArray<int>& ints) 436 FindCustomQuery::FindCustomQuery(const QArray<int>& ints)
@@ -372,13 +470,13 @@ OContactAccessBackend_SQL::OContactAccessBackend_SQL ( const QString& /* appname
372 OSQLManager man; 470 OSQLManager man;
373 m_driver = man.standard(); 471 m_driver = man.standard();
374 m_driver->setUrl( m_fileName ); 472 m_driver->setUrl( m_fileName );
375 473
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
381 479
382bool OContactAccessBackend_SQL::load () 480bool OContactAccessBackend_SQL::load ()
383{ 481{
384 if (!m_driver->open() ) 482 if (!m_driver->open() )
@@ -475,53 +573,144 @@ OContact OContactAccessBackend_SQL::find ( int uid ) const
475 QTime t; 573 QTime t;
476 t.start(); 574 t.start();
477 575
478 OContact retContact( requestNonCustom( uid ) ); 576 OContact retContact( requestNonCustom( uid ) );
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}
484 582
485 583
486 584
487QArray<int> OContactAccessBackend_SQL::queryByExample ( const OContact &query, int settings, const QDateTime& d = QDateTime() ) 585QArray<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
493QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const 634QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const
494{ 635{
495 QArray<int> nix(0); 636 QArray<int> nix(0);
496 return nix; 637 return nix;
497} 638}
498 639
499const uint OContactAccessBackend_SQL::querySettings() 640const uint OContactAccessBackend_SQL::querySettings()
500{ 641{
501 return 0; 642 return OContactAccess::IgnoreCase
643 || OContactAccess::WildCards;
502} 644}
503 645
504bool OContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const 646bool OContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const
505{ 647{
506 return false; 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 )
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
509QArray<int> OContactAccessBackend_SQL::sorted( bool asc, int , int , int ) 694QArray<int> OContactAccessBackend_SQL::sorted( bool asc, int , int , int )
510{ 695{
511 QTime t; 696 QTime t;
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 );
524 OSQLResult res = m_driver->query( &raw ); 713 OSQLResult res = m_driver->query( &raw );
525 if ( res.state() != OSQLResult::Success ){ 714 if ( res.state() != OSQLResult::Success ){
526 QArray<int> empty; 715 QArray<int> empty;
527 return empty; 716 return empty;
@@ -549,13 +738,13 @@ void OContactAccessBackend_SQL::update()
549 return; 738 return;
550 739
551 m_uids = extractUids( res ); 740 m_uids = extractUids( res );
552 741
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
558QArray<int> OContactAccessBackend_SQL::extractUids( OSQLResult& res ) const 747QArray<int> OContactAccessBackend_SQL::extractUids( OSQLResult& res ) const
559{ 748{
560 qWarning("extractUids"); 749 qWarning("extractUids");
561 QTime t; 750 QTime t;
@@ -573,12 +762,78 @@ QArray<int> OContactAccessBackend_SQL::extractUids( OSQLResult& res ) const
573 qWarning("extractUids ready: count2 = %d needs %d ms", i, t.elapsed() ); 762 qWarning("extractUids ready: count2 = %d needs %d ms", i, t.elapsed() );
574 763
575 return ints; 764 return ints;
576 765
577} 766}
578 767
768#ifdef __STORE_HORIZONTAL_
769QMap<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
579QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const 834QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const
580{ 835{
581 QTime t; 836 QTime t;
582 t.start(); 837 t.start();
583 838
584 QMap<int, QString> nonCustomMap; 839 QMap<int, QString> nonCustomMap;
@@ -630,16 +885,18 @@ QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const
630 } 885 }
631 } 886 }
632 // Add UID to Map.. 887 // Add UID to Map..
633 nonCustomMap.insert( Qtopia::AddressUid, QString::number( uid ) ); 888 nonCustomMap.insert( Qtopia::AddressUid, QString::number( uid ) );
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
640QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) const 897QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) const
641{ 898{
642 QTime t; 899 QTime t;
643 t.start(); 900 t.start();
644 901
645 QMap<QString, QString> customMap; 902 QMap<QString, QString> customMap;
@@ -656,9 +913,9 @@ QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) cons
656 OSQLResultItem::ValueList list = res_custom.results(); 913 OSQLResultItem::ValueList list = res_custom.results();
657 OSQLResultItem::ValueList::Iterator it = list.begin(); 914 OSQLResultItem::ValueList::Iterator it = list.begin();
658 for ( ; it != list.end(); ++it ) { 915 for ( ; it != list.end(); ++it ) {
659 customMap.insert( (*it).data( "type" ), (*it).data( "value" ) ); 916 customMap.insert( (*it).data( "type" ), (*it).data( "value" ) );
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/libopie/pim/ocontactfields.cpp b/libopie/pim/ocontactfields.cpp
index 831a596..7206f0d 100644
--- a/libopie/pim/ocontactfields.cpp
+++ b/libopie/pim/ocontactfields.cpp
@@ -170,12 +170,15 @@ QStringList OContactFields::trfields( bool sorted )
170*/ 170*/
171QStringList OContactFields::untrfields( bool sorted ) 171QStringList OContactFields::untrfields( bool sorted )
172{ 172{
173 QStringList list; 173 QStringList list;
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 ] );
178 list.append( mapIdToStr[ Qtopia::MiddleName ] ); 181 list.append( mapIdToStr[ Qtopia::MiddleName ] );
179 list.append( mapIdToStr[ Qtopia::LastName ] ); 182 list.append( mapIdToStr[ Qtopia::LastName ] );
180 list.append( mapIdToStr[ Qtopia::Suffix ] ); 183 list.append( mapIdToStr[ Qtopia::Suffix ] );
181 list.append( mapIdToStr[ Qtopia::FileAs ] ); 184 list.append( mapIdToStr[ Qtopia::FileAs ] );
@@ -208,12 +211,15 @@ QStringList OContactFields::untrfields( bool sorted )
208 return list; 211 return list;
209} 212}
210QMap<int, QString> OContactFields::idToTrFields() 213QMap<int, QString> OContactFields::idToTrFields()
211{ 214{
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" ) );
216 ret_map.insert( Qtopia::MiddleName, QObject::tr( "Middle Name" ) ); 222 ret_map.insert( Qtopia::MiddleName, QObject::tr( "Middle Name" ) );
217 ret_map.insert( Qtopia::LastName, QObject::tr( "Last Name" ) ); 223 ret_map.insert( Qtopia::LastName, QObject::tr( "Last Name" ) );
218 ret_map.insert( Qtopia::Suffix, QObject::tr( "Suffix" )); 224 ret_map.insert( Qtopia::Suffix, QObject::tr( "Suffix" ));
219 ret_map.insert( Qtopia::FileAs, QObject::tr( "File As" ) ); 225 ret_map.insert( Qtopia::FileAs, QObject::tr( "File As" ) );
@@ -271,12 +277,15 @@ QMap<int, QString> OContactFields::idToTrFields()
271} 277}
272 278
273QMap<int, QString> OContactFields::idToUntrFields() 279QMap<int, QString> OContactFields::idToUntrFields()
274{ 280{
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" );
279 ret_map.insert( Qtopia::MiddleName, "Middle Name" ); 288 ret_map.insert( Qtopia::MiddleName, "Middle Name" );
280 ret_map.insert( Qtopia::LastName, "Last Name" ); 289 ret_map.insert( Qtopia::LastName, "Last Name" );
281 ret_map.insert( Qtopia::Suffix, "Suffix" ); 290 ret_map.insert( Qtopia::Suffix, "Suffix" );
282 ret_map.insert( Qtopia::FileAs, "File As" ); 291 ret_map.insert( Qtopia::FileAs, "File As" );
@@ -325,12 +334,13 @@ QMap<int, QString> OContactFields::idToUntrFields()
325 ret_map.insert( Qtopia::Anniversary, "Anniversary" ); 334 ret_map.insert( Qtopia::Anniversary, "Anniversary" );
326 ret_map.insert( Qtopia::Nickname, "Nickname" ); 335 ret_map.insert( Qtopia::Nickname, "Nickname" );
327 ret_map.insert( Qtopia::Children, "Children" ); 336 ret_map.insert( Qtopia::Children, "Children" );
328 337
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
333 return ret_map; 343 return ret_map;
334} 344}
335 345
336QMap<QString, int> OContactFields::trFieldsToId() 346QMap<QString, int> OContactFields::trFieldsToId()
diff --git a/libopie/pim/otodoaccesssql.cpp b/libopie/pim/otodoaccesssql.cpp
index 23e0c3e..d255c66 100644
--- a/libopie/pim/otodoaccesssql.cpp
+++ b/libopie/pim/otodoaccesssql.cpp
@@ -115,22 +115,25 @@ namespace {
115 115
116 116
117 CreateQuery::CreateQuery() : OSQLQuery() {} 117 CreateQuery::CreateQuery() : OSQLQuery() {}
118 CreateQuery::~CreateQuery() {} 118 CreateQuery::~CreateQuery() {}
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 }
125 126
126 LoadQuery::LoadQuery() : OSQLQuery() {} 127 LoadQuery::LoadQuery() : OSQLQuery() {}
127 LoadQuery::~LoadQuery() {} 128 LoadQuery::~LoadQuery() {}
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;
133 } 136 }
134 137
135 InsertQuery::InsertQuery( const OTodo& todo ) 138 InsertQuery::InsertQuery( const OTodo& todo )
136 : OSQLQuery(), m_todo( todo ) { 139 : OSQLQuery(), m_todo( todo ) {
@@ -147,18 +150,52 @@ namespace {
147 year = month = day = 0; 150 year = month = day = 0;
148 if (m_todo.hasDueDate() ) { 151 if (m_todo.hasDueDate() ) {
149 QDate date = m_todo.dueDate(); 152 QDate date = m_todo.dueDate();
150 year = date.year(); 153 year = date.year();
151 month = date.month(); 154 month = date.month();
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() );
161 return qu; 198 return qu;
162 } 199 }
163 200
164 RemoveQuery::RemoveQuery(int uid ) 201 RemoveQuery::RemoveQuery(int uid )
@@ -189,19 +226,17 @@ namespace {
189 if (m_uids.count() == 0 ) 226 if (m_uids.count() == 0 )
190 return single(); 227 return single();
191 else 228 else
192 return multi(); 229 return multi();
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";
204 } 239 }
205 qu.remove( qu.length()-2, 2 ); 240 qu.remove( qu.length()-2, 2 );
206 return qu; 241 return qu;
207 } 242 }
@@ -285,13 +320,13 @@ OTodo OTodoAccessBackendSQL::find(int uid ) const{
285 FindQuery query( uid ); 320 FindQuery query( uid );
286 return todo( m_driver->query(&query) ); 321 return todo( m_driver->query(&query) );
287 322
288} 323}
289OTodo OTodoAccessBackendSQL::find( int uid, const QArray<int>& ints, 324OTodo 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 );
294 uint size =0; 329 uint size =0;
295 OTodo to; 330 OTodo to;
296 331
297 // we try to cache CACHE items 332 // we try to cache CACHE items
@@ -469,20 +504,36 @@ OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{
469 cache( todo( (*it) ) ); 504 cache( todo( (*it) ) );
470 } 505 }
471 return to; 506 return to;
472} 507}
473OTodo OTodoAccessBackendSQL::todo( OSQLResultItem& item )const { 508OTodo 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}
485OTodo OTodoAccessBackendSQL::todo( int uid )const { 536OTodo OTodoAccessBackendSQL::todo( int uid )const {
486 FindQuery find( uid ); 537 FindQuery find( uid );
487 return todo( m_driver->query(&find) ); 538 return todo( m_driver->query(&find) );
488} 539}
@@ -567,13 +618,20 @@ QArray<int> OTodoAccessBackendSQL::matchRegexp( const QRegExp &r ) const
567#endif 618#endif
568 QArray<int> empty; 619 QArray<int> empty;
569 return empty; 620 return empty;
570} 621}
571QBitArray OTodoAccessBackendSQL::supports()const { 622QBitArray 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}
576 634
577QBitArray OTodoAccessBackendSQL::sup() { 635QBitArray OTodoAccessBackendSQL::sup() {
578 636
579 QBitArray ar( OTodo::CompletedDate + 1 ); 637 QBitArray ar( OTodo::CompletedDate + 1 );
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
@@ -11,12 +11,17 @@
11 * ===================================================================== 11 * =====================================================================
12 * ===================================================================== 12 * =====================================================================
13 * Version: $Id$ 13 * Version: $Id$
14 * ===================================================================== 14 * =====================================================================
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.
19 * Some modifications to be able to compile the todo sql-backend. 24 * Some modifications to be able to compile the todo sql-backend.
20 * A lot of changes fill follow... 25 * A lot of changes fill follow...
21 * 26 *
22 */ 27 */
@@ -34,12 +39,28 @@
34#include <opie/oconversion.h> 39#include <opie/oconversion.h>
35#include <opie2/osqldriver.h> 40#include <opie2/osqldriver.h>
36#include <opie2/osqlresult.h> 41#include <opie2/osqlresult.h>
37#include <opie2/osqlmanager.h> 42#include <opie2/osqlmanager.h>
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
42 * CREATE query 63 * CREATE query
43 * LOAD query 64 * LOAD query
44 * INSERT 65 * INSERT
45 * REMOVE 66 * REMOVE
@@ -137,52 +158,65 @@ namespace {
137 }; 158 };
138 159
139 160
140 161
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 ?
147 CreateQuery::CreateQuery() : OSQLQuery() {} 167 CreateQuery::CreateQuery() : OSQLQuery() {}
148 CreateQuery::~CreateQuery() {} 168 CreateQuery::~CreateQuery() {}
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 }
156 192
157 ClearQuery::ClearQuery() 193 ClearQuery::ClearQuery()
158 : OSQLQuery() {} 194 : OSQLQuery() {}
159 ClearQuery::~ClearQuery() {} 195 ClearQuery::~ClearQuery() {}
160 QString ClearQuery::query()const { 196 QString ClearQuery::query()const {
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() {}
175 LoadQuery::~LoadQuery() {} 205 LoadQuery::~LoadQuery() {}
176 QString LoadQuery::query()const { 206 QString LoadQuery::query()const {
177 QString qu; 207 QString qu;
178#ifndef __USE_SUPERFAST_LOADQUERY 208#ifdef __STORE_HORIZONTAL_
179 qu += "select distinct uid from addressbook"; 209 qu += "select uid from addressbook";
180#else 210#else
211# ifndef __USE_SUPERFAST_LOADQUERY
212 qu += "select distinct uid from addressbook";
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;
185 } 219 }
186 220
187 221
188 InsertQuery::InsertQuery( const OContact& contact ) 222 InsertQuery::InsertQuery( const OContact& contact )
@@ -194,16 +228,68 @@ namespace {
194 228
195 /* 229 /*
196 * converts from a OContact to a query 230 * converts from a OContact to a query
197 */ 231 */
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;
206 292
207 // Get the translation from the ID to the String 293 // Get the translation from the ID to the String
208 QMap<int, QString> transMap = OContactFields::idToUntrFields(); 294 QMap<int, QString> transMap = OContactFields::idToUntrFields();
209 295
@@ -258,14 +344,19 @@ namespace {
258 + "0" // Priority for future enhancements 344 + "0" // Priority for future enhancements
259 + ",'" 345 + ",'"
260 + it.data() //.latin1() 346 + it.data() //.latin1()
261 + "');"; 347 + "');";
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 ){
268 qu += "insert into custom_data VALUES(" 359 qu += "insert into custom_data VALUES("
269 + QString::number( m_contact.uid() ) 360 + QString::number( m_contact.uid() )
270 + "," 361 + ","
271 + QString::number( id++ ) 362 + QString::number( id++ )
@@ -274,27 +365,24 @@ namespace {
274 + "'," 365 + "',"
275 + "0" // Priority for future enhancements 366 + "0" // Priority for future enhancements
276 + ",'" 367 + ",'"
277 + it.data() //.latin1() 368 + it.data() //.latin1()
278 + "');"; 369 + "');";
279 } 370 }
280
281 // qu += "commit;"; 371 // qu += "commit;";
282 qWarning("add %s", qu.latin1() ); 372 qWarning("add %s", qu.latin1() );
283 return qu; 373 return qu;
284 } 374 }
285 375
286 376
287 RemoveQuery::RemoveQuery(int uid ) 377 RemoveQuery::RemoveQuery(int uid )
288 : OSQLQuery(), m_uid( uid ) {} 378 : OSQLQuery(), m_uid( uid ) {}
289 RemoveQuery::~RemoveQuery() {} 379 RemoveQuery::~RemoveQuery() {}
290 QString RemoveQuery::query()const { 380 QString RemoveQuery::query()const {
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) + ";";
297 return qu; 385 return qu;
298 } 386 }
299 387
300 388
@@ -322,17 +410,27 @@ namespace {
322 qu += " UID = " + QString::number( m_uids[i] ) + " OR"; 410 qu += " UID = " + QString::number( m_uids[i] ) + " OR";
323 } 411 }
324 qu.remove( qu.length()-2, 2 ); // Hmmmm.. 412 qu.remove( qu.length()-2, 2 ); // Hmmmm..
325 return qu; 413 return qu;
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 = ";
330 qu += QString::number(m_uid); 427 qu += QString::number(m_uid);
331 return qu; 428 return qu;
332 } 429 }
430#endif
333 431
334 432
335 FindCustomQuery::FindCustomQuery(int uid) 433 FindCustomQuery::FindCustomQuery(int uid)
336 : OSQLQuery(), m_uid( uid ) { 434 : OSQLQuery(), m_uid( uid ) {
337 } 435 }
338 FindCustomQuery::FindCustomQuery(const QArray<int>& ints) 436 FindCustomQuery::FindCustomQuery(const QArray<int>& ints)
@@ -372,13 +470,13 @@ OContactAccessBackend_SQL::OContactAccessBackend_SQL ( const QString& /* appname
372 OSQLManager man; 470 OSQLManager man;
373 m_driver = man.standard(); 471 m_driver = man.standard();
374 m_driver->setUrl( m_fileName ); 472 m_driver->setUrl( m_fileName );
375 473
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
381 479
382bool OContactAccessBackend_SQL::load () 480bool OContactAccessBackend_SQL::load ()
383{ 481{
384 if (!m_driver->open() ) 482 if (!m_driver->open() )
@@ -475,53 +573,144 @@ OContact OContactAccessBackend_SQL::find ( int uid ) const
475 QTime t; 573 QTime t;
476 t.start(); 574 t.start();
477 575
478 OContact retContact( requestNonCustom( uid ) ); 576 OContact retContact( requestNonCustom( uid ) );
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}
484 582
485 583
486 584
487QArray<int> OContactAccessBackend_SQL::queryByExample ( const OContact &query, int settings, const QDateTime& d = QDateTime() ) 585QArray<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
493QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const 634QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const
494{ 635{
495 QArray<int> nix(0); 636 QArray<int> nix(0);
496 return nix; 637 return nix;
497} 638}
498 639
499const uint OContactAccessBackend_SQL::querySettings() 640const uint OContactAccessBackend_SQL::querySettings()
500{ 641{
501 return 0; 642 return OContactAccess::IgnoreCase
643 || OContactAccess::WildCards;
502} 644}
503 645
504bool OContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const 646bool OContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const
505{ 647{
506 return false; 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 )
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
509QArray<int> OContactAccessBackend_SQL::sorted( bool asc, int , int , int ) 694QArray<int> OContactAccessBackend_SQL::sorted( bool asc, int , int , int )
510{ 695{
511 QTime t; 696 QTime t;
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 );
524 OSQLResult res = m_driver->query( &raw ); 713 OSQLResult res = m_driver->query( &raw );
525 if ( res.state() != OSQLResult::Success ){ 714 if ( res.state() != OSQLResult::Success ){
526 QArray<int> empty; 715 QArray<int> empty;
527 return empty; 716 return empty;
@@ -549,13 +738,13 @@ void OContactAccessBackend_SQL::update()
549 return; 738 return;
550 739
551 m_uids = extractUids( res ); 740 m_uids = extractUids( res );
552 741
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
558QArray<int> OContactAccessBackend_SQL::extractUids( OSQLResult& res ) const 747QArray<int> OContactAccessBackend_SQL::extractUids( OSQLResult& res ) const
559{ 748{
560 qWarning("extractUids"); 749 qWarning("extractUids");
561 QTime t; 750 QTime t;
@@ -573,12 +762,78 @@ QArray<int> OContactAccessBackend_SQL::extractUids( OSQLResult& res ) const
573 qWarning("extractUids ready: count2 = %d needs %d ms", i, t.elapsed() ); 762 qWarning("extractUids ready: count2 = %d needs %d ms", i, t.elapsed() );
574 763
575 return ints; 764 return ints;
576 765
577} 766}
578 767
768#ifdef __STORE_HORIZONTAL_
769QMap<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
579QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const 834QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const
580{ 835{
581 QTime t; 836 QTime t;
582 t.start(); 837 t.start();
583 838
584 QMap<int, QString> nonCustomMap; 839 QMap<int, QString> nonCustomMap;
@@ -630,16 +885,18 @@ QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const
630 } 885 }
631 } 886 }
632 // Add UID to Map.. 887 // Add UID to Map..
633 nonCustomMap.insert( Qtopia::AddressUid, QString::number( uid ) ); 888 nonCustomMap.insert( Qtopia::AddressUid, QString::number( uid ) );
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
640QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) const 897QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) const
641{ 898{
642 QTime t; 899 QTime t;
643 t.start(); 900 t.start();
644 901
645 QMap<QString, QString> customMap; 902 QMap<QString, QString> customMap;
@@ -656,9 +913,9 @@ QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) cons
656 OSQLResultItem::ValueList list = res_custom.results(); 913 OSQLResultItem::ValueList list = res_custom.results();
657 OSQLResultItem::ValueList::Iterator it = list.begin(); 914 OSQLResultItem::ValueList::Iterator it = list.begin();
658 for ( ; it != list.end(); ++it ) { 915 for ( ; it != list.end(); ++it ) {
659 customMap.insert( (*it).data( "type" ), (*it).data( "value" ) ); 916 customMap.insert( (*it).data( "type" ), (*it).data( "value" ) );
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
@@ -115,22 +115,25 @@ namespace {
115 115
116 116
117 CreateQuery::CreateQuery() : OSQLQuery() {} 117 CreateQuery::CreateQuery() : OSQLQuery() {}
118 CreateQuery::~CreateQuery() {} 118 CreateQuery::~CreateQuery() {}
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 }
125 126
126 LoadQuery::LoadQuery() : OSQLQuery() {} 127 LoadQuery::LoadQuery() : OSQLQuery() {}
127 LoadQuery::~LoadQuery() {} 128 LoadQuery::~LoadQuery() {}
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;
133 } 136 }
134 137
135 InsertQuery::InsertQuery( const OTodo& todo ) 138 InsertQuery::InsertQuery( const OTodo& todo )
136 : OSQLQuery(), m_todo( todo ) { 139 : OSQLQuery(), m_todo( todo ) {
@@ -147,18 +150,52 @@ namespace {
147 year = month = day = 0; 150 year = month = day = 0;
148 if (m_todo.hasDueDate() ) { 151 if (m_todo.hasDueDate() ) {
149 QDate date = m_todo.dueDate(); 152 QDate date = m_todo.dueDate();
150 year = date.year(); 153 year = date.year();
151 month = date.month(); 154 month = date.month();
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() );
161 return qu; 198 return qu;
162 } 199 }
163 200
164 RemoveQuery::RemoveQuery(int uid ) 201 RemoveQuery::RemoveQuery(int uid )
@@ -189,19 +226,17 @@ namespace {
189 if (m_uids.count() == 0 ) 226 if (m_uids.count() == 0 )
190 return single(); 227 return single();
191 else 228 else
192 return multi(); 229 return multi();
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";
204 } 239 }
205 qu.remove( qu.length()-2, 2 ); 240 qu.remove( qu.length()-2, 2 );
206 return qu; 241 return qu;
207 } 242 }
@@ -285,13 +320,13 @@ OTodo OTodoAccessBackendSQL::find(int uid ) const{
285 FindQuery query( uid ); 320 FindQuery query( uid );
286 return todo( m_driver->query(&query) ); 321 return todo( m_driver->query(&query) );
287 322
288} 323}
289OTodo OTodoAccessBackendSQL::find( int uid, const QArray<int>& ints, 324OTodo 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 );
294 uint size =0; 329 uint size =0;
295 OTodo to; 330 OTodo to;
296 331
297 // we try to cache CACHE items 332 // we try to cache CACHE items
@@ -469,20 +504,36 @@ OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{
469 cache( todo( (*it) ) ); 504 cache( todo( (*it) ) );
470 } 505 }
471 return to; 506 return to;
472} 507}
473OTodo OTodoAccessBackendSQL::todo( OSQLResultItem& item )const { 508OTodo 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}
485OTodo OTodoAccessBackendSQL::todo( int uid )const { 536OTodo OTodoAccessBackendSQL::todo( int uid )const {
486 FindQuery find( uid ); 537 FindQuery find( uid );
487 return todo( m_driver->query(&find) ); 538 return todo( m_driver->query(&find) );
488} 539}
@@ -567,13 +618,20 @@ QArray<int> OTodoAccessBackendSQL::matchRegexp( const QRegExp &r ) const
567#endif 618#endif
568 QArray<int> empty; 619 QArray<int> empty;
569 return empty; 620 return empty;
570} 621}
571QBitArray OTodoAccessBackendSQL::supports()const { 622QBitArray 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}
576 634
577QBitArray OTodoAccessBackendSQL::sup() { 635QBitArray OTodoAccessBackendSQL::sup() {
578 636
579 QBitArray ar( OTodo::CompletedDate + 1 ); 637 QBitArray ar( OTodo::CompletedDate + 1 );
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
@@ -170,12 +170,15 @@ QStringList OContactFields::trfields( bool sorted )
170*/ 170*/
171QStringList OContactFields::untrfields( bool sorted ) 171QStringList OContactFields::untrfields( bool sorted )
172{ 172{
173 QStringList list; 173 QStringList list;
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 ] );
178 list.append( mapIdToStr[ Qtopia::MiddleName ] ); 181 list.append( mapIdToStr[ Qtopia::MiddleName ] );
179 list.append( mapIdToStr[ Qtopia::LastName ] ); 182 list.append( mapIdToStr[ Qtopia::LastName ] );
180 list.append( mapIdToStr[ Qtopia::Suffix ] ); 183 list.append( mapIdToStr[ Qtopia::Suffix ] );
181 list.append( mapIdToStr[ Qtopia::FileAs ] ); 184 list.append( mapIdToStr[ Qtopia::FileAs ] );
@@ -208,12 +211,15 @@ QStringList OContactFields::untrfields( bool sorted )
208 return list; 211 return list;
209} 212}
210QMap<int, QString> OContactFields::idToTrFields() 213QMap<int, QString> OContactFields::idToTrFields()
211{ 214{
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" ) );
216 ret_map.insert( Qtopia::MiddleName, QObject::tr( "Middle Name" ) ); 222 ret_map.insert( Qtopia::MiddleName, QObject::tr( "Middle Name" ) );
217 ret_map.insert( Qtopia::LastName, QObject::tr( "Last Name" ) ); 223 ret_map.insert( Qtopia::LastName, QObject::tr( "Last Name" ) );
218 ret_map.insert( Qtopia::Suffix, QObject::tr( "Suffix" )); 224 ret_map.insert( Qtopia::Suffix, QObject::tr( "Suffix" ));
219 ret_map.insert( Qtopia::FileAs, QObject::tr( "File As" ) ); 225 ret_map.insert( Qtopia::FileAs, QObject::tr( "File As" ) );
@@ -271,12 +277,15 @@ QMap<int, QString> OContactFields::idToTrFields()
271} 277}
272 278
273QMap<int, QString> OContactFields::idToUntrFields() 279QMap<int, QString> OContactFields::idToUntrFields()
274{ 280{
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" );
279 ret_map.insert( Qtopia::MiddleName, "Middle Name" ); 288 ret_map.insert( Qtopia::MiddleName, "Middle Name" );
280 ret_map.insert( Qtopia::LastName, "Last Name" ); 289 ret_map.insert( Qtopia::LastName, "Last Name" );
281 ret_map.insert( Qtopia::Suffix, "Suffix" ); 290 ret_map.insert( Qtopia::Suffix, "Suffix" );
282 ret_map.insert( Qtopia::FileAs, "File As" ); 291 ret_map.insert( Qtopia::FileAs, "File As" );
@@ -325,12 +334,13 @@ QMap<int, QString> OContactFields::idToUntrFields()
325 ret_map.insert( Qtopia::Anniversary, "Anniversary" ); 334 ret_map.insert( Qtopia::Anniversary, "Anniversary" );
326 ret_map.insert( Qtopia::Nickname, "Nickname" ); 335 ret_map.insert( Qtopia::Nickname, "Nickname" );
327 ret_map.insert( Qtopia::Children, "Children" ); 336 ret_map.insert( Qtopia::Children, "Children" );
328 337
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
333 return ret_map; 343 return ret_map;
334} 344}
335 345
336QMap<QString, int> OContactFields::trFieldsToId() 346QMap<QString, int> OContactFields::trFieldsToId()