DB/SQLiteDB.cpp

Go to the documentation of this file.

/*
 * Copyright (C) 2007-2010 Guy Hachlili <[email protected]>
 * 
 * Used with permission in CC PDF Converter and Excel to PDF Converter
 *
 */


#include "precomp.h"
#include "SQLiteDB.h"

namespace SQLite
{

class Realset;
class RealStatement;

class RealRecord : protected STRARRAY
{
public:
    // Ctors/Dtor
    RealRecord(Realset* pSet, char** pData, int nColumns) : m_pSet(pSet) {for (int i=0;i<nColumns;i++) push_back((pData[i] != NULL) ? MakeTString(pData[i]) : _T(""));};
    RealRecord(Realset* pSet, RealStatement* pStatement, int nColumns);
    virtual ~RealRecord() {};
    friend class Record;
    friend class Realset;

protected:
    // Members
    Realset*    m_pSet;

public:
    // Data Access
    std::tstring GetField(int nColumn) const {return STRARRAY::operator[](nColumn);};
    std::tstring GetField(const std::tstring& sName) const;
};

class Realset : public RefObj
{
public:
    // Ctors/Dtor
    Realset(sqlite3* pDB, LPCTSTR pSQL) {m_bValid = (sqlite3_exec(pDB, MakeAnsiString(pSQL).c_str(), QueryCallback, (void*)this, NULL) == SQLITE_OK); if (!IsValid()) Clean();};
    Realset(RealStatement* pStatement);
protected:
    ~Realset() {Clean();};

protected:
    // Definions
    typedef std::vector<RealRecord*> RECORDARRAY;

protected:
    // Members
    STR2INT     m_mColumns;
    RECORDARRAY m_arRecords;
    bool        m_bValid;

public:
    // Data Access
    int         GetColumn(const std::tstring& sName) const {STR2INT::const_iterator i = m_mColumns.find(sName); if (i == m_mColumns.end()) return -1; return (*i).second;};
    RealRecord* GetRecord(int nNum) const {if ((nNum < 0) || (nNum >= (int)m_arRecords.size())) return NULL; return m_arRecords[nNum];};
    int         GetRecordCount() const {return (int)m_arRecords.size();};
    bool        IsValid() const {return m_bValid;};
    int         GetColumnCount() const {return (int)m_mColumns.size();};
    std::tstring GetColumnName(int nNum) const {for (STR2INT::const_iterator i = m_mColumns.begin(); i != m_mColumns.end(); i++) if ((*i).second == nNum) return (*i).first; return _T("");};

private:
    // Helpers
    int         AddRecord(int nColumns, char** pData, char** pColumns);
    void        Clean();

