[sr-dev] db_postgres / DB1_BLOB / sipcapture HOMER issue

[Daniel Pocock]

Hi all,

I was doing some tests with HOMER and a PostgreSQL backend

The HOMER schema creates the msg column as varchar(1500)

CREATE TABLE IF NOT EXISTS rtcp_capture (
  id BIGSERIAL NOT NULL,
  date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  micro_ts bigint NOT NULL DEFAULT '0',
  correlation_id varchar(256) NOT NULL DEFAULT '',
  source_ip varchar(60) NOT NULL DEFAULT '',
  source_port integer NOT NULL DEFAULT 0,
  destination_ip varchar(60) NOT NULL DEFAULT '',
  destination_port integer NOT NULL DEFAULT 0,
  proto integer NOT NULL DEFAULT 0,
  family smallint DEFAULT NULL,
  type integer NOT NULL DEFAULT 0,
  node varchar(125) NOT NULL DEFAULT '',
  msg varchar(1500) NOT NULL DEFAULT '',
  PRIMARY KEY (id,date)
);


The sipcapture.c source code says that msg is DB1_BLOB

https://github.com/kamailio/kamailio/blob/master/src/modules/sipcapture/sipcapture.c#L2710

	db_keys[11] = &msg_column;
	db_vals[11].type = DB1_BLOB;
	db_vals[11].nul = 0;


When the Kamailio PostgreSQL module db_postgres tries to store DB1_BLOB,
it converts the message text into a hex string and stores that string
like this:

'0x7b2273656e6465725f696e666f726d6174696f6e223a7b226e74705f74696d657374616d705f736563223a3338333033312c226e74705f74696d657374616d705f75736563223a32333237332c226f6374657473223a3136343030302c227274705f74696d657374616d70223a323132353238323231342c227061636b657473223a313032357d2c2273737263223a323330343334323235382c2274797065223a3230302c227265706f72745f626c6f636b73223a5b7b22736f757263655f73737263223a333737363636373030392c22686967686573745f7365715f6e6f223a31363739332c226672616374696f6e5f6c6f7374223a302c2269615f6a6974746572223a31372c227061636b6574735f6c6f7374223a302c226c7372223a3837363937393735352c22646c7372223a35303234397d5d2c227265706f72745f636f756e74223a317d'

I tried changing the msg column type to bytea (blob) and Kamailio is
still doing the same thing, storing a hex string into the column.

Then I changed the column back to varchar(1500) and I modified
sipcapture.c to use DB1_STR.  With this change it is working OK.
However, I suspect that the support for DB1_BLOB conversions may need to
be reviewed in
src/modules/db_postgres/km_val.c

Regards,

Daniel

-- 
https://danielpocock.com
Follow with RSS: https://danielpocock.com/rss.xml