00001
00006
00007
00008
00009
00010
00011
00012
00013
00014 #include "precomp.h"
00015 #include "SQLiteDB.h"
00016
00017 namespace SQLite
00018 {
00019
00020 class Realset;
00021 class RealStatement;
00022
00026 class RealRecord : protected STRARRAY
00027 {
00028 public:
00029
00036 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(""));};
00038 RealRecord(Realset* pSet, RealStatement* pStatement, int nColumns);
00042 virtual ~RealRecord() {};
00043 friend class Record;
00044 friend class Realset;
00045
00046 protected:
00047
00049 Realset* m_pSet;
00050
00051 public:
00052
00058 std::tstring GetField(int nColumn) const {return STRARRAY::operator[](nColumn);};
00060 std::tstring GetField(const std::tstring& sName) const;
00061 };
00062
00066 class Realset : public RefObj
00067 {
00068 public:
00069
00075 Realset(sqlite3* pDB, LPCTSTR pSQL) {m_bValid = (sqlite3_exec(pDB, MakeAnsiString(pSQL).c_str(), QueryCallback, (void*)this, NULL) == SQLITE_OK); if (!IsValid()) Clean();};
00077 Realset(RealStatement* pStatement);
00078 protected:
00082 ~Realset() {Clean();};
00083
00084 protected:
00085
00087 typedef std::vector<RealRecord*> RECORDARRAY;
00088
00089 protected:
00090
00092 STR2INT m_mColumns;
00094 RECORDARRAY m_arRecords;
00096 bool m_bValid;
00097
00098 public:
00099
00104 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;};
00110 RealRecord* GetRecord(int nNum) const {if ((nNum < 0) || (nNum >= (int)m_arRecords.size())) return NULL; return m_arRecords[nNum];};
00115 int GetRecordCount() const {return (int)m_arRecords.size();};
00120 bool IsValid() const {return m_bValid;};
00125 int GetColumnCount() const {return (int)m_mColumns.size();};
00131 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("");};
00132
00133 private:
00134
00136 int AddRecord(int nColumns, char** pData, char** pColumns);
00138 void Clean();
00139
00140
00148 static int QueryCallback(void* pThis, int nColumns, char** pData, char** pColumns) {return ((Realset*)pThis)->AddRecord(nColumns, pData, pColumns);};
00149 };
00150
00154 void Realset::Clean()
00155 {
00156 ASSERT(m_nRef == 0);
00157 for (int i=0;i < (int)m_arRecords.size();i++)
00158 delete m_arRecords[i];
00159 }
00160
00167 int Realset::AddRecord(int nColumns, char** pData, char** pColumns)
00168 {
00169
00170 int i;
00171 for (i=0;i<nColumns;i++)
00172 if (pData[i] != NULL)
00173 break;
00174 if (i == nColumns)
00175
00176 return 0;
00177
00178
00179 if (!m_mColumns.empty())
00180 {
00181
00182 if (m_mColumns.size() != nColumns)
00183 {
00184 ASSERT(FALSE);
00185 return 1;
00186 }
00187 }
00188 else
00189 {
00190
00191 ASSERT(m_arRecords.empty());
00192 for (int i=0;i<nColumns;i++)
00193 m_mColumns[MakeTString(pColumns[i])] = i;
00194 }
00195
00196
00197 m_arRecords.push_back(new RealRecord(this, pData, nColumns));
00198 return 0;
00199 }
00200
00201
00202
00207 Record Recordset::GetRecord(int nNum) const
00208 {
00209
00210 if (!IsValid())
00211
00212 return Record();
00213
00214 return Record(m_pSet->GetRecord(nNum));
00215 }
00216
00220 bool Recordset::IsValid() const
00221 {
00222 return (m_pSet != NULL) && m_pSet->IsValid();
00223 }
00224
00228 int Recordset::GetRecordCount() const
00229 {
00230 if (!IsValid())
00231
00232 return 0;
00233 return m_pSet->GetRecordCount();
00234 }
00235
00239 void Recordset::AddRef() const
00240 {
00241 if (m_pSet != NULL)
00242 m_pSet->AddRef();
00243 }
00244
00248 void Recordset::DecRef() const
00249 {
00250 if (m_pSet != NULL)
00251 m_pSet->DecRef();
00252 }
00253
00257 int Recordset::GetColumnCount() const
00258 {
00259 if (!IsValid())
00260
00261 return 0;
00262 return m_pSet->GetColumnCount();
00263 }
00264
00269 std::tstring Recordset::GetColumnName(int nNum) const
00270 {
00271 if (!IsValid())
00272
00273 return _T("");
00274 return m_pSet->GetColumnName(nNum);
00275 }
00276
00277
00278
00283 std::tstring RealRecord::GetField(const std::tstring& sName) const
00284 {
00285
00286 int nColumn = m_pSet->GetColumn(sName);
00287 if (nColumn == -1)
00288
00289 return _T("");
00290 return STRARRAY::operator[](nColumn);
00291 }
00292
00293
00294
00295
00296
00301 std::tstring Record::operator[](int nColumn) const
00302 {
00303 return m_pRecord->GetField(nColumn);
00304 }
00305
00310 std::tstring Record::operator[](const std::tstring& sName) const
00311 {
00312 return m_pRecord->GetField(sName);
00313 }
00314
00319 std::tstring Record::GetField(int nColumn) const
00320 {
00321 return m_pRecord->GetField(nColumn);
00322 }
00323
00328 std::tstring Record::GetField(const std::tstring& sName) const
00329 {
00330 return m_pRecord->GetField(sName);
00331 }
00332
00336 void Record::AddRef() const
00337 {
00338 if (m_pRecord != NULL)
00339
00340 m_pRecord->m_pSet->AddRef();
00341 }
00342
00346 void Record::DecRef() const
00347 {
00348 if (m_pRecord != NULL)
00349
00350 m_pRecord->m_pSet->DecRef();
00351 }
00352
00353
00357 class RealDB : public RefObj
00358 {
00359 public:
00360
00365 RealDB(LPCTSTR pFilename) : m_pDB(NULL) {if (pFilename != NULL) Open(pFilename);};
00366 protected:
00370 virtual ~RealDB() {Close();};
00371
00372 protected:
00373
00375 sqlite3* m_pDB;
00376
00377 public:
00378
00383 bool IsOpen() const {return (m_pDB != NULL);};
00387 sqlite3* GetSQLiteDB() const {return m_pDB;};
00388
00389 public:
00390
00396 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;};
00401 bool Close() {if (!IsOpen()) return true; if (sqlite3_close(m_pDB) != SQLITE_OK) return false; m_pDB = NULL; return true;};
00407 bool Execute(LPCTSTR pSQL) {return Execute(MakeTString(pSQL));};
00413 bool Execute(const std::tstring& sSQL) {if (!IsOpen()) return false; return (sqlite3_exec(m_pDB, MakeAnsiString(sSQL).c_str(), NULL, NULL, NULL) == SQLITE_OK);};
00414
00419 sqlite_int64 GetLastInsertRowid() const {if (!IsOpen()) return 0; return sqlite3_last_insert_rowid(m_pDB);};
00420
00425 std::tstring GetLastError() {if (m_pDB == NULL) return _T(""); return MakeTString(sqlite3_errmsg(m_pDB));};
00426 };
00427
00431 class RealTransaction : public RefObj
00432 {
00433 public:
00434
00441 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);};
00442 protected:
00446 virtual ~RealTransaction() {if (m_bActive) Close();};
00447
00448 protected:
00449
00451 DB m_db;
00453 std::tstring m_sName;
00455 bool m_bActive;
00457 std::tstring m_sLastError;
00458
00459
00460 static LPCTSTR TRANSACTION_TYPE[];
00461
00462 public:
00463
00468 bool IsValid() const {return m_bActive;};
00472 const std::tstring& GetLastError() const {return m_sLastError;};
00473
00474 public:
00475
00477 bool Close();
00482 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;};
00487 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;};
00488
00494 bool Execute(LPCTSTR pSQL) {if (!IsValid()) return false; bool bRet = m_db.Execute(pSQL); if (!bRet) m_sLastError = m_db.GetLastError(); return bRet;};
00500 bool Execute(const std::tstring& sSQL) {return m_db.Execute(sSQL.c_str());};
00506 bool Execute(Statement& statement) {if (!IsValid() || (statement.GetDB() != m_db)) return false; return statement.Execute();};
00512 Recordset Query(LPCTSTR pSQL) {if (!IsValid()) return Recordset(); Recordset ret = m_db.Query(pSQL); if (!ret.IsValid()) m_sLastError = m_db.GetLastError(); return ret;};
00518 Recordset Query(const std::tstring& sSQL) {if (!IsValid()) return Recordset(); return m_db.Query(sSQL.c_str());};
00524 Recordset Query(Statement& statement) {if (!IsValid() || (statement.GetDB() != m_db)) return Recordset(); return statement.Query();};
00525
00526 protected:
00527
00529 bool StartTransaction(Transaction::TransactionType eType);
00530 };
00531
00533 LPCTSTR RealTransaction::TRANSACTION_TYPE[] =
00534 {
00535 _T("DEFERRED"),
00536 _T("IMMEDIATE"),
00537 _T("EXCLUSIVE"),
00538 NULL
00539 };
00540
00545 bool RealTransaction::StartTransaction(Transaction::TransactionType eType)
00546 {
00547
00548 ASSERT((eType >= Transaction::TTDeferred) && (eType <= Transaction::TTExclusive));
00549
00550 if (IsValid())
00551 return false;
00552
00553 std::tstring sSQL = _T("BEGIN ") + std::tstring(TRANSACTION_TYPE[eType]) + _T(" TRANSACTION ") + m_sName;
00554
00555 if (m_db.Execute(sSQL))
00556 m_bActive = true;
00557 return m_bActive;
00558 }
00559
00563 bool RealTransaction::Close()
00564 {
00565
00566 if (!IsValid())
00567
00568 return false;
00569
00570 if (!m_db.Execute(_T("END TRANSACTION ") + m_sName))
00571
00572 return false;
00573
00574
00575 m_bActive = false;
00576 return true;
00577 }
00578
00579
00580
00581
00585 class RealStatement : public RefObj
00586 {
00587 public:
00588
00589 #ifdef _DEBUG
00590
00595 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;}}};
00596 #else
00597
00602 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;}}};
00603 #endif
00604
00607 ~RealStatement() {if (m_pStatement != NULL) sqlite3_finalize(m_pStatement);};
00608 friend class Realset;
00609 friend class RealRecord;
00610
00611 protected:
00612
00614 DB m_db;
00616 std::tstring m_sLastError;
00618 sqlite3_stmt* m_pStatement;
00620 bool m_bExecuted;
00622 sqlite_int64 m_nLastInsertRowid;
00623 #ifdef _DEBUG
00625 std::tstring m_sOriginalSQL;
00626 #endif
00627
00628 public:
00629
00634 const DB GetDB() const {return m_db;};
00639 bool IsValid() const {return (m_pStatement != NULL);};
00644 const std::tstring& GetLastError() const {return m_sLastError;};
00650 bool HasParameter(LPCTSTR pParam) const {if (!IsValid()) return false; return sqlite3_bind_parameter_index(m_pStatement, MakeAnsiString(pParam).c_str()) != 0;};
00651
00652 public:
00653
00660 bool BindParameter(LPCTSTR pParam, LPCTSTR pValue) {int nParam = ParameterIndex(pParam); if (nParam == 0) return false; return BindParameter(nParam, pValue);};
00667 bool BindParameter(LPCTSTR pParam, int nValue) {int nParam = ParameterIndex(pParam); if (nParam == 0) return false; return BindParameter(nParam, nValue);};
00674 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;};
00681 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;};
00682
00687 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;};
00689 Recordset Query();
00690 #ifdef _DEBUG
00691
00692 #endif
00693
00697 sqlite_int64 GetLastInsertRowid() const {if (!IsValid()) return 0; return m_nLastInsertRowid;};
00698
00699 protected:
00700
00705 bool Reset() {m_bExecuted = false; if (sqlite3_reset(m_pStatement) == SQLITE_OK) return true; m_sLastError = m_db.GetLastError(); return false;};
00711 int ParameterIndex(LPCTSTR pParam) {ASSERT(IsValid()); return sqlite3_bind_parameter_index(m_pStatement, MakeAnsiString(pParam).c_str());};
00712 };
00713
00717 Recordset RealStatement::Query()
00718 {
00719
00720 if (!IsValid())
00721
00722 return Recordset();
00723
00724 m_nLastInsertRowid = 0;
00725
00726 return Recordset(new Realset(this));
00727 }
00728
00732 Realset::Realset(RealStatement* pStatement)
00733 {
00734
00735 int nRes;
00736 m_bValid = false;
00737
00738
00739 while ((nRes = sqlite3_step(pStatement->m_pStatement)) == SQLITE_ROW)
00740 {
00741
00742 int nColumns = sqlite3_data_count(pStatement->m_pStatement);
00743 if (!m_mColumns.empty())
00744 {
00745
00746 if (m_mColumns.size() != nColumns)
00747 {
00748
00749 ASSERT(FALSE);
00750 nRes = SQLITE_ERROR;
00751 break;
00752 }
00753 }
00754 else
00755 {
00756
00757 ASSERT(m_arRecords.empty());
00758 for (int i=0;i<nColumns;i++)
00759 m_mColumns[MakeTString(sqlite3_column_name(pStatement->m_pStatement, i))] = i;
00760 }
00761
00762
00763 m_arRecords.push_back(new RealRecord(this, pStatement, nColumns));
00764 }
00765
00766
00767 if (nRes == SQLITE_DONE)
00768
00769 m_bValid = true;
00770 else
00771 {
00772
00773 pStatement->m_sLastError = pStatement->m_db.GetLastError();
00774 Clean();
00775 }
00776
00777
00778 pStatement->Reset();
00779 }
00780
00786 RealRecord::RealRecord(Realset* pSet, RealStatement* pStatement, int nColumns) : m_pSet(pSet)
00787 {
00788
00789 for (int i=0;i<nColumns;i++)
00790 {
00791
00792 const char* pValue = (const char*)sqlite3_column_text(pStatement->m_pStatement, i);
00793 if (pValue == NULL)
00794
00795 push_back(_T(""));
00796 else
00797
00798 push_back(MakeTString(pValue));
00799 }
00800 }
00801
00802 #ifdef _DEBUG
00803
00804
00805
00806
00807
00808
00809
00810
00811
00812
00813
00814
00815
00816
00817 #endif
00818
00819
00820
00821
00825 bool Statement::IsValid() const
00826 {
00827 if (m_pReal == NULL)
00828 return false;
00829 return m_pReal->IsValid();
00830 }
00831
00835 bool Statement::Execute()
00836 {
00837 if (!IsValid())
00838 return false;
00839 return m_pReal->Execute();
00840 }
00841
00845 Recordset Statement::Query()
00846 {
00847 if (!IsValid())
00848 return Recordset();
00849 return m_pReal->Query();
00850 }
00851
00856 bool Statement::HasParameter(LPCTSTR pParam) const
00857 {
00858 if (!IsValid())
00859 return false;
00860 return m_pReal->HasParameter(pParam);
00861 }
00862
00868 bool Statement::BindParameter(LPCTSTR pParam, LPCTSTR pValue)
00869 {
00870 if (!IsValid())
00871 return false;
00872 return m_pReal->BindParameter(pParam, pValue);
00873 }
00874
00880 bool Statement::BindParameter(LPCTSTR pParam, int nValue)
00881 {
00882 if (!IsValid())
00883 return false;
00884 return m_pReal->BindParameter(pParam, nValue);
00885 }
00886
00892 bool Statement::BindParameter(int nParam, LPCTSTR pValue)
00893 {
00894 if (!IsValid())
00895 return false;
00896 return m_pReal->BindParameter(nParam, pValue);
00897 }
00898
00904 bool Statement::BindParameter(int nParam, int nValue)
00905 {
00906 if (!IsValid())
00907 return false;
00908 return m_pReal->BindParameter(nParam, nValue);
00909 }
00910
00914 std::tstring Statement::GetLastError()
00915 {
00916 if (m_pReal == NULL)
00917 return _T("");
00918 return m_pReal->GetLastError();
00919 }
00920
00924 void Statement::AddRef() const
00925 {
00926 if (m_pReal != NULL)
00927 m_pReal->AddRef();
00928 }
00929
00933 void Statement::DecRef() const
00934 {
00935 if (m_pReal != NULL)
00936 m_pReal->DecRef();
00937 }
00938
00939 #ifdef _DEBUG
00940
00941
00942
00943
00944
00945
00946
00947
00948
00949 #endif
00950
00954 sqlite_int64 Statement::GetLastInsertRowid() const
00955 {
00956 if (m_pReal == NULL)
00957 return 0;
00958 return m_pReal->GetLastInsertRowid();
00959 }
00960
00964 DB Statement::GetDB() const
00965 {
00966 if (m_pReal == NULL)
00967 return DB();
00968 return m_pReal->GetDB();
00969 }
00970
00971
00972
00973
00977 DB::DB(LPCTSTR pFilename )
00978 {
00979 m_pDB = new RealDB(pFilename);
00980 AddRef();
00981 }
00982
00986 bool DB::IsOpen() const
00987 {
00988 return m_pDB->IsOpen();
00989 }
00990
00995 bool DB::Open(LPCTSTR pFilename)
00996 {
00997 return m_pDB->Open(pFilename);
00998 }
00999
01003 bool DB::Close()
01004 {
01005 return m_pDB->Close();
01006 }
01007
01012 bool DB::Execute(LPCTSTR pSQL)
01013 {
01014 return m_pDB->Execute(pSQL);
01015 }
01016
01021 Recordset DB::Query(LPCTSTR pSQL)
01022 {
01023 if (!IsOpen())
01024 return Recordset();
01025 return Recordset(new Realset(m_pDB->GetSQLiteDB(), pSQL));
01026 }
01027
01033 Transaction DB::BeginTransaction(Transaction::TransactionType eType, const std::tstring& sName )
01034 {
01035 return Transaction(new RealTransaction(m_pDB, eType, sName));
01036 }
01037
01042 Statement DB::CreateStatement(LPCTSTR pStatement)
01043 {
01044 return Statement(new RealStatement(m_pDB, pStatement));
01045 }
01046
01050 std::tstring DB::GetLastError()
01051 {
01052 return m_pDB->GetLastError();
01053 }
01054
01058 void DB::AddRef() const
01059 {
01060 if (m_pDB != NULL)
01061 m_pDB->AddRef();
01062 }
01063
01067 void DB::DecRef() const
01068 {
01069 if (m_pDB != NULL)
01070 m_pDB->DecRef();
01071 }
01072
01077 std::string DB::printf(const char* pString, ...)
01078 {
01079
01080 va_list vlist;
01081 va_start(vlist, pString);
01082
01083 char* pResult = sqlite3_vmprintf(pString, vlist);
01084 va_end(vlist);
01085
01086
01087 std::string sRet;
01088 if (pResult != NULL)
01089 {
01090 sRet = pResult;
01091 sqlite3_free(pResult);
01092 }
01093 return sRet;
01094 }
01095
01099 sqlite_int64 DB::GetLastInsertRowid() const
01100 {
01101 if (m_pDB == NULL)
01102 return 0;
01103 return m_pDB->GetLastInsertRowid();
01104 }
01105
01106
01107
01108
01112 bool Transaction::Close()
01113 {
01114 return m_pReal->Close();
01115 }
01116
01120 bool Transaction::Commit()
01121 {
01122 return m_pReal->Commit();
01123 }
01124
01128 bool Transaction::Rollback()
01129 {
01130 return m_pReal->Rollback();
01131 }
01132
01137 bool Transaction::Execute(LPCTSTR pSQL)
01138 {
01139 return m_pReal->Execute(pSQL);
01140 }
01141
01146 bool Transaction::Execute(Statement& statment)
01147 {
01148 return m_pReal->Execute(statment);
01149 }
01150
01155 Recordset Transaction::Query(LPCTSTR pSQL)
01156 {
01157 return m_pReal->Query(pSQL);
01158 }
01159
01164 Recordset Transaction::Query(Statement& statement)
01165 {
01166 return m_pReal->Query(statement);
01167 }
01168
01172 void Transaction::AddRef() const
01173 {
01174 m_pReal->AddRef();
01175 }
01176
01180 void Transaction::DecRef() const
01181 {
01182 m_pReal->DecRef();
01183 }
01184
01188 const std::tstring& Transaction::GetLastError()
01189 {
01190 return m_pReal->GetLastError();
01191 }
01192
01193
01194
01195
01196 }