    // Callbacks
    static int  QueryCallback(void* pThis, int nColumns, char** pData, char** pColumns) {return ((Realset*)pThis)->AddRecord(nColumns, pData, pColumns);};
};

void Realset::Clean()
{
    ASSERT(m_nRef == 0);
    for (int i=0;i < (int)m_arRecords.size();i++) 
        delete m_arRecords[i];
}

int Realset::AddRecord(int nColumns, char** pData, char** pColumns)
{
    // Make sure this is a valid record:
    int i;
    for (i=0;i<nColumns;i++)
        if (pData[i] != NULL)
            break;
    if (i == nColumns)
        // Didn't find any values, just move on
        return 0;

    // Is this the first record?
    if (!m_mColumns.empty())
    {
        // No, make sure we have the same number of columns!
        if (m_mColumns.size() != nColumns)
        {
            ASSERT(FALSE);
            return 1;
        }
    }
    else
    {
        // OK, remember the names of the columns
        ASSERT(m_arRecords.empty());
        for (int i=0;i<nColumns;i++)
            m_mColumns[MakeTString(pColumns[i])] = i;
    }

    // Add a new record
    m_arRecords.push_back(new RealRecord(this, pData, nColumns));
    return 0;
}



Record Recordset::GetRecord(int nNum) const 
{
    // Is this record set valid?
    if (!IsValid()) 
        // No, just return an empty record
        return Record(); 
    // Retrieve the record
    return Record(m_pSet->GetRecord(nNum));
}

bool Recordset::IsValid() const 
{
    return (m_pSet != NULL) && m_pSet->IsValid();
}

int Recordset::GetRecordCount() const 
{
    if (!IsValid())
        // No records in an invalid set
        return 0;
    return m_pSet->GetRecordCount();
}

void Recordset::AddRef() const
{
    if (m_pSet != NULL) 
        m_pSet->AddRef();
}

void Recordset::DecRef() const
{
    if (m_pSet != NULL)
        m_pSet->DecRef();
}

int Recordset::GetColumnCount() const
{
    if (!IsValid())
        // Not valid - no fields
        return 0;
    return m_pSet->GetColumnCount();
}

std::tstring Recordset::GetColumnName(int nNum) const
{
    if (!IsValid())
        // Not valid - no name
        return _T("");
    return m_pSet->GetColumnName(nNum);
}



std::tstring RealRecord::GetField(const std::tstring& sName) const
{
    // Get the ordinal of the column
    int nColumn = m_pSet->GetColumn(sName);
    if (nColumn == -1)
        // Not found, empty value!
        return _T("");
    return STRARRAY::operator[](nColumn);
}





std::tstring Record::operator[](int nColumn) const 
{
    return m_pRecord->GetField(nColumn);
}

std::tstring Record::operator[](const std::tstring& sName) const 
{
    return m_pRecord->GetField(sName);
}

std::tstring Record::GetField(int nColumn) const 
{
    return m_pRecord->GetField(nColumn);
}

std::tstring Record::GetField(const std::tstring& sName) const 
{
    return m_pRecord->GetField(sName);
}

void Record::AddRef() const
{
    if (m_pRecord != NULL) 
        // We keep the set (individual records do not have references)
        m_pRecord->m_pSet->AddRef();
}

void Record::DecRef() const
{
    if (m_pRecord != NULL) 
        // We keep the set (individual records do not have references)
        m_pRecord->m_pSet->DecRef();
}


class RealDB : public RefObj
{
public:
    // Ctor/Dtor
    RealDB(LPCTSTR pFilename) : m_pDB(NULL) {if (pFilename != NULL) Open(pFilename);};
protected:
    virtual ~RealDB() {Close();};

protected:
    // Members
    sqlite3*    m_pDB;

public:
    // Data Access
    bool        IsOpen() const {return (m_pDB != NULL);};
    sqlite3*    GetSQLiteDB() const {return m_pDB;};

public:
    // Methods
    bool        Open(LPCTSTR pFilename) {if (IsOpen()) if (!Close()) return false; if (sqlite3_open(MakeAnsiString(pFilename).c_str(), &m_pDB) != SQLITE_OK) return false; return true;};
    bool        Close() {if (!IsOpen()) return true; if (sqlite3_close(m_pDB) != SQLITE_OK) return false; m_pDB = NULL; return true;};
    bool        Execute(LPCTSTR pSQL) {return Execute(MakeTString(pSQL));};
    bool        Execute(const std::tstring& sSQL) {if (!IsOpen()) return false; return (sqlite3_exec(m_pDB, MakeAnsiString(sSQL).c_str(), NULL, NULL, NULL) == SQLITE_OK);};

    sqlite_int64 GetLastInsertRowid() const {if (!IsOpen()) return 0; return sqlite3_last_insert_rowid(m_pDB);};

    std::tstring GetLastError() {if (m_pDB == NULL) return _T(""); return MakeTString(sqlite3_errmsg(m_pDB));};
};

class RealTransaction : public RefObj
{
public:
    // Ctor/Dtor
    RealTransaction(const DB& db, Transaction::TransactionType eType, const std::tstring& sName = _T("")) : m_db(db), m_sName(sName), m_bActive(false) {m_bActive = StartTransaction(eType);};
protected:
    virtual ~RealTransaction() {if (m_bActive) Close();};

protected:
    // Members
    DB          m_db;
    std::tstring m_sName;
    bool        m_bActive;
    std::tstring m_sLastError;

    // Static members
    static LPCTSTR TRANSACTION_TYPE[];

public:
    // Data Access
    bool        IsValid() const {return m_bActive;};
    const std::tstring& GetLastError() const {return m_sLastError;};

public:
    // Methods
    bool        Close();
    bool        Commit() {if (!IsValid()) return false; bool bRet = m_db.Execute(_T("COMMIT TRANSACTION ") + m_sName); if (!bRet) m_sLastError = m_db.GetLastError(); return bRet;};
    bool        Rollback() {if (!IsValid()) return false; if (!m_db.Execute(_T("ROLLBACK TRANSACTION ") + m_sName)) {m_sLastError = m_db.GetLastError(); return false;} m_bActive = false; return true;};

