static bool
create_database(DbType type, QofBackend *qbe, dbi_conn conn, const char* db)
{
const char *dbname;
const char *dbcreate;
if (type == DbType::DBI_MYSQL)
{
dbname = "mysql";
dbcreate = "CREATE DATABASE %s CHARACTER SET utf8";
}
else
{
dbname = "postgres";
dbcreate = "CREATE DATABASE %s WITH TEMPLATE template0 ENCODING 'UTF8'";
}
PairVec options;
options.push_back(std::make_pair("dbname", dbname));
try
{
set_options(conn, options);
}
catch (std::runtime_error& err)
{
qof_backend_set_error (qbe, ERR_BACKEND_SERVER_ERR);
return false;
}
auto result = dbi_conn_connect (conn);
if (result < 0)
{
PERR ("Unable to connect to %s database", dbname);
qof_backend_set_error (qbe, ERR_BACKEND_SERVER_ERR);
return false;
}
if (type == DbType::DBI_MYSQL)
adjust_sql_options(conn);
auto dresult = dbi_conn_queryf (conn, dbcreate, db);
if (dresult == nullptr)
{
PERR ("Unable to create database '%s'\n", db);
qof_backend_set_error (qbe, ERR_BACKEND_SERVER_ERR);
return false;
}
if (type == DbType::DBI_PGSQL)
{
const char *alterdb = "ALTER DATABASE %s SET "
"standard_conforming_strings TO on";
dbi_conn_queryf (conn, alterdb, db);
}
dbi_conn_close(conn);
conn = nullptr;
return true;
}
int swd::database::save_hash(const int& request_id, const std::string& algorithm,
const std::string& digest) {
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *algorithm_esc = strdup(algorithm.c_str());
dbi_conn_quote_string(conn_, &algorithm_esc);
char *digest_esc = strdup(digest.c_str());
dbi_conn_quote_string(conn_, &digest_esc);
dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO hashes (request_id, "
"algorithm, digest) VALUES (%i, %s, %s)", request_id, algorithm_esc, digest_esc);
free(algorithm_esc);
free(digest_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute hash query");
}
int id = dbi_conn_sequence_last(conn_, "hashes_id_seq");
dbi_result_free(res);
return id;
}
/**
* Add a new registration to the database
*
* @param c the db configuration
* @param s the server
* @param r the registration
*
* @return 1 on success
*/
int db_add_registration(struct config *c, struct server *s, struct registration *r)
{
char *req = "INSERT INTO registrations (server_id, serveradmin, name, password) VALUES (%i, %i, %s, %s);";
char *quoted_name, *quoted_pass;
dbi_result res;
struct channel *ch;
struct player_channel_privilege *priv;
size_t iter, iter2;
dbi_conn_quote_string_copy(c->conn, r->name, "ed_name);
dbi_conn_quote_string_copy(c->conn, r->password, "ed_pass);
res = dbi_conn_queryf(c->conn, req, s->id, r->global_flags, quoted_name, quoted_pass);
if (res == NULL) {
logger(LOG_WARN, "db_add_registration : SQL query failed");
} else {
r->db_id = dbi_conn_sequence_last(c->conn, NULL);
dbi_result_free(res);
}
free(quoted_pass);
free(quoted_name);
ar_each(struct channel *, ch, iter, s->chans)
ar_each(struct player_channel_privilege *, priv, iter2, ch->pl_privileges)
if (priv->reg == PL_CH_PRIV_REGISTERED && priv->pl_or_reg.reg == r) {
logger(LOG_INFO, "db_add_registration : adding a new pl_chan_priv");
db_add_pl_chan_priv(c, priv);
}
ar_end_each;
ar_end_each;
return 1;
}
/**
* Go through the database, read and add to the server all
* the registrations stored.
*
* @param c the configuration of the db
* @param s the server
*/
int db_create_registrations(struct config *c, struct server *s)
{
char *q = "SELECT * FROM registrations WHERE server_id = %i;";
struct registration *r;
char *name, *pass;
dbi_result res;
res = dbi_conn_queryf(c->conn, q, s->id);
if (res) {
while (dbi_result_next_row(res)) {
r = new_registration();
r->db_id = dbi_result_get_uint(res, "id");
r->global_flags = dbi_result_get_uint(res, "serveradmin");
name = dbi_result_get_string_copy(res, "name");
strncpy(r->name, name, MIN(29, strlen(name)));
pass = dbi_result_get_string_copy(res, "password");
strcpy(r->password, pass);
add_registration(s, r);
/* free temporary variables */
free(pass); free(name);
}
dbi_result_free(res);
}
return 1;
}
bool swd::database::is_flooding(const std::string& client_ip,
const int& profile_id) {
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *client_ip_esc = strdup(client_ip.c_str());
dbi_conn_quote_string(conn_, &client_ip_esc);
dbi_result res = dbi_conn_queryf(conn_, "SELECT is_flooding(%i, %s) AS result",
profile_id, client_ip_esc);
free(client_ip_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute request count query");
}
bool flooding = false;
if (dbi_result_get_numrows(res) == 1) {
if (!dbi_result_next_row(res)) {
throw swd::exceptions::database_exception("No flooding?");
}
flooding = (dbi_result_get_uint(res, "result") == 1);
}
dbi_result_free(res);
return flooding;
}
int rpserver_db_authenticate(dbi_conn db, const char *user, const char *password)
{
int retval = AUTH_UNKNOWN_USER;
dbi_result result;
LOG(LOG_INFO, "%s():%d - username: %s password: %s", __FUNCTION__, __LINE__, user, password);
return 0;
result = dbi_conn_queryf(db,
"SELECT * FROM `users` WHERE `username` = '%s' AND `password` = MD5('%s')",
user, password);
LOG(LOG_INFO, "%s():%d - SELECT * FROM `users` WHERE `username` = '%s' AND `password` = '%s'",
__FUNCTION__, __LINE__, user, password);
if (result) {
if( dbi_result_get_numrows(result) == 1 ) {
while (dbi_result_next_row(result)) {
LOG(LOG_INFO, "%s():%d - User \"%s\" is known and ACTIVE.",
__FUNCTION__, __LINE__, user);
retval = AUTH_VALID;
}
} else {
LOG(LOG_INFO, "%s():%d - User \"%s\" is unknown",
__FUNCTION__, __LINE__, user);
}
}
dbi_result_free(result);
return retval;
}
///////////////////////////////////
// developer's utils ;-)
int com_test(int p, param_list param)
{
dbi_result result;
unsigned int idnumber;
const char *fullname;
int j,k;
for(k=1;k<10;k++)
{
for(j=1; j<100; j++)
{
char *fen=get_fen();
result = dbi_conn_queryf(conn, "INSERT INTO test (fen) values ('%s')",fen);
FREE(fen);
if (result) {
while (dbi_result_next_row(result)) {
pprintf(p,"OK\n");
}
dbi_result_free(result);
}
else
{
//pprintf(p,"no result\n");
}
}
}
pprintf(p,"OK\n");
return COM_OK;
}
int rpserver_db_pair_lookup(dbi_conn *db, const char *localid, char *buf, size_t maxlen)
{
dbi_result *result;
int len = 0;
result = dbi_conn_queryf(db,
"SELECT hname FROM "DB_TABLE_DEVICES
" WHERE localid='%s' AND name IS NULL "
,localid);
if(result){
if(dbi_result_next_row(result)) {
len += snprintf(buf+len, maxlen-len, "%s",
dbi_result_get_string(result, "hname"));
}
} else {
const char *err;
dbi_conn_error(db, &err);
LOG(LOG_INFO, "%s():%d - dbi error querying database (%s)\n",
__FUNCTION__, __LINE__, err);
return -1;
}
dbi_result_free(result);
return len;
}
static char
query_handler(char *query_name, char *myq)
{
if(myq == NULL) {
myq = "SELECT NULL";
}
if(query_name == NULL) {
query_name = "noname";
}
if(query_name == "") {
query_name = "noname";
}
dbi_initialize(NULL);
conn = dbi_conn_new("mysql");
dbi_conn_set_option(conn, "host", getenv("DB_HOSTNAME"));
dbi_conn_set_option(conn, "username", getenv("DB_USERNAME"));
dbi_conn_set_option(conn, "password", getenv("DB_PASSWORD"));
dbi_conn_set_option(conn, "dbname", getenv("DB_DATABASE"));
dbi_conn_set_option(conn, "encoding", "UTF-8");
if (dbi_conn_connect(conn) < 0) {
printf("Could not connect. Please check the option settings and if the" \
"specific driver is available\n");
} else {
result = dbi_conn_queryf(conn, myq, threshold);
if (result) {
xmlAddChild(sql_node,query_doc(result,query_name));
dbi_result_free(result);
}
dbi_conn_close(conn);
}
return 0;
}
template<> StrVec
GncDbiProviderImpl<DbType::DBI_MYSQL>::get_index_list (dbi_conn conn)
{
StrVec retval;
const char* errmsg;
auto tables = get_table_list(conn, "");
for (auto table_name : tables)
{
auto result = dbi_conn_queryf (conn,
"SHOW INDEXES IN %s WHERE Key_name != 'PRIMARY'",
table_name.c_str());
if (dbi_conn_error (conn, &errmsg) != DBI_ERROR_NONE)
{
PWARN ("Index Table Retrieval Error: %s on table %s\n",
errmsg, table_name.c_str());
continue;
}
while (dbi_result_next_row (result) != 0)
{
std::string index_name {dbi_result_get_string_idx (result, 3)};
retval.push_back(index_name + " " + table_name);
}
dbi_result_free (result);
}
return retval;
}
template <DbType P> void
GncDbiProviderImpl<P>::drop_index(dbi_conn conn, const std::string& index)
{
dbi_result result = dbi_conn_queryf (conn, "DROP INDEX %s", index.c_str());
if (result)
dbi_result_free (result);
}
int swd::database::save_parameter(const int& request_id, const std::string& path,
const std::string& value, const int& total_whitelist_rules,
const int& critical_impact, const int& threat) {
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *path_esc = strdup(path.c_str());
dbi_conn_quote_string(conn_, &path_esc);
char *value_esc = strdup(value.c_str());
dbi_conn_quote_string(conn_, &value_esc);
dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO parameters "
"(request_id, path, value, total_whitelist_rules, critical_impact, threat) "
"VALUES (%i, %s, %s, %i, %i, %i)", request_id, path_esc, value_esc,
total_whitelist_rules, critical_impact, threat);
free(path_esc);
free(value_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute parameter query");
}
int id = dbi_conn_sequence_last(conn_, "parameters_id_seq");
dbi_result_free(res);
return id;
}
static void calc_ser_cds(struct module *mod, dbi_conn conn, int interval)
{
int ct = 0, st = 0;
dbi_result result = 0;
ST_CDS *ps = NULL;
ST_CDS *pe = NULL;
long long rst = 0;
ps = (ST_CDS *)mod->ser_array;
pe = (ST_CDS *)mod->emp_array;
st = 0;
/* acquire ser uss stats */
result = dbi_conn_queryf(conn, "select d_id from \"T_Device\" where d_type = 'mds' order by d_id");
if (result) {
while(dbi_result_next_row(result)) {
ps[st].sdr_id = dbi_result_get_longlong(result, "d_id");
convert_time_to_string(statis.cur_time, ps[st].sdr_record_time, 0);
convert_time_to_string(statis.cur_time - 24*60*60, ps[st].sdr_time, 1);
if (interval == 0) {
ps[st].sdr_date_flag = 0;
} else if (interval == 7) {
ps[st].sdr_date_flag = 1;
} else {
ps[st].sdr_date_flag = 2;
}
st++;
}
dbi_result_free(result);
}
for (st = 0; st < mod->ser_record; st++)
{
result = dbi_conn_queryf(conn, "select e_id from \"T_Enterprise\" where \
e_mds_id = %lld order by e_id ", ps[st].sdr_id);
if (result) {
while(dbi_result_next_row(result)) {
rst = dbi_result_get_longlong(result, "e_id");
for (ct = 0; ct < mod->emp_record; ct++) {
if (rst == pe[ct].sdr_id) {
ps[st].sdr_ptt_htime += pe[ct].sdr_ptt_htime;
ps[st].sdr_ptt_hcount += pe[ct].sdr_ptt_hcount;
ps[st].sdr_call_htime += pe[ct].sdr_call_htime;
ps[st].sdr_call_hcount += pe[ct].sdr_call_hcount;
ps[st].sdr_video_htime += pe[ct].sdr_video_htime;
ps[st].sdr_video_hcount += pe[ct].sdr_video_hcount;
ps[st].sdr_audio_htime += pe[ct].sdr_audio_htime;
ps[st].sdr_audio_hcount += pe[ct].sdr_audio_hcount;
}
}
}
dbi_result_free(result);
}
}
//log_cds_stats(ps, mod->ser_record);
return;
}
swd::profile_ptr swd::database::get_profile(const std::string& server_ip,
const int& profile_id) {
std::stringstream log_message;
log_message << "Get profile from db -> server_ip: " << server_ip
<< "; profile_id: " << profile_id;
swd::log::i()->send(swd::notice, log_message.str());
/* Test the database connection status. Tries to reconnect if disconnected. */
ensure_connection();
/* Mutex to avoid race conditions. */
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
/**
* First we escape server_ip. It comes from a trusted source, but better safe
* than sorry. This does not work with std::string though.
*/
char *server_ip_esc = strdup(server_ip.c_str());
dbi_conn_quote_string(conn_, &server_ip_esc);
/* Insert the ip and execute the query. */
dbi_result res = dbi_conn_queryf(conn_, "SELECT id, hmac_key, mode, "
"whitelist_enabled, blacklist_enabled, integrity_enabled, flooding_enabled, "
"blacklist_threshold, cache_outdated FROM profiles WHERE %s LIKE "
"prepare_wildcard(server_ip) AND id = %i", server_ip_esc, profile_id);
/* Don't forget to free server_ip_esc to avoid a memory leak. */
free(server_ip_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute profile query");
}
if (dbi_result_get_numrows(res) != 1) {
throw swd::exceptions::database_exception("Can't get profile");
}
if (!dbi_result_next_row(res)) {
throw swd::exceptions::database_exception("No profile?");
}
swd::profile_ptr profile(new swd::profile());
profile->set_server_ip(server_ip),
profile->set_id(dbi_result_get_uint(res, "id"));
profile->set_mode(dbi_result_get_uint(res, "mode"));
profile->set_whitelist_enabled(dbi_result_get_uint(res, "whitelist_enabled") == 1);
profile->set_blacklist_enabled(dbi_result_get_uint(res, "blacklist_enabled") == 1);
profile->set_integrity_enabled(dbi_result_get_uint(res, "integrity_enabled") == 1);
profile->set_flooding_enabled(dbi_result_get_uint(res, "flooding_enabled") == 1);
profile->set_key(dbi_result_get_string(res, "hmac_key"));
profile->set_blacklist_threshold(dbi_result_get_uint(res, "blacklist_threshold"));
profile->set_cache_outdated(dbi_result_get_uint(res, "cache_outdated") == 1);
dbi_result_free(res);
return profile;
}
static int set_uas_record(struct module *mod)
{
int j, k;
char line[LEN_4096] = {0};
char tmp[LEN_2048] = {0};
ST_UAS *pe = NULL;
tDBConn *pconn = NULL;
if ((pconn = attach_conn(GDSDB, gds_ip, gds_port)) == NULL)
{
logRecord(LOG_ERR, "%s(%d): failed to get db conn\n", __FUNCTION__, __LINE__);
return -1;
}
/* save collect data to output_db */
if (mod->emp_record != 0 && mod->emp_array != NULL) {
pe = mod->emp_array;
for (j = 0; j < mod->emp_record; j++) {
memset(line, '\0', LEN_4096);
sprintf(line, "insert into \"%s\"(", mod->table[0].table);
for (k = 0; k < mod->n_col; k++) {
memset(tmp, '\0', LEN_2048);
sprintf(tmp, "\"%s\",", mod->info[k].hdr);
strcat(line, tmp);
}
line[strlen(line) - 1] = ')';
strcat(line, "values(");
memset(tmp, '\0', LEN_2048);
sprintf(tmp, "'%lld','%s', '%lld','%lld','%lld','%d');",
pe[j].sdr_id, pe[j].sdr_time, pe[j].sdr_num,
pe[j].sdr_online_times, pe[j].sdr_offline_times,
pe[j].sdr_active_flag);
strcat(line, tmp);
dbi_conn_queryf(pconn->conn, line);
logRecord(LOG_DEBUG, "%s(%d):line->%s\n", __FUNCTION__, __LINE__, line);
}
}
if (mod->emp_array) {
free(mod->emp_array);
mod->emp_array = NULL;
}
if (mod->amp_array) {
free(mod->amp_array);
mod->amp_array = NULL;
}
if (mod->ser_array) {
free(mod->ser_array);
mod->ser_array = NULL;
}
detach_conn(pconn);
return 0;
}
static int set_ccs_record(struct module *mod)
{
int j, k;
char line[LEN_4096] = {0};
char tmp[LEN_2048] = {0};
char sdr_id_field[LEN_64] = { '\0' };
ST_CCS *pe = NULL;
tDBConn *pconn = NULL;
if ((pconn = attach_conn(GDSDB, gds_ip, gds_port)) == NULL)
{
logRecord(LOG_ERR, "%s(%d): failed to get db conn\n", __FUNCTION__, __LINE__);
return -1;
}
/* save collect data to output_db */
if (mod->emp_record != 0 && mod->emp_array != NULL) {
pe = mod->emp_array;
for (j = 0; j < mod->emp_record; j++) {
memset(line, '\0', LEN_4096);
sprintf(line, "insert into \"%s\"(", mod->table[0].table);
/*
for (k = 0; k < mod->n_col; k++) {
memset(tmp, '\0', LEN_2048);
sprintf(tmp, "\"%s\",", mod->info[k].hdr);
strcat(line, tmp);
}
*/
/* EMP: sdr_id, not include sdr_amp_id */
snprintf( sdr_id_field, sizeof(sdr_id_field) - 1, "\"%s\",", mod->info[0].hdr );
strcat( line, sdr_id_field );
for ( k = 2; k < mod->n_col; k++ ) {
memset( tmp, '\0', LEN_2048 );
snprintf( tmp, sizeof(tmp) - 1 , "\"%s\",", mod->info[k].hdr );
strncat( line, tmp, sizeof(line) - 1 );
}
line[strlen(line) - 1] = ')';
strcat(line, "values(");
memset(tmp, '\0', LEN_2048);
sprintf(tmp, "'%lld','%s','%s','%lld','%lld','%lld','%lld','%lld','%lld','%lld',\
'%lld','%lld','%lld','%lld','%lld','%lld','%lld','%lld');",
pe[j].sdr_id, pe[j].sdr_record_time, pe[j].sdr_time, pe[j].sdr_cyc_type,
pe[j].sdr_ptt_time, pe[j].sdr_ptt_count, pe[j].sdr_audio_time,
pe[j].sdr_audio_caller_time, pe[j].sdr_audio_callee_time,
pe[j].sdr_audio_count, pe[j].sdr_audio_caller_count,
pe[j].sdr_audio_callee_count, pe[j].sdr_video_time,
pe[j].sdr_video_caller_time, pe[j].sdr_video_callee_time,
pe[j].sdr_video_count, pe[j].sdr_video_caller_count,
pe[j].sdr_video_callee_count);
strcat(line, tmp);
dbi_conn_queryf(pconn->conn, line);
logRecord(LOG_DEBUG, "%s(%d):line->%s\n", __FUNCTION__, __LINE__, line);
}
}
int db_del_registration(struct config *c, struct server *s, struct registration *r)
{
char *q = "DELETE FROM registrations WHERE id = %i;";
char *q2 = "DELETE FROM player_channel_privileges WHERE player_id = %i;";
dbi_result res;
res = dbi_conn_queryf(c->conn, q, r->db_id);
if (res == NULL)
logger(LOG_WARN, "db_del_registration : SQL query failed");
else
dbi_result_free(res);
res = dbi_conn_queryf(c->conn, q2, r->db_id);
if (res == NULL)
logger(LOG_WARN, "db_del_registration : SQL query failed (2)");
else
dbi_result_free(res);
return 1;
}
/**
@brief Load the child queries subordinate to a UNION, INTERSECT, or EXCEPT query.
@param state Pointer to the query-building context.
@param parent ID of the UNION, INTERSECT, or EXCEPT query.
@param type_str The type of the query ("UNION", "INTERSECT", or "EXCEPT").
@return If successful, a pointer to a linked list of QSeq, each bearing a pointer to a
StoredQ; otherwise NULL.
The @a type_str parameter is used only for building error messages.
*/
static QSeq* loadChildQueries( BuildSQLState* state, int parent_id, const char* type_str ) {
QSeq* child_list = NULL;
// The ORDER BY is in descending order so that we can build the list by adding to
// the head, and it will wind up in the right order.
dbi_result result = dbi_conn_queryf( state->dbhandle,
"SELECT id, parent_query, seq_no, child_query "
"FROM query.query_sequence WHERE parent_query = %d ORDER BY seq_no DESC", parent_id );
if( result ) {
if( dbi_result_first_row( result ) ) {
int count = 0;
while( 1 ) {
++count;
QSeq* seq = constructQSeq( state, result );
if( seq ) {
PRINT( "Found a child query\n" );
PRINT( "\tid: %d\n", seq->id );
PRINT( "\tparent id: %d\n", seq->parent_query_id );
PRINT( "\tseq_no: %d\n", seq->seq_no );
// Add to the head of the list
seq->next = child_list;
child_list = seq;
} else{
freeQSeqList( child_list );
return NULL;
}
if( !dbi_result_next_row( result ))
break;
}
if( count < 2 ) {
osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
"%s query # %d has only one child query", type_str, parent_id ));
state->error = 1;
freeQSeqList( child_list );
return NULL;
}
} else {
osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
"%s query # %d has no child queries within it", type_str, parent_id ));
state->error = 1;
return NULL;
}
} else {
const char* msg;
int errnum = dbi_conn_error( state->dbhandle, &msg );
osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
"Unable to query query.query_sequence table: # %d %s",
errnum, msg ? msg : "No description available" ));
state->error = 1;
return NULL;
}
return child_list;
}
swd::whitelist_rules swd::database::get_whitelist_rules(const int& profile_id,
const std::string& caller, const std::string& path) {
swd::log::i()->send(swd::notice, "Get whitelist rules from db");
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *caller_esc = strdup(caller.c_str());
dbi_conn_quote_string(conn_, &caller_esc);
char *path_esc = strdup(path.c_str());
dbi_conn_quote_string(conn_, &path_esc);
/**
* Remove LIKE single character wildcard, because it could result easily in security
* problems if a user forgets to escape an underscore. And instead of a percentage sign
* it is nicer to use an asterisk, because it is more common.
*/
dbi_result res = dbi_conn_queryf(conn_, "SELECT r.id, r.path, f.id as filter_id, "
"f.rule, f.impact, r.min_length, r.max_length FROM whitelist_rules AS r, "
"whitelist_filters AS f WHERE r.filter_id = f.id AND r.profile_id = %i AND %s LIKE "
"prepare_wildcard(r.caller) AND %s LIKE prepare_wildcard(r.path) AND r.status = %i",
profile_id, caller_esc, path_esc, STATUS_ACTIVATED);
free(caller_esc);
free(path_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute whitelist_rules query");
}
swd::whitelist_rules rules;
while (dbi_result_next_row(res)) {
swd::whitelist_filter_ptr filter(new swd::whitelist_filter());
filter->set_id(dbi_result_get_uint(res, "filter_id"));
filter->set_regex(dbi_result_get_string(res, "rule"));
swd::whitelist_rule_ptr rule(new swd::whitelist_rule());
rule->set_id(dbi_result_get_uint(res, "id"));
rule->set_filter(filter);
rule->set_min_length(dbi_result_get_uint(res, "min_length"));
rule->set_max_length(dbi_result_get_uint(res, "max_length"));
rules.push_back(rule);
}
dbi_result_free(res);
return rules;
}
void swd::database::set_cache_outdated(const int& profile_id,
const bool& cache_outdated) {
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
dbi_result res = dbi_conn_queryf(conn_, "UPDATE profiles SET cache_outdated = %i "
"WHERE id = %i OR %i < 0", (cache_outdated ? 1 : 0), profile_id, profile_id);
if (!res) {
throw swd::exceptions::database_exception("Can't execute cache_outdated query");
}
}
void swd::database::add_integrity_request_connector(const int& rule_id,
const int& request_id) {
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO integrity_requests "
"(rule_id, request_id) VALUES (%i, %i)", rule_id, request_id);
if (!res) {
throw swd::exceptions::database_exception("Can't execute integrity_request query");
}
}
void swd::database::add_whitelist_parameter_connector(const int& rule_id,
const int& parameter_id) {
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO whitelist_parameters "
"(rule_id, parameter_id) VALUES (%i, %i)", rule_id, parameter_id);
if (!res) {
throw swd::exceptions::database_exception("Can't execute whitelist_parameter query");
}
}
/**
@brief Load a stored query.
@param state Pointer to the query-building context.
@param query_id ID of the query in query.stored_query.
@return A pointer to the newly loaded StoredQ if successful, or NULL if not.
The calling code is responsible for freeing the StoredQ by calling storedQFree().
*/
StoredQ* getStoredQuery( BuildSQLState* state, int query_id ) {
if( !state )
return NULL;
// Check the stack to see if the current query is nested inside itself. If it is, then
// abort in order to avoid infinite recursion. If it isn't, then add it to the stack.
// (Make sure to pop it off the stack before returning.)
if( searchIdStack( state->query_stack, query_id, NULL )) {
osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
"Infinite recursion detected; query # %d is nested within itself", query_id ));
state->error = 1;
return NULL;
} else
push_id( &state->query_stack, query_id, NULL );
StoredQ* sq = NULL;
dbi_result result = dbi_conn_queryf( state->dbhandle,
"SELECT id, type, use_all, use_distinct, from_clause, where_clause, having_clause "
"FROM query.stored_query WHERE id = %d;", query_id );
if( result ) {
if( dbi_result_first_row( result ) ) {
sq = constructStoredQ( state, result );
if( sq ) {
PRINT( "Got a query row\n" );
PRINT( "\tid: %d\n", sq->id );
PRINT( "\ttype: %d\n", (int) sq->type );
PRINT( "\tuse_all: %s\n", sq->use_all ? "true" : "false" );
PRINT( "\tuse_distinct: %s\n", sq->use_distinct ? "true" : "false" );
} else
osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
"Unable to build a query for id = %d", query_id ));
} else {
sqlAddMsg( state, "Stored query not found for id %d", query_id );
}
dbi_result_free( result );
} else {
const char* msg;
int errnum = dbi_conn_error( state->dbhandle, &msg );
osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
"Unable to query query.stored_query table: #%d %s",
errnum, msg ? msg : "No description available" ));
}
pop_id( &state->query_stack );
return sq;
}
void db_create_pl_ch_privileges(struct config *c, struct server *s)
{
dbi_result res;
int flags;
size_t iter, iter2;
int reg_id;
struct channel *ch;
struct registration *reg;
struct player_channel_privilege *tmp_priv;
char *q = "SELECT * FROM player_channel_privileges WHERE channel_id = %i;";
logger(LOG_INFO, "Reading player channel privileges...");
ar_each(struct channel *, ch, iter, s->chans)
if (!(ch->flags & CHANNEL_FLAG_UNREGISTERED)) {
res = dbi_conn_queryf(c->conn, q, ch->db_id);
if (res) {
while (dbi_result_next_row(res)) {
tmp_priv = new_player_channel_privilege();
tmp_priv->ch = ch;
flags = 0;
if (dbi_result_get_uint(res, "channel_admin"))
flags |= CHANNEL_PRIV_CHANADMIN;
if (dbi_result_get_uint(res, "operator"))
flags |= CHANNEL_PRIV_OP;
if (dbi_result_get_uint(res, "voice"))
flags |= CHANNEL_PRIV_VOICE;
if (dbi_result_get_uint(res, "auto_operator"))
flags |= CHANNEL_PRIV_AUTOOP;
if (dbi_result_get_uint(res, "auto_voice"))
flags |= CHANNEL_PRIV_AUTOVOICE;
tmp_priv->flags = flags;
tmp_priv->reg = PL_CH_PRIV_REGISTERED;
reg_id = dbi_result_get_uint(res, "player_id");
ar_each(struct registration *, reg, iter2, s->regs)
if (reg->db_id == reg_id)
tmp_priv->pl_or_reg.reg = reg;
ar_end_each;
if (tmp_priv->pl_or_reg.reg != NULL)
add_player_channel_privilege(ch, tmp_priv);
else
free(tmp_priv);
}
dbi_result_free(res);
} else {
template<> void
GncDbiProviderImpl<DbType::DBI_MYSQL>::drop_index (dbi_conn conn, const std::string& index)
{
auto sep = index.find(' ', 0);
if (index.find(' ', sep + 1) != std::string::npos)
{
PWARN("Drop index error: invalid MySQL index format (<index> <table>): %s",
index.c_str());
return;
}
auto result = dbi_conn_queryf (conn, "DROP INDEX %s ON %s",
index.substr(0, sep).c_str(),
index.substr(sep + 1).c_str());
if (result)
dbi_result_free (result);
}
static SelectItem* getSelectList( BuildSQLState* state, int query_id ) {
SelectItem* select_list = NULL;
// The ORDER BY is in descending order so that we can build the list by adding to
// the head, and it will wind up in the right order.
dbi_result result = dbi_conn_queryf( state->dbhandle,
"SELECT id, stored_query, seq_no, expression, column_alias, grouped_by "
"FROM query.select_item WHERE stored_query = %d ORDER BY seq_no DESC", query_id );
if( result ) {
if( dbi_result_first_row( result ) ) {
while( 1 ) {
SelectItem* item = constructSelectItem( state, result );
if( item ) {
PRINT( "Found a SELECT item\n" );
PRINT( "\tid: %d\n", item->id );
PRINT( "\tstored_query_id: %d\n", item->stored_query_id );
PRINT( "\tseq_no: %d\n", item->seq_no );
PRINT( "\tcolumn_alias: %s\n",
item->column_alias ? item->column_alias : "(none)" );
PRINT( "\tgrouped_by: %d\n", item->grouped_by );
item->next = select_list;
select_list = item;
} else {
osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
"Unable to build select list for query id #%d", query_id ));
selectListFree( select_list );
select_list = NULL;
break;
}
if( !dbi_result_next_row( result ) )
break;
};
}
} else {
const char* msg;
int errnum = dbi_conn_error( state->dbhandle, &msg );
osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
"Unable to query query.select_list table: #%d %s",
errnum, msg ? msg : "No description available" ));
}
return select_list;
}
static Expression* getExpression( BuildSQLState* state, int id ) {
// Check the stack to see if the current expression is nested inside itself. If it is,
// then abort in order to avoid infinite recursion. If it isn't, then add it to the
// stack. (Make sure to pop it off the stack before returning.)
if( searchIdStack( state->expr_stack, id, NULL )) {
osrfLogWarning( OSRF_LOG_MARK, sqlAddMsg( state,
"Infinite recursion detected; expression # %d is nested within itself", id ));
state->error = 1;
return NULL;
} else
push_id( &state->expr_stack, id, NULL );
Expression* exp = NULL;
dbi_result result = dbi_conn_queryf( state->dbhandle,
"SELECT id, type, parenthesize, parent_expr, seq_no, literal, table_alias, "
"column_name, left_operand, operator, right_operand, function_id, subquery, cast_type "
"FROM query.expression WHERE id = %d;", id );
if( result ) {
if( dbi_result_first_row( result ) ) {
exp = constructExpression( state, result );
if( exp ) {
PRINT( "Got an expression\n" );
PRINT( "\tid = %d\n", exp->id );
PRINT( "\ttype = %d\n", exp->type );
PRINT( "\tparenthesize = %d\n", exp->parenthesize );
PRINT( "\tcolumn_name = %s\n", exp->column_name ? exp->column_name : "(none)" );
} else
osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
"Unable to construct an Expression for id = %d", id ));
}
} else {
const char* msg;
int errnum = dbi_conn_error( state->dbhandle, &msg );
osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
"Unable to query query.expression table: #%d %s",
errnum, msg ? msg : "No description available" ));
}
pop_id( &state->expr_stack );
return exp;
}
int swd::database::save_request(const int& profile_id, const std::string& caller,
const std::string& resource, const int& mode, const std::string& client_ip,
const int& total_integrity_rules) {
std::stringstream log_message;
log_message << "Save request -> profile: " << profile_id
<< "; caller: " << caller << "; resource: " << resource
<< "; mode: " << mode << "; client_ip: " << client_ip;
swd::log::i()->send(swd::notice, log_message.str());
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *caller_esc = strdup(caller.c_str());
dbi_conn_quote_string(conn_, &caller_esc);
char *resource_esc = strdup(resource.c_str());
dbi_conn_quote_string(conn_, &resource_esc);
char *client_ip_esc = strdup(client_ip.c_str());
dbi_conn_quote_string(conn_, &client_ip_esc);
dbi_result res = dbi_conn_queryf(conn_, "INSERT INTO requests (profile_id, "
"caller, resource, mode, client_ip, total_integrity_rules) VALUES (%i, %s, "
"%s, %i, %s, %i)", profile_id, caller_esc, resource_esc, mode, client_ip_esc,
total_integrity_rules);
free(caller_esc);
free(resource_esc);
free(client_ip_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute request query");
}
int id = dbi_conn_sequence_last(conn_, "requests_id_seq");
dbi_result_free(res);
return id;
}
/**
@brief Build a list of joined relations.
@param state Pointer to the query-building context.
@param id ID of the parent relation.
@return A pointer to the first in a linked list of FromRelations, if there are any; or
NULL if there aren't any, or in case of an error.
Look for relations joined directly to the parent relation, and make a list of them.
*/
static FromRelation* getJoinList( BuildSQLState* state, int id ) {
FromRelation* join_list = NULL;
// The ORDER BY is in descending order so that we can build the list by adding to
// the head, and it will wind up in the right order.
dbi_result result = dbi_conn_queryf( state->dbhandle,
"SELECT id, type, table_name, class_name, subquery, function_call, "
"table_alias, parent_relation, seq_no, join_type, on_clause "
"FROM query.from_relation WHERE parent_relation = %d ORDER BY seq_no DESC", id );
if( result ) {
if( dbi_result_first_row( result ) ) {
while( 1 ) {
FromRelation* relation = constructFromRelation( state, result );
if( relation ) {
PRINT( "Found a joined relation\n" );
PRINT( "\tjoin_type: %d\n", relation->join_type );
PRINT( "\ttable_name: %s\n", relation->table_name );
relation->next = join_list;
join_list = relation;
} else {
osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
"Unable to build join list for from relation id #%d", id ));
joinListFree( join_list );
join_list = NULL;
break;
}
if( !dbi_result_next_row( result ) )
break;
};
}
} else {
const char* msg;
int errnum = dbi_conn_error( state->dbhandle, &msg );
osrfLogError( OSRF_LOG_MARK, sqlAddMsg( state,
"Unable to query query.from_relation table for join list: #%d %s",
errnum, msg ? msg : "No description available" ));
}
return join_list;
}
swd::blacklist_rules swd::database::get_blacklist_rules(const int& profile_id,
const std::string& caller, const std::string& path) {
swd::log::i()->send(swd::notice, "Get blacklist rules from db");
ensure_connection();
boost::unique_lock<boost::mutex> scoped_lock(dbi_mutex_);
char *caller_esc = strdup(caller.c_str());
dbi_conn_quote_string(conn_, &caller_esc);
char *path_esc = strdup(path.c_str());
dbi_conn_quote_string(conn_, &path_esc);
dbi_result res = dbi_conn_queryf(conn_, "SELECT r.id, r.path, r.threshold "
"FROM blacklist_rules AS r WHERE r.profile_id = %i AND %s LIKE "
"prepare_wildcard(r.caller) AND %s LIKE prepare_wildcard(r.path) AND "
"r.status = %i", profile_id, caller_esc, path_esc, STATUS_ACTIVATED);
free(caller_esc);
free(path_esc);
if (!res) {
throw swd::exceptions::database_exception("Can't execute blacklist_rules query");
}
swd::blacklist_rules rules;
while (dbi_result_next_row(res)) {
swd::blacklist_rule_ptr rule(new swd::blacklist_rule());
rule->set_id(dbi_result_get_uint(res, "id"));
rule->set_threshold(dbi_result_get_uint(res, "threshold"));
rules.push_back(rule);
}
dbi_result_free(res);
return rules;
}
