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..

1 files changed, 175 insertions, 40 deletions

diff --git a/libopie/pim/odatebookaccessbackend_sql.cpp b/libopie/pim/odatebookaccessbackend_sql.cpp
index 9769bf7..e893b38 100644
--- a/libopie/pim/odatebookaccessbackend_sql.cpp
+++ b/libopie/pim/odatebookaccessbackend_sql.cpp
@@ -14,6 +14,18 @@
  * =====================================================================
  * 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..
  *
@@ -26,19 +38,16 @@
 #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 )
@@ -57,6 +66,8 @@ ODateBookAccessBackend_SQL::ODateBookAccessBackend_SQL( const QString& ,
 }
 
 ODateBookAccessBackend_SQL::~ODateBookAccessBackend_SQL() {
+	if( m_driver )
+		delete m_driver;
 }
 
 void ODateBookAccessBackend_SQL::initFields()
@@ -78,13 +89,20 @@ void ODateBookAccessBackend_SQL::initFields()
 	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() 
@@ -99,12 +117,14 @@ bool ODateBookAccessBackend_SQL::load()
 	
 	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 )
@@ -122,7 +142,7 @@ void ODateBookAccessBackend_SQL::update()
 	OSQLRawQuery raw( qu );
 	OSQLResult res = m_driver->query( &raw );
 	if ( res.state() != OSQLResult::Success ){
-		m_uids.clear();
+		// m_uids.clear();
 		return;
 	}
 	
@@ -130,25 +150,6 @@ void ODateBookAccessBackend_SQL::update()
 	
 }
 
-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();
@@ -156,7 +157,7 @@ bool ODateBookAccessBackend_SQL::reload()
 
 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 
@@ -176,39 +177,150 @@ void ODateBookAccessBackend_SQL::clear()
 	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;
 }
@@ -216,6 +328,29 @@ OEvent::ValueList ODateBookAccessBackend_SQL::directRawRepeats() {
 
 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;
 }