    bool        Execute(LPCTSTR pSQL) {if (!IsValid()) return false; bool bRet = m_db.Execute(pSQL); if (!bRet) m_sLastError = m_db.GetLastError(); return bRet;};
    bool        Execute(const std::tstring& sSQL) {return m_db.Execute(sSQL.c_str());};
    bool        Execute(Statement& statement) {if (!IsValid() || (statement.GetDB() != m_db)) return false; return statement.Execute();};
    Recordset   Query(LPCTSTR pSQL) {if (!IsValid()) return Recordset(); Recordset ret = m_db.Query(pSQL); if (!ret.IsValid()) m_sLastError = m_db.GetLastError(); return ret;};
    Recordset   Query(const std::tstring& sSQL) {if (!IsValid()) return Recordset(); return m_db.Query(sSQL.c_str());};
    Recordset   Query(Statement& statement) {if (!IsValid() || (statement.GetDB() != m_db)) return Recordset(); return statement.Query();};

protected:
    // Helpers
    bool        StartTransaction(Transaction::TransactionType eType);
};

LPCTSTR RealTransaction::TRANSACTION_TYPE[] =
{
    _T("DEFERRED"),
    _T("IMMEDIATE"),
    _T("EXCLUSIVE"),
    NULL
};

bool RealTransaction::StartTransaction(Transaction::TransactionType eType)
{
    // Make sure we have valid parameters
    ASSERT((eType >= Transaction::TTDeferred) && (eType <= Transaction::TTExclusive));
    // And that we haven't started the transaction already!
    if (IsValid())
        return false;
    // Create a transaction start query
    std::tstring sSQL = _T("BEGIN ") + std::tstring(TRANSACTION_TYPE[eType]) + _T(" TRANSACTION ") + m_sName;
    // And run it
    if (m_db.Execute(sSQL))
        m_bActive = true;
    return m_bActive;
}

bool RealTransaction::Close()
{
    // Do we have a valid transaction?
    if (!IsValid())
        // Nope
        return false;
    // Close it
    if (!m_db.Execute(_T("END TRANSACTION ") + m_sName))
        // Failed?!
        return false;

    // OK, finished here
    m_bActive = false;
    return true;
}




class RealStatement : public RefObj
{
public:
    // Ctors/Dtor
#ifdef _DEBUG

    RealStatement(const DB& db, LPCTSTR pStatement) : m_sOriginalSQL(pStatement), m_db(db), m_pStatement(NULL), m_bExecuted(false), m_nLastInsertRowid(0) {const char* pTail; if (sqlite3_prepare(m_db.m_pDB->GetSQLiteDB(), MakeAnsiString(m_sOriginalSQL).c_str(), -1, &m_pStatement, &pTail) != SQLITE_OK) {m_sLastError = m_db.m_pDB->GetLastError(); if (m_pStatement != NULL) {sqlite3_finalize(m_pStatement); m_pStatement = NULL;}}};
#else

    RealStatement(const DB& db, LPCTSTR pStatement) : m_db(db), m_pStatement(NULL), m_bExecuted(false), m_nLastInsertRowid(0) {const char* pTail; if (sqlite3_prepare(m_db.m_pDB->GetSQLiteDB(), MakeAnsiString(pStatement).c_str(), -1, &m_pStatement, &pTail) != SQLITE_OK) {m_sLastError = m_db.m_pDB->GetLastError(); if (m_pStatement != NULL) {sqlite3_finalize(m_pStatement); m_pStatement = NULL;}}};
#endif

    ~RealStatement() {if (m_pStatement != NULL) sqlite3_finalize(m_pStatement);};
    friend class Realset;
    friend class RealRecord;

protected:
    // Members
    DB          m_db;
    std::tstring m_sLastError;
    sqlite3_stmt* m_pStatement;
    bool        m_bExecuted;
    sqlite_int64 m_nLastInsertRowid;
#ifdef _DEBUG
    std::tstring m_sOriginalSQL;
#endif

public:
    // Data Access
    const DB    GetDB() const {return m_db;};
    bool        IsValid() const {return (m_pStatement != NULL);};
    const std::tstring& GetLastError() const {return m_sLastError;};
    bool        HasParameter(LPCTSTR pParam) const {if (!IsValid()) return false; return sqlite3_bind_parameter_index(m_pStatement, MakeAnsiString(pParam).c_str()) != 0;};

public:
    // Methods
    bool        BindParameter(LPCTSTR pParam, LPCTSTR pValue) {int nParam = ParameterIndex(pParam); if (nParam == 0) return false; return BindParameter(nParam, pValue);};
    bool        BindParameter(LPCTSTR pParam, int nValue) {int nParam = ParameterIndex(pParam); if (nParam == 0) return false; return BindParameter(nParam, nValue);};
    bool        BindParameter(int nParam, LPCTSTR pValue) {if (!IsValid()) return false; if (m_bExecuted) if (!Reset()) return false; std::string sValue = MakeAnsiString(pValue); if (sqlite3_bind_text(m_pStatement, nParam, sValue.c_str(), (int)sValue.size(), SQLITE_TRANSIENT) == SQLITE_OK) return true; m_sLastError = m_db.GetLastError(); return false;};
    bool        BindParameter(int nParam, int nValue) {if (!IsValid()) return false; if (m_bExecuted) if (!Reset()) return false; if (sqlite3_bind_int(m_pStatement, nParam, nValue) == SQLITE_OK) return true; m_sLastError = m_db.GetLastError(); return false;};

