Diffstat (limited to 'libopie/pim/ocontactaccessbackend_sql.cpp') (more/less context) (ignore whitespace changes)
-rw-r--r-- | libopie/pim/ocontactaccessbackend_sql.cpp | 11 |
1 files changed, 10 insertions, 1 deletions
diff --git a/libopie/pim/ocontactaccessbackend_sql.cpp b/libopie/pim/ocontactaccessbackend_sql.cpp index 132c9fc..dd9dbde 100644 --- a/libopie/pim/ocontactaccessbackend_sql.cpp +++ b/libopie/pim/ocontactaccessbackend_sql.cpp | |||
@@ -1,208 +1,211 @@ | |||
1 | /* | 1 | /* |
2 | * SQL Backend for the OPIE-Contact Database. | 2 | * SQL Backend for the OPIE-Contact Database. |
3 | * | 3 | * |
4 | * Copyright (c) 2002 by Stefan Eilers (Eilers.Stefan@epost.de) | 4 | * Copyright (c) 2002 by Stefan Eilers (Eilers.Stefan@epost.de) |
5 | * | 5 | * |
6 | * ===================================================================== | 6 | * ===================================================================== |
7 | *This program is free software; you can redistribute it and/or | 7 | *This program is free software; you can redistribute it and/or |
8 | *modify it under the terms of the GNU Library General Public | 8 | *modify it under the terms of the GNU Library General Public |
9 | * License as published by the Free Software Foundation; either | 9 | * License as published by the Free Software Foundation; either |
10 | * version 2 of the License, or (at your option) any later version. | 10 | * version 2 of the License, or (at your option) any later version. |
11 | * ===================================================================== | 11 | * ===================================================================== |
12 | * ===================================================================== | 12 | * ===================================================================== |
13 | * Version: $Id$ | 13 | * Version: $Id$ |
14 | * ===================================================================== | 14 | * ===================================================================== |
15 | * History: | 15 | * History: |
16 | * $Log$ | 16 | * $Log$ |
17 | * Revision 1.3 2003/12/08 15:18:10 eilers | ||
18 | * Committing unfinished sql implementation before merging to libopie2 starts.. | ||
19 | * | ||
17 | * Revision 1.2 2003/09/29 07:44:26 eilers | 20 | * Revision 1.2 2003/09/29 07:44:26 eilers |
18 | * Improvement of PIM-SQL Databases, but search queries are still limited. | 21 | * 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. | 22 | * Addressbook: Changed table layout. Now, we just need 1/3 of disk-space. |
20 | * Todo: Started to add new attributes. Some type conversions missing. | 23 | * Todo: Started to add new attributes. Some type conversions missing. |
21 | * | 24 | * |
22 | * Revision 1.1 2003/09/22 14:31:16 eilers | 25 | * Revision 1.1 2003/09/22 14:31:16 eilers |
23 | * Added first experimental incarnation of sql-backend for addressbook. | 26 | * Added first experimental incarnation of sql-backend for addressbook. |
24 | * Some modifications to be able to compile the todo sql-backend. | 27 | * Some modifications to be able to compile the todo sql-backend. |
25 | * A lot of changes fill follow... | 28 | * A lot of changes fill follow... |
26 | * | 29 | * |
27 | */ | 30 | */ |
28 | 31 | ||
29 | #include "ocontactaccessbackend_sql.h" | 32 | #include "ocontactaccessbackend_sql.h" |
30 | 33 | ||
31 | #include <qarray.h> | 34 | #include <qarray.h> |
32 | #include <qdatetime.h> | 35 | #include <qdatetime.h> |
33 | #include <qstringlist.h> | 36 | #include <qstringlist.h> |
34 | 37 | ||
35 | #include <qpe/global.h> | 38 | #include <qpe/global.h> |
36 | #include <qpe/recordfields.h> | 39 | #include <qpe/recordfields.h> |
37 | 40 | ||
38 | #include <opie/ocontactfields.h> | 41 | #include <opie/ocontactfields.h> |
39 | #include <opie/oconversion.h> | 42 | #include <opie/oconversion.h> |
40 | #include <opie2/osqldriver.h> | 43 | #include <opie2/osqldriver.h> |
41 | #include <opie2/osqlresult.h> | 44 | #include <opie2/osqlresult.h> |
42 | #include <opie2/osqlmanager.h> | 45 | #include <opie2/osqlmanager.h> |
43 | #include <opie2/osqlquery.h> | 46 | #include <opie2/osqlquery.h> |
44 | 47 | ||
45 | 48 | ||
46 | 49 | ||
47 | 50 | ||
48 | // If defined, we use a horizontal table ( uid, attr1, attr2, attr3, ..., attrn ) instead | 51 | // If defined, we use a horizontal table ( uid, attr1, attr2, attr3, ..., attrn ) instead |
49 | // vertical like "uid, type, value". | 52 | // vertical like "uid, type, value". |
50 | // DON'T DEACTIVATE THIS DEFINE IN PRODUCTIVE ENVIRONMENTS !! | 53 | // DON'T DEACTIVATE THIS DEFINE IN PRODUCTIVE ENVIRONMENTS !! |
51 | #define __STORE_HORIZONTAL_ | 54 | #define __STORE_HORIZONTAL_ |
52 | 55 | ||
53 | // Distinct loading is not very fast. If I expect that every person has just | 56 | // 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, | 57 | // one (and always one) 'Last Name', I can request all uid's for existing lastnames, |
55 | // which is faster.. | 58 | // which is faster.. |
56 | // But this may not be true for all entries, like company contacts.. | 59 | // 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) | 60 | // The current AddressBook application handles this problem, but other may not.. (eilers) |
58 | #define __USE_SUPERFAST_LOADQUERY | 61 | #define __USE_SUPERFAST_LOADQUERY |
59 | 62 | ||
60 | 63 | ||
61 | /* | 64 | /* |
62 | * Implementation of used query types | 65 | * Implementation of used query types |
63 | * CREATE query | 66 | * CREATE query |
64 | * LOAD query | 67 | * LOAD query |
65 | * INSERT | 68 | * INSERT |
66 | * REMOVE | 69 | * REMOVE |
67 | * CLEAR | 70 | * CLEAR |
68 | */ | 71 | */ |
69 | namespace { | 72 | namespace { |
70 | /** | 73 | /** |
71 | * CreateQuery for the Todolist Table | 74 | * CreateQuery for the Todolist Table |
72 | */ | 75 | */ |
73 | class CreateQuery : public OSQLQuery { | 76 | class CreateQuery : public OSQLQuery { |
74 | public: | 77 | public: |
75 | CreateQuery(); | 78 | CreateQuery(); |
76 | ~CreateQuery(); | 79 | ~CreateQuery(); |
77 | QString query()const; | 80 | QString query()const; |
78 | }; | 81 | }; |
79 | 82 | ||
80 | /** | 83 | /** |
81 | * Clears (delete) a Table | 84 | * Clears (delete) a Table |
82 | */ | 85 | */ |
83 | class ClearQuery : public OSQLQuery { | 86 | class ClearQuery : public OSQLQuery { |
84 | public: | 87 | public: |
85 | ClearQuery(); | 88 | ClearQuery(); |
86 | ~ClearQuery(); | 89 | ~ClearQuery(); |
87 | QString query()const; | 90 | QString query()const; |
88 | 91 | ||
89 | }; | 92 | }; |
90 | 93 | ||
91 | 94 | ||
92 | /** | 95 | /** |
93 | * LoadQuery | 96 | * LoadQuery |
94 | * this one queries for all uids | 97 | * this one queries for all uids |
95 | */ | 98 | */ |
96 | class LoadQuery : public OSQLQuery { | 99 | class LoadQuery : public OSQLQuery { |
97 | public: | 100 | public: |
98 | LoadQuery(); | 101 | LoadQuery(); |
99 | ~LoadQuery(); | 102 | ~LoadQuery(); |
100 | QString query()const; | 103 | QString query()const; |
101 | }; | 104 | }; |
102 | 105 | ||
103 | /** | 106 | /** |
104 | * inserts/adds a OContact to the table | 107 | * inserts/adds a OContact to the table |
105 | */ | 108 | */ |
106 | class InsertQuery : public OSQLQuery { | 109 | class InsertQuery : public OSQLQuery { |
107 | public: | 110 | public: |
108 | InsertQuery(const OContact& ); | 111 | InsertQuery(const OContact& ); |
109 | ~InsertQuery(); | 112 | ~InsertQuery(); |
110 | QString query()const; | 113 | QString query()const; |
111 | private: | 114 | private: |
112 | OContact m_contact; | 115 | OContact m_contact; |
113 | }; | 116 | }; |
114 | 117 | ||
115 | 118 | ||
116 | /** | 119 | /** |
117 | * removes one from the table | 120 | * removes one from the table |
118 | */ | 121 | */ |
119 | class RemoveQuery : public OSQLQuery { | 122 | class RemoveQuery : public OSQLQuery { |
120 | public: | 123 | public: |
121 | RemoveQuery(int uid ); | 124 | RemoveQuery(int uid ); |
122 | ~RemoveQuery(); | 125 | ~RemoveQuery(); |
123 | QString query()const; | 126 | QString query()const; |
124 | private: | 127 | private: |
125 | int m_uid; | 128 | int m_uid; |
126 | }; | 129 | }; |
127 | 130 | ||
128 | /** | 131 | /** |
129 | * a find query for noncustom elements | 132 | * a find query for noncustom elements |
130 | */ | 133 | */ |
131 | class FindQuery : public OSQLQuery { | 134 | class FindQuery : public OSQLQuery { |
132 | public: | 135 | public: |
133 | FindQuery(int uid); | 136 | FindQuery(int uid); |
134 | FindQuery(const QArray<int>& ); | 137 | FindQuery(const QArray<int>& ); |
135 | ~FindQuery(); | 138 | ~FindQuery(); |
136 | QString query()const; | 139 | QString query()const; |
137 | private: | 140 | private: |
138 | QString single()const; | 141 | QString single()const; |
139 | QString multi()const; | 142 | QString multi()const; |
140 | QArray<int> m_uids; | 143 | QArray<int> m_uids; |
141 | int m_uid; | 144 | int m_uid; |
142 | }; | 145 | }; |
143 | 146 | ||
144 | /** | 147 | /** |
145 | * a find query for custom elements | 148 | * a find query for custom elements |
146 | */ | 149 | */ |
147 | class FindCustomQuery : public OSQLQuery { | 150 | class FindCustomQuery : public OSQLQuery { |
148 | public: | 151 | public: |
149 | FindCustomQuery(int uid); | 152 | FindCustomQuery(int uid); |
150 | FindCustomQuery(const QArray<int>& ); | 153 | FindCustomQuery(const QArray<int>& ); |
151 | ~FindCustomQuery(); | 154 | ~FindCustomQuery(); |
152 | QString query()const; | 155 | QString query()const; |
153 | private: | 156 | private: |
154 | QString single()const; | 157 | QString single()const; |
155 | QString multi()const; | 158 | QString multi()const; |
156 | QArray<int> m_uids; | 159 | QArray<int> m_uids; |
157 | int m_uid; | 160 | int m_uid; |
158 | }; | 161 | }; |
159 | 162 | ||
160 | 163 | ||
161 | 164 | ||
162 | // We using three tables to store the information: | 165 | // We using three tables to store the information: |
163 | // 1. addressbook : It contains General information about the contact (non custom) | 166 | // 1. addressbook : It contains General information about the contact (non custom) |
164 | // 2. custom_data : Not official supported entries | 167 | // 2. custom_data : Not official supported entries |
165 | // All tables are connected by the uid of the contact. | 168 | // All tables are connected by the uid of the contact. |
166 | // Maybe I should add a table for meta-information ? | 169 | // Maybe I should add a table for meta-information ? |
167 | CreateQuery::CreateQuery() : OSQLQuery() {} | 170 | CreateQuery::CreateQuery() : OSQLQuery() {} |
168 | CreateQuery::~CreateQuery() {} | 171 | CreateQuery::~CreateQuery() {} |
169 | QString CreateQuery::query()const { | 172 | QString CreateQuery::query()const { |
170 | QString qu; | 173 | QString qu; |
171 | #ifdef __STORE_HORIZONTAL_ | 174 | #ifdef __STORE_HORIZONTAL_ |
172 | 175 | ||
173 | qu += "create table addressbook( uid PRIMARY KEY "; | 176 | qu += "create table addressbook( uid PRIMARY KEY "; |
174 | 177 | ||
175 | QStringList fieldList = OContactFields::untrfields( false ); | 178 | QStringList fieldList = OContactFields::untrfields( false ); |
176 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | 179 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ |
177 | qu += QString( ",\"%1\" VARCHAR(10)" ).arg( *it ); | 180 | qu += QString( ",\"%1\" VARCHAR(10)" ).arg( *it ); |
178 | } | 181 | } |
179 | qu += " );"; | 182 | qu += " );"; |
180 | 183 | ||
181 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; | 184 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; |
182 | 185 | ||
183 | #else | 186 | #else |
184 | 187 | ||
185 | qu += "create table addressbook( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id));"; | 188 | qu += "create table addressbook( 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) );"; | 189 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; |
187 | // qu += "create table dates( uid PRIMARY KEY, type, day, month, year, hour, minute, second );"; | 190 | // qu += "create table dates( uid PRIMARY KEY, type, day, month, year, hour, minute, second );"; |
188 | 191 | ||
189 | #endif // __STORE_HORIZONTAL_ | 192 | #endif // __STORE_HORIZONTAL_ |
190 | return qu; | 193 | return qu; |
191 | } | 194 | } |
192 | 195 | ||
193 | ClearQuery::ClearQuery() | 196 | ClearQuery::ClearQuery() |
194 | : OSQLQuery() {} | 197 | : OSQLQuery() {} |
195 | ClearQuery::~ClearQuery() {} | 198 | ClearQuery::~ClearQuery() {} |
196 | QString ClearQuery::query()const { | 199 | QString ClearQuery::query()const { |
197 | QString qu = "drop table addressbook;"; | 200 | QString qu = "drop table addressbook;"; |
198 | qu += "drop table custom_data;"; | 201 | qu += "drop table custom_data;"; |
199 | // qu += "drop table dates;"; | 202 | // qu += "drop table dates;"; |
200 | return qu; | 203 | return qu; |
201 | } | 204 | } |
202 | 205 | ||
203 | 206 | ||
204 | LoadQuery::LoadQuery() : OSQLQuery() {} | 207 | LoadQuery::LoadQuery() : OSQLQuery() {} |
205 | LoadQuery::~LoadQuery() {} | 208 | LoadQuery::~LoadQuery() {} |
206 | QString LoadQuery::query()const { | 209 | QString LoadQuery::query()const { |
207 | QString qu; | 210 | QString qu; |
208 | #ifdef __STORE_HORIZONTAL_ | 211 | #ifdef __STORE_HORIZONTAL_ |
@@ -265,406 +268,412 @@ namespace { | |||
265 | // These entries should stored in a special format | 268 | // These entries should stored in a special format |
266 | // year-month-day | 269 | // year-month-day |
267 | QDate day = m_contact.anniversary(); | 270 | QDate day = m_contact.anniversary(); |
268 | if ( day.isValid() ){ | 271 | if ( day.isValid() ){ |
269 | qu += QString(",\"%1-%2-%3\"") | 272 | qu += QString(",\"%1-%2-%3\"") |
270 | .arg( day.year() ) | 273 | .arg( day.year() ) |
271 | .arg( day.month() ) | 274 | .arg( day.month() ) |
272 | .arg( day.day() ); | 275 | .arg( day.day() ); |
273 | } else { | 276 | } else { |
274 | qu += ",\"\""; | 277 | qu += ",\"\""; |
275 | } | 278 | } |
276 | } | 279 | } |
277 | break; | 280 | break; |
278 | 281 | ||
279 | default: | 282 | default: |
280 | qu += QString( ",\"%1\"" ).arg( contactMap[id] ); | 283 | qu += QString( ",\"%1\"" ).arg( contactMap[id] ); |
281 | } | 284 | } |
282 | } | 285 | } |
283 | qu += " );"; | 286 | qu += " );"; |
284 | 287 | ||
285 | 288 | ||
286 | #else | 289 | #else |
287 | // Get all information out of the contact-class | 290 | // Get all information out of the contact-class |
288 | // Remember: The category is stored in contactMap, too ! | 291 | // Remember: The category is stored in contactMap, too ! |
289 | QMap<int, QString> contactMap = m_contact.toMap(); | 292 | QMap<int, QString> contactMap = m_contact.toMap(); |
290 | 293 | ||
291 | QMap<QString, QString> addressbook_db; | 294 | QMap<QString, QString> addressbook_db; |
292 | 295 | ||
293 | // Get the translation from the ID to the String | 296 | // Get the translation from the ID to the String |
294 | QMap<int, QString> transMap = OContactFields::idToUntrFields(); | 297 | QMap<int, QString> transMap = OContactFields::idToUntrFields(); |
295 | 298 | ||
296 | for( QMap<int, QString>::Iterator it = contactMap.begin(); | 299 | for( QMap<int, QString>::Iterator it = contactMap.begin(); |
297 | it != contactMap.end(); ++it ){ | 300 | it != contactMap.end(); ++it ){ |
298 | switch ( it.key() ){ | 301 | switch ( it.key() ){ |
299 | case Qtopia::Birthday:{ | 302 | case Qtopia::Birthday:{ |
300 | // These entries should stored in a special format | 303 | // These entries should stored in a special format |
301 | // year-month-day | 304 | // year-month-day |
302 | QDate day = m_contact.birthday(); | 305 | QDate day = m_contact.birthday(); |
303 | addressbook_db.insert( transMap[it.key()], | 306 | addressbook_db.insert( transMap[it.key()], |
304 | QString("%1-%2-%3") | 307 | QString("%1-%2-%3") |
305 | .arg( day.year() ) | 308 | .arg( day.year() ) |
306 | .arg( day.month() ) | 309 | .arg( day.month() ) |
307 | .arg( day.day() ) ); | 310 | .arg( day.day() ) ); |
308 | } | 311 | } |
309 | break; | 312 | break; |
310 | case Qtopia::Anniversary:{ | 313 | case Qtopia::Anniversary:{ |
311 | // These entries should stored in a special format | 314 | // These entries should stored in a special format |
312 | // year-month-day | 315 | // year-month-day |
313 | QDate day = m_contact.anniversary(); | 316 | QDate day = m_contact.anniversary(); |
314 | addressbook_db.insert( transMap[it.key()], | 317 | addressbook_db.insert( transMap[it.key()], |
315 | QString("%1-%2-%3") | 318 | QString("%1-%2-%3") |
316 | .arg( day.year() ) | 319 | .arg( day.year() ) |
317 | .arg( day.month() ) | 320 | .arg( day.month() ) |
318 | .arg( day.day() ) ); | 321 | .arg( day.day() ) ); |
319 | } | 322 | } |
320 | break; | 323 | break; |
321 | case Qtopia::AddressUid: // Ignore UID | 324 | case Qtopia::AddressUid: // Ignore UID |
322 | break; | 325 | break; |
323 | default: // Translate id to String | 326 | default: // Translate id to String |
324 | addressbook_db.insert( transMap[it.key()], it.data() ); | 327 | addressbook_db.insert( transMap[it.key()], it.data() ); |
325 | break; | 328 | break; |
326 | } | 329 | } |
327 | 330 | ||
328 | } | 331 | } |
329 | 332 | ||
330 | // Now convert this whole stuff into a SQL String, beginning with | 333 | // Now convert this whole stuff into a SQL String, beginning with |
331 | // the addressbook table.. | 334 | // the addressbook table.. |
332 | QString qu; | 335 | QString qu; |
333 | // qu += "begin transaction;"; | 336 | // qu += "begin transaction;"; |
334 | int id = 0; | 337 | int id = 0; |
335 | for( QMap<QString, QString>::Iterator it = addressbook_db.begin(); | 338 | for( QMap<QString, QString>::Iterator it = addressbook_db.begin(); |
336 | it != addressbook_db.end(); ++it ){ | 339 | it != addressbook_db.end(); ++it ){ |
337 | qu += "insert into addressbook VALUES(" | 340 | qu += "insert into addressbook VALUES(" |
338 | + QString::number( m_contact.uid() ) | 341 | + QString::number( m_contact.uid() ) |
339 | + "," | 342 | + "," |
340 | + QString::number( id++ ) | 343 | + QString::number( id++ ) |
341 | + ",'" | 344 | + ",'" |
342 | + it.key() //.latin1() | 345 | + it.key() //.latin1() |
343 | + "'," | 346 | + "'," |
344 | + "0" // Priority for future enhancements | 347 | + "0" // Priority for future enhancements |
345 | + ",'" | 348 | + ",'" |
346 | + it.data() //.latin1() | 349 | + it.data() //.latin1() |
347 | + "');"; | 350 | + "');"; |
348 | } | 351 | } |
349 | 352 | ||
350 | #endif //__STORE_HORIZONTAL_ | 353 | #endif //__STORE_HORIZONTAL_ |
351 | // Now add custom data.. | 354 | // Now add custom data.. |
352 | #ifdef __STORE_HORIZONTAL_ | 355 | #ifdef __STORE_HORIZONTAL_ |
353 | int id = 0; | 356 | int id = 0; |
354 | #endif | 357 | #endif |
355 | id = 0; | 358 | id = 0; |
356 | QMap<QString, QString> customMap = m_contact.toExtraMap(); | 359 | QMap<QString, QString> customMap = m_contact.toExtraMap(); |
357 | for( QMap<QString, QString>::Iterator it = customMap.begin(); | 360 | for( QMap<QString, QString>::Iterator it = customMap.begin(); |
358 | it != customMap.end(); ++it ){ | 361 | it != customMap.end(); ++it ){ |
359 | qu += "insert into custom_data VALUES(" | 362 | qu += "insert into custom_data VALUES(" |
360 | + QString::number( m_contact.uid() ) | 363 | + QString::number( m_contact.uid() ) |
361 | + "," | 364 | + "," |
362 | + QString::number( id++ ) | 365 | + QString::number( id++ ) |
363 | + ",'" | 366 | + ",'" |
364 | + it.key() //.latin1() | 367 | + it.key() //.latin1() |
365 | + "'," | 368 | + "'," |
366 | + "0" // Priority for future enhancements | 369 | + "0" // Priority for future enhancements |
367 | + ",'" | 370 | + ",'" |
368 | + it.data() //.latin1() | 371 | + it.data() //.latin1() |
369 | + "');"; | 372 | + "');"; |
370 | } | 373 | } |
371 | // qu += "commit;"; | 374 | // qu += "commit;"; |
372 | qWarning("add %s", qu.latin1() ); | 375 | qWarning("add %s", qu.latin1() ); |
373 | return qu; | 376 | return qu; |
374 | } | 377 | } |
375 | 378 | ||
376 | 379 | ||
377 | RemoveQuery::RemoveQuery(int uid ) | 380 | RemoveQuery::RemoveQuery(int uid ) |
378 | : OSQLQuery(), m_uid( uid ) {} | 381 | : OSQLQuery(), m_uid( uid ) {} |
379 | RemoveQuery::~RemoveQuery() {} | 382 | RemoveQuery::~RemoveQuery() {} |
380 | QString RemoveQuery::query()const { | 383 | QString RemoveQuery::query()const { |
381 | QString qu = "DELETE from addressbook where uid = " | 384 | QString qu = "DELETE from addressbook where uid = " |
382 | + QString::number(m_uid) + ";"; | 385 | + QString::number(m_uid) + ";"; |
383 | qu += "DELETE from custom_data where uid = " | 386 | qu += "DELETE from custom_data where uid = " |
384 | + QString::number(m_uid) + ";"; | 387 | + QString::number(m_uid) + ";"; |
385 | return qu; | 388 | return qu; |
386 | } | 389 | } |
387 | 390 | ||
388 | 391 | ||
389 | 392 | ||
390 | 393 | ||
391 | FindQuery::FindQuery(int uid) | 394 | FindQuery::FindQuery(int uid) |
392 | : OSQLQuery(), m_uid( uid ) { | 395 | : OSQLQuery(), m_uid( uid ) { |
393 | } | 396 | } |
394 | FindQuery::FindQuery(const QArray<int>& ints) | 397 | FindQuery::FindQuery(const QArray<int>& ints) |
395 | : OSQLQuery(), m_uids( ints ){ | 398 | : OSQLQuery(), m_uids( ints ){ |
396 | } | 399 | } |
397 | FindQuery::~FindQuery() { | 400 | FindQuery::~FindQuery() { |
398 | } | 401 | } |
399 | QString FindQuery::query()const{ | 402 | QString FindQuery::query()const{ |
400 | // if ( m_uids.count() == 0 ) | 403 | // if ( m_uids.count() == 0 ) |
401 | return single(); | 404 | return single(); |
402 | } | 405 | } |
403 | /* | 406 | /* |
404 | else | 407 | else |
405 | return multi(); | 408 | return multi(); |
406 | } | 409 | } |
407 | QString FindQuery::multi()const { | 410 | QString FindQuery::multi()const { |
408 | QString qu = "select uid, type, value from addressbook where"; | 411 | QString qu = "select uid, type, value from addressbook where"; |
409 | for (uint i = 0; i < m_uids.count(); i++ ) { | 412 | for (uint i = 0; i < m_uids.count(); i++ ) { |
410 | qu += " UID = " + QString::number( m_uids[i] ) + " OR"; | 413 | qu += " UID = " + QString::number( m_uids[i] ) + " OR"; |
411 | } | 414 | } |
412 | qu.remove( qu.length()-2, 2 ); // Hmmmm.. | 415 | qu.remove( qu.length()-2, 2 ); // Hmmmm.. |
413 | return qu; | 416 | return qu; |
414 | } | 417 | } |
415 | */ | 418 | */ |
416 | #ifdef __STORE_HORIZONTAL_ | 419 | #ifdef __STORE_HORIZONTAL_ |
417 | QString FindQuery::single()const{ | 420 | QString FindQuery::single()const{ |
418 | QString qu = "select *"; | 421 | QString qu = "select *"; |
419 | qu += " from addressbook where uid = " + QString::number(m_uid); | 422 | qu += " from addressbook where uid = " + QString::number(m_uid); |
420 | 423 | ||
421 | // qWarning("find query: %s", qu.latin1() ); | 424 | // qWarning("find query: %s", qu.latin1() ); |
422 | return qu; | 425 | return qu; |
423 | } | 426 | } |
424 | #else | 427 | #else |
425 | QString FindQuery::single()const{ | 428 | QString FindQuery::single()const{ |
426 | QString qu = "select uid, type, value from addressbook where uid = "; | 429 | QString qu = "select uid, type, value from addressbook where uid = "; |
427 | qu += QString::number(m_uid); | 430 | qu += QString::number(m_uid); |
428 | return qu; | 431 | return qu; |
429 | } | 432 | } |
430 | #endif | 433 | #endif |
431 | 434 | ||
432 | 435 | ||
433 | FindCustomQuery::FindCustomQuery(int uid) | 436 | FindCustomQuery::FindCustomQuery(int uid) |
434 | : OSQLQuery(), m_uid( uid ) { | 437 | : OSQLQuery(), m_uid( uid ) { |
435 | } | 438 | } |
436 | FindCustomQuery::FindCustomQuery(const QArray<int>& ints) | 439 | FindCustomQuery::FindCustomQuery(const QArray<int>& ints) |
437 | : OSQLQuery(), m_uids( ints ){ | 440 | : OSQLQuery(), m_uids( ints ){ |
438 | } | 441 | } |
439 | FindCustomQuery::~FindCustomQuery() { | 442 | FindCustomQuery::~FindCustomQuery() { |
440 | } | 443 | } |
441 | QString FindCustomQuery::query()const{ | 444 | QString FindCustomQuery::query()const{ |
442 | // if ( m_uids.count() == 0 ) | 445 | // if ( m_uids.count() == 0 ) |
443 | return single(); | 446 | return single(); |
444 | } | 447 | } |
445 | QString FindCustomQuery::single()const{ | 448 | QString FindCustomQuery::single()const{ |
446 | QString qu = "select uid, type, value from custom_data where uid = "; | 449 | QString qu = "select uid, type, value from custom_data where uid = "; |
447 | qu += QString::number(m_uid); | 450 | qu += QString::number(m_uid); |
448 | return qu; | 451 | return qu; |
449 | } | 452 | } |
450 | 453 | ||
451 | }; | 454 | }; |
452 | 455 | ||
453 | 456 | ||
454 | /* --------------------------------------------------------------------------- */ | 457 | /* --------------------------------------------------------------------------- */ |
455 | 458 | ||
456 | OContactAccessBackend_SQL::OContactAccessBackend_SQL ( const QString& /* appname */, | 459 | OContactAccessBackend_SQL::OContactAccessBackend_SQL ( const QString& /* appname */, |
457 | const QString& filename ): m_changed(false) | 460 | const QString& filename ): |
461 | OContactAccessBackend(), m_changed(false), m_driver( NULL ) | ||
458 | { | 462 | { |
459 | qWarning("C'tor OContactAccessBackend_SQL starts"); | 463 | qWarning("C'tor OContactAccessBackend_SQL starts"); |
460 | QTime t; | 464 | QTime t; |
461 | t.start(); | 465 | t.start(); |
462 | 466 | ||
463 | /* Expecting to access the default filename if nothing else is set */ | 467 | /* Expecting to access the default filename if nothing else is set */ |
464 | if ( filename.isEmpty() ){ | 468 | if ( filename.isEmpty() ){ |
465 | m_fileName = Global::applicationFileName( "addressbook","addressbook.db" ); | 469 | m_fileName = Global::applicationFileName( "addressbook","addressbook.db" ); |
466 | } else | 470 | } else |
467 | m_fileName = filename; | 471 | m_fileName = filename; |
468 | 472 | ||
469 | // Get the standart sql-driver from the OSQLManager.. | 473 | // Get the standart sql-driver from the OSQLManager.. |
470 | OSQLManager man; | 474 | OSQLManager man; |
471 | m_driver = man.standard(); | 475 | m_driver = man.standard(); |
472 | m_driver->setUrl( m_fileName ); | 476 | m_driver->setUrl( m_fileName ); |
473 | 477 | ||
474 | load(); | 478 | load(); |
475 | 479 | ||
476 | qWarning("C'tor OContactAccessBackend_SQL ends: %d ms", t.elapsed() ); | 480 | qWarning("C'tor OContactAccessBackend_SQL ends: %d ms", t.elapsed() ); |
477 | } | 481 | } |
478 | 482 | ||
483 | OContactAccessBackend_SQL::~OContactAccessBackend_SQL () | ||
484 | { | ||
485 | if( m_driver ) | ||
486 | delete m_driver; | ||
487 | } | ||
479 | 488 | ||
480 | bool OContactAccessBackend_SQL::load () | 489 | bool OContactAccessBackend_SQL::load () |
481 | { | 490 | { |
482 | if (!m_driver->open() ) | 491 | if (!m_driver->open() ) |
483 | return false; | 492 | return false; |
484 | 493 | ||
485 | // Don't expect that the database exists. | 494 | // Don't expect that the database exists. |
486 | // It is save here to create the table, even if it | 495 | // It is save here to create the table, even if it |
487 | // do exist. ( Is that correct for all databases ?? ) | 496 | // do exist. ( Is that correct for all databases ?? ) |
488 | CreateQuery creat; | 497 | CreateQuery creat; |
489 | OSQLResult res = m_driver->query( &creat ); | 498 | OSQLResult res = m_driver->query( &creat ); |
490 | 499 | ||
491 | update(); | 500 | update(); |
492 | 501 | ||
493 | return true; | 502 | return true; |
494 | 503 | ||
495 | } | 504 | } |
496 | 505 | ||
497 | bool OContactAccessBackend_SQL::reload() | 506 | bool OContactAccessBackend_SQL::reload() |
498 | { | 507 | { |
499 | return load(); | 508 | return load(); |
500 | } | 509 | } |
501 | 510 | ||
502 | bool OContactAccessBackend_SQL::save() | 511 | bool OContactAccessBackend_SQL::save() |
503 | { | 512 | { |
504 | return m_driver->close(); | 513 | return m_driver->close(); |
505 | } | 514 | } |
506 | 515 | ||
507 | 516 | ||
508 | void OContactAccessBackend_SQL::clear () | 517 | void OContactAccessBackend_SQL::clear () |
509 | { | 518 | { |
510 | ClearQuery cle; | 519 | ClearQuery cle; |
511 | OSQLResult res = m_driver->query( &cle ); | 520 | OSQLResult res = m_driver->query( &cle ); |
512 | CreateQuery qu; | 521 | CreateQuery qu; |
513 | res = m_driver->query(&qu); | 522 | res = m_driver->query(&qu); |
514 | } | 523 | } |
515 | 524 | ||
516 | bool OContactAccessBackend_SQL::wasChangedExternally() | 525 | bool OContactAccessBackend_SQL::wasChangedExternally() |
517 | { | 526 | { |
518 | return false; | 527 | return false; |
519 | } | 528 | } |
520 | 529 | ||
521 | QArray<int> OContactAccessBackend_SQL::allRecords() const | 530 | QArray<int> OContactAccessBackend_SQL::allRecords() const |
522 | { | 531 | { |
523 | 532 | ||
524 | // FIXME: Think about cute handling of changed tables.. | 533 | // FIXME: Think about cute handling of changed tables.. |
525 | // Thus, we don't have to call update here... | 534 | // Thus, we don't have to call update here... |
526 | if ( m_changed ) | 535 | if ( m_changed ) |
527 | ((OContactAccessBackend_SQL*)this)->update(); | 536 | ((OContactAccessBackend_SQL*)this)->update(); |
528 | 537 | ||
529 | return m_uids; | 538 | return m_uids; |
530 | } | 539 | } |
531 | 540 | ||
532 | bool OContactAccessBackend_SQL::add ( const OContact &newcontact ) | 541 | bool OContactAccessBackend_SQL::add ( const OContact &newcontact ) |
533 | { | 542 | { |
534 | InsertQuery ins( newcontact ); | 543 | InsertQuery ins( newcontact ); |
535 | OSQLResult res = m_driver->query( &ins ); | 544 | OSQLResult res = m_driver->query( &ins ); |
536 | 545 | ||
537 | if ( res.state() == OSQLResult::Failure ) | 546 | if ( res.state() == OSQLResult::Failure ) |
538 | return false; | 547 | return false; |
539 | 548 | ||
540 | int c = m_uids.count(); | 549 | int c = m_uids.count(); |
541 | m_uids.resize( c+1 ); | 550 | m_uids.resize( c+1 ); |
542 | m_uids[c] = newcontact.uid(); | 551 | m_uids[c] = newcontact.uid(); |
543 | 552 | ||
544 | return true; | 553 | return true; |
545 | } | 554 | } |
546 | 555 | ||
547 | 556 | ||
548 | bool OContactAccessBackend_SQL::remove ( int uid ) | 557 | bool OContactAccessBackend_SQL::remove ( int uid ) |
549 | { | 558 | { |
550 | RemoveQuery rem( uid ); | 559 | RemoveQuery rem( uid ); |
551 | OSQLResult res = m_driver->query(&rem ); | 560 | OSQLResult res = m_driver->query(&rem ); |
552 | 561 | ||
553 | if ( res.state() == OSQLResult::Failure ) | 562 | if ( res.state() == OSQLResult::Failure ) |
554 | return false; | 563 | return false; |
555 | 564 | ||
556 | m_changed = true; | 565 | m_changed = true; |
557 | 566 | ||
558 | return true; | 567 | return true; |
559 | } | 568 | } |
560 | 569 | ||
561 | bool OContactAccessBackend_SQL::replace ( const OContact &contact ) | 570 | bool OContactAccessBackend_SQL::replace ( const OContact &contact ) |
562 | { | 571 | { |
563 | if ( !remove( contact.uid() ) ) | 572 | if ( !remove( contact.uid() ) ) |
564 | return false; | 573 | return false; |
565 | 574 | ||
566 | return add( contact ); | 575 | return add( contact ); |
567 | } | 576 | } |
568 | 577 | ||
569 | 578 | ||
570 | OContact OContactAccessBackend_SQL::find ( int uid ) const | 579 | OContact OContactAccessBackend_SQL::find ( int uid ) const |
571 | { | 580 | { |
572 | qWarning("OContactAccessBackend_SQL::find()"); | 581 | qWarning("OContactAccessBackend_SQL::find()"); |
573 | QTime t; | 582 | QTime t; |
574 | t.start(); | 583 | t.start(); |
575 | 584 | ||
576 | OContact retContact( requestNonCustom( uid ) ); | 585 | OContact retContact( requestNonCustom( uid ) ); |
577 | retContact.setExtraMap( requestCustom( uid ) ); | 586 | retContact.setExtraMap( requestCustom( uid ) ); |
578 | 587 | ||
579 | qWarning("OContactAccessBackend_SQL::find() needed: %d ms", t.elapsed() ); | 588 | qWarning("OContactAccessBackend_SQL::find() needed: %d ms", t.elapsed() ); |
580 | return retContact; | 589 | return retContact; |
581 | } | 590 | } |
582 | 591 | ||
583 | 592 | ||
584 | 593 | ||
585 | QArray<int> OContactAccessBackend_SQL::queryByExample ( const OContact &query, int settings, const QDateTime& d = QDateTime() ) | 594 | QArray<int> OContactAccessBackend_SQL::queryByExample ( const OContact &query, int settings, const QDateTime& d = QDateTime() ) |
586 | { | 595 | { |
587 | QString qu = "SELECT uid FROM addressbook WHERE"; | 596 | QString qu = "SELECT uid FROM addressbook WHERE"; |
588 | 597 | ||
589 | QMap<int, QString> queryFields = query.toMap(); | 598 | QMap<int, QString> queryFields = query.toMap(); |
590 | QStringList fieldList = OContactFields::untrfields( false ); | 599 | QStringList fieldList = OContactFields::untrfields( false ); |
591 | QMap<QString, int> translate = OContactFields::untrFieldsToId(); | 600 | QMap<QString, int> translate = OContactFields::untrFieldsToId(); |
592 | 601 | ||
593 | // Convert every filled field to a SQL-Query | 602 | // Convert every filled field to a SQL-Query |
594 | bool isAnyFieldSelected = false; | 603 | bool isAnyFieldSelected = false; |
595 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ | 604 | for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){ |
596 | int id = translate[*it]; | 605 | int id = translate[*it]; |
597 | QString queryStr = queryFields[id]; | 606 | QString queryStr = queryFields[id]; |
598 | if ( !queryStr.isEmpty() ){ | 607 | if ( !queryStr.isEmpty() ){ |
599 | isAnyFieldSelected = true; | 608 | isAnyFieldSelected = true; |
600 | switch( id ){ | 609 | switch( id ){ |
601 | default: | 610 | default: |
602 | // Switching between case sensitive and insensitive... | 611 | // Switching between case sensitive and insensitive... |
603 | // LIKE is not case sensitive, GLOB is case sensitive | 612 | // LIKE is not case sensitive, GLOB is case sensitive |
604 | // Do exist a better solution to switch this ? | 613 | // Do exist a better solution to switch this ? |
605 | if ( settings & OContactAccess::IgnoreCase ) | 614 | if ( settings & OContactAccess::IgnoreCase ) |
606 | qu += "(\"" + *it + "\"" + " LIKE " + "'" | 615 | qu += "(\"" + *it + "\"" + " LIKE " + "'" |
607 | + queryStr.replace(QRegExp("\\*"),"%") + "'" + ") AND "; | 616 | + queryStr.replace(QRegExp("\\*"),"%") + "'" + ") AND "; |
608 | else | 617 | else |
609 | qu += "(\"" + *it + "\"" + " GLOB " + "'" | 618 | qu += "(\"" + *it + "\"" + " GLOB " + "'" |
610 | + queryStr + "'" + ") AND "; | 619 | + queryStr + "'" + ") AND "; |
611 | 620 | ||
612 | } | 621 | } |
613 | } | 622 | } |
614 | } | 623 | } |
615 | // Skip trailing "AND" | 624 | // Skip trailing "AND" |
616 | if ( isAnyFieldSelected ) | 625 | if ( isAnyFieldSelected ) |
617 | qu = qu.left( qu.length() - 4 ); | 626 | qu = qu.left( qu.length() - 4 ); |
618 | 627 | ||
619 | qWarning( "queryByExample query: %s", qu.latin1() ); | 628 | qWarning( "queryByExample query: %s", qu.latin1() ); |
620 | 629 | ||
621 | // Execute query and return the received uid's | 630 | // Execute query and return the received uid's |
622 | OSQLRawQuery raw( qu ); | 631 | OSQLRawQuery raw( qu ); |
623 | OSQLResult res = m_driver->query( &raw ); | 632 | OSQLResult res = m_driver->query( &raw ); |
624 | if ( res.state() != OSQLResult::Success ){ | 633 | if ( res.state() != OSQLResult::Success ){ |
625 | QArray<int> empty; | 634 | QArray<int> empty; |
626 | return empty; | 635 | return empty; |
627 | } | 636 | } |
628 | 637 | ||
629 | QArray<int> list = extractUids( res ); | 638 | QArray<int> list = extractUids( res ); |
630 | 639 | ||
631 | return list; | 640 | return list; |
632 | } | 641 | } |
633 | 642 | ||
634 | QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const | 643 | QArray<int> OContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const |
635 | { | 644 | { |
636 | QArray<int> nix(0); | 645 | QArray<int> nix(0); |
637 | return nix; | 646 | return nix; |
638 | } | 647 | } |
639 | 648 | ||
640 | const uint OContactAccessBackend_SQL::querySettings() | 649 | const uint OContactAccessBackend_SQL::querySettings() |
641 | { | 650 | { |
642 | return OContactAccess::IgnoreCase | 651 | return OContactAccess::IgnoreCase |
643 | || OContactAccess::WildCards; | 652 | || OContactAccess::WildCards; |
644 | } | 653 | } |
645 | 654 | ||
646 | bool OContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const | 655 | bool OContactAccessBackend_SQL::hasQuerySettings (uint querySettings) const |
647 | { | 656 | { |
648 | /* OContactAccess::IgnoreCase, DateDiff, DateYear, DateMonth, DateDay | 657 | /* OContactAccess::IgnoreCase, DateDiff, DateYear, DateMonth, DateDay |
649 | * may be added with any of the other settings. IgnoreCase should never used alone. | 658 | * 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... | 659 | * Wildcards, RegExp, ExactMatch should never used at the same time... |
651 | */ | 660 | */ |
652 | 661 | ||
653 | // Step 1: Check whether the given settings are supported by this backend | 662 | // Step 1: Check whether the given settings are supported by this backend |
654 | if ( ( querySettings & ( | 663 | if ( ( querySettings & ( |
655 | OContactAccess::IgnoreCase | 664 | OContactAccess::IgnoreCase |
656 | | OContactAccess::WildCards | 665 | | OContactAccess::WildCards |
657 | // | OContactAccess::DateDiff | 666 | // | OContactAccess::DateDiff |
658 | // | OContactAccess::DateYear | 667 | // | OContactAccess::DateYear |
659 | // | OContactAccess::DateMonth | 668 | // | OContactAccess::DateMonth |
660 | // | OContactAccess::DateDay | 669 | // | OContactAccess::DateDay |
661 | // | OContactAccess::RegExp | 670 | // | OContactAccess::RegExp |
662 | // | OContactAccess::ExactMatch | 671 | // | OContactAccess::ExactMatch |
663 | ) ) != querySettings ) | 672 | ) ) != querySettings ) |
664 | return false; | 673 | return false; |
665 | 674 | ||
666 | // Step 2: Check whether the given combinations are ok.. | 675 | // Step 2: Check whether the given combinations are ok.. |
667 | 676 | ||
668 | // IngoreCase alone is invalid | 677 | // IngoreCase alone is invalid |
669 | if ( querySettings == OContactAccess::IgnoreCase ) | 678 | if ( querySettings == OContactAccess::IgnoreCase ) |
670 | return false; | 679 | return false; |