Switching to sqlite3

Please read the following page for upgrade your old sqlite V2 database:
http://opie.handhelds.org/cgi-bin/moin.cgi/SqlBackends

11 files changed, 34 insertions, 43 deletions

diff --git a/Rules.make b/Rules.make
index 2f32c04..c025d72 100644
--- a/Rules.make
+++ b/Rules.make
@@ -288,30 +288,30 @@ $(TOPDIR)/scripts/filesubst : force
 		echo 's,$(OPIEDIR),$(prefix),g'; \
 		echo 's,\$$QTDIR,$(prefix),g'; \
 		echo 's,$(QTDIR),$(prefix),g'; \
 		echo 's,^\(\./\)*root/,/,g'; \
 		echo 's,^\(\./\)*etc/,$(prefix)/etc/,g'; \
 		echo 's,^\(\./\)*lib/,$(prefix)/lib/,g'; \
 		echo 's,^\(\./\)*bin/,$(prefix)/bin/,g'; \
 		echo 's,^\(\./\)*pics/,$(prefix)/pics/,g'; \
 		echo 's,^\(\./\)*sounds/,$(prefix)/sounds/,g'; \
 		echo 's,^\(\./\)*i18n/,$(prefix)/i18n/,g'; \
 		echo 's,^\(\./\)*plugins/,$(prefix)/plugins/,g'; \
 		echo 's,^\(\./\)*apps/,$(prefix)/apps/,g'; \
 		echo 's,^\(\./\)*share/,$(prefix)/share/,g'; \
 		echo 's,^\(\./\)*i18n/,$(prefix)/i18n/,g'; \
 		echo 's,^\(\./\)*help/,$(prefix)/help/,g'; \
 	) > $@ || ( rm -f $@; exit 1 )
 
 ## general rules ##
 
 define descend
 	$(MAKE) $(if $(QMAKE),QMAKE=$(QMAKE)) -C $(1) $(2)
 endef
 
 define makefilegen
-	cd $(if $(1),$(dir $(1))); $(TOPDIR)/qmake/qmake $(3) -o $(if $(1),$(notdir $(1))) $(word 3,$(shell grep -m 1 "[[:space:]]$(patsubst %/Makefile,%,$(1))[[:space:]]" $(OPIEDIR)/packages))
+	cd $(if $(1),$(dir $(1))); $(TOPDIR)/qmake/qmake $(3) -o $(if $(1),$(notdir $(1))) $(word 3,$(shell grep "[[:space:]]$(patsubst %/Makefile,%,$(1))[[:space:]]" $(OPIEDIR)/packages))
 endef
 
 define makecfg
 	$(TOPDIR)/scripts/makecfg.pl $1 $(OPIEDIR)
 endef
diff --git a/dependencies.in b/dependencies.in
index 55b44e6..ce1ac96 100644
--- a/dependencies.in
+++ b/dependencies.in
@@ -2,49 +2,49 @@
     boolean "Have Libetpan >= 0.33pre"
     default "n"
 
   config LIBETPAN_LIB_DIR
     string "LIBETPAN library dir"
     depends LIBETPAN_DEP
 
   config LIBETPAN_INC_DIR
     string "LIBETPAN include dir"
     depends LIBETPAN_DEP
 
   config LIBPCAP_DEP
     boolean "Have libpcap >= 0.7.2"
     default "n"
 
   config LIBPCAP_LIB_DIR
     string "LIBPCAP library dir"
     depends LIBPCAP_DEP
 
   config LIBPCAP_INC_DIR
     string "LIBPCAP include dir"
     depends LIBPCAP_DEP
 
   config LIBSQLITE_DEP
-    boolean "Have sqlite >= 2.8.6"
+    boolean "Have sqlite >= 3.0.7"
     default n
 
   config LIBSQLITE_LIB_DIR
     string "LIBSQLITE library dir"
     depends  LIBSQLITE_DEP
 
   config LIBSQLITE_INC_DIR
     string "LIBSQLITE include dir"
     depends LIBSQLITE_DEP
 
   config LIBXINE_DEP
     boolean "Have libxine >= 1.0rc6"
     default "n"
 
   config LIBXINE_LIB_DIR
     string "LIBXINE library dir"
     depends LIBXINE_DEP
 
   config LIBXINE_INC_DIR
     string "LIBXINE include dir"
     depends LIBXINE_DEP
 
   config LIBIPK_DEP
     boolean "Have libipkg >= 0.99.120"
