[PATCH] Meta-Data Implementation for SQLBOX-Standalone

"Alejandro Guerrieri" <[email protected]>
Newsgroups gmane.comp.mobile.kannel.devel
Message-ID <[email protected]> 
Dear List,

I've made a patch against sqlbox-standalone to implement the meta_data
stuff from Alex's Patch into sqlbox (Standalone). I think it may work
against the built-in SQLBox as well, but didn't tried myself. It only
changes sqlbox_mysql.c and sqlbox_pgsql.c so it's a simple mod in
fact.

The patch adds an extra TEXT column "meta_data" to send_sms and
sent_sms. If you're using sqlbox already, you must add the column
yourself or drop the tables and sqlbox will create the new ones at
boot time.

Note: I've used TEXT instead of VARCHAR(255) since 255 chars may fall
short if you're using many TLV's at the same time. I don't see any
performance gain in using varchars anyway.

Regards,

Alejandro

-- 
Alejandro Guerrieri
Magicom
http://www.magicom-bcn.net/
LinkedIn: http://www.linkedin.com/in/aguerrieri
sqlbox-standalone-tlvpatch.diff (application/octet-stream, 13.2 KB) 
diff -ur sqlbox-standalone/gw/sqlbox_mysql.c sqlbox-standalone-tlv/gw/sqlbox_mysql.c
--- sqlbox-standalone/gw/sqlbox_mysql.c	2006-11-06 14:47:57.000000000 -0200
+++ sqlbox-standalone-tlv/gw/sqlbox_mysql.c	2007-08-07 00:33:38.000000000 -0300
@@ -91,10 +91,10 @@
 	}
 
 	/* create send_sms && sent_sms tables if they do not exist */
-	sql = octstr_format("CREATE TABLE IF NOT EXISTS %S (sql_id bigint(20) not null auto_increment primary key, momt enum('MO', 'MT', 'DLR') null, sender varchar(20) null, receiver varchar(20) null, udhdata blob null, msgdata text null, time bigint(20) null, smsc_id varchar(255) null, service varchar(255) null, account varchar(255) null, id bigint(20) null, sms_type bigint(20) null, mclass bigint(20) null, mwi bigint(20) null, coding bigint(20) null, compress bigint(20) null, validity bigint(20) null, deferred bigint(20) null, dlr_mask bigint(20) null, dlr_url varchar(255) null, pid bigint(20) null, alt_dcs bigint(20) null, rpi bigint(20) null, charset varchar(255) null, boxc_id varchar(255) null, binfo varchar(255) null)", sqlbox_logtable);
+	sql = octstr_format("CREATE TABLE IF NOT EXISTS %S (sql_id bigint(20) not null auto_increment primary key, momt enum('MO', 'MT', 'DLR') null, sender varchar(20) null, receiver varchar(20) null, udhdata blob null, msgdata text null, time bigint(20) null, smsc_id varchar(255) null, service varchar(255) null, account varchar(255) null, id bigint(20) null, sms_type bigint(20) null, mclass bigint(20) null, mwi bigint(20) null, coding bigint(20) null, compress bigint(20) null, validity bigint(20) null, deferred bigint(20) null, dlr_mask bigint(20) null, dlr_url varchar(255) null, pid bigint(20) null, alt_dcs bigint(20) null, rpi bigint(20) null, charset varchar(255) null, boxc_id varchar(255) null, binfo varchar(255) null, meta_data text null)", sqlbox_logtable);
 	sql_update(sql);
 	octstr_destroy(sql);
-	sql = octstr_format("CREATE TABLE IF NOT EXISTS %S (sql_id bigint(20) not null auto_increment primary key, momt enum('MO', 'MT') null, sender varchar(20) null, receiver varchar(20) null, udhdata blob null, msgdata text null, time bigint(20) null, smsc_id varchar(255) null, service varchar(255) null, account varchar(255) null, id bigint(20) null, sms_type bigint(20) null, mclass bigint(20) null, mwi bigint(20) null, coding bigint(20) null, compress bigint(20) null, validity bigint(20) null, deferred bigint(20) null, dlr_mask bigint(20) null, dlr_url varchar(255) null, pid bigint(20) null, alt_dcs bigint(20) null, rpi bigint(20) null, charset varchar(255) null, boxc_id varchar(255) null, binfo varchar(255) null)", sqlbox_insert_table);
+	sql = octstr_format("CREATE TABLE IF NOT EXISTS %S (sql_id bigint(20) not null auto_increment primary key, momt enum('MO', 'MT') null, sender varchar(20) null, receiver varchar(20) null, udhdata blob null, msgdata text null, time bigint(20) null, smsc_id varchar(255) null, service varchar(255) null, account varchar(255) null, id bigint(20) null, sms_type bigint(20) null, mclass bigint(20) null, mwi bigint(20) null, coding bigint(20) null, compress bigint(20) null, validity bigint(20) null, deferred bigint(20) null, dlr_mask bigint(20) null, dlr_url varchar(255) null, pid bigint(20) null, alt_dcs bigint(20) null, rpi bigint(20) null, charset varchar(255) null, boxc_id varchar(255) null, binfo varchar(255) null, meta_data text null)", sqlbox_insert_table);
 	sql_update(sql);
 	octstr_destroy(sql);
 	/* end table creation */
