SQLDevKit: A C++ Object Oriented Wrapper to SQLite

• Download SQLiteViewer_VS12_12112013.zip
• Download SQLiteViewer_VS08_12112013.zip
• Download SQLDevKit_Documentation.zip

SQL Developer Kit (SQD) Users Guide

Version 0.5.0
Date: 12/11/2013
Author: Anthony Daniels
Company: Pyramid Solutions
http://www.pyramidsolutions.com

The SQL Developer Kit, or SQD, is an interface wrapper around the SQLite 3 Database engine. SQD provides a collection of convenience classes that simplify access to SQLite data. There are four classes in the library, SQLField, SQLRecord, SQLQuerySet, and SQLDatabase. SQLite an open source, zero configuration, and embeddable Structured Query Language (SQL) database engine. There are a number of SQLite wrappers in existence. This wrapper is based on an Microsoft ADO style. It was developed mainly as a learning exercise in SQLite3. The decision was made to make it open source. If you find a bug please let the author know. The use of the classes are outlined in the following paragraphs.

SQDDatabase Reference:

A Database can be opened easily by setting the file path and then calling OpenDB();.

SQDDatabase m_objSqlDB;
this->m_objSqlDB.Set_strDBPathName(strFullPathName);
intRet = this->m_objSqlDB.OpenDB();

Closing a database is equally simple.

this->m_objSqlDB.CloseDB();

SQDDatabase Methods:

public:
 //The following macro makes standard accessor functions for the protected member variables
 //The macro is from the HPC Template Library (HTL), and open source supplement the the STL.
 //For example:
 //std::string Get_strUserName(void)
 //void Set_strUserName(std::string varVal);
 
 //MEMBER VARIABLES///////////////////////////
 //!The string user name
 HTLMEMVAR_BYVAL_STYLE1(public,std::string,strUserName)
 //!The string password
 HTLMEMVAR_BYVAL_STYLE1(public,std::string,strPassword)
 //!The string database path name
 HTLMEMVAR_BYVAL_STYLE1(public,std::string,strDBPathName)
 //!The integer port number
 HTLMEMVAR_BYVAL_STYLE1(public,int,intPort)
        //!Boolean flage for is open  
 HTLMEMVAR_BYVAL_STYLE1(public,bool,blnIsOpen)
 //!The string last error
 HTLMEMVAR_BYVAL_STYLE1(public,std::string,strError)

         //MEMBER FUNCTIONS//////////////////////////
 public:
        //!Get the pointer to the RAW DATABASE
        sqlite3* Get_ptrDB(void);
                //!Open the database connection
        int OpenDB(void);
        //!Close the database Connection
        int CloseDB(void);
        //!Returns if the database is open
        bool IsOpen(void);
 //!Returns a list of table names for the database.
  std::vector<std::string> GetTableNames(void);

SQDQuerySet Reference

The Query Set is the primary interface with the SQLite database engine. The user sets the query string and then executes the query. If the query is a SELECT statement, then the records retrieve are collected in the recordset of the query. It is important that in best practice the user performs queries with 'rowid' as the first column being retrieved. Unless it is specifically requested, the rowid primary key is not returned.

For example: SELECT rowid,* FROM strTableName
For example: SELECT rowid, col01, col02, col03 FROM strTableName

Important! The SQLDevKit assumes that the first column of the recordset is the primary key. Otherwise it has no means of saving data back to the correct row in the database. The following is an example of performing a query.

SQDQuery m_objQuery;
std::string strTableName;
int intRes;
std::stringstream strCom;
strTableName; = "tblContactInfo";
strCom << "SELECT rowid,* FROM  " << strTableName.c_str();
m_objQuery.Set_ptrDatabase(&m_objSqlDB);
m_objQuery.Set_strQuery(strCom.str());
intRes = m_objQuery.Execute();

SQDQuerySet Methods

