[Database]初试SQLite，看看能不能在小东西内用用
手头有一个小工具，需要每次都调用WebSevice从服务器获取数据，但是这种数据属于历史数据，基本上没有改动，而长期下来，数据量可能会比较大。试想每次都重新导入，本地程序会有较大的Performance问题，也浪费网络资源。所以考虑利用SQLite作为本地数据库把这些数据保存起来，而只是向服务器获取新的数据，并对历史数据做极小量的更新。
但是Piaoger并没有SQLite经验，所以模仿网上的代码，练习练习，修改修改，并加了N多注释，算是对SQLite的CURD（创建、更新、读取和删除）有了一个基本的了解：
// References:
// The.Definitive.Guide.to.SQLite[2006][EN][PDF].
// Part of Code is borrowed from:
//http://blog.csdn.net/yang_rong_yong/article/details/2832727
//
//#include "Global/Precompiled.h"
// SQLite3
#include "http://www.cnblogs.com/SQLite/sqlite3/sqlite3.h"
//TUT
#include <BedRock/TestSupport/AutoTest.h>
namespace tut
{
struct test_SQLite3_data
{
void testSqliteDatabase()
{
// Create a new Database "testSQLite3.db"
// with a table "testtable" with 2 columns Column1[Char]; Column2[Blob]
//
createDatabase();
// Query above database and "testtable" table
queryDatabase();
// Create a new table "filetable" with 2 column [filename, filecontent]
// and insert a "picture.jpg" to the table.
insertFile();
// Query "filetable" and save the file content to "c:\file_n.jpg"
//
queryFile();
// Update "picture.jpg" from "File1.jpg" to "File2.jpg"
updateFile();
// Delete "picture.jpg"
// Sql differences of INSERT, UPDATE and DELETE:
// char* sql = "insert into filetable values('picture.jpg', ?);";
// char* sql = "update filetable set filecontent=? where filename='picture.jpg'";
// char* sql = "delete from filetable where filename='picture.jpg'";
//
deleteFileFromDatabase();
}
void createDatabase()
{
// Connecting to a Database
// It involves little more than opening a file.
// Every SQLite database is stored in a single operating system file.
// sqlite3_open is basically just a system call for opening a file.
//
sqlite3* database = NULL;
int ret = sqlite3_open("c:\\testSqlite3.db", &database);
if( ret != SQLITE_OK ) return;
// Execute and create a "testtable" which has 2 columns.
// testtable: Column1[Char]; Column2[Blob]
// Available types: TEXT /NUMERIC /INTEGER /REAL /NONE
// sqlite3_exec wraps sqlite3_prepare and sqlite3_step
// It is a quick and easy way to execute INSERT, UPDATE, and DELETE statements.
// It is capable of processing a string of multiple SQL statements delimited by semicolons
// and running them all together.
// it parses the SQL string, identifies individual statements, and then processes
// them one by one.
//
char* error = NULL;
ret = sqlite3_exec(database, "create table testtable(column1 char(20), column2 BLOB)", 0, 0, &error );
if( ret != SQLITE_OK ) return;
// SQL statement with named parameter.
// It's noted that a named parameter "param" is added in the statement.
// You can get the index of this parameter with sqlite3_bind_parameter_index.
// In a SQL statement, one or more literals can be replaced by a parameter
// (also called a host parameter name) of the forms ?, :name, and $var.
// The parameter of the form ? is called a positional parameter.
// The parameter of the form :name is called a named parameter, where name is an
// alphanumeric identifier.
// The parameter of the form $var is called a TCL parameter, where var
// is a variable name according to the syntax rules of the TCL programming language.
// An example of numbered parameter:
// "insert into testtable values('column0x1',?101)"
// "insert into testtable values('column0x1',:param)"
//
char* sql = "insert into testtable values('column0x1',:param)";
// Preparation: prepare statement with specified sql string.
// A prepared SQL statement is a string containing SQL commands passed to sqlite3_prepare()
// The parser, tokenizer, and code generator prepare the SQL statement
// by compiling it into VDBE byte code.
// sqlite3_prepare talks directly to the compiler.
// The compiler creates a sqlite3_stmt handle (statement handle).
// Statement handles contains the byte code and all other resources
// needed to execute the command and iterate over the result set.
//
sqlite3_stmt* statement = NULL;
const char* prepareError = NULL;
ret = sqlite3_prepare(database, sql,strlen(sql), &statement, &prepareError);
if( ret != SQLITE_OK ) return;
// Get index of the named parameter
// It takes a parameter name and returns the corresponding parameter number.
// This is the number you use to bind the value to its parameter.
//
int index = sqlite3_bind_parameter_index(statement, ":param");
// Assign (or bind) values to parameters in a prepared SQL statement.
//
static const char* value = "1 2 3 4 5 6 7 8 9 0";
ret = sqlite3_bind_blob(statement, index, value, strlen(value), SQLITE_STATIC);
if( ret != SQLITE_OK ) return;
// Executes above prepared query.
//
ret = sqlite3_step(statement);
if( ret != SQLITE_DONE)return;
// Finalize prepared statement.
// All prepared statements must be finalized
ret = sqlite3_finalize(statement);
assert(ret == SQLITE_OK);
// Closes the connection given by db.
//
ret = sqlite3_close(database);
assert(ret == SQLITE_OK);
}
void queryDatabase()
{
// Connecting to Database
sqlite3* database = NULL;
int ret = sqlite3_open("c:\\testSQLite3.db", &database);
if( ret != SQLITE_OK ) return;
// Select any rows in "testtable"
char* sql = "select * from testtable";
sqlite3_stmt* statement = 0;
const char* prepareError = NULL;
ret = sqlite3_prepare(database, sql, strlen(sql), &statement, &prepareError);
if( ret != SQLITE_OK ) return;
// Iterate through all resulted rows.
// Gets name/value/length
int length = 0;
const unsigned char* name = NULL;
const void* value = NULL;
while(true)
{
ret = sqlite3_step(statement);
if( ret != SQLITE_ROW ) break;
name = sqlite3_column_text(statement, 0);
value = sqlite3_column_blob(statement, 1);
length = sqlite3_column_bytes(statement, 1);
}
// Finalize prepared statement and close connection..
ret = sqlite3_finalize(statement);
assert(ret == SQLITE_OK);
ret = sqlite3_close(database);
assert(ret == SQLITE_OK);
}
void insertFile()
{
// Connecting to a Database
sqlite3* database = NULL;
int ret = sqlite3_open("c:\\testSqlite3.db", &database);
if( ret != SQLITE_OK ) return;
// Create a new table "filetable"
// Columns[filename, filecontent]
//
char* error = NULL;
sqlite3_exec(database, "create table filetable(filename varchar(128) UNIQUE, filecontent BLOB);", 0, 0, &error);
if( ret != SQLITE_OK ) return;
// Prepare statement to insert a file.
sqlite3_stmt* statement = 0;
const char* prepareError = NULL;
char* sql = "insert into filetable values('picture.jpg', ?);";
ret = sqlite3_prepare(database, sql, strlen(sql) , &statement, &prepareError);
if( ret != SQLITE_OK ) return;
// Load stream for specified file
FILE* fileHandle = NULL;
long filesize = 0;
char* fileStream = NULL;
fileHandle = fopen("S:\\Workspace\\Test\\FileStore\\Picture\\file.jpg", "rb");
if(fileHandle != NULL)
{
// get file size
fseek(fileHandle, 0, SEEK_END);
filesize = ftell(fileHandle);
fseek(fileHandle, 0, SEEK_SET);
// read the file into filestream
fileStream = new char[filesize + 1];
size_t size = fread(fileStream, sizeof(char), filesize + 1, fileHandle);
fclose(fileHandle);
}
int index = 1;
ret = sqlite3_bind_blob(statement, index, fileStream, filesize, SQLITE_STATIC);
if( ret != SQLITE_OK ) return;
// execute the statement to insert the file to database.
ret = sqlite3_step(statement);
if( ret != SQLITE_OK ) return;
// Finalize prepared statement.
ret = sqlite3_finalize(statement);
assert(ret == SQLITE_OK);
ret = sqlite3_close(database);
assert(ret == SQLITE_OK);
}
void queryFile()
{
// Connecting to a Database
sqlite3* database = NULL;
int ret = sqlite3_open("c:\\testSqlite3.db", &database);
if( ret != SQLITE_OK ) return;
// Try to get the file from Database.
sqlite3_stmt* statement = 0;
const char* prepareError = NULL;
const char* selectSql = "select * from filetable;";
ret = sqlite3_prepare(database, selectSql, strlen(selectSql), &statement, 0);
if( ret != SQLITE_OK ) return;
int rowCount = 0;
while(true)
{
ret = sqlite3_step(statement);
if( ret != SQLITE_ROW ) break;
// get file content
const unsigned char * fileName = sqlite3_column_text(statement, 0);
const void* fileContent = sqlite3_column_blob(statement, 1);
int size = sqlite3_column_bytes(statement, 1);
char to[256];
size_t nCount = strlen(to);
int nResult = _snprintf(to, nCount, "c:\\imagefile_%d.jpg", rowCount);
// write out the file content to "c:\imagefile_N.jpg".
FILE* outFile;
outFile = fopen((const char *)to, "wb");
if(outFile != NULL)
{
size_t ret = fwrite(fileContent, sizeof(char), size, outFile);
fclose(outFile);
}
}
ret = sqlite3_finalize(statement);
assert(ret == SQLITE_OK);
ret = sqlite3_close(database);
assert(ret == SQLITE_OK);
}
void updateFile()
{
// Connecting to a Database
sqlite3* database = NULL;
int ret = sqlite3_open("c:\\testSqlite3.db", &database);
if( ret != SQLITE_OK ) return;
// Prepare statement to insert a file.
sqlite3_stmt* statement = 0;
const char* prepareError = NULL;
char* sql = "update filetable set filecontent=? where filename='picture.jpg'";
ret = sqlite3_prepare(database, sql, /*strlen(sql)*/-1 , &statement, &prepareError);
assert(ret == SQLITE_OK);
if( ret != SQLITE_OK ) return;
// Load stream for specified file
FILE* fileHandle = NULL;
long filesize = 0;
char* fileStream = NULL;
fileHandle = fopen("S:\\Workspace\\Test\\FileStore\\Picture\\File2.jpg", "rb");
if(fileHandle != NULL)
{
// get file size
fseek(fileHandle, 0, SEEK_END);
filesize = ftell(fileHandle);
fseek(fileHandle, 0, SEEK_SET);
// read the file into filestream
fileStream = new char[filesize + 1];
size_t size = fread(fileStream, sizeof(char), filesize + 1, fileHandle);
fclose(fileHandle);
}
int index = 1;
ret = sqlite3_bind_blob(statement, index, fileStream, filesize, SQLITE_STATIC);
if( ret != SQLITE_OK ) return;
// execute the statement to insert the file to database.
ret = sqlite3_step(statement);
if( ret != SQLITE_OK ) return;
ret = sqlite3_finalize(statement);
assert(ret == SQLITE_OK);
ret = sqlite3_close(database);
assert(ret == SQLITE_OK);
}
void deleteFileFromDatabase()
{
// Connecting to a Database
sqlite3* database = NULL;
int ret = sqlite3_open("c:\\testSqlite3.db", &database);
if( ret != SQLITE_OK ) return;
// Prepare statement to insert a file.
sqlite3_stmt* statement = 0;
const char* prepareError = NULL;
char* sql = "delete from filetable where filename='picture.jpg'";
ret = sqlite3_prepare(database, sql, /*strlen(sql)*/-1 , &statement, &prepareError);
assert(ret == SQLITE_OK);
if( ret != SQLITE_OK ) return;
// execute the statement to insert the file to database.
ret = sqlite3_step(statement);
if( ret != SQLITE_DONE ) return;
ret = sqlite3_finalize(statement);
assert(ret == SQLITE_OK);
ret = sqlite3_close(database);
assert(ret == SQLITE_OK);
}
};
typedef test_group<test_SQLite3_data> TestGroup;
TestGroup ThisTestGroup("test_SQLite3_data");
typedef TestGroup::object TestObject;
template<>
template<>
void TestObject::test<1>()
{
testSqliteDatabase();
}
};
在SqliteSpy中看看结果吧:
后来发现基于QT写的Sqliteman更好用，还能预览：
http://sourceforge.net/projects/sqliteman
- Piaoger
