siremis support for postgresql

[mbodbg]

• The connection to the postgres db is not possible. It tries to connect with the user www-data, however an other user is specified.
• To create the db it uses an sql script in mysql syntax (with backticks, sql keywords as table and column names, auto incement fields, ...) which is not compatible with postgres.

[miconda]

This probably needs help from someone using postgres to create the postgres sql scripts and review the code.

There was an attempt to do it, so some parts were done:

• https://github.com/asipto/siremis/blob/master/siremis/modules/ser/mod.install.siremis.pgsql.sql

But not using postgres here and lack of time left it unfinished.

[defeden]

Hi;
i've done the conversion from mysql to postgresql for you

CREATE OR REPLACE FUNCTION kamailio_cdrs() RETURNS VOID AS $$

DECLARE
bye_record INT DEFAULT 0;
v_bye_time TIMESTAMP(0);

DECLARE
inv_cursor CURSOR FOR
SELECT src_user, src_domain, dst_user, dst_domain, dst_ouser, time, callid,from_tag, to_tag, src_ip
FROM acc
WHERE method = 'INVITE' and cdr_id = '0';
BEGIN
FOR record IN inv_cursor LOOP
SET bye_record = 0;

	SELECT 1, time INTO bye_record, v_bye_time
	FROM acc
	WHERE
		 method = 'BYE'
	AND      callid = record.callid
	AND      ((from_tag = record.from_tag AND to_tag = record.to_tag)
            OR       (from_tag = record.to_tag AND to_tag = record.from_tag))
            ORDER BY time ASC
	LIMIT 1;

	IF bye_record = 1 THEN
		INSERT INTO cdrs (src_username,src_domain,dst_username,
             	dst_domain,dst_ousername,call_start_time,duration,sip_call_id,
             	sip_from_tag,sip_to_tag,src_ip,created) VALUES (record.src_user,
             	record.src_domain,record.dst_user,record.dst_domain,record.dst_ouser,record.inv_time,
             	UNIX_TIMESTAMP(v_bye_time)-UNIX_TIMESTAMP(record.time),
             	record.callid,record.from_tag,record.to_tag,record.src_ip,NOW());

		UPDATE acc
		SET cdr_id = last_insert_id()
		WHERE callid = record.callid
		AND from_tag=record.from_tag AND to_tag=record.to_tag;
	END IF;                                                                   
END LOOP;

END
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION kamailio_rating(rgroup varchar(64)) RETURNS VOID AS $$

DECLARE
rate_record INT DEFAULT 0;
v_rate_unit INT DEFAULT 0;
v_time_unit INT DEFAULT 0;
vx_cost INT DEFAULT 0;
cdrs_cursor CURSOR FOR
SELECT cdr_id, dst_username, duration
FROM cdrs
WHERE rated = 0;
BEGIN
FOR record IN cdrs_cursor LOOP
SET rate_record = 0;
SELECT 1, rate_unit, time_unit INTO rate_record, v_rate_unit, v_time_unit
FROM billing_rates
WHERE rate_group = rgroup AND record.dst_username LIKE concat(prefix, '%')
ORDER BY prefix DESC
LIMIT 1;

	IF rate_record = 1 THEN
		SELECT v_rate_unit * CEIL(record.duration / v_time_unit) INTO vx_cost;
		UPDATE cdrs SET rated = 1, cost = vx_cost WHERE cdr_id = record.cdr_id;
	END IF;
END LOOP;

END
$$ LANGUAGE plpgsql;

DROP TABLE IF EXISTS acc;

DO
$$
BEGIN
CREATE SEQUENCE acc_seq;
EXCEPTION WHEN duplicate_table THEN
-- do nothing, it's already there
END
$$ LANGUAGE plpgsql;

CREATE TABLE acc (
id int check (id > 0) NOT NULL default nextval ('acc_seq'),
method varchar(16) NOT NULL default '',
from_tag varchar(64) NOT NULL default '',
to_tag varchar(64) NOT NULL default '',
callid varchar(128) NOT NULL default '',
sip_code char(3) NOT NULL default '',
sip_reason varchar(32) NOT NULL default '',
time timestamp(0) NOT NULL default '1900-01-01 00:00:00',
src_ip varchar(64) NOT NULL default '',
dst_ouser VARCHAR(64) NOT NULL DEFAULT '',
dst_user varchar(64) NOT NULL default '',
dst_domain varchar(128) NOT NULL default '',
src_user varchar(64) NOT NULL default '',
src_domain varchar(128) NOT NULL default '',
cdr_id integer NOT NULL default '0'
,
PRIMARY KEY (id)
);

DO
$$
BEGIN
CREATE INDEX acc_callid ON acc (callid);
EXCEPTION WHEN duplicate_table THEN
-- do nothing, it's already there
END
$$ LANGUAGE plpgsql;