diff --git a/development/pim/dbpaper/intro.tex b/development/pim/dbpaper/intro.tex
index c456333..fd790d7 100644
--- a/development/pim/dbpaper/intro.tex
+++ b/development/pim/dbpaper/intro.tex
@@ -1,49 +1,49 @@
 \pagebreak
 \section{Introduction}
 
 \subsection{This document}
 
 Note: This paper is just a request for comment! Don't expect to find anything in the real implementation !
 \\
 This draft paper describes the database layout of the Opie PIM applications.
 Design goal is a fast and extendable layout on a sql database to support cross referencing.
 
 
 \subsection{Copyright}
 
 Copyright notice
-\copyright 2002, Maximilian Rei{\ss} \& Stefan Eilers \& Holger Freyther
+\copyright 2002-2004, Maximilian Rei{\ss} \& Stefan Eilers \& Holger Freyther
 
 \pagebreak
 \section{Relations}
 \subsection{About Cross Referencing and Horizontal Data Storage}
 Before you read on, it may be helpful to get some information about the solution of ``horizontal'' data storage into the tables and cross referencing.
 Some of the tables are just using 5 attributes:
 \begin{enumerate}
 \item UID: This countains an unique ID for the complete entry. An entry may consist of multiple
 rows, seperated by ID.
-\item ID: This defines a number to seperate the rows of an entry. UID + ID must be unique for every entry.
+\item ID: This defines a number to seperate the rows of an entry. UID + ID must be unique for every entry (primary key).
 \item Type: Describes the type of this entry.
 \item Priority: For instance: Your default email-address will get the priority 1 and the other 2.. . 0 means ``not defined''.
 \item Value: The data which was described by ``Type'' is stored here.
 \end{enumerate}
 
 These kinds of tables are very flexible in what they may contain, due to the fact that the ``Type'' may be without any constraint. Therefore it is perfectly for storing personal information, which may change in the future, or should be increased.
 \\
 Let's see how such an entry may look like for the table ``ADDRESSBOOK'':
 
 \begin{tabular}[ht]{|l|l|l|l|}
 \hline
 \underline{UID} & \underline{ID} & Type & Value\\
 \hline
  1231& 00& Title& Herr\\
 \hline
  1231& 01& FirstName& Stefan\\
 \hline
  1231& 02& LastName& Eilers\\
 \hline
 \end{tabular}
 \\
 \\
 We need some additional information about this entry, which is stored into the table ``PERSONAL\_DATA'':
 \\
diff --git a/libopie2/opiedb/config.in b/libopie2/opiedb/config.in
index 4d85609..0d74887 100644
--- a/libopie2/opiedb/config.in
+++ b/libopie2/opiedb/config.in
@@ -1,7 +1,7 @@
   config LIBOPIE2DB
     boolean "libopie2db (database related classes)"
     default "y"
     depends ( LIBQPE || LIBQPE-X11 ) && LIBOPIE2CORE && LIBSQLITE_DEP
-    comment "libopie2db needs a libqpe, sqlite and libopie2core"
+    comment "libopie2db needs a libqpe, libsqlite3 and libopie2core"
     depends !(( LIBQPE || LIBQPE-X11 ) && LIBOPIE2CORE && LIBSQLITE_DEP)
 
diff --git a/libopie2/opiedb/libopiedb2.control b/libopie2/opiedb/libopiedb2.control
index 4859c46..29fae78 100644
--- a/libopie2/opiedb/libopiedb2.control
+++ b/libopie2/opiedb/libopiedb2.control
@@ -1,10 +1,10 @@
 Package: libopiedb2
 Files: lib/libopiedb2.so*
 Priority: optional
 Section: opie/system
 Maintainer: Opie Team <opie@handhelds.org>
 Architecture: arm
 Version: 1.8.2-$SUB_VERSION.2