public:
 //!The string query to be executed
 HTLMEMVAR_BYVAL_STYLE1(public,std::string,strQuery)
 //!The integer code query result
 HTLMEMVAR_BYVAL_STYLE1(public,int,intResult)
 //!The string user name
 HTLMEMVAR_BYVAL_STYLE1(public, std::string, strTableName)
 //!The pointer to the native sqlite3 statement object
 HTLMEMVAR_BYVAL_STYLE1(public, sqlite3_stmt *, ptrStatement)
 //!Database Accessors
 void Set_ptrDatabase(SQDDatabase* ptrDB);
 SQDDatabase* Get_ptrDatabase(void);
 //!Promulgate table name to all records.  This is important to do before trying commits.
 //Execute does this automatically at the end of a query recordset build.
 void Set_AllTableNames(std::string strTableName);
 //!Promulgate database pointer to all records.  This is important to do before trying commits.
 //Execute does this automatically at the end of a query recordset build.
 void Set_AllDatabasePtrs(SQDDatabase* ptrDB);
 //!Executes the SQL Call to the database
 int Execute(void);
 //!Update All Records from the Database to the Recordset
 //This calls an sqlite3 reset function before executing the query again.
 int UpdateAll(void);
 //!Update Row from the Database to the Recordset
 int UpdateRow(size_t intRow);
 //!Commit All Changes to the database
 int CommitAll(void);
 //!Commit Row Changes to the database
 int CommitRow(size_t index);
 //!Get the recordset collection pointer
 SQDRecordCollection * GetRecordCollPtr(void);
 //!Returns the record at the desired index
 SQDRecord * AtRecord(size_t index);
 //!Returns the number of records in the recordset
 size_t CountRecords(void);
 size_t Size(void);
 //!Gets the number of columns in the recordset
 size_t CountColumns (void);
 //!Gets the specified column name
 std::string GetColumnName (int index);
 //!Get all column names 
 std::vector<std::string> GetColumnNames (void);
 //!Get the first record in the collection iterator
 SQDRecordIterator BeginRecord(void);
 //!Get the last record in the collection iterator
 SQDRecordIterator EndRecord(void);
 //!Get the next record in the list
 SQDRecordIterator NextRecord(void);
 //!Get the current cursor iterator
 SQDRecordIterator GetCursor(void);
 //!Create a Record with the supplied table definition
 SQDRecord * CreateRecord();
 //!Create a Record with the supplied record definition
 int InsertRecord(SQDRecord * ptrRec);
 //!Mark a Record for Deletion = TRUE
 int DeleteRecord(size_t index);
 //!Mark all Records for Deletion = TRUE
 int DeleteAllRecords(void);
 //!Mark a Record for Deletion = FALSE
 int UnDeleteRecord(size_t index);
 //!Mark all Records for Deletion = FALSE
 int UnDeleteAllRecords(void);
 //The Remove destroys the records in the LOCAL COPY ONLY, NOT THE DATABASE
 //!Delete the record with the provided index
 int RemoveRecord(size_t index);
 //!Delete all of the records
 int RemoveAllRecords(void);
 //The DeleteCommits destroy the records IN THE DATABASE
 //!Delete the record with the provided index
 int DeleteCommitRecord(size_t index);
 //!Delete all of the records
 int DeleteCommitAllRecords(void);

SQDRecord Reference

At the heart of the information of a table relational database such as SQLite is the record, or row. It is the collection of fields that make up one row of data in the set. SQLite rows have a unique rowid from which they are accessed. The record has a collection of fields for each column of data in the set. Individual rows can be updated and commit their information to the database independent of one another. They can commit or update just one field or the entire collection of fields. Fields are accessed via STL like AtField() functions or via iterators. The collection is managed like an STL vector with similar functions.

SQDRecord Methods