DROP TABLE IF EXISTS missed_calls;

DO
$$
BEGIN
CREATE SEQUENCE missed_calls_seq;
EXCEPTION WHEN duplicate_table THEN
-- do nothing, it's already there
END
$$ LANGUAGE plpgsql;

CREATE TABLE missed_calls (
id int check (id > 0) NOT NULL default nextval ('missed_calls_seq'),
method varchar(16) NOT NULL default '',
from_tag varchar(64) NOT NULL default '',
to_tag varchar(64) NOT NULL default '',
callid varchar(128) NOT NULL default '',
sip_code char(3) NOT NULL default '',
sip_reason varchar(32) NOT NULL default '',
time timestamp(0) NOT NULL default '1900-01-01 00:00:00',
src_ip varchar(64) NOT NULL default '',
dst_ouser VARCHAR(64) NOT NULL DEFAULT '',
dst_user varchar(64) NOT NULL default '',
dst_domain varchar(128) NOT NULL default '',
src_user varchar(64) NOT NULL default '',
src_domain varchar(128) NOT NULL default '',
cdr_id integer NOT NULL default '0'
,
PRIMARY KEY (id)
);

DO
$$
BEGIN
CREATE INDEX mc_callid ON missed_calls (callid);
EXCEPTION WHEN duplicate_table THEN
-- do nothing, it's already there
END
$$ LANGUAGE plpgsql;

DROP TABLE IF EXISTS cdrs;

DO
$$
BEGIN
CREATE SEQUENCE cdrs_seq;
EXCEPTION WHEN duplicate_table THEN
-- do nothing, it's already there
END
$$ LANGUAGE plpgsql;

CREATE TABLE cdrs (
cdr_id bigint NOT NULL default nextval ('cdrs_seq'),
src_username varchar(64) NOT NULL default '',
src_domain varchar(128) NOT NULL default '',
dst_username varchar(64) NOT NULL default '',
dst_domain varchar(128) NOT NULL default '',
dst_ousername varchar(64) NOT NULL default '',
call_start_time timestamp(0) NOT NULL default '1900-01-01 00:00:00',
duration int check (duration > 0) NOT NULL default '0',
sip_call_id varchar(128) NOT NULL default '',
sip_from_tag varchar(128) NOT NULL default '',
sip_to_tag varchar(128) NOT NULL default '',
src_ip varchar(64) NOT NULL default '',
cost integer NOT NULL default '0',
rated integer NOT NULL default '0',
created timestamp(0) NOT NULL,
PRIMARY KEY (cdr_id),
CONSTRAINT uk_cft UNIQUE (sip_call_id,sip_from_tag,sip_to_tag)
);

DROP TABLE IF EXISTS billing_rates;

DO
$$
BEGIN
CREATE SEQUENCE billing_rates_seq;
EXCEPTION WHEN duplicate_table THEN
-- do nothing, it's already there
END
$$ LANGUAGE plpgsql;

CREATE TABLE billing_rates (
rate_id bigint NOT NULL default nextval ('billing_rates_seq'),
rate_group varchar(64) NOT NULL default 'default',
prefix varchar(64) NOT NULL default '',
rate_unit integer NOT NULL default '0',
time_unit integer NOT NULL default '60',
PRIMARY KEY (rate_id),
CONSTRAINT uk_rp UNIQUE (rate_group,prefix)
);

DROP TABLE IF EXISTS statistics;

DO
$$
BEGIN
CREATE SEQUENCE statistics_seq;
EXCEPTION WHEN duplicate_table THEN
-- do nothing, it's already there
END
$$ LANGUAGE plpgsql;

CREATE TABLE statistics (
id int check (id > 0) NOT NULL default nextval ('statistics_seq'),
time_stamp int check (time_stamp > 0) NOT NULL default '0',
shm_used_size int check (shm_used_size > 0) NOT NULL default '0',
shm_real_used_size int check (shm_real_used_size > 0) NOT NULL default '0',
shm_max_used_size int check (shm_max_used_size > 0) NOT NULL default '0',
shm_free_used_size int check (shm_free_used_size > 0) NOT NULL default '0',
ul_users int check (ul_users > 0) NOT NULL default '0',
ul_contacts int check (ul_contacts > 0) NOT NULL default '0',
tm_active int check (tm_active > 0) NOT NULL default '0',
rcv_req_diff int check (rcv_req_diff > 0) NOT NULL default '0',
fwd_req_diff int check (fwd_req_diff > 0) NOT NULL default '0',
"2xx_trans_diff" int check ("2xx_trans_diff" > 0) NOT NULL default '0',
PRIMARY KEY (id)
);

INSERT INTO domain (domain, did)
SELECT '127.0.0.1', 'default'
WHERE NOT EXISTS (SELECT did FROM domain WHERE did = 'default');