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

[Henning Westerholt]

Hello,

the scheme in src/modules/sipcapture/sql/schema_data.sql is not maintained as part of the core database schemes. I guess it was probably only really tested with MySQL/MariaDB. 

Maybe somebody else that uses the module with postgres can share some comments.

DB1_BLOB is used also at other places, e.g. presence modules. Here it seems to work with DB1_BLOB and BYTEA with postgres.

Cheers,

Henning

-- 
Henning Westerholt - https://skalatan.de/blog/
Kamailio services - https://gilawa.com

-----Original Message-----
From: sr-dev <sr-dev-bounces at lists.kamailio.org> On Behalf Of Daniel Pocock
Sent: Wednesday, August 10, 2022 12:12 AM
To: Kamailio Devel List <sr-dev at lists.sip-router.org>
Subject: [sr-dev] db_postgres / DB1_BLOB / sipcapture HOMER issue



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

_______________________________________________
Kamailio (SER) - Development Mailing List sr-dev at lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev