/**
* @brief Release the psql database.
*
* This function closes the connection to the database and frees all
* of the allocated memory associated with the database.
*
* @param db the database that contains the psql database to release.
*/
void
psql_release(Database* db)
{
PsqlDB* self = (PsqlDB*)db->handle;
end_transaction (self);
PQfinish(self->conn);
// TODO: Release table specific data
xfree(self);
db->handle = NULL;
}
static void transact_command(ostream &out, istream &in)
{
int choice ;
int transaction ;
int result ;
do {
choice = display_menu(out,in,true,3,
"Transactions",
"\t1. Start a transaction\n"
"\t2. End a transaction\n"
"\t3. Abort a transaction\n"
) ;
switch (choice)
{
case 0:
// do nothing
break ;
case 1:
transaction = start_transaction() ;
if (transaction == -1)
out << "Unable to start transaction" << endl ;
else
out << "Started transaction number " << transaction << endl ;
break ;
case 2:
transaction = get_number(out,in,"Transaction number:",-1,
SHRT_MAX) ;
result = end_transaction(transaction) ;
if (result == -1)
out << "Failed while ending transaction, error code = "
<< Fr_errno << endl ;
else
out << "Transaction ended successfully" << endl ;
break ;
case 3:
transaction = get_number(out,in,"Transaction number:",-1,
SHRT_MAX) ;
result = abort_transaction(transaction) ;
if (result == -1)
{
out << "Failed while aborting transaction, error code = "
<< Fr_errno << endl ;
}
else
out << "Transaction successfully rolled back" << endl ;
break ;
default:
FrMissedCase("transact_command") ;
break ;
}
} while (choice != 0) ;
return ;
}
int
exec_sql_list_in_transaction ( sqlite3 *db, const char **sqls )
{
VERBOSE_PRINTF("exec_sql_list_in_transaction()\n");
int ret = 0;
ret = start_transaction(db);
if (ret) {
return ret;
}
ret = exec_sql_list(db, sqls);
if (ret) {
//return ret;
}
int ret2 = end_transaction(db, ret);
if (ret) {
return ret;
}
return ret2;
}
void update(int pass)
{
char *tsmd_name;
int tsmd_rowcnt, tsmd_processed, tsmd_rowid, ec;
stmt_timeseriesmetadata.sql = SQL_TIMESERIESMETADATA;
ec = PREPARE(adb, stmt_timeseriesmetadata);
if (ec != SQLITE_OK) {
APPERR("prepare", stmt_timeseriesmetadata.sql, ec);
exit(1);
}
do {
ec = STEP(stmt_timeseriesmetadata);
if (ec == SQLITE_ROW) {
/*
* 0 1 2 3
* rowid, tsmd_name, tsmd_rowcnt, tsmd_processed
*/
tsmd_rowid = sqlite3_column_int(stmt_timeseriesmetadata.stmtp, 0);
tsmd_name = (char *)sqlite3_column_text(stmt_timeseriesmetadata.stmtp, 1);
tsmd_rowcnt = sqlite3_column_int(stmt_timeseriesmetadata.stmtp, 2);
tsmd_processed = sqlite3_column_int(stmt_timeseriesmetadata.stmtp, 3);
printf("Processing rowid %d name %s rowcnt %d processed %d\n",
tsmd_rowid, tsmd_name, tsmd_rowcnt, tsmd_processed);
switch(pass) {
case 1:
update_day_pass1(tsmd_name);
break;
case 2:
begin_transaction();
update_day_pass2(tsmd_name);
end_transaction();
break;
case 3:
update_day_pass3(tsmd_name);
break;
default:
printf("invalude pass number %d\n", pass);
exit(1);
}
}
} while (ec == SQLITE_ROW);
if (ec != SQLITE_DONE) {
APPERR("step", stmt_timeseriesmetadata.sql, ec);
exit(1);
}
ec = FINALIZE(stmt_timeseriesmetadata);
if (ec != SQLITE_OK) {
APPERR("finalize", stmt_timeseriesmetadata.sql, ec);
exit(1);
}
}
int main ( int argc, char **argv )
{
int ret = check_args(argc, argv);
if (ret) {
printf("invalid argument error!\n");
return -1;
}
if (new_db) {
unlink(db_name);
}
soft_heap_limit = sqlite3_soft_heap_limit64(soft_heap_limit);
if ( soft_heap_limit < 0 ) {
ret = soft_heap_limit;
printf("sqlite3_soft_heap_limit() error: %x", ret);
} else {
printf("sqlite3_soft_heap_limit64(): prev: %ld (byte) is set to: %lld (byte).\n", soft_heap_limit, sqlite3_soft_heap_limit64(-1));
}
sqlite3 *db = NULL;
ret = sqlite3_open_v2 (db_name, &db,
SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE,
NULL);
if ( ret != SQLITE_OK )
{
printf ( "Can't open database: %s\n", sqlite3_errmsg ( db ) );
return -1;
}
if (new_db) {
// create table, transaction is useful for speed optimization
printf("== execute sql_create_table ==\n");
exec_sql_list_in_transaction(db, sql_create_table);
} else {
printf("== use existing db: %s ==\n", db_name);
}
int sql_count = 0;
int trans_count = 0;
uint64_t total_time = 0;
int64_t max_highwater = 0;
uint64_t total_highwater = 0;
do {
uint64_t start_time = sceKernelGetProcessTime();
ret = start_transaction(db);
if (ret) {
printf("start_transaction() err: %x\n", ret);
break;
}
int trans_sql_count = 0;
trans_count++;
do {
fill_sql(sql_count);
ret = exec_sql(db, sql_buf);
if (ret) {
break;
}
sql_count++;
trans_sql_count++;
} while (sql_count < total_sql_num
&& trans_sql_count < max_sql_in_trans);
ret = end_transaction(db, ret);
if (ret) {
printf("end_transaction() err: %x\n", ret);
break;
}
uint64_t end_time = sceKernelGetProcessTime();
total_time += end_time - start_time;
printf("[%d] th transaction is end. operated sql num: %d, time: %lu (us)", trans_count, sql_count, end_time - start_time);
printf("\t: sqlite3_memory_used(): %lld", sqlite3_memory_used());
int64_t highwater = sqlite3_memory_highwater(true);
if (max_highwater < highwater) {
max_highwater = highwater;
}
total_highwater += highwater;
printf("\t: sqlite3_memory_highwater(true): %ld\n", highwater);
} while (sql_count < total_sql_num);
printf("operated sql count: %d, transaction count: %d\n", sql_count, trans_count);
printf("total elapsed time: %lu (us)\n", total_time);
if (trans_count != 0) {
printf("maximum memory highwater: %ld, average high water / transaction: %lu\n", max_highwater, total_highwater / trans_count);
}
/*
// select
const char *sql_select[] =
{
"SELECT * FROM tbl1;", // select all
"SELECT id, c1_int FROM tbl1 WHERE 1 < id;", // select 1 < id
NULL, // terminater
};
printf("\n== execute sql_select ==\n");
exec_sql(db, sql_select);
*/
sqlite3_close ( db );
return 0;
}
persistence_sqlite::~persistence_sqlite()
{
end_transaction();
}
void
persistence_sqlite::timeout(const boost::system::error_code& err)
{
if (err != boost::asio::error::operation_aborted)
end_transaction();
}