void DBManager::update_processing(const int& job_id) {
char *stmt_str;
stmt_str = "UPDATE wkdd_ldp_jobs SET status='processing' WHERE id = ?";
MYSQL_BIND param[1];
int my_job_id;
if (mysql_stmt_prepare(m_stmt, stmt_str, strlen(stmt_str)) != 0) {
print_stmt_error("could not prepare update statement");
return;
}
// zero the parameter structures, and then perform all parameter initialization that is constant and does not change for each row
memset((void *)param, 0, sizeof(param));
// set up my_id parameter
param[0].buffer_type = MYSQL_TYPE_LONG;
param[0].buffer = (void *) &my_job_id;
param[0].is_unsigned = 0;
param[0].is_null = 0;
// buffer length, length need not be set
if (mysql_stmt_bind_param(m_stmt, param) != 0) {
print_stmt_error("could not bind parameters for update");
return;
}
my_job_id = job_id;
if (mysql_stmt_execute(m_stmt) != 0) {
print_stmt_error("could not execute statement");
return;
}
}
std::vector<int> DBManager::check_jobs() {
std::vector<int> job_ids;
char *stmt_str = "SELECT id FROM wkdd_ldp_jobs WHERE status = 'pending' ORDER BY start_time ASC";
MYSQL_BIND result[1];
int my_job_id;
if (mysql_stmt_prepare(m_stmt, stmt_str, strlen(stmt_str)) != 0) {
print_stmt_error("could not prepare select statement");
return job_ids;
}
if (mysql_stmt_field_count(m_stmt) != 1) {
print_stmt_error("unexpected column count from select");
return job_ids;
}
// initialize result column structures
memset((void *)result, 0, sizeof(result)); // zero the structures
// set up my_id parameter
result[0].buffer_type = MYSQL_TYPE_LONG;
result[0].buffer = (void *) &my_job_id;
result[0].is_unsigned = 0;
result[0].is_null = 0;
if (mysql_stmt_bind_result(m_stmt, result) != 0) {
print_stmt_error("could not bind parameters for select");
return job_ids;
}
if (mysql_stmt_execute(m_stmt) != 0) {
print_stmt_error("could not execute select");
return job_ids;
}
// fetch result set into client memory; this is optional, but it allows mysql_stmt_num_rows() to be called to determine the number of rows in the result set
if (mysql_stmt_store_result(m_stmt) != 0) {
print_stmt_error("could not buffer result set");
return job_ids;
}
while (mysql_stmt_fetch(m_stmt) == 0) { // fetch each row
// store into vector
job_ids.push_back(my_job_id);
}
// deallocate result set
mysql_stmt_free_result(m_stmt);
return job_ids;
}
/* #@ _PROCESS_CALL_RESULT_ */
static void process_call_result (MYSQL *conn, MYSQL_STMT *stmt)
{
int status;
int num_cols;
/*
* For each result, check number of columns. If none, the result is
* the final status packet and there is nothing to do. Otherwise,
* fetch the result set.
*/
do {
if ((num_cols = mysql_stmt_field_count (stmt)) > 0)
{
/* announce whether result set contains parameters or data set */
if (conn->server_status & SERVER_PS_OUT_PARAMS)
printf ("OUT/INOUT parameter values:\n");
else
printf ("Statement result set values:\n");
if (process_result_set (stmt, num_cols))
break; /* some error occurred */
}
/* status is -1 = done, 0 = more results, >0 = error */
status = mysql_stmt_next_result (stmt);
if (status > 0)
print_stmt_error (stmt, "Error checking for next result");
} while (status == 0);
}
/* #@ _EXEC_PREPARED_CALL_ */
static int exec_prepared_call (MYSQL_STMT *stmt)
{
MYSQL_BIND params[3]; /* parameter buffers */
int int_data[3]; /* parameter values */
int i;
/* prepare CALL statement */
if (mysql_stmt_prepare (stmt, "CALL grade_event_stats(?, ?, ?)", 31))
{
print_stmt_error (stmt, "Cannot prepare statement");
return (1);
}
/* initialize parameter structures and bind to statement */
memset (params, 0, sizeof (params));
for (i = 0; i < 3; ++i)
{
params[i].buffer_type = MYSQL_TYPE_LONG;
params[i].buffer = (char *) &int_data[i];
params[i].length = 0;
params[i].is_null = 0;
}
if (mysql_stmt_bind_param (stmt, params))
{
print_stmt_error (stmt, "Cannot bind parameters");
return (1);
}
/* assign parameter values and execute statement */
int_data[0]= 4; /* p_event_id */
int_data[1]= 0; /* p_min (OUT param; initial value ignored by procedure */
int_data[2]= 0; /* p_min (OUT param; initial value ignored by procedure */
if (mysql_stmt_execute (stmt))
{
print_stmt_error (stmt, "Cannot execute statement");
return (1);
}
return (0);
}
void DBManager::init(const int &initial) {
if (initial == 1) {
// call to initialize MySQL library
if (mysql_library_init(0, NULL, NULL)) {
fprintf(stderr, "could not initialize MySQL library\n");
exit(1);
}
}
// Connect to database
// NOTE: mysql_init() has the side effect of calling mysql_thread_init()
m_conn = mysql_init(NULL);
if (!mysql_real_connect(m_conn, m_server, m_user, m_password, m_database, 0, NULL, 0)) {
fprintf(stderr, "%s\n", mysql_error(m_conn));
exit(1);
}
m_stmt = mysql_stmt_init(m_conn); // allocate statement handler
if (m_stmt == NULL) {
print_stmt_error("could not prepare statement");
exit(1);
}
}
void DBManager::update_completed_type134(const int& job_id, const std::string& result, const int& type) {
char *stmt_str;
if (type == 1)
stmt_str = "UPDATE wkdd_ldp_jobs SET top_cui = ?, status = 'complete', end_time = ? WHERE id = ?";
else if (type == 3)
stmt_str = "UPDATE wkdd_ldp_jobs SET start_cui_option = ?, status = 'complete', end_time = ? WHERE id = ?";
else if (type == 4)
stmt_str = "UPDATE wkdd_ldp_jobs SET end_cui_option = ?, status = 'complete', end_time = ? WHERE id = ?";
MYSQL_BIND param[3];
int my_job_id;
char my_cui[result.length()];
unsigned long my_cui_length;
MYSQL_TIME my_datetime;
time_t clock;
struct tm *cur_time;
if (mysql_stmt_prepare(m_stmt, stmt_str, strlen(stmt_str)) != 0) {
print_stmt_error("could not prepare update statement");
return;
}
// zero the parameter structures, and then perform all parameter initialization that is constant and does not change for each row
memset((void *)param, 0, sizeof(param));
// set up my_cui parameter
param[0].buffer_type = MYSQL_TYPE_STRING;
param[0].buffer = (void *) &my_cui;
param[0].buffer_length = sizeof(my_cui);
param[0].length = &my_cui_length;
param[0].is_null = 0;
// set up my_datetime
param[1].buffer_type = MYSQL_TYPE_DATETIME;
param[1].buffer = (void *) &my_datetime;
param[1].is_null = 0;
// set up my_id parameter
param[2].buffer_type = MYSQL_TYPE_LONG;
param[2].buffer = (void *) &my_job_id;
param[2].is_unsigned = 0;
param[2].is_null = 0;
if (mysql_stmt_bind_param(m_stmt, param) != 0) {
print_stmt_error("could not bind parameters for update");
return;
}
// get current time
(void)time (&clock);
// set variables
my_job_id = job_id;
(void) strcpy(my_cui, result.c_str());
my_cui_length = strlen(my_cui);
cur_time = localtime(&clock);
my_datetime.year = cur_time->tm_year + 1900;
my_datetime.month = cur_time->tm_mon + 1;
my_datetime.day = cur_time->tm_mday;
my_datetime.hour = cur_time->tm_hour;
my_datetime.minute = cur_time->tm_min;
my_datetime.second = cur_time->tm_sec;
my_datetime.second_part = 0;
my_datetime.neg = 0;
if (mysql_stmt_execute(m_stmt) != 0) {
print_stmt_error("could not execute statement");
return;
}
}
DBJob DBManager::get_job(const int& id) {
DBJob data;
char *stmt_str = "SELECT term, type FROM wkdd_ldp_jobs WHERE id = ?";
MYSQL_BIND param[1];
MYSQL_BIND result[2];
int my_job_id;
int my_type;
char my_term[2000];
unsigned long my_term_length;
if (mysql_stmt_prepare(m_stmt, stmt_str, strlen(stmt_str)) != 0) {
print_stmt_error("could not prepare select statement");
return data;
}
if (mysql_stmt_field_count(m_stmt) != 2) {
print_stmt_error("unexpected column count from select");
return data;
}
// initialize result column structures
memset((void *)param, 0, sizeof(param));
memset((void *)result, 0, sizeof(result)); // zero the structures
// set up my_id parameter
param[0].buffer_type = MYSQL_TYPE_LONG;
param[0].buffer = (void *) &my_job_id;
param[0].is_unsigned = 0;
param[0].is_null = 0;
// set up my_term parameter
result[0].buffer_type = MYSQL_TYPE_STRING;
result[0].buffer = (void *) &my_term;
result[0].buffer_length = sizeof(my_term);
result[0].length = &my_term_length;
result[0].is_null = 0;
// set up my_type parameter
result[1].buffer_type = MYSQL_TYPE_LONG;
result[1].buffer = (void *) &my_type;
result[1].is_unsigned = 0;
result[1].is_null = 0;
if (mysql_stmt_bind_param(m_stmt, param) != 0) {
print_stmt_error("could not bind parameters for select");
return data;
}
if (mysql_stmt_bind_result(m_stmt, result) != 0) {
print_stmt_error("could not bind parameters for select");
return data;
}
// set variable
my_job_id = id;
if (mysql_stmt_execute(m_stmt) != 0) {
print_stmt_error("could not execute select");
return data;
}
// fetch result set into client memory; this is optional, but it allows mysql_stmt_num_rows() to be called to determine the number of rows in the result set
if (mysql_stmt_store_result(m_stmt) != 0) {
print_stmt_error("could not buffer result set");
return data;
}
mysql_stmt_fetch(m_stmt); // fetch the single result
data = DBJob(my_term, my_type);
// deallocate result set
mysql_stmt_free_result(m_stmt);
return data;
}
/* #@ _PROCESS_RESULT_SET_ */
static int process_result_set (MYSQL_STMT *stmt, int num_cols)
{
MYSQL_FIELD *fields; /* pointer to result set metadata */
MYSQL_BIND *params; /* pointer to output buffers */
int int_data[3]; /* output parameter values */
my_bool is_null[3]; /* output parameter nullability */
int i;
MYSQL_RES *metadata = mysql_stmt_result_metadata (stmt);
if (metadata == NULL)
{
print_stmt_error (stmt, "Cannot get statement result metadata");
return (1);
}
fields = mysql_fetch_fields (metadata);
params = (MYSQL_BIND *) malloc (sizeof (MYSQL_BIND) * num_cols);
if (!params)
{
print_stmt_error (NULL, "Cannot allocate output parameters");
return (1);
}
/* initialize parameter structures and bind to statement */
memset (params, 0, sizeof (MYSQL_BIND) * num_cols);
for (i = 0; i < num_cols; ++i)
{
params[i].buffer_type = fields[i].type;
params[i].is_null = &is_null[i];
if (fields[i].type != MYSQL_TYPE_LONG)
{
fprintf (stderr, "ERROR: unexpected type: %d.\n", fields[i].type);
return (1);
}
params[i].buffer = (char *) &(int_data[i]);
params[i].buffer_length = sizeof (int_data);
}
mysql_free_result (metadata); /* done with metadata, free it */
if (mysql_stmt_bind_result (stmt, params))
{
print_stmt_error (stmt, "Cannot bind result to output buffers");
return (1);
}
/* retrieve result set rows, display contents */
while (1)
{
int status = mysql_stmt_fetch (stmt);
if (status == 1 || status == MYSQL_NO_DATA)
break; /* no more rows */
for (i = 0; i < num_cols; ++i)
{
printf (" val[%d] = ", i+1);
if (params[i].buffer_type != MYSQL_TYPE_LONG)
printf (" unexpected type (%d)\n", params[i].buffer_type);
else
{
if (*params[i].is_null)
printf ("NULL;");
else
printf ("%ld;", (long) *((int *) params[i].buffer));
}
}
printf ("\n");
}
free (params); /* done with output buffers, free them */
return (0);
}
void insert_objects(MYSQL* conn, const char* table_name, time_t epoch, uint index, std::vector<Libpair>& lddA,
std::string& syshost, Table& rmapT)
{
//************************************************************
// build SELECT obj_id INTO xalt_object stmt
//************************************************************
const char* stmt_sql_s = "SELECT obj_id FROM xalt_object WHERE hash_id=? AND object_path=? AND syshost=? limit 1";
MYSQL_STMT *stmt_s = mysql_stmt_init(conn);
if (!stmt_s)
{
print_stmt_error(stmt_s, "mysql_stmt_init(), out of memmory",__FILE__,__LINE__);
exit(1);
}
if (mysql_stmt_prepare(stmt_s, stmt_sql_s, strlen(stmt_sql_s)))
{
print_stmt_error(stmt_s, "Could not prepare stmt_s for select obj_id",__FILE__,__LINE__);
exit(1);
}
MYSQL_BIND param_s[3], result_s[1];
memset((void *) param_s, 0, sizeof(param_s));
memset((void *) result_s, 0, sizeof(result_s));
const int hash_id_sz = 41;
char hash_id[hash_id_sz];
const int objSz = 2048;
char object_path[objSz];
// STRING PARAM_S[0] hash_id;
std::string::size_type len_hash_id = 0; // set length later in loop.
param_s[0].buffer_type = MYSQL_TYPE_STRING;
param_s[0].buffer = (void *) &hash_id[0];
param_s[0].buffer_length = hash_id_sz;
param_s[0].length = &len_hash_id;
// STRING PARAM_S[1] object_path
std::string::size_type len_object_path = 0; // set length later in loop.
param_s[1].buffer_type = MYSQL_TYPE_STRING;
param_s[1].buffer = (void *) &object_path[0];
param_s[1].buffer_length = objSz;
param_s[1].length = &len_object_path;
// STRING PARAM_S[2] syshost
std::string::size_type len_syshost = syshost.size();
param_s[2].buffer_type = MYSQL_TYPE_STRING;
param_s[2].buffer = (void *) syshost.c_str();
param_s[2].buffer_length = syshost.capacity();
param_s[2].length = &len_syshost;
if (mysql_stmt_bind_param(stmt_s, param_s))
{
print_stmt_error(stmt_s, "Could not bind paramaters for selecting obj_id(1)",__FILE__,__LINE__);
exit(1);
}
// UNSIGNED INT RESULT_S obj_id;
uint obj_id;
result_s[0].buffer_type = MYSQL_TYPE_LONG;
result_s[0].buffer = (void *) &obj_id;
result_s[0].is_unsigned = 1;
result_s[0].length = 0;
if (mysql_stmt_bind_result(stmt_s, result_s))
{
print_stmt_error(stmt_s, "Could not bind paramaters for selecting obj_id(2)",__FILE__,__LINE__);
exit(1);
}
//************************************************************
// build INSERT into xalt_object stmt
//************************************************************
const char* stmt_sql_i = "INSERT INTO xalt_object VALUES (NULL,?,?,?,?,?,?)";
MYSQL_STMT *stmt_i = mysql_stmt_init(conn);
if (!stmt_i)
{
print_stmt_error(stmt_i, "mysql_stmt_init(), out of memmory(2)",__FILE__,__LINE__);
exit(1);
}
if (mysql_stmt_prepare(stmt_i, stmt_sql_i, strlen(stmt_sql_i)))
{
print_stmt_error(stmt_i, "Could not prepare stmt_i for insert into xalt_object",__FILE__,__LINE__);
exit(1);
}
MYSQL_BIND param_i[6];
memset((void *) param_i, 0, sizeof(param_i));
// STRING PARAM_I[0] object_path
param_i[0].buffer_type = MYSQL_TYPE_STRING;
param_i[0].buffer = (void *) &object_path[0];
param_i[0].buffer_length = objSz;
param_i[0].length = &len_object_path;
// STRING PARAM_I[1] syshost
param_i[1].buffer_type = MYSQL_TYPE_STRING;
param_i[1].buffer = (void *) syshost.c_str();
param_i[1].buffer_length = syshost.capacity();
param_i[1].length = &len_syshost;
// STRING PARAM_I[2] hash_id
param_i[2].buffer_type = MYSQL_TYPE_STRING;
param_i[2].buffer = (void *) &hash_id[0];
param_i[2].buffer_length = hash_id_sz;
param_i[2].length = &len_hash_id;
// STRING PARAM_I[3] module_name
const int module_name_sz = 64;
char module_name[module_name_sz];
std::string::size_type len_module_name = 0; // set length in loop
my_bool module_name_null_flag;
param_i[3].buffer_type = MYSQL_TYPE_STRING;
param_i[3].buffer = (void *) &module_name[0];
param_i[3].buffer_length = module_name_sz;
param_i[3].is_null = &module_name_null_flag;
param_i[3].length = &len_module_name;
// TIMESTAMP PARAM_I[4] timestamp
MYSQL_TIME my_datetime;
time_t clock;
(void ) time(&clock);
struct tm* curr_time = localtime(&clock);
my_datetime.year = curr_time->tm_year + 1900;
my_datetime.month = curr_time->tm_mon + 1;
my_datetime.day = curr_time->tm_mday;
my_datetime.hour = curr_time->tm_hour;
my_datetime.minute = curr_time->tm_min;
my_datetime.second = curr_time->tm_sec;
my_datetime.second_part = 0;
param_i[4].buffer_type = MYSQL_TYPE_DATETIME;
param_i[4].buffer = &my_datetime;
// STRING PARAM_I[5] lib_type
const int lib_type_sz = 3;
char lib_type[lib_type_sz];
std::string::size_type len_lib_type = 0; // set length in loop.
param_i[5].buffer_type = MYSQL_TYPE_STRING;
param_i[5].buffer = (void *) &lib_type[0];
param_i[5].buffer_length = lib_type_sz;
param_i[5].length = &len_lib_type;
if (mysql_stmt_bind_param(stmt_i, param_i))
{
print_stmt_error(stmt_i, "Could not bind paramaters for inserting into xalt_object",__FILE__,__LINE__);
exit(1);
}
//************************************************************
// "INSERT into <TABLE_NAME> VALUES (NULL, ?, ?, ?)"
//************************************************************
std::string s2("INSERT INTO ");
s2.append(table_name);
s2.append(" VALUES (NULL,?,?,?)");
const char* stmt_sql_ii = s2.c_str();
MYSQL_STMT *stmt_ii = mysql_stmt_init(conn);
if (!stmt_ii)
{
print_stmt_error(stmt_ii, "mysql_stmt_init(), out of memmory(3)",__FILE__,__LINE__);
exit(1);
}
if (mysql_stmt_prepare(stmt_ii, stmt_sql_ii, strlen(stmt_sql_ii)))
{
print_stmt_error(stmt_ii, "Could not prepare stmt_ii for insert into <table_name>",__FILE__,__LINE__);
exit(1);
}
MYSQL_BIND param_ii[3];
memset((void *) param_ii, 0, sizeof(param_ii));
// UINT PARAM_II[0] obj_id
param_ii[0].buffer_type = MYSQL_TYPE_LONG;
param_ii[0].buffer = (void *) &obj_id;
param_ii[0].buffer_length = 0;
param_ii[0].is_unsigned = 1;
// UINT PARAM_II[1] index
param_ii[1].buffer_type = MYSQL_TYPE_LONG;
param_ii[1].buffer = (void *) &index;
param_ii[1].buffer_length = 0;
param_ii[1].is_unsigned = 1;
// DATE PARAM_II[2] date
MYSQL_TIME my_date;
curr_time = localtime(&epoch);
my_date.year = curr_time->tm_year + 1900;
my_date.month = curr_time->tm_mon + 1;
my_date.day = curr_time->tm_mday;
my_date.hour = 0;
my_date.minute = 0;
my_date.second = 0;
my_date.second_part = 0;
param_ii[2].buffer_type = MYSQL_TYPE_DATE;
param_ii[2].buffer = &my_date;
if (mysql_stmt_bind_param(stmt_ii, param_ii))
{
print_stmt_error(stmt_ii, "Could not bind paramaters for inserting into xalt_object",__FILE__,__LINE__);
exit(1);
}
for ( auto const & it : lddA)
{
len_object_path = it.lib.size();
strcpy(object_path, it.lib.c_str());
len_hash_id = it.sha1.size();
strcpy(hash_id, it.sha1.c_str());
// "SELECT obj_id ..."
if (mysql_stmt_execute(stmt_s))
{
print_stmt_error(stmt_s, "Could not execute stmt for selecting obj_id",__FILE__,__LINE__);
exit(1);
}
if (mysql_stmt_store_result(stmt_s))
{
print_stmt_error(stmt_s, "Could not mysql_stmt_store_result() selecting obj_id",__FILE__,__LINE__);
exit(1);
}
// mysql_stmt_fetch(stmt_s) will return 0 if successful. That means it found obj_id
if (mysql_stmt_fetch(stmt_s) != 0)
{
// If here then the object is NOT in the db so store it and compute obj_id
if (path2module(object_path, rmapT, module_name, module_name_sz))
{
module_name_null_flag = 0;
len_module_name = strlen(module_name);
}
else
{
module_name_null_flag = 1;
len_module_name = 0;
}
object_type(object_path, &lib_type[0]);
len_lib_type = strlen(lib_type);
// "INSERT INTO xalt_object ..."
if (mysql_stmt_execute(stmt_i))
{
print_stmt_error(stmt_i, "Could not execute stmt for inserting into xalt_object",__FILE__,__LINE__);
exit(1);
}
obj_id = (uint) mysql_stmt_insert_id(stmt_i);
}
// "INSERT INTO <table_name>"
if (mysql_stmt_execute(stmt_ii))
{
print_stmt_error(stmt_ii, "Could not execute stmt for inserting into <table_name>",__FILE__,__LINE__);
exit(1);
}
}
mysql_stmt_free_result(stmt_s);
if (mysql_stmt_close(stmt_s))
{
print_stmt_error(stmt_s, "Could not close stmt for selecting obj_id",__FILE__,__LINE__);
exit(1);
}
if (mysql_stmt_close(stmt_i))
{
print_stmt_error(stmt_i, "Could not close stmt for inserting into xalt_object",__FILE__,__LINE__);
exit(1);
}
if (mysql_stmt_close(stmt_ii))
{
print_stmt_error(stmt_ii, "Could not close stmt for inserting into <table_name>",__FILE__,__LINE__);
exit(1);
}
}
