summaryrefslogtreecommitdiff
path: root/libopie2/opiepim
authorzecke <zecke>2002-10-07 09:10:05 (UTC)
committer zecke <zecke>2002-10-07 09:10:05 (UTC)
commitacd2d0062dd87635feb03927663b1f700305d67b (patch) (unidiff)
tree179217749ec7ba3217ca71f80a6e1064f249d1b0 /libopie2/opiepim
parentde56dc7c5a21cbd70e9d1e66ab68ca95a277c45c (diff)
downloadopie-acd2d0062dd87635feb03927663b1f700305d67b.zip
opie-acd2d0062dd87635feb03927663b1f700305d67b.tar.gz
opie-acd2d0062dd87635feb03927663b1f700305d67b.tar.bz2
First SQL backend Resource
The DB layout is not fully what we've in mind but for example I do lack the Categories infrastructure for that I've to implement sorted lists and then I'll make Todolist fast
Diffstat (limited to 'libopie2/opiepim') (more/less context) (ignore whitespace changes)
-rw-r--r--libopie2/opiepim/backend/otodoaccesssql.cpp397
-rw-r--r--libopie2/opiepim/backend/otodoaccesssql.h46
-rw-r--r--libopie2/opiepim/backend/otodoaccessxml.cpp1
-rw-r--r--libopie2/opiepim/core/otodoaccess.cpp4
-rw-r--r--libopie2/opiepim/orecordlist.h5
5 files changed, 448 insertions, 5 deletions
diff --git a/libopie2/opiepim/backend/otodoaccesssql.cpp b/libopie2/opiepim/backend/otodoaccesssql.cpp
new file mode 100644
index 0000000..209e714
--- a/dev/null
+++ b/libopie2/opiepim/backend/otodoaccesssql.cpp
@@ -0,0 +1,397 @@
1
2#include <qdatetime.h>
3
4#include <qpe/global.h>
5
6#include <opie/osqldriver.h>
7#include <opie/osqlresult.h>
8#include <opie/osqlmanager.h>
9#include <opie/osqlquery.h>
10
11#include "otodoaccesssql.h"
12
13/*
14 * first some query
15 * CREATE query
16 * LOAD query
17 * INSERT
18 * REMOVE
19 * CLEAR
20 */
21namespace {
22 /**
23 * CreateQuery for the Todolist Table
24 */
25 class CreateQuery : public OSQLQuery {
26 public:
27 CreateQuery();
28 ~CreateQuery();
29 QString query()const;
30 };
31
32 /**
33 * LoadQuery
34 * this one queries for all uids
35 */
36 class LoadQuery : public OSQLQuery {
37 public:
38 LoadQuery();
39 ~LoadQuery();
40 QString query()const;
41 };
42
43 /**
44 * inserts/adds a OTodo to the table
45 */
46 class InsertQuery : public OSQLQuery {
47 public:
48 InsertQuery(const OTodo& );
49 ~InsertQuery();
50 QString query()const;
51 private:
52 OTodo m_todo;
53 };
54
55 /**
56 * removes one from the table
57 */
58 class RemoveQuery : public OSQLQuery {
59 public:
60 RemoveQuery(int uid );
61 ~RemoveQuery();
62 QString query()const;
63 private:
64 int m_uid;
65 };
66
67 /**
68 * Clears (delete) a Table
69 */
70 class ClearQuery : public OSQLQuery {
71 public:
72 ClearQuery();
73 ~ClearQuery();
74 QString query()const;
75
76 };
77
78 /**
79 * a find query
80 */
81 class FindQuery : public OSQLQuery {
82 public:
83 FindQuery(int uid);
84 ~FindQuery();
85 QString query()const;
86 private:
87 int m_uid;
88 };
89
90 /**
91 * overdue query
92 */
93 class OverDueQuery : public OSQLQuery {
94 public:
95 OverDueQuery();
96 ~OverDueQuery();
97 QString query()const;
98 };
99 class EffQuery : public OSQLQuery {
100 public:
101 EffQuery( const QDate&, const QDate&, bool inc );
102 ~EffQuery();
103 QString query()const;
104 private:
105 QString with()const;
106 QString out()const;
107 QDate m_start;
108 QDate m_end;
109 bool m_inc :1;
110 };
111
112
113 CreateQuery::CreateQuery() : OSQLQuery() {}
114 CreateQuery::~CreateQuery() {}
115 QString CreateQuery::query()const {
116 QString qu;
117 qu += "create table todolist( uid, categories, completed, progress, ";
118 qu += "summary, DueDate, priority, description )";
119 return qu;
120 }
121
122 LoadQuery::LoadQuery() : OSQLQuery() {}
123 LoadQuery::~LoadQuery() {}
124 QString LoadQuery::query()const {
125 QString qu;
126 qu += "select distinct uid from todolist";
127
128 return qu;
129 }
130
131 InsertQuery::InsertQuery( const OTodo& todo )
132 : OSQLQuery(), m_todo( todo ) {
133 }
134 InsertQuery::~InsertQuery() {
135 }
136 /*
137 * converts from a OTodo to a query
138 * we leave out X-Ref + Alarms
139 */
140 QString InsertQuery::query()const{
141 int year, month, day;
142 year = month = day = 0;
143 if (m_todo.hasDueDate() ) {
144 QDate date = m_todo.dueDate();
145 year = date.year();
146 month = date.month();
147 day = date.day();
148 }
149 QString qu;
150 qu = "insert into todolist VALUES(" + QString::number( m_todo.uid() ) + ",'" + m_todo.idsToString( m_todo.categories() ) + "',";
151 qu += QString::number( m_todo.isCompleted() ) + "," + QString::number( m_todo.progress() ) + ",";
152 qu += "'"+m_todo.summary()+"','"+QString::number(year)+"-"+QString::number(month)+"-"+QString::number(day)+"',";
153 qu += QString::number(m_todo.priority() ) +",'" + m_todo.description() + "')";
154
155 qWarning("add %s", qu.latin1() );
156 return qu;
157 }
158
159 RemoveQuery::RemoveQuery(int uid )
160 : OSQLQuery(), m_uid( uid ) {}
161 RemoveQuery::~RemoveQuery() {}
162 QString RemoveQuery::query()const {
163 QString qu = "DELETE from todolist where uid = " + QString::number(m_uid);
164 return qu;
165 }
166
167
168 ClearQuery::ClearQuery()
169 : OSQLQuery() {}
170 ClearQuery::~ClearQuery() {}
171 QString ClearQuery::query()const {
172 QString qu = "drop table todolist";
173 return qu;
174 }
175 FindQuery::FindQuery(int uid)
176 : OSQLQuery(), m_uid(uid ) {
177 }
178 FindQuery::~FindQuery() {
179 }
180 QString FindQuery::query()const{
181 QString qu = "select uid, categories, completed, progress, summary, ";
182 qu += "DueDate, priority, description from todolist where uid = " + QString::number(m_uid);
183 return qu;
184 }
185
186 OverDueQuery::OverDueQuery(): OSQLQuery() {}
187 OverDueQuery::~OverDueQuery() {}
188 QString OverDueQuery::query()const {
189 QDate date = QDate::currentDate();
190 QString str;
191 str = QString("select uid from todolist where DueDate ='%1-%2-%3'").arg(date.year() ).arg(date.month() ).arg(date.day() );
192
193 return str;
194 }
195
196
197 EffQuery::EffQuery( const QDate& start, const QDate& end, bool inc )
198 : OSQLQuery(), m_start( start ), m_end( end ),m_inc(inc) {}
199 EffQuery::~EffQuery() {}
200 QString EffQuery::query()const {
201 return m_inc ? with() : out();
202 }
203 QString EffQuery::with()const {
204 QString str;
205 str = QString("select uid from todolist where ( DueDate >= '%1-%2-%3' AND DueDate <= '%4-%5-%6' ) OR DueDate = '0-0-0' ")
206 .arg( m_start.year() ).arg( m_start.month() ).arg( m_start.day() )
207 .arg( m_end .year() ).arg( m_end .month() ).arg( m_end .day() );
208 return str;
209 }
210 QString EffQuery::out()const {
211 QString str;
212 str = QString("select uid from todolist where DueDate >= '%1-%2-%3' AND DueDate <= '%4-%5-%6'")
213 .arg(m_start.year() ).arg(m_start.month() ).arg( m_start.day() )
214 .arg(m_end. year() ).arg(m_end. month() ).arg(m_end.day() );
215
216 return str;
217 }
218};
219
220OTodoAccessBackendSQL::OTodoAccessBackendSQL( const QString& file )
221 : OTodoAccessBackend(), m_dict(15)
222{
223 QString fi = file;
224 if ( fi.isEmpty() )
225 fi = Global::applicationFileName( "todolist", "todolist.db" );
226 OSQLManager man;
227 m_driver = man.standard();
228 m_driver->setUrl(fi);
229 fillDict();
230}
231
232OTodoAccessBackendSQL::~OTodoAccessBackendSQL(){
233}
234bool OTodoAccessBackendSQL::load(){
235 if (!m_driver->open() )
236 return false;
237
238 CreateQuery creat;
239 OSQLResult res = m_driver->query(&creat );
240
241 update();
242 qWarning("loaded %d", m_uids.count() );
243 return true;
244}
245bool OTodoAccessBackendSQL::reload(){
246 return load();
247}
248
249bool OTodoAccessBackendSQL::save(){
250 return m_driver->close();
251}
252QArray<int> OTodoAccessBackendSQL::allRecords()const {
253 return m_uids;
254}
255QArray<int> OTodoAccessBackendSQL::queryByExample( const OTodo& , int ){
256 QArray<int> ints(0);
257 return ints;
258}
259OTodo OTodoAccessBackendSQL::find(int uid ) const{
260 FindQuery query( uid );
261 return todo( m_driver->query(&query) );
262
263}
264void OTodoAccessBackendSQL::clear() {
265 ClearQuery cle;
266 OSQLResult res = m_driver->query( &cle );
267 CreateQuery qu;
268 res = m_driver->query(&qu);
269}
270bool OTodoAccessBackendSQL::add( const OTodo& t) {
271 InsertQuery ins( t );
272 OSQLResult res = m_driver->query( &ins );
273
274 if ( res.state() == OSQLResult::Failure )
275 return false;
276 int c = m_uids.count();
277 m_uids.resize( c+1 );
278 m_uids[c] = t.uid();
279
280 return true;
281}
282bool OTodoAccessBackendSQL::remove( int uid ) {
283 RemoveQuery rem( uid );
284 OSQLResult res = m_driver->query(&rem );
285
286 if ( res.state() == OSQLResult::Failure )
287 return false;
288
289 update();
290 return true;
291}
292/*
293 * FIXME better set query
294 * but we need the cache for that
295 * now we remove
296 */
297bool OTodoAccessBackendSQL::replace( const OTodo& t) {
298 remove( t.uid() );
299 return add(t);
300}
301QArray<int> OTodoAccessBackendSQL::overDue() {
302 OverDueQuery qu;
303 return uids( m_driver->query(&qu ) );
304}
305QArray<int> OTodoAccessBackendSQL::effectiveToDos( const QDate& s,
306 const QDate& t,
307 bool u) {
308 EffQuery ef(s, t, u );
309 return uids (m_driver->query(&ef) );
310}
311QArray<int> OTodoAccessBackendSQL::sorted( bool asc, int sortOrder,
312 int sortFilter, int cat ) {
313 QArray<int> ints(0);
314 return ints;
315}
316bool OTodoAccessBackendSQL::date( QDate& da, const QString& str ) const{
317 if ( str == "0-0-0" )
318 return false;
319 else{
320 int day, year, month;
321 QStringList list = QStringList::split("-", str );
322 year = list[0].toInt();
323 month = list[1].toInt();
324 day = list[2].toInt();
325 da.setYMD( year, month, day );
326 return true;
327 }
328}
329OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{
330 if ( res.state() == OSQLResult::Failure ) {
331 OTodo to;
332 return to;
333 }
334
335 OSQLResultItem::ValueList list = res.results();
336 OSQLResultItem::ValueList::Iterator it = list.begin();
337
338 bool has = false; QDate da = QDate::currentDate();
339 has = date( da, (*it).data("DueDate") );
340 QStringList cats = QStringList::split(";", (*it).data("categories") );
341
342 OTodo to( (bool)(*it).data("completed").toInt(), (*it).data("priority").toInt(),
343 cats, (*it).data("summary"), (*it).data("description"),
344 (*it).data("progress").toUShort(), has, da, (*it).data("uid").toInt() );
345 return to;
346}
347OTodo OTodoAccessBackendSQL::todo( int uid )const {
348 FindQuery find( uid );
349 return todo( m_driver->query(&find) );
350}
351/*
352 * update the dict
353 */
354void OTodoAccessBackendSQL::fillDict() {
355 /* initialize dict */
356 /*
357 * UPDATE dict if you change anything!!!
358 */
359 m_dict.setAutoDelete( TRUE );
360 m_dict.insert("Categories" , new int(OTodo::Category) );
361 m_dict.insert("Uid" , new int(OTodo::Uid) );
362 m_dict.insert("HasDate" , new int(OTodo::HasDate) );
363 m_dict.insert("Completed" , new int(OTodo::Completed) );
364 m_dict.insert("Description" , new int(OTodo::Description) );
365 m_dict.insert("Summary" , new int(OTodo::Summary) );
366 m_dict.insert("Priority" , new int(OTodo::Priority) );
367 m_dict.insert("DateDay" , new int(OTodo::DateDay) );
368 m_dict.insert("DateMonth" , new int(OTodo::DateMonth) );
369 m_dict.insert("DateYear" , new int(OTodo::DateYear) );
370 m_dict.insert("Progress" , new int(OTodo::Progress) );
371 m_dict.insert("Completed", new int(OTodo::Completed) );
372 m_dict.insert("CrossReference", new int(OTodo::CrossReference) );
373 m_dict.insert("HasAlarmDateTime",new int(OTodo::HasAlarmDateTime) );
374 m_dict.insert("AlarmDateTime", new int(OTodo::AlarmDateTime) );
375}
376void OTodoAccessBackendSQL::update() {
377 LoadQuery lo;
378 OSQLResult res = m_driver->query(&lo);
379 if ( res.state() != OSQLResult::Success )
380 return;
381
382 m_uids = uids( res );
383}
384QArray<int> OTodoAccessBackendSQL::uids( const OSQLResult& res) const{
385
386 OSQLResultItem::ValueList list = res.results();
387 OSQLResultItem::ValueList::Iterator it;
388 QArray<int> ints(list.count() );
389
390 int i = 0;
391 for (it = list.begin(); it != list.end(); ++it ) {
392 ints[i] = (*it).data("uid").toInt();
393 i++;
394 }
395 return ints;
396}
397
diff --git a/libopie2/opiepim/backend/otodoaccesssql.h b/libopie2/opiepim/backend/otodoaccesssql.h
new file mode 100644
index 0000000..966628d
--- a/dev/null
+++ b/libopie2/opiepim/backend/otodoaccesssql.h
@@ -0,0 +1,46 @@
1#ifndef OPIE_PIM_ACCESS_SQL_H
2#define OPIE_PIM_ACCESS_SQL_H
3
4#include <qasciidict.h>
5
6#include "otodoaccessbackend.h"
7
8class OSQLDriver;
9class OSQLResult;
10class OTodoAccessBackendSQL : public OTodoAccessBackend {
11public:
12 OTodoAccessBackendSQL( const QString& file );
13 ~OTodoAccessBackendSQL();
14
15 bool load();
16 bool reload();
17 bool save();
18 QArray<int> allRecords()const;
19
20 QArray<int> queryByExample( const OTodo& t, int sort );
21 OTodo find(int uid)const;
22 void clear();
23 bool add( const OTodo& t );
24 bool remove( int uid );
25 bool replace( const OTodo& t );
26
27 QArray<int> overDue();
28 QArray<int> effectiveToDos( const QDate& start,
29 const QDate& end, bool includeNoDates );
30 QArray<int> sorted(bool asc, int sortOrder, int sortFilter, int cat );
31
32private:
33 void update();
34 void fillDict();
35 bool date( QDate& date, const QString& )const;
36 OTodo todo( const OSQLResult& )const;
37 QArray<int> uids( const OSQLResult& )const;
38 OTodo todo( int uid )const;
39
40 QAsciiDict<int> m_dict;
41 OSQLDriver* m_driver;
42 QArray<int> m_uids;
43};
44
45
46#endif
diff --git a/libopie2/opiepim/backend/otodoaccessxml.cpp b/libopie2/opiepim/backend/otodoaccessxml.cpp
index 31822d4..80b8599 100644
--- a/libopie2/opiepim/backend/otodoaccessxml.cpp
+++ b/libopie2/opiepim/backend/otodoaccessxml.cpp
@@ -119,6 +119,7 @@ bool OTodoAccessXML::load() {
119 m_events.insert(ev.uid(), ev ); 119 m_events.insert(ev.uid(), ev );
120 } 120 }
121 121
122 qWarning("counts %d", m_events.count() );
122 return true; 123 return true;
123} 124}
124bool OTodoAccessXML::reload() { 125bool OTodoAccessXML::reload() {
diff --git a/libopie2/opiepim/core/otodoaccess.cpp b/libopie2/opiepim/core/otodoaccess.cpp
index f51da29..5990841 100644
--- a/libopie2/opiepim/core/otodoaccess.cpp
+++ b/libopie2/opiepim/core/otodoaccess.cpp
@@ -2,7 +2,7 @@
2 2
3#include <qpe/alarmserver.h> 3#include <qpe/alarmserver.h>
4 4
5#include "otodoaccessxml.h" 5#include "otodoaccesssql.h"
6#include "otodoaccess.h" 6#include "otodoaccess.h"
7 7
8 8
@@ -10,7 +10,7 @@ OTodoAccess::OTodoAccess( OTodoAccessBackend* end )
10 : QObject(), OPimAccessTemplate<OTodo>( end ), m_todoBackEnd( end ) 10 : QObject(), OPimAccessTemplate<OTodo>( end ), m_todoBackEnd( end )
11{ 11{
12 if (end == 0l ) 12 if (end == 0l )
13 m_todoBackEnd = new OTodoAccessXML( "Todolist" ); 13 m_todoBackEnd = new OTodoAccessBackendSQL( QString::null);
14 14
15 setBackEnd( m_todoBackEnd ); 15 setBackEnd( m_todoBackEnd );
16} 16}
diff --git a/libopie2/opiepim/orecordlist.h b/libopie2/opiepim/orecordlist.h
index 1fd0741..b6fa7fa 100644
--- a/libopie2/opiepim/orecordlist.h
+++ b/libopie2/opiepim/orecordlist.h
@@ -154,14 +154,13 @@ ORecordListIterator<T> &ORecordListIterator<T>::operator=( const ORecordListIter
154 154
155template <class T> 155template <class T>
156T ORecordListIterator<T>::operator*() { 156T ORecordListIterator<T>::operator*() {
157// qWarning("operator* %d %d", m_current, m_uids[m_current] ); 157 qWarning("operator* %d %d", m_current, m_uids[m_current] );
158 if (!m_end ) 158 if (!m_end )
159 /* FIXME 159 /* FIXME
160 * until the cache is in place 160 * until the cache is in place
161 * we do the uid match uid check 161 * we do the uid match uid check
162 */ 162 */
163 if(m_record.uid() != m_uids[m_current] ) 163 m_record = m_temp->find( m_uids[m_current] );
164 m_record = m_temp->find( m_uids[m_current] );
165 else 164 else
166 m_record = T(); 165 m_record = T();
167 166