@@ -109,7 +109,7 @@
 	MYSQL_RES *res;
 	MYSQL_ROW row;
 
-	sql = octstr_format("SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo FROM %S LIMIT 0,1", sqlbox_insert_table);
+	sql = octstr_format("SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo, meta_data FROM %S LIMIT 0,1", sqlbox_insert_table);
 	res = mysql_select(sql);
 	if (res == NULL) {
 		debug("sqlbox", 0, "SQL statement failed: %s", octstr_get_cstr(sql));
@@ -143,6 +143,7 @@
 			msg->sms.rpi		= atol_null(row[22]);
 			msg->sms.charset	= octstr_null_create(row[23]);
 			msg->sms.binfo		= octstr_null_create(row[25]);
+			msg->sms.meta_data	= octstr_null_create(row[26]);
 			if (row[24] == NULL) {
 				msg->sms.boxc_id= octstr_duplicate(sqlbox_id);
 			}
@@ -196,8 +197,8 @@
 	if(msg->sms.coding == 2)
          	octstr_binary_to_hex(msg->sms.msgdata,1);
 
-	values = octstr_format("NULL, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S", 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));
-	sql = octstr_format("INSERT INTO %S (sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo) VALUES (%S)", sqlbox_logtable, values);
+	values = octstr_format("NULL, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S", 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 = octstr_format("INSERT INTO %S (sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo, meta_data) VALUES (%S)", sqlbox_logtable, values);
 	sql_update(sql);
         //debug("sqlbox", 0, "mysql_save_msg: %s", octstr_get_cstr(sql));
 	while (stuffcount > 0) {
diff -ur sqlbox-standalone/gw/sqlbox_pgsql.c sqlbox-standalone-tlv/gw/sqlbox_pgsql.c
--- sqlbox-standalone/gw/sqlbox_pgsql.c	2006-11-06 14:47:57.000000000 -0200
+++ sqlbox-standalone-tlv/gw/sqlbox_pgsql.c	2007-08-07 00:36:41.000000000 -0300
@@ -154,10 +154,10 @@
 	}
 
 	/* create send_sms && sent_sms tables if they do not exist */
-	sql = octstr_format("CREATE TABLE %S (sql_id SERIAL PRIMARY KEY, momt VARCHAR(3) CHECK(momt IN ('MO', 'MT', 'DLR', NULL)) DEFAULT NULL, sender VARCHAR(20) NULL, receiver VARCHAR(20) NULL, udhdata VARCHAR(255) NULL, msgdata VARCHAR(255) NULL, time BIGINT NULL, smsc_id VARCHAR(255) NULL, service VARCHAR(255) NULL, account VARCHAR(255) NULL, id BIGINT NULL, sms_type BIGINT NULL, mclass BIGINT NULL, mwi BIGINT NULL, coding BIGINT NULL, compress BIGINT NULL, validity BIGINT NULL, deferred BIGINT NULL, dlr_mask BIGINT NULL, dlr_url VARCHAR(255) NULL, pid BIGINT NULL, alt_dcs BIGINT NULL, rpi BIGINT NULL, charset VARCHAR(255) NULL, boxc_id VARCHAR(255) NULL, binfo VARCHAR(255) NULL)", sqlbox_logtable);
+	sql = octstr_format("CREATE TABLE %S (sql_id SERIAL PRIMARY KEY, momt VARCHAR(3) CHECK(momt IN ('MO', 'MT', 'DLR', NULL)) DEFAULT NULL, sender VARCHAR(20) NULL, receiver VARCHAR(20) NULL, udhdata VARCHAR(255) NULL, msgdata VARCHAR(255) NULL, time BIGINT NULL, smsc_id VARCHAR(255) NULL, service VARCHAR(255) NULL, account VARCHAR(255) NULL, id BIGINT NULL, sms_type BIGINT NULL, mclass BIGINT NULL, mwi BIGINT NULL, coding BIGINT NULL, compress BIGINT NULL, validity BIGINT NULL, deferred BIGINT NULL, dlr_mask BIGINT NULL, dlr_url VARCHAR(255) NULL, pid BIGINT NULL, alt_dcs BIGINT NULL, rpi BIGINT NULL, charset VARCHAR(255) NULL, boxc_id VARCHAR(255) NULL, binfo VARCHAR(255) NULL, meta_data TEXT NULL)", sqlbox_logtable);
 	sql_update(sql);
 	octstr_destroy(sql);
-	sql = octstr_format("CREATE TABLE %S (sql_id SERIAL PRIMARY KEY, momt VARCHAR(3) CHECK(momt IN ('MO', 'MT', NULL)) DEFAULT NULL, sender VARCHAR(20) NULL, receiver VARCHAR(20) NULL, udhdata VARCHAR(255) NULL, msgdata VARCHAR(255) NULL, time BIGINT NULL, smsc_id VARCHAR(255) NULL, service VARCHAR(255) NULL, account VARCHAR(255) NULL, id BIGINT NULL, sms_type BIGINT NULL, mclass BIGINT NULL, mwi BIGINT NULL, coding BIGINT NULL, compress BIGINT NULL, validity BIGINT NULL, deferred BIGINT NULL, dlr_mask BIGINT NULL, dlr_url VARCHAR(255) NULL, pid BIGINT NULL, alt_dcs BIGINT NULL, rpi BIGINT NULL, charset VARCHAR(255) NULL, boxc_id VARCHAR(255) NULL, binfo VARCHAR(255) NULL)", sqlbox_insert_table);
+	sql = octstr_format("CREATE TABLE %S (sql_id SERIAL PRIMARY KEY, momt VARCHAR(3) CHECK(momt IN ('MO', 'MT', NULL)) DEFAULT NULL, sender VARCHAR(20) NULL, receiver VARCHAR(20) NULL, udhdata VARCHAR(255) NULL, msgdata VARCHAR(255) NULL, time BIGINT NULL, smsc_id VARCHAR(255) NULL, service VARCHAR(255) NULL, account VARCHAR(255) NULL, id BIGINT NULL, sms_type BIGINT NULL, mclass BIGINT NULL, mwi BIGINT NULL, coding BIGINT NULL, compress BIGINT NULL, validity BIGINT NULL, deferred BIGINT NULL, dlr_mask BIGINT NULL, dlr_url VARCHAR(255) NULL, pid BIGINT NULL, alt_dcs BIGINT NULL, rpi BIGINT NULL, charset VARCHAR(255) NULL, boxc_id VARCHAR(255) NULL, binfo VARCHAR(255) NULL, meta_data TEXT NULL)", sqlbox_insert_table);
 	sql_update(sql);
 	octstr_destroy(sql);
 	/* end table creation */
@@ -197,8 +197,8 @@
         if(msg->sms.coding == 2)
                 octstr_binary_to_hex(msg->sms.msgdata,1);
 
-	values = octstr_format("%S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S", 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));
-	sql = octstr_format("INSERT INTO %S (momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo) VALUES (%S)", sqlbox_logtable, values);
+	values = octstr_format("%S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S, %S", 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 = octstr_format("INSERT INTO %S (momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, , meta_data) VALUES (%S)", sqlbox_logtable, values);
 	sql_update(sql);
         //debug("sqlbox", 0, "sql_save_msg: %s", octstr_get_cstr(sql));
 	while (stuffcount > 0) {
@@ -221,7 +221,7 @@
 	Octstr *sql, *delet, *id;
 	PGresult *res;
 
-	sql = octstr_format("SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo FROM %S LIMIT 1 OFFSET 0", sqlbox_insert_table);
+	sql = octstr_format("SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id, service, account, id, sms_type, mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo, meta_data FROM %S LIMIT 1 OFFSET 0", sqlbox_insert_table);
 	res = pgsql_select(sql);
 	if (res == NULL) {
 		debug("sqlbox", 0, "SQL statement failed: %s", octstr_get_cstr(sql));
@@ -254,6 +254,7 @@
 			msg->sms.rpi		= atol_null(22);
 			msg->sms.charset	= octstr_null_create(23);
 			msg->sms.binfo		= octstr_null_create(25);
+			msg->sms.meta_data	= octstr_null_create(26);
 			if ((PQgetvalue(res, 0, 24)) == NULL) {
 				msg->sms.boxc_id= octstr_duplicate(sqlbox_id);
 			}
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.