public:   
 //!Boolean Flag for New Record
 HTLMEMVAR_BYVAL_STYLE1(public,bool,blnNewRecord);
 //!Boolean Flag for Delete Record
 HTLMEMVAR_BYVAL_STYLE1(public,bool,blnDeleteRecord);
 //!Boolean Flag for whether the Record was Modified or Edited
 HTLMEMVAR_BYVAL_STYLE1(public,bool,blnModified);
 //!String Table Name this record belongs to
 HTLMEMVAR_BYVAL_STYLE1(public,std::string,strTableName)
 //!The pointer to the database object
 HTLMEMVAR_BYVAL_STYLE1(public,SQDDatabase*,ptrDatabase)
 public:
 //DB====>Active Memory
 //Updates all fields 
 int UpdateAll(void);
 //!Update the value for the selected field
 int UpdateField(size_t index);
 //Active Memory====>DB
 //!Commit all values for all fields
 int CommitAll(void);
 //!Commit the value of the selected field to the database
 int CommitField(size_t index);
 //!Get the field collection pointer
 SQDFieldCollection * GetFieldCollPtr(void);
 //CONTAINER ACCESSORS FOR TABLE COLLECTION
 //!Does the Table with strName Exist
 bool DoesFieldExist(std::string strName);
 //!Does the Table with strName Exist
 SQDField * GetField(std::string strName);
 //!Gets the field index number
 int GetFieldLoc(std::string strName);
 //!Returns the table at the desired index
 SQDField * AtField(size_t index);
 //!Returns the number of fields in the record
 int CountFields(void);
 //!Returns the 
 int Size(void);
 //!Get the FieldName at the index
 std::string GetFieldName(size_t index);
 //!Get the first table in the collection iterator
 SQDFieldIterator BeginField(void);
 //!Get the last table in the collection iterator
 SQDFieldIterator EndField(void);
 //!Get the next field iterator
 SQDFieldIterator NextField(void);
 //!Get the cursor directly
 SQDFieldIterator GetCursor(void);
 //!Create a Table with the supplied table definition
 SQDField * CreateField(std::string strName, SQDFieldType enmType);
 //!Delete the fields with the provided name
 int DeleteField(std::string strName);
 //!Delete all of the fields
 int DeleteAllFields(void);

SQDField Reference 

The field is essentially a name value pair container of the field name and the field value. SQLDevKit handles all data types except BLOB. This feature is intended to be added at a later date. All information is natively stored in the field as text and converted via convenience Get and Set functions for the value.

SQDField Methods

public:
 //!Field Name
 HTLMEMVAR_BYVAL_STYLE1(public,std::string,strName);
 //!Pointer to Parent Record
 HTLMEMVAR_BYVAL_STYLE1(public,SQDRecord*,ptrParent);
 //!Field Typebr /> HTLMEMVAR_BYVAL_STYLE1(public,SQDFieldType,objType);
 //!Set and Get for Node Value
 void Set_strValue(const std::string & strValue);
 void Set_strValue(const char * ptrCharString);
  void Set_strValue(bool blnVal);
 void Set_strValue(short shrtVal);
 void Set_strValue(unsigned short ushrtVal);
 void Set_strValue(int intVal);
 void Set_strValue(unsigned int uint);
 void Set_strValue(long lngVal);
 void Set_strValue(unsigned long ulngVal);
 void Set_strValue(float sngVal);
  void Set_strValue(double dblVal);
 void Set_strValue(char chrVal);
 void Set_strValue(unsigned char uchrVal);
 //!Set and Get for Node Value
 std::string Get_strValue(void);
 void Get_strValue(bool & blnValReturn);
 void Get_strValue(short & shrtValReturn);
 void Get_strValue(unsigned short & ushrtValReturn);
 void Get_strValue(int & intValReturn);
 void Get_strValue(unsigned int & uintValReturn);
 void Get_strValue(long & lngValReturn);
 void Get_strValue(unsigned long & ulngValReturn);
 void Get_strValue(float & sngValReturn);
 void Get_strValue(double & dblValReturn);
 void Get_strValue(char & chrValReturn);
 void Get_strValue(unsigned char & uchrValReturn);
 void Get_strValue(std::string & strValReturn);