std::string CWRecorder::dump()
{
using namespace boost;
format day_fmt("%04d/%02d/%02d");
format time_fmt("%02d:%02d:%02d");
format table_name_fmt("%s_%04d%02d%02d_%02d%02d%02d");
// get current time
time_t raw_time;
tm* ptm;
time(&raw_time);
ptm = localtime(&raw_time);
day_fmt%(ptm->tm_year+1900)%(ptm->tm_mon+1)%(ptm->tm_mday);
time_fmt%(ptm->tm_hour)%(ptm->tm_min)%(ptm->tm_sec);
table_name_fmt%name()%(ptm->tm_year+1900)%(ptm->tm_mon+1)%(ptm->tm_mday)%(ptm->tm_hour)%(ptm->tm_min)%(ptm->tm_sec);
// add a record to RecordSummary table
CppSQLite3DB* db = MyDatabase::shared_output_database();
if(!db->tableExists("RecordSummary"))
{
throw HException("RecordSummary does not exist in output database");
}
format value_fmt("insert into RecordSummary values('%s','%s','%s')");
value_fmt%day_fmt.str()%time_fmt.str()%table_name_fmt.str();
db->execDML(value_fmt.str().c_str());
return table_name_fmt.str();
}
bool CheckCreateSettingTable(CppSQLite3DB &dbTask)
{
try{
//如果表格不存在，则创建表格
if (!dbTask.tableExists("T_Setting")) //创建事件日志表
{
//数据库字段：任务id，任务类型，任务时间，上次提示时间，提示语句等。
//last_run_time可以用来辅助确定提示是否已经执行，避免重复
dbTask.execDML("Create table T_Setting("
"key integer , " //key
"value char[1024]);" //value
);
//为类型字段建立索引
dbTask.execDML("create index idx_id on T_Setting(key);");
}
return true;
}
catch(CppSQLite3Exception &exp)
{
exp;
ATLTRACE("error:%s\n",exp.errorMessage());
ATLASSERT(FALSE);
return false;
}
}
bool CheckCreateLogTable(CppSQLite3DB &dbTask)
{
try{
//如果表格不存在，则创建表格
if (!dbTask.tableExists("T_log")) //创建事件日志表
{
//数据库字段：任务id，任务类型，任务时间，上次提示时间，提示语句等。
//last_run_time可以用来辅助确定提示是否已经执行，避免重复
dbTask.execDML("Create table T_log("
"id integer PRIMARY KEY AUTOINCREMENT, " //唯一id
"modal char[128],"
"time_log integer, " //时间
"type integer, " //状态
"value1 integer, " //值1
"value2 integer, " //值2
"message char[1024]);" //提示语句
);
//为类型字段建立索引
dbTask.execDML("create index idx_time on T_log(time_log);");
}
return true;
}
catch(CppSQLite3Exception &exp)
{
exp;
ATLTRACE("error:%s\n",exp.errorMessage());
ATLASSERT(FALSE);
return false;
}
}
BOOL Maxthon3PlugIn::IsWorked()
{
BOOL bRet = FALSE;
if (m_pMemFavoriteDB)
{
CppSQLite3DB objSqliteDatabase;
objSqliteDatabase.openmem(m_pMemFavoriteDB, "");
bRet = objSqliteDatabase.tableExists("MyFavNodes");
if (bRet)
{
CppSQLite3Table objSqliteTable = objSqliteDatabase.getTable("select * from MyFavNodes");
if (14 != objSqliteTable.numFields())
{
bRet = FALSE;
}
}
}
return bRet;
}
CppSQLite3DB* MyDatabase::shared_output_database()// used to output records
{
if(!CommandOption::Option().bIsInit)return NULL;
static MyDatabase global_out_db(CommandOption::Option().OutDir.c_str(),CommandOption::Option().OutDatabaseFile.c_str(),true);
CppSQLite3DB* db = global_out_db.get_db();
if(!db->tableExists("RecordSummary"))
{
// DATE(YYYY-MM-DD); TIME(HH:MM:SS)
db->execDML("create table RecordSummary (date DATE, time TIME, table_name TEXT);");
}
return db;
}
int main(int argc, char** argv)
{
try
{
int i, fld;
time_t tmStart, tmEnd;
CppSQLite3DB db;
cout << "SQLite Header Version: " << CppSQLite3DB::SQLiteHeaderVersion() << endl;
cout << "SQLite Library Version: " << CppSQLite3DB::SQLiteLibraryVersion() << endl;
cout << "SQLite Library Version Number: " << CppSQLite3DB::SQLiteLibraryVersionNumber() << endl;
remove(gszFile);
db.open(gszFile);
cout << endl << "emp table exists=" << (db.tableExists("emp") ? "TRUE":"FALSE") << endl;
cout << endl << "Creating emp table" << endl;
db.execDML("create table emp(empno int, empname char(20));");
cout << endl << "emp table exists=" << (db.tableExists("emp") ? "TRUE":"FALSE") << endl;
////////////////////////////////////////////////////////////////////////////////
// Execute some DML, and print number of rows affected by each one
////////////////////////////////////////////////////////////////////////////////
cout << endl << "DML tests" << endl;
int nRows = db.execDML("insert into emp values (7, 'David Beckham');");
cout << nRows << " rows inserted" << endl;
nRows = db.execDML("update emp set empname = 'Christiano Ronaldo' where empno = 7;");
cout << nRows << " rows updated" << endl;
nRows = db.execDML("delete from emp where empno = 7;");
cout << nRows << " rows deleted" << endl;
////////////////////////////////////////////////////////////////////////////////
// Transaction Demo
// The transaction could just as easily have been rolled back
////////////////////////////////////////////////////////////////////////////////
int nRowsToCreate(25000);
cout << endl << "Transaction test, creating " << nRowsToCreate;
cout << " rows please wait..." << endl;
tmStart = time(0);
cout << "PRE-TXN AUTO COMMIT=" << (db.IsAutoCommitOn() ? "Y" : "N") << endl;
db.execDML("begin transaction;");
cout << "IN-TXN AUTO COMMIT=" << (db.IsAutoCommitOn() ? "Y" : "N") << endl;
for (i = 0; i < nRowsToCreate; i++)
{
char buf[128];
sprintf(buf, "insert into emp values (%d, 'Empname%06d');", i, i);
db.execDML(buf);
}
db.execDML("commit transaction;");
cout << "POST-TXN AUTO COMMIT=" << (db.IsAutoCommitOn() ? "Y" : "N") << endl;
tmEnd = time(0);
////////////////////////////////////////////////////////////////////////////////
// Demonstrate CppSQLiteDB::execScalar()
////////////////////////////////////////////////////////////////////////////////
cout << db.execScalar("select count(*) from emp;") << " rows in emp table in ";
cout << tmEnd-tmStart << " seconds (that was fast!)" << endl;
////////////////////////////////////////////////////////////////////////////////
// Re-create emp table with auto-increment field
////////////////////////////////////////////////////////////////////////////////
cout << endl << "Auto increment test" << endl;
db.execDML("drop table emp;");
db.execDML("create table emp(empno integer primary key, empname char(20));");
cout << nRows << " rows deleted" << endl;
for (i = 0; i < 5; i++)
{
char buf[128];
sprintf(buf, "insert into emp (empname) values ('Empname%06d');", i+1);
db.execDML(buf);
cout << " primary key: " << (int)db.lastRowId() << endl;
}
////////////////////////////////////////////////////////////////////////////////
// Query data and also show results of inserts into auto-increment field
////////////////////////////////////////////////////////////////////////////////
cout << endl << "Select statement test" << endl;
CppSQLite3Query q = db.execQuery("select * from emp order by 1;");
for (fld = 0; fld < q.numFields(); fld++)
{
cout << q.fieldName(fld) << "(" << q.fieldDeclType(fld) << ")|";
}
cout << endl;
while (!q.eof())
{
cout << q.fieldValue(0) << "|";
cout << q.fieldValue(1) << "|" << endl;
q.nextRow();
}
////////////////////////////////////////////////////////////////////////////////
// SQLite's printf() functionality. Handles embedded quotes and NULLs
////////////////////////////////////////////////////////////////////////////////
cout << endl << "SQLite sprintf test" << endl;
CppSQLite3Buffer bufSQL;
bufSQL.format("insert into emp (empname) values (%Q);", "He's bad");
cout << (const char*)bufSQL << endl;
db.execDML(bufSQL);
bufSQL.format("insert into emp (empname) values (%Q);", NULL);
cout << (const char*)bufSQL << endl;
db.execDML(bufSQL);
////////////////////////////////////////////////////////////////////////////////
// Fetch table at once, and also show how to use CppSQLiteTable::setRow() method
////////////////////////////////////////////////////////////////////////////////
cout << endl << "getTable() test" << endl;
CppSQLite3Table t = db.getTable("select * from emp order by 1;");
for (fld = 0; fld < t.numFields(); fld++)
{
cout << t.fieldName(fld) << "|";
}
cout << endl;
for (int row = 0; row < t.numRows(); row++)
{
t.setRow(row);
for (int fld = 0; fld < t.numFields(); fld++)
{
if (!t.fieldIsNull(fld))
cout << t.fieldValue(fld) << "|";
else
cout << "NULL" << "|";
}
cout << endl;
}
////////////////////////////////////////////////////////////////////////////////
// Test CppSQLiteBinary by storing/retrieving some binary data, checking
// it afterwards to make sure it is the same
////////////////////////////////////////////////////////////////////////////////
cout << endl << "Binary data test" << endl;
db.execDML("create table bindata(desc char(10), data blob);");
unsigned char bin[256];
CppSQLite3Binary blob;
for (i = 0; i < sizeof bin; i++)
{
bin[i] = i;
}
blob.setBinary(bin, sizeof bin);
bufSQL.format("insert into bindata values ('testing', %Q);", blob.getEncoded());
db.execDML(bufSQL);
cout << "Stored binary Length: " << sizeof bin << endl;
q = db.execQuery("select data from bindata where desc = 'testing';");
if (!q.eof())
{
blob.setEncoded((unsigned char*)q.fieldValue("data"));
cout << "Retrieved binary Length: " << blob.getBinaryLength() << endl;
}
q.finalize();
const unsigned char* pbin = blob.getBinary();
for (i = 0; i < sizeof bin; i++)
{
if (pbin[i] != i)
{
cout << "Problem: i: ," << i << " bin[i]: " << pbin[i] << endl;
}
}
////////////////////////////////////////////////////////////////////////////////
// Pre-compiled Statements Demo
////////////////////////////////////////////////////////////////////////////////
cout << endl << "Transaction test, creating " << nRowsToCreate*2;
cout << " rows please wait..." << endl;
db.execDML("drop table emp;");
db.execDML("create table emp(empno int, empname char(20));");
tmStart = time(0);
db.execDML("begin transaction;");
cout << endl << "Creating with bind by number" << endl;
CppSQLite3Statement stmt = db.compileStatement("insert into emp values (?, ?);");
for (i = 0; i < nRowsToCreate; i++)
{
char buf[16];
sprintf(buf, "EmpName%06d", i);
stmt.bind(1, i);
stmt.bind(2, buf);
stmt.execDML();
stmt.reset();
}
cout << endl << "Creating with bind by name" << endl;
CppSQLite3Statement stmt2 = db.compileStatement("insert into emp values (:p1, @p2);");
for (i = 0; i < nRowsToCreate; i++)
{
char buf[16];
sprintf(buf, "EmpName%06d", i);
stmt2.bind(":p1", i);
stmt2.bind("@p2", buf);
stmt2.execDML();
stmt2.reset();
}
db.execDML("commit transaction;");
tmEnd = time(0);
cout << db.execScalar("select count(*) from emp;") << " rows in emp table in ";
cout << tmEnd-tmStart << " seconds (that was even faster!)" << endl;
cout << endl << "End of tests" << endl;
}
catch (CppSQLite3Exception& e)
{
cerr << e.errorCode() << ":" << e.errorMessage() << endl;
}
////////////////////////////////////////////////////////////////////////////////
// Loop until user enters q or Q
////////////////////////////////////////////////////////////////////////////////
char c(' ');
while (c != 'q' && c != 'Q')
{
cout << "Press q then enter to quit: ";
cin >> c;
}
return 0;
}
