Diffstat (limited to 'noncore/unsupported/libopie/pim/ocontactaccessbackend_sql.cpp') (more/less context) (ignore whitespace changes)
-rw-r--r-- | noncore/unsupported/libopie/pim/ocontactaccessbackend_sql.cpp | 948 |
1 files changed, 948 insertions, 0 deletions
diff --git a/noncore/unsupported/libopie/pim/ocontactaccessbackend_sql.cpp b/noncore/unsupported/libopie/pim/ocontactaccessbackend_sql.cpp new file mode 100644 index 0000000..669483d --- a/dev/null +++ b/noncore/unsupported/libopie/pim/ocontactaccessbackend_sql.cpp | |||
@@ -0,0 +1,948 @@ | |||
1 | /* | ||
2 | * SQL Backend for the OPIE-Contact Database. | ||
3 | * | ||
4 | * Copyright (c) 2002 by Stefan Eilers (Eilers.Stefan@epost.de) | ||
5 | * | ||
6 | * ===================================================================== | ||
7 | *This program is free software; you can redistribute it and/or | ||
8 | *modify it under the terms of the GNU Library General Public | ||
9 | * License as published by the Free Software Foundation; either | ||
10 | * version 2 of the License, or (at your option) any later version. | ||
11 | * ===================================================================== | ||
12 | * ===================================================================== | ||
13 | * Version: $Id$ | ||
14 | * ===================================================================== | ||
15 | * History: | ||
16 | * $Log$ | ||
17 | * Revision 1.1 2004/11/16 21:46:07 mickeyl | ||
18 | * libopie1 goes into unsupported | ||
19 | * | ||
20 | * Revision 1.5 2004/03/14 13:50:35 alwin | ||
21 | * namespace correction | ||
22 | * | ||
23 | * Revision 1.4 2003/12/22 10:19:26 eilers | ||
24 | * Finishing implementation of sql-backend for datebook. But I have to | ||
25 | * port the PIM datebook application to use it, before I could debug the | ||
26 | * whole stuff. | ||
27 | * Thus, PIM-Database backend is finished, but highly experimental. And some | ||
28 | * parts are still generic. For instance, the "queryByExample()" methods are | ||
29 | * not (or not fully) implemented. Todo: custom-entries not stored. | ||
30 | * The big show stopper: matchRegExp() (needed by OpieSearch) needs regular | ||
31 | * expression search in the database, which is not supported by sqlite ! | ||
32 | * Therefore we need either an extended sqlite or a workaround which would | ||
33 | * be very slow and memory consuming.. | ||
34 | * | ||
35 | * Revision 1.3 2003/12/08 15:18:10 eilers | ||
36 | * Committing unfinished sql implementation before merging to libopie2 starts.. | ||
37 | * | ||
38 | * Revision 1.2 2003/09/29 07:44:26 eilers | ||
39 | * Improvement of PIM-SQL Databases, but search queries are still limited. | ||
40 | * Addressbook: Changed table layout. Now, we just need 1/3 of disk-space. | ||
41 | * Todo: Started to add new attributes. Some type conversions missing. | ||
42 | * | ||
43 | * Revision 1.1 2003/09/22 14:31:16 eilers | ||
44 | * Added first experimental incarnation of sql-backend for addressbook. | ||
45 | * Some modifications to be able to compile the todo sql-backend. | ||
46 | * A lot of changes fill follow... | ||
47 | * | ||
48 | */ | ||
49 | |||
50 | #include "ocontactaccessbackend_sql.h" | ||
51 | |||
52 | #include <qarray.h> | ||
53 | #include <qdatetime.h> | ||
54 | #include <qstringlist.h> | ||
55 | |||
56 | #include <qpe/global.h> | ||
57 | #include <qpe/recordfields.h> | ||
58 | |||
59 | #include <opie/ocontactfields.h> | ||
60 | #include <opie/oconversion.h> | ||
61 | #include <opie2/osqldriver.h> | ||
62 | #include <opie2/osqlresult.h> | ||
63 | #include <opie2/osqlmanager.h> | ||
64 | #include <opie2/osqlquery.h> | ||
65 | |||
66 | using namespace Opie::DB; | ||
67 | |||
68 | |||
69 | // If defined, we use a horizontal table ( uid, attr1, attr2, attr3, ..., attrn ) instead | ||
70 | // vertical like "uid, type, value". | ||
71 | // DON'T DEACTIVATE THIS DEFINE IN PRODUCTIVE ENVIRONMENTS !! | ||
72 | #define __STORE_HORIZONTAL_ | ||
73 | |||
74 | // Distinct loading is not very fast. If I expect that every person has just | ||
75 | // one (and always one) 'Last Name', I can request all uid's for existing lastnames, | ||
76 | // which is faster.. | ||
77 | // But this may not be true for all entries, like company contacts.. | ||
78 | // The current AddressBook application handles this problem, but other may not.. (eilers) | ||
79 | #define __USE_SUPERFAST_LOADQUERY | ||
80 | |||
81 | |||
82 | /* | ||
83 | * Implementation of used query types | ||
84 | * CREATE query | ||
85 | * LOAD query | ||
86 | * INSERT | ||
87 | * REMOVE | ||
88 | * CLEAR | ||
89 | */ | ||
90 | namespace { | ||
91 | /** | ||
92 | * CreateQuery for the Todolist Table | ||
93 | */ | ||
94 | class CreateQuery : public OSQLQuery { | ||
95 | public: | ||
96 | CreateQuery(); | ||
97 | ~CreateQuery(); | ||
98 | QString query()const; | ||
99 | }; | ||
100 | |||
101 | /** | ||
102 | * Clears (delete) a Table | ||
103 | */ | ||
104 | class ClearQuery : public OSQLQuery { | ||
105 | public: | ||
106 | ClearQuery(); | ||
107 | ~ClearQuery(); | ||
108 | QString query()const; | ||
109 | |||
110 | }; | ||
111 | |||
112 | |||
113 | /** | ||
114 | * LoadQuery | ||
115 | * this one queries for all uids | ||
116 | */ | ||
117 | class LoadQuery : public OSQLQuery { | ||
118 | public: | ||
119 | LoadQuery(); | ||
120 | ~LoadQuery(); | ||
121 | QString query()const; | ||
122 | }; | ||
123 | |||
124 | /** | ||
125 | * inserts/adds a OContact to the table | ||
126 | */ | ||
127 | class InsertQuery : public OSQLQuery { | ||
128 | public: | ||
129 | InsertQuery(const OContact& ); | ||
130 | ~InsertQuery(); | ||
131 | QString query()const; | ||
132 | private: | ||
133 | OContact m_contact; | ||
134 | }; | ||
135 | |||
136 | |||
137 | /** | ||
138 | * removes one from the table | ||
139 | */ | ||
140 | class RemoveQuery : public OSQLQuery { | ||
141 | public: | ||
142 | RemoveQuery(int uid ); | ||
143 | ~RemoveQuery(); | ||
144 | QString query()const; | ||
145 | private: | ||
146 | int m_uid; | ||
147 | }; | ||
148 | |||
149 | /** | ||
150 | * a find query for noncustom elements | ||
151 | */ | ||
152 | class FindQuery : public OSQLQuery { | ||
153 | public: | ||
154 | FindQuery(int uid); | ||
155 | FindQuery(const QArray<int>& ); | ||
156 | ~FindQuery(); | ||
157 | QString query()const; | ||
158 | private: | ||
159 | QString single()const; | ||
160 | QString multi()const; | ||
161 | QArray<int> m_uids; | ||
162 | int m_uid; | ||
163 | }; | ||
164 | |||
165 | /** | ||
166 | * a find query for custom elements | ||
167 | */ | ||
168 | class FindCustomQuery : public OSQLQuery { | ||
169 | public: | ||
170 | FindCustomQuery(int uid); | ||
171 | FindCustomQuery(const QArray<int>& ); | ||
172 | ~FindCustomQuery(); | ||
173 | QString query()const; | ||
174 | private: | ||
175 | QString single()const; | ||
176 | QString multi()const; | ||
177 | QArray<int> m_uids; | ||
178 | int m_uid; | ||
179 | }; | ||
180 | |||
181 | |||
182 | |||
183 | // We using three tables to store the information: | ||
184 | // 1. addressbook : It contains General information about the contact (non custom) | ||
185 | // 2. custom_data : Not official supported entries | ||
186 | // All tables are connected by the uid of the contact. | ||
187 | // Maybe I should add a table for meta-information ? | ||
188 | CreateQuery::CreateQuery() : OSQLQuery() {} | ||
189 | CreateQuery::~CreateQuery() {} | ||
190 | QString CreateQuery::query()const { | ||
191 | QString qu; | ||
192 | #ifdef __STORE_HORIZONTAL_ | ||
193 | |||
194 | qu += "create table addressbook( uid PRIMARY KEY "; | ||
195 | |||
196 | QStringList fieldList = OContactFields::untrfields( false ); | ||
197 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | ||
198 | qu += QString( ",\"%1\" VARCHAR(10)" ).arg( *it ); | ||
199 | } | ||
200 | qu += " );"; | ||
201 | |||
202 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; | ||
203 | |||
204 | #else | ||
205 | |||
206 | qu += "create table addressbook( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id));"; | ||
207 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; | ||
208 | // qu += "create table dates( uid PRIMARY KEY, type, day, month, year, hour, minute, second );"; | ||
209 | |||
210 | #endif // __STORE_HORIZONTAL_ | ||
211 | return qu; | ||
212 | } | ||
213 | |||
214 | ClearQuery::ClearQuery() | ||
215 | : OSQLQuery() {} | ||
216 | ClearQuery::~ClearQuery() {} | ||
217 | QString ClearQuery::query()const { | ||
218 | QString qu = "drop table addressbook;"; | ||
219 | qu += "drop table custom_data;"; | ||
220 | // qu += "drop table dates;"; | ||
221 | return qu; | ||
222 | } | ||
223 | |||
224 | |||
225 | LoadQuery::LoadQuery() : OSQLQuery() {} | ||
226 | LoadQuery::~LoadQuery() {} | ||
227 | QString LoadQuery::query()const { | ||
228 | QString qu; | ||
229 | #ifdef __STORE_HORIZONTAL_ | ||
230 | qu += "select uid from addressbook"; | ||
231 | #else | ||
232 | # ifndef __USE_SUPERFAST_LOADQUERY | ||
233 | qu += "select distinct uid from addressbook"; | ||
234 | # else | ||
235 | qu += "select uid from addressbook where type = 'Last Name'"; | ||
236 | # endif // __USE_SUPERFAST_LOADQUERY | ||
237 | #endif // __STORE_HORIZONTAL_ | ||
238 | |||
239 | return qu; | ||
240 | } | ||
241 | |||
242 | |||
243 | InsertQuery::InsertQuery( const OContact& contact ) | ||
244 | : OSQLQuery(), m_contact( contact ) { | ||
245 | } | ||
246 | |||
247 | InsertQuery::~InsertQuery() { | ||
248 | } | ||
249 | |||
250 | /* | ||
251 | * converts from a OContact to a query | ||
252 | */ | ||
253 | QString InsertQuery::query()const{ | ||
254 | |||
255 | #ifdef __STORE_HORIZONTAL_ | ||
256 | QString qu; | ||
257 | qu += "insert into addressbook VALUES( " + | ||
258 | QString::number( m_contact.uid() ); | ||
259 | |||
260 | // Get all information out of the contact-class | ||
261 | // Remember: The category is stored in contactMap, too ! | ||
262 | QMap<int, QString> contactMap = m_contact.toMap(); | ||
263 | |||
264 | QStringList fieldList = OContactFields::untrfields( false ); | ||
265 | QMap<QString, int> translate = OContactFields::untrFieldsToId(); | ||
266 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | ||
267 | // Convert Column-String to Id and get value for this id.. | ||
268 | // Hmmm.. Maybe not very cute solution.. | ||
269 | int id = translate[*it]; | ||
270 | switch ( id ){ | ||
271 | case Qtopia::Birthday:{ | ||
272 | // These entries should stored in a special format | ||
273 | // year-month-day | ||
274 | QDate day = m_contact.birthday(); | ||
275 | if ( day.isValid() ){ | ||
276 | qu += QString(",\"%1-%2-%3\"") | ||
277 | .arg( day.year() ) | ||
278 | .arg( day.month() ) | ||
279 | .arg( day.day() ); | ||
280 | } else { | ||
281 | qu += ",\"\""; | ||
282 | } | ||
283 | } | ||
284 | break; | ||
285 | case Qtopia::Anniversary:{ | ||
286 | // These entries should stored in a special format | ||
287 | // year-month-day | ||
288 | QDate day = m_contact.anniversary(); | ||
289 | if ( day.isValid() ){ | ||
290 | qu += QString(",\"%1-%2-%3\"") | ||
291 | .arg( day.year() ) | ||
292 | .arg( day.month() ) | ||
293 | .arg( day.day() ); | ||
294 | } else { | ||
295 | qu += ",\"\""; | ||
296 | } | ||
297 | } | ||
298 | break; | ||
299 | |||
300 | default: | ||
301 | qu += QString( ",\"%1\"" ).arg( contactMap[id] ); | ||
302 | } | ||
303 | } | ||
304 | qu += " );"; | ||
305 | |||
306 | |||
307 | #else | ||
308 | // Get all information out of the contact-class | ||
309 | // Remember: The category is stored in contactMap, too ! | ||
310 | QMap<int, QString> contactMap = m_contact.toMap(); | ||
311 | |||
312 | QMap<QString, QString> addressbook_db; | ||
313 | |||
314 | // Get the translation from the ID to the String | ||
315 | QMap<int, QString> transMap = OContactFields::idToUntrFields(); | ||
316 | |||
317 | for( QMap<int, QString>::Iterator it = contactMap.begin(); | ||
318 | it != contactMap.end(); ++it ){ | ||
319 | switch ( it.key() ){ | ||
320 | case Qtopia::Birthday:{ | ||
321 | // These entries should stored in a special format | ||
322 | // year-month-day | ||
323 | QDate day = m_contact.birthday(); | ||
324 | addressbook_db.insert( transMap[it.key()], | ||
325 | QString("%1-%2-%3") | ||
326 | .arg( day.year() ) | ||
327 | .arg( day.month() ) | ||
328 | .arg( day.day() ) ); | ||
329 | } | ||
330 | break; | ||
331 | case Qtopia::Anniversary:{ | ||
332 | // These entries should stored in a special format | ||
333 | // year-month-day | ||
334 | QDate day = m_contact.anniversary(); | ||
335 | addressbook_db.insert( transMap[it.key()], | ||
336 | QString("%1-%2-%3") | ||
337 | .arg( day.year() ) | ||
338 | .arg( day.month() ) | ||
339 | .arg( day.day() ) ); | ||
340 | } | ||
341 | break; | ||
342 | case Qtopia::AddressUid: // Ignore UID | ||
343 | break; | ||
344 | default: // Translate id to String | ||
345 | addressbook_db.insert( transMap[it.key()], it.data() ); | ||
346 | break; | ||
347 | } | ||
348 | |||
349 | } | ||
350 | |||
351 | // Now convert this whole stuff into a SQL String, beginning with | ||
352 | // the addressbook table.. | ||
353 | QString qu; | ||
354 | // qu += "begin transaction;"; | ||
355 | int id = 0; | ||
356 | for( QMap<QString, QString>::Iterator it = addressbook_db.begin(); | ||
357 | it != addressbook_db.end(); ++it ){ | ||
358 | qu += "insert into addressbook VALUES(" | ||
359 | + QString::number( m_contact.uid() ) | ||
360 | + "," | ||
361 | + QString::number( id++ ) | ||
362 | + ",'" | ||
363 | + it.key() //.latin1() | ||
364 | + "'," | ||
365 | + "0" // Priority for future enhancements | ||
366 | + ",'" | ||
367 | + it.data() //.latin1() | ||
368 | + "');"; | ||
369 | } | ||
370 | |||
371 | #endif //__STORE_HORIZONTAL_ | ||
372 | // Now add custom data.. | ||
373 | #ifdef __STORE_HORIZONTAL_ | ||
374 | int id = 0; | ||
375 | #endif | ||
376 | id = 0; | ||
377 | QMap<QString, QString> customMap = m_contact.toExtraMap(); | ||
378 | for( QMap<QString, QString>::Iterator it = customMap.begin(); | ||
379 | it != customMap.end(); ++it ){ | ||
380 | qu += "insert into custom_data VALUES(" | ||
381 | + QString::number( m_contact.uid() ) | ||
382 | + "," | ||
383 | + QString::number( id++ ) | ||
384 | + ",'" | ||
385 | + it.key() //.latin1() | ||
386 | + "'," | ||
387 | + "0" // Priority for future enhancements | ||
388 | + ",'" | ||
389 | + it.data() //.latin1() | ||
390 | + "');"; | ||
391 | } | ||
392 | // qu += "commit;"; | ||
393 | qWarning("add %s", qu.latin1() ); | ||
394 | return qu; | ||
395 | } | ||
396 | |||
397 | |||
398 | RemoveQuery::RemoveQuery(int uid ) | ||
399 | : OSQLQuery(), m_uid( uid ) {} | ||
400 | RemoveQuery::~RemoveQuery() {} | ||
401 | QString RemoveQuery::query()const { | ||
402 | QString qu = "DELETE from addressbook where uid = " | ||
403 | + QString::number(m_uid) + ";"; | ||
404 | qu += "DELETE from custom_data where uid = " | ||
405 | + QString::number(m_uid) + ";"; | ||
406 | return qu; | ||
407 | } | ||
408 | |||
409 | |||
410 | |||
411 | |||
412 | FindQuery::FindQuery(int uid) | ||
413 | : OSQLQuery(), m_uid( uid ) { | ||
414 | } | ||
415 | FindQuery::FindQuery(const QArray<int>& ints) | ||
416 | : OSQLQuery(), m_uids( ints ){ | ||
417 | } | ||
418 | FindQuery::~FindQuery() { | ||
419 | } | ||
420 | QString FindQuery::query()const{ | ||
421 | // if ( m_uids.count() == 0 ) | ||
422 | return single(); | ||
423 | } | ||
424 | /* | ||
425 | else | ||
426 | return multi(); | ||
427 | } | ||
428 | QString FindQuery::multi()const { | ||
429 | QString qu = "select uid, type, value from addressbook where"; | ||
430 | for (uint i = 0; i < m_uids.count(); i++ ) { | ||
431 | qu += " UID = " + QString::number( m_uids[i] ) + " OR"; | ||
432 | } | ||
433 | qu.remove( qu.length()-2, 2 ); // Hmmmm.. | ||
434 | return qu; | ||
435 | } | ||
436 | */ | ||
437 | #ifdef __STORE_HORIZONTAL_ | ||
438 | QString FindQuery::single()const{ | ||
439 | QString qu = "select *"; | ||
440 | qu += " from addressbook where uid = " + QString::number(m_uid); | ||
441 | |||
442 | // qWarning("find query: %s", qu.latin1() ); | ||
443 | return qu; | ||
444 | } | ||
445 | #else | ||
446 | QString FindQuery::single()const{ | ||
447 | QString qu = "select uid, type, value from addressbook where uid = "; | ||
448 | qu += QString::number(m_uid); | ||
449 | return qu; | ||
450 | } | ||
451 | #endif | ||
452 | |||
453 | |||
454 | FindCustomQuery::FindCustomQuery(int uid) | ||
455 | : OSQLQuery(), m_uid( uid ) { | ||
456 | } | ||
457 | FindCustomQuery::FindCustomQuery(const QArray<int>& ints) | ||
458 | : OSQLQuery(), m_uids( ints ){ | ||
459 | } | ||
460 | FindCustomQuery::~FindCustomQuery() { | ||
461 | } | ||
462 | QString FindCustomQuery::query()const{ | ||
463 | // if ( m_uids.count() == 0 ) | ||
464 | return single(); | ||
465 | } | ||
466 | QString FindCustomQuery::single()const{ | ||
467 | QString qu = "select uid, type, value from custom_data where uid = "; | ||
468 | qu += QString::number(m_uid); | ||
469 | return qu; | ||
470 | } | ||
471 | |||
472 | }; | ||
473 | |||
474 | |||
475 | /* --------------------------------------------------------------------------- */ | ||
476 | |||
477 | OContactAccessBackend_SQL::OContactAccessBackend_SQL ( const QString& /* appname */, | ||
478 | const QString& filename ): | ||
479 | OContactAccessBackend(), m_changed(false), m_driver( NULL ) | ||
480 | { | ||
481 | qWarning("C'tor OContactAccessBackend_SQL starts"); | ||
482 | QTime t; | ||
483 | t.start(); | ||
484 | |||
485 | /* Expecting to access the default filename if nothing else is set */ | ||
486 | if ( filename.isEmpty() ){ | ||
487 | m_fileName = Global::applicationFileName( "addressbook","addressbook.db" ); | ||
488 | } else | ||
489 | m_fileName = filename; | ||
490 | |||
491 | // Get the standart sql-driver from the OSQLManager.. | ||
492 | OSQLManager man; | ||
493 | m_driver = man.standard(); | ||
494 | m_driver->setUrl( m_fileName ); | ||
495 | |||
496 | load(); | ||
497 | |||
498 | qWarning("C'tor OContactAccessBackend_SQL ends: %d ms", t.elapsed() ); | ||
499 | } | ||
500 | |||
501 | OContactAccessBackend_SQL::~OContactAccessBackend_SQL () | ||
502 | { | ||
503 | if( m_driver ) | ||
504 | delete m_driver; | ||
505 | } | ||
506 | |||
507 | bool OContactAccessBackend_SQL::load () | ||
508 | { | ||
509 | if (!m_driver->open() ) | ||
510 | return false; | ||
511 | |||
512 | // Don't expect that the database exists. | ||
513 | // It is save here to create the table, even if it | ||
514 | // do exist. ( Is that correct for all databases ?? ) | ||
515 | CreateQuery creat; | ||
516 | OSQLResult res = m_driver->query( &creat ); | ||
517 | |||
518 | update(); | ||
519 | |||
520 | return true; | ||
521 | |||
522 | } | ||
523 | |||
524 | bool OContactAccessBackend_SQL::reload() | ||
525 | { | ||
526 | return load(); | ||
527 | } | ||
528 | |||
529 | bool OContactAccessBackend_SQL::save() | ||
530 | { | ||
531 | return m_driver->close(); // Shouldn't m_driver->sync be better than close ? (eilers) | ||
532 | } | ||
533 | |||
534 | |||
535 | void OContactAccessBackend_SQL::clear () | ||
536 | { | ||
537 | ClearQuery cle; | ||
538 | OSQLResult res = m_driver->query( &cle ); | ||
539 | |||
540 | reload(); | ||
541 | } | ||
542 | |||
543 | bool OContactAccessBackend_SQL::wasChangedExternally() | ||
544 | { | ||
545 | return false; | ||
546 | } | ||
547 | |||
548 | QArray<int> OContactAccessBackend_SQL::allRecords() const | ||
549 | { | ||
550 | |||
551 | // FIXME: Think about cute handling of changed tables.. | ||
552 | // Thus, we don't have to call update here... | ||
553 | if ( m_changed ) | ||
554 | ((OContactAccessBackend_SQL*)this)->update(); | ||
555 | |||
556 | return m_uids; | ||
557 | } | ||
558 | |||
559 | bool OContactAccessBackend_SQL::add ( const OContact &newcontact ) | ||
560 | { | ||
561 | InsertQuery ins( newcontact ); | ||
562 | OSQLResult res = m_driver->query( &ins ); | ||
563 | |||
564 | if ( res.state() == OSQLResult::Failure ) | ||
565 | return false; | ||
566 | |||
567 | int c = m_uids.count(); | ||
568 | m_uids.resize( c+1 ); | ||
569 | m_uids[c] = newcontact.uid(); | ||
570 | |||
571 | return true; | ||
572 | } | ||
573 | |||
574 | |||
575 | bool OContactAccessBackend_SQL::remove ( int uid ) | ||
576 | { | ||
577 | RemoveQuery rem( uid ); | ||
578 | OSQLResult res = m_driver->query(&rem ); | ||
579 | |||
580 | if ( res.state() == OSQLResult::Failure ) | ||
581 | return false; | ||
582 | |||
583 | m_changed = true; | ||
584 | |||
585 | return true; | ||
586 | } | ||
587 | |||
588 | bool OContactAccessBackend_SQL::replace ( const OContact &contact ) | ||
589 | { | ||
590 | if ( !remove( contact.uid() ) ) | ||
591 | return false; | ||
592 | |||
593 | return add( contact ); | ||
594 | } | ||
595 | |||
596 | |||
597 | OContact OContactAccessBackend_SQL::find ( int uid ) const | ||
598 | { | ||
599 | qWarning("OContactAccessBackend_SQL::find()"); | ||
600 | QTime t; | ||
601 | t.start(); | ||
602 | |||
603 | OContact retContact( requestNonCustom( uid ) ); | ||
604 | retContact.setExtraMap( requestCustom( uid ) ); | ||
605 | |||
606 | qWarning("OContactAccessBackend_SQL::find() needed: %d ms", t.elapsed() ); | ||
607 | return retContact; | ||
608 | } | ||
609 | |||
610 | |||
611 | |||
612 | QArray<int> OContactAccessBackend_SQL::queryByExample ( const OContact &query, int settings, const QDateTime& d = QDateTime() ) | ||
613 | { | ||
614 | QString qu = "SELECT uid FROM addressbook WHERE"; | ||
615 | |||
616 | QMap<int, QString> queryFields = query.toMap(); | ||
617 | QStringList fieldList = OContactFields::untrfields( false ); | ||
618 | QMap<QString, int> translate = OContactFields::untrFieldsToId(); | ||
619 | |||
620 | // Convert every filled field to a SQL-Query | ||
621 | bool isAnyFieldSelected = false; | ||
622 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | ||
623 | int id = translate[*it]; | ||
624 | QString queryStr = queryFields[id]; | ||
625 | if ( !queryStr.isEmpty() ){ | ||
626 | isAnyFieldSelected = true; | ||
627 | switch( id ){ | ||
628 | default: | ||
629 | // Switching between case sensitive and insensitive... | ||
630 | // LIKE is not case sensitive, GLOB is case sensitive | ||
631 | // Do exist a better solution to switch this ? | ||
632 | if ( settings & OContactAccess::IgnoreCase ) | ||
633 | qu += "(\"" + *it + "\"" + " LIKE " + "'" | ||
634 | + queryStr.replace(QRegExp("\\*"),"%") + "'" + ") AND "; | ||
635 | else | ||
636 | qu += "(\"" + *it + "\"" + " GLOB " + "'" | ||
637 | + queryStr + "'" + ") AND "; | ||
638 | |||
639 | } | ||
640 | } | ||
641 | } | ||
642 | // Skip trailing "AND" | ||
643 | if ( isAnyFieldSelected ) | ||
644 | qu = qu.left( qu.length() - 4 ); | ||
645 | |||
646 | qWarning( "queryByExample query: %s", qu.latin1() ); | ||
647 | |||
648 | // Execute query and return the received uid's | ||
649 | OSQLRawQuery raw( qu ); | ||
650 | OSQLResult res = m_driver->query( &raw ); | ||
651 | if ( res.state() != OSQLResult::Success ){ | ||
652 | QArray<int> empty; | ||
653 | return empty; | ||
654 | } | ||
655 | |||
656 | QArray<int> list = extractUids( res ); | ||
657 | |||
658 | return list; | ||
659 | } | ||
660 | |||
661 | QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const | ||
662 | { | ||
663 | QArray<int> nix(0); | ||
664 | return nix; | ||
665 | } | ||
666 | |||
667 | const uint OContactAccessBackend_SQL::querySettings() | ||
668 | { | ||
669 | return OContactAccess::IgnoreCase | ||
670 | || OContactAccess::WildCards; | ||
671 | } | ||
672 | |||
673 | bool OContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const | ||
674 | { | ||
675 | /* OContactAccess::IgnoreCase, DateDiff, DateYear, DateMonth, DateDay | ||
676 | * may be added with any of the other settings. IgnoreCase should never used alone. | ||
677 | * Wildcards, RegExp, ExactMatch should never used at the same time... | ||
678 | */ | ||
679 | |||
680 | // Step 1: Check whether the given settings are supported by this backend | ||
681 | if ( ( querySettings & ( | ||
682 | OContactAccess::IgnoreCase | ||
683 | | OContactAccess::WildCards | ||
684 | // | OContactAccess::DateDiff | ||
685 | // | OContactAccess::DateYear | ||
686 | // | OContactAccess::DateMonth | ||
687 | // | OContactAccess::DateDay | ||
688 | // | OContactAccess::RegExp | ||
689 | // | OContactAccess::ExactMatch | ||
690 | ) ) != querySettings ) | ||
691 | return false; | ||
692 | |||
693 | // Step 2: Check whether the given combinations are ok.. | ||
694 | |||
695 | // IngoreCase alone is invalid | ||
696 | if ( querySettings == OContactAccess::IgnoreCase ) | ||
697 | return false; | ||
698 | |||
699 | // WildCards, RegExp and ExactMatch should never used at the same time | ||
700 | switch ( querySettings & ~( OContactAccess::IgnoreCase | ||
701 | | OContactAccess::DateDiff | ||
702 | | OContactAccess::DateYear | ||
703 | | OContactAccess::DateMonth | ||
704 | | OContactAccess::DateDay | ||
705 | ) | ||
706 | ){ | ||
707 | case OContactAccess::RegExp: | ||
708 | return ( true ); | ||
709 | case OContactAccess::WildCards: | ||
710 | return ( true ); | ||
711 | case OContactAccess::ExactMatch: | ||
712 | return ( true ); | ||
713 | case 0: // one of the upper removed bits were set.. | ||
714 | return ( true ); | ||
715 | default: | ||
716 | return ( false ); | ||
717 | } | ||
718 | |||
719 | } | ||
720 | |||
721 | QArray<int> OContactAccessBackend_SQL::sorted( bool asc, int , int , int ) | ||
722 | { | ||
723 | QTime t; | ||
724 | t.start(); | ||
725 | |||
726 | #ifdef __STORE_HORIZONTAL_ | ||
727 | QString query = "SELECT uid FROM addressbook "; | ||
728 | query += "ORDER BY \"Last Name\" "; | ||
729 | #else | ||
730 | QString query = "SELECT uid FROM addressbook WHERE type = 'Last Name' "; | ||
731 | query += "ORDER BY upper( value )"; | ||
732 | #endif | ||
733 | |||
734 | if ( !asc ) | ||
735 | query += "DESC"; | ||
736 | |||
737 | // qWarning("sorted query is: %s", query.latin1() ); | ||
738 | |||
739 | OSQLRawQuery raw( query ); | ||
740 | OSQLResult res = m_driver->query( &raw ); | ||
741 | if ( res.state() != OSQLResult::Success ){ | ||
742 | QArray<int> empty; | ||
743 | return empty; | ||
744 | } | ||
745 | |||
746 | QArray<int> list = extractUids( res ); | ||
747 | |||
748 | qWarning("sorted needed %d ms!", t.elapsed() ); | ||
749 | return list; | ||
750 | } | ||
751 | |||
752 | |||
753 | void OContactAccessBackend_SQL::update() | ||
754 | { | ||
755 | qWarning("Update starts"); | ||
756 | QTime t; | ||
757 | t.start(); | ||
758 | |||
759 | // Now load the database set and extract the uid's | ||
760 | // which will be held locally | ||
761 | |||
762 | LoadQuery lo; | ||
763 | OSQLResult res = m_driver->query(&lo); | ||
764 | if ( res.state() != OSQLResult::Success ) | ||
765 | return; | ||
766 | |||
767 | m_uids = extractUids( res ); | ||
768 | |||
769 | m_changed = false; | ||
770 | |||
771 | qWarning("Update ends %d ms", t.elapsed() ); | ||
772 | } | ||
773 | |||
774 | QArray<int> OContactAccessBackend_SQL::extractUids( OSQLResult& res ) const | ||
775 | { | ||
776 | qWarning("extractUids"); | ||
777 | QTime t; | ||
778 | t.start(); | ||
779 | OSQLResultItem::ValueList list = res.results(); | ||
780 | OSQLResultItem::ValueList::Iterator it; | ||
781 | QArray<int> ints(list.count() ); | ||
782 | qWarning(" count = %d", list.count() ); | ||
783 | |||
784 | int i = 0; | ||
785 | for (it = list.begin(); it != list.end(); ++it ) { | ||
786 | ints[i] = (*it).data("uid").toInt(); | ||
787 | i++; | ||
788 | } | ||
789 | qWarning("extractUids ready: count2 = %d needs %d ms", i, t.elapsed() ); | ||
790 | |||
791 | return ints; | ||
792 | |||
793 | } | ||
794 | |||
795 | #ifdef __STORE_HORIZONTAL_ | ||
796 | QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const | ||
797 | { | ||
798 | QTime t; | ||
799 | t.start(); | ||
800 | |||
801 | QMap<int, QString> nonCustomMap; | ||
802 | |||
803 | int t2needed = 0; | ||
804 | int t3needed = 0; | ||
805 | QTime t2; | ||
806 | t2.start(); | ||
807 | FindQuery query( uid ); | ||
808 | OSQLResult res_noncustom = m_driver->query( &query ); | ||
809 | t2needed = t2.elapsed(); | ||
810 | |||
811 | OSQLResultItem resItem = res_noncustom.first(); | ||
812 | |||
813 | QTime t3; | ||
814 | t3.start(); | ||
815 | // Now loop through all columns | ||
816 | QStringList fieldList = OContactFields::untrfields( false ); | ||
817 | QMap<QString, int> translate = OContactFields::untrFieldsToId(); | ||
818 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | ||
819 | // Get data for the selected column and store it with the | ||
820 | // corresponding id into the map.. | ||
821 | |||
822 | int id = translate[*it]; | ||
823 | QString value = resItem.data( (*it) ); | ||
824 | |||
825 | // qWarning("Reading %s... found: %s", (*it).latin1(), value.latin1() ); | ||
826 | |||
827 | switch( id ){ | ||
828 | case Qtopia::Birthday: | ||
829 | case Qtopia::Anniversary:{ | ||
830 | // Birthday and Anniversary are encoded special ( yyyy-mm-dd ) | ||
831 | QStringList list = QStringList::split( '-', value ); | ||
832 | QStringList::Iterator lit = list.begin(); | ||
833 | int year = (*lit).toInt(); | ||
834 | int month = (*(++lit)).toInt(); | ||
835 | int day = (*(++lit)).toInt(); | ||
836 | if ( ( day != 0 ) && ( month != 0 ) && ( year != 0 ) ){ | ||
837 | QDate date( year, month, day ); | ||
838 | nonCustomMap.insert( id, OConversion::dateToString( date ) ); | ||
839 | } | ||
840 | } | ||
841 | break; | ||
842 | case Qtopia::AddressCategory: | ||
843 | qWarning("Category is: %s", value.latin1() ); | ||
844 | default: | ||
845 | nonCustomMap.insert( id, value ); | ||
846 | } | ||
847 | } | ||
848 | |||
849 | // First insert uid | ||
850 | nonCustomMap.insert( Qtopia::AddressUid, resItem.data( "uid" ) ); | ||
851 | t3needed = t3.elapsed(); | ||
852 | |||
853 | // qWarning("Adding UID: %s", resItem.data( "uid" ).latin1() ); | ||
854 | qWarning("RequestNonCustom needed: insg.:%d ms, query: %d ms, mapping: %d ms", | ||
855 | t.elapsed(), t2needed, t3needed ); | ||
856 | |||
857 | return nonCustomMap; | ||
858 | } | ||
859 | #else | ||
860 | |||
861 | QMap<int, QString> OContactAccessBackend_SQL::requestNonCustom( int uid ) const | ||
862 | { | ||
863 | QTime t; | ||
864 | t.start(); | ||
865 | |||
866 | QMap<int, QString> nonCustomMap; | ||
867 | |||
868 | int t2needed = 0; | ||
869 | QTime t2; | ||
870 | t2.start(); | ||
871 | FindQuery query( uid ); | ||
872 | OSQLResult res_noncustom = m_driver->query( &query ); | ||
873 | t2needed = t2.elapsed(); | ||
874 | |||
875 | if ( res_noncustom.state() == OSQLResult::Failure ) { | ||
876 | qWarning("OSQLResult::Failure in find query !!"); | ||
877 | QMap<int, QString> empty; | ||
878 | return empty; | ||
879 | } | ||
880 | |||
881 | int t3needed = 0; | ||
882 | QTime t3; | ||
883 | t3.start(); | ||
884 | QMap<QString, int> translateMap = OContactFields::untrFieldsToId(); | ||
885 | |||
886 | OSQLResultItem::ValueList list = res_noncustom.results(); | ||
887 | OSQLResultItem::ValueList::Iterator it = list.begin(); | ||
888 | for ( ; it != list.end(); ++it ) { | ||
889 | if ( (*it).data("type") != "" ){ | ||
890 | int typeId = translateMap[(*it).data( "type" )]; | ||
891 | switch( typeId ){ | ||
892 | case Qtopia::Birthday: | ||
893 | case Qtopia::Anniversary:{ | ||
894 | // Birthday and Anniversary are encoded special ( yyyy-mm-dd ) | ||
895 | QStringList list = QStringList::split( '-', (*it).data( "value" ) ); | ||
896 | QStringList::Iterator lit = list.begin(); | ||
897 | int year = (*lit).toInt(); | ||
898 | qWarning("1. %s", (*lit).latin1()); | ||
899 | int month = (*(++lit)).toInt(); | ||
900 | qWarning("2. %s", (*lit).latin1()); | ||
901 | int day = (*(++lit)).toInt(); | ||
902 | qWarning("3. %s", (*lit).latin1()); | ||
903 | qWarning( "RequestNonCustom->Converting:%s to Year: %d, Month: %d, Day: %d ", (*it).data( "value" ).latin1(), year, month, day ); | ||
904 | QDate date( year, month, day ); | ||
905 | nonCustomMap.insert( typeId, OConversion::dateToString( date ) ); | ||
906 | } | ||
907 | break; | ||
908 | default: | ||
909 | nonCustomMap.insert( typeId, | ||
910 | (*it).data( "value" ) ); | ||
911 | } | ||
912 | } | ||
913 | } | ||
914 | // Add UID to Map.. | ||
915 | nonCustomMap.insert( Qtopia::AddressUid, QString::number( uid ) ); | ||
916 | t3needed = t3.elapsed(); | ||
917 | |||
918 | qWarning("RequestNonCustom needed: insg.:%d ms, query: %d ms, mapping: %d ms", t.elapsed(), t2needed, t3needed ); | ||
919 | return nonCustomMap; | ||
920 | } | ||
921 | |||
922 | #endif // __STORE_HORIZONTAL_ | ||
923 | |||
924 | QMap<QString, QString> OContactAccessBackend_SQL::requestCustom( int uid ) const | ||
925 | { | ||
926 | QTime t; | ||
927 | t.start(); | ||
928 | |||
929 | QMap<QString, QString> customMap; | ||
930 | |||
931 | FindCustomQuery query( uid ); | ||
932 | OSQLResult res_custom = m_driver->query( &query ); | ||
933 | |||
934 | if ( res_custom.state() == OSQLResult::Failure ) { | ||
935 | qWarning("OSQLResult::Failure in find query !!"); | ||
936 | QMap<QString, QString> empty; | ||
937 | return empty; | ||
938 | } | ||
939 | |||
940 | OSQLResultItem::ValueList list = res_custom.results(); | ||
941 | OSQLResultItem::ValueList::Iterator it = list.begin(); | ||
942 | for ( ; it != list.end(); ++it ) { | ||
943 | customMap.insert( (*it).data( "type" ), (*it).data( "value" ) ); | ||
944 | } | ||
945 | |||
946 | qWarning("RequestCustom needed: %d ms", t.elapsed() ); | ||
947 | return customMap; | ||
948 | } | ||