author | zecke <zecke> | 2002-10-07 09:10:05 (UTC) |
---|---|---|
committer | zecke <zecke> | 2002-10-07 09:10:05 (UTC) |
commit | acd2d0062dd87635feb03927663b1f700305d67b (patch) (unidiff) | |
tree | 179217749ec7ba3217ca71f80a6e1064f249d1b0 /libopie/pim/otodoaccesssql.cpp | |
parent | de56dc7c5a21cbd70e9d1e66ab68ca95a277c45c (diff) | |
download | opie-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 'libopie/pim/otodoaccesssql.cpp') (more/less context) (show whitespace changes)
-rw-r--r-- | libopie/pim/otodoaccesssql.cpp | 397 |
1 files changed, 397 insertions, 0 deletions
diff --git a/libopie/pim/otodoaccesssql.cpp b/libopie/pim/otodoaccesssql.cpp new file mode 100644 index 0000000..209e714 --- a/dev/null +++ b/libopie/pim/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 | */ | ||
21 | namespace { | ||
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 | |||
220 | OTodoAccessBackendSQL::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 | |||
232 | OTodoAccessBackendSQL::~OTodoAccessBackendSQL(){ | ||
233 | } | ||
234 | bool 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 | } | ||
245 | bool OTodoAccessBackendSQL::reload(){ | ||
246 | return load(); | ||
247 | } | ||
248 | |||
249 | bool OTodoAccessBackendSQL::save(){ | ||
250 | return m_driver->close(); | ||
251 | } | ||
252 | QArray<int> OTodoAccessBackendSQL::allRecords()const { | ||
253 | return m_uids; | ||
254 | } | ||
255 | QArray<int> OTodoAccessBackendSQL::queryByExample( const OTodo& , int ){ | ||
256 | QArray<int> ints(0); | ||
257 | return ints; | ||
258 | } | ||
259 | OTodo OTodoAccessBackendSQL::find(int uid ) const{ | ||
260 | FindQuery query( uid ); | ||
261 | return todo( m_driver->query(&query) ); | ||
262 | |||
263 | } | ||
264 | void OTodoAccessBackendSQL::clear() { | ||
265 | ClearQuery cle; | ||
266 | OSQLResult res = m_driver->query( &cle ); | ||
267 | CreateQuery qu; | ||
268 | res = m_driver->query(&qu); | ||
269 | } | ||
270 | bool 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 | } | ||
282 | bool 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 | */ | ||
297 | bool OTodoAccessBackendSQL::replace( const OTodo& t) { | ||
298 | remove( t.uid() ); | ||
299 | return add(t); | ||
300 | } | ||
301 | QArray<int> OTodoAccessBackendSQL::overDue() { | ||
302 | OverDueQuery qu; | ||
303 | return uids( m_driver->query(&qu ) ); | ||
304 | } | ||
305 | QArray<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 | } | ||
311 | QArray<int> OTodoAccessBackendSQL::sorted( bool asc, int sortOrder, | ||
312 | int sortFilter, int cat ) { | ||
313 | QArray<int> ints(0); | ||
314 | return ints; | ||
315 | } | ||
316 | bool 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 | } | ||
329 | OTodo 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 | } | ||
347 | OTodo OTodoAccessBackendSQL::todo( int uid )const { | ||
348 | FindQuery find( uid ); | ||
349 | return todo( m_driver->query(&find) ); | ||
350 | } | ||
351 | /* | ||
352 | * update the dict | ||
353 | */ | ||
354 | void 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 | } | ||
376 | void 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 | } | ||
384 | QArray<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 | |||