mirror of https://github.com/sipwise/db-schema.git
You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
72 lines
2.8 KiB
72 lines
2.8 KiB
-- step out of our provisioning DB
|
|
USE mysql;
|
|
|
|
-- drop database if it allready exists
|
|
-- this will drop all tables and triggers
|
|
DROP DATABASE IF EXISTS accounting;
|
|
|
|
-- create DB with utf8 default charset, so we don't have to
|
|
-- specify charset for each table
|
|
CREATE DATABASE IF NOT EXISTS accounting CHARACTER SET 'utf8';
|
|
|
|
USE accounting;
|
|
|
|
-- create accounting tables
|
|
|
|
CREATE TABLE `acc` like kamailio.acc;
|
|
|
|
CREATE TABLE `cdr` (
|
|
`id` int(10) unsigned NOT NULL auto_increment,
|
|
`update_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
|
|
`source_user_id` char(36) NOT NULL,
|
|
`source_provider_id` varchar(255) NOT NULL,
|
|
`source_user` varchar(255) NOT NULL,
|
|
`source_domain` varchar(255) NOT NULL,
|
|
`source_cli` varchar(64) NOT NULL,
|
|
`source_clir` tinyint(1) NOT NULL default '0',
|
|
`destination_user_id` char(36) NOT NULL,
|
|
`destination_provider_id` varchar(255) NOT NULL,
|
|
`destination_user` varchar(255) NOT NULL,
|
|
`destination_domain` varchar(255) NOT NULL,
|
|
`destination_user_dialed` varchar(255) NOT NULL,
|
|
`destination_user_in` varchar(255) NOT NULL,
|
|
`destination_domain_in` varchar(255) NOT NULL,
|
|
`call_type` enum('call','cfu','cft','cfb','cfna') NOT NULL default 'call',
|
|
`call_status` enum('ok','busy','noanswer','cancel','offline','timeout','other') NOT NULL default 'ok',
|
|
`call_code` char(3) NOT NULL,
|
|
`start_time` timestamp NOT NULL default '0000-00-00 00:00:00',
|
|
`duration` int(10) unsigned NOT NULL,
|
|
`call_id` varchar(255) NOT NULL,
|
|
`carrier_cost` float default NULL,
|
|
`reseller_cost` float default NULL,
|
|
`customer_cost` float default NULL,
|
|
`carrier_billing_fee_id` int(11) unsigned default NULL,
|
|
`reseller_billing_fee_id` int(11) unsigned default NULL,
|
|
`customer_billing_fee_id` int(11) unsigned default NULL,
|
|
`carrier_billing_zone_id` int(11) unsigned default NULL,
|
|
`reseller_billing_zone_id` int(11) unsigned default NULL,
|
|
`customer_billing_zone_id` int(11) unsigned default NULL,
|
|
`frag_carrier_onpeak` tinyint(1) default NULL,
|
|
`frag_reseller_onpeak` tinyint(1) default NULL,
|
|
`frag_customer_onpeak` tinyint(1) default NULL,
|
|
`is_fragmented` tinyint(1) default NULL,
|
|
`rated_at` datetime default NULL,
|
|
`rating_status` enum('unrated','ok','failed') NOT NULL default 'unrated',
|
|
PRIMARY KEY (`id`),
|
|
KEY `suid` (`source_user_id`),
|
|
KEY `duid` (`destination_user_id`),
|
|
KEY `suri` (`source_user`,`source_domain`,`source_cli`),
|
|
KEY `duri` (`destination_user`,`destination_domain`),
|
|
KEY `sprov` (`source_provider_id`),
|
|
KEY `dprov` (`destination_provider_id`),
|
|
KEY `kcid` (`call_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE `mark` (
|
|
`id` int(10) unsigned NOT NULL auto_increment,
|
|
`collector` varchar(255) NOT NULL,
|
|
`acc_id` int(10) unsigned NOT NULL,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|