Register Database Schema

Revision as of 13:03, 21 March 2013

This are examples of database tables for storing the CDRs, for keeping the accounts and the users data written in PostgreSQL and MySQL.

Create tables in PostGreSQL

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
)

Create tables in MySQL

For MySQL users here is an example for a CDR table with an equivalent MySQL-Function to be called on cdr_insert, cdr_update and cdr_finalize

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