Register Database Schema

Latest revision as of 15:28, 6 August 2020

Below you can find the database schema for PostgreSQL and MySQL for using yate's register module. You also have the CDR queries for both versions.

You can consider this schemas a starting point. You can modify them and the queries so you can customize the information you want to hold depending on your application.

[edit] PostgreSQL

[edit] Database schema

CREATE TABLE cdr
(
"time" timestamp without time zone,
chan text,
address text,
direction text,
billid text,
caller text,
"called" text,
duration time without time zone,
billtime time without time zone,
ringtime time without time zone,
status text,
reason text,
ended boolean
)

CREATE TABLE accounts
(
enabled boolean,
protocol text,
username text,
description text,
"interval" text,
formats text,
authname text,
"password" text,
"number" text,
server text,
gatekeeper text,
"domain" text,
registrar text,
outbound text,
localaddress text,
modified boolean,
account text,
id serial NOT NULL,
status text
)

CREATE TABLE users
(
"password" text,
first_name text,
last_name text,
address text,
inuse integer,
"location" text,
expires timestamp without time zone,
username text
)

[edit] CDR queries

The CDR queries for PostgreSQL are in register.conf.sample.

[edit] MySQL

[edit] Dabase schema

CREATE TABLE cdr
(
"time" timestamp,
chan text,
address text,
direction text,
billid text,
caller text,
"called" text,
duration time,
billtime time,
ringtime time,
status text,
reason text,
ended boolean
)

CREATE TABLE users
(
 password text,
 first_name text,
 last_name text,
 address text,
 inuse int(11),
 location text,
 expires datetime,
 username text
)

For accounts table you can use the PostgreSQL version above.

[edit] CDR queries

cdr_initialize=INSERT INTO cdr(time, chan, address, direction, billid, caller, called, duration, billtime, ringtime, status, reason, ended) VALUES(from_unixtime(${time}, '%Y-%m-%d %H:%i:%s'), '${chan}', '${address}', '${direction}', '${billid}', '${caller}', '${called}', sec_to_time(${duration}), sec_to_time(${billtime}), sec_to_time(${ringtime}), '${status}', '${reason}', false)

cdr_update=UPDATE cdr SET address='${address}', direction='${direction}', billid='${billid}', caller='${caller}', called='${called}', duration=sec_to_time($duration}), billtime=sec_to_time(${billtime}), ringtime=sec_to_time(${ringtime}), status='${status}', reason='${reason}' WHERE chan='${chan}' AND time=from_unixtime(${time}, '%Y-%m-%d %H:%i:%s')

cdr_finalize=UPDATE cdr SET address='${address}', direction='${direction}', billid='${billid}', caller='${caller}', called='${called}',duration=sec_to_time(${duration}), billtime=sec_to_time(${billtime}), ringtime=sec_to_time(${ringtime}), status='${status}', reason='${reason}', ended=true WHERE chan='${chan}' AND time=from_unixtime(${time}, '%Y-%m-%d %H:%i:%s')

[edit] MySQL version with stored functions

One of our users suggested a more complex approach. Below is the CDR table with an equivalent MySQL-Function to be called on cdr_insert, cdr_update and cdr_finalize.

NOTE: The table structure below only works with the functions under it.

CREATE  TABLE IF NOT EXISTS `cdr` (
  `idcdr` BIGINT NULL AUTO_INCREMENT,
  `sqltime` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `yatetime` INT(11) NULL,
  `billid` VARCHAR(55) NOT NULL,
  `chan` VARCHAR(20) NULL,
  `address` CHAR(15) NULL,
  `addressport` CHAR(5) NULL,
  `caller` VARCHAR(55) NULL,
  `callername` VARCHAR(255) NULL,
  `called` VARCHAR(55) NULL,
  `billtime` FLOAT NULL,
  `ringtime` FLOAT NULL,
  `duration` FLOAT NULL,
  `direction` ENUM('incoming','outgoing') NULL,
  `status` VARCHAR(11) NULL,
  `reason` VARCHAR(55) NULL,
  `ended` TINYINT(1) NULL,
PRIMARY KEY (`idcdr`),
INDEX `ix_cdr_sqltime` (`sqltime` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- A primary key to prevent duplicates on billid and chan
CREATE UNIQUE INDEX uq_cdr_billid_chan ON cdr (billid, chan);


-- A function to use for CDR operations
delimiter $$
CREATE FUNCTION  cdr_upsert (p_yatetime INT(11), p_billid varchar(20), p_chan varchar(20), p_address varchar(21), p_caller varchar(55), p_callername varchar(255),
p_called varchar(55), p_billtime float, p_ringtime float, p_duration float, p_direction enum('incoming','outgoing'), p_status varchar(11), p_reason varchar(55),
p_ended tinyint(1))  RETURNS SMALLINT(1)
BEGIN

  DECLARE i_ended TINYINT(1);
  DECLARE i_address_port VARCHAR(5);

  -- extracting IP and Port
  SET i_address_port = (SUBSTRING(p_address, POSITION(':' IN p_address) + 1));
  SET p_address = (SUBSTRING(p_address, 1, POSITION(':' IN p_address) - 1));

  -- Checking current CDR state, when ended is true, then no update should be done, because cdr_finalize was already called
  SELECT ended INTO i_ended FROM cdr WHERE billid = p_billid AND chan = p_chan FOR UPDATE;

  IF i_ended IS NULL OR i_ended = 0 THEN

    -- perform an UPSERT meaning: try to INSERT the data, if there is a DUPLICATE KEY (uq_cdr_billid_chan which we created ealiert) then do an update on that record instead
    INSERT INTO cdr (sqltime, yatetime, billid, chan, address, addressport, caller, callername, called, billtime, ringtime, duration, direction, status ,reason, ended)
    VALUES (FROM_UNIXTIME(p_yatetime), p_yatetime, p_billid, p_chan, p_address, i_address_port, p_caller, p_callername, p_called, p_billtime, p_ringtime, p_duration,
    p_direction, p_status, p_reason, p_ended) ON DUPLICATE KEY UPDATE sqltime = VALUES(sqltime), yatetime = VALUES(yatetime), address = VALUES(address),
    addressport = VALUES(addressport), caller = VALUES(caller), callername = VALUES(callername), called = VALUES(called), billtime = VALUES(billtime),
    ringtime = VALUES(ringtime), duration = VALUES(duration),
    direction = VALUES(direction), status = VALUES(status), reason = VALUES(reason),
    ended = VALUES(ended);    

  END IF;

  RETURN 1;
END;
$$
delimiter ;

Then in register.conf you configure it as follows:

 -- Usage of the function in register.conf, only difference in cdr_insert/update and cdr_finalize is the last value for "ended"
cdr_insert=SELECT cdr_upsert(${time}, '${billid}', '${chan}', '${address}', '${caller}', '${callername}', '${called}', ${billtime}, ${ringtime}, ${duration},
'${direction}', '${status}', '${reason}', false);
cdr_update=SELECT cdr_upsert(${time}, '${billid}', '${chan}', '${address}', '${caller}', '${callername}', '${called}', ${billtime}, ${ringtime}, ${duration},
'${direction}', '${status}', '${reason}', false);
cdr_finalize=SELECT cdr_upsert(${time}, '${billid}', '${chan}', '${address}', '${caller}', '${callername}', '${called}', ${billtime}, ${ringtime}, ${duration},
'${direction}', '${status}', '${reason}', true);

See also

• register.conf