author | eilers <eilers> | 2003-09-29 07:44:26 (UTC) |
---|---|---|
committer | eilers <eilers> | 2003-09-29 07:44:26 (UTC) |
commit | 36d6b0096c41b01e69bb0d12e6c29648cbbf8290 (patch) (unidiff) | |
tree | c87f4f92c4a1fbdf57e502a9c5e3e44fd9e98540 /libopie2/opiepim/backend/otodoaccesssql.cpp | |
parent | b2e22408970ef548e23e9bbdcd87302f35fc6d4d (diff) | |
download | opie-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 (limited to 'libopie2/opiepim/backend/otodoaccesssql.cpp') (more/less context) (show whitespace changes)
-rw-r--r-- | libopie2/opiepim/backend/otodoaccesssql.cpp | 90 |
1 files changed, 74 insertions, 16 deletions
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 | |||
@@ -109,62 +109,99 @@ namespace { | |||
109 | QString with()const; | 109 | QString with()const; |
110 | QString out()const; | 110 | QString out()const; |
111 | QDate m_start; | 111 | QDate m_start; |
112 | QDate m_end; | 112 | QDate m_end; |
113 | bool m_inc :1; | 113 | bool m_inc :1; |
114 | }; | 114 | }; |
115 | 115 | ||
116 | 116 | ||
117 | CreateQuery::CreateQuery() : OSQLQuery() {} | 117 | CreateQuery::CreateQuery() : OSQLQuery() {} |
118 | CreateQuery::~CreateQuery() {} | 118 | CreateQuery::~CreateQuery() {} |
119 | QString CreateQuery::query()const { | 119 | QString CreateQuery::query()const { |
120 | QString qu; | 120 | QString qu; |
121 | qu += "create table todolist( uid, categories, completed, progress, "; | 121 | qu += "create table todolist( uid PRIMARY KEY, categories, completed, "; |
122 | qu += "summary, DueDate, priority, description )"; | 122 | qu += "description, summary, priority, DueDate, progress , state, "; |
123 | qu += "Recurrence, notifiers, maintainer, startdate, completeddate)"; | ||
123 | return qu; | 124 | return qu; |
124 | } | 125 | } |
125 | 126 | ||
126 | LoadQuery::LoadQuery() : OSQLQuery() {} | 127 | LoadQuery::LoadQuery() : OSQLQuery() {} |
127 | LoadQuery::~LoadQuery() {} | 128 | LoadQuery::~LoadQuery() {} |
128 | QString LoadQuery::query()const { | 129 | QString LoadQuery::query()const { |
129 | QString qu; | 130 | QString qu; |
130 | qu += "select distinct uid from todolist"; | 131 | // We do not need "distinct" here. The primary key is always unique.. |
132 | //qu += "select distinct uid from todolist"; | ||
133 | qu += "select uid from todolist"; | ||
131 | 134 | ||
132 | return qu; | 135 | return qu; |
133 | } | 136 | } |
134 | 137 | ||
135 | InsertQuery::InsertQuery( const OTodo& todo ) | 138 | InsertQuery::InsertQuery( const OTodo& todo ) |
136 | : OSQLQuery(), m_todo( todo ) { | 139 | : OSQLQuery(), m_todo( todo ) { |
137 | } | 140 | } |
138 | InsertQuery::~InsertQuery() { | 141 | InsertQuery::~InsertQuery() { |
139 | } | 142 | } |
140 | /* | 143 | /* |
141 | * converts from a OTodo to a query | 144 | * converts from a OTodo to a query |
142 | * we leave out X-Ref + Alarms | 145 | * we leave out X-Ref + Alarms |
143 | */ | 146 | */ |
144 | QString InsertQuery::query()const{ | 147 | QString InsertQuery::query()const{ |
145 | 148 | ||
146 | int year, month, day; | 149 | int year, month, day; |
147 | year = month = day = 0; | 150 | year = month = day = 0; |
148 | if (m_todo.hasDueDate() ) { | 151 | if (m_todo.hasDueDate() ) { |
149 | QDate date = m_todo.dueDate(); | 152 | QDate date = m_todo.dueDate(); |
150 | year = date.year(); | 153 | year = date.year(); |
151 | month = date.month(); | 154 | month = date.month(); |
152 | day = date.day(); | 155 | day = date.day(); |
153 | } | 156 | } |
157 | int sYear = 0, sMonth = 0, sDay = 0; | ||
158 | if( m_todo.hasStartDate() ){ | ||
159 | QDate sDate = m_todo.startDate(); | ||
160 | sYear = sDate.year(); | ||
161 | sMonth= sDate.month(); | ||
162 | sDay = sDate.day(); | ||
163 | } | ||
164 | |||
165 | int eYear = 0, eMonth = 0, eDay = 0; | ||
166 | if( m_todo.hasCompletedDate() ){ | ||
167 | QDate eDate = m_todo.completedDate(); | ||
168 | eYear = eDate.year(); | ||
169 | eMonth= eDate.month(); | ||
170 | eDay = eDate.day(); | ||
171 | } | ||
154 | QString qu; | 172 | QString qu; |
155 | qu = "insert into todolist VALUES(" + QString::number( m_todo.uid() ) + ",'" + m_todo.idsToString( m_todo.categories() ) + "',"; | 173 | qu = "insert into todolist VALUES(" |
156 | qu += QString::number( m_todo.isCompleted() ) + "," + QString::number( m_todo.progress() ) + ","; | 174 | + QString::number( m_todo.uid() ) + "," |
157 | qu += "'"+m_todo.summary()+"','"+QString::number(year)+"-"+QString::number(month)+"-"+QString::number(day)+"',"; | 175 | + "'" + m_todo.idsToString( m_todo.categories() ) + "'" + "," |
158 | qu += QString::number(m_todo.priority() ) +",'" + m_todo.description() + "')"; | 176 | + QString::number( m_todo.isCompleted() ) + "," |
177 | + "'" + m_todo.description() + "'" + "," | ||
178 | + "'" + m_todo.summary() + "'" + "," | ||
179 | + QString::number(m_todo.priority() ) + "," | ||
180 | + "'" + QString::number(year) + "-" | ||
181 | + QString::number(month) | ||
182 | + "-" + QString::number( day ) + "'" + "," | ||
183 | + QString::number( m_todo.progress() ) + "," | ||
184 | + "''" + "," // state (conversion needed) | ||
185 | // + QString::number( m_todo.state() ) + "," | ||
186 | + "''" + "," // Recurrence (conversion needed) | ||
187 | + "''" + "," // Notifiers (conversion needed) | ||
188 | + "''" + "," // Maintainers (conversion needed) | ||
189 | + "'" + QString::number(sYear) + "-" | ||
190 | + QString::number(sMonth) | ||
191 | + "-" + QString::number(sDay) + "'" + "," | ||
192 | + "'" + QString::number(eYear) + "-" | ||
193 | + QString::number(eMonth) | ||
194 | + "-"+QString::number(eDay) + "'" | ||
195 | + ")"; | ||
159 | 196 | ||
160 | qWarning("add %s", qu.latin1() ); | 197 | qWarning("add %s", qu.latin1() ); |
161 | return qu; | 198 | return qu; |
162 | } | 199 | } |
163 | 200 | ||
164 | RemoveQuery::RemoveQuery(int uid ) | 201 | RemoveQuery::RemoveQuery(int uid ) |
165 | : OSQLQuery(), m_uid( uid ) {} | 202 | : OSQLQuery(), m_uid( uid ) {} |
166 | RemoveQuery::~RemoveQuery() {} | 203 | RemoveQuery::~RemoveQuery() {} |
167 | QString RemoveQuery::query()const { | 204 | QString RemoveQuery::query()const { |
168 | QString qu = "DELETE from todolist where uid = " + QString::number(m_uid); | 205 | QString qu = "DELETE from todolist where uid = " + QString::number(m_uid); |
169 | return qu; | 206 | return qu; |
170 | } | 207 | } |
@@ -183,31 +220,29 @@ namespace { | |||
183 | FindQuery::FindQuery(const QArray<int>& ints) | 220 | FindQuery::FindQuery(const QArray<int>& ints) |
184 | : OSQLQuery(), m_uids(ints){ | 221 | : OSQLQuery(), m_uids(ints){ |
185 | } | 222 | } |
186 | FindQuery::~FindQuery() { | 223 | FindQuery::~FindQuery() { |
187 | } | 224 | } |
188 | QString FindQuery::query()const{ | 225 | QString FindQuery::query()const{ |
189 | if (m_uids.count() == 0 ) | 226 | if (m_uids.count() == 0 ) |
190 | return single(); | 227 | return single(); |
191 | else | 228 | else |
192 | return multi(); | 229 | return multi(); |
193 | } | 230 | } |
194 | QString FindQuery::single()const{ | 231 | QString FindQuery::single()const{ |
195 | QString qu = "select uid, categories, completed, progress, summary, "; | 232 | QString qu = "select * from todolist where uid = " + QString::number(m_uid); |
196 | qu += "DueDate, priority, description from todolist where uid = " + QString::number(m_uid); | ||
197 | return qu; | 233 | return qu; |
198 | } | 234 | } |
199 | QString FindQuery::multi()const { | 235 | QString FindQuery::multi()const { |
200 | QString qu = "select uid, categories, completed, progress, summary, "; | 236 | QString qu = "select * from todolist where "; |
201 | qu += "DueDate, priority, description from todolist where "; | ||
202 | for (uint i = 0; i < m_uids.count(); i++ ) { | 237 | for (uint i = 0; i < m_uids.count(); i++ ) { |
203 | qu += " UID = " + QString::number( m_uids[i] ) + " OR"; | 238 | qu += " UID = " + QString::number( m_uids[i] ) + " OR"; |
204 | } | 239 | } |
205 | qu.remove( qu.length()-2, 2 ); | 240 | qu.remove( qu.length()-2, 2 ); |
206 | return qu; | 241 | return qu; |
207 | } | 242 | } |
208 | 243 | ||
209 | OverDueQuery::OverDueQuery(): OSQLQuery() {} | 244 | OverDueQuery::OverDueQuery(): OSQLQuery() {} |
210 | OverDueQuery::~OverDueQuery() {} | 245 | OverDueQuery::~OverDueQuery() {} |
211 | QString OverDueQuery::query()const { | 246 | QString OverDueQuery::query()const { |
212 | QDate date = QDate::currentDate(); | 247 | QDate date = QDate::currentDate(); |
213 | QString str; | 248 | QString str; |
@@ -279,25 +314,25 @@ QArray<int> OTodoAccessBackendSQL::allRecords()const { | |||
279 | } | 314 | } |
280 | QArray<int> OTodoAccessBackendSQL::queryByExample( const OTodo& , int, const QDateTime& ){ | 315 | QArray<int> OTodoAccessBackendSQL::queryByExample( const OTodo& , int, const QDateTime& ){ |
281 | QArray<int> ints(0); | 316 | QArray<int> ints(0); |
282 | return ints; | 317 | return ints; |
283 | } | 318 | } |
284 | OTodo OTodoAccessBackendSQL::find(int uid ) const{ | 319 | OTodo OTodoAccessBackendSQL::find(int uid ) const{ |
285 | FindQuery query( uid ); | 320 | FindQuery query( uid ); |
286 | return todo( m_driver->query(&query) ); | 321 | return todo( m_driver->query(&query) ); |
287 | 322 | ||
288 | } | 323 | } |
289 | OTodo OTodoAccessBackendSQL::find( int uid, const QArray<int>& ints, | 324 | OTodo OTodoAccessBackendSQL::find( int uid, const QArray<int>& ints, |
290 | uint cur, Frontend::CacheDirection dir ) const{ | 325 | uint cur, Frontend::CacheDirection dir ) const{ |
291 | int CACHE = readAhead(); | 326 | uint CACHE = readAhead(); |
292 | qWarning("searching for %d", uid ); | 327 | qWarning("searching for %d", uid ); |
293 | QArray<int> search( CACHE ); | 328 | QArray<int> search( CACHE ); |
294 | uint size =0; | 329 | uint size =0; |
295 | OTodo to; | 330 | OTodo to; |
296 | 331 | ||
297 | // we try to cache CACHE items | 332 | // we try to cache CACHE items |
298 | switch( dir ) { | 333 | switch( dir ) { |
299 | /* forward */ | 334 | /* forward */ |
300 | case 0: // FIXME: Not a good style to use magic numbers here (eilers) | 335 | case 0: // FIXME: Not a good style to use magic numbers here (eilers) |
301 | for (uint i = cur; i < ints.count() && size < CACHE; i++ ) { | 336 | for (uint i = cur; i < ints.count() && size < CACHE; i++ ) { |
302 | qWarning("size %d %d", size, ints[i] ); | 337 | qWarning("size %d %d", size, ints[i] ); |
303 | search[size] = ints[i]; | 338 | search[size] = ints[i]; |
@@ -463,32 +498,48 @@ OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{ | |||
463 | OTodo to = todo( (*it) ); | 498 | OTodo to = todo( (*it) ); |
464 | cache( to ); | 499 | cache( to ); |
465 | ++it; | 500 | ++it; |
466 | 501 | ||
467 | for ( ; it != list.end(); ++it ) { | 502 | for ( ; it != list.end(); ++it ) { |
468 | qWarning("caching"); | 503 | qWarning("caching"); |
469 | cache( todo( (*it) ) ); | 504 | cache( todo( (*it) ) ); |
470 | } | 505 | } |
471 | return to; | 506 | return to; |
472 | } | 507 | } |
473 | OTodo OTodoAccessBackendSQL::todo( OSQLResultItem& item )const { | 508 | OTodo 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 | } |
485 | OTodo OTodoAccessBackendSQL::todo( int uid )const { | 536 | OTodo OTodoAccessBackendSQL::todo( int uid )const { |
486 | FindQuery find( uid ); | 537 | FindQuery find( uid ); |
487 | return todo( m_driver->query(&find) ); | 538 | return todo( m_driver->query(&find) ); |
488 | } | 539 | } |
489 | /* | 540 | /* |
490 | * update the dict | 541 | * update the dict |
491 | */ | 542 | */ |
492 | void OTodoAccessBackendSQL::fillDict() { | 543 | void OTodoAccessBackendSQL::fillDict() { |
493 | /* initialize dict */ | 544 | /* initialize dict */ |
494 | /* | 545 | /* |
@@ -561,25 +612,32 @@ QArray<int> OTodoAccessBackendSQL::matchRegexp( const QRegExp &r ) const | |||
561 | 612 | ||
562 | } | 613 | } |
563 | // Shrink to fit.. | 614 | // Shrink to fit.. |
564 | m_currentQuery.resize(arraycounter); | 615 | m_currentQuery.resize(arraycounter); |
565 | 616 | ||
566 | return m_currentQuery; | 617 | return m_currentQuery; |
567 | #endif | 618 | #endif |
568 | QArray<int> empty; | 619 | QArray<int> empty; |
569 | return empty; | 620 | return empty; |
570 | } | 621 | } |
571 | QBitArray OTodoAccessBackendSQL::supports()const { | 622 | QBitArray OTodoAccessBackendSQL::supports()const { |
572 | 623 | ||
573 | static QBitArray ar = sup(); | 624 | QBitArray ar( OTodo::CompletedDate + 1 ); |
625 | ar.fill( true ); | ||
626 | ar[OTodo::CrossReference] = false; | ||
627 | ar[OTodo::State ] = false; | ||
628 | ar[OTodo::Reminders] = false; | ||
629 | ar[OTodo::Notifiers] = false; | ||
630 | ar[OTodo::Maintainer] = false; | ||
631 | |||
574 | return ar; | 632 | return ar; |
575 | } | 633 | } |
576 | 634 | ||
577 | QBitArray OTodoAccessBackendSQL::sup() { | 635 | QBitArray OTodoAccessBackendSQL::sup() { |
578 | 636 | ||
579 | QBitArray ar( OTodo::CompletedDate + 1 ); | 637 | QBitArray ar( OTodo::CompletedDate + 1 ); |
580 | ar.fill( true ); | 638 | ar.fill( true ); |
581 | ar[OTodo::CrossReference] = false; | 639 | ar[OTodo::CrossReference] = false; |
582 | ar[OTodo::State ] = false; | 640 | ar[OTodo::State ] = false; |
583 | ar[OTodo::Reminders] = false; | 641 | ar[OTodo::Reminders] = false; |
584 | ar[OTodo::Notifiers] = false; | 642 | ar[OTodo::Notifiers] = false; |
585 | ar[OTodo::Maintainer] = false; | 643 | ar[OTodo::Maintainer] = false; |