| -rw-r--r-- | libopie2/opiepim/backend/otodoaccesssql.cpp | 397 | ||||
| -rw-r--r-- | libopie2/opiepim/backend/otodoaccesssql.h | 46 | ||||
| -rw-r--r-- | libopie2/opiepim/backend/otodoaccessxml.cpp | 1 |
3 files changed, 444 insertions, 0 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 @@ + +#include <qdatetime.h> + +#include <qpe/global.h> + +#include <opie/osqldriver.h> +#include <opie/osqlresult.h> +#include <opie/osqlmanager.h> +#include <opie/osqlquery.h> + +#include "otodoaccesssql.h" + +/* + * first some query + * CREATE query + * LOAD query + * INSERT + * REMOVE + * CLEAR + */ +namespace { + /** + * CreateQuery for the Todolist Table + */ + class CreateQuery : public OSQLQuery { + public: + CreateQuery(); + ~CreateQuery(); + QString query()const; + }; + + /** + * LoadQuery + * this one queries for all uids + */ + class LoadQuery : public OSQLQuery { + public: + LoadQuery(); + ~LoadQuery(); + QString query()const; + }; + + /** + * inserts/adds a OTodo to the table + */ + class InsertQuery : public OSQLQuery { + public: + InsertQuery(const OTodo& ); + ~InsertQuery(); + QString query()const; + private: + OTodo m_todo; + }; + + /** + * removes one from the table + */ + class RemoveQuery : public OSQLQuery { + public: + RemoveQuery(int uid ); + ~RemoveQuery(); + QString query()const; + private: + int m_uid; + }; + + /** + * Clears (delete) a Table + */ + class ClearQuery : public OSQLQuery { + public: + ClearQuery(); + ~ClearQuery(); + QString query()const; + + }; + + /** + * a find query + */ + class FindQuery : public OSQLQuery { + public: + FindQuery(int uid); + ~FindQuery(); + QString query()const; + private: + int m_uid; + }; + + /** + * overdue query + */ + class OverDueQuery : public OSQLQuery { + public: + OverDueQuery(); + ~OverDueQuery(); + QString query()const; + }; + class EffQuery : public OSQLQuery { + public: + EffQuery( const QDate&, const QDate&, bool inc ); + ~EffQuery(); + QString query()const; + private: + QString with()const; + QString out()const; + QDate m_start; + QDate m_end; + bool m_inc :1; + }; + + + CreateQuery::CreateQuery() : OSQLQuery() {} + CreateQuery::~CreateQuery() {} + QString CreateQuery::query()const { + QString qu; + qu += "create table todolist( uid, categories, completed, progress, "; + qu += "summary, DueDate, priority, description )"; + return qu; + } + + LoadQuery::LoadQuery() : OSQLQuery() {} + LoadQuery::~LoadQuery() {} + QString LoadQuery::query()const { + QString qu; + qu += "select distinct uid from todolist"; + + return qu; + } + + InsertQuery::InsertQuery( const OTodo& todo ) + : OSQLQuery(), m_todo( todo ) { + } + InsertQuery::~InsertQuery() { + } + /* + * converts from a OTodo to a query + * we leave out X-Ref + Alarms + */ + QString InsertQuery::query()const{ + int year, month, day; + year = month = day = 0; + if (m_todo.hasDueDate() ) { + QDate date = m_todo.dueDate(); + year = date.year(); + month = date.month(); + day = date.day(); + } + QString qu; + qu = "insert into todolist VALUES(" + QString::number( m_todo.uid() ) + ",'" + m_todo.idsToString( m_todo.categories() ) + "',"; + qu += QString::number( m_todo.isCompleted() ) + "," + QString::number( m_todo.progress() ) + ","; + qu += "'"+m_todo.summary()+"','"+QString::number(year)+"-"+QString::number(month)+"-"+QString::number(day)+"',"; + qu += QString::number(m_todo.priority() ) +",'" + m_todo.description() + "')"; + + qWarning("add %s", qu.latin1() ); + return qu; + } + + RemoveQuery::RemoveQuery(int uid ) + : OSQLQuery(), m_uid( uid ) {} + RemoveQuery::~RemoveQuery() {} + QString RemoveQuery::query()const { + QString qu = "DELETE from todolist where uid = " + QString::number(m_uid); + return qu; + } + + + ClearQuery::ClearQuery() + : OSQLQuery() {} + ClearQuery::~ClearQuery() {} + QString ClearQuery::query()const { + QString qu = "drop table todolist"; + return qu; + } + FindQuery::FindQuery(int uid) + : OSQLQuery(), m_uid(uid ) { + } + FindQuery::~FindQuery() { + } + QString FindQuery::query()const{ + QString qu = "select uid, categories, completed, progress, summary, "; + qu += "DueDate, priority, description from todolist where uid = " + QString::number(m_uid); + return qu; + } + + OverDueQuery::OverDueQuery(): OSQLQuery() {} + OverDueQuery::~OverDueQuery() {} + QString OverDueQuery::query()const { + QDate date = QDate::currentDate(); + QString str; + str = QString("select uid from todolist where DueDate ='%1-%2-%3'").arg(date.year() ).arg(date.month() ).arg(date.day() ); + + return str; + } + + + EffQuery::EffQuery( const QDate& start, const QDate& end, bool inc ) + : OSQLQuery(), m_start( start ), m_end( end ),m_inc(inc) {} + EffQuery::~EffQuery() {} + QString EffQuery::query()const { + return m_inc ? with() : out(); + } + QString EffQuery::with()const { + QString str; + str = QString("select uid from todolist where ( DueDate >= '%1-%2-%3' AND DueDate <= '%4-%5-%6' ) OR DueDate = '0-0-0' ") + .arg( m_start.year() ).arg( m_start.month() ).arg( m_start.day() ) + .arg( m_end .year() ).arg( m_end .month() ).arg( m_end .day() ); + return str; + } + QString EffQuery::out()const { + QString str; + str = QString("select uid from todolist where DueDate >= '%1-%2-%3' AND DueDate <= '%4-%5-%6'") + .arg(m_start.year() ).arg(m_start.month() ).arg( m_start.day() ) + .arg(m_end. year() ).arg(m_end. month() ).arg(m_end.day() ); + + return str; + } +}; + +OTodoAccessBackendSQL::OTodoAccessBackendSQL( const QString& file ) + : OTodoAccessBackend(), m_dict(15) +{ + QString fi = file; + if ( fi.isEmpty() ) + fi = Global::applicationFileName( "todolist", "todolist.db" ); + OSQLManager man; + m_driver = man.standard(); + m_driver->setUrl(fi); + fillDict(); +} + +OTodoAccessBackendSQL::~OTodoAccessBackendSQL(){ +} +bool OTodoAccessBackendSQL::load(){ + if (!m_driver->open() ) + return false; + + CreateQuery creat; + OSQLResult res = m_driver->query(&creat ); + + update(); + qWarning("loaded %d", m_uids.count() ); + return true; +} +bool OTodoAccessBackendSQL::reload(){ + return load(); +} + +bool OTodoAccessBackendSQL::save(){ + return m_driver->close(); +} +QArray<int> OTodoAccessBackendSQL::allRecords()const { + return m_uids; +} +QArray<int> OTodoAccessBackendSQL::queryByExample( const OTodo& , int ){ + QArray<int> ints(0); + return ints; +} +OTodo OTodoAccessBackendSQL::find(int uid ) const{ + FindQuery query( uid ); + return todo( m_driver->query(&query) ); + +} +void OTodoAccessBackendSQL::clear() { + ClearQuery cle; + OSQLResult res = m_driver->query( &cle ); + CreateQuery qu; + res = m_driver->query(&qu); +} +bool OTodoAccessBackendSQL::add( const OTodo& t) { + InsertQuery ins( t ); + OSQLResult res = m_driver->query( &ins ); + + if ( res.state() == OSQLResult::Failure ) + return false; + int c = m_uids.count(); + m_uids.resize( c+1 ); + m_uids[c] = t.uid(); + + return true; +} +bool OTodoAccessBackendSQL::remove( int uid ) { + RemoveQuery rem( uid ); + OSQLResult res = m_driver->query(&rem ); + + if ( res.state() == OSQLResult::Failure ) + return false; + + update(); + return true; +} +/* + * FIXME better set query + * but we need the cache for that + * now we remove + */ +bool OTodoAccessBackendSQL::replace( const OTodo& t) { + remove( t.uid() ); + return add(t); +} +QArray<int> OTodoAccessBackendSQL::overDue() { + OverDueQuery qu; + return uids( m_driver->query(&qu ) ); +} +QArray<int> OTodoAccessBackendSQL::effectiveToDos( const QDate& s, + const QDate& t, + bool u) { + EffQuery ef(s, t, u ); + return uids (m_driver->query(&ef) ); +} +QArray<int> OTodoAccessBackendSQL::sorted( bool asc, int sortOrder, + int sortFilter, int cat ) { + QArray<int> ints(0); + return ints; +} +bool OTodoAccessBackendSQL::date( QDate& da, const QString& str ) const{ + if ( str == "0-0-0" ) + return false; + else{ + int day, year, month; + QStringList list = QStringList::split("-", str ); + year = list[0].toInt(); + month = list[1].toInt(); + day = list[2].toInt(); + da.setYMD( year, month, day ); + return true; + } +} +OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{ + if ( res.state() == OSQLResult::Failure ) { + OTodo to; + return to; + } + + OSQLResultItem::ValueList list = res.results(); + OSQLResultItem::ValueList::Iterator it = list.begin(); + + bool has = false; QDate da = QDate::currentDate(); + has = date( da, (*it).data("DueDate") ); + QStringList cats = QStringList::split(";", (*it).data("categories") ); + + OTodo to( (bool)(*it).data("completed").toInt(), (*it).data("priority").toInt(), + cats, (*it).data("summary"), (*it).data("description"), + (*it).data("progress").toUShort(), has, da, (*it).data("uid").toInt() ); + return to; +} +OTodo OTodoAccessBackendSQL::todo( int uid )const { + FindQuery find( uid ); + return todo( m_driver->query(&find) ); +} +/* + * update the dict + */ +void OTodoAccessBackendSQL::fillDict() { + /* initialize dict */ + /* + * UPDATE dict if you change anything!!! + */ + m_dict.setAutoDelete( TRUE ); + m_dict.insert("Categories" , new int(OTodo::Category) ); + m_dict.insert("Uid" , new int(OTodo::Uid) ); + m_dict.insert("HasDate" , new int(OTodo::HasDate) ); + m_dict.insert("Completed" , new int(OTodo::Completed) ); + m_dict.insert("Description" , new int(OTodo::Description) ); + m_dict.insert("Summary" , new int(OTodo::Summary) ); + m_dict.insert("Priority" , new int(OTodo::Priority) ); + m_dict.insert("DateDay" , new int(OTodo::DateDay) ); + m_dict.insert("DateMonth" , new int(OTodo::DateMonth) ); + m_dict.insert("DateYear" , new int(OTodo::DateYear) ); + m_dict.insert("Progress" , new int(OTodo::Progress) ); + m_dict.insert("Completed", new int(OTodo::Completed) ); + m_dict.insert("CrossReference", new int(OTodo::CrossReference) ); + m_dict.insert("HasAlarmDateTime",new int(OTodo::HasAlarmDateTime) ); + m_dict.insert("AlarmDateTime", new int(OTodo::AlarmDateTime) ); +} +void OTodoAccessBackendSQL::update() { + LoadQuery lo; + OSQLResult res = m_driver->query(&lo); + if ( res.state() != OSQLResult::Success ) + return; + + m_uids = uids( res ); +} +QArray<int> OTodoAccessBackendSQL::uids( const OSQLResult& res) const{ + + OSQLResultItem::ValueList list = res.results(); + OSQLResultItem::ValueList::Iterator it; + QArray<int> ints(list.count() ); + + int i = 0; + for (it = list.begin(); it != list.end(); ++it ) { + ints[i] = (*it).data("uid").toInt(); + i++; + } + return ints; +} + 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 @@ +#ifndef OPIE_PIM_ACCESS_SQL_H +#define OPIE_PIM_ACCESS_SQL_H + +#include <qasciidict.h> + +#include "otodoaccessbackend.h" + +class OSQLDriver; +class OSQLResult; +class OTodoAccessBackendSQL : public OTodoAccessBackend { +public: + OTodoAccessBackendSQL( const QString& file ); + ~OTodoAccessBackendSQL(); + + bool load(); + bool reload(); + bool save(); + QArray<int> allRecords()const; + + QArray<int> queryByExample( const OTodo& t, int sort ); + OTodo find(int uid)const; + void clear(); + bool add( const OTodo& t ); + bool remove( int uid ); + bool replace( const OTodo& t ); + + QArray<int> overDue(); + QArray<int> effectiveToDos( const QDate& start, + const QDate& end, bool includeNoDates ); + QArray<int> sorted(bool asc, int sortOrder, int sortFilter, int cat ); + +private: + void update(); + void fillDict(); + bool date( QDate& date, const QString& )const; + OTodo todo( const OSQLResult& )const; + QArray<int> uids( const OSQLResult& )const; + OTodo todo( int uid )const; + + QAsciiDict<int> m_dict; + OSQLDriver* m_driver; + QArray<int> m_uids; +}; + + +#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 @@ -1,377 +1,378 @@ #include <qfile.h> #include <qvector.h> #include <qpe/global.h> #include <qpe/stringutil.h> #include <qpe/timeconversion.h> #include <opie/xmltree.h> #include "otodoaccessxml.h" OTodoAccessXML::OTodoAccessXML( const QString& appName, const QString& fileName ) : OTodoAccessBackend(), m_app( appName ), m_opened( false ), m_changed( false ) { if (!fileName.isEmpty() ) m_file = fileName; else m_file = Global::applicationFileName( "todolist", "todolist.xml" ); } OTodoAccessXML::~OTodoAccessXML() { } bool OTodoAccessXML::load() { m_opened = true; m_changed = false; /* initialize dict */ /* * UPDATE dict if you change anything!!! */ QAsciiDict<int> dict(15); dict.setAutoDelete( TRUE ); dict.insert("Categories" , new int(OTodo::Category) ); dict.insert("Uid" , new int(OTodo::Uid) ); dict.insert("HasDate" , new int(OTodo::HasDate) ); dict.insert("Completed" , new int(OTodo::Completed) ); dict.insert("Description" , new int(OTodo::Description) ); dict.insert("Summary" , new int(OTodo::Summary) ); dict.insert("Priority" , new int(OTodo::Priority) ); dict.insert("DateDay" , new int(OTodo::DateDay) ); dict.insert("DateMonth" , new int(OTodo::DateMonth) ); dict.insert("DateYear" , new int(OTodo::DateYear) ); dict.insert("Progress" , new int(OTodo::Progress) ); dict.insert("Completed", new int(OTodo::Completed) ); dict.insert("CrossReference", new int(OTodo::CrossReference) ); dict.insert("HasAlarmDateTime",new int(OTodo::HasAlarmDateTime) ); dict.insert("AlarmDateTime", new int(OTodo::AlarmDateTime) ); // here the custom XML parser from TT it's GPL // but we want to push that to TT..... QFile f(m_file ); if (!f.open(IO_ReadOnly) ) return false; QByteArray ba = f.readAll(); f.close(); char* dt = ba.data(); int len = ba.size(); int i = 0; char *point; const char* collectionString = "<Task "; while ( dt+i != 0 && ( point = strstr( dt+i, collectionString ) ) != 0l ) { i = point -dt; i+= strlen(collectionString); OTodo ev; m_year = m_month = m_day = 0; while ( TRUE ) { while ( i < len && (dt[i] == ' ' || dt[i] == '\n' || dt[i] == '\r') ) ++i; if ( i >= len-2 || (dt[i] == '/' && dt[i+1] == '>') ) break; // we have another attribute, read it. int j = i; while ( j < len && dt[j] != '=' ) ++j; QCString attr( dt+i, j-i+1); i = ++j; // skip = // find the start of quotes while ( i < len && dt[i] != '"' ) ++i; j = ++i; bool haveUtf = FALSE; bool haveEnt = FALSE; while ( j < len && dt[j] != '"' ) { if ( ((unsigned char)dt[j]) > 0x7f ) haveUtf = TRUE; if ( dt[j] == '&' ) haveEnt = TRUE; ++j; } if ( i == j ) { // empty value i = j + 1; continue; } QCString value( dt+i, j-i+1 ); i = j + 1; QString str = (haveUtf ? QString::fromUtf8( value ) : QString::fromLatin1( value ) ); if ( haveEnt ) str = Qtopia::plainString( str ); /* * add key + value */ todo( &dict, ev, attr, str ); } /* * now add it */ m_events.insert(ev.uid(), ev ); } + qWarning("counts %d", m_events.count() ); return true; } bool OTodoAccessXML::reload() { return load(); } bool OTodoAccessXML::save() { // qWarning("saving"); if (!m_opened || !m_changed ) { // qWarning("not saving"); return true; } QString strNewFile = m_file + ".new"; QFile f( strNewFile ); if (!f.open( IO_WriteOnly|IO_Raw ) ) return false; int written; QString out; out = "<!DOCTYPE Tasks>\n<Tasks>\n"; // for all todos QMap<int, OTodo>::Iterator it; for (it = m_events.begin(); it != m_events.end(); ++it ) { out+= "<Task " + toString( (*it) ) + " />\n"; QCString cstr = out.utf8(); written = f.writeBlock( cstr.data(), cstr.length() ); /* less written then we wanted */ if ( written != (int)cstr.length() ) { f.close(); QFile::remove( strNewFile ); return false; } out = QString::null; } out += "</Tasks>"; QCString cstr = out.utf8(); written = f.writeBlock( cstr.data(), cstr.length() ); if ( written != (int)cstr.length() ) { f.close(); QFile::remove( strNewFile ); return false; } /* flush before renaming */ f.close(); if( ::rename( strNewFile.latin1(), m_file.latin1() ) < 0 ) { // qWarning("error renaming"); QFile::remove( strNewFile ); } m_changed = false; return true; } QArray<int> OTodoAccessXML::allRecords()const { QArray<int> ids( m_events.count() ); QMap<int, OTodo>::ConstIterator it; int i = 0; for ( it = m_events.begin(); it != m_events.end(); ++it ) { ids[i] = it.key(); i++; } return ids; } QArray<int> OTodoAccessXML::queryByExample( const OTodo&, int sort ) { QArray<int> ids(0); return ids; } OTodo OTodoAccessXML::find( int uid )const { OTodo todo; todo.setUid( 0 ); // isEmpty() QMap<int, OTodo>::ConstIterator it = m_events.find( uid ); if ( it != m_events.end() ) todo = it.data(); return todo; } void OTodoAccessXML::clear() { if (m_opened ) m_changed = true; m_events.clear(); } bool OTodoAccessXML::add( const OTodo& todo ) { // qWarning("add"); m_changed = true; m_events.insert( todo.uid(), todo ); return true; } bool OTodoAccessXML::remove( int uid ) { m_changed = true; m_events.remove( uid ); return true; } bool OTodoAccessXML::replace( const OTodo& todo) { m_changed = true; m_events.replace( todo.uid(), todo ); return true; } QArray<int> OTodoAccessXML::effectiveToDos( const QDate& start, const QDate& end, bool includeNoDates ) { QArray<int> ids( m_events.count() ); QMap<int, OTodo>::Iterator it; int i = 0; for ( it = m_events.begin(); it != m_events.end(); ++it ) { if ( !it.data().hasDueDate() ) { if ( includeNoDates ) { ids[i] = it.key(); i++; } }else if ( it.data().dueDate() >= start && it.data().dueDate() <= end ) { ids[i] = it.key(); i++; } } ids.resize( i ); return ids; } QArray<int> OTodoAccessXML::overDue() { QArray<int> ids( m_events.count() ); int i = 0; QMap<int, OTodo>::Iterator it; for ( it = m_events.begin(); it != m_events.end(); ++it ) { if ( it.data().isOverdue() ) { ids[i] = it.key(); i++; } } ids.resize( i ); return ids; } /* private */ void OTodoAccessXML::todo( QAsciiDict<int>* dict, OTodo& ev, const QCString& attr, const QString& val) { // qWarning("parse to do from XMLElement" ); int *find=0; find = (*dict)[ attr.data() ]; if (!find ) { // qWarning("Unknown option" + it.key() ); ev.setCustomField( attr, val ); return; } switch( *find ) { case OTodo::Uid: ev.setUid( val.toInt() ); break; case OTodo::Category: ev.setCategories( ev.idsFromString( val ) ); break; case OTodo::HasDate: ev.setHasDueDate( val.toInt() ); break; case OTodo::Completed: ev.setCompleted( val.toInt() ); break; case OTodo::Description: ev.setDescription( val ); break; case OTodo::Summary: ev.setSummary( val ); break; case OTodo::Priority: ev.setPriority( val.toInt() ); break; case OTodo::DateDay: m_day = val.toInt(); break; case OTodo::DateMonth: m_month = val.toInt(); break; case OTodo::DateYear: m_year = val.toInt(); break; case OTodo::Progress: ev.setProgress( val.toInt() ); break; case OTodo::CrossReference: { /* * A cross refernce looks like * appname,id;appname,id * we need to split it up */ QStringList refs = QStringList::split(';', val ); QStringList::Iterator strIt; for (strIt = refs.begin(); strIt != refs.end(); ++strIt ) { int pos = (*strIt).find(','); if ( pos > -1 ) ev.addRelation( (*strIt).left(pos), (*strIt).mid(pos+1).toInt() ); } break; } case OTodo::HasAlarmDateTime: ev.setHasAlarmDateTime( val.toInt() ); break; case OTodo::AlarmDateTime: { /* this sounds better ;) zecke */ ev.setAlarmDateTime( TimeConversion::fromISO8601( val.local8Bit() ) ); break; } default: break; } if ( ev.hasDueDate() ) { QDate date( m_year, m_month, m_day ); ev.setDueDate( date ); } } QString OTodoAccessXML::toString( const OTodo& ev )const { QString str; str += "Completed=\"" + QString::number( ev.isCompleted() ) + "\" "; str += "HasDate=\"" + QString::number( ev.hasDueDate() ) + "\" "; str += "Priority=\"" + QString::number( ev.priority() ) + "\" "; str += "Progress=\"" + QString::number(ev.progress() ) + "\" "; str += "Categories=\"" + toString( ev.categories() ) + "\" "; str += "Description=\"" + Qtopia::escapeString( ev.description() ) + "\" "; str += "Summary=\"" + Qtopia::escapeString( ev.summary() ) + "\" "; if ( ev.hasDueDate() ) { str += "DateYear=\"" + QString::number( ev.dueDate().year() ) + "\" "; str += "DateMonth=\"" + QString::number( ev.dueDate().month() ) + "\" "; str += "DateDay=\"" + QString::number( ev.dueDate().day() ) + "\" "; } // qWarning( "Uid %d", ev.uid() ); str += "Uid=\"" + QString::number( ev.uid() ) + "\" "; // append the extra options /* FIXME Qtopia::Record this is currently not * possible you can set custom fields * but don' iterate over the list * I may do #define private protected * for this case - cough --zecke */ /* QMap<QString, QString> extras = ev.extras(); QMap<QString, QString>::Iterator extIt; for (extIt = extras.begin(); extIt != extras.end(); ++extIt ) |