SqliteConnection & SqliteResultset

Useful Tools and Components
1

Hi !
i’ve coded this some times ago cause i needed a easy class to handle a internal database
for an application. feel free to use, but i would like to know how can i extend it and keeping
it clean and maybe make it easy to use too. what do you think ? any ideas ?

if you want also, i have an extended version of this class that can use encrypted sqlite databases
files using the juce RSAKey encryption, so actually the sqlite database is decoded from an
encrypted file in memory and then it is stored back to disk with encryption.

/*
 ==============================================================================

 Sqlite classes for JUCE

 ------------------------------------------------------------------------------

 Copyright (c) 2010, Lucio Asnaghi
 All rights reserved.

 Redistribution and use in source and binary forms, with or without modification,
 are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice, this
      list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright notice,
      this list of conditions and the following disclaimer in the documentation
      and/or other materials provided with the distribution.
    * Neither the name of Lucio Asnaghi nor the names of its contributors may
      be used to endorse or promote products derived from this software without
      specific prior written permission.

 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR
 CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
 OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
 WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
 OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
 ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

 ==============================================================================
*/

#ifndef __JUCETICE_SQLITE_HEADER__
#define __JUCETICE_SQLITE_HEADER__

#include <juce.h>

class SqliteConnection;

//==============================================================================
class SqliteResultset
{
public:

    //==============================================================================
    enum ColumnType
    {
        Unknown   = 0,
        Null      = 1,
        Integer   = 2,
        Double    = 3,
        Text      = 4,
        Blob      = 5
    };
    
    //==============================================================================
    /** Destructor */
    virtual ~SqliteResultset ();

    //==============================================================================
    /** Returns if the resultset is actually a valid one */
    bool isValid () const;

    /** Returns the string with the recordset error */
    const String getErrorText () const;

    //==============================================================================
    /** Advances to the next value

        This function is to iterate all over the lines in the recordset.
        If no actual records are found, false will be returned
    */
    bool next ();

    //==============================================================================
    /** Returns the number of columns in the recordset */
    int columnCount ();

    /** Returns the type of a specified column */
    const ColumnType getColumnType (const int columnIndex);
    
    /** Returns the name of a specified column */
    const String getColumnName (const int columnIndex);

    //==============================================================================
    /** Returns if a column is null */    
    bool columnIsNull (const int columnIndex);

    /** Interpret the columns as integer */    
    int columnAsInteger (const int columnIndex);

    /** Interpret the column as double */    
    double columnAsDouble (const int columnIndex);

    /** Interpret the column as String */    
    const String columnAsText (const int columnIndex);

    /** Return the memory of the blob field */    
    const void* columnAsBlob (const int columnIndex, int& bytesRead);

private:

    /** Private Constructor */
    SqliteResultset (SqliteConnection* connection,
                     void* statement,
                     const String& errorText);

    friend class SqliteConnection;

    SqliteConnection* connection;
    void* statement;
    String errorText;
};


//==============================================================================
/** Class for connecting to a sqlite database

  This class let you handle a local sqlite connection to store your data.

 @code

   SqliteConnection conn;

   if (conn.openDatabaseFile (File("mydb.sqlite")) == String::empty)
   {
     ScopedPointer<SqliteResultset> rs(conn.executeQuery("SELECT id, name FROM abc"));

     if (rs->isValid())
     {
       while (rs->next())
       {
         int id = rs->columnAsInt(0);
         String name = rs->columnAsString(1);
       }
     }
   }

 @endcode
*/
class SqliteConnection
{
public:

    //==============================================================================
    /** Constructor */
    SqliteConnection ();

    /** Destructor */
    ~SqliteConnection ();

    //==============================================================================
    /** Opens the connection to the SQLite database.
    
        Opens the connection to the SQLite database. Clients must call this method
        before "executeQuery()" or they will get a runtime error.
    
    */
    const String openDatabaseFile (const File& file);

