int JSQLInvitationCodeDB::removeInvitationCode(const QString& code)
{
QSqlQuery query;
QString dn = QSqlDatabase::database().driverName();
// QSQLITE
if( dn == "QSQLITE" ){
PREPARE( query,
"DELETE FROM "
TableName
" where `id` in ( "
" select `id` from "
TableName
" where `code` = :code "
" order by `id` LIMIT 1 "
" ) ",
0);
}else{
PREPARE( query,
"DELETE FROM "
TableName
" where code = :code "
"LIMIT 1",
0);
}
query.bindValue(":code" , code );
EXEC( query , -1 );
return query.numRowsAffected();
}
/**
* Setup prepared statements.
*
* @param db_conn connection handle to initialize
* @return #GNUNET_OK on success, #GNUNET_SYSERR on failure
*/
static int
postgres_prepare (PGconn *db_conn)
{
PGresult *result;
#define PREPARE(name, sql, ...) \
do { \
result = PQprepare (db_conn, name, sql, __VA_ARGS__); \
if (PGRES_COMMAND_OK != PQresultStatus (result)) \
{ \
GNUNET_break (0); \
PQclear (result); result = NULL; \
return GNUNET_SYSERR; \
} \
PQclear (result); result = NULL; \
} while (0);
PREPARE ("test_insert",
"INSERT INTO test_pq ("
" pub"
",sig"
",abs_time"
",forever"
",hash"
",vsize"
",u16"
",u32"
",u64"
") VALUES "
"($1, $2, $3, $4, $5, $6,"
"$7, $8, $9);",
9, NULL);
PREPARE ("test_select",
"SELECT"
" pub"
",sig"
",abs_time"
",forever"
",hash"
",vsize"
",u16"
",u32"
",u64"
" FROM test_pq"
" ORDER BY abs_time DESC "
" LIMIT 1;",
0, NULL);
return GNUNET_OK;
#undef PREPARE
}
SQLRETURN SRVR_STMT_HDL::Prepare(const SQLValue_def *inSqlString, short inStmtType, short inHoldability,
long inQueryTimeout,bool isISUD)
{
FUNCTION_ENTRY("SRVR_STMT_HDL::Prepare",(""));
DEBUG_OUT(DEBUG_LEVEL_ENTRY,(" inSqlString='%s'",
CLI_SQL_VALUE_STR(inSqlString)));
DEBUG_OUT(DEBUG_LEVEL_ENTRY,(" inStmtType=%s, inHoldability=%d, inQueryTimeout=%ld, isISUD=%d",
CliDebugStatementType(inStmtType),
inHoldability,
inQueryTimeout,isISUD));
SQLRETURN rc;
size_t len;
this->isISUD = isISUD;
if (isReadFromModule) // Already SMD label is found
CLI_DEBUG_RETURN_SQL(SQL_SUCCESS);
// cleanup all memory allocated in the previous operations
cleanupAll();
sqlString.dataCharset = inSqlString->dataCharset;
sqlString.dataType = inSqlString->dataType;
MEMORY_ALLOC_ARRAY(sqlString.dataValue._buffer,unsigned char,inSqlString->dataValue._length+1);
sqlString.dataValue._length = inSqlString->dataValue._length+1;
strncpy((char *)sqlString.dataValue._buffer, (const char *)inSqlString->dataValue._buffer, inSqlString->dataValue._length);
sqlString.dataValue._buffer[inSqlString->dataValue._length] = '\0';
stmtType = inStmtType;
holdability = inHoldability;
CLI_DEBUG_RETURN_SQL(PREPARE(this));
}
void update_day_pass2(char *table_name)
{
int ec, sp_id;
char *ticker_symbol;
printf("PASS2\n");
stmt_q1.sql = Q1;
ec = PREPARE(adb, stmt_q1);
if (ec != SQLITE_OK) {
APPERR("prepare", stmt_q1.sql, ec);
exit(1);
}
do {
ec = STEP(stmt_q1);
if (ec == SQLITE_ROW) {
sp_id = sqlite3_column_int(stmt_q1.stmtp, 0);
ticker_symbol = (char *)sqlite3_column_text(stmt_q1.stmtp, 1);
get_stock_prices(sp_id, table_name, ticker_symbol);
}
} while (ec == SQLITE_ROW);
if (ec != SQLITE_DONE) {
APPERR("step", stmt_q1.sql, ec);
exit(1);
}
}
void detach(char *dbname)
{
int ec;
stmt_detach.sql = DETACH;
ec = PREPARE(adb, stmt_detach);
if (ec != SQLITE_OK) {
APPERR("prepare", stmt_detach.sql, ec);
exit(1);
}
ec = BINDSTR(stmt_detach, 1, dbname);
if (ec != SQLITE_OK) {
APPERR("bind", stmt_detach.sql, ec);
exit(1);
}
ec = STEP(stmt_detach);
if (ec = SQLITE_OK) {
APPERR("step", stmt_detach.sql, ec);
exit(1);
}
ec = FINALIZE(stmt_detach);
if (ec = SQLITE_OK) {
APPERR("finalize", stmt_detach.sql, ec);
exit(1);
}
}
bool JSQLGameInfoDB::isGameIdExist(JID id)
{
QSqlQuery query;
PREPARE( query ,
"select game_id from gameinfo "
"where game_id= :gameID" ,
false );
query.bindValue(":gameID", id);
EXEC( query , false );
if( query.driver()->hasFeature(QSqlDriver::QuerySize) ){
if(query.size() > 0){
return true;
}else{
return false;
}
}else{
if(query.next()){
return true;
}else{
return false;
}
}
}
JCode JSQLGameInfoDB::updateGameInfo(const JGameInfo &gameInfo) {
QSqlQuery query ;
PREPARE( query ,
"UPDATE gameinfo SET "
"game_name = :gameName, "
"version = :version, "
"author_id = :authorID, "
"runner_id = :runnerID, "
"intro = :introdution, "
"server_id = :serverID, "
"download_url = :downUrl "
"WHERE game_id = :gameID" ,
EPrepareFailed );
query.bindValue(":gameName", gameInfo.getName());
query.bindValue(":version", gameInfo.getVersion().getData());
query.bindValue(":authorID", gameInfo.getAuthor());
query.bindValue(":runnerID", gameInfo.getRunner());
query.bindValue(":introdution", gameInfo.getIntroduction());
query.bindValue(":serverID", gameInfo.getServerId());
query.bindValue(":downUrl", gameInfo.getDownloadUrl());
query.bindValue(":gameID", gameInfo.getGameId());
EXEC( query , EExecFailed );
return 0;
}
void update_day_pass3(char *table_name)
{
int ec;
printf("PASS3: %s\n", table_name);
attach("analytic.db", "analytic");
attach("timeSeries.db", "timeSeries");
stmt_q4.sql = sql_buffer;
sprintf(sql_buffer, Q4, "analytic", "timeSeries", table_name);
ec = PREPARE(adb, stmt_q4);
if (ec != SQLITE_OK) {
APPERR("prepare", stmt_q4.sql, ec);
exit(1);
}
ec = STEP(stmt_q4);
if (ec = SQLITE_OK) {
APPERR("step", stmt_q4.sql, ec);
exit(1);
}
ec = FINALIZE(begin);
if (ec = SQLITE_OK) {
APPERR("finalize", stmt_q4.sql, ec);
exit(1);
}
detach("analytic");
detach("timeSeries");
}
JGameInfo JSQLGameInfoDB::getGameInfoById(JID ID) {
QSqlQuery query ;
PREPARE( query ,
"SELECT game_name, "
"version, "
"author_id, "
"runner_id, "
"intro, "
"server_id, "
"download_url "
"FROM gameinfo "
"WHERE game_id = :gameID",
JGameInfo() );
query.bindValue(":gameID", ID);
EXEC(query , JGameInfo() );
if (query.next())
return JGameInfo(ID,
query.value(0).toString(),
JVersion((quint32)query.value(1).toInt()),
query.value(2).toInt(),
query.value(3).toInt(),
query.value(4).toString(),
query.value(5).toInt(),
query.value(6).toString()
);
else
return JGameInfo();
}
bool JSQLUserInfoDB::isUserIdExist(JID id){
QSqlQuery query;
PREPARE( query ,
"select * from userinfo "
"where user_id = :userId" ,
false );
query.bindValue(":userId", id);
EXEC( query , false );
if( query.driver()->hasFeature(QSqlDriver::QuerySize) ){
if(query.size() > 0){
return true;
}else{
return false;
}
}else{
if(query.next()){
return true;
}else{
return false;
}
}
}
int main(int argc , char * argv[])
{
int status;
char * str_err_msg= NULL;
db_report = stdout;
printf("sqlite version : %s\n%s\n",sqlite3_libversion(),sqlite3_sourceid());
//_hdb db;
connect_db("demo.db");
// /**
PREPARE("create table usrpwd(usr QString, pwd QString)");
status = sqlite3_exec(db,
QUERY,
0,
0,
&str_err_msg);
printf("c->status: %d ~ %s \n",status,str_err_msg);
/* */
// /**
PREPARE("insert into usrpwd values('hkkk','khhh')");
status = sqlite3_exec(db,
QUERY,
0,
0,
&str_err_msg);
printf("c->status: %d ~ %s \n",status,str_err_msg);
//**/
PREPARE("select * from usrpwd ");
status = sqlite3_exec(db,
QUERY,
result_handle, // int (*callback)(void*,int,char**,char**),
0,
&str_err_msg);
printf("c->status: %d ~ %s \n",status,str_err_msg);
status = sqlite3_close(db);
if(status != SQLITE_OK )
printf("error:%d\n",sqlite3_errcode(db));
printf("%s",sqlite3_mprintf("%q","it's example"));
return 0;
}
int insert_stock_price(int sp_id, double sp_price, char *sp_date)
{
int ec;
stmt_q3.sql = Q3;
ec = PREPARE(adb, stmt_q3);
if (ec != SQLITE_OK) {
APPERR("prepare", stmt_q3.sql, ec);
exit(1);
}
ec = BINDINT(stmt_q3, 1, sp_id);
if (ec != SQLITE_OK) {
APPERR("bind sp_id", stmt_q3.sql, ec);
exit(1);
}
ec = BINDDBL(stmt_q3, 2, sp_price);
if (ec != SQLITE_OK) {
APPERR("bind sp_price", stmt_q3.sql, ec);
exit(1);
}
ec = BINDSTR(stmt_q3, 3, sp_date);
if (ec != SQLITE_OK) {
APPERR("bind sp_date", stmt_q3.sql, ec);
exit(1);
}
ec = STEP(stmt_q3);
if (ec == SQLITE_DONE) {
printf("insert of sp_id %d sp_price $%.2f sp_date %s succeeded\n",
sp_id, sp_price, sp_date);
ec = FINALIZE(stmt_q3);
if (ec != SQLITE_OK) {
APPERR("finalize", stmt_q3.sql, ec);
exit(1);
}
return 1;
} else if (ec == SQLITE_CONSTRAINT) {
return 0;
} else {
APPERR("STEP", stmt_q3.sql, ec);
exit(1);
}
}
/*!
\brief 插入 root 用户
参数 \a password 是 root 用户的密码。\n
成功返回 true ， 失败返回 false 。
*/
bool JRootUserInserter::insert(const QString& password) {
QSqlQuery query ;
PREPARE( query ,
"INSERT INTO logininfo "
"(user_name, passwd, roles) "
"VALUES(:loginName, :passwd, :roles)" ,
EPrepareFailed );
query.bindValue(":loginName", "root");
query.bindValue(":passwd", password);
query.bindValue(":roles", 31);
EXEC( query , EExecFailed );
return true;
}
JGameList JSQLGameInfoDB::getGameList() {
QSqlQuery query ;
PREPARE( query ,
"SELECT game_id, game_name FROM gameinfo",
JGameList() );
EXEC( query , JGameList() );
JGameList gameList;
while (query.next()) {
gameList.insert(query.value(0).toInt(),
query.value(1).toString()
);
}
return gameList;
}
JCode JSQLUserInfoDB::insertUserInfo(const JUserInfo& userInfo){
QSqlQuery query;
PREPARE( query ,
" INSERT INTO userinfo "
" (user_id,nickname,org) "
" values(:userId,:nickname,:org) ",
EPrepareFailed );
query.bindValue(":nickname", userInfo.getNickname());
query.bindValue(":org", userInfo.getOrganization());
query.bindValue(":userId", userInfo.getUserId());
EXEC( query , EExecFailed );
return 0 ;
}
JCode JSQLInvitationCodeDB::addInvitationCode(const QString& code)
{
QSqlQuery query;
PREPARE( query ,
"INSERT INTO "
TableName
" (code) "
" values ( :code )",
EPrepareFailed
);
query.bindValue(":code" , code );
EXEC( query , EExecFailed);
return 0;
}
JCode JSQLUserInfoDB::updateUserInfo(const JUserInfo &userInfo) {
QSqlQuery query ;
PREPARE( query ,
"UPDATE userinfo SET "
"nickname = :nickname, "
"org = :org "
"WHERE user_id = :userID",
EPrepareFailed
);
query.bindValue(":nickname", userInfo.getNickname());
query.bindValue(":org", userInfo.getOrganization());
query.bindValue(":userID", userInfo.getUserId());
EXEC( query , EExecFailed );
return 0 ;
}
JUserInfo JSQLUserInfoDB::getUserInfoById(JID userID) {
QSqlQuery query ;
PREPARE( query ,
"SELECT nickname, org FROM userinfo "
"WHERE user_id = :userID",
JUserInfo() );
query.bindValue(":userID", userID);
EXEC( query , JUserInfo() );
if (query.next())
return JUserInfo(userID,
query.value(0).toString(),
query.value(1).toString()
);
else
return JUserInfo();
}
void update_day_pass1(char *table_name)
{
char *ticker_symbol;
int ec, symbols = 0, inserts = 0;
stmt_distinctticker.sql = sql_buffer;
sprintf(sql_buffer, SQL_DISTINCTTICKER, table_name);
ec = PREPARE(tdb, stmt_distinctticker);
if (ec != SQLITE_OK) {
APPERR("prepare", stmt_distinctticker.sql, ec);
exit(1);
}
do {
ec = STEP(stmt_distinctticker);
if (ec == SQLITE_ROW) {
ticker_symbol = (char *)sqlite3_column_text(stmt_distinctticker.stmtp, 0);
symbols++;
if (insertTicker(ticker_symbol))
inserts++;
}
} while (ec == SQLITE_ROW);
printf("PASS1 %s %d %d\n", table_name, symbols, inserts);
if (ec != SQLITE_DONE) {
APPERR("step", stmt_distinctticker.sql, ec);
exit(1);
}
ec = FINALIZE(stmt_distinctticker);
if (ec != SQLITE_OK) {
APPERR("finalize", stmt_distinctticker.sql, ec);
exit(1);
}
}
JCode JSQLGameInfoDB::insertGameInfo(const JGameInfo& gameInfo)
{
QSqlQuery query;
PREPARE( query ,
" INSERT INTO gameinfo "
" (game_id,game_name,version,author_id,runner_id,intro,server_id,download_url) "
" values(:gameID,:gameName,:version,:authorID,:runnerID,:introduction,:serverID,:downUrl) ",
EPrepareFailed );
query.bindValue(":gameName", gameInfo.getName());
query.bindValue(":version", gameInfo.getVersion().getData());
query.bindValue(":authorID", gameInfo.getAuthor());
query.bindValue(":runnerID", gameInfo.getRunner());
query.bindValue(":introduction", gameInfo.getIntroduction());
query.bindValue(":serverID", gameInfo.getServerId());
query.bindValue(":downUrl", gameInfo.getDownloadUrl());
query.bindValue(":gameID", gameInfo.getGameId());
EXEC( query , EExecFailed );
return 0 ;
}
int insertTicker(char *ticker_symbol)
{
int ec;
if (!strcmp(ticker_symbol, "UnderlyingSymbol"))
return 0;
stmt_insertticker.sql = SQL_INSERTTICKER;
ec = PREPARE(adb, stmt_insertticker);
if (ec != SQLITE_OK) {
APPERR("prepare", stmt_insertticker.sql, ec);
exit(1);
}
ec = BINDSTR(stmt_insertticker, 1, ticker_symbol);
if (ec != SQLITE_OK) {
APPERR("bind", stmt_insertticker.sql, ec);
exit(1);
}
ec = STEP(stmt_insertticker);
if (ec == SQLITE_DONE) {
ec = FINALIZE(stmt_insertticker);
printf("insert of symbol %s succeeded\n", ticker_symbol);
if (ec != SQLITE_OK) {
APPERR("finalize", stmt_insertticker.sql, ec);
exit(1);
}
return 1;
} else if (ec == SQLITE_CONSTRAINT) {
return 0;
} else {
APPERR("STEP", stmt_insertticker.sql, ec);
exit(1);
}
}
void end_transaction(void)
{
int ec;
end.sql = END;
ec = PREPARE(adb, end);
if (ec != SQLITE_OK) {
APPERR("prepare", end.sql, ec);
exit(1);
}
ec = STEP(end);
if (ec = SQLITE_OK) {
APPERR("step", end.sql, ec);
exit(1);
}
ec = FINALIZE(end);
if (ec = SQLITE_OK) {
APPERR("finalize", end.sql, ec);
exit(1);
}
}
void begin_transaction(void)
{
int ec;
begin.sql = BEGIN;
ec = PREPARE(adb, begin);
if (ec != SQLITE_OK) {
APPERR("prepare", begin.sql, ec);
exit(1);
}
ec = STEP(begin);
if (ec = SQLITE_OK) {
APPERR("step", begin.sql, ec);
exit(1);
}
ec = FINALIZE(begin);
if (ec = SQLITE_OK) {
APPERR("finalize", begin.sql, ec);
exit(1);
}
}
void get_stock_prices(int sp_id, char *table_name, char *symbol)
{
int ec;
double stock_price;
char *data_date;
stmt_q2.sql = sql_buffer;
sprintf(sql_buffer, Q2, table_name, symbol);
ec = PREPARE(tdb, stmt_q2);
if (ec != SQLITE_OK) {
APPERR("get_stock_prices:prepare", stmt_q2.sql, ec);
exit(1);
}
do {
ec = STEP(stmt_q2);
if (ec == SQLITE_ROW) {
stock_price = sqlite3_column_double(stmt_q2.stmtp, 0);
data_date = (char *)sqlite3_column_text(stmt_q2.stmtp, 1);
insert_stock_price(sp_id, stock_price, data_date);
}
} while (ec == SQLITE_ROW);
if (ec != SQLITE_DONE) {
APPERR("step", stmt_q2.sql, ec);
exit(1);
}
ec = FINALIZE(stmt_q2);
if (ec != SQLITE_OK) {
APPERR("finalize", stmt_q2.sql, ec);
exit(1);
}
}
/************* ANTIC mode 00: *********************************
* generate 1-8 empty scanlines
***************************************************************/
void antic_mode_0_xx(VIDEO *video)
{
PREPARE();
memset(dst, PBK, HWIDTH*4);
POST();
}
//The following function is converted from the macro with the same name,since it may lead compiling error under
//Microsoft Visual Studio.
static void _findHashEntry(HashTable* table,Object* ptr,Monitor* ptr2,int add_if_absent,int scavenge,int locked)
{
int hash;
int i;
Thread *self;
hash = HASH(ptr);
if(locked) {
self = threadSelf();
lockHashTable0(table, self);
}
i = hash & (table->hash_size - 1);
for(;;) {
ptr2 = table->hash_table[i].data;
if((ptr2 == NULL) || (COMPARE(ptr, ptr2, hash, table->hash_table[i].hash)))
break;
i = (i+1) & (table->hash_size - 1);
}
if(ptr2) {
ptr2 = FOUND(ptr, ptr2);
} else
if(add_if_absent) {
table->hash_table[i].hash = hash;
ptr2 = table->hash_table[i].data = PREPARE(ptr);
if(ptr2) {
table->hash_count++;
if((table->hash_count * 4) > (table->hash_size * 3)) {
int new_size;
if(scavenge) {
HashEntry *entry = table->hash_table;
int cnt = table->hash_count;
for(; cnt; entry++) {
void *data = entry->data;
if(data) {
if(SCAVENGE(data)) {
entry->data = NULL;
table->hash_count--;
}
cnt--;
}
}
if((table->hash_count * 3) > (table->hash_size * 2))
new_size = table->hash_size*2;
else
new_size = table->hash_size;
} else
new_size = table->hash_size*2;
resizeHash(table, new_size);
}
}
}
if(locked)
unlockHashTable0(table, self);
}
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);
}
}
