author | eilers <eilers> | 2003-09-29 07:44:26 (UTC) |
---|---|---|
committer | eilers <eilers> | 2003-09-29 07:44:26 (UTC) |
commit | 36d6b0096c41b01e69bb0d12e6c29648cbbf8290 (patch) (side-by-side diff) | |
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) (ignore whitespace changes)
-rw-r--r-- | libopie2/opiepim/backend/otodoaccesssql.cpp | 92 |
1 files changed, 75 insertions, 17 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 @@ -97,129 +97,164 @@ namespace { 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 )"; + qu += "create table todolist( uid PRIMARY KEY, categories, completed, "; + qu += "description, summary, priority, DueDate, progress , state, "; + qu += "Recurrence, notifiers, maintainer, startdate, completeddate)"; return qu; } LoadQuery::LoadQuery() : OSQLQuery() {} LoadQuery::~LoadQuery() {} QString LoadQuery::query()const { QString qu; - qu += "select distinct uid from todolist"; + // We do not need "distinct" here. The primary key is always unique.. + //qu += "select distinct uid from todolist"; + qu += "select 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(); - } + } + int sYear = 0, sMonth = 0, sDay = 0; + if( m_todo.hasStartDate() ){ + QDate sDate = m_todo.startDate(); + sYear = sDate.year(); + sMonth= sDate.month(); + sDay = sDate.day(); + } + + int eYear = 0, eMonth = 0, eDay = 0; + if( m_todo.hasCompletedDate() ){ + QDate eDate = m_todo.completedDate(); + eYear = eDate.year(); + eMonth= eDate.month(); + eDay = eDate.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() + "')"; + qu = "insert into todolist VALUES(" + + QString::number( m_todo.uid() ) + "," + + "'" + m_todo.idsToString( m_todo.categories() ) + "'" + "," + + QString::number( m_todo.isCompleted() ) + "," + + "'" + m_todo.description() + "'" + "," + + "'" + m_todo.summary() + "'" + "," + + QString::number(m_todo.priority() ) + "," + + "'" + QString::number(year) + "-" + + QString::number(month) + + "-" + QString::number( day ) + "'" + "," + + QString::number( m_todo.progress() ) + "," + + "''" + "," // state (conversion needed) +// + QString::number( m_todo.state() ) + "," + + "''" + "," // Recurrence (conversion needed) + + "''" + "," // Notifiers (conversion needed) + + "''" + "," // Maintainers (conversion needed) + + "'" + QString::number(sYear) + "-" + + QString::number(sMonth) + + "-" + QString::number(sDay) + "'" + "," + + "'" + QString::number(eYear) + "-" + + QString::number(eMonth) + + "-"+QString::number(eDay) + "'" + + ")"; 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(const QArray<int>& ints) : OSQLQuery(), m_uids(ints){ } FindQuery::~FindQuery() { } QString FindQuery::query()const{ if (m_uids.count() == 0 ) return single(); else return multi(); } QString FindQuery::single()const{ - QString qu = "select uid, categories, completed, progress, summary, "; - qu += "DueDate, priority, description from todolist where uid = " + QString::number(m_uid); + QString qu = "select * from todolist where uid = " + QString::number(m_uid); return qu; } QString FindQuery::multi()const { - QString qu = "select uid, categories, completed, progress, summary, "; - qu += "DueDate, priority, description from todolist where "; + QString qu = "select * from todolist where "; for (uint i = 0; i < m_uids.count(); i++ ) { qu += " UID = " + QString::number( m_uids[i] ) + " OR"; } qu.remove( qu.length()-2, 2 ); 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(); } @@ -267,49 +302,49 @@ bool OTodoAccessBackendSQL::load(){ bool OTodoAccessBackendSQL::reload(){ return load(); } bool OTodoAccessBackendSQL::save(){ return m_driver->close(); } QArray<int> OTodoAccessBackendSQL::allRecords()const { if (m_dirty ) update(); return m_uids; } QArray<int> OTodoAccessBackendSQL::queryByExample( const OTodo& , int, const QDateTime& ){ QArray<int> ints(0); return ints; } OTodo OTodoAccessBackendSQL::find(int uid ) const{ FindQuery query( uid ); return todo( m_driver->query(&query) ); } OTodo OTodoAccessBackendSQL::find( int uid, const QArray<int>& ints, uint cur, Frontend::CacheDirection dir ) const{ - int CACHE = readAhead(); + uint CACHE = readAhead(); qWarning("searching for %d", uid ); QArray<int> search( CACHE ); uint size =0; OTodo to; // we try to cache CACHE items switch( dir ) { /* forward */ case 0: // FIXME: Not a good style to use magic numbers here (eilers) for (uint i = cur; i < ints.count() && size < CACHE; i++ ) { qWarning("size %d %d", size, ints[i] ); search[size] = ints[i]; size++; } break; /* reverse */ case 1: // FIXME: Not a good style to use magic numbers here (eilers) for (uint i = cur; i != 0 && size < CACHE; i-- ) { search[size] = ints[i]; size++; } break; } search.resize( size ); @@ -451,56 +486,72 @@ bool OTodoAccessBackendSQL::date( QDate& da, const QString& str ) const{ 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(); qWarning("todo1"); OTodo to = todo( (*it) ); cache( to ); ++it; for ( ; it != list.end(); ++it ) { qWarning("caching"); cache( todo( (*it) ) ); } return to; } OTodo OTodoAccessBackendSQL::todo( OSQLResultItem& item )const { qWarning("todo"); - bool has = false; QDate da = QDate::currentDate(); - has = date( da, item.data("DueDate") ); + bool hasDueDate = false; QDate dueDate = QDate::currentDate(); + hasDueDate = date( dueDate, item.data("DueDate") ); QStringList cats = QStringList::split(";", item.data("categories") ); OTodo to( (bool)item.data("completed").toInt(), item.data("priority").toInt(), cats, item.data("summary"), item.data("description"), - item.data("progress").toUShort(), has, da, + item.data("progress").toUShort(), hasDueDate, dueDate, item.data("uid").toInt() ); + + bool isOk; + int prioInt = QString( item.data("priority") ).toInt( &isOk ); + if ( isOk ) + to.setPriority( prioInt ); + + bool hasStartDate = false; QDate startDate = QDate::currentDate(); + hasStartDate = date( startDate, item.data("startdate") ); + bool hasCompletedDate = false; QDate completedDate = QDate::currentDate(); + hasCompletedDate = date( completedDate, item.data("completeddate") ); + + if ( hasStartDate ) + to.setStartDate( startDate ); + if ( hasCompletedDate ) + to.setCompletedDate( completedDate ); + 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!!! * FIXME: Isn't this dict obsolete ? (eilers) */ 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) ); @@ -549,45 +600,52 @@ QArray<int> OTodoAccessBackendSQL::matchRegexp( const QRegExp &r ) const Copied from xml-backend by not adapted to sql (eilers) QArray<int> m_currentQuery( m_events.count() ); uint arraycounter = 0; QMap<int, OTodo>::ConstIterator it; for (it = m_events.begin(); it != m_events.end(); ++it ) { if ( it.data().match( r ) ) m_currentQuery[arraycounter++] = it.data().uid(); } // Shrink to fit.. m_currentQuery.resize(arraycounter); return m_currentQuery; #endif QArray<int> empty; return empty; } QBitArray OTodoAccessBackendSQL::supports()const { - static QBitArray ar = sup(); + QBitArray ar( OTodo::CompletedDate + 1 ); + ar.fill( true ); + ar[OTodo::CrossReference] = false; + ar[OTodo::State ] = false; + ar[OTodo::Reminders] = false; + ar[OTodo::Notifiers] = false; + ar[OTodo::Maintainer] = false; + return ar; } QBitArray OTodoAccessBackendSQL::sup() { QBitArray ar( OTodo::CompletedDate + 1 ); ar.fill( true ); ar[OTodo::CrossReference] = false; ar[OTodo::State ] = false; ar[OTodo::Reminders] = false; ar[OTodo::Notifiers] = false; ar[OTodo::Maintainer] = false; return ar; } void OTodoAccessBackendSQL::removeAllCompleted(){ #warning OTodoAccessBackendSQL::removeAllCompleted() not implemented !! } |