    //==============================================================================
    /** Executes the query passed as parameter.

        This is the heart of the class; this method takes a SQL query
        in a string and sets the internal state of the class to inform clients
        about the result of the query.
        
        Remember to not store the returning resultset, which may live only if this
        instance of 
        
        @param query             The query to execute.
        @return SqliteResultset  the resultset class instance even in case of failure
    */
    SqliteResultset* executeQuery (const String& sqlText);

    //==============================================================================
    /** Executes the query passed as parameter.

        This is the heart of the class; this method takes a SQL query
        in a string and sets the internal state of the class to inform clients
        about the result of the query.

        Remember to not store the returning resultset, which may live only if this
        instance of

        @param query             The query to execute.
        @return SqliteResultset  the resultset class instance even in case of failure
    */
    const String executeCommands (const String& sqlCommandText);

    //==============================================================================
    /** Returns the last inserted row id

        This can come in handy after a plain INSERT statement to return the
        last inserted rowid.

        @see lastInsertIntColumn
    */
    int64 lastInsertRowID();

    /** Returns the last inserted value for a integer column

        This can come in handy after a plain INSERT statement to return the
        last inserted value for a column, typically an autoincrement primary
        key but this can be anything.

        @see lastInsertRowID
    */
    int lastInsertIntColumn (const String& columnName);

    //==============================================================================
    /** Returns the column names of the table passed as parameter.
        
        Returns a map with the name and type of the columns of the table
        whose name is passed as parameter.
    
        @param tableName         The name of the table whose schema is sought.
        @return A StringPairArray with pairs representing: [column name = column type]
    */
    const StringPairArray getTableSchema (const String& tableName);

    /** Returns if a table exists in the schema.

        Returns true if the table table whose name is passed as parameter exists
        or not.

        @param tableName         The name of the table whose schema is sought.
        @return a boolean representing the table existence
    */
    bool tableExists (const String& tableName);

private:

    friend class SqliteResultset;

    /** Removes a resultset from our list, eventually freeing the object memory */
    void removeResultset (SqliteResultset* resultSet, const bool deleteObject = false);

    void* database;
    File databaseFile;
    CriticalSection queriesLock;
    OwnedArray<SqliteResultset> queries;
};

#endif // __JUCETICE_SQLITE_HEADER__

/*
 ==============================================================================

 Sqlite classes for JUCE

 ------------------------------------------------------------------------------

 Copyright (c) 2010, Lucio Asnaghi
 All rights reserved.

 Redistribution and use in source and binary forms, with or without modification,
 are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice, this
      list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright notice,
      this list of conditions and the following disclaimer in the documentation
      and/or other materials provided with the distribution.
    * Neither the name of Lucio Asnaghi nor the names of its contributors may
      be used to endorse or promote products derived from this software without
      specific prior written permission.

 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
 TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
 PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR
 CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
 OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
 WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
 OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
 ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

 ==============================================================================
*/

extern "C" {
  #include <sqlite3.h>
}

#include "Sqlite.h"

//==============================================================================
SqliteResultset::SqliteResultset (SqliteConnection* connection_,
                                  void* statement_,
                                  const String& errorText_)
    : connection (connection_),
      statement ((sqlite3_stmt*) statement_),
      errorText (errorText_)
{
}

SqliteResultset::~SqliteResultset ()
{
    if (statement)
    {
        ::sqlite3_finalize((sqlite3_stmt*) statement);
    }

    if (connection)
    {
        connection->removeResultset (this, false);
    }
}

bool SqliteResultset::isValid () const
{
    return statement != 0;
}

const String SqliteResultset::getErrorText () const
{
    return errorText;
}

bool SqliteResultset::next ()
{
    const bool returnValue = (::sqlite3_step ((sqlite3_stmt*) statement) == SQLITE_ROW);

    if (! returnValue)
    {
      errorText = ::sqlite3_errmsg ((sqlite3*)connection->database);
    }

    return returnValue;
}