-Depends: libqpe1, libopiecore2 (1.8.2), libsqlite0 | sqlite
+Depends: libqpe1, libopiecore2 (1.8.2), libsqlite3 | sqlite3
 Provides: libopiedb2
-Description: Opie library 2.0 DB
+Description: Opie library 2.1 DB
diff --git a/libopie2/opiedb/opiedb.pro b/libopie2/opiedb/opiedb.pro
index 8432674..f727995 100644
--- a/libopie2/opiedb/opiedb.pro
+++ b/libopie2/opiedb/opiedb.pro
@@ -4,40 +4,40 @@ DESTDIR     = $(OPIEDIR)/lib
 HEADERS     = osqlbackend.h    \
               osqldriver.h     \
               osqlerror.h      \
               osqlmanager.h    \
               osqlquery.h      \
               osqlresult.h     \
               osqltable.h      \
               osqlbackendmanager.h \
               osqlitedriver.h
 
 SOURCES     = osqlbackend.cpp   \
               osqldriver.cpp   \
               osqlerror.cpp    \
               osqlmanager.cpp  \
               osqlquery.cpp    \
               osqlresult.cpp   \
               osqltable.cpp    \
               osqlbackendmanager.cpp \
               osqlitedriver.cpp
 
 TARGET      = opiedb2
 VERSION     = 1.9.0
 INCLUDEPATH = $(OPIEDIR)/include
 DEPENDPATH  = $(OPIEDIR)/include
-LIBS       += -lopiecore2 -lqpe -lsqlite
+LIBS       += -lopiecore2 -lqpe -lsqlite3
 
 !contains( platform, x11 ) {
   include ( $(OPIEDIR)/include.pro )
 }
 
 contains( platform, x11 ) {
   LIBS        += -L$(OPIEDIR)/lib -Wl,-rpath,$(OPIEDIR)/lib
 }
 
 !isEmpty( LIBSQLITE_INC_DIR ) {
     INCLUDEPATH = $$LIBSQLITE_INC_DIR $$INCLUDEPATH
 }
 !isEmpty( LIBSQLITE_LIB_DIR ) {
     LIBS = -L$$LIBSQLITE_LIB_DIR $$LIBS
 }
