summaryrefslogtreecommitdiff
path: root/libopie/pim/otodoaccesssql.cpp
Unidiff
Diffstat (limited to 'libopie/pim/otodoaccesssql.cpp') (more/less context) (ignore whitespace changes)
-rw-r--r--libopie/pim/otodoaccesssql.cpp694
1 files changed, 0 insertions, 694 deletions
diff --git a/libopie/pim/otodoaccesssql.cpp b/libopie/pim/otodoaccesssql.cpp
deleted file mode 100644
index fd01a42..0000000
--- a/libopie/pim/otodoaccesssql.cpp
+++ b/dev/null
@@ -1,694 +0,0 @@
1
2#include <qdatetime.h>
3
4#include <qpe/global.h>
5
6#include <opie2/osqldriver.h>
7#include <opie2/osqlresult.h>
8#include <opie2/osqlmanager.h>
9#include <opie2/osqlquery.h>
10
11#include "otodoaccesssql.h"
12#include "opimstate.h"
13#include "opimnotifymanager.h"
14#include "orecur.h"
15
16using namespace Opie::DB;
17/*
18 * first some query
19 * CREATE query
20 * LOAD query
21 * INSERT
22 * REMOVE
23 * CLEAR
24 */
25namespace {
26 /**
27 * CreateQuery for the Todolist Table
28 */
29 class CreateQuery : public OSQLQuery {
30 public:
31 CreateQuery();
32 ~CreateQuery();
33 QString query()const;
34 };
35
36 /**
37 * LoadQuery
38 * this one queries for all uids
39 */
40 class LoadQuery : public OSQLQuery {
41 public:
42 LoadQuery();
43 ~LoadQuery();
44 QString query()const;
45 };
46
47 /**
48 * inserts/adds a OTodo to the table
49 */
50 class InsertQuery : public OSQLQuery {
51 public:
52 InsertQuery(const OTodo& );
53 ~InsertQuery();
54 QString query()const;
55 private:
56 OTodo m_todo;
57 };
58
59 /**
60 * removes one from the table
61 */
62 class RemoveQuery : public OSQLQuery {
63 public:
64 RemoveQuery(int uid );
65 ~RemoveQuery();
66 QString query()const;
67 private:
68 int m_uid;
69 };
70
71 /**
72 * Clears (delete) a Table
73 */
74 class ClearQuery : public OSQLQuery {
75 public:
76 ClearQuery();
77 ~ClearQuery();
78 QString query()const;
79
80 };
81
82 /**
83 * a find query
84 */
85 class FindQuery : public OSQLQuery {
86 public:
87 FindQuery(int uid);
88 FindQuery(const QArray<int>& );
89 ~FindQuery();
90 QString query()const;
91 private:
92 QString single()const;
93 QString multi()const;
94 QArray<int> m_uids;
95 int m_uid;
96 };
97
98 /**
99 * overdue query
100 */
101 class OverDueQuery : public OSQLQuery {
102 public:
103 OverDueQuery();
104 ~OverDueQuery();
105 QString query()const;
106 };
107 class EffQuery : public OSQLQuery {
108 public:
109 EffQuery( const QDate&, const QDate&, bool inc );
110 ~EffQuery();
111 QString query()const;
112 private:
113 QString with()const;
114 QString out()const;
115 QDate m_start;
116 QDate m_end;
117 bool m_inc :1;
118 };
119
120
121 CreateQuery::CreateQuery() : OSQLQuery() {}
122 CreateQuery::~CreateQuery() {}
123 QString CreateQuery::query()const {
124 QString qu;
125 qu += "create table todolist( uid PRIMARY KEY, categories, completed, ";
126 qu += "description, summary, priority, DueDate, progress , state, ";
127 // This is the recurrance-stuff .. Exceptions are currently not supported (see ORecur.cpp) ! (eilers)
128 qu += "RType, RWeekdays, RPosition, RFreq, RHasEndDate, EndDate, Created, Exceptions, ";
129 qu += "reminders, alarms, maintainer, startdate, completeddate);";
130 qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR(10), value VARCHAR(10), PRIMARY KEY /* identifier */ (uid, id) );";
131 return qu;
132 }
133
134 LoadQuery::LoadQuery() : OSQLQuery() {}
135 LoadQuery::~LoadQuery() {}
136 QString LoadQuery::query()const {
137 QString qu;
138 // We do not need "distinct" here. The primary key is always unique..
139 //qu += "select distinct uid from todolist";
140 qu += "select uid from todolist";
141
142 return qu;
143 }
144
145 InsertQuery::InsertQuery( const OTodo& todo )
146 : OSQLQuery(), m_todo( todo ) {
147 }
148 InsertQuery::~InsertQuery() {
149 }
150 /*
151 * converts from a OTodo to a query
152 * we leave out X-Ref + Alarms
153 */
154 QString InsertQuery::query()const{
155
156 int year, month, day;
157 year = month = day = 0;
158 if (m_todo.hasDueDate() ) {
159 QDate date = m_todo.dueDate();
160 year = date.year();
161 month = date.month();
162 day = date.day();
163 }
164 int sYear = 0, sMonth = 0, sDay = 0;
165 if( m_todo.hasStartDate() ){
166 QDate sDate = m_todo.startDate();
167 sYear = sDate.year();
168 sMonth= sDate.month();
169 sDay = sDate.day();
170 }
171
172 int eYear = 0, eMonth = 0, eDay = 0;
173 if( m_todo.hasCompletedDate() ){
174 QDate eDate = m_todo.completedDate();
175 eYear = eDate.year();
176 eMonth= eDate.month();
177 eDay = eDate.day();
178 }
179 QString qu;
180 QMap<int, QString> recMap = m_todo.recurrence().toMap();
181 qu = "insert into todolist VALUES("
182 + QString::number( m_todo.uid() ) + ","
183 + "'" + m_todo.idsToString( m_todo.categories() ) + "'" + ","
184 + QString::number( m_todo.isCompleted() ) + ","
185 + "'" + m_todo.description() + "'" + ","
186 + "'" + m_todo.summary() + "'" + ","
187 + QString::number(m_todo.priority() ) + ","
188 + "'" + QString::number(year) + "-"
189 + QString::number(month)
190 + "-" + QString::number( day ) + "'" + ","
191 + QString::number( m_todo.progress() ) + ","
192 + QString::number( m_todo.state().state() ) + ","
193 + "'" + recMap[ ORecur::RType ] + "'" + ","
194 + "'" + recMap[ ORecur::RWeekdays ] + "'" + ","
195 + "'" + recMap[ ORecur::RPosition ] + "'" + ","
196 + "'" + recMap[ ORecur::RFreq ] + "'" + ","
197 + "'" + recMap[ ORecur::RHasEndDate ] + "'" + ","
198 + "'" + recMap[ ORecur::EndDate ] + "'" + ","
199 + "'" + recMap[ ORecur::Created ] + "'" + ","
200 + "'" + recMap[ ORecur::Exceptions ] + "'" + ",";
201
202 if ( m_todo.hasNotifiers() ) {
203 OPimNotifyManager manager = m_todo.notifiers();
204 qu += "'" + manager.remindersToString() + "'" + ","
205 + "'" + manager.alarmsToString() + "'" + ",";
206 }
207 else{
208 qu += QString( "''" ) + ","
209 + "''" + ",";
210 }
211
212 qu += QString( "''" ) + QString( "," ) // Maintainers (cur. not supported !)
213 + "'" + QString::number(sYear) + "-"
214 + QString::number(sMonth)
215 + "-" + QString::number(sDay) + "'" + ","
216 + "'" + QString::number(eYear) + "-"
217 + QString::number(eMonth)
218 + "-"+QString::number(eDay) + "'"
219 + ")";
220
221 qWarning("add %s", qu.latin1() );
222 return qu;
223 }
224
225 RemoveQuery::RemoveQuery(int uid )
226 : OSQLQuery(), m_uid( uid ) {}
227 RemoveQuery::~RemoveQuery() {}
228 QString RemoveQuery::query()const {
229 QString qu = "DELETE from todolist where uid = " + QString::number(m_uid);
230 return qu;
231 }
232
233
234 ClearQuery::ClearQuery()
235 : OSQLQuery() {}
236 ClearQuery::~ClearQuery() {}
237 QString ClearQuery::query()const {
238 QString qu = "drop table todolist";
239 return qu;
240 }
241 FindQuery::FindQuery(int uid)
242 : OSQLQuery(), m_uid(uid ) {
243 }
244 FindQuery::FindQuery(const QArray<int>& ints)
245 : OSQLQuery(), m_uids(ints){
246 }
247 FindQuery::~FindQuery() {
248 }
249 QString FindQuery::query()const{
250 if (m_uids.count() == 0 )
251 return single();
252 else
253 return multi();
254 }
255 QString FindQuery::single()const{
256 QString qu = "select * from todolist where uid = " + QString::number(m_uid);
257 return qu;
258 }
259 QString FindQuery::multi()const {
260 QString qu = "select * from todolist where ";
261 for (uint i = 0; i < m_uids.count(); i++ ) {
262 qu += " UID = " + QString::number( m_uids[i] ) + " OR";
263 }
264 qu.remove( qu.length()-2, 2 );
265 return qu;
266 }
267
268 OverDueQuery::OverDueQuery(): OSQLQuery() {}
269 OverDueQuery::~OverDueQuery() {}
270 QString OverDueQuery::query()const {
271 QDate date = QDate::currentDate();
272 QString str;
273 str = QString("select uid from todolist where DueDate ='%1-%2-%3'").arg(date.year() ).arg(date.month() ).arg(date.day() );
274
275 return str;
276 }
277
278
279 EffQuery::EffQuery( const QDate& start, const QDate& end, bool inc )
280 : OSQLQuery(), m_start( start ), m_end( end ),m_inc(inc) {}
281 EffQuery::~EffQuery() {}
282 QString EffQuery::query()const {
283 return m_inc ? with() : out();
284 }
285 QString EffQuery::with()const {
286 QString str;
287 str = QString("select uid from todolist where ( DueDate >= '%1-%2-%3' AND DueDate <= '%4-%5-%6' ) OR DueDate = '0-0-0' ")
288 .arg( m_start.year() ).arg( m_start.month() ).arg( m_start.day() )
289 .arg( m_end .year() ).arg( m_end .month() ).arg( m_end .day() );
290 return str;
291 }
292 QString EffQuery::out()const {
293 QString str;
294 str = QString("select uid from todolist where DueDate >= '%1-%2-%3' AND DueDate <= '%4-%5-%6'")
295 .arg(m_start.year() ).arg(m_start.month() ).arg( m_start.day() )
296 .arg(m_end. year() ).arg(m_end. month() ).arg(m_end.day() );
297
298 return str;
299 }
300};
301
302OTodoAccessBackendSQL::OTodoAccessBackendSQL( const QString& file )
303 : OTodoAccessBackend(), m_dict(15), m_driver(NULL), m_dirty(true)
304{
305 QString fi = file;
306 if ( fi.isEmpty() )
307 fi = Global::applicationFileName( "todolist", "todolist.db" );
308 OSQLManager man;
309 m_driver = man.standard();
310 m_driver->setUrl(fi);
311 // fillDict();
312}
313
314OTodoAccessBackendSQL::~OTodoAccessBackendSQL(){
315 if( m_driver )
316 delete m_driver;
317}
318
319bool OTodoAccessBackendSQL::load(){
320 if (!m_driver->open() )
321 return false;
322
323 CreateQuery creat;
324 OSQLResult res = m_driver->query(&creat );
325
326 m_dirty = true;
327 return true;
328}
329bool OTodoAccessBackendSQL::reload(){
330 return load();
331}
332
333bool OTodoAccessBackendSQL::save(){
334 return m_driver->close(); // Shouldn't m_driver->sync be better than close ? (eilers)
335}
336QArray<int> OTodoAccessBackendSQL::allRecords()const {
337 if (m_dirty )
338 update();
339
340 return m_uids;
341}
342QArray<int> OTodoAccessBackendSQL::queryByExample( const OTodo& , int, const QDateTime& ){
343 QArray<int> ints(0);
344 return ints;
345}
346OTodo OTodoAccessBackendSQL::find(int uid ) const{
347 FindQuery query( uid );
348 return todo( m_driver->query(&query) );
349
350}
351OTodo OTodoAccessBackendSQL::find( int uid, const QArray<int>& ints,
352 uint cur, Frontend::CacheDirection dir ) const{
353 uint CACHE = readAhead();
354 qWarning("searching for %d", uid );
355 QArray<int> search( CACHE );
356 uint size =0;
357 OTodo to;
358
359 // we try to cache CACHE items
360 switch( dir ) {
361 /* forward */
362 case 0: // FIXME: Not a good style to use magic numbers here (eilers)
363 for (uint i = cur; i < ints.count() && size < CACHE; i++ ) {
364 qWarning("size %d %d", size, ints[i] );
365 search[size] = ints[i];
366 size++;
367 }
368 break;
369 /* reverse */
370 case 1: // FIXME: Not a good style to use magic numbers here (eilers)
371 for (uint i = cur; i != 0 && size < CACHE; i-- ) {
372 search[size] = ints[i];
373 size++;
374 }
375 break;
376 }
377 search.resize( size );
378 FindQuery query( search );
379 OSQLResult res = m_driver->query( &query );
380 if ( res.state() != OSQLResult::Success )
381 return to;
382
383 return todo( res );
384}
385void OTodoAccessBackendSQL::clear() {
386 ClearQuery cle;
387 OSQLResult res = m_driver->query( &cle );
388 CreateQuery qu;
389 res = m_driver->query(&qu);
390}
391bool OTodoAccessBackendSQL::add( const OTodo& t) {
392 InsertQuery ins( t );
393 OSQLResult res = m_driver->query( &ins );
394
395 if ( res.state() == OSQLResult::Failure )
396 return false;
397 int c = m_uids.count();
398 m_uids.resize( c+1 );
399 m_uids[c] = t.uid();
400
401 return true;
402}
403bool OTodoAccessBackendSQL::remove( int uid ) {
404 RemoveQuery rem( uid );
405 OSQLResult res = m_driver->query(&rem );
406
407 if ( res.state() == OSQLResult::Failure )
408 return false;
409
410 m_dirty = true;
411 return true;
412}
413/*
414 * FIXME better set query
415 * but we need the cache for that
416 * now we remove
417 */
418bool OTodoAccessBackendSQL::replace( const OTodo& t) {
419 remove( t.uid() );
420 bool b= add(t);
421 m_dirty = false; // we changed some stuff but the UID stayed the same
422 return b;
423}
424QArray<int> OTodoAccessBackendSQL::overDue() {
425 OverDueQuery qu;
426 return uids( m_driver->query(&qu ) );
427}
428QArray<int> OTodoAccessBackendSQL::effectiveToDos( const QDate& s,
429 const QDate& t,
430 bool u) {
431 EffQuery ef(s, t, u );
432 return uids (m_driver->query(&ef) );
433}
434/*
435 *
436 */
437QArray<int> OTodoAccessBackendSQL::sorted( bool asc, int sortOrder,
438 int sortFilter, int cat ) {
439 qWarning("sorted %d, %d", asc, sortOrder );
440 QString query;
441 query = "select uid from todolist WHERE ";
442
443 /*
444 * Sort Filter stuff
445 * not that straight forward
446 * FIXME: Replace magic numbers
447 *
448 */
449 /* Category */
450 if ( sortFilter & 1 ) {
451 QString str;
452 if (cat != 0 ) str = QString::number( cat );
453 query += " categories like '%" +str+"%' AND";
454 }
455 /* Show only overdue */
456 if ( sortFilter & 2 ) {
457 QDate date = QDate::currentDate();
458 QString due;
459 QString base;
460 base = QString("DueDate <= '%1-%2-%3' AND completed = 0").arg( date.year() ).arg( date.month() ).arg( date.day() );
461 query += " " + base + " AND";
462 }
463 /* not show completed */
464 if ( sortFilter & 4 ) {
465 query += " completed = 0 AND";
466 }else{
467 query += " ( completed = 1 OR completed = 0) AND";
468 }
469 /* srtip the end */
470 query = query.remove( query.length()-3, 3 );
471
472
473 /*
474 * sort order stuff
475 * quite straight forward
476 */
477 query += "ORDER BY ";
478 switch( sortOrder ) {
479 /* completed */
480 case 0:
481 query += "completed";
482 break;
483 case 1:
484 query += "priority";
485 break;
486 case 2:
487 query += "summary";
488 break;
489 case 3:
490 query += "DueDate";
491 break;
492 }
493
494 if ( !asc ) {
495 qWarning("not ascending!");
496 query += " DESC";
497 }
498
499 qWarning( query );
500 OSQLRawQuery raw(query );
501 return uids( m_driver->query(&raw) );
502}
503bool OTodoAccessBackendSQL::date( QDate& da, const QString& str ) const{
504 if ( str == "0-0-0" )
505 return false;
506 else{
507 int day, year, month;
508 QStringList list = QStringList::split("-", str );
509 year = list[0].toInt();
510 month = list[1].toInt();
511 day = list[2].toInt();
512 da.setYMD( year, month, day );
513 return true;
514 }
515}
516OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{
517 if ( res.state() == OSQLResult::Failure ) {
518 OTodo to;
519 return to;
520 }
521
522 OSQLResultItem::ValueList list = res.results();
523 OSQLResultItem::ValueList::Iterator it = list.begin();
524 qWarning("todo1");
525 OTodo to = todo( (*it) );
526 cache( to );
527 ++it;
528
529 for ( ; it != list.end(); ++it ) {
530 qWarning("caching");
531 cache( todo( (*it) ) );
532 }
533 return to;
534}
535OTodo OTodoAccessBackendSQL::todo( OSQLResultItem& item )const {
536 qWarning("todo");
537 bool hasDueDate = false; QDate dueDate = QDate::currentDate();
538 hasDueDate = date( dueDate, item.data("DueDate") );
539 QStringList cats = QStringList::split(";", item.data("categories") );
540
541 qWarning("Item is completed: %d", item.data("completed").toInt() );
542
543 OTodo to( (bool)item.data("completed").toInt(), item.data("priority").toInt(),
544 cats, item.data("summary"), item.data("description"),
545 item.data("progress").toUShort(), hasDueDate, dueDate,
546 item.data("uid").toInt() );
547
548 bool isOk;
549 int prioInt = QString( item.data("priority") ).toInt( &isOk );
550 if ( isOk )
551 to.setPriority( prioInt );
552
553 bool hasStartDate = false; QDate startDate = QDate::currentDate();
554 hasStartDate = date( startDate, item.data("startdate") );
555 bool hasCompletedDate = false; QDate completedDate = QDate::currentDate();
556 hasCompletedDate = date( completedDate, item.data("completeddate") );
557
558 if ( hasStartDate )
559 to.setStartDate( startDate );
560 if ( hasCompletedDate )
561 to.setCompletedDate( completedDate );
562
563 OPimNotifyManager& manager = to.notifiers();
564 manager.alarmsFromString( item.data("alarms") );
565 manager.remindersFromString( item.data("reminders") );
566
567 OPimState pimState;
568 pimState.setState( QString( item.data("state") ).toInt() );
569 to.setState( pimState );
570
571 QMap<int, QString> recMap;
572 recMap.insert( ORecur::RType , item.data("RType") );
573 recMap.insert( ORecur::RWeekdays , item.data("RWeekdays") );
574 recMap.insert( ORecur::RPosition , item.data("RPosition") );
575 recMap.insert( ORecur::RFreq , item.data("RFreq") );
576 recMap.insert( ORecur::RHasEndDate, item.data("RHasEndDate") );
577 recMap.insert( ORecur::EndDate , item.data("EndDate") );
578 recMap.insert( ORecur::Created , item.data("Created") );
579 recMap.insert( ORecur::Exceptions , item.data("Exceptions") );
580
581 ORecur recur;
582 recur.fromMap( recMap );
583 to.setRecurrence( recur );
584
585 return to;
586}
587OTodo OTodoAccessBackendSQL::todo( int uid )const {
588 FindQuery find( uid );
589 return todo( m_driver->query(&find) );
590}
591/*
592 * update the dict
593 */
594void OTodoAccessBackendSQL::fillDict() {
595 /* initialize dict */
596 /*
597 * UPDATE dict if you change anything!!!
598 * FIXME: Isn't this dict obsolete ? (eilers)
599 */
600 m_dict.setAutoDelete( TRUE );
601 m_dict.insert("Categories" , new int(OTodo::Category) );
602 m_dict.insert("Uid" , new int(OTodo::Uid) );
603 m_dict.insert("HasDate" , new int(OTodo::HasDate) );
604 m_dict.insert("Completed" , new int(OTodo::Completed) );
605 m_dict.insert("Description" , new int(OTodo::Description) );
606 m_dict.insert("Summary" , new int(OTodo::Summary) );
607 m_dict.insert("Priority" , new int(OTodo::Priority) );
608 m_dict.insert("DateDay" , new int(OTodo::DateDay) );
609 m_dict.insert("DateMonth" , new int(OTodo::DateMonth) );
610 m_dict.insert("DateYear" , new int(OTodo::DateYear) );
611 m_dict.insert("Progress" , new int(OTodo::Progress) );
612 m_dict.insert("Completed", new int(OTodo::Completed) ); // Why twice ? (eilers)
613 m_dict.insert("CrossReference", new int(OTodo::CrossReference) );
614// m_dict.insert("HasAlarmDateTime",new int(OTodo::HasAlarmDateTime) ); // old stuff (eilers)
615// m_dict.insert("AlarmDateTime", new int(OTodo::AlarmDateTime) ); // old stuff (eilers)
616}
617/*
618 * need to be const so let's fool the
619 * compiler :(
620 */
621void OTodoAccessBackendSQL::update()const {
622 ((OTodoAccessBackendSQL*)this)->m_dirty = false;
623 LoadQuery lo;
624 OSQLResult res = m_driver->query(&lo);
625 if ( res.state() != OSQLResult::Success )
626 return;
627
628 ((OTodoAccessBackendSQL*)this)->m_uids = uids( res );
629}
630QArray<int> OTodoAccessBackendSQL::uids( const OSQLResult& res) const{
631
632 OSQLResultItem::ValueList list = res.results();
633 OSQLResultItem::ValueList::Iterator it;
634 QArray<int> ints(list.count() );
635 qWarning(" count = %d", list.count() );
636
637 int i = 0;
638 for (it = list.begin(); it != list.end(); ++it ) {
639 ints[i] = (*it).data("uid").toInt();
640 i++;
641 }
642 return ints;
643}
644
645QArray<int> OTodoAccessBackendSQL::matchRegexp( const QRegExp &r ) const
646{
647
648#warning OTodoAccessBackendSQL::matchRegexp() not implemented !!
649
650#if 0
651
652 Copied from xml-backend by not adapted to sql (eilers)
653
654 QArray<int> m_currentQuery( m_events.count() );
655 uint arraycounter = 0;
656
657
658
659 QMap<int, OTodo>::ConstIterator it;
660 for (it = m_events.begin(); it != m_events.end(); ++it ) {
661 if ( it.data().match( r ) )
662 m_currentQuery[arraycounter++] = it.data().uid();
663
664 }
665 // Shrink to fit..
666 m_currentQuery.resize(arraycounter);
667
668 return m_currentQuery;
669#endif
670 QArray<int> empty;
671 return empty;
672}
673QBitArray OTodoAccessBackendSQL::supports()const {
674
675 return sup();
676}
677
678QBitArray OTodoAccessBackendSQL::sup() const{
679
680 QBitArray ar( OTodo::CompletedDate + 1 );
681 ar.fill( true );
682 ar[OTodo::CrossReference] = false;
683 ar[OTodo::State ] = false;
684 ar[OTodo::Reminders] = false;
685 ar[OTodo::Notifiers] = false;
686 ar[OTodo::Maintainer] = false;
687
688 return ar;
689}
690
691void OTodoAccessBackendSQL::removeAllCompleted(){
692#warning OTodoAccessBackendSQL::removeAllCompleted() not implemented !!
693
694}