void sqlbox_configure_mysql(Cfg* cfg)
{
CfgGroup *grp;
Octstr *sql;
if (!(grp = cfg_get_single_group(cfg, octstr_imm("sqlbox"))))
panic(0, "SQLBOX: MySQL: group 'sqlbox' is not specified!");
sqlbox_logtable = cfg_get(grp, octstr_imm("sql-log-table"));
if (sqlbox_logtable == NULL) {
panic(0, "No 'sql-log-table' not configured.");
}
sqlbox_insert_table = cfg_get(grp, octstr_imm("sql-insert-table"));
if (sqlbox_insert_table == NULL) {
panic(0, "No 'sql-insert-table' not configured.");
}
/* create send_sms && sent_sms tables if they do not exist */
sql = octstr_format(SQLBOX_MYSQL_CREATE_LOG_TABLE, sqlbox_logtable);
sql_update(sql);
octstr_destroy(sql);
sql = octstr_format(SQLBOX_MYSQL_CREATE_INSERT_TABLE, sqlbox_insert_table);
sql_update(sql);
octstr_destroy(sql);
/* end table creation */
}
void mssql_save_msg(Msg *msg, Octstr *momt /*, Octstr smsbox_id */)
{
Octstr *sql;
Octstr *stuffer[30];
int stuffcount = 0;
DBPoolConn *pc;
pc = dbpool_conn_consume(pool);
if (pc == NULL) {
error(0, "MSSql: DBPool Error!");
return;
}
sql = octstr_format(SQLBOX_MSSQL_INSERT_QUERY, sqlbox_logtable, st_str(momt), st_str(msg->sms.sender),
st_str(msg->sms.receiver), st_str(msg->sms.udhdata), st_str(msg->sms.msgdata), st_num(msg->sms.time),
st_str(msg->sms.smsc_id), st_str(msg->sms.service), st_str(msg->sms.account), st_num(msg->sms.sms_type),
st_num(msg->sms.mclass), st_num(msg->sms.mwi), st_num(msg->sms.coding), st_num(msg->sms.compress),
st_num(msg->sms.validity), st_num(msg->sms.deferred), st_num(msg->sms.dlr_mask), st_str(msg->sms.dlr_url),
st_num(msg->sms.pid), st_num(msg->sms.alt_dcs), st_num(msg->sms.rpi), st_str(msg->sms.charset),
st_str(msg->sms.boxc_id), st_str(msg->sms.binfo), st_str(msg->sms.meta_data));
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
sql_update(pc, sql, NULL);
while (stuffcount > 0) {
octstr_destroy(stuffer[--stuffcount]);
}
dbpool_conn_produce(pc);
octstr_destroy(sql);
}
void redis_save_msg(Msg *msg, Octstr *momt /*, Octstr smsbox_id */)
{
Octstr *sql, *jsonstr, *subst;
Octstr *stuffer[30];
json_t *msgjson, *root;
char *json;
int stuffcount = 0;
msgjson = json_object();
json_object_set_new(msgjson, "momt", json_string(octstr_get_cstr(st_str(momt))));
json_object_set_new(msgjson, "sender", json_string(octstr_get_cstr(st_str(msg->sms.sender))));
json_object_set_new(msgjson, "receiver", json_string(octstr_get_cstr(st_str(msg->sms.receiver))));
json_object_set_new(msgjson, "foreign_id", json_string(octstr_get_cstr(st_str(msg->sms.foreign_id))));
json_object_set_new(msgjson, "udhdata", json_string(octstr_get_cstr(st_str(msg->sms.udhdata))));
json_object_set_new(msgjson, "msgdata", json_string(octstr_get_cstr(st_str(msg->sms.msgdata))));
json_object_set_new(msgjson, "time", json_string(octstr_get_cstr(st_num(msg->sms.time))));
json_object_set_new(msgjson, "smsc_id", json_string(octstr_get_cstr(st_str(msg->sms.smsc_id))));
json_object_set_new(msgjson, "service", json_string(octstr_get_cstr(st_str(msg->sms.service))));
json_object_set_new(msgjson, "account", json_string(octstr_get_cstr(st_str(msg->sms.account))));
json_object_set_new(msgjson, "sms_type", json_string(octstr_get_cstr(st_num(msg->sms.sms_type))));
json_object_set_new(msgjson, "mclass", json_string(octstr_get_cstr(st_num(msg->sms.mclass))));
json_object_set_new(msgjson, "mwi", json_string(octstr_get_cstr(st_num(msg->sms.mwi))));
json_object_set_new(msgjson, "coding", json_string(octstr_get_cstr(st_num(msg->sms.coding))));
json_object_set_new(msgjson, "compress", json_string(octstr_get_cstr(st_num(msg->sms.compress))));
json_object_set_new(msgjson, "validity", json_string(octstr_get_cstr(st_num(msg->sms.validity))));
json_object_set_new(msgjson, "deferred", json_string(octstr_get_cstr(st_num(msg->sms.deferred))));
json_object_set_new(msgjson, "dlr_mask", json_string(octstr_get_cstr(st_num(msg->sms.dlr_mask))));
json_object_set_new(msgjson, "dlr_url", json_string(octstr_get_cstr(st_str(msg->sms.dlr_url))));
json_object_set_new(msgjson, "pid", json_string(octstr_get_cstr(st_num(msg->sms.pid))));
json_object_set_new(msgjson, "alt_dcs", json_string(octstr_get_cstr(st_num(msg->sms.alt_dcs))));
json_object_set_new(msgjson, "rpi", json_string(octstr_get_cstr(st_num(msg->sms.rpi))));
json_object_set_new(msgjson, "charset", json_string(octstr_get_cstr(st_str(msg->sms.charset))));
json_object_set_new(msgjson, "boxc_id", json_string(octstr_get_cstr(st_str(msg->sms.boxc_id))));
json_object_set_new(msgjson, "binfo", json_string(octstr_get_cstr(st_str(msg->sms.binfo))));
json_object_set_new(msgjson, "priority", json_string(octstr_get_cstr(st_num(msg->sms.priority))));
json_object_set_new(msgjson, "meta_data", json_string(octstr_get_cstr(st_str(msg->sms.meta_data))));
root = json_object();
json_object_set(root, "msg", msgjson);
json = json_dumps(root, JSON_COMPACT);
jsonstr = octstr_create(json);
subst = octstr_create("%s");
sql = octstr_format(SQLBOX_REDIS_QUEUE_PUSH, sqlbox_logtable, subst);
sql_update(sql, jsonstr);
octstr_destroy(sql);
octstr_destroy(subst);
octstr_destroy(jsonstr);
while (stuffcount > 0) {
octstr_destroy(stuffer[--stuffcount]);
}
json_decref(msgjson);
json_decref(root);
gw_free(json);
}
bool db_pgsql::rollback(void)
{
const char* sql = "rollback";
if (sql_update(sql) == false)
{
logger_error("%s error: %s", sql, get_error());
return false;
}
return true;
}
bool db_pgsql::begin_transaction(void)
{
const char* sql = "start transaction";
if (sql_update(sql) == false)
{
logger_error("%s error: %s", sql, get_error());
return false;
}
return true;
}
void
table_base::update_with_no_output(
const abstract_mapper_base &dest,
const Src &src,
const abstract_predicate &pred
) const {
_database.get_session()->exec(
*sql_update(dest, src, pred)
);
}
bool db_mysql::commit(void)
{
const char* sql = "commit";
if (sql_update(sql) == false)
{
logger_error("%s error: %s", sql, get_error());
return false;
}
return true;
}
void sqlbox_configure_mssql(Cfg* cfg)
{
DBPoolConn *pc;
CfgGroup *grp;
Octstr *sql;
if (!(grp = cfg_get_single_group(cfg, octstr_imm("sqlbox"))))
panic(0, "SQLBOX: MSSql: group 'sqlbox' is not specified!");
sqlbox_logtable = cfg_get(grp, octstr_imm("sql-log-table"));
if (sqlbox_logtable == NULL) {
panic(0, "Parameter 'sql-log-table' not configured.");
}
sqlbox_insert_table = cfg_get(grp, octstr_imm("sql-insert-table"));
if (sqlbox_insert_table == NULL) {
panic(0, "Parameter 'sql-insert-table' not configured.");
}
pc = dbpool_conn_consume(pool);
if (pc == NULL) {
error(0, "MSSql: DBPool Error!");
return;
}
/* create send_sms && sent_sms tables if they do not exist */
sql = octstr_format(SQLBOX_MSSQL_CREATE_LOG_TABLE, sqlbox_logtable, sqlbox_logtable);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
sql_update(pc, sql, NULL);
octstr_destroy(sql);
sql = octstr_format(SQLBOX_MSSQL_CREATE_INSERT_TABLE, sqlbox_insert_table, sqlbox_insert_table);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
sql_update(pc, sql, NULL);
octstr_destroy(sql);
dbpool_conn_produce(pc);
}
unique_ptr<row>
table_base::update_with_one_output(
const abstract_mapper_base &dest,
const Src &src,
const abstract_predicate &pred,
const abstract_mapper_base &returning
) const {
return _database.get_session()->exec_with_one_output(
*sql_update(dest, src, pred, &returning)
);
}
result_stream
table_base::update_with_stream_output(
const abstract_mapper_base &dest,
const Src &src,
const abstract_predicate &pred,
const abstract_mapper_base &returning,
uint32_t fetch_size
) const {
return _database.get_session()->exec_with_stream_output(
*sql_update(dest, src, pred, &returning),
fetch_size
);
}
void mysql_save_msg(Msg *msg, Octstr *momt /*, Octstr smsbox_id */)
{
Octstr *sql;
Octstr *stuffer[30];
int stuffcount = 0;
sql = octstr_format(SQLBOX_MYSQL_INSERT_QUERY, sqlbox_logtable, st_str(momt), st_str(msg->sms.sender),
st_str(msg->sms.receiver), st_str(msg->sms.udhdata), st_str(msg->sms.msgdata), st_num(msg->sms.time),
st_str(msg->sms.smsc_id), st_str(msg->sms.service), st_str(msg->sms.account), st_num(msg->sms.sms_type),
st_num(msg->sms.mclass), st_num(msg->sms.mwi), st_num(msg->sms.coding), st_num(msg->sms.compress),
st_num(msg->sms.validity), st_num(msg->sms.deferred), st_num(msg->sms.dlr_mask), st_str(msg->sms.dlr_url),
st_num(msg->sms.pid), st_num(msg->sms.alt_dcs), st_num(msg->sms.rpi), st_str(msg->sms.charset),
st_str(msg->sms.boxc_id), st_str(msg->sms.binfo), st_str(msg->sms.meta_data));
sql_update(sql);
while (stuffcount > 0) {
octstr_destroy(stuffer[--stuffcount]);
}
octstr_destroy(sql);
}
bool db_handle::exec_update(query& query)
{
return sql_update(query.to_string().c_str());
}
Msg *mssql_fetch_msg()
{
Msg *msg = NULL;
Octstr *sql, *delet, *id;
List *res, *row;
int ret;
DBPoolConn *pc;
pc = dbpool_conn_consume(pool);
if (pc == NULL) {
error(0, "MSSql: DBPool error!");
return;
}
sql = octstr_format(SQLBOX_MSSQL_SELECT_QUERY, sqlbox_insert_table);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
if (sql_select(pc, sql, NULL, &res) != 0) {
debug("sqlbox", 0, "SQL statement failed: %s", octstr_get_cstr(sql));
} else {
if (gwlist_len(res) > 0) {
row = gwlist_extract_first(res);
id = get_mssql_octstr_col(0);
/* save fields in this row as msg struct */
msg = msg_create(sms);
msg->sms.sender = get_mssql_octstr_col(2);
msg->sms.receiver = get_mssql_octstr_col(3);
msg->sms.udhdata = get_mssql_octstr_col(4);
msg->sms.msgdata = get_mssql_octstr_col(5);
msg->sms.time = get_mssql_long_col(6);
msg->sms.smsc_id = get_mssql_octstr_col(7);
msg->sms.service = get_mssql_octstr_col(8);
msg->sms.account = get_mssql_octstr_col(9);
/* msg->sms.id = get_mssql_long_col(10); */
msg->sms.sms_type = get_mssql_long_col(11);
msg->sms.mclass = get_mssql_long_col(12);
msg->sms.mwi = get_mssql_long_col(13);
msg->sms.coding = get_mssql_long_col(14);
msg->sms.compress = get_mssql_long_col(15);
msg->sms.validity = get_mssql_long_col(16);
msg->sms.deferred = get_mssql_long_col(17);
msg->sms.dlr_mask = get_mssql_long_col(18);
msg->sms.dlr_url = get_mssql_octstr_col(19);
msg->sms.pid = get_mssql_long_col(20);
msg->sms.alt_dcs = get_mssql_long_col(21);
msg->sms.rpi = get_mssql_long_col(22);
msg->sms.charset = get_mssql_octstr_col(23);
msg->sms.binfo = get_mssql_octstr_col(25);
msg->sms.meta_data = get_mssql_octstr_col(26);
if (gwlist_get(row,24) == NULL) {
msg->sms.boxc_id = octstr_duplicate(sqlbox_id);
}
else {
msg->sms.boxc_id = get_mssql_octstr_col(24);
}
/* delete current row */
delet = octstr_format(SQLBOX_MSSQL_DELETE_QUERY, sqlbox_insert_table, id);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(delet));
#endif
sql_update(pc, delet, NULL);
octstr_destroy(id);
octstr_destroy(delet);
gwlist_destroy(row, octstr_destroy_item);
}
gwlist_destroy(res, NULL);
}
dbpool_conn_produce(pc);
octstr_destroy(sql);
return msg;
}
Msg *redis_fetch_msg()
{
Msg *msg = NULL;
Octstr *sql, *delet, *subst, *jsonstr;
redisReply *res = NULL;
char *resjson;
json_t *root;
json_t *jsonmsg;
json_error_t *error = NULL;
if (sqlbox_inflight_table != NULL)
sql = octstr_format(SQLBOX_REDIS_QUEUE_POP_WITH_INFLIGHT, sqlbox_insert_table, sqlbox_inflight_table);
else
sql = octstr_format(SQLBOX_REDIS_QUEUE_POP, sqlbox_insert_table);
res = redis_select(sql);
if (res->type == REDIS_REPLY_ARRAY) {
resjson = res->element[1]->str; /* In-flight usage (BRPOPLPUSH) returns this */
}
else if (res->type == REDIS_REPLY_STRING) { /* Non in-flight usage (BRPOP) returns this */
resjson = res->str;
}
else if (res->type == REDIS_REPLY_NIL) { /* No messages queued - loop */
freeReplyObject(res);
return NULL;
}
else if (res->type == REDIS_REPLY_ERROR) {
warning(0, "REDIS command %s failed with error %s", octstr_get_cstr(sql), res->str);
freeReplyObject(res);
return NULL;
}
else
{
warning(0, "REDIS command %s return unknown status", octstr_get_cstr(sql));
freeReplyObject(res);
return NULL;
}
root = json_loads(resjson, 0, error);
if (!root) {
warning(0, "sqlbox: Invalid JSON in message retrieved from Redis. Skipping message.");
freeReplyObject(res);
return NULL;
}
jsonmsg = json_object_get(root, "msg");
if (!json_is_object(jsonmsg)) {
warning(0, "sqlbox: JSON does not include 'msg' root element. Skipping message.");
json_decref(root);
freeReplyObject(res);
return NULL;
}
/* save fields in this row as msg struct */
msg = msg_create(sms);
msg->sms.sender = octstr_null_create(json_string_value(json_object_get(jsonmsg,"sender")));
msg->sms.receiver = octstr_null_create(json_string_value(json_object_get(jsonmsg,"receiver")));
msg->sms.udhdata = octstr_null_create(json_string_value(json_object_get(jsonmsg,"udhdata")));
msg->sms.msgdata = octstr_null_create(json_string_value(json_object_get(jsonmsg,"msgdata")));
msg->sms.time = atol_null(json_string_value(json_object_get(jsonmsg,"time")));
msg->sms.smsc_id = octstr_null_create(json_string_value(json_object_get(jsonmsg,"smsc_id")));
msg->sms.service = octstr_null_create(json_string_value(json_object_get(jsonmsg,"service")));
msg->sms.account = octstr_null_create(json_string_value(json_object_get(jsonmsg,"account")));
msg->sms.sms_type = atol_null(json_string_value(json_object_get(jsonmsg,"sms_type")));
msg->sms.mclass = atol_null(json_string_value(json_object_get(jsonmsg,"mclass")));
msg->sms.mwi = atol_null(json_string_value(json_object_get(jsonmsg,"mwi")));
msg->sms.coding = atol_null(json_string_value(json_object_get(jsonmsg,"coding")));
msg->sms.compress = atol_null(json_string_value(json_object_get(jsonmsg,"compress")));
msg->sms.validity = atol_null(json_string_value(json_object_get(jsonmsg,"validity")));
msg->sms.deferred = atol_null(json_string_value(json_object_get(jsonmsg,"deferred")));
msg->sms.dlr_mask = atol_null(json_string_value(json_object_get(jsonmsg,"dlr_mask")));
msg->sms.dlr_url = octstr_null_create(json_string_value(json_object_get(jsonmsg,"dlr_url")));
msg->sms.pid = atol_null(json_string_value(json_object_get(jsonmsg,"pid")));
msg->sms.alt_dcs = atol_null(json_string_value(json_object_get(jsonmsg,"alt_dcs")));
msg->sms.rpi = atol_null(json_string_value(json_object_get(jsonmsg,"rpi")));
msg->sms.charset = octstr_null_create(json_string_value(json_object_get(jsonmsg,"charset")));
msg->sms.binfo = octstr_null_create(json_string_value(json_object_get(jsonmsg,"binfo")));
msg->sms.priority = atol_null(json_string_value(json_object_get(jsonmsg,"priority")));
msg->sms.meta_data = octstr_null_create(json_string_value(json_object_get(jsonmsg,"meta_data")));
if (json_string_value(json_object_get(jsonmsg,"boxc_id")) == NULL) {
msg->sms.boxc_id= octstr_duplicate(sqlbox_id);
}
else {
msg->sms.boxc_id= octstr_null_create(json_string_value(json_object_get(jsonmsg,"boxc_id")));
}
/* delete from inflight table. This shoudl really be done after the message has been queued to bearerbox */
if (sqlbox_inflight_table != NULL) {
subst = octstr_create("%s");
delet = octstr_format(SQLBOX_REDIS_DELETE, sqlbox_inflight_table, subst);
jsonstr = octstr_create(resjson);
sql_update(delet, jsonstr);
octstr_destroy(delet);
octstr_destroy(jsonstr);
}
json_decref(root);
freeReplyObject(res);
octstr_destroy(sql);
return msg;
}
void sqlbox_configure_oracle(Cfg* cfg)
{
DBPoolConn *pc;
CfgGroup *grp;
Octstr *sql;
if (!(grp = cfg_get_single_group(cfg, octstr_imm("sqlbox"))))
panic(0, "SQLBOX: Oracle: group 'sqlbox' is not specified!");
sqlbox_logtable = cfg_get(grp, octstr_imm("sql-log-table"));
if (sqlbox_logtable == NULL) {
panic(0, "Parameter 'sql-log-table' not configured.");
}
sqlbox_insert_table = cfg_get(grp, octstr_imm("sql-insert-table"));
if (sqlbox_insert_table == NULL) {
panic(0, "Parameter 'sql-insert-table' not configured.");
}
pc = dbpool_conn_consume(pool);
if (pc == NULL) {
error(0, "Oracle: DBPool Error!");
return;
}
/* create send_sms && sent_sms tables if they do not exist */
sql = octstr_format(SQLBOX_ORACLE_CREATE_LOG_TABLE, sqlbox_logtable, sqlbox_logtable);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
sql_update(pc, sql, NULL);
octstr_destroy(sql);
sql = octstr_format(SQLBOX_ORACLE_CREATE_INSERT_TABLE, sqlbox_insert_table, sqlbox_insert_table);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
sql_update(pc, sql, NULL);
octstr_destroy(sql);
/*
* Oracle implementation using a sequence and a trigger for auto_increment fields.
*/
sql = octstr_format(SQLBOX_ORACLE_CREATE_LOG_SEQUENCE, sqlbox_logtable);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
sql_update(pc, sql, NULL);
octstr_destroy(sql);
sql = octstr_format(SQLBOX_ORACLE_CREATE_INSERT_SEQUENCE, sqlbox_insert_table);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
sql_update(pc, sql, NULL);
octstr_destroy(sql);
sql = octstr_format(SQLBOX_ORACLE_CREATE_LOG_TRIGGER, sqlbox_logtable, sqlbox_logtable, sqlbox_logtable);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
sql_update(pc, sql, NULL);
octstr_destroy(sql);
sql = octstr_format(SQLBOX_ORACLE_CREATE_INSERT_TRIGGER, sqlbox_insert_table, sqlbox_insert_table, sqlbox_insert_table);
#if defined(SQLBOX_TRACE)
debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
#endif
sql_update(pc, sql, NULL);
octstr_destroy(sql);
/* end table creation */
dbpool_conn_produce(pc);
}