Diffstat (limited to 'libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp') (more/less context) (ignore whitespace changes)
-rw-r--r-- | libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp | 231 |
1 files changed, 183 insertions, 48 deletions
diff --git a/libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp b/libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp index 9769bf7..e893b38 100644 --- a/libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp +++ b/libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp | |||
@@ -13,8 +13,20 @@ | |||
13 | * Version: $Id$ | 13 | * Version: $Id$ |
14 | * ===================================================================== | 14 | * ===================================================================== |
15 | * History: | 15 | * History: |
16 | * $Log$ | 16 | * $Log$ |
17 | * Revision 1.2 2003/12/22 10:19:26 eilers | ||
18 | * Finishing implementation of sql-backend for datebook. But I have to | ||
19 | * port the PIM datebook application to use it, before I could debug the | ||
20 | * whole stuff. | ||
21 | * Thus, PIM-Database backend is finished, but highly experimental. And some | ||
22 | * parts are still generic. For instance, the "queryByExample()" methods are | ||
23 | * not (or not fully) implemented. Todo: custom-entries not stored. | ||
24 | * The big show stopper: matchRegExp() (needed by OpieSearch) needs regular | ||
25 | * expression search in the database, which is not supported by sqlite ! | ||
26 | * Therefore we need either an extended sqlite or a workaround which would | ||
27 | * be very slow and memory consuming.. | ||
28 | * | ||
17 | * Revision 1.1 2003/12/08 15:18:12 eilers | 29 | * Revision 1.1 2003/12/08 15:18:12 eilers |
18 | * Committing unfinished sql implementation before merging to libopie2 starts.. | 30 | * Committing unfinished sql implementation before merging to libopie2 starts.. |
19 | * | 31 | * |
20 | * | 32 | * |
@@ -25,27 +37,24 @@ | |||
25 | 37 | ||
26 | #include <qarray.h> | 38 | #include <qarray.h> |
27 | #include <qstringlist.h> | 39 | #include <qstringlist.h> |
28 | 40 | ||
29 | #include "orecur.h" | 41 | #include <qpe/global.h> |
30 | #include "odatebookaccessbackend_sql.h" | ||
31 | 42 | ||
32 | #include <opie2/osqldriver.h> | 43 | #include <opie2/osqldriver.h> |
33 | #include <opie2/osqlresult.h> | ||
34 | #include <opie2/osqlmanager.h> | 44 | #include <opie2/osqlmanager.h> |
35 | #include <opie2/osqlquery.h> | 45 | #include <opie2/osqlquery.h> |
36 | 46 | ||
37 | namespace { | 47 | #include "orecur.h" |
38 | 48 | #include "odatebookaccessbackend_sql.h" | |
39 | 49 | ||
40 | 50 | ||
41 | }; | ||
42 | 51 | ||
43 | ODateBookAccessBackend_SQL::ODateBookAccessBackend_SQL( const QString& , | 52 | ODateBookAccessBackend_SQL::ODateBookAccessBackend_SQL( const QString& , |
44 | const QString& fileName ) | 53 | const QString& fileName ) |
45 | : ODateBookAccessBackend(), m_driver( NULL ) | 54 | : ODateBookAccessBackend(), m_driver( NULL ) |
46 | { | 55 | { |
47 | m_fileName = fileName.isEmpty() ? Global::applicationFileName( "datebook", "datebook.db" ) : fileName; | 56 | m_fileName = fileName.isEmpty() ? Global::applicationFileName( "datebook", "datebook.db" ) : fileName; |
48 | 57 | ||
49 | // Get the standart sql-driver from the OSQLManager.. | 58 | // Get the standart sql-driver from the OSQLManager.. |
50 | OSQLManager man; | 59 | OSQLManager man; |
51 | m_driver = man.standard(); | 60 | m_driver = man.standard(); |
@@ -56,8 +65,10 @@ ODateBookAccessBackend_SQL::ODateBookAccessBackend_SQL( const QString& , | |||
56 | load(); | 65 | load(); |
57 | } | 66 | } |
58 | 67 | ||
59 | ODateBookAccessBackend_SQL::~ODateBookAccessBackend_SQL() { | 68 | ODateBookAccessBackend_SQL::~ODateBookAccessBackend_SQL() { |
69 | if( m_driver ) | ||
70 | delete m_driver; | ||
60 | } | 71 | } |
61 | 72 | ||
62 | void ODateBookAccessBackend_SQL::initFields() | 73 | void ODateBookAccessBackend_SQL::initFields() |
63 | { | 74 | { |
@@ -77,15 +88,22 @@ void ODateBookAccessBackend_SQL::initFields() | |||
77 | m_fieldMap.insert( OEvent::FRFreq, "RFreq" ); | 88 | m_fieldMap.insert( OEvent::FRFreq, "RFreq" ); |
78 | m_fieldMap.insert( OEvent::FRHasEndDate, "RHasEndDate" ); | 89 | m_fieldMap.insert( OEvent::FRHasEndDate, "RHasEndDate" ); |
79 | m_fieldMap.insert( OEvent::FREndDate, "REndDate" ); | 90 | m_fieldMap.insert( OEvent::FREndDate, "REndDate" ); |
80 | m_fieldMap.insert( OEvent::FRCreated, "RCreated" ); | 91 | m_fieldMap.insert( OEvent::FRCreated, "RCreated" ); |
81 | m_fieldMap.insert( OEvent::FRExeptions, "RExceptions" ); | 92 | m_fieldMap.insert( OEvent::FRExceptions, "RExceptions" ); |
82 | m_fieldMap.insert( OEvent::FStart, "Start" ); | 93 | m_fieldMap.insert( OEvent::FStart, "Start" ); |
83 | m_fieldMap.insert( OEvent::FEnd, "End" ); | 94 | m_fieldMap.insert( OEvent::FEnd, "End" ); |
84 | m_fieldMap.insert( OEvent::FNote, "Note" ); | 95 | m_fieldMap.insert( OEvent::FNote, "Note" ); |
85 | m_fieldMap.insert( OEvent::FTimeZone, "TimeZone" ); | 96 | m_fieldMap.insert( OEvent::FTimeZone, "TimeZone" ); |
86 | m_fieldMap.insert( OEvent::FRecParent, "RecParent" ); | 97 | m_fieldMap.insert( OEvent::FRecParent, "RecParent" ); |
87 | m_fieldMap.insert( OEvent::FRecChildren, "Recchildren" ); | 98 | m_fieldMap.insert( OEvent::FRecChildren, "Recchildren" ); |
99 | |||
100 | // Create a map that maps the column name to the id | ||
101 | QMapConstIterator<int, QString> it; | ||
102 | for ( it = ++m_fieldMap.begin(); it != m_fieldMap.end(); ++it ){ | ||
103 | m_reverseFieldMap.insert( it.data(), it.key() ); | ||
104 | } | ||
105 | |||
88 | } | 106 | } |
89 | 107 | ||
90 | bool ODateBookAccessBackend_SQL::load() | 108 | bool ODateBookAccessBackend_SQL::load() |
91 | { | 109 | { |
@@ -98,14 +116,16 @@ bool ODateBookAccessBackend_SQL::load() | |||
98 | QStringqu = "create table datebook( uid INTEGER PRIMARY KEY "; | 116 | QStringqu = "create table datebook( uid INTEGER PRIMARY KEY "; |
99 | 117 | ||
100 | QMap<int, QString>::Iterator it; | 118 | QMap<int, QString>::Iterator it; |
101 | for ( it = ++m_fieldMap.begin(); it != m_fieldMap.end(); ++it ){ | 119 | for ( it = ++m_fieldMap.begin(); it != m_fieldMap.end(); ++it ){ |
102 | qu += QString( ",\"%1\" VARCHAR(10)" ).arg( it.data() ); | 120 | qu += QString( ",%1 VARCHAR(10)" ).arg( it.data() ); |
103 | } | 121 | } |
104 | qu += " );"; | 122 | qu += " );"; |
105 | 123 | ||
106 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; | 124 | qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );"; |
107 | 125 | ||
126 | qWarning( "command: %s", qu.latin1() ); | ||
127 | |||
108 | OSQLRawQuery raw( qu ); | 128 | OSQLRawQuery raw( qu ); |
109 | OSQLResult res = m_driver->query( &raw ); | 129 | OSQLResult res = m_driver->query( &raw ); |
110 | if ( res.state() != OSQLResult::Success ) | 130 | if ( res.state() != OSQLResult::Success ) |
111 | return false; | 131 | return false; |
@@ -121,52 +141,33 @@ void ODateBookAccessBackend_SQL::update() | |||
121 | QString qu = "select uid from datebook"; | 141 | QString qu = "select uid from datebook"; |
122 | OSQLRawQuery raw( qu ); | 142 | OSQLRawQuery raw( qu ); |
123 | OSQLResult res = m_driver->query( &raw ); | 143 | OSQLResult res = m_driver->query( &raw ); |
124 | if ( res.state() != OSQLResult::Success ){ | 144 | if ( res.state() != OSQLResult::Success ){ |
125 | m_uids.clear(); | 145 | // m_uids.clear(); |
126 | return; | 146 | return; |
127 | } | 147 | } |
128 | 148 | ||
129 | m_uids = extractUids( res ); | 149 | m_uids = extractUids( res ); |
130 | 150 | ||
131 | } | 151 | } |
132 | 152 | ||
133 | QArray<int> ODateBookAccessBackend_SQL::extractUids( OSQLResult& res ) const | ||
134 | { | ||
135 | qWarning("extractUids"); | ||
136 | |||
137 | OSQLResultItem::ValueList list = res.results(); | ||
138 | OSQLResultItem::ValueList::Iterator it; | ||
139 | QArray<int> ints(list.count() ); | ||
140 | qWarning(" count = %d", list.count() ); | ||
141 | |||
142 | int i = 0; | ||
143 | for (it = list.begin(); it != list.end(); ++it ) { | ||
144 | ints[i] = (*it).data("uid").toInt(); | ||
145 | i++; | ||
146 | } | ||
147 | |||
148 | return ints; | ||
149 | |||
150 | } | ||
151 | |||
152 | bool ODateBookAccessBackend_SQL::reload() | 153 | bool ODateBookAccessBackend_SQL::reload() |
153 | { | 154 | { |
154 | return load(); | 155 | return load(); |
155 | } | 156 | } |
156 | 157 | ||
157 | bool ODateBookAccessBackend_SQL::save() | 158 | bool ODateBookAccessBackend_SQL::save() |
158 | { | 159 | { |
159 | return m_driver->close(); | 160 | return m_driver->close(); // Shouldn't m_driver->sync be better than close ? (eilers) |
160 | } | 161 | } |
161 | 162 | ||
162 | QArray<int> ODateBookAccessBackend_SQL::allRecords()const | 163 | QArray<int> ODateBookAccessBackend_SQL::allRecords()const |
163 | { | 164 | { |
164 | return m_uids; | 165 | return m_uids; |
165 | } | 166 | } |
166 | 167 | ||
167 | QArray<int> ODateBookAccessBackend_SQL::queryByExample(const OEvent&, int, const QDateTime& ) { | 168 | QArray<int> ODateBookAccessBackend_SQL::queryByExample(const OEvent&, int, const QDateTime& ) { |
168 | return QArray<int>(); | 169 | return QArray<int>(); |
169 | } | 170 | } |
170 | 171 | ||
171 | void ODateBookAccessBackend_SQL::clear() | 172 | void ODateBookAccessBackend_SQL::clear() |
172 | { | 173 | { |
@@ -175,47 +176,181 @@ void ODateBookAccessBackend_SQL::clear() | |||
175 | 176 | ||
176 | OSQLRawQuery raw( qu ); | 177 | OSQLRawQuery raw( qu ); |
177 | OSQLResult res = m_driver->query( &raw ); | 178 | OSQLResult res = m_driver->query( &raw ); |
178 | 179 | ||
180 | reload(); | ||
179 | } | 181 | } |
180 | 182 | ||
181 | 183 | ||
182 | OEvent ODateBookAccessBackend_SQL::find( int uid ) const{ | 184 | OEvent ODateBookAccessBackend_SQL::find( int uid ) const{ |
185 | QString qu = "select *"; | ||
186 | qu += "from datebook where uid = " + QString::number(uid); | ||
187 | |||
188 | OSQLRawQuery raw( qu ); | ||
189 | OSQLResult res = m_driver->query( &raw ); | ||
190 | |||
191 | OSQLResultItem resItem = res.first(); | ||
192 | |||
193 | // Create Map for date event and insert UID | ||
194 | QMap<int,QString> dateEventMap; | ||
195 | dateEventMap.insert( OEvent::FUid, QString::number( uid ) ); | ||
196 | |||
197 | // Now insert the data out of the columns into the map. | ||
198 | QMapConstIterator<int, QString> it; | ||
199 | for ( it = ++m_fieldMap.begin(); it != m_fieldMap.end(); ++it ){ | ||
200 | dateEventMap.insert( m_reverseFieldMap[*it], resItem.data( *it ) ); | ||
201 | } | ||
202 | |||
203 | // Last step: Put map into date event and return it | ||
204 | OEvent retDate( dateEventMap ); | ||
205 | |||
206 | return retDate; | ||
183 | } | 207 | } |
184 | 208 | ||
185 | bool ODateBookAccessBackend_SQL::add( const OEvent& ev ) { | 209 | bool ODateBookAccessBackend_SQL::add( const OEvent& ev ) |
186 | return true; | 210 | { |
211 | QMap<int,QString> eventMap = ev.toMap(); | ||
212 | |||
213 | QString qu = "insert into datebook VALUES( " + QString::number( ev.uid() ); | ||
214 | QMap<int, QString>::Iterator it; | ||
215 | for ( it = ++m_fieldMap.begin(); it != m_fieldMap.end(); ++it ){ | ||
216 | if ( !eventMap[it.key()].isEmpty() ) | ||
217 | qu += QString( ",\"%1\"" ).arg( eventMap[it.key()] ); | ||
218 | else | ||
219 | qu += QString( ",\"\"" ); | ||
220 | } | ||
221 | qu += " );"; | ||
222 | |||
223 | // Add custom entries | ||
224 | int id = 0; | ||
225 | QMap<QString, QString> customMap = ev.toExtraMap(); | ||
226 | for( QMap<QString, QString>::Iterator it = customMap.begin(); | ||
227 | it != customMap.end(); ++it ){ | ||
228 | qu += "insert into custom_data VALUES(" | ||
229 | + QString::number( ev.uid() ) | ||
230 | + "," | ||
231 | + QString::number( id++ ) | ||
232 | + ",'" | ||
233 | + it.key() //.latin1() | ||
234 | + "'," | ||
235 | + "0" // Priority for future enhancements | ||
236 | + ",'" | ||
237 | + it.data() //.latin1() | ||
238 | + "');"; | ||
239 | } | ||
240 | qWarning("add %s", qu.latin1() ); | ||
241 | |||
242 | OSQLRawQuery raw( qu ); | ||
243 | OSQLResult res = m_driver->query( &raw ); | ||
244 | if ( res.state() != OSQLResult::Success ){ | ||
245 | return false; | ||
246 | } | ||
247 | |||
248 | return true; | ||
187 | } | 249 | } |
188 | bool ODateBookAccessBackend_SQL::remove( int uid ) { | ||
189 | 250 | ||
190 | return true; | 251 | bool ODateBookAccessBackend_SQL::remove( int uid ) |
252 | { | ||
253 | QString qu = "DELETE from datebook where uid = " | ||
254 | + QString::number( uid ) + ";"; | ||
255 | qu += "DELETE from custom_data where uid = " | ||
256 | + QString::number( uid ) + ";"; | ||
257 | |||
258 | OSQLRawQuery raw( qu ); | ||
259 | OSQLResult res = m_driver->query( &raw ); | ||
260 | if ( res.state() != OSQLResult::Success ){ | ||
261 | return false; | ||
262 | } | ||
263 | |||
264 | return true; | ||
191 | } | 265 | } |
192 | bool ODateBookAccessBackend_SQL::replace( const OEvent& ev ) { | 266 | |
267 | bool ODateBookAccessBackend_SQL::replace( const OEvent& ev ) | ||
268 | { | ||
193 | remove( ev.uid() ); | 269 | remove( ev.uid() ); |
194 | return add( ev ); | 270 | return add( ev ); |
195 | } | 271 | } |
196 | QArray<int> ODateBookAccessBackend_SQL::rawEvents()const { | 272 | |
273 | QArray<int> ODateBookAccessBackend_SQL::rawEvents()const | ||
274 | { | ||
197 | return allRecords(); | 275 | return allRecords(); |
198 | } | 276 | } |
199 | QArray<int> ODateBookAccessBackend_SQL::rawRepeats()const { | ||
200 | 277 | ||
201 | return ints; | 278 | QArray<int> ODateBookAccessBackend_SQL::rawRepeats()const |
279 | { | ||
280 | QString qu = "select uid from datebook where RType!=\"\" AND RType!=\"NoRepeat\""; | ||
281 | OSQLRawQuery raw( qu ); | ||
282 | OSQLResult res = m_driver->query( &raw ); | ||
283 | if ( res.state() != OSQLResult::Success ){ | ||
284 | QArray<int> nix; | ||
285 | return nix; | ||
286 | } | ||
287 | |||
288 | return extractUids( res ); | ||
202 | } | 289 | } |
203 | QArray<int> ODateBookAccessBackend_SQL::nonRepeats()const { | ||
204 | 290 | ||
205 | return ints; | 291 | QArray<int> ODateBookAccessBackend_SQL::nonRepeats()const |
292 | { | ||
293 | QString qu = "select uid from datebook where RType=\"\" or RType=\"NoRepeat\""; | ||
294 | OSQLRawQuery raw( qu ); | ||
295 | OSQLResult res = m_driver->query( &raw ); | ||
296 | if ( res.state() != OSQLResult::Success ){ | ||
297 | QArray<int> nix; | ||
298 | return nix; | ||
299 | } | ||
300 | |||
301 | return extractUids( res ); | ||
206 | } | 302 | } |
207 | OEvent::ValueList ODateBookAccessBackend_SQL::directNonRepeats() { | ||
208 | 303 | ||
209 | return list; | 304 | OEvent::ValueList ODateBookAccessBackend_SQL::directNonRepeats() |
305 | { | ||
306 | QArray<int> nonRepUids = nonRepeats(); | ||
307 | OEvent::ValueList list; | ||
308 | |||
309 | for (uint i = 0; i < nonRepUids.count(); ++i ){ | ||
310 | list.append( find( nonRepUids[i] ) ); | ||
311 | } | ||
312 | |||
313 | return list; | ||
314 | |||
210 | } | 315 | } |
211 | OEvent::ValueList ODateBookAccessBackend_SQL::directRawRepeats() { | 316 | OEvent::ValueList ODateBookAccessBackend_SQL::directRawRepeats() |
317 | { | ||
318 | QArray<int> rawRepUids = rawRepeats(); | ||
319 | OEvent::ValueList list; | ||
212 | 320 | ||
213 | return list; | 321 | for (uint i = 0; i < rawRepUids.count(); ++i ){ |
322 | list.append( find( rawRepUids[i] ) ); | ||
323 | } | ||
324 | |||
325 | return list; | ||
214 | } | 326 | } |
215 | 327 | ||
216 | 328 | ||
217 | QArray<int> ODateBookAccessBackend_SQL::matchRegexp( const QRegExp &r ) const | 329 | QArray<int> ODateBookAccessBackend_SQL::matchRegexp( const QRegExp &r ) const |
218 | { | 330 | { |
331 | QArray<int> null; | ||
332 | return null; | ||
333 | } | ||
334 | |||
335 | /* ===== Private Functions ========================================== */ | ||
336 | |||
337 | QArray<int> ODateBookAccessBackend_SQL::extractUids( OSQLResult& res ) const | ||
338 | { | ||
339 | qWarning("extractUids"); | ||
340 | QTime t; | ||
341 | t.start(); | ||
342 | OSQLResultItem::ValueList list = res.results(); | ||
343 | OSQLResultItem::ValueList::Iterator it; | ||
344 | QArray<int> ints(list.count() ); | ||
345 | qWarning(" count = %d", list.count() ); | ||
346 | |||
347 | int i = 0; | ||
348 | for (it = list.begin(); it != list.end(); ++it ) { | ||
349 | ints[i] = (*it).data("uid").toInt(); | ||
350 | i++; | ||
351 | } | ||
352 | qWarning("extractUids ready: count2 = %d needs %d ms", i, t.elapsed() ); | ||
353 | |||
354 | return ints; | ||
219 | 355 | ||
220 | return m_currentQuery; | ||
221 | } | 356 | } |