Discussion: Prepaid counting
"Rene Kluwen" <[email protected]>
| Newsgroups | gmane.comp.mobile.kannel.devel |
|---|---|
| Message-ID | <[email protected]> |
Suppose I add 2 tables to sqlbox: mysql> describe sms_users; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | binfo | varchar(100) | NO | PRI | NULL | | | balance | float(10,0) | NO | | 0 | | +---------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) And: mysql> describe sms_rates; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | prefix | varchar(100) | NO | PRI | | | | smsc | varchar(100) | NO | PRI | | | | rate | float(10,4) | NO | | 0.0000 | | | country | varchar(255) | YES | | | | +---------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) And suppose I match prefix/smsc against sms_rates and draw the variable "rate" from it. Next, I check the sms_users table with the binfo field (not sure if this is a good idea or not) and I withdraw "rate" from "balance" if "balance" is big enough. Once balance reaches 0, sqlbox will refuse to relay further messages to bearerbox. Attached is a patch that does the trick (for now, only mysql engines). Note: This code hasn't been tested yet and probably contains a bug or two. But I am posting it anyway to see what the almighty Kannel Developers have to say about it. This patch is especially useful if you want your clients to have access to an open smppbox with a limited amount of credits. But also it works for smsboxes and sqlboxes, as you please. I made a small patch to open smppbox that allows passing of binfo. == Rene
prepaid_1.patch
(application/octet-stream, 9 KB)
Index: sb-config.h.in
===================================================================
--- sb-config.h.in (revision 29)
+++ sb-config.h.in (working copy)
@@ -82,10 +82,6 @@
/* Define to 1 if you have the <unistd.h> header file. */
#undef HAVE_UNISTD_H
-/* Define to the sub-directory in which libtool stores uninstalled libraries.
- */
-#undef LT_OBJDIR
-
/* Name of package */
#undef PACKAGE
Index: gw/sqlbox_sdb.c
===================================================================
--- gw/sqlbox_sdb.c (revision 29)
+++ gw/sqlbox_sdb.c (working copy)
@@ -413,6 +413,7 @@
res->sql_leave = sdb_leave;
res->sql_fetch_msg = sdb_fetch_msg;
res->sql_save_msg = sdb_save_msg;
+ res->sql_check_balance = NULL;
return res;
}
#endif
Index: gw/sqlbox.c
===================================================================
--- gw/sqlbox.c (revision 29)
+++ gw/sqlbox.c (working copy)
@@ -86,6 +86,7 @@
static Octstr *box_allow_ip;
static Octstr *box_deny_ip;
static Octstr *global_sender;
+static int checkbalance;
#ifndef HAVE_MSSQL
#ifndef HAVE_MYSQL
@@ -149,6 +150,10 @@
return 0;
}
+#undef OCTSTR
+#undef SINGLE_GROUP
+#undef MULTI_GROUP
+
static int sqlbox_is_single_group(Octstr *query)
{
#define OCTSTR(name)
@@ -253,6 +258,7 @@
{
Boxc *conn = arg;
Msg *msg;
+ int hasbalance;
/* remove messages from socket until it is closed */
while (sqlbox_status != SQL_DEAD && conn->alive) {
@@ -266,13 +272,22 @@
break;
}
+ hasbalance = 1;
+
if (msg_type(msg) == sms) {
debug("sqlbox", 0, "smsbox_to_bearerbox: sms received");
- gw_sql_save_msg(msg, octstr_imm("MT"));
+ if (checkbalance && NULL != gw_sql_check_balance) {
+ hasbalance = gw_sql_check_balance(msg);
+ }
+ if (hasbalance) {
+ gw_sql_save_msg(msg, octstr_imm("MT"));
+ }
}
- send_msg(conn->bearerbox_connection, conn, msg);
+ if (hasbalance) {
+ send_msg(conn->bearerbox_connection, conn, msg);
+ }
/* if this is an identification message from an smsbox instance */
if (msg_type(msg) == admin && msg->admin.command == cmd_identify) {
/*
@@ -528,6 +543,7 @@
Boxc *boxc;
int fd;
Msg *msg;
+ int hasbalance;
boxc = gw_malloc(sizeof(Boxc));
boxc->bearerbox_connection = connect_to_bearerbox_real(bearerbox_host, bearerbox_port, bearerbox_port_ssl, NULL /* bb_our_host */);
@@ -546,12 +562,18 @@
identify_to_bearerbox(boxc);
while (sqlbox_status == SQL_RUNNING) {
+ hasbalance = 1;
if ((msg = gw_sql_fetch_msg()) != NULL) {
if (global_sender != NULL && (msg->sms.sender == NULL || octstr_len(msg->sms.sender) == 0)) {
msg->sms.sender = octstr_duplicate(global_sender);
}
- send_msg(boxc->bearerbox_connection, boxc, msg);
- gw_sql_save_msg(msg, octstr_imm("MT"));
+ if (checkbalance && NULL != gw_sql_check_balance) {
+ hasbalance = gw_sql_check_balance(msg);
+ }
+ if (hasbalance) {
+ send_msg(boxc->bearerbox_connection, boxc, msg);
+ gw_sql_save_msg(msg, octstr_imm("MT"));
+ }
}
else {
gwthread_sleep(SLEEP_BETWEEN_SELECTS);
@@ -658,6 +680,7 @@
bearerbox_port_ssl = 0;
logfile = NULL;
lvl = 0;
+ checkbalance = 0;
/*
* first we take the port number in bearerbox and other values from the
@@ -697,10 +720,17 @@
octstr_destroy(logfile);
}
+ cfg_get_bool(&checkbalance, grp, octstr_imm("check-balance"));
+
sql_type = sqlbox_init_sql(cfg);
if (sql_type == NULL) {
panic(0, "No proper SQL server defined.");
}
+
+ if (checkbalance && NULL == gw_sql_check_balance) {
+ panic(0, "Prepaid functionality not implemented for this db engine.");
+ }
+
gw_sql_enter(cfg);
sqlbox_status = SQL_RUNNING;
Index: gw/sqlbox_sqlite3.c
===================================================================
--- gw/sqlbox_sqlite3.c (revision 29)
+++ gw/sqlbox_sqlite3.c (working copy)
@@ -315,6 +315,7 @@
res->sql_leave = sqlite3_leave;
res->sql_fetch_msg = sqlite3_fetch_msg;
res->sql_save_msg = sqlite3_save_msg;
+ res->sql_check_balance = NULL;
return res;
}
#endif
Index: gw/sqlbox_pgsql.c
===================================================================
--- gw/sqlbox_pgsql.c (revision 29)
+++ gw/sqlbox_pgsql.c (working copy)
@@ -377,6 +377,7 @@
res->sql_leave = pgsql_leave;
res->sql_fetch_msg = pgsql_fetch_msg;
res->sql_save_msg = pgsql_save_msg;
+ res->sql_check_balance = NULL;
return res;
}
Index: gw/sqlbox-cfg.def
===================================================================
--- gw/sqlbox-cfg.def (revision 29)
+++ gw/sqlbox-cfg.def (working copy)
@@ -17,6 +17,7 @@
OCTSTR(log-file)
OCTSTR(log-level)
OCTSTR(bearerbox-port)
+ OCTSTR(check-balance)
OCTSTR(ssl-client-certkey-file)
OCTSTR(ssl-server-cert-file)
OCTSTR(ssl-server-key-file)
Index: gw/sqlbox_mssql.c
===================================================================
--- gw/sqlbox_mssql.c (revision 29)
+++ gw/sqlbox_mssql.c (working copy)
@@ -294,6 +294,7 @@
res->sql_leave = mssql_leave;
res->sql_fetch_msg = mssql_fetch_msg;
res->sql_save_msg = mssql_save_msg;
+ res->sql_check_balance = NULL;
return res;
}
#endif
Index: gw/sqlbox_sqlite.c
===================================================================
--- gw/sqlbox_sqlite.c (revision 29)
+++ gw/sqlbox_sqlite.c (working copy)
@@ -323,6 +323,7 @@
res->sql_leave = sqlite_leave;
res->sql_fetch_msg = sqlite_fetch_msg;
res->sql_save_msg = sqlite_save_msg;
+ res->sql_check_balance = NULL;
return res;
}
#endif
Index: gw/sqlbox_sql.h
===================================================================
--- gw/sqlbox_sql.h (revision 29)
+++ gw/sqlbox_sql.h (working copy)
@@ -16,6 +16,7 @@
void (*sql_leave) ();
Msg *(*sql_fetch_msg) ();
void (*sql_save_msg) (Msg *, Octstr *);
+ int (*sql_check_balance) (Msg *);
};
struct sqlbox_db_queries {
@@ -41,6 +42,7 @@
#define gw_sql_save_msg sql_type->sql_save_msg
#define gw_sql_enter sql_type->sql_enter
#define gw_sql_leave sql_type->sql_leave
+#define gw_sql_check_balance sql_type->sql_check_balance
/* Macro to run the queries to create tables */
#define sqlbox_run_query(query, table) \
Index: gw/sqlbox_oracle.c
===================================================================
--- gw/sqlbox_oracle.c (revision 29)
+++ gw/sqlbox_oracle.c (working copy)
@@ -315,6 +315,7 @@
res->sql_leave = oracle_leave;
res->sql_fetch_msg = oracle_fetch_msg;
res->sql_save_msg = oracle_save_msg;
+ res->sql_check_balance = NULL;
return res;
}
#endif
Index: gw/sqlbox_mysql.c
===================================================================
--- gw/sqlbox_mysql.c (revision 29)
+++ gw/sqlbox_mysql.c (working copy)
@@ -39,6 +39,31 @@
dbpool_conn_produce(pc);
}
+static int mysql_update_affected_rows(const Octstr *sql)
+{
+ int state, result;
+ DBPoolConn *pc;
+
+#if defined(SQLBOX_TRACE)
+ debug("SQLBOX", 0, "sql: %s", octstr_get_cstr(sql));
+#endif
+
+ pc = dbpool_conn_consume(pool);
+ if (pc == NULL) {
+ error(0, "MYSQL: Database pool got no connection! DB update failed!");
+ return;
+ }
+
+ state = mysql_query(pc->conn, octstr_get_cstr(sql));
+ result = mysql_affected_rows(pc->conn);
+ if (state != 0)
+ error(0, "MYSQL: %s", mysql_error(pc->conn));
+
+ dbpool_conn_produce(pc);
+
+ return result;
+}
+
static MYSQL_RES* mysql_select(const Octstr *sql)
{
int state;
@@ -212,6 +237,39 @@
octstr_destroy(sql);
}
+int mysql_check_balance(Msg *msg)
+{
+ Octstr *sql;
+ double rate;
+ int affected_rows;
+ MYSQL_RES *res;
+ MYSQL_ROW row;
+
+ if (NULL == msg->sms.smsc_id) {
+ sql = octstr_format("select rate from sms_rates where '%s' regexp prefix", octstr_get_cstr(msg->sms.receiver));
+ }
+ else {
+ sql = octstr_format("select rate from sms_rates where smsc='%s' and '%s' regexp prefix", octstr_get_cstr(msg->sms.smsc_id), octstr_get_cstr(msg->sms.receiver));
+ }
+ res = mysql_select(sql);
+ octstr_destroy(sql);
+ if (res == NULL) {
+ debug("sqlbox", 0, "SQL statement failed: %s", octstr_get_cstr(sql));
+ return 1;
+ }
+ if (mysql_num_rows(res) != 1) {
+ debug("sqlbox", 0, "SQL statement did not return exactly one row: %s", octstr_get_cstr(sql));
+ return 1;
+ }
+ row = mysql_fetch_row(res);
+ rate = atof(row[0]);
+ mysql_free_result(res);
+ sql = octstr_format("update sms_users set balance = balance - %f where binfo='%s' and balance >= %f", rate, octstr_get_cstr(msg->sms.binfo), rate);
+ affected_rows = mysql_update_affected_rows(sql);
+ octstr_destroy(sql);
+ return affected_rows > 0;
+}
+
void mysql_leave()
{
dbpool_destroy(pool);
@@ -310,6 +368,7 @@
res->sql_leave = mysql_leave;
res->sql_fetch_msg = mysql_fetch_msg;
res->sql_save_msg = mysql_save_msg;
+ res->sql_check_balance = mysql_check_balance;
return res;
}
#endif