starting with sqlite in c++
IntroductionStoring data is an important part of most programs,and before I discovered SQLite,I used to spend a large amount of development time creating file-handling code. About a year ago,I discovered a library called SQLite. This acts as a tiny database you can embed into your program. This not only saves time,but allows you to use SQL to access the correct data. In this article,I will show how I:
BackgroundThere are already a number of tutorials on compiling SQLite into your C++ code,and it is not that hard to do. I am writing this to explain my method and show what tools I use. Prerequisites for following this tutorialI have created a CodeProject article that completely shows how I set up the tool chain I use for compiling C++ programs. It is available here: opensource_tool.aspx Downloaded SQLiteTo get SQLite,go to: http://www.sqlite.org/download.html and download the amalgamation. (I downloaded sqlite-amalgamation-3_6_14_2.zip.) SQLite comes as source files we can compile as part of our build process for our program. To do this,we must set up our project. Launch MSYS and follow these commands:
Extract the contents of the zip file to this directory. (I used 7-zip for this.) We don't need the file sqlite3ext.h,so delete this. SQLite comes as code that can be compiled directly into applications,so next we should create a makefile and check that we can compile SQLite into an object. (You can copy the command from the webpage and use Shift + Ins to place the command in MSYS.)
$(warning Starting Makefile)
CXX=g++
sqlite3.obj: sqlite3.c
gcc -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1
clean:
-rm *.obj
Now,select Run -> RUN_CODE from the Notepad++ menu we created in the prerequisite tutorial. If it all went well,you will see something like the following output: Collapse | Copy CodeC:Program FilesNotepad++>c:
C:Program FilesNotepad++>cd
C:&;cd c:codesqlite_hello_world
C:codesqlite_hello_world>make
makefile:1: Starting Makefile
gcc -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1
C:codesqlite_hello_world>pause
Press any key to continue . . .
C:codesqlite_hello_world>main.exe
'main.exe' is not recognized as an internal or external command,operable program or batch file.
C:codesqlite_hello_world>pause
Press any key to continue . . .
We haven't created a main.exe yet,so don't worry about the error. If you look in the directory,you will see sqlite3.obj. Note: If you want to find out more about SQLite,the website is http://www.sqlite.org/. Use the SQLite Handler classI'm going to cheat a bit now and use some code I have prepared earlier. In this section,I am just going to provide instructions showing how to get the code in place and compiling. In the next section,I am going to dive into the code I have given you and tour you through what it does and why. There are two sections of code which I am going to use to help me create the SQLite program. Firstly,take the following files out of this articles zip file and place them in the c:codesqlite_hello_world directory.
After copying the files,make some changes to the Make file to compile all the objects.
$(warning Starting Makefile)
CXX=g++
main.exe: sqlite3.obj RJM_SQLite_Resultset.obj RJMFTime.obj
$(warning main bit not done)
sqlite3.obj: sqlite3.c
gcc -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1
RJM_SQLite_Resultset.obj: RJM_SQLite_Resultset.cpp
RJM_SQLite_Resultset.h Glob_Defs.h RJMFTime.h sqlite3.h
$(CXX) -c RJM_SQLite_Resultset.cpp -o RJM_SQLite_Resultset.obj
RJMFTime.obj: RJMFTime.cpp RJMFTime.h
$(CXX) -c RJMFTime.cpp -o RJMFTime.obj
clean:
-rm *.obj
-rm *.exe
This new Make file shows some features of Make. When you type Make without any arguments,Make tries to create the first object it finds in the file. In this case,the first section does nothing,since we are not ready to create the .exe yet (we don't have a program). The reason I have put it there is to list its dependencies. This list (sqlite3.obj,RJM_SQLite_Resultset.obj,RJMFTime.obj) tells Make that it needs to create these three files. Instructions on how to create the files are in the makefile program. Test this by clicking RUN_CODE in the Notepad++ menu we created in the prerequisite article. The output should look something like: Collapse | Copy CodeC:Program FilesNotepad++>c:
C:Program FilesNotepad++>cd
C:&;cd C:codesqlite_hello_world
C:codesqlite_hello_world>make
makefile:1: Starting Makefile
gcc -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1
g++ -c RJM_SQLite_Resultset.cpp -o RJM_SQLite_Resultset.obj
g++ -c RJMFTime.cpp -o RJMFTime.obj
makefile:6: main bit not done
C:codesqlite_hello_world>pause
Press any key to continue . . .
C:codesqlite_hello_world>main.exe
'main.exe' is not recognized as an internal or external command,operable program or batch file.
C:codesqlite_hello_world>pause
Press any key to continue . . .
Again,ignore the error message. Explanation of the SQLite Handler classIn this section,we are not going to make any changes to our hello application. Instead,I intend to take you on a tour of the code we copied into place and show what it does,why,and how. We used two separate bits of code,and I will explain each separately. RJMFTimeThis uses the following files:
I used to program MFC and used its time classes for dealing with storing dates and times. When I moved to code that doesn't rely on propriety stuff,I moved to using Unix time ( Since then I have learnt about GTK+ and glib. This could replace this class. A good idea would be to learn to get this article working and learn how SQLite works,then simply replace RJMFtime with the GTK+ equivalent. My class isn't too bad,and it has been well tested and has some functionality especially designed for working with SQLite times. SQLite ResultsetThis uses the following files:
SQLite works on SQL statements and each of these statements returns a result set. The RJMSQLite Glob Defs explanationData types are important to the class,and there is an include file called Glob_Defs.h which includes macro definitions. It has macros for the data types I use ( Resultset explanationThe RJM_SQLite_Resultset files contain two classes, static RJM_SQLite_Resultset* SQL_Execute(const char* cmd,sqlite3* db) {
RJM_SQLite_Resultset* pRS;
pRS = new RJM_SQLite_Resultset();
int rc;
char *zErrMsg = 0;
rc = sqlite3_exec(db,cmd,sql_callback,pRS,&zErrMsg);
if( rc!=SQLITE_OK ){
pRS->SetError(zErrMsg);
sqlite3_free(zErrMsg);
}
return pRS;
};
This is a static function that is called to execute a query. It needs the query string and a pointer to the static int sql_callback(void *NotUsed,int argc,char **argv,char **azColName){
RJM_SQLite_Resultset *pRS = (RJM_SQLite_Resultset*) NotUsed;
return pRS->sql_callback(argc,argv,azColName);
}
I have used a parameter which SQLite lets me have (called Note: This method means that for every call to Create an empty shell program to use thisOK,now we have managed to compile SQLite and my helper classes. We now need to write a program that actually uses them to do something. I am going to build a simple command line application,but this can be built into the GUI and other applications as well. First,let's create a main.cpp file with all the required headers,and compile it to make sure we have got that right:
#include <iostream>
#include "sqlite3.h"
#include "Glob_Defs.h"
int main( int argc,char *argv[] )
{
srand ( time(NULL) );
printf("SQLite Demo program startn");
printf("SQLite Demo program endn");
return 0;
};
Don't forget the new line at the end of the file,and make sure the <'s and >'s get copied OK. This is the demo program. We need to adjust the Make file to build it:
$(warning Starting Makefile)
CXX=g++
main.exe: sqlite_demo.lib main.cpp
$(CXX) -s main.cpp -o main.exe -Wl,sqlite_demo.lib
sqlite_demo.lib: sqlite3.obj RJM_SQLite_Resultset.obj RJMFTime.obj
ar cq $@ RJMFTime.obj
ar cq $@ RJM_SQLite_Resultset.obj
ar cq $@ sqlite3.obj
sqlite3.obj: sqlite3.c
$(CXX) -c sqlite3.c -o sqlite3.obj -DTHREADSAFE=1
RJM_SQLite_Resultset.obj: RJM_SQLite_Resultset.cpp
RJM_SQLite_Resultset.h Glob_Defs.h RJMFTime.h sqlite3.h
$(CXX) -c RJM_SQLite_Resultset.cpp -o RJM_SQLite_Resultset.obj
RJMFTime.obj: RJMFTime.cpp RJMFTime.h
$(CXX) -c RJMFTime.cpp -o RJMFTime.obj
clean:
-rm *.obj
-rm *.exe
-rm *.lib
As you can see,the Make file has got more complicated. It creates a series of .obj files,and puts these .obj files together into a .lib file. Then,the main application is linked against the lib file. This may seem like overkill at this stage,but one of the benefits of SQLite is you can write lots of different programs which work with the same database. I take this one stage further and split my program development into two parts. Have a backend lib and a front end. I add my own validation code etc.,to the standard functions in the backend which gets compiled into the .lib,and then each individual front-end is linked with the .lib file. Another point with this Make file is that there are .obj,.exe,and .lib files mixed in with your code. I plan to write a CodeProject article to show how we can get these files made in different directories. Click Run -> Run code,and our basic application will work with all the right bits linked in. Write a simple program into our shellI have created a program which goes through a few basic functions with the database and shows error checking code. I was going to put it here and give the usual copy into Notepad instructions,but the file is quite long,so I have put it in the document zip file. Copy main.cpp out of the zip file,and replace the main.cpp you just created. Open it up to look at,and click Run -> Run Code in Notepad++ to execute it. It should compile and run perfectly with the Make file you already have. Every time you run it,you will see more and more records are created in the database. I will go through the main function here,explaining what each piece does. Collapse | Copy Codesrand ( time(NULL) );
printf("SQLite Demo program startn");
Seed the random number generator,this is needed because I will use the std::string l_filename = "datafile.sqlite";
std::ostringstream l_query;
sqlite3* l_sql_db = NULL;
Declare the variables. We need a string to hold the file name. printf("Opening DBn");
int rc = sqlite3_open(l_filename.c_str(),&l_sql_db);
if( rc ){
sqlite3_close(l_sql_db);
printf("Error couldn't open SQLite database %s",l_filename.c_str());
return 1;
};
Next,we open the database. If there is no file,it will be created; otherwise,the file that is there is used. If an invalid file is selected,this code doesn't cause an error. SQLite will only error when you start running RJM_SQLite_Resultset *pRS = NULL;
printf("Checking if table existsn");
pRS = SQL_Execute("SELECT name FROM sqlite_master " +
"WHERE type='table' and name='simple_table';",l_sql_db);
if (!pRS->Valid()) {
printf("Invalid result set returned (%s)n",pRS->GetLastError());
SAFE_DELETE(pRS);
sqlite3_close(l_sql_db);
return 0;
};
rc = pRS->GetRowCount();
SAFE_DELETE(pRS);
The program I have written only has one table. (You can have multiple tables in your SQLite tables.) This code runs a if (0!=rc) {
printf("Table existsn");
} else {
If a row is returned,the table is there and we can skip the next bit. Otherwise,we need to create it before continuing: Collapse | Copy Codeprintf("Table dosn't exist creating itn");
l_query.str("");
l_query << "CREATE TABLE [simple_table] (";
l_query << "[ID] INTEGER NOT NULL PRIMARY KEY,";
l_query << "[some_text] VARCHAR(255) NULL,";
l_query << "[some_number] INTEGER NULL,";
l_query << "[created] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NULL";
l_query << ")";
pRS = SQL_Execute(l_query.str().c_str(),pRS->GetLastError());
SAFE_DELETE(pRS);
sqlite3_close(l_sql_db);
return 0;
};
Now we create the table. Like working with all databases,this is just done by running SQL statements and acting on the returned information. I have used printf("Checking if table existsn");
pRS = SQL_Execute("SELECT name FROM sqlite_master WHERE " +
"type='table' and name='simple_table';",pRS->GetLastError());
SAFE_DELETE(pRS);
sqlite3_close(l_sql_db);
return 0;
};
rc = pRS->GetRowCount();
SAFE_DELETE(pRS);
if (0==rc) {
printf("Error table still dosn't exist despite " +
"the fact I created itn",l_sql_db);
sqlite3_close(l_sql_db);
return 0;
};
I have repeated the check for the table to make sure it does exist now. Collapse | Copy Code}; //End If table dosen't exist SAFE_DELETE(pRS);
This is the end of the Ccreate Table section. I have added an extra printf("Add some data to the tablen");
l_query.str("");
l_query << "insert into simple_table (some_text,some_number)";
l_query << " values ('Some text'," << GEN_ID << ")";
pRS = SQL_Execute(l_query.str().c_str(),pRS->GetLastError());
SAFE_DELETE(pRS);
sqlite3_close(l_sql_db);
return 0;
};
SAFE_DELETE(pRS);
Adding data is just simply executing another SQL query. There is lots of repeated error detection code in this program. In production programs,this can be hidden away in another function. Collapse | Copy Codeprintf("Listing out some datan");
l_query.str("");
l_query << "select ID,some_text,some_number,created FROM simple_table";
pRS = SQL_Execute(l_query.str().c_str(),pRS->GetLastError());
SAFE_DELETE(pRS);
sqlite3_close(l_sql_db);
return 0;
};
Yet another query,this time to return some data: Collapse | Copy Coderc = pRS->GetRowCount();
//Declare variables to hold data DB_DT_LONG r_id;
DB_DT_VARCHAR r_some_text;
DB_DT_LONG r_some_number;
DB_DT_TIMESTAMP r_created;
char buf[1024] = "";
First,get the number of rows returned,then declare the variables we will need to hold the data. This uses the macros declared in Glob_defs.h. Collapse | Copy Codefor (unsigned int c=0;c<rc;c++) {
Loop through the rows returned: Collapse | Copy CodepRS->GetColValueINTEGER(c,0,&r_id);
pRS->GetColValueVARCHAR(c,1,&r_some_text);
pRS->GetColValueINTEGER(c,2,&r_some_number);
pRS->GetColValueTIMESTAMP(c,3,&r_created);
Call functions from the resultset to collect the data. There are different functions for each timestamp. The parameters are the same: the row,the column and a pointer to an object to put the data into. Collapse | Copy Codeprintf("ID: %d,Text: %s,Num: %d,Created: %sn",r_id,r_some_text.c_str(),r_some_number,r_created.c_str());
This line outputs the data. };
SAFE_DELETE(pRS);
We now need to delete the resultset object Collapse | Copy Codeprintf("Closing DBn");
sqlite3_close(l_sql_db);
printf("SQLite Demo program endn");
return 0;
Finally,close the database and end the program. Download a program which views SQLite databasesWe can write lots of programs that use the same database and so can other people. I use a tool called SQLite Administrator to view the contents of files my programs create whilst programming and debugging. As you might expect,there are also SQLite ODBC drivers that could provide a great way of getting your data into Excel,or other applications. Another reason for using SQLite Administrator is that you can use it to test out SQL statements to get them working before putting them into your program's code. To get it,go to http://sqliteadmin.orbmu2k.de/ and download and install the program (link at the bottom of the page). I extracted the zip files into C:Program Files and put a shortcut on my quick launch bar. To use it,run the program,go to Database -> Open,and select c:codesqlite_hello_worlddatafile.sqlite. Enter a query like the following: Collapse | Copy Codeselect * from simple_table
Then,select Query -> Execute with result. You can use this tool to prototype queries before putting them into your applications. You can even right click on a table and select 'Show SQL' to show the Create statements. This way,you can create objects using this program and copy the create scripts into your code. Not only is this a simple file storage system,but you have all the facilities of a database at your disposal: Tables,Views,etc. Usage notesWhen using Edit Data,you need to click in the table name after you click on the Edit Data tab to get it to refresh. You then need to keep clicking on the table name to refresh the data. For some reason,if I create the database file in SQLite Administrator,it doesn't work when I access it in my code. As a workaround,I get my program to create the database and tables,and it works fine. The admin program has no problems accessing this data. Points of interestThere are other advantages to SQLite. I have written various programs that all use the same data file simultaneously. This means I could develop a suite of simple programs for specific tasks that share a common data source. Your data file naming convention will vary. Sometimes,you might want people to know it's a SQLite file and that they can access it with other programs. Other times,you won't want this. Unfortunately,it is not possible to restrict access to the raw files without purchasing a special version of SQLite libraries which support encrypted files. I recommend my users use TrueCrypt for storing their files as there is no budget for my programming in this area. This project leaves us with a messy directory. As well as our program code,we have .obj,.lib,and .exe files mixed in. The Make Clean option will clear them up for you,but it would be nice to have them in a separate place. I am planning to create an article showing exactly this. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |