1 files changed, 175 insertions, 40 deletions

diff --git a/libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp b/libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp
index 9769bf7..e893b38 100644
--- a/libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp
+++ b/libopie2/opiepim/backend/odatebookaccessbackend_sql.cpp
@@ -11,37 +11,46 @@
  * =====================================================================
  * =====================================================================
  * Version: $Id$
  * =====================================================================
  * History:
  * $Log$
+ * Revision 1.2  2003/12/22 10:19:26  eilers
+ * Finishing implementation of sql-backend for datebook. But I have to
+ * port the PIM datebook application to use it, before I could debug the
+ * whole stuff.
+ * Thus, PIM-Database backend is finished, but highly experimental. And some
+ * parts are still generic. For instance, the "queryByExample()" methods are
+ * not (or not fully) implemented. Todo: custom-entries not stored.
+ * The big show stopper: matchRegExp() (needed by OpieSearch) needs regular
+ * expression search in the database, which is not supported by sqlite !
+ * Therefore we need either an extended sqlite or a workaround which would
+ * be very slow and memory consuming..
+ *
  * Revision 1.1  2003/12/08 15:18:12  eilers
  * Committing unfinished sql implementation before merging to libopie2 starts..
  *
  *
  */
 
 #include <stdio.h>
 #include <stdlib.h>
 
 #include <qarray.h>
 #include <qstringlist.h>
 
-#include "orecur.h"
-#include "odatebookaccessbackend_sql.h"
+#include <qpe/global.h>
 
 #include <opie2/osqldriver.h>
-#include <opie2/osqlresult.h>
 #include <opie2/osqlmanager.h>
 #include <opie2/osqlquery.h>
 
