summaryrefslogtreecommitdiff
authoreilers <eilers>2003-09-29 07:44:26 (UTC)
committer eilers <eilers>2003-09-29 07:44:26 (UTC)
commit36d6b0096c41b01e69bb0d12e6c29648cbbf8290 (patch) (unidiff)
treec87f4f92c4a1fbdf57e502a9c5e3e44fd9e98540
parentb2e22408970ef548e23e9bbdcd87302f35fc6d4d (diff)
downloadopie-36d6b0096c41b01e69bb0d12e6c29648cbbf8290.zip
opie-36d6b0096c41b01e69bb0d12e6c29648cbbf8290.tar.gz
opie-36d6b0096c41b01e69bb0d12e6c29648cbbf8290.tar.bz2
Improvement of PIM-SQL Databases, but search queries are still limited.
Addressbook: Changed table layout. Now, we just need 1/3 of disk-space. Todo: Started to add new attributes. Some type conversions missing.
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
@@ -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;
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;
@@ -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
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
@@ -499,10 +640,54 @@ QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const
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
@@ -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_
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{
@@ -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
640QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) const 897QMap<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/libopie/pim/ocontactfields.cpp b/libopie/pim/ocontactfields.cpp
index 831a596..7206f0d 100644
--- a/libopie/pim/ocontactfields.cpp
+++ b/libopie/pim/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
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
@@ -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;
@@ -151,10 +154,44 @@ namespace {
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() );
@@ -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{
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 );
@@ -473,12 +508,28 @@ OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{
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}
@@ -571,5 +622,12 @@ QArray<int> OTodoAccessBackendSQL::matchRegexp( const QRegExp &r ) const
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}
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;
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;
@@ -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
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
@@ -499,10 +640,54 @@ QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const
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
@@ -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_
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{
@@ -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
640QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) const 897QMap<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;
@@ -151,10 +154,44 @@ namespace {
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() );
@@ -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{
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 );
@@ -473,12 +508,28 @@ OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{
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}
@@ -571,5 +622,12 @@ QArray<int> OTodoAccessBackendSQL::matchRegexp( const QRegExp &r ) const
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}
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