int SqliteResultset::columnCount ()
{
    return ::sqlite3_column_count ((sqlite3_stmt*) statement);
}

const SqliteResultset::ColumnType SqliteResultset::getColumnType (const int columnIndex)
{
    int columnType = ::sqlite3_column_type ((sqlite3_stmt*) statement, columnIndex);

    switch (columnType) {
        case SQLITE_NULL:    return SqliteResultset::Null;
        case SQLITE_INTEGER: return SqliteResultset::Integer;
        case SQLITE_FLOAT:   return SqliteResultset::Double;
        case SQLITE_TEXT:    return SqliteResultset::Text;
        case SQLITE_BLOB:    return SqliteResultset::Blob;
        default:             return SqliteResultset::Unknown;
    }
}

const String SqliteResultset::getColumnName (int columnIndex)
{
    return String (::sqlite3_column_name ((sqlite3_stmt*) statement, columnIndex));
}

bool SqliteResultset::columnIsNull (const int columnIndex)
{
    return ::sqlite3_column_type ((sqlite3_stmt*) statement, columnIndex) == SQLITE_NULL;
}

int SqliteResultset::columnAsInteger (const int columnIndex)
{
    return ::sqlite3_column_int ((sqlite3_stmt*) statement, columnIndex);
}

double SqliteResultset::columnAsDouble (const int columnIndex)
{
    return ::sqlite3_column_double ((sqlite3_stmt*) statement, columnIndex);
}

const String SqliteResultset::columnAsText (const int columnIndex)
{
    return String::fromUTF8(reinterpret_cast<const unsigned char*>(
      ::sqlite3_column_text((sqlite3_stmt*) statement, columnIndex)));
}

const void* SqliteResultset::columnAsBlob (const int columnIndex, int& bytesRead)
{
    bytesRead = ::sqlite3_column_bytes ((sqlite3_stmt*) statement, columnIndex);

    return ::sqlite3_column_blob ((sqlite3_stmt*) statement, columnIndex);
}


//==============================================================================
SqliteConnection::SqliteConnection ()
    : database (0)
{
}

SqliteConnection::~SqliteConnection ()
{
    if (database)
    {
        std::cout << (const char*) executeCommands ("VACUUM") << std::endl;

        ::sqlite3_close ((sqlite3*) database);
    }
}

const String SqliteConnection::openDatabaseFile (const File& file)
{
    String errorText;

    if (database)
    {
        databaseFile = File::nonexistent;
        ::sqlite3_close ((sqlite3*) database);
    }

    sqlite3* db = 0;
    int createFlags = SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;

    if (::sqlite3_open_v2((const char*) file.getFullPathName(), &db, createFlags, 0))
    {
      errorText = "Error connecting to " + file.getFullPathName();
    }
    else
    {
      database = db;
      databaseFile = file;
    }

    return errorText;
}

SqliteResultset* SqliteConnection::executeQuery (const String& sqlText)
{
    sqlite3_stmt* statement = 0;
    String errorText;

    if (database)
    {
        int rc = ::sqlite3_prepare_v2 ((sqlite3*) database, (const char*) sqlText, -1, &statement, 0);
        if (rc != SQLITE_OK)
        {
            errorText = (const char*) ::sqlite3_errmsg ((sqlite3*) database);
        }
    }

    ScopedLock sl (queriesLock);

    SqliteResultset* queryResultSet = new SqliteResultset (this, statement, errorText);
    queries.add (queryResultSet);

    return queryResultSet;
}

const String SqliteConnection::executeCommands (const String& sqlCommandText)
{
    sqlite3_stmt* statement = 0;
    String errorText;

    if (database)
    {
        int rc = ::sqlite3_prepare_v2 ((sqlite3*) database, (const char*) sqlCommandText, -1, &statement, 0);
        if (rc != SQLITE_OK)
        {
            errorText = (const char*) ::sqlite3_errmsg ((sqlite3*) database);
        }
        else
        {
            while (::sqlite3_step ((sqlite3_stmt*) statement) == SQLITE_ROW)
            {
                if (::sqlite3_errmsg ((sqlite3*) database) != NULL)
                {
                    errorText = (const char*) ::sqlite3_errmsg ((sqlite3*) database);
                    break;
                }
            }
        }

        if (statement)
        {
            ::sqlite3_finalize((sqlite3_stmt*) statement);
        }
    }

    return errorText;
}