diff --git a/libopie2/opiedb/osqlitedriver.cpp b/libopie2/opiedb/osqlitedriver.cpp
index c8b560f..816223e 100644
--- a/libopie2/opiedb/osqlitedriver.cpp
+++ b/libopie2/opiedb/osqlitedriver.cpp
@@ -82,115 +82,119 @@ void OSQLiteDriver::setOptions( const QStringList& ) {
 /*
  * Functions to patch a regex search into sqlite
  */
 int sqliteRlikeCompare(const char *zPattern, const char *zString, sqregex *reg){
     int res;
     if (reg->regex_raw == NULL || (strcmp (zPattern, reg->regex_raw) != 0)){
         if (reg->regex_raw != NULL) {
             free(reg->regex_raw);
             regfree(&reg->regex_c);
         }
         reg->regex_raw = (char *)malloc(strlen(zPattern)+1);
         strncpy(reg->regex_raw, zPattern, strlen(zPattern)+1);
         res = regcomp(&reg->regex_c, zPattern, REG_EXTENDED);
         if ( res != 0 ) {
             printf("Regcomp failed with code %u on string %s\n",res,zPattern);
             free(reg->regex_raw);
             reg->regex_raw=NULL;
         return 0;
         }
     }
     res = (regexec(&reg->regex_c, zString, 0, NULL, 0)==0);
     return res;
 }
 
-void rlikeFunc(sqlite_func *context, int arg, const char **argv){
-    if( arg < 2 || argv[0]==0 || argv[1]==0 ){
-        printf("One of arguments Null!!\n");
+void rlikeFunc( sqlite3_context* context, int count, sqlite3_value** values ){
+    const unsigned char* argv0 = sqlite3_value_text( values[0] );
+    const unsigned char* argv1 = sqlite3_value_text( values[1] ); 
+    if( count < 2 || argv0 == 0 || argv1 == 0 ){
+	    qWarning( "One of arguments Null!!\n" );
         return;
     }
-        sqlite_set_result_int(context,
-        sqliteRlikeCompare((const char*)argv[0],
-    (const char*)argv[1], (sqregex *)sqlite_user_data(context) ));
+    sqlite3_result_int(context, sqliteRlikeCompare((const char*)argv0,
+						      (const char*)argv1, 
+						      (sqregex *) sqlite3_user_data( context ) ));
 }
 
 /*
  * try to open a db specified via setUrl
  * and options
  */
 bool OSQLiteDriver::open() {
-    char *error;
-
     odebug << "OSQLiteDriver::open: about to open" << oendl;
-    m_sqlite = sqlite_open(m_url.local8Bit(),
-                           0,
-                           &error );
+
+    int error = sqlite3_open( m_url.utf8(),
+                           &m_sqlite );
 
     /* failed to open */
-    if (m_sqlite == 0l ) {
+    if ( error != SQLITE_OK ) {
         // FIXME set the last error
         owarn << "OSQLiteDriver::open: " << error << "" << oendl;
-        free( error );
+        sqlite3_close( m_sqlite );
         return false;
     }
-    if (sqlite_create_function(m_sqlite,"rlike",2,rlikeFunc,&sqreg) != 0)
+    if ( sqlite3_create_function( m_sqlite, "rlike", 2, SQLITE_UTF8, &sqreg, rlikeFunc, NULL, NULL ) != SQLITE_OK ){
         odebug << "Unable to create user defined function!" << oendl;
-    if (sqlite_function_type(m_sqlite,"rlike",SQLITE_NUMERIC) != 0)
-        odebug << "Unable to set rlike function result type!" << oendl;
+	    return false;
+    }
+
     sqreg.regex_raw = NULL;
+
     return true;
 }
 
 
 /* close the db
  * sqlite closes them without
  * telling failure or success
  */
 bool OSQLiteDriver::close() {
-    if (m_sqlite )
-        sqlite_close( m_sqlite ), m_sqlite=0l;
+	if ( m_sqlite ){
+		sqlite3_close( m_sqlite );
+		m_sqlite=0l;
+	}
     if (sqreg.regex_raw != NULL){
         odebug << "Freeing regex on close" << oendl;
         free(sqreg.regex_raw);
         sqreg.regex_raw=NULL;
         regfree(&sqreg.regex_c);
     }
     return true;
 }
 
 
 /* Query */
 OSQLResult OSQLiteDriver::query( OSQLQuery* qu) {
     if ( !m_sqlite ) {
         // FIXME set error code
         OSQLResult result( OSQLResult::Failure );
         return result;
     }
     Query query;
     query.driver = this;
     char *err;
     /* SQLITE_OK 0 if return code > 0 == failure */
-    if ( sqlite_exec(m_sqlite, qu->query().utf8(),&call_back, &query, &err)  > 0 ) {
+	if ( sqlite3_exec( m_sqlite, qu->query().utf8(), &call_back, &query, &err )  > SQLITE_OK ) {
 	    owarn << "OSQLiteDriver::query: Error while executing " << err << "" << oendl;
 	    free( err );
 	    // FixMe Errors
     }
 
     OSQLResult result(OSQLResult::Success,
                       query.items,
                       query.errors );
     return result;
 }
 
 
 OSQLTable::ValueList OSQLiteDriver::tables() const {
     return OSQLTable::ValueList();
 }
 
 
 OSQLError OSQLiteDriver::lastError() {
     OSQLError error;
     return error;
 };
 
 
 /* handle a callback add the row to the global
diff --git a/libopie2/opiedb/osqlitedriver.h b/libopie2/opiedb/osqlitedriver.h
index e38fd52..4990a11 100644
--- a/libopie2/opiedb/osqlitedriver.h
+++ b/libopie2/opiedb/osqlitedriver.h
@@ -1,51 +1,51 @@
 #ifndef OSQL_LITE_DRIVER_H
 #define OSQL_LITE_DRIVER_H
 
-#include <sqlite.h>
+#include <sqlite3.h>
 #include <sys/types.h>
 #include <regex.h>
 
 #include "osqldriver.h"
 #include "osqlerror.h"
 #include "osqlresult.h"
 
 namespace Opie {
 namespace DB {
 namespace Internal {
 
 struct sqregex {
 	char *regex_raw;
 	regex_t regex_c;
 };
 
 class OSQLiteDriver : public OSQLDriver {
     Q_OBJECT
 public:
     OSQLiteDriver( QLibrary *lib = 0l );
     ~OSQLiteDriver();
     QString id()const;
     void setUserName( const QString& );
     void setPassword( const QString& );
     void setUrl( const QString& url );
     void setOptions( const QStringList& );
     bool open();
     bool close();
     OSQLError lastError();
     OSQLResult query( OSQLQuery* );
     OSQLTable::ValueList tables()const;
 
 private:
     OSQLError m_lastE;
     OSQLResult m_result;
     OSQLResultItem m_items;
     int handleCallBack( int, char**, char** );
     static int call_back( void*, int, char**, char** );
     QString m_url;
-    sqlite *m_sqlite;
+    sqlite3 *m_sqlite;
     sqregex sqreg;
 };
 }
 }
 }
 
 #endif
diff --git a/libopie2/opiepim/core/opimaccesstemplate.h b/libopie2/opiepim/core/opimaccesstemplate.h
index 7ab1ea5..f936d4e 100644
--- a/libopie2/opiepim/core/opimaccesstemplate.h
+++ b/libopie2/opiepim/core/opimaccesstemplate.h
@@ -157,49 +157,48 @@ public:
      * @param t The item to remove
      * @return <i>true</i> if successful.
      */
     virtual bool remove( const T& t );
 
     /**
      * remove the OPimRecord with uid
      * @param uid The ID of the item to remove
      * @return <i>true</i> if successful.
      */
     bool remove( int uid );
     bool remove( const OPimRecord& );
 
     /**
      * replace T from backend
      * @param t The item to replace
      * @return <i>true</i> if successful.
      */
     virtual bool replace( const T& t) ;
 
     void setReadAhead( uint count );
     /**
      * @internal
      */
-    virtual T cacheFind( int uid )const;
     void cache( const T& )const;
     void setSaneCacheSize( int );
 
     QArray<int> records()const;
 protected:
     /**
      * invalidate the cache
      */
     void invalidateCache();
 
     void setBackEnd( BackEnd* end );
     /**
      * returns the backend
      */
     BackEnd* backEnd();
     BackEnd* m_backEnd;
 
     Cache m_cache;
 
 private:
     OPimAccessTemplatePrivate *d;
 
 };
 
@@ -245,54 +244,48 @@ template <class T>
 QArray<int> OPimAccessTemplate<T>::records()const {
     return m_backEnd->allRecords();
 }
 template <class T>
 typename OPimAccessTemplate<T>::List
 OPimAccessTemplate<T>::queryByExample( const T& t, int settings, const QDateTime& d ) {
     QArray<int> ints = m_backEnd->queryByExample( t, settings, d );
 
     List lis(ints, this );
     return lis;
 }
 template <class T>
 T OPimAccessTemplate<T>::find( int uid ) const{
     // First search in cache..
     if ( m_cache.contains( uid ) )
 	    return m_cache.find( uid );
 
     T t = m_backEnd->find( uid );
     cache( t );
 
     return t;
 }
 
 template <class T>
-T OPimAccessTemplate<T>::cacheFind( int uid ) const
-{
-	return m_cache.find( uid );
-}
-
-template <class T>
 T OPimAccessTemplate<T>::find( int uid, const QArray<int>& ar,
                                uint current, typename OTemplateBase<T>::CacheDirection dir )const {
     /*
      * better do T.isEmpty()
      * after a find this way we would
      * avoid two finds in QCache...
      */
     // owarn << "find it now " << uid << oendl;
     if ( m_cache.contains( uid ) ) {
         return m_cache.find( uid );
     }
 
     T t = m_backEnd->find( uid, ar, current, dir );
     cache( t );
     return t;
 }
 template <class T>
 void OPimAccessTemplate<T>::clear() {
     invalidateCache();
     m_backEnd->clear();
 }
 template <class T>
 bool OPimAccessTemplate<T>::add( const T& t ) {
     cache( t );
diff --git a/libopie2/opiepim/core/opimcontactfields.h b/libopie2/opiepim/core/opimcontactfields.h
index 3aa3894..2e42951 100644
--- a/libopie2/opiepim/core/opimcontactfields.h
+++ b/libopie2/opiepim/core/opimcontactfields.h
@@ -1,27 +1,27 @@
 /*
                              This file is part of the Opie Project
-                             Copyright (C) The Main Author <main-author@whereever.org>
+                             Copyright (C) Stefan Eielrs <eilers.stefan@epost.de>
               =.             Copyright (C) The Opie Team <opie-devel@handhelds.org>
             .=l.
            .>+-=
  _;:,     .>    :=|.         This program is free software; you can
 .> <`_,   >  .   <=          redistribute it and/or  modify it under
 :`=1 )Y*s>-.--   :           the terms of the GNU Library General Public
 .="- .-=="i,     .._         License as published by the Free Software
  - .   .-<_>     .<>         Foundation; either version 2 of the License,
      ._= =}       :          or (at your option) any later version.
     .%`+i>       _;_.
     .i_,=:_.      -<s.       This program is distributed in the hope that
      +  .  -:.       =       it will be useful,  but WITHOUT ANY WARRANTY;
     : ..    .:,     . . .    without even the implied warranty of
     =_        +     =;=|`    MERCHANTABILITY or FITNESS FOR A
   _.=:.       :    :=>`:     PARTICULAR PURPOSE. See the GNU
 ..}^=.=       =       ;      Library General Public License for more
 ++=   -.     .`     .:       details.
  :     =  ...= . :.=-
  -.   .:....=;==+<;          You should have received a copy of the GNU
   -_. . .   )=.  =           Library General Public License along with
     --        :-=`           this library; see the file COPYING.LIB.
                              If not, write to the Free Software Foundation,
                              Inc., 59 Temple Place - Suite 330,
                              Boston, MA 02111-1307, USA.
diff --git a/libopie2/opiepim/core/opimtemplatebase.h b/libopie2/opiepim/core/opimtemplatebase.h
index ec9a94e..787486c 100644
--- a/libopie2/opiepim/core/opimtemplatebase.h
+++ b/libopie2/opiepim/core/opimtemplatebase.h
@@ -72,54 +72,48 @@ private:
 /**
  * internal template base
  * Attention: T needs to implement the copy c'tor!!!
  */
 class OTemplateBasePrivate;
 template <class T = OPimRecord>
 class OTemplateBase : public OPimBase {
 public:
     /** Look ahead direction of cache */ 
     enum CacheDirection { Forward=0, Reverse };
 
     OTemplateBase() {
     };
     virtual ~OTemplateBase() {
     }
     virtual T find( int uid )const = 0;
 
     /**
      * read ahead find
      */
     virtual T find( int uid, const QArray<int>& items,
                     uint current, CacheDirection dir = Forward )const = 0;
 
    /**
-    * Find in Cache..
-    * Returns empty object if nothing found.
-    */
-    virtual T cacheFind( int uid )const = 0;
-
-   /**
     * Put element into Cache
     */
     virtual void cache( const T& )const = 0;
     virtual void setSaneCacheSize( int ) = 0;
 
     OPimRecord* record()const;
     OPimRecord* record(int uid )const;
     static T* rec();
 
     
 private:
     OTemplateBasePrivate *d;    
 };
 
 
 template <class T>
 OPimRecord* OTemplateBase<T>::record()const {
     T* t = new T;
     return t;
 }
 template <class T>
 OPimRecord* OTemplateBase<T>::record(int uid )const {
     T  t2 = find(uid );
     T* t1 = new T(t2);