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

[Sergey Safarov]

For me looks like it is all fine.
Postgres sends BLOBs using "bytea Escape Format" or "bytea Hex Format".
Sowhenyoutry send XML text as BLOB it will converted to encoded format. And
you see hex string.

On Wed, Aug 10, 2022 at 11:18 AM Henning Westerholt <hw at gilawa.com> wrote:

> 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
>
> _______________________________________________
> Kamailio (SER) - Development Mailing List
> sr-dev at lists.kamailio.org
> https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-dev/attachments/20220815/e1c16f4d/attachment-0002.htm>