-namespace {
-
+#include "orecur.h"
+#include "odatebookaccessbackend_sql.h"
 
 
-};
 
 ODateBookAccessBackend_SQL::ODateBookAccessBackend_SQL( const QString& ,
                                                         const QString& fileName )
         : ODateBookAccessBackend(), m_driver( NULL ) 
 {
     m_fileName = fileName.isEmpty() ? Global::applicationFileName( "datebook", "datebook.db" ) : fileName;
@@ -54,12 +63,14 @@ ODateBookAccessBackend_SQL::ODateBookAccessBackend_SQL( const QString& ,
     initFields();
 
     load();
 }
 
 ODateBookAccessBackend_SQL::~ODateBookAccessBackend_SQL() {
+        if( m_driver )
+                delete m_driver;
 }
 
 void ODateBookAccessBackend_SQL::initFields()
 {
 
         // This map contains the translation of the fieldtype id's to 
@@ -75,19 +86,26 @@ void ODateBookAccessBackend_SQL::initFields()
         m_fieldMap.insert( OEvent::FRWeekdays, "RWeekdays" );
         m_fieldMap.insert( OEvent::FRPosition, "RPosition" );
         m_fieldMap.insert( OEvent::FRFreq, "RFreq" );
         m_fieldMap.insert( OEvent::FRHasEndDate, "RHasEndDate" );
         m_fieldMap.insert( OEvent::FREndDate, "REndDate" );
         m_fieldMap.insert( OEvent::FRCreated, "RCreated" );
-        m_fieldMap.insert( OEvent::FRExeptions, "RExceptions" );
+        m_fieldMap.insert( OEvent::FRExceptions, "RExceptions" );
         m_fieldMap.insert( OEvent::FStart, "Start" );
         m_fieldMap.insert( OEvent::FEnd, "End" );
         m_fieldMap.insert( OEvent::FNote, "Note" );
         m_fieldMap.insert( OEvent::FTimeZone, "TimeZone" );
         m_fieldMap.insert( OEvent::FRecParent, "RecParent" );
         m_fieldMap.insert( OEvent::FRecChildren, "Recchildren" );
+
+        // Create a map that maps the column name to the id
+        QMapConstIterator<int, QString> it;
+        for ( it = ++m_fieldMap.begin(); it != m_fieldMap.end(); ++it ){
+                m_reverseFieldMap.insert( it.data(), it.key() );
+        }
+        
 }
 
 bool ODateBookAccessBackend_SQL::load() 
 {
         if (!m_driver->open() )
                 return false;
@@ -96,18 +114,20 @@ bool ODateBookAccessBackend_SQL::load()
         // It is save here to create the table, even if it
         // do exist. ( Is that correct for all databases ?? )
                 QStringqu = "create table datebook( uid INTEGER PRIMARY KEY ";
         
         QMap<int, QString>::Iterator it;
         for ( it = ++m_fieldMap.begin(); it != m_fieldMap.end(); ++it ){
-                qu += QString( ",\"%1\" VARCHAR(10)" ).arg( it.data() );
+                qu += QString( ",%1 VARCHAR(10)" ).arg( it.data() );
         }
         qu += " );";
                 
         qu += "create table custom_data( uid INTEGER, id INTEGER, type VARCHAR, priority INTEGER, value VARCHAR, PRIMARY KEY /* identifier */ (uid, id) );";
 
+        qWarning( "command: %s", qu.latin1() );
+
         OSQLRawQuery raw( qu );
         OSQLResult res = m_driver->query( &raw );
         if ( res.state() != OSQLResult::Success )
                 return false;
 
         update();
@@ -119,47 +139,28 @@ void ODateBookAccessBackend_SQL::update()
 {
 
         QString qu = "select uid from datebook";
         OSQLRawQuery raw( qu );
         OSQLResult res = m_driver->query( &raw );
         if ( res.state() != OSQLResult::Success ){
-                m_uids.clear();
+                // m_uids.clear();
                 return;
         }
         
         m_uids = extractUids( res );
         
 }
 
-QArray<int> ODateBookAccessBackend_SQL::extractUids( OSQLResult& res ) const
-{
-        qWarning("extractUids");
-
-        OSQLResultItem::ValueList list = res.results();
-        OSQLResultItem::ValueList::Iterator it;
-        QArray<int> ints(list.count() );
-        qWarning(" count = %d", list.count() );
-
-        int i = 0;
-        for (it = list.begin(); it != list.end(); ++it ) {
-                ints[i] =  (*it).data("uid").toInt();
-                i++;
-        }
-
-        return ints;
-
-}
-
 bool ODateBookAccessBackend_SQL::reload() 
 {
     return load();
 }
 
 bool ODateBookAccessBackend_SQL::save() 
 {
-        return m_driver->close();
+        return m_driver->close();  // Shouldn't m_driver->sync be better than close ? (eilers)
 }
 
 QArray<int> ODateBookAccessBackend_SQL::allRecords()const 
 {
     return m_uids;
 }
@@ -173,49 +174,183 @@ void ODateBookAccessBackend_SQL::clear()
         QString qu = "drop table datebook;";
         qu += "drop table custom_data;";
 
         OSQLRawQuery raw( qu );
         OSQLResult res = m_driver->query( &raw );
 
+        reload();
 }
 
 
 OEvent ODateBookAccessBackend_SQL::find( int uid ) const{
+        QString qu = "select *";
+        qu += "from datebook where uid = " + QString::number(uid);
+
+        OSQLRawQuery raw( qu );
+        OSQLResult res = m_driver->query( &raw );
+
+        OSQLResultItem resItem = res.first();
+
+        // Create Map for date event and insert UID
+        QMap<int,QString> dateEventMap;
+        dateEventMap.insert( OEvent::FUid, QString::number( uid ) );
+
+        // Now insert the data out of the columns into the map.
+        QMapConstIterator<int, QString> it;
+        for ( it = ++m_fieldMap.begin(); it != m_fieldMap.end(); ++it ){
+                dateEventMap.insert( m_reverseFieldMap[*it], resItem.data( *it ) );
+        }
+
+        // Last step: Put map into date event and return it
+        OEvent retDate( dateEventMap );
+
+        return retDate;
+}
+
+bool ODateBookAccessBackend_SQL::add( const OEvent& ev ) 
+{
+        QMap<int,QString> eventMap = ev.toMap();
+
+        QString qu = "insert into datebook VALUES( " + QString::number( ev.uid() );
+        QMap<int, QString>::Iterator it;
+        for ( it = ++m_fieldMap.begin(); it != m_fieldMap.end(); ++it ){
+                if ( !eventMap[it.key()].isEmpty() )
+                        qu += QString( ",\"%1\"" ).arg( eventMap[it.key()] );
+                else
+                        qu += QString( ",\"\"" );
+        }
+        qu += " );";
+
+        // Add custom entries
+        int id = 0;
+        QMap<QString, QString> customMap = ev.toExtraMap();
+        for( QMap<QString, QString>::Iterator it = customMap.begin(); 
+             it != customMap.end(); ++it ){
+                qu  += "insert into custom_data VALUES(" 
+                        +  QString::number( ev.uid() )
+                        + ","
+                        +  QString::number( id++ ) 
+                        + ",'" 
+                        + it.key() //.latin1()
+                        + "',"
+                        + "0" // Priority for future enhancements
+                        + ",'" 
+                        + it.data() //.latin1()
+                        + "');";
+                }       
+        qWarning("add %s", qu.latin1() );
+
+        OSQLRawQuery raw( qu );
+        OSQLResult res = m_driver->query( &raw );
+        if ( res.state() != OSQLResult::Success ){
+                return false;
 }
 
-bool ODateBookAccessBackend_SQL::add( const OEvent& ev ) {
     return true;
 }
-bool ODateBookAccessBackend_SQL::remove( int uid ) {
+
+bool ODateBookAccessBackend_SQL::remove( int uid ) 
+{
+        QString qu = "DELETE from datebook where uid = " 
+                + QString::number( uid ) + ";";
+        qu += "DELETE from custom_data where uid = " 
+                + QString::number( uid ) + ";";
+        
+        OSQLRawQuery raw( qu );
+        OSQLResult res = m_driver->query( &raw );
+        if ( res.state() != OSQLResult::Success ){
+                return false;
+        }
 
     return true;
 }
-bool ODateBookAccessBackend_SQL::replace( const OEvent& ev ) {
+
+bool ODateBookAccessBackend_SQL::replace( const OEvent& ev ) 
+{
     remove( ev.uid() );
     return add( ev );
 }
-QArray<int> ODateBookAccessBackend_SQL::rawEvents()const {
+
+QArray<int> ODateBookAccessBackend_SQL::rawEvents()const 
+{
     return allRecords();
 }
-QArray<int> ODateBookAccessBackend_SQL::rawRepeats()const {
 
-    return ints;
+QArray<int> ODateBookAccessBackend_SQL::rawRepeats()const 
+{
+        QString qu = "select uid from datebook where RType!=\"\" AND RType!=\"NoRepeat\"";
+        OSQLRawQuery raw( qu );
+        OSQLResult res = m_driver->query( &raw );
+        if ( res.state() != OSQLResult::Success ){
+                QArray<int> nix;
+                return nix;
 }
-QArray<int> ODateBookAccessBackend_SQL::nonRepeats()const {
 
-    return ints;
+        return extractUids( res );
+}
+
+QArray<int> ODateBookAccessBackend_SQL::nonRepeats()const 
+{
+        QString qu = "select uid from datebook where RType=\"\" or RType=\"NoRepeat\"";
+        OSQLRawQuery raw( qu );
+        OSQLResult res = m_driver->query( &raw );
+        if ( res.state() != OSQLResult::Success ){
+                QArray<int> nix;
+                return nix;
+        }
+        
+        return extractUids( res );
+}
+
+OEvent::ValueList ODateBookAccessBackend_SQL::directNonRepeats() 
+{
+        QArray<int> nonRepUids = nonRepeats();
+        OEvent::ValueList list;
+
+        for (uint i = 0; i < nonRepUids.count(); ++i ){
+                list.append( find( nonRepUids[i] ) );
 }
-OEvent::ValueList ODateBookAccessBackend_SQL::directNonRepeats() {
 
     return list;
+
+}
+OEvent::ValueList ODateBookAccessBackend_SQL::directRawRepeats() 
+{
+        QArray<int> rawRepUids = rawRepeats();
+        OEvent::ValueList list;
+
+        for (uint i = 0; i < rawRepUids.count(); ++i ){
+                list.append( find( rawRepUids[i] ) );
 }
-OEvent::ValueList ODateBookAccessBackend_SQL::directRawRepeats() {
 
     return list;
 }
 
 
 QArray<int> ODateBookAccessBackend_SQL::matchRegexp(  const QRegExp &r ) const
 {
+        QArray<int> null;
+        return null;
+}
+
+/* ===== Private Functions ========================================== */
+
+QArray<int> ODateBookAccessBackend_SQL::extractUids( OSQLResult& res ) const
+{
+        qWarning("extractUids");
+        QTime t;
+        t.start();
+        OSQLResultItem::ValueList list = res.results();
+        OSQLResultItem::ValueList::Iterator it;
+        QArray<int> ints(list.count() );
+        qWarning(" count = %d", list.count() );
+
+        int i = 0;
+        for (it = list.begin(); it != list.end(); ++it ) {
+                ints[i] =  (*it).data("uid").toInt();
+                i++;
+        }
+        qWarning("extractUids ready: count2 = %d needs %d ms", i, t.elapsed() );
+
+        return ints;
 
-    return m_currentQuery;
 }