int64 SqliteConnection::lastInsertRowID()
{
    return (int64)::sqlite3_last_insert_rowid ((sqlite3*) database);
}

int SqliteConnection::lastInsertIntColumn(const String& columnName)
{
    String sqlString;
    sqlString = "SELECT " + columnName + " WHERE rowid = last_insert_rowid()";

    ScopedPointer<SqliteResultset> rs (executeQuery(sqlString));

    if (rs->getErrorText() == String::empty && rs->next())
    {
        return rs->columnAsInteger(0);
    }

    return -1;
}

const StringPairArray SqliteConnection::getTableSchema (const String& tableName)
{
    String query;
    query << "PRAGMA table_info(\"";
    query << tableName;
    query << "\");";

    StringPairArray schema;

    ScopedPointer<SqliteResultset> rs (executeQuery(query));

    if (rs->getErrorText() == String::empty)
    {
        while (rs->next())
        {
            schema.set(rs->columnAsText(1), rs->columnAsText(2));
        }
    }

    return schema;
}

bool SqliteConnection::tableExists (const String& tableName)
{
   ScopedPointer<SqliteResultset> rs
     (executeQuery("SELECT name FROM sqlite_master WHERE name='" + tableName + "'"));

   if (rs->getErrorText() != String::empty)
     return false;

   if (rs->next() && rs->columnAsText(0).equalsIgnoreCase(tableName))
     return true;

   return false;
}

void SqliteConnection::removeResultset (SqliteResultset* resultSet, const bool deleteObject)
{
    ScopedLock sl (queriesLock);

    if (queries.contains (resultSet))
    {
        queries.removeObject (resultSet, deleteObject);
    }
}
2

Neat!

3

thanx man, it still needs some refinement here and there to be a perfect juce class, but i’m striving for perfection…

4

did you already look at sqlite3x?
http://int64.org/projects/sqlite-c-wrapper
http://wanderinghorse.net/computing/sqlite/

5

I have been using sqlite since 3 years… It has some drawbacks… Just check the documentation of sqlite.

6

[quote=“mdsp”]did you already look at sqlite3x?
http://int64.org/projects/sqlite-c-wrapper
http://wanderinghorse.net/computing/sqlite/[/quote]

cool classes thanx ! i will definately take a look.

7

very good!

8

Thanks.I will learn to use it.

BTW,besides the already implemented function next(),how to implement the other navigation functions of a result set,say resultset.previous(),first(),last()?

Regards,
Sam

9

next() is only a helper function that allows you to iterates over the results one at a time while they are being processed instead of blocking, waiting for all the results (potentially huge) of your query.
so prev(), first() and last() don’t make any sense at all, it’s up to you to store those results in a container, group results by page… etc

This is also the recommended way to implement pagination, rather than specifying a limit directly in the SQL query

10

Dude can you share the code for the extended classes for sqlite database encrypted using RSA which you were talking…
It would be of great help…
thanks

11

I found this wrapper to be the best:

http://soci.sourceforge.net/

It has REALLY easy to use syntax, especially with binds and parameters!

This actually compiles and works:

int id = ...;
Person p;

sql << "select first_name, last_name, date_of_birth "
       "from persons where id = " << id,
       into(p);
12

VFLib now comes with a module for SQLite, which comes with its own full, zero-configuration copy of SQLite if you use it on platforms that don’t have SQLite built in like iOS. There’s also the soci wrapper in the module vf_db, with bindings for native Juce types so you can do something like “SELECT name FROM employees” << into(name) where name is a String: