I have been working on developing a Billing System (GPL) and the following is the work I have done on the Realtime Database.
My concerns are if asterisk will pick-up info that is cross-referenced between tables using Foreign Keys.
Does anyone have any ideas on this.
We are also looking for developers interested in working on the project.
The base framework for the system is www.pylonshq.com, which is a python/mason/perl styled web framework.
I look forward to comments.
/*
Created 5/25/2006
Modified 5/31/2006
Project Opentelcom Billing (Released under GPL)
Model
Company Inventigo.co.uk
Author Charles Sibbald
Version 0.0.2
Database mySQL 4.1
*/
Create table ast_cdr (
entitlement_id Int NOT NULL,
uniqueid Text NOT NULL,
accountcode Int NOT NULL,
cdr_pkey Serial NOT NULL AUTO_INCREMENT,
calldate Timestamp NOT NULL,
clid Text NOT NULL,
src Text NOT NULL,
dst Text NOT NULL,
dcontext Text NOT NULL,
channel Text NOT NULL,
dstchannel Text NOT NULL,
lastapp Text NOT NULL,
lastdata Text NOT NULL,
duration Bigint NOT NULL DEFAULT 0,
billsec Bigint NOT NULL DEFAULT 0,
disposition Text NOT NULL,
amaflags Bigint NOT NULL DEFAULT 0,
userfield Text NOT NULL,
service_id Int NOT NULL,
context Char(20) NOT NULL,
UNIQUE (uniqueid),
Primary Key (entitlement_id,accountcode,cdr_pkey,service_id,context),
Foreign Key (entitlement_id,service_id,accountcode,context) references webapp_entitlement (entitlement_id,service_id,accountcode,context) on delete restrict on update restrict
) ENGINE = MyISAM;
Create table ast_voicemail (
uniqueid Serial NOT NULL AUTO_INCREMENT,
mailbox Varchar(11) NOT NULL DEFAULT 0,
entitlement_id Int NOT NULL,
service_id Int NOT NULL,
accountcode Int NOT NULL,
context Char(20) NOT NULL,
customer_id Varchar(11) NOT NULL DEFAULT 0,
password Varchar(5) NOT NULL DEFAULT 0,
fullname Varchar(150) NOT NULL,
email Varchar(50) NOT NULL,
pager Varchar(50) NOT NULL,
tz Varchar(10) NOT NULL DEFAULT EST,
attach Varchar(4) NOT NULL DEFAULT yes,
saycid Varchar(4) NOT NULL DEFAULT yes,
dialout Varchar(20) NOT NULL,
callback Varchar(10) NOT NULL,
review Varchar(4) NOT NULL DEFAULT no,
operator Varchar(4) NOT NULL DEFAULT no,
envelope Varchar(4) NOT NULL DEFAULT no,
sayduration Varchar(4) NOT NULL DEFAULT no,
saydurationm Int NOT NULL DEFAULT 1,
sendvoicemail Varchar(4) NOT NULL DEFAULT no,
delete Varchar(4) NOT NULL DEFAULT no,
nextaftercmd Varchar(4) NOT NULL DEFAULT yes,
forcename Varchar(4) NOT NULL DEFAULT no,
forcegreetings Varchar(4) NOT NULL DEFAULT no,
hidefromdir Varchar(4) NOT NULL DEFAULT yes,
stamp Timestamp NOT NULL DEFAULT now(),
UNIQUE (uniqueid),
UNIQUE (mailbox),
Primary Key (uniqueid,mailbox,entitlement_id,service_id,accountcode,context),
Foreign Key (entitlement_id,service_id,accountcode,context) references webapp_entitlement (entitlement_id,service_id,accountcode,context) on delete restrict on update restrict
) ENGINE = MyISAM;
Create table ast_iax_buddies (
entitlement_id Int NOT NULL AUTO_INCREMENT,
name Varchar(30) NOT NULL,
accountcode Int NOT NULL,
mailbox Int NOT NULL AUTO_INCREMENT,
username Varchar(30),
type Varchar(6) NOT NULL,
secret Varchar(50),
md5secret Varchar(32),
dbsecret Varchar(100),
notransfer Varchar(10) DEFAULT yes,
inkeys Varchar(100),
outkeys Varchar(100),
auth Varchar(100) NOT NULL DEFAULT md5,
amaflags Varchar(100),
callerid Varchar(100),
defaultip Varchar(15) NOT NULL,
host Char(31) NOT NULL DEFAULT dynamic,
language Char(5),
deny Varchar(95),
permit Varchar(95),
qualify Varchar(4) DEFAULT yes,
disallow Varchar(100) NOT NULL DEFAULT all,
allow Varchar(100) NOT NULL DEFAULT ulaw,
ipaddr Varchar(15),
port Int DEFAULT 0,
regseconds Int DEFAULT 0,
service_id Int NOT NULL,
context Char(20) NOT NULL,
UNIQUE (mailbox),
UNIQUE (qualify),
Primary Key (entitlement_id,name,accountcode,service_id,context),
Foreign Key (entitlement_id,service_id,accountcode,context) references webapp_entitlement (entitlement_id,service_id,accountcode,context) on delete restrict on update restrict
) ENGINE = MyISAM;
Create table ast_extensions (
exten_id Int NOT NULL,
serial Serial AUTO_INCREMENT,
exten Varchar(20) NOT NULL,
context Varchar(40) NOT NULL,
priority Int NOT NULL,
app Varchar(40) NOT NULL,
appdata Varchar(255) NOT NULL,
UNIQUE (serial),
Primary Key (exten_id,exten,context,priority),
Foreign Key (exten_id,exten) references ast_ddi_numbers (exten_id,exten) on delete restrict on update restrict
) ENGINE = MyISAM;
Create table ast_config (
id Int NOT NULL AUTO_INCREMENT,
cat_metric Int NOT NULL DEFAULT 0,
var_metric Int NOT NULL DEFAULT 0,
commented Int NOT NULL DEFAULT 0,
filename Varchar(128) NOT NULL,
category Varchar(128) NOT NULL DEFAULT default,
var_name Varchar(128) NOT NULL,
var_val Varchar(128) NOT NULL,
Primary Key (id,commented,filename)
) ENGINE = MyISAM;
Create table ast_sip_buddies (
entitlement_id Int NOT NULL,
name Varchar(80) NOT NULL,
accountcode Int NOT NULL,
mailbox Int NOT NULL AUTO_INCREMENT,
amaflags Varchar(13) DEFAULT NULL,
callgroup Varchar(10) DEFAULT NULL,
callerid Varchar(80) DEFAULT NULL,
canreinvite Char(3) DEFAULT yes,
defaultip Varchar(15) DEFAULT NULL,
dtmfmode Varchar(7) DEFAULT NULL,
fromuser Varchar(80) DEFAULT NULL,
fromdomain Varchar(20),
fullcontact Varchar(80) DEFAULT NULL,
host Varchar(31) NOT NULL,
insecure Varchar(4) DEFAULT NULL,
language Char(2) DEFAULT NULL,
md5secret Varchar(80) DEFAULT NULL,
nat Varchar(5) DEFAULT no,
deny Varchar(95) DEFAULT NULL,
permit Varchar(95) DEFAULT NULL,
mask Varchar(95) DEFAULT NULL,
pickupgroup Varchar(10) DEFAULT NULL,
port Varchar(5) NOT NULL,
qualify Char(3) DEFAULT NULL,
restrictcid Char(1) DEFAULT NULL,
rtptimeout Char(3) DEFAULT NULL,
rtpholdtimeout Char(3) DEFAULT NULL,
secret Varchar(80) DEFAULT NULL,
type Varchar(6) NOT NULL DEFAULT friend,
username Varchar(80) NOT NULL,
disallow Varchar(100) DEFAULT all,
allow Varchar(100) DEFAULT 'g729;ilbc;gsm;ulaw;alaw',
musiconhold Varchar(100) DEFAULT NULL,
regseconds Int NOT NULL DEFAULT 0,
ipaddr Varchar(15) NOT NULL,
regexten Varchar(80) NOT NULL,
cancelforward Char(3) DEFAULT yes,
setvar Varchar(100) NOT NULL,
service_id Int NOT NULL,
context Char(20) NOT NULL,
UNIQUE (name),
UNIQUE (mailbox),
Primary Key (entitlement_id,name,accountcode,service_id,context),
unique name_2 (name),
Foreign Key (entitlement_id,service_id,accountcode,context) references webapp_entitlement (entitlement_id,service_id,accountcode,context) on delete restrict on update restrict
) ENGINE = MyISAM;
Create table webapp_user (
uid Int NOT NULL AUTO_INCREMENT,
username Varchar(255) NOT NULL,
password Varchar(255) NOT NULL,
firstname Varchar(255),
surname Varchar(255),
active Tinyint,
session Int,
group_ Int,
webapp_customer_id Int NOT NULL,
address_id Int NOT NULL,
localisation_id Int NOT NULL,
UNIQUE (uid),
UNIQUE (username),
Primary Key (uid,username,webapp_customer_id,address_id,localisation_id),
Foreign Key (webapp_customer_id,address_id,localisation_id) references webapp_Customer (webapp_customer_id,address_id,localisation_id) on delete restrict on update restrict
) ENGINE = MyISAM
COMMENT = 'Application user';
Create table webapp_apps (
uid Int NOT NULL AUTO_INCREMENT,
appname Varchar(255) NOT NULL,
UNIQUE (uid),
Primary Key (uid)
) ENGINE = MyISAM;
Create table webapp_groups (
uid Int NOT NULL AUTO_INCREMENT,
groupname Varchar(255) NOT NULL,
Primary Key (uid)
) ENGINE = MyISAM;
Create table webapp_histories (
uid Int NOT NULL AUTO_INCREMENT,
user_ Int NOT NULL DEFAULT 0,
signed_in Datetime NOT NULL,
last_accessed Datetime,
signed_out Datetime,
Primary Key (uid)
) ENGINE = MyISAM;
Create table webapp_permissions (
uid Int NOT NULL AUTO_INCREMENT,
user_ Int NOT NULL DEFAULT 0,
app Int,
role Int,
Primary Key (uid)
) ENGINE = MyISAM;
Create table webapp_roles (
uid Int NOT NULL AUTO_INCREMENT,
rolename Varchar(20) NOT NULL,
Primary Key (uid)
) ENGINE = MyISAM;
Create table ast_ddi_numbers (
exten_id Int NOT NULL AUTO_INCREMENT,
exten Varchar(20) NOT NULL,
geo Char(20) NOT NULL,
telco Char(20) NOT NULL,
UNIQUE (exten_id),
UNIQUE (exten),
Primary Key (exten_id,exten)
) ENGINE = MyISAM;
Create table webapp_entitlement (
entitlement_id Int NOT NULL AUTO_INCREMENT,
service_id Int NOT NULL,
accountcode Int NOT NULL AUTO_INCREMENT,
exten Varchar(20) NOT NULL,
username Char(20) NOT NULL,
context Char(20) NOT NULL,
UNIQUE (accountcode),
Index AI_accountcode (accountcode),
Primary Key (entitlement_id,service_id,accountcode,context),
Foreign Key (service_id) references ast_service_options (service_id) on delete restrict on update restrict
) ENGINE = MyISAM;
Create table ast_service_options (
service_id Int NOT NULL,
service Char(20) NOT NULL,
startdate Timestamp NOT NULL,
expirationdate Timestamp NOT NULL,
creationdate Timestamp NOT NULL,
description Mediumtext,
service_users Varchar(20) NOT NULL,
monthly_rate Float(8,2) NOT NULL,
createdby Char(20) NOT NULL,
Primary Key (service_id)
) ENGINE = MyISAM;
Create table webapp_Customer (
webapp_customer_id Int NOT NULL AUTO_INCREMENT,
registered_name Char(40) NOT NULL,
company_reg_no Varchar(20) NOT NULL,
company_reg_date Date,
business_type Char(20) NOT NULL,
vat_tax_no Varchar(40),
employees Int,
address_id Int NOT NULL,
localisation_id Int NOT NULL,
Primary Key (webapp_customer_id,address_id,localisation_id),
Foreign Key (address_id,localisation_id) references webapp_baddress (address_id,localisation_id) on delete restrict on update restrict
) ENGINE = MyISAM;
Create table webapp_localisation (
localisation_id Int NOT NULL AUTO_INCREMENT,
country Varchar(40),
city Varchar(40),
timezone Int,
Primary Key (localisation_id)
) ENGINE = MyISAM;
Create table webapp_baddress (
address_id Int NOT NULL AUTO_INCREMENT,
localisation_id Int NOT NULL,
building_no Varchar(20),
address_1 Varchar(80),
address_2 Varchar(80),
zip_post Varchar(20),
Primary Key (address_id,localisation_id),
Foreign Key (localisation_id) references webapp_localisation (localisation_id) on delete restrict on update restrict
) ENGINE = MyISAM;
Create table entitlement_to_user (
entitlement_id Int NOT NULL,
service_id Int NOT NULL,
accountcode Int NOT NULL,
context Char(20) NOT NULL,
uid Int NOT NULL,
username Varchar(255) NOT NULL,
webapp_customer_id Int NOT NULL,
address_id Int NOT NULL,
localisation_id Int NOT NULL,
eu_id Int NOT NULL,
Primary Key (entitlement_id,service_id,accountcode,context,uid,username,webapp_customer_id,address_id,localisation_id),
Foreign Key (entitlement_id,service_id,accountcode,context) references webapp_entitlement (entitlement_id,service_id,accountcode,context) on delete restrict on update restrict,
Foreign Key (uid,username,webapp_customer_id,address_id,localisation_id) references webapp_user (uid,username,webapp_customer_id,address_id,localisation_id) on delete restrict on update restrict
) ENGINE = MyISAM;