如何提高::序列化到一个SQLite :: BLOB? [英] How to boost::serialize into a sqlite::blob?
问题描述
我工作的一个科学项目,该项目需要多个程序的能力。四处寻找可用的工具之后,我决定用这为我提供所需的功能,C ++标准库不提供这样的日期/时间管理等Boost库工作。
我的项目设置其处理一组数据从旧的,自制的,基于文件的纯文本数据库命令行:导入,转换,分析,报告
现在我达到了,我确实需要持久化的地步。所以,我包括提高::系列化,我发现真的很有用。我能够保存和恢复中数据集(不那么大,但不那么小),他们对(7000,48,15,10)-dataset。
我还使用SQLite C API来存储和管理命令默认,输出设置和变量元信息(单位,比例限制)。
东西穿过我的脑海:序列化到BLOB字段而不是独立的文件。可能有一些缺点,我还没有看到,但(总是有),但我认为它可以是一个很好的解决方案,适合我的需要。
我能够文本序列化到一个std :: string的,所以我可以做到这样:有,因为它仅使用普通字符没有任何困难。但我想二进制序列化为一滴。
应该如何填写我的INSERT查询时,我开始为了使用标准流?
哈。我以前从未使用过的sqlite3 C API。我从来没有写一个输出流缓冲
的实施。但看到我怎么可能会在未来一个C ++ codeBase的使用sqlite3的,我想我花了一些时间
- http://www.sqlite.org/c3ref/funclist.html
-
CP preference <一个href=\"http://en.cp$p$pference.com/w/cpp/io/basic_streambuf\">http://en.cp$p$pference.com/w/cpp/io/basic_streambuf
块引用>
因此,原来你的可以的打开BLOB字段增量IO 。不过,虽然你可以读/写BLOB,你不能改变大小(除通过单独的UPDATE语句)。
所以,我演示的步骤变成了:
- 插入记录表,结合一定的(固定)大小的零看待
- 在新插入的记录打开BLOB字段
- 包裹的blob手柄在自定义的派生自
blob_buf
对象的std :: basic_streambuf&LT;&GT;
并能与的std :: ostream的
用于写入到BLOB - 连载一些数据到
的ostream
- 刷新
- 自毁/清理
它的工作原理:)
在code在主
:
INT的main()
{
sqlite3的* DB = NULL;
INT RC = sqlite3_open_v2(test.sqlite3,&放大器;分贝,SQLITE_OPEN_READWRITE,NULL);
如果(RC!= SQLITE_OK){
的std :: CERR&LT;&LT; 数据库打开失败:&LT;&LT; sqlite3_errmsg(DB)LT;&LT; \\ n;
出口(255);
} // 1.插入记录表,结合一定的(固定)大小的零看待
sqlite3_int64插入= InsertRecord(DB); {
// 2.新插入的记录打开blob字段
// 3.包装在一个自定义的`从`性病:: basic_streambuf&LT派生blob_buf`对象中的BLOB手柄;&GT;`,可以用'的std :: ostream`用于写入到BLOB
blob_buf BUF(OpenBlobByRowId(DB,插入));
的std :: ostream的作家(安培; BUF); //这个流现在写入BLOB! // 4.连载一些数据到`ostream`
汽车有效载荷= {CanBeSerialized的Hello World,{1,2,3.4,1E7,-42.42}}; 提高::档案:: text_oarchive的OA(作家);
OA&LT;&LT;有效载荷;#如果0 //用于测试具有更大的数据
性病:: ifstream的IFS(TEST.CPP);
作家&LT;&LT; ifs.rdbuf();
#万一 // 5.冲洗
writer.flush(); // 6.自毁/清理
} sqlite3_close(DB);
// == == 7653 HEAP摘要:
// == == 7653使用在退出:0块0字节
// == == 7653总堆的使用情况:227 allocs,227的FreeS,123540字节分配
// == == 7653
// == == 7653的所有堆块被释放 - 无泄漏是可能的
}
您会认识列出的步骤。
要测试它,假设你创建一个新的SQLite数据库:
sqlite3的test.sqlite3&LT;&LT;&LT; CREATE TABLE DEMO(ID INTEGER PRIMARY KEY AUTOINCREMENT,FILE BLOB);
现在,一旦你已经运行程序,您可以查询它:
sqlite3的test.sqlite3&LT;&LT;&LT; SELECT * FROM DEMO;
1 | 22 ::系列化存档10 0 0 11的hello world 5 0 1 2 3.3999999999999999千万-42.420000000000002
如果您启用测试code(即把更多的数据比blob_size允许的话),你会看到越来越BLOB截断:
内容截断,在256个字节
完整的程序
的#include&LT; sqlite3.h&GT;
#包括LT&;串GT;
#包括LT&;&iostream的GT;
#包括LT&;&ostream的GT;
#包括LT&;&的fstream GT;
#包括LT&;升压/系列化/ vector.hpp&GT;
#包括LT&;升压/存档/ text_oarchive.hpp&GT;模板&LT; typename的图表类型名TraitsT =的std :: char_traits&LT;&图表GT; &GT;
类basic_blob_buf:公众的std :: basic_streambuf&LT;图表,TraitsT&GT;
{
sqlite3_blob * _blob; //资
INT max_blob_size; 的typedef的std :: basic_streambuf&LT;图表,TraitsT&GT; base_type;
枚举{BUFSIZE = 10}; //块大小 - 调整?
焦炭BUF [BUFSIZE + 1 / *的溢出字符* /] 为size_t cur_offset;
的std :: ostream的调试; //没有抄袭
basic_blob_buf(basic_blob_buf常量和放大器;)=删除;
basic_blob_buf&安培;运算符=(const的basic_blob_buf&安培)=删除;
上市:
basic_blob_buf(sqlite3_blob * BLOB,INT MAX_SIZE = -1)
:_blob(BLOB)
max_blob_size(MAX_SIZE)
BUF {0},
cur_offset(0),
//调试(的std :: cerr.rdbuf())//或者只是使用`nullptr`共进晚餐preSS调试输出
调试(nullptr)
{
debug.setf(性病:: IOS :: unitbuf);
如果(max_blob_size == -1){
max_blob_size = sqlite3_blob_bytes(_blob);
调试&LT;&LT; max_blob_size检测:&LT;&LT; max_blob_size&LT;&LT; \\ n;
}
这 - &GT; SETP(BUF,BUF + BUFSIZE);
} INT溢出(INT C = base_type :: ::的traits_type EOF())
{
汽车putpointer =这个 - &GT; PPTR();
如果(C!= base_type :: ::的traits_type EOF())
{
//添加字符 - 尽管PPTR可能epptr
* putpointer ++ = C;
} 如果(cur_offset&GT; =为size_t(max_blob_size))
返回base_type ::的traits_type :: EOF(); //信号故障 为size_t N =的std ::距离(这个 - &GT; PBASE(),putpointer);
调试&LT;&LT; 溢出&LT;&LT; N'LT;&LT; &LT在字节;&LT; cur_offset&LT;&LT; \\ n;
如果(cur_offset + N&GT;为size_t(max_blob_size))
{
的std :: CERR&LT;&LT; 内容被截断在&LT;&LT; max_blob_size&LT;&LT; 字节的\\ n;
N =为size_t(max_blob_size) - cur_offset;
} 如果(SQLITE_OK = sqlite3_blob_write(_blob,这 - &GT;!PBASE()中,n,cur_offset))
{
调试&LT;&LT; sqlite3_blob_write报告了一个错误。\\ n;
返回base_type ::的traits_type :: EOF(); //信号故障
} cur_offset + = N; 如果(这 - &GT; PPTR()&GT;(这 - &GT; PBASE()+ N))
{
调试&LT;&LT; 挂起的数据还没有被写入
返回base_type ::的traits_type :: EOF(); //信号故障
} //重置缓冲区
这 - &GT; SETP(BUF,BUF + BUFSIZE); 返回base_type :: ::的traits_type not_eof(C);
} 诠释同步()
{
返回base_type :: ::的traits_type EOF()=溢出()!;
} 〜basic_blob_buf(){
sqlite3_blob_close(_blob);
}
};typedef的basic_blob_buf&LT;焦炭&GT; blob_buf;结构CanBeSerialized
{
标准::字符串sometext;
的std ::矢量&lt;&双GT; a_vector; 模板&LT;类归档和GT;
无效连载(归档和放大器; AR,const的无符号整型版)
{
AR&安培;提高::系列化:: make_nvp(SomeText则会,sometext);
AR&安培;提高::系列化:: make_nvp(a_vector,a_vector);
}
};#定义MAX_BLOB_SIZE 256sqlite3_int64 InsertRecord(* sqlite3的DB)
{
sqlite3_stmt *语句= NULL;
INT RC = sqlite3_ prepare_v2(DB,INSERT INTO DEMO(ID,FILE)VALUES(NULL,)?,-1,&安培;语句,NULL); 如果(RC!= SQLITE_OK){
的std :: CERR&LT;&LT; prepare失败:&LT;&LT; sqlite3_errmsg(DB)LT;&LT; \\ n;
出口(255);
}其他{
RC = sqlite3_bind_zeroblob(语句,1,MAX_BLOB_SIZE);
如果(RC!= SQLITE_OK){
的std :: CERR&LT;&LT; bind_zeroblob失败:&所述;&下; sqlite3_errmsg(DB)LT;&LT; \\ n;
出口(255);
}
RC = sqlite3_step(语句);
如果(RC!= SQLITE_DONE)
{
的std :: CERR&LT;&LT; 执行失败:&LT;&LT; sqlite3_errmsg(DB)LT;&LT; \\ n;
出口(255);
}
}
RC = sqlite3_finalize(语句);
如果(RC!= SQLITE_OK)
{
的std :: CERR&LT;&LT; 最终确定语句失败:&LT;&LT; sqlite3_errmsg(DB)LT;&LT; \\ n;
出口(255);
} 返回sqlite3_last_insert_rowid(DB);
}sqlite3_blob * OpenBlobByRowId(sqlite3的*分贝,sqlite3_int64 ROWID)
{
sqlite3_blob * pBlob = NULL;
INT RC = sqlite3_blob_open(DB,主,DEMO,文件,ROWID,1 / * * RW /,&安培; pBlob); 如果(RC!= SQLITE_OK){
的std :: CERR&LT;&LT; blob_open失败:&所述;&下; sqlite3_errmsg(DB)LT;&LT; \\ n;
出口(255);
}
返回pBlob;
}诠释的main()
{
sqlite3的* DB = NULL;
INT RC = sqlite3_open_v2(test.sqlite3,&放大器;分贝,SQLITE_OPEN_READWRITE,NULL);
如果(RC!= SQLITE_OK){
的std :: CERR&LT;&LT; 数据库打开失败:&LT;&LT; sqlite3_errmsg(DB)LT;&LT; \\ n;
出口(255);
} // 1.插入记录表,结合一定的(固定)大小的零看待
sqlite3_int64插入= InsertRecord(DB); {
// 2.新插入的记录打开blob字段
// 3.包装在一个自定义的`从`性病:: basic_streambuf&LT派生blob_buf`对象中的BLOB手柄;&GT;`,可以用'的std :: ostream`用于写入到BLOB
blob_buf BUF(OpenBlobByRowId(DB,插入));
的std :: ostream的作家(安培; BUF); //这个流现在写入BLOB! // 4.连载一些数据到`ostream`
汽车有效载荷= {CanBeSerialized的Hello World,{1,2,3.4,1E7,-42.42}}; 提高::档案:: text_oarchive的OA(作家);
OA&LT;&LT;有效载荷;#如果0 //用于测试具有更大的数据
性病:: ifstream的IFS(TEST.CPP);
作家&LT;&LT; ifs.rdbuf();
#万一 // 5.冲洗
writer.flush(); // 6.自毁/清理
} sqlite3_close(DB);
}
PS。我已经把错误处理......很简陋。你将要为大家介绍一个辅助函数来检查错误sqlite3的codeS和转化为异常(S)也许。 :)
I am working on a scientific project which requires several program abilities. After looking around for available tools I decided to work with Boost library which provided me needed features that C++ standard library does not provide such as date/time management, etc.
My project is set of command line which process a bunch of data from a old, homemade, plain-text file-based database: import, conversion, analysis, reporting.
Now I reached the point where I do need persistence. So I included boost::serialization that I found really useful. I am able to store and restore 'medium' dataset (not-so-big but not-so-small), they are about (7000,48,15,10)-dataset.
I also use SQLite C API to store and manage command defaults, output settings and variables meta informations (units, scale, limits).
Something crossed my mind: serialize into blob field instead of separate files. There might be some drawback that I haven't seen yet (there always is) but I think it can be a good solution that will suits my needs.
I am able to text-serialize into a std::string so I can do it that way: there is no difficulties because it only uses normal characters. But I would like to binary-serialize into a blob.
How should I proceed in order to use standard stream when filling my INSERT query?
Hah. I've never used sqlite3 C API before. And I've never written an output streambuf
implementation. But seeing how I will probably be using sqlite3 in a c++ codebase in the future, I thought I'd spent some time with
- http://www.sqlite.org/c3ref/funclist.html
cppreference http://en.cppreference.com/w/cpp/io/basic_streambuf
So it turns out you can open a blob field for incremental IO. However, though you can read/write the BLOB, you can't change the size (except via a separate UPDATE statement).
So, the steps for my demonstration became:
- insert a record into a table, binding a "zero-blob" of a certain (fixed) size
- open the blob field in the newly inserted record
- wrap the blob handle in a custom
blob_buf
object that derives fromstd::basic_streambuf<>
and can be used withstd::ostream
to write to that blob - serialize some data into the
ostream
- flush
- destruct/cleanup
It works :)
The code in main
:
int main()
{
sqlite3 *db = NULL;
int rc = sqlite3_open_v2("test.sqlite3", &db, SQLITE_OPEN_READWRITE, NULL);
if (rc != SQLITE_OK) {
std::cerr << "database open failed: " << sqlite3_errmsg(db) << "\n";
exit(255);
}
// 1. insert a record into a table, binding a "zero-blob" of a certain (fixed) size
sqlite3_int64 inserted = InsertRecord(db);
{
// 2. open the blob field in the newly inserted record
// 3. wrap the blob handle in a custom `blob_buf` object that derives from `std::basic_streambuf<>` and can be used with `std::ostream` to write to that blob
blob_buf buf(OpenBlobByRowId(db, inserted));
std::ostream writer(&buf); // this stream now writes to the blob!
// 4. serialize some data into the `ostream`
auto payload = CanBeSerialized { "hello world", { 1, 2, 3.4, 1e7, -42.42 } };
boost::archive::text_oarchive oa(writer);
oa << payload;
#if 0 // used for testing with larger data
std::ifstream ifs("test.cpp");
writer << ifs.rdbuf();
#endif
// 5. flush
writer.flush();
// 6. destruct/cleanup
}
sqlite3_close(db);
// ==7653== HEAP SUMMARY:
// ==7653== in use at exit: 0 bytes in 0 blocks
// ==7653== total heap usage: 227 allocs, 227 frees, 123,540 bytes allocated
// ==7653==
// ==7653== All heap blocks were freed -- no leaks are possible
}
You'll recognize the steps outlined.
To test it, assume you create a new sqlite database:
sqlite3 test.sqlite3 <<< "CREATE TABLE DEMO(ID INTEGER PRIMARY KEY AUTOINCREMENT, FILE BLOB);"
Now, once you have run the program, you can query for it:
sqlite3 test.sqlite3 <<< "SELECT * FROM DEMO;"
1|22 serialization::archive 10 0 0 11 hello world 5 0 1 2 3.3999999999999999 10000000 -42.420000000000002
If you enable the test code (that puts more data than the blob_size allows) you'll see the blob getting truncated:
contents truncated at 256 bytes
Full Program
#include <sqlite3.h>
#include <string>
#include <iostream>
#include <ostream>
#include <fstream>
#include <boost/serialization/vector.hpp>
#include <boost/archive/text_oarchive.hpp>
template<typename CharT, typename TraitsT = std::char_traits<CharT> >
class basic_blob_buf : public std::basic_streambuf<CharT, TraitsT>
{
sqlite3_blob* _blob; // owned
int max_blob_size;
typedef std::basic_streambuf<CharT, TraitsT> base_type;
enum { BUFSIZE = 10 }; // Block size - tuning?
char buf[BUFSIZE+1/*for the overflow character*/];
size_t cur_offset;
std::ostream debug;
// no copying
basic_blob_buf(basic_blob_buf const&) = delete;
basic_blob_buf& operator= (basic_blob_buf const&) = delete;
public:
basic_blob_buf(sqlite3_blob* blob, int max_size = -1)
: _blob(blob),
max_blob_size(max_size),
buf {0},
cur_offset(0),
// debug(std::cerr.rdbuf()) // or just use `nullptr` to suppress debug output
debug(nullptr)
{
debug.setf(std::ios::unitbuf);
if (max_blob_size == -1) {
max_blob_size = sqlite3_blob_bytes(_blob);
debug << "max_blob_size detected: " << max_blob_size << "\n";
}
this->setp(buf, buf + BUFSIZE);
}
int overflow (int c = base_type::traits_type::eof())
{
auto putpointer = this->pptr();
if (c!=base_type::traits_type::eof())
{
// add the character - even though pptr might be epptr
*putpointer++ = c;
}
if (cur_offset >= size_t(max_blob_size))
return base_type::traits_type::eof(); // signal failure
size_t n = std::distance(this->pbase(), putpointer);
debug << "Overflow " << n << " bytes at " << cur_offset << "\n";
if (cur_offset+n > size_t(max_blob_size))
{
std::cerr << "contents truncated at " << max_blob_size << " bytes\n";
n = size_t(max_blob_size) - cur_offset;
}
if (SQLITE_OK != sqlite3_blob_write(_blob, this->pbase(), n, cur_offset))
{
debug << "sqlite3_blob_write reported an error\n";
return base_type::traits_type::eof(); // signal failure
}
cur_offset += n;
if (this->pptr() > (this->pbase() + n))
{
debug << "pending data has not been written";
return base_type::traits_type::eof(); // signal failure
}
// reset buffer
this->setp(buf, buf + BUFSIZE);
return base_type::traits_type::not_eof(c);
}
int sync()
{
return base_type::traits_type::eof() != overflow();
}
~basic_blob_buf() {
sqlite3_blob_close(_blob);
}
};
typedef basic_blob_buf<char> blob_buf;
struct CanBeSerialized
{
std::string sometext;
std::vector<double> a_vector;
template<class Archive>
void serialize(Archive & ar, const unsigned int version)
{
ar & boost::serialization::make_nvp("sometext", sometext);
ar & boost::serialization::make_nvp("a_vector", a_vector);
}
};
#define MAX_BLOB_SIZE 256
sqlite3_int64 InsertRecord(sqlite3* db)
{
sqlite3_stmt *stmt = NULL;
int rc = sqlite3_prepare_v2(db, "INSERT INTO DEMO(ID, FILE) VALUES(NULL, ?)", -1, &stmt, NULL);
if (rc != SQLITE_OK) {
std::cerr << "prepare failed: " << sqlite3_errmsg(db) << "\n";
exit(255);
} else {
rc = sqlite3_bind_zeroblob(stmt, 1, MAX_BLOB_SIZE);
if (rc != SQLITE_OK) {
std::cerr << "bind_zeroblob failed: " << sqlite3_errmsg(db) << "\n";
exit(255);
}
rc = sqlite3_step(stmt);
if (rc != SQLITE_DONE)
{
std::cerr << "execution failed: " << sqlite3_errmsg(db) << "\n";
exit(255);
}
}
rc = sqlite3_finalize(stmt);
if (rc != SQLITE_OK)
{
std::cerr << "finalize stmt failed: " << sqlite3_errmsg(db) << "\n";
exit(255);
}
return sqlite3_last_insert_rowid(db);
}
sqlite3_blob* OpenBlobByRowId(sqlite3* db, sqlite3_int64 rowid)
{
sqlite3_blob* pBlob = NULL;
int rc = sqlite3_blob_open(db, "main", "DEMO", "FILE", rowid, 1/*rw*/, &pBlob);
if (rc != SQLITE_OK) {
std::cerr << "blob_open failed: " << sqlite3_errmsg(db) << "\n";
exit(255);
}
return pBlob;
}
int main()
{
sqlite3 *db = NULL;
int rc = sqlite3_open_v2("test.sqlite3", &db, SQLITE_OPEN_READWRITE, NULL);
if (rc != SQLITE_OK) {
std::cerr << "database open failed: " << sqlite3_errmsg(db) << "\n";
exit(255);
}
// 1. insert a record into a table, binding a "zero-blob" of a certain (fixed) size
sqlite3_int64 inserted = InsertRecord(db);
{
// 2. open the blob field in the newly inserted record
// 3. wrap the blob handle in a custom `blob_buf` object that derives from `std::basic_streambuf<>` and can be used with `std::ostream` to write to that blob
blob_buf buf(OpenBlobByRowId(db, inserted));
std::ostream writer(&buf); // this stream now writes to the blob!
// 4. serialize some data into the `ostream`
auto payload = CanBeSerialized { "hello world", { 1, 2, 3.4, 1e7, -42.42 } };
boost::archive::text_oarchive oa(writer);
oa << payload;
#if 0 // used for testing with larger data
std::ifstream ifs("test.cpp");
writer << ifs.rdbuf();
#endif
// 5. flush
writer.flush();
// 6. destruct/cleanup
}
sqlite3_close(db);
}
PS. I've kept error handling... very crude. You'll want to introduce a helper function to check sqlite3 errorcodes and translate into exception(s) maybe. :)
这篇关于如何提高::序列化到一个SQLite :: BLOB?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!