uint32_t v_count_age_owners(uint32_t ageInfoId)
{
PostgresStrings<2> parms;
parms.set(0, ageInfoId);
parms.set(1, DS::Vault::e_AgeOwnersFolder);
PGresult* result = PQexecParams(s_postgres,
"SELECT idx FROM vault.find_folder($1, $2);",
2, 0, parms.m_values, 0, 0, 0);
uint32_t owners = 0;
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
PQclear(result);
return owners;
}
parms.set(0, PQgetvalue(result, 0, 0));
PQclear(result);
result = PQexecParams(s_postgres,
"SELECT COUNT(*) FROM vault.\"NodeRefs\" WHERE \"ParentIdx\"=$1",
1, 0, parms.m_values, 0, 0, 0);
if (PQresultStatus(result) == PGRES_TUPLES_OK) {
owners = strtoul(PQgetvalue(result, 0, 0), 0, 10);
} else {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
}
PQclear(result);
return owners;
}
void dm_save_sdl_state(GameHost_Private* host, const DS::String& descriptor,
const MOUL::Uoid& object, const SDL::State& state)
{
check_postgres(host);
DS::Blob sdlBlob = state.toBlob();
PostgresStrings<4> parms;
host->m_buffer.truncate();
object.write(&host->m_buffer);
parms.set(0, host->m_serverIdx);
parms.set(1, descriptor);
parms.set(2, DS::Base64Encode(host->m_buffer.buffer(), host->m_buffer.size()));
parms.set(3, DS::Base64Encode(sdlBlob.buffer(), sdlBlob.size()));
PGresult* result = PQexecParams(host->m_postgres,
"SELECT idx FROM game.\"AgeStates\""
" WHERE \"ServerIdx\"=$1 AND \"Descriptor\"=$2 AND \"ObjectKey\"=$3",
3, 0, parms.m_values, 0, 0, 0);
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(host->m_postgres));
PQclear(result);
return;
}
if (PQntuples(result) == 0) {
PQclear(result);
result = PQexecParams(host->m_postgres,
"INSERT INTO game.\"AgeStates\""
" (\"ServerIdx\", \"Descriptor\", \"ObjectKey\", \"SdlBlob\")"
" VALUES ($1, $2, $3, $4)",
4, 0, parms.m_values, 0, 0, 0);
if (PQresultStatus(result) != PGRES_COMMAND_OK) {
fprintf(stderr, "%s:%d:\n Postgres INSERT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(host->m_postgres));
PQclear(result);
return;
}
PQclear(result);
} else {
DS_DASSERT(PQntuples(result) == 1);
parms.set(0, DS::String(PQgetvalue(result, 0, 0)));
parms.set(1, parms.m_strings[3]); // SDL blob
PQclear(result);
result = PQexecParams(host->m_postgres,
"UPDATE game.\"AgeStates\""
" SET \"SdlBlob\"=$2 WHERE idx=$1",
2, 0, parms.m_values, 0, 0, 0);
if (PQresultStatus(result) != PGRES_COMMAND_OK) {
fprintf(stderr, "%s:%d:\n Postgres UPDATE error: %s\n",
__FILE__, __LINE__, PQerrorMessage(host->m_postgres));
PQclear(result);
return;
}
PQclear(result);
}
}
static uint32_t find_public_age_1(const DS::String& filename)
{
PGresult* result;
{
PostgresStrings<1> parm;
parm.set(0, filename);
result = PQexecParams(s_postgres,
"SELECT \"AgeUuid\" FROM game.\"PublicAges\""
" WHERE \"AgeFilename\"=$1 AND \"SeqNumber\"=0",
1, 0, parm.m_values, 0, 0, 0);
}
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
PQclear(result);
return 0;
}
DS_DASSERT(PQntuples(result) == 1);
DS::Uuid ageId(PQgetvalue(result, 0, 0));
PQclear(result);
{
PostgresStrings<2> parms;
parms.set(0, DS::Vault::e_NodeAgeInfo);
parms.set(1, ageId.toString());
result = PQexecParams(s_postgres,
"SELECT idx FROM vault.\"Nodes\""
" WHERE \"NodeType\"=$1 AND \"Uuid_1\"=$2",
2, 0, parms.m_values, 0, 0, 0);
}
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
PQclear(result);
return 0;
}
if (PQntuples(result) == 0) {
// Public age not found
PQclear(result);
return 0;
}
DS_DASSERT(PQntuples(result) == 1);
uint32_t ageInfoNode = strtoul(PQgetvalue(result, 0, 0), 0, 10);
PQclear(result);
return ageInfoNode;
}
CAMLprim value PQexecParams_stub(
value v_conn, value v_query, value v_params, value v_binary_params)
{
CAMLparam1(v_conn);
PGconn *conn = get_conn(v_conn);
np_callback *np_cb = get_conn_cb(v_conn);
PGresult *res;
size_t len = caml_string_length(v_query) + 1;
char *query = caml_stat_alloc(len);
size_t nparams = Wosize_val(v_params);
const char * const *params = copy_params(v_params, nparams);
int *formats, *lengths;
copy_binary_params(v_params, v_binary_params, nparams, &formats, &lengths);
memcpy(query, String_val(v_query), len);
caml_enter_blocking_section();
res =
(nparams == 0)
? PQexec(conn, query)
: PQexecParams(conn, query, nparams, NULL, params, lengths, formats, 0);
free_binary_params(formats, lengths);
free_params(params, nparams);
free(query);
caml_leave_blocking_section();
CAMLreturn(alloc_result(res, np_cb));
}
boost::shared_ptr<ResultSet> executeQuery(const std::string& sql,int type=0) const
{
PGresult *result=0;
if (type==1)
{
result=PQexecParams(conn_,sql.c_str(),0,0,0,0,0,1);
}
else
{
result=PQexec(conn_,sql.c_str());
}
if(!result || PQresultStatus(result) != PGRES_TUPLES_OK)
{
std::ostringstream s("Postgis Plugin: PSQL error");
if (conn_ )
{
std::string msg = PQerrorMessage( conn_ );
if ( ! msg.empty() )
{
s << ":\n" << msg.substr( 0, msg.size() - 1 );
}
s << "\nFull sql was: '" << sql << "'\n";
}
if (result)
PQclear(result);
throw mapnik::datasource_exception( s.str() );
}
return boost::make_shared<ResultSet>(result);
}
int mode_balance(PGconn* db, int argc, char** argv){
static const char* QUERY_SNACK_ACCOUNT_BALANCE="SELECT users.user_id, user_name, user_full_name, balance FROM garfield.users JOIN garfield.user_balances ON users.user_id = user_balances.user_id WHERE user_name = $1";
char* user_name=PQuser(db);
PGresult* result=PQexecParams(db, QUERY_SNACK_ACCOUNT_BALANCE, 1, NULL, (const char**)&user_name, NULL, NULL, 0);
if(!result){
printf("Failed to execute query\n");
return -1;
}
if(PQresultStatus(result)!=PGRES_TUPLES_OK){
printf("Query returned failure\n");
PQclear(result);
return -1;
}
if(PQntuples(result)!=1){
printf("Query returned invalid number of rows\n");
PQclear(result);
return -1;
}
//print balance
printf("Snack account balance for user %s (%s) is %s\n",PQgetvalue(result,0,1),PQgetvalue(result,0,2),PQgetvalue(result,0,3));
PQclear(result);
return 0;
}
virtual long long sequence_last(std::string const &sequence)
{
PGresult *res = 0;
long long rowid = 0;
try {
char const * const param_ptr = sequence.c_str();
res = PQexecParams( conn_,
"SELECT currval($1)",
1, // 1 param
0, // types
¶m_ptr, // param values
0, // lengths
0, // formats
0 // string format
);
if(PQresultStatus(res) != PGRES_TUPLES_OK) {
throw pqerror(res,"failed to fetch last sequence id");
}
char const *val = PQgetvalue(res,0,0);
if(!val || *val==0)
throw pqerror("Failed to get value for sequecne id");
fmt_.str(val);
fmt_.clear();
fmt_ >> rowid;
fmt_.str(std::string());
fmt_.clear();
}
catch(...) {
if(res) PQclear(res);
throw;
}
PQclear(res);
return rowid;
}
static int _DB_mark_config_del(Database *db, unsigned int id, time_t del_at){
PGconn *conn;
PGresult *res;
const char *paramValues[2];
const int paramLengths[2] = {sizeof(int), sizeof(int)};
const int paramFormats[2] = {BINARY_FRMT, BINARY_FRMT};
uint32_t config_id, time_del_at;
if(_DB_connect(db, &conn) != 0) return 1;
if(_DB_begin(db, conn, &res) != 0){ _DB_close_res(db, conn, res); return 1;}
paramValues[0] = chk_time_to_null(del_at, &time_del_at);
paramValues[1] = order_int(id, &config_id);
res = PQexecParams(conn,
"UPDATE "CONFIGS_TABNAME" SET deleted_at = "TO_TIMESTAMP("$3")
" WHERE id = $2::int;",
2, /* count of params */
NULL, /* let the backend deduce param type */
paramValues,
paramLengths,
paramFormats,
TEXT_FRMT); /* text results */
if(PQresultStatus(res) != PGRES_COMMAND_OK){
_DB_close_res(db, conn, res);
return 1;
}
PQclear(res);
if(_DB_commit(db, conn, &res) != 0){ _DB_close_res(db, conn, res); return 1;}
PQfinish(conn);
return 0;
}
static void fillNext(GtkTreeSelection* selection, GtkListStore* model) {
uint32_t i;
gtk_list_store_clear(model);
static char offset[0x100];
ssize_t len = snprintf(offset,0x100,"%d",page*pagesize);
const char* values[] = { offset, derpagesize };
const int lengths[] = { len, sizeof(derpagesize) };
const int fmt[] = { 0, 0 };
PGresult* result = PQexecParams(PQconn,"getpage",1,NULL,values,lengths,fmt,0);
GtkTreeIter iter = {};
gtk_tree_model_get_iter_first(GTK_TREE_MODEL(model),&iter);
for(i=0;i<PQntuples(result);++i) {
gtk_list_store_append(model,&iter);
gtk_list_store_set(model,&iter,0,strtof(PQgetvalue(result,i,0),NULL),
1,PQgetvalue(result,i,1),
2,PQgetvalue(result,i,2),
-1);
}
PQclear(result);
++page;
}
bool v_fetch_tree(uint32_t nodeId, std::vector<DS::Vault::NodeRef>& refs)
{
PostgresStrings<1> parm;
parm.set(0, nodeId);
PGresult* result = PQexecParams(s_postgres,
"SELECT \"ParentIdx\", \"ChildIdx\", \"OwnerIdx\", \"Seen\""
" FROM vault.fetch_tree($1);",
1, 0, parm.m_values, 0, 0, 0);
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
PQclear(result);
return false;
}
refs.resize(PQntuples(result));
for (size_t i=0; i<refs.size(); ++i) {
refs[i].m_parent = strtoul(PQgetvalue(result, i, 0), 0, 10);
refs[i].m_child = strtoul(PQgetvalue(result, i, 1), 0, 10);
refs[i].m_owner = strtoul(PQgetvalue(result, i, 2), 0, 10);
refs[i].m_seen = strtoul(PQgetvalue(result, i, 3), 0, 10);
}
PQclear(result);
return true;
}
bool v_has_node(uint32_t parentId, uint32_t childId)
{
if (parentId == 0)
return false;
if (parentId == childId)
return true;
PostgresStrings<2> parms;
parms.set(0, parentId);
parms.set(1, childId);
check_postgres();
PGresult* result = PQexecParams(s_postgres,
"SELECT vault.has_node($1, $2)",
2, 0, parms.m_values, 0, 0, 0);
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
PQclear(result);
return false;
}
DS_DASSERT(PQntuples(result) == 1);
bool retval = (*PQgetvalue(result, 0, 0) == 't');
PQclear(result);
return retval != 0;
}
static char *pdo_pgsql_last_insert_id(pdo_dbh_t *dbh, const char *name, unsigned int *len)
{
pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh->driver_data;
char *id = NULL;
if (name == NULL) {
if (H->pgoid == InvalidOid) {
return NULL;
}
*len = spprintf(&id, 0, ZEND_LONG_FMT, (zend_long) H->pgoid);
} else {
PGresult *res;
ExecStatusType status;
const char *q[1];
q[0] = name;
res = PQexecParams(H->server, "SELECT CURRVAL($1)", 1, NULL, q, NULL, NULL, 0);
status = PQresultStatus(res);
if (res && (status == PGRES_TUPLES_OK)) {
id = estrdup((char *)PQgetvalue(res, 0, 0));
*len = PQgetlength(res, 0, 0);
} else {
pdo_pgsql_error(dbh, status, pdo_pgsql_sqlstate(res));
}
if (res) {
PQclear(res);
}
}
return id;
}
void clearSession()
{
const char *conninfo;
PGconn *conn;
PGresult *res;
conninfo = "dbname=echuraev";
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK)
{
printf ("Connection to database failed: %s\n", PQerrorMessage(conn));
exit_nicely(conn);
}
res = PQexecParams(conn, "DELETE FROM sessions", 0, NULL, NULL, NULL, NULL, 0);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
printf ("Cannot remove old sessions: %s\n", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
PQfinish(conn);
}
/**
* Execute a database command that has no result.
*
* Error messages from this function are sent to the message
* handler (see msngr_init_log() and msngr_init_mail()).
*
* @param dbconn - pointer to the database connection
* @param command - command string
* @param nparams - number of $1, $2, ... parameters in the command
* @param params - parameters to substitute in the command
*
* @return database status:
* - DB_NO_ERROR
* - DB_ERROR
*
* @see DBStatus
*/
DBStatus pgsql_exec(
DBConn *dbconn,
const char *command,
int nparams,
const char **params)
{
PGconn *pgconn = (PGconn *)dbconn->dbh;
PGresult *pgres;
DBStatus status;
if (nparams > 0) {
pgres = PQexecParams(
pgconn, command, nparams, NULL, params, NULL, NULL, 0);
}
else {
pgres = PQexec(pgconn, command);
}
if (!pgres || PQresultStatus(pgres) != PGRES_COMMAND_OK) {
PGSQL_ERROR(dbconn, pgconn, pgres,
"FAILED: %s\n", command);
status = DB_ERROR;
}
else {
status = DB_NO_ERROR;
}
if (pgres) {
PQclear(pgres);
}
return(status);
}
static char *pdo_pgsql_last_insert_id(pdo_dbh_t *dbh, const char *name, size_t *len)
{
pdo_pgsql_db_handle *H = (pdo_pgsql_db_handle *)dbh->driver_data;
char *id = NULL;
PGresult *res;
ExecStatusType status;
if (name == NULL) {
res = PQexec(H->server, "SELECT LASTVAL()");
} else {
const char *q[1];
q[0] = name;
res = PQexecParams(H->server, "SELECT CURRVAL($1)", 1, NULL, q, NULL, NULL, 0);
}
status = PQresultStatus(res);
if (res && (status == PGRES_TUPLES_OK)) {
id = estrdup((char *)PQgetvalue(res, 0, 0));
*len = PQgetlength(res, 0, 0);
} else {
pdo_pgsql_error(dbh, status, pdo_pgsql_sqlstate(res));
}
if (res) {
PQclear(res);
}
return id;
}
/*
* Receive a single file as a malloc'd buffer.
*/
char *
libpqGetFile(const char *filename, size_t *filesize)
{
PGresult *res;
char *result;
int len;
const char *paramValues[1];
paramValues[0] = filename;
res = PQexecParams(conn, "SELECT pg_read_binary_file($1)",
1, NULL, paramValues, NULL, NULL, 1);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
pg_fatal("could not fetch remote file \"%s\": %s",
filename, PQresultErrorMessage(res));
/* sanity check the result set */
if (PQntuples(res) != 1 || PQgetisnull(res, 0, 0))
pg_fatal("unexpected result set while fetching remote file \"%s\"\n",
filename);
/* Read result to local variables */
len = PQgetlength(res, 0, 0);
result = pg_malloc(len + 1);
memcpy(result, PQgetvalue(res, 0, 0), len);
result[len] = '\0';
PQclear(res);
pg_log(PG_DEBUG, "fetched file \"%s\", length %d\n", filename, len);
if (filesize)
*filesize = len;
return result;
}
bool v_find_public_ages(const DS::String& ageFilename, std::vector<Auth_PubAgeRequest::NetAgeInfo>& ages)
{
PostgresStrings<2> parms;
parms.set(0, DS::Vault::e_NodeAgeInfo);
parms.set(1, ageFilename);
// ageInfoId, Uuid, InstName, UserName, Description, SeqNumber, Language
PGresult* result = PQexecParams(s_postgres,
"SELECT idx, \"Uuid_1\", \"String64_3\", \"String64_4\","
" \"Text_1\",\"Int32_1\", \"Int32_3\" FROM vault.\"Nodes\""
" WHERE \"NodeType\"=$1 AND \"Int32_2\"=1 AND \"String64_2\"=$2"
" ORDER BY \"ModifyTime\" LIMIT 50",
2, 0, parms.m_values, 0, 0, 0);
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
PQclear(result);
return false;
}
for (int i = 0; i < PQntuples(result); ++i) {
Auth_PubAgeRequest::NetAgeInfo ai;
ai.m_instance = DS::Uuid(PQgetvalue(result, i, 1));
ai.m_instancename = PQgetvalue(result, i, 2);
ai.m_username = PQgetvalue(result, i, 3);
ai.m_description = PQgetvalue(result, i, 4);
ai.m_sequence = strtoul(PQgetvalue(result, i, 5), 0, 10);
ai.m_language = strtoul(PQgetvalue(result, i, 6), 0, 10);
ai.m_curPopulation = v_count_age_population(PQgetvalue(result, i, 1));
ai.m_population = v_count_age_owners(strtoul(PQgetvalue(result, i, 0), 0 , 10));
ages.push_back(ai);
}
PQclear(result);
return true;
}
DS::Vault::NodeRef v_send_node(uint32_t nodeId, uint32_t playerId, uint32_t senderId)
{
DS::Vault::NodeRef ref;
ref.m_child = ref.m_owner = ref.m_parent = 0;
PostgresStrings<2> parms;
parms.set(0, playerId);
parms.set(1, DS::Vault::e_InboxFolder);
PGresult* result = PQexecParams(s_postgres,
"SELECT idx FROM vault.find_folder($1, $2);",
2, 0, parms.m_values, 0, 0, 0);
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
PQclear(result);
return ref;
}
DS_DASSERT(PQntuples(result) == 1);
uint32_t inbox = strtoul(PQgetvalue(result, 0, 0), 0, 10);
PQclear(result);
if (v_ref_node(inbox, nodeId, senderId)) {
ref.m_child = nodeId;
ref.m_owner = senderId;
ref.m_parent = inbox;
}
return ref;
}
void cmdev_list_game(const struct lh_kv_elem *params, const struct lh_kv_elem *cookies, struct lh_response* resp) {
//check auth
cm::Session session;
int err = cm::find_session(cookies, session);
if (err) {
return cm_send_error(resp, CMERR_AUTH);
}
//check param
const char *appid = lh_kv_string(params, "appid", &err);
if (err) {
return cm_send_error(resp, CMERR_PARAM);
}
//check db
PGconn *cmatchdb = cm::get_context()->cmatchdb;
if (PQstatus(cmatchdb) != CONNECTION_OK) {
return cm_send_error(resp, CMERR_DB);
}
//qurey db
const int VAR_NUMS = 1;
const char *vars[VAR_NUMS] = {
appid,
};
PGresult *res = PQexecParams(cmatchdb,
"SELECT id, name FROM game WHERE app_id=$1;",
VAR_NUMS, NULL, vars, NULL, NULL, 0);
Autofree af_res_cl(res, (Freefunc)PQclear);
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
return cm_send_error(resp, CMERR_DB);
}
int rownums = PQntuples(res);
if (rownums == 0) {
return cm_send_ok(resp);
}
std::stringstream ssout;
ssout << "{\"error\":0,\"games\":{";
bool first = true;
for (int i = 0; i < rownums; ++i) {
const char* sid = PQgetvalue(res, i, 0);
const char* sname = PQgetvalue(res, i, 1);
if (sid && sname) {
if (first)
first = false;
else
ssout << ",";
ssout << "\"" << sid << "\"" << ":\"" << sname << "\"";
}
}
ssout << "}}";
//send to client
lh_append_body(resp, ssout.str().c_str());
}
PGresult *OGRPG_PQexec(PGconn *conn, const char *query, int bMultipleCommandAllowed,
int bErrorAsDebug)
{
PGresult* hResult;
#if defined(PG_PRE74)
/* PQexecParams introduced in PG >= 7.4 */
hResult = PQexec(conn, query);
#else
if (bMultipleCommandAllowed)
hResult = PQexec(conn, query);
else
hResult = PQexecParams(conn, query, 0, NULL, NULL, NULL, NULL, 0);
#endif
#ifdef DEBUG
const char* pszRetCode = "UNKNOWN";
char szNTuples[32];
szNTuples[0] = '\0';
if (hResult)
{
switch(PQresultStatus(hResult))
{
case PGRES_TUPLES_OK:
pszRetCode = "PGRES_TUPLES_OK";
snprintf(szNTuples, sizeof(szNTuples), ", ntuples = %d", PQntuples(hResult));
break;
case PGRES_COMMAND_OK:
pszRetCode = "PGRES_COMMAND_OK";
break;
case PGRES_NONFATAL_ERROR:
pszRetCode = "PGRES_NONFATAL_ERROR";
break;
case PGRES_FATAL_ERROR:
pszRetCode = "PGRES_FATAL_ERROR";
break;
default:
break;
}
}
if (bMultipleCommandAllowed)
CPLDebug("PG", "PQexec(%s) = %s%s", query, pszRetCode, szNTuples);
else
CPLDebug("PG", "PQexecParams(%s) = %s%s", query, pszRetCode, szNTuples);
#endif
/* -------------------------------------------------------------------- */
/* Generate an error report if an error occurred. */
/* -------------------------------------------------------------------- */
if ( !hResult || (PQresultStatus(hResult) == PGRES_NONFATAL_ERROR ||
PQresultStatus(hResult) == PGRES_FATAL_ERROR ) )
{
if( bErrorAsDebug )
CPLDebug("PG", "%s", PQerrorMessage( conn ) );
else
CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage( conn ) );
}
return hResult;
}
void cmdev_add_match(const struct lh_kv_elem *params, const struct lh_kv_elem *cookies, struct lh_response* resp) {
//check auth
cm::Session session;
int err = cm::find_session(cookies, session);
if (err) {
return cm_send_error(resp, CMERR_AUTH);
}
//parse param
const char *appid = lh_kv_string(params, "appid", &err);
const char *gameid = lh_kv_string(params, "gameid", &err);
const char *begin = lh_kv_string(params, "begin", &err);
const char *end = lh_kv_string(params, "end", &err);
const char *matchname = lh_kv_string(params, "matchname", NULL);
if (matchname && matchname[0] == 0) {
matchname = NULL;
}
if (err) {
return cm_send_error(resp, CMERR_PARAM);
}
//check db
PGconn *cmatchdb = cm::get_context()->cmatchdb;
if (PQstatus(cmatchdb) != CONNECTION_OK) {
return cm_send_error(resp, CMERR_DB);
}
//insert into db
const int VAR_NUMS = 6;
const char *vars[VAR_NUMS] = {
matchname,
gameid,
appid,
session.userid.c_str(),
begin,
end,
};
PGresult *res = PQexecParams(cmatchdb,
"INSERT INTO match (name, game_id, app_id, developer_id, begin_time, end_time) "
"SELECT $1, $2, $3, $4, $5::TIMESTAMP, $6::TIMESTAMP "
"WHERE $4=(SELECT developer_id FROM game WHERE id=$2) "
"AND $3=(SELECT app_id FROM game WHERE id=$2) "
"AND $5::TIMESTAMP<$6::TIMESTAMP "
"RETURNING id;",
VAR_NUMS, NULL, vars, NULL, NULL, 0);
Autofree af_res_insert(res, (Freefunc)PQclear);
if (PQresultStatus(res) != PGRES_TUPLES_OK){
return cm_send_error(resp, CMERR_DB);
}
//get match id
const char* matchid = PQgetvalue(res, 0, 0);
if (matchid == NULL) {
return cm_send_error(resp, CMERR_DB);
}
//send to client
lh_appendf_body(resp, "{\"error\":0, \"matchid\":%s}", matchid);
}
void real_query()
{
char const * const *pvalues = 0;
int *plengths = 0;
int *pformats = 0;
std::vector<char const *> values;
std::vector<int> lengths;
std::vector<int> formats;
if(params_>0) {
values.resize(params_,0);
lengths.resize(params_,0);
formats.resize(params_,0);
for(unsigned i=0;i<params_;i++) {
if(params_set_[i]!=null_param) {
if(params_pvalues_[i]!=0) {
values[i]=params_pvalues_[i];
lengths[i]=params_plengths_[i];
}
else {
values[i]=params_values_[i].c_str();
lengths[i]=params_values_[i].size();
}
if(params_set_[i]==binary_param) {
formats[i]=1;
}
}
}
pvalues=&values.front();
plengths=&lengths.front();
pformats=&formats.front();
}
if(res_) {
PQclear(res_);
res_ = 0;
}
if(prepared_id_.empty()) {
res_ = PQexecParams(
conn_,
query_.c_str(),
params_,
0, // param types
pvalues,
plengths,
pformats, // format - text
0 // result format - text
);
}
else {
res_ = PQexecPrepared(
conn_,
prepared_id_.c_str(),
params_,
pvalues,
plengths,
pformats, // format - text
0 // result format - text
);
}
}
int CHttpThread::PgExecuteSQL(PGconn *conn,
PGresult * &dataset,
const char * szSQL,
int nParams,
const char * const *paramValues,
char * szErrMsg)
{
int retrycnt=0;
sqlexecretry:
dataset = PQexecParams(m_pq,
szSQL,
nParams, /* 参数个数 */
NULL, /* 让后端推出参数类型 */
paramValues,
NULL, /* 因为是文本，所以必须要参数长度 */
NULL, /* 缺省是全部文本参数 */
0); /* 是否是二进制结果 */
if( (PQresultStatus(dataset) == PGRES_COMMAND_OK ) ||(PQresultStatus(dataset) == PGRES_TUPLES_OK))
{
printf("Successfully execute SQL : %s\n",szSQL);
return 0;
}
else
{
sprintf(szErrMsg,"%s",PQerrorMessage(m_pq));
printf("%s\n",szErrMsg);
PQclear(dataset);
if(PQstatus(m_pq) != CONNECTION_OK)
{
if(retrycnt > 3)
{
return -1;
}
sleep(1);
PQreset(m_pq);
retrycnt++;
if(PQstatus(m_pq)!=CONNECTION_OK)
{
printf("Thread %d reconnect database fail!\n",m_id);
PQclear(dataset);
goto sqlexecretry;
}
else
{
printf("Thread %d reconnect database success!\n",m_id);
}
}
else //非连接性错误，可能是SQL语句错误等原因
{
return -1;
}
}
}
DB_Detail *DB_get_details(Database *db, unsigned int computer_id){
PGconn *conn;
PGresult *res;
DB_Detail *details;
int nTuples, i;
const char *paramValues[1];
const int paramFormats[1] = {BINARY_FRMT};
const int paramLengths[1] = {sizeof(uint32_t)};
uint32_t tmp_id;
if(_DB_connect(db, &conn) != 0) return NULL;
paramValues[0] = order_int(computer_id, &tmp_id);
res = PQexecParams(conn,
"SELECT d.id, d.vendor_id, d.device_id, d.subsystem_id, "
"d.serial, d.created_at "
"FROM "CONFIGS_TABNAME" cd "
"JOIN "DETAILS_TABNAME" d ON d.id = cd.detail_id "
"WHERE "
"cd.computer_id = $1::int AND "
"cd.allow_del_at IS NULL;",
1, /* count of params */
NULL, /* let the backend deduce param type */
paramValues,
paramLengths,
paramFormats,
TEXT_FRMT); /* text results */
if (PQresultStatus(res) != PGRES_TUPLES_OK){
_DB_close_res(db, conn, res);
return NULL;
}
nTuples = PQntuples(res);
details = (DB_Detail *) malloc(sizeof(DB_Detail) * nTuples);
for(i = 0; i < nTuples; i++){
details[i].id = (unsigned int) atoi(PQgetvalue(res, i, 0));
details[i].vendor_id = (unsigned int) atoi(PQgetvalue(res, i, 1));
details[i].device_id = (unsigned int) atoi(PQgetvalue(res, i, 2));
details[i].subsystem_id = (unsigned int) atoi(PQgetvalue(res, i, 3));
strncpy(details[i].serial,
PQgetvalue(res, i, 4),
DETAIL_SERIAL_SIZE);
details[i].created_at = str_to_time(PQgetvalue(res, i, 5));
printf("Detail: %4d, %.4x, %.4x, %.8x, '%s', %d\n",
details[i].id,
details[i].vendor_id,
details[i].device_id,
details[i].subsystem_id,
details[i].serial,
(int) details[i].created_at
);
}
PQclear(res);
PQfinish(conn);
return details;
}
/** Determine the format of timestamps used by the server.
* A PostgresSQL server can be configured to store timestamps either as 8-byte
* integers or floating point numbers with double precision. This functions
* sends a simple SQL query to the server and tries to determine the format of
* timestamps from the reply. This function is executed once after connecting
* to a PostgreSQL server and the result of the detection is then stored in
* form of a flag in pg_con connection structure.
* @param con A PostgreSQL connection handle
* @retval 0 If the server stores timestamps as floating point numbers.
* @retval 1 If the server stores timestamps as 8-byte integers.
* @retval A negative number on error.
*/
static int timestamp_format(PGconn *con)
{
unsigned long long offset;
PGresult *res = 0;
char *val;
str sql;
if(build_timestamp_format_sql(&sql) != 0) {
ERR("postgres: Error while building SQL query to obtain timestamp "
"format\n");
return -1;
}
res = PQexecParams(con, sql.s, 0, 0, 0, 0, 0, 1);
pkg_free(sql.s);
if(PQfformat(res, 0) != 1) {
ERR("postgres: Binary format expected but server sent text\n");
goto error;
}
if(PQntuples(res) != 1) {
ERR("postgres: Only one column expected, %d received\n",
PQntuples(res));
goto error;
}
if(PQnfields(res) != 1) {
ERR("postgres: Only one row expected, %d received\n", PQnfields(res));
goto error;
}
val = PQgetvalue(res, 0, 0);
offset = ((unsigned long long)ntohl(((unsigned int *)val)[0]) << 32)
+ ntohl(((unsigned int *)val)[1]);
PQclear(res);
/* Server using int8 timestamps would return 1000000, because it stores
* timestamps in microsecond resolution across the whole range. Server
* using double timestamps would return 1 (encoded as double) here because
* subsection fraction is stored as fractional part in the IEEE
* representation. 1 stored as double would result in 4607182418800017408
* when the memory location occupied by the variable is read as unsigned
* long long.
*/
if(offset == 1000000) {
DBG("postgres: Server uses int8 format for timestamps.\n");
return 1;
} else {
DBG("postgres: Server uses double format for timestamps.\n");
return 0;
}
error:
PQclear(res);
return -1;
}
/*
* Execute an SQL request
*/
PGresult * ows_psql_exec(ows *o, const char *sql)
{
assert(o);
assert(sql);
assert(o->pg);
ows_log(o, 8, sql);
return PQexecParams(o->pg, sql, 0, NULL, NULL, NULL, NULL, 0);
}
void pp_export(PGconn * conn, const char * query)
{
const int ascFmt = 0;
int len = 0, i;
int rows, ncol, icol;
char * val;
char * file;
PGresult * res;
const char * const * ival = (const char * const *) &val;
FILE * fimg;
/* execute the user provided query */
res = PQexecParams(conn, query,
0, //n. of params
NULL, //oids guessed by backend
NULL,
NULL,
NULL,
ascFmt);
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
pp_print_error(PQerrorMessage(conn));
PQclear(res);
return;
}
/* some check */
icol = PQfnumber(res, opts.imagecol);
if(icol==-1) {
fprintf(stderr, "ERROR: Image column '%s' does not exist in result set.\n", opts.imagecol);
PQclear(res);
return;
}
if (!opts.nameprefix) {
ncol = PQfnumber(res, opts.namecol);
if(ncol==-1) {
fprintf(stderr, "ERROR: Name column '%s' does not exist in result set.\n", opts.namecol);
PQclear(res);
return;
}
}
rows = PQntuples(res);
/* fetch the data and save */
for (i = 0; i < rows; ++i) {
val = PQunescapeBytea( PQgetvalue(res, i, icol), &len);
if(opts.namecol) file = PQgetvalue(res, i, ncol);
else file = image_name(opts.nameprefix, i);
fimg = fopen(file, "w");
fwrite(val, len, 1, fimg);
fclose(fimg);
PQfreemem(val);
fprintf(stderr, "INFO: exported file %s\n", file);
}
PQclear(res);
}
static void doStuffWithConnection(PGconn * const conn) {
struct H { // just for the helper function
inline static void helper(PGconn * const conn, PGresult * const result) {
const ExecStatusType es = PQresultStatus(result);
if (PGRES_TUPLES_OK == es) {
const bool schemaIsOk = verifyTheSchema(conn, result);
if (schemaIsOk) {
std::cout << "All schema checks passed\n";
processReturnedTable(result);
} // else rely on specific checks to print messages
PQclear(result); // Despite name, is *not* just a memset-style 'clear'.
// It calls 'free' for us, see https://git.io/vKpNI
} else { // then PGRES_TUPLES_OK != es
if ( PGRES_COMMAND_OK == es ) {
std::cerr << "Expected to run a command which returns column data\n";
} else { // we have a real error
char * const errorMessage = PQresultErrorMessage(result);
BOOST_ASSERT(( NULL != errorMessage ));
std::cerr << "Error attempting query:\n";
printHorizontalBar(std::cerr);
std::cerr << '\n' << errorMessage << '\n';
printHorizontalBar(std::cerr);
std::cerr << "\n\n";
}
}
}
};
BOOST_ASSERT( NULL != conn );
//PGresult * const result = PQexec(conn, "SELECT * FROM my_table WHERE ((number = 1) or (english='Twenty'));");
//PGresult * const result = PQexec(conn, "SELECT * FROM my_table WHERE ((number = 1) or (lower(english)='two'));");
PGresult * const result =
PQexecParams(conn,
"SELECT * FROM my_table WHERE ((number = 1) or (english='Twenty'));",
0,
NULL,
NULL,
NULL,
NULL,
1); // request binary format outputs
if (NULL != result) {
H::helper(conn,result);
} else {
std::cerr << "Error attempting SELECT query. Likely causes: failed network connection, server down, or out-of-memory\n";
}
}
static std::tuple<uint32_t, uint32_t>
find_a_friendly_neighborhood_for_our_new_visitor()
{
PGresult* result = PQexec(s_postgres,
"SELECT idx FROM vault.\"Nodes\" WHERE \"String64_2\"="
" 'Neighborhood' AND \"String64_4\" = '" HOOD_USER_NAME "'"
" ORDER BY \"Int32_1\"");
if (PQresultStatus(result) != PGRES_TUPLES_OK) {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
PQclear(result);
return std::make_tuple<uint32_t, uint32_t>(0, 0);
}
uint32_t theHoodInfo = 0;
for (int i = 0; i < PQntuples(result); ++i) {
uint32_t ageInfoId = strtoul(PQgetvalue(result, i, 0), 0, 10);
uint32_t owners = v_count_age_owners(ageInfoId);
if (owners < HOOD_POPULATION_THRESHOLD) {
theHoodInfo = ageInfoId;
break;
}
}
PQclear(result);
// Need new hood?
if (theHoodInfo == 0) {
AuthServer_AgeInfo age;
age.m_ageId = gen_uuid();
age.m_filename = "Neighborhood";
age.m_instName = HOOD_INSTANCE_NAME;
age.m_userName = HOOD_USER_NAME;
age.m_description = HOOD_USER_NAME " " HOOD_INSTANCE_NAME;
age.m_seqNumber = -1; // Auto-generate
theHoodInfo = std::get<1>(v_create_age(age, e_AgePublic));
}
// It's important to BCast new hood members, so we'll return the ageOwners folder
PostgresStrings<2> parms;
parms.set(0, theHoodInfo);
parms.set(1, DS::Vault::e_AgeOwnersFolder);
result = PQexecParams(s_postgres, "SELECT idx FROM vault.find_folder($1, $2);",
2, 0, parms.m_values, 0, 0, 0);
if (PQresultStatus(result) == PGRES_TUPLES_OK) {
uint32_t ownersFolder = strtoul(PQgetvalue(result, 0, 0), 0, 10);
PQclear(result);
return std::make_tuple(theHoodInfo, ownersFolder);
} else {
fprintf(stderr, "%s:%d:\n Postgres SELECT error: %s\n",
__FILE__, __LINE__, PQerrorMessage(s_postgres));
PQclear(result);
return std::make_tuple(0, 0);
}
}
DBMSqlDatatable* PostgreSql::exec(const core::string &sql, const params_type ¶ms)
{
usize const sz_params = params.size();
if (0 == sz_params)
return this->exec(sql);
params_type str_params = params;
//Oid* pm_types = (Oid*)malloc(sz_params * sizeof(Oid));
char const* *pm_vals = (char const* *)malloc(sz_params * sizeof(char const*));
int* pm_lens = (int*)malloc(sz_params * sizeof(int));
int* pm_fmts = (int*)malloc(sz_params * sizeof(int));
for (core::counter<params_type::iterator> each = str_params.begin();
each != str_params.end();
++each)
{
variant_t& var = each->second;
var.stringize();
pm_vals[each.count] = (char const*)var;
pm_lens[each.count] = var.size();
pm_fmts[each.count] = 0;
}
PGresult* res = PQexecParams(d_ptr->pg,
sql.c_str(),
sz_params,
NULL,
pm_vals,
pm_lens,
pm_fmts,
0);
//free(pm_types);
free(pm_vals);
free(pm_lens);
free(pm_fmts);
PgSqlDatatable* dt = NULL;
switch (PQresultStatus(res))
{
default:
trace_msg(PQresultErrorMessage(res));
break;
case PGRES_COMMAND_OK: break;
case PGRES_TUPLES_OK:
{
dt = new PgSqlDatatable(res);
dt->update();
} break;
};
if (dt == NULL)
PQclear(res);
else if (dt->rows().size() == 0)
zero_drop(dt);
return dt;
}