    bool        Execute() {if (!IsValid()) return false; m_bExecuted = true; if (sqlite3_step(m_pStatement) == SQLITE_DONE) {m_nLastInsertRowid = m_db.GetLastInsertRowid(); return true;} m_sLastError = m_db.GetLastError(); m_nLastInsertRowid = 0; return false;};
    Recordset   Query();
#ifdef _DEBUG
//  void        Dump(CDumpContext& dc) const;
#endif

    sqlite_int64 GetLastInsertRowid() const {if (!IsValid()) return 0; return m_nLastInsertRowid;};

protected:
    // Helpers
    bool        Reset() {m_bExecuted = false; if (sqlite3_reset(m_pStatement) == SQLITE_OK) return true; m_sLastError = m_db.GetLastError(); return false;};
    int         ParameterIndex(LPCTSTR pParam) {ASSERT(IsValid()); return sqlite3_bind_parameter_index(m_pStatement, MakeAnsiString(pParam).c_str());};
};

Recordset RealStatement::Query()
{
    // The statement should be prepared:
    if (!IsValid())
        // Not!
        return Recordset();
    // Queries will not be an INSERT, so:
    m_nLastInsertRowid = 0;
    // OK, run the query
    return Recordset(new Realset(this));
}

Realset::Realset(RealStatement* pStatement)
{
    // Initialize the result set
    int nRes;
    m_bValid = false;
    
    // This is the main loop for retrieving the results: step through one row at a time
    while ((nRes = sqlite3_step(pStatement->m_pStatement)) == SQLITE_ROW)
    {
        // Got a row, how many columns?
        int nColumns = sqlite3_data_count(pStatement->m_pStatement);
        if (!m_mColumns.empty())
        {
            // We already have columns, make sure there's no problem with the count
            if (m_mColumns.size() != nColumns)
            {
                // What?
                ASSERT(FALSE);
                nRes = SQLITE_ERROR;
                break;
            }
        }
        else
        {
            // This should be the first round, so save the column names
            ASSERT(m_arRecords.empty());
            for (int i=0;i<nColumns;i++)
                m_mColumns[MakeTString(sqlite3_column_name(pStatement->m_pStatement, i))] = i;
        }

        // Add a new record object from the data
        m_arRecords.push_back(new RealRecord(this, pStatement, nColumns));
    }

    // Is everything all right?
    if (nRes == SQLITE_DONE)
        // Yeah
        m_bValid = true;
    else
    {
        // No, get the last error
        pStatement->m_sLastError = pStatement->m_db.GetLastError();
        Clean();
    }

    // Make sure we can use the statement again
    pStatement->Reset();
}

RealRecord::RealRecord(Realset* pSet, RealStatement* pStatement, int nColumns) : m_pSet(pSet)
{
    // Go over the columns
    for (int i=0;i<nColumns;i++)
    {
        // Get the valud for each
        const char* pValue = (const char*)sqlite3_column_text(pStatement->m_pStatement, i);
        if (pValue == NULL)
            // Empty...
            push_back(_T(""));
        else
            // Value...
            push_back(MakeTString(pValue));
    }
}

#ifdef _DEBUG
/*
void RealStatement::Dump(CDumpContext& dc) const
{
    if (m_pStatement == NULL)
    {
        dc << "Error: " << m_sLastError.c_str() << "\n";
        return;
    }
    int nParams = sqlite3_bind_parameter_count(m_pStatement);
    dc << "Dumping statement with " << nParams << " parameters:\n";
    for (int i=1;i<=nParams;i++)
        dc << "\t" << i << ". " << sqlite3_bind_parameter_name(m_pStatement, i) << "\n";
}
*/
#endif




bool Statement::IsValid() const
{
    if (m_pReal == NULL)
        return false;
    return m_pReal->IsValid();
}

bool Statement::Execute()
{
    if (!IsValid())
        return false;
    return m_pReal->Execute();
}

Recordset Statement::Query()
{
    if (!IsValid())
        return Recordset();
    return m_pReal->Query();
}

bool Statement::HasParameter(LPCTSTR pParam) const
{
    if (!IsValid())
        return false;
    return m_pReal->HasParameter(pParam);
}

bool Statement::BindParameter(LPCTSTR pParam, LPCTSTR pValue)
{
    if (!IsValid())
        return false;
    return m_pReal->BindParameter(pParam, pValue);
}

bool Statement::BindParameter(LPCTSTR pParam, int nValue)
{
    if (!IsValid())
        return false;
    return m_pReal->BindParameter(pParam, nValue);
}

bool Statement::BindParameter(int nParam, LPCTSTR pValue)
{
    if (!IsValid())
        return false;
    return m_pReal->BindParameter(nParam, pValue);
}

bool Statement::BindParameter(int nParam, int nValue)
{
    if (!IsValid())
        return false;
    return m_pReal->BindParameter(nParam, nValue);
}

std::tstring Statement::GetLastError()
{
    if (m_pReal == NULL)
        return _T("");
    return m_pReal->GetLastError();
}

void Statement::AddRef() const
{
    if (m_pReal != NULL)
        m_pReal->AddRef();
}

void Statement::DecRef() const
{
    if (m_pReal != NULL)
        m_pReal->DecRef();
}

#ifdef _DEBUG
/*
void Statement::Dump(CDumpContext& dc) const
{
    if (m_pReal == NULL)
        dc << "Not attached to a statement\n";
    else
        m_pReal->Dump(dc);
}
*/
#endif

sqlite_int64 Statement::GetLastInsertRowid() const
{
    if (m_pReal == NULL)
        return 0;
    return m_pReal->GetLastInsertRowid();
}

DB Statement::GetDB() const
{
    if (m_pReal == NULL)
        return DB();
    return m_pReal->GetDB();
}




DB::DB(LPCTSTR pFilename /* = NULL */) 
{
    m_pDB = new RealDB(pFilename); 
    AddRef();
}

bool DB::IsOpen() const 
{
    return m_pDB->IsOpen();
}

bool DB::Open(LPCTSTR pFilename)
{
    return m_pDB->Open(pFilename);
}

bool DB::Close() 
{
    return m_pDB->Close();
}

bool DB::Execute(LPCTSTR pSQL)
{
    return m_pDB->Execute(pSQL);
}

Recordset DB::Query(LPCTSTR pSQL) 
{
    if (!IsOpen()) 
        return Recordset(); 
    return Recordset(new Realset(m_pDB->GetSQLiteDB(), pSQL));
}

Transaction DB::BeginTransaction(Transaction::TransactionType eType, const std::tstring& sName /* = _T("") */) 
{
    return Transaction(new RealTransaction(m_pDB, eType, sName));
}

Statement DB::CreateStatement(LPCTSTR pStatement)
{
    return Statement(new RealStatement(m_pDB, pStatement));
}

std::tstring DB::GetLastError() 
{
    return m_pDB->GetLastError();
}

void DB::AddRef() const
{
    if (m_pDB != NULL) 
        m_pDB->AddRef();
}

void DB::DecRef() const
{
    if (m_pDB != NULL) 
        m_pDB->DecRef();
}

std::string DB::printf(const char* pString, ...)
{
    // Retrieve the variable list
    va_list vlist;
    va_start(vlist, pString);
    // Get the result from the SQLite print function
    char* pResult = sqlite3_vmprintf(pString, vlist);
    va_end(vlist);

    // Now create a copy
    std::string sRet;
    if (pResult != NULL)
    {
        sRet = pResult;
        sqlite3_free(pResult);
    }
    return sRet;
}

sqlite_int64 DB::GetLastInsertRowid() const
{
    if (m_pDB == NULL)
        return 0;
    return m_pDB->GetLastInsertRowid();
}




bool Transaction::Close() 
{
    return m_pReal->Close();
}

bool Transaction::Commit() 
{
    return m_pReal->Commit();
}

bool Transaction::Rollback() 
{
    return m_pReal->Rollback();
}

bool Transaction::Execute(LPCTSTR pSQL) 
{
    return m_pReal->Execute(pSQL);
}

bool Transaction::Execute(Statement& statment) 
{
    return m_pReal->Execute(statment);
}

Recordset Transaction::Query(LPCTSTR pSQL) 
{
    return m_pReal->Query(pSQL);
}

Recordset Transaction::Query(Statement& statement) 
{
    return m_pReal->Query(statement);
}

void Transaction::AddRef() const 
{
    m_pReal->AddRef();
}

void Transaction::DecRef() const 
{
    m_pReal->DecRef();
}

const std::tstring& Transaction::GetLastError() 
{
    return m_pReal->GetLastError();
}




}

---