<div dir="ltr">For me looks like it is all fine.<div>Postgres sends BLOBs using "bytea Escape Format" or "bytea Hex Format".<br>Sowhenyoutry send XML text as BLOB it will converted to encoded format. And you see hex string.</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Aug 10, 2022 at 11:18 AM Henning Westerholt <<a href="mailto:hw@gilawa.com">hw@gilawa.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hello,<br>
<br>
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. <br>
<br>
Maybe somebody else that uses the module with postgres can share some comments.<br>
<br>
DB1_BLOB is used also at other places, e.g. presence modules. Here it seems to work with DB1_BLOB and BYTEA with postgres.<br>
<br>
Cheers,<br>
<br>
Henning<br>
<br>
-- <br>
Henning Westerholt - <a href="https://skalatan.de/blog/" rel="noreferrer" target="_blank">https://skalatan.de/blog/</a><br>
Kamailio services - <a href="https://gilawa.com" rel="noreferrer" target="_blank">https://gilawa.com</a><br>
<br>
-----Original Message-----<br>
From: sr-dev <<a href="mailto:sr-dev-bounces@lists.kamailio.org" target="_blank">sr-dev-bounces@lists.kamailio.org</a>> On Behalf Of Daniel Pocock<br>
Sent: Wednesday, August 10, 2022 12:12 AM<br>
To: Kamailio Devel List <<a href="mailto:sr-dev@lists.sip-router.org" target="_blank">sr-dev@lists.sip-router.org</a>><br>
Subject: [sr-dev] db_postgres / DB1_BLOB / sipcapture HOMER issue<br>
<br>
<br>
<br>
Hi all,<br>
<br>
I was doing some tests with HOMER and a PostgreSQL backend<br>
<br>
The HOMER schema creates the msg column as varchar(1500)<br>
<br>
CREATE TABLE IF NOT EXISTS rtcp_capture (<br>
id BIGSERIAL NOT NULL,<br>
date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,<br>
micro_ts bigint NOT NULL DEFAULT '0',<br>
correlation_id varchar(256) NOT NULL DEFAULT '',<br>
source_ip varchar(60) NOT NULL DEFAULT '',<br>
source_port integer NOT NULL DEFAULT 0,<br>
destination_ip varchar(60) NOT NULL DEFAULT '',<br>
destination_port integer NOT NULL DEFAULT 0,<br>
proto integer NOT NULL DEFAULT 0,<br>
family smallint DEFAULT NULL,<br>
type integer NOT NULL DEFAULT 0,<br>
node varchar(125) NOT NULL DEFAULT '',<br>
msg varchar(1500) NOT NULL DEFAULT '',<br>
PRIMARY KEY (id,date)<br>
);<br>
<br>
<br>
The sipcapture.c source code says that msg is DB1_BLOB<br>
<br>
<a href="https://github.com/kamailio/kamailio/blob/master/src/modules/sipcapture/sipcapture.c#L2710" rel="noreferrer" target="_blank">https://github.com/kamailio/kamailio/blob/master/src/modules/sipcapture/sipcapture.c#L2710</a><br>
<br>
db_keys[11] = &msg_column;<br>
db_vals[11].type = DB1_BLOB;<br>
db_vals[11].nul = 0;<br>
<br>
<br>
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:<br>
<br>
'0x7b2273656e6465725f696e666f726d6174696f6e223a7b226e74705f74696d657374616d705f736563223a3338333033312c226e74705f74696d657374616d705f75736563223a32333237332c226f6374657473223a3136343030302c227274705f74696d657374616d70223a323132353238323231342c227061636b657473223a313032357d2c2273737263223a323330343334323235382c2274797065223a3230302c227265706f72745f626c6f636b73223a5b7b22736f757263655f73737263223a333737363636373030392c22686967686573745f7365715f6e6f223a31363739332c226672616374696f6e5f6c6f7374223a302c2269615f6a6974746572223a31372c227061636b6574735f6c6f7374223a302c226c7372223a3837363937393735352c22646c7372223a35303234397d5d2c227265706f72745f636f756e74223a317d'<br>
<br>
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.<br>
<br>
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.<br>
However, I suspect that the support for DB1_BLOB conversions may need to be reviewed in src/modules/db_postgres/km_val.c<br>
<br>
Regards,<br>
<br>
Daniel<br>
<br>
--<br>
<a href="https://danielpocock.com" rel="noreferrer" target="_blank">https://danielpocock.com</a><br>
Follow with RSS: <a href="https://danielpocock.com/rss.xml" rel="noreferrer" target="_blank">https://danielpocock.com/rss.xml</a><br>
<br>
_______________________________________________<br>
Kamailio (SER) - Development Mailing List <a href="mailto:sr-dev@lists.kamailio.org" target="_blank">sr-dev@lists.kamailio.org</a> <a href="https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev" rel="noreferrer" target="_blank">https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev</a><br>
<br>
_______________________________________________<br>
Kamailio (SER) - Development Mailing List<br>
<a href="mailto:sr-dev@lists.kamailio.org" target="_blank">sr-dev@lists.kamailio.org</a><br>
<a href="https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev" rel="noreferrer" target="_blank">https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-dev</a><br>
</blockquote></div>