Oovcde Index

SQLite Interface with C++

I recently got a request to provide information in a database with the Oovcde project. I wanted to make it optional since I want very few dependencies, and very little project setup for users that do not need database access.

It turns out that it is possible to use the SQLite database with no compile or link dependencies. For the users that wish to use SQLite, they can download the SQLite Windows .dll or Linux .so. This method also does not require any special tools to build a .DEF file or an import library. It also does not require any source files from the SQLite project. SQLite is nice because it does not require any administration even for users that will use the database.

The minimal C style interface needed to use SQLite is the following.

    // This is the C style interface to the run-time library.
    extern "C"
    {
    typedef struct sqlite3 sqlite3;
    typedef int (*SQLite_callback)(void*,int,char**,char**);

    struct SQLiteInterface
        {
        int (*sqlite3_open)(const char *filename, sqlite3 **ppDb);
        int (*sqlite3_close)(sqlite3 *pDb);
        int (*sqlite3_exec)(sqlite3 *pDb, const char *sql,
            SQLite_callback callback, void *callback_data,
            char **errmsg);
        void (*sqlite3_free)(void*);
        };
    };

The method used to load the function pointers is to use gmodule or something similar. I haven't shown the exact code for this, but it is similar to using g_module_symbol, dlsym, or GetProcAddress. The full source code can be found at the Oovcde project.

    class SQLiteImporter:public SQLiteInterface, public OovLibrary
        {
        public:
            void loadSymbols()
                {
                loadModuleSymbol("sqlite3_open", (OovProcPtr*)&sqlite3_open);
                loadModuleSymbol("sqlite3_close", (OovProcPtr*)&sqlite3_close);
                loadModuleSymbol("sqlite3_exec", (OovProcPtr*)&sqlite3_exec);
                // This must be called for returned error strings.
                loadModuleSymbol("sqlite3_free", (OovProcPtr*)&sqlite3_free);
                }
        };

A callback listener can be defined like the following. This is called when results are returned from a query or an error is generated.

    /// Functions that return errors and results will call functions in this
    /// listener.
    class SQLiteListener
        {
        public:
            virtual void SQLError(int retCode, char const *errMsg) = 0;
            /// This is called for each row returned from a query, so it
	    /// can be called many times after a single exec call.
            virtual void SQLResultCallback(int numColumns, char **colVal,
                char **colName) = 0;
        };

Errors are returned from SQLite as codes, and as strings. If the numeric codes are needed, then it probably will require using sqlite3.h. If the error strings are used, then the codes may not be needed.

    // This is normally defined in sqlite3.h, so if more error codes are needed,
    // get them from there.
    #define SQLITE_OK 0

Then the C++ interface can look something like the following.

    /// This is a wrapper class for the SQLite functions.
    class SQLite:public SQLiteImporter
    {
    public:
        SQLite():
            mDb(nullptr), mListener(nullptr)
            {}
        ~SQLite()
            {
            close();
            }
        void setListener(SQLiteListener *listener)
            { mListener = listener; }
        // The libName is usually libsqlite3.so.? on linux, and sqlite3.dll on windows.
        bool loadDbLib(char const *libName)
            {
            close();
            bool success = OovLibrary::open(libName);
            if(success)
                {
                loadSymbols();
                }
            return success;
            }
        /// The dbName is the name of the file that will be saved.
        bool openDb(char const *dbName)
            {
            int retCode = sqlite3_open(dbName, &mDb);
            return handleRetCode(retCode, "Unable to open database");
            }
        /// Execute an SQL query.  If there are results, they will be reported
        /// through the listener.
        bool execDb(const char *sql)
            {
            char *errMsg = nullptr;
            int retCode = sqlite3_exec(mDb, sql, &resultsCallback, this, &errMsg);
            bool success = handleRetCode(retCode, errMsg);
            if(errMsg)
                {
                sqlite3_free(errMsg);
                }
            return success;
            }
        /// This is called from the destructor, so does not need an additional
        /// call unless it must be closed early.
        void closeDb()
            {
            if(mDb)
                {
                sqlite3_close(mDb);
                mDb = nullptr;
                }
            }

    private:
        sqlite3 *mDb;
        SQLiteListener *mListener;

        /// This is called from the sqlite3_exec call, and sends the results to
        /// the listener.
        static int resultsCallback(void *customData, int numColumns,
            char **colValues, char **colNames)
            {
            SQLite *sqlite = static_cast<SQLite*>(customData);
            if(sqlite->mListener)
                {
                sqlite->mListener->SQLResultCallback(numColumns, colValues, colNames);
                }
            return 0;
            }
        /// If the retCode indicates an error, then the errStr is sent to the
        /// listener.
        bool handleRetCode(int retCode, char const *errStr)
            {
            if(retCode != SQLITE_OK && mListener)
                {
                mListener->SQLError(retCode, errStr);
                }
            return(retCode == SQLITE_OK);
            }
    };