Carrier-Sensitive Routing User Guide
Data Schema
Download this chapter
Data SchemaData Schema
give_us_feedback

Table of Contents

Data Schema

Data Schema

This appendix describes the following:

Configuration Data Schema

BEGIN WORK;

SET CONSTRAINTS ALL DEFERRED;

CREATE TABLE ConfigurationData (

ActiveDataset text NOT NULL DEFAULT '_1' CONSTRAINT

ActiveDatasetSuffixInvalid

CHECK (
lower(ActiveDataset) = '_1' OR lower(ActiveDataset) = '_2'
),
LoggingLevel text NOT NULL DEFAULT 'Error' CONSTRAINT

LoggingLevelInvalid

CHECK (
lower(LoggingLevel) = 'error' OR
lower(LoggingLevel) = 'info' OR
lower(LoggingLevel) = 'debug'
),
CSR_ID varchar(64) NOT NULL,

CSR_Version text,

GKTMP_Version integer,

Priority integer NOT NULL DEFAULT 1CONSTRAINT

CSRPriorityInvalid

CHECK (Priority >= 1),

ListenPort integer CONSTRAINT CSRListenPortInvalid

CHECK (ListenPort > 0 AND ListenPort <= 32767),

ThreadCount integer NOT NULL DEFAULT 10,

MaxSizeOfQueue integer NOT NULL DEFAULT 5000,



TestRoutesAccountingEnabled boolean NOT NULL DEFAULT FALSE,

RejectSrcCarrierInactive boolean NOT NULL DEFAULT FALSE,

RejectNullSrcCarrier boolean NOT NULL DEFAULT FALSE,

RejectSrcCarrierUnknown boolean NOT NULL DEFAULT FALSE,

RejectDestinationUnknown boolean NOT NULL DEFAULT FALSE,

RejectSrcPercentage boolean NOT NULL DEFAULT FALSE,

RejectSrcUnits boolean NOT NULL DEFAULT FALSE,

RejectNoDstCarrier boolean NOT NULL DEFAULT FALSE

);

CREATE TABLE GateKeepers (

GKKey integer NOT NULL PRIMARY KEY,

GKID varchar(64) NOT NULL UNIQUE,

GK_IP inet NOT NULL,

GK_Port integer NOT NULL CONSTRAINT GK_PortInvalid

CHECK (GK_Port > 0 AND GK_Port <= 32767),

ListenARQ boolean NOT NULL DEFAULT FALSE,

ListenLRQ boolean NOT NULL DEFAULT FALSE,

ListenIRR boolean NOT NULL DEFAULT FALSE,

ListenDRQ boolean NOT NULL DEFAULT FALSE,

ListenRRQ boolean NOT NULL DEFAULT FALSE,

ListenURQ boolean NOT NULL DEFAULT FALSE,

ListenLCF boolean NOT NULL DEFAULT FALSE,

ListenLRJ boolean NOT NULL DEFAULT FALSE,

ListenRAI boolean NOT NULL DEFAULT FALSE,

ListenBRQ boolean NOT NULL DEFAULT FALSE,



ConnectionState text NOT NULL CONSTRAINT ConnectionStateInvalid

CHECK (

lower(ConnectionState) = 'connect' OR

lower(ConnectionState) = 'disconnect'

)

);

CREATE TABLE TestRoutes (

TestKey integer NOT NULL PRIMARY KEY,

TestTime time,

TestDate date,

DatasetID text DEFAULT '_1' CONSTRAINT DatasetIDInvalid

CHECK (

lower(DatasetID) = '_1' OR lower(DatasetID) = '_2'

),

ANI varchar(64),

DNIS varchar(64),

SrcCarrierID varchar(64),

DstCarrierID varchar(64),

RunMe boolean NOT NULL DEFAULT FALSE,

HomeCarrierID varchar(64),

ResultCode text

);

CREATE TABLE TestOutputs (

TestKey integer NOT NULL,

SelectOrder integer NOT NULL,

DstCarrierID varchar(64) NOT NULL,

ListKey integer NOT NULL,

IsSelected boolean NOT NULL DEFAULT FALSE

);

CREATE TABLE LogEntries (

TestKey integer NOT NULL,

EntryOrder integer NOT NULL,

LogString varchar(1024) NOT NULL

);

CREATE TABLE StringResources (

ResourceKey integer NOT NULL PRIMARY KEY,

ResourceString text NOT NULL

);

CREATE TABLE Filters (

FilterKey integer NOT NULL PRIMARY KEY,

GKID varchar(64) NOT NULL,

MsgType text NOT NULL CONSTRAINT MsgTypeInvalid

CHECK (

lower(MsgType) = 'arq' OR

lower(MsgType) = 'lrq' OR

lower(MsgType) = 'rrq' OR

lower(MsgType) = 'urq' OR

lower(MsgType) = 'lcf' OR

lower(MsgType) = 'lrj' OR

lower(MsgType) = 'drq' OR

lower(MsgType) = 'brq' OR

lower(MsgType) = 'irr' OR

lower(MsgType) = 'rai'

),

FilterType text NOT NULL CONSTRAINT FilterTypeInvalid

CHECK (

lower(FilterType) = 'endpoint' OR

lower(FilterType) = 'supportedprefix' OR

lower(FilterType) = 'destinationinformation' OR

lower(FilterType) = 'remoteextensionaddress' OR

lower(FilterType) = 'redirectreason' OR

lower(FilterType) = 'answercall' OR

lower(FilterType) = 'notificationonly'

),

Value text NOT NULL

);

ALTER TABLE TestOutputs ADD FOREIGN KEY (TestKey) REFERENCES TestRoutes (TestKey) DEFERRABLE;

ALTER TABLE LogEntries ADD FOREIGN KEY (TestKey) REFERENCES TestRoutes (TestKey) DEFERRABLE;

ALTER TABLE Filters ADD FOREIGN KEY (GKID) REFERENCES GateKeepers (GKID) DEFERRABLE;

COMMIT;

Dataset Schema

BEGIN WORK;

SET CONSTRAINTS ALL DEFERRED;

CREATE TABLE Carriers (

CarrierKey integer NOT NULL PRIMARY KEY CONSTRAINT

CarrierKeyNotZero

CHECK (CarrierKey <> 0),

CarrierName varchar(64) NOT NULL,

CarrierType text NOT NULL

CONSTRAINT CarrierTypeInvalid CHECK (

lower(CarrierType) = 'home' OR

lower(CarrierType) = 'itsp' OR

lower(CarrierType) = 'tdm'

),

CarrierID varchar(64) NOT NULL UNIQUE,

MaxIngressUnits integer CONSTRAINT MaxIngressUnitsInvalid

CHECK (MaxIngressUnits >= 0),

MaxEgressUnits integer CONSTRAINT MaxEgressUnitsInvalid

CHECK (MaxEgressUnits >= 0),

RuleKey integer,

ListKey integer NOT NULL,

isActive boolean NOT NULL DEFAULT TRUE

);

CREATE TABLE Carriers_1 () INHERITS (Carriers);

CREATE TABLE Carriers_2 () INHERITS (Carriers);

CREATE TABLE MatchPatterns (

PatternKey integer NOT NULL PRIMARY KEY CONSTRAINT

PatternKeyNotZero

CHECK (PatternKey <> 0),

Pattern varchar(64) NOT NULL UNIQUE,

RouteKey integer

);

CREATE TABLE MatchPatterns_1 () INHERITS (MatchPatterns);

CREATE TABLE MatchPatterns_2 () INHERITS (MatchPatterns);

CREATE TABLE Routes (

RouteKey integer NOT NULL PRIMARY KEY CONSTRAINT

RouteKeyNotZero

CHECK (RouteKey <> 0),

RouteName text,

RuleKey integer

);

CREATE TABLE Routes_1 () INHERITS (Routes);

CREATE TABLE Routes_2 () INHERITS (Routes);

CREATE TABLE Rules (

RuleKey integer NOT NULL PRIMARY KEY CONSTRAINT

RuleKeyNotZero

CHECK (RuleKey <> 0),

RuleName text,

IsDefaultRule boolean NOT NULL DEFAULT FALSE,

MinQoS integer CONSTRAINT MinQoSInvalid

CHECK (MinQoS >= 1 AND MinQoS <= 9),

PreferredCarrierKey integer,



RejectMaxOriginationPercentage boolean NOT NULL DEFAULT FALSE,

RejectMaxIngressUnits boolean NOT NULL DEFAULT FALSE,

RejectMaxIngressUnitsPerRoute boolean NOT NULL DEFAULT FALSE,

RejectEgressCostMoreThanIngress boolean NOT NULL DEFAULT FALSE,

RejectInsufficientQoS boolean NOT NULL DEFAULT FALSE,

RejectMaxEgressUnits boolean NOT NULL DEFAULT FALSE,

RejectMaxEgressUnitsPerRoute boolean NOT NULL DEFAULT FALSE,

PreventITSPtoITSP boolean NOT NULL DEFAULT FALSE,



SelectMinCostPerEgressRoute integer NOT NULL DEFAULT 0 CONSTRAINT

SelMinCostInvalid

CHECK (

SelectMinCostPerEgressRoute >= 0 AND

SelectMinCostPerEgressRoute <= 5

),

SelectBestQoS integer NOT NULL DEFAULT 0 CONSTRAINT

SelBestQoSInvalid

CHECK (

SelectBestQoS >= 0 AND

SelectBestQoS <= 5

),

SelectSameCarrier integer NOT NULL DEFAULT 0 CONSTRAINT

SelSameCarrierInvalid

CHECK (

SelectSameCarrier >= 0 AND

SelectSameCarrier <= 5

),

SelectPreferredCarrier integer NOT NULL DEFAULT 0 CONSTRAINT

SelPreferredCarrierInvalid

CHECK (

SelectPreferredCarrier >= 0 AND

SelectPreferredCarrier <= 5

),

SelectPercentageEgress integer NOT NULL DEFAULT 0 CONSTRAINT

SelPercentageEgressInvalid

CHECK (

SelectPercentageEgress >= 0 AND

SelectPercentageEgress <= 5

)

);

CREATE TABLE Rules_1 () INHERITS (Rules);

CREATE TABLE Rules_2 () INHERITS (Rules);

CREATE TABLE Contacts (

ContactKey integer NOT NULL PRIMARY KEY CONSTRAINT

ContactKeyNotZero

CHECK (ContactKey <> 0),

RASAddress varchar(64) NOT NULL,

AddressType text NOT NULL CONSTRAINT ContactAddressTypeInvalid

CHECK (

lower(AddressType) = 'ipv4' OR

lower(AddressType) = 'gkid' OR

lower(AddressType) = 'dnsname'

),

Cost integer CONSTRAINT CostInvalid

CHECK (Cost >= 0),

Priority integer CONSTRAINT PriorityInvalid

CHECK (Priority >= 0)

);

CREATE TABLE Contacts_1 () INHERITS (Contacts);

CREATE TABLE Contacts_2 () INHERITS (Contacts);

CREATE TABLE Lists (

ListKey integer NOT NULL PRIMARY KEY CONSTRAINT

ListKeyNotZero

CHECK (ListKey <> 0),

ListName text

);

CREATE TABLE Lists_1 () INHERITS (Lists);

CREATE TABLE Lists_2 () INHERITS (Lists);

CREATE TABLE ContactLists (

ListKey integer NOT NULL,

ContactKey integer NOT NULL

);

CREATE TABLE ContactLists_1 () INHERITS (ContactLists);

CREATE TABLE ContactLists_2 () INHERITS (ContactLists);

CREATE TABLE RouteAttributes (

AttributeKey integer NOT NULL PRIMARY KEY CONSTRAINT

AttributeKeyNotZero

CHECK (AttributeKey <> 0),

AttributeName text,

CarrierKey integer NOT NULL,

DaypartType text NOT NULL CONSTRAINT DaypartInvalid

CHECK (

lower(DaypartType) = 'absolute' OR

lower(DaypartType) = 'weekly' OR

lower(DaypartType) = 'daily' OR

lower(DaypartType) = 'always'

),

WeekDay integer CONSTRAINT WeekdayInvalid

CHECK (Weekday >=0 AND Weekday <= 6),

StartTime time,

EndTime time CONSTRAINT TimeRangeInvalid

CHECK (

(StartTime ISNULL AND EndTime ISNULL AND

lower(DaypartType) = 'always') OR

(StartTime NOTNULL AND EndTime NOTNULL AND

(StartTime < EndTime) OR

(

lower(DaypartType) = 'absolute' AND (StartDate <

EndDate) AND

(lower(DaypartType) != 'weekly' OR WeekDay NOTNULL)

)

)),

StartDate date,

EndDate date,

Cost integer CONSTRAINT CostInvalid

CHECK (Cost >= 1),

MaxUnits integer CONSTRAINT MaxUnitsInvalid

CHECK (MaxUnits >= 0),

MaxPercentage integer CONSTRAINT MaxPercentageInvalid

CHECK (MaxPercentage >= 1 AND MaxPercentage <=

100)

);

CREATE TABLE IngressRouteAttributes_1 () INHERITS (RouteAttributes);

CREATE TABLE IngressRouteAttributes_2 () INHERITS (RouteAttributes);

CREATE TABLE EgressRouteAttributes (

QoS integer CONSTRAINT QoSInvalid

CHECK (QoS >= 1 and QoS <= 9),

ListKey integer

) INHERITS (RouteAttributes);

CREATE TABLE EgressRouteAttributes_1 () INHERITS (EgressRouteAttributes);

CREATE TABLE EgressRouteAttributes_2 () INHERITS (EgressRouteAttributes);

CREATE TABLE QoS (

IngressCarrierKey integer NOT NULL,

EgressCarrierKey integer NOT NULL,

RouteKey integer NOT NULL,

RelativeQoS integer NOT NULL CONSTRAINT RelativeQoSInvalid

CHECK (RelativeQoS >= 1 AND RelativeQoS <=

9)

);

CREATE TABLE QoS_1 () INHERITS (QoS);

CREATE TABLE QoS_2 () INHERITS (QoS);

CREATE TABLE RouteParent (

RouteKey integer NOT NULL,

AttributeKey integer NOT NULL

);

CREATE TABLE IngressRoutes_1 () INHERITS (RouteParent);

CREATE TABLE IngressRoutes_2 () INHERITS (RouteParent);

CREATE TABLE EgressRoutes_1 () INHERITS (RouteParent);

CREATE TABLE EgressRoutes_2 () INHERITS (RouteParent);

COMMIT;

BEGIN WORK;

CREATE UNIQUE INDEX Carriers_1_PKey ON Carriers_1 (CarrierKey);

CREATE UNIQUE INDEX Carriers_2_PKey ON Carriers_2 (CarrierKey);

CREATE UNIQUE INDEX Carriers_1_ID ON Carriers_1 (CarrierID);

CREATE UNIQUE INDEX Carriers_2_ID ON Carriers_2 (CarrierID);

CREATE UNIQUE INDEX Patterns_1_PKey ON MatchPatterns_1 (PatternKey);

CREATE UNIQUE INDEX Patterns_2_PKey ON MatchPatterns_2 (PatternKey);

CREATE UNIQUE INDEX Routes_1_PKey ON Routes_1 (RouteKey);

CREATE UNIQUE INDEX Routes_2_PKey ON Routes_2 (RouteKey);

CREATE UNIQUE INDEX Rules_1_PKey ON Rules_1 (RuleKey);

CREATE UNIQUE INDEX Rules_2_PKey ON Rules_2 (RuleKey);

CREATE UNIQUE INDEX Contacts_1_PKey ON Contacts_1 (ContactKey);

CREATE UNIQUE INDEX Contacts_2_PKey ON Contacts_2 (ContactKey);

CREATE UNIQUE INDEX Lists_1_PKey ON Lists_1 (ListKey);

CREATE UNIQUE INDEX Lists_2_PKey ON Lists_2 (ListKey);

CREATE UNIQUE INDEX Match_1_Pattern ON MatchPatterns_1(Pattern);

CREATE UNIQUE INDEX Match_2_Pattern ON MatchPatterns_2(Pattern);

CREATE UNIQUE INDEX IRA_1_PKey ON IngressRouteAttributes_1 (AttributeKey);

CREATE UNIQUE INDEX IRA_2_PKey ON IngressRouteAttributes_2 (AttributeKey);

CREATE UNIQUE INDEX ECA_1_PKey ON EgressRouteAttributes_1 (AttributeKey);

CREATE UNIQUE INDEX ECA_2_PKey ON EgressRouteAttributes_2 (AttributeKey);

CREATE UNIQUE INDEX ContactLists_1_Key ON ContactLists_1 (ListKey, ContactKey);

CREATE UNIQUE INDEX ContactLists_2_Key ON ContactLists_2 (ListKey, ContactKey);

CREATE UNIQUE INDEX QoS_1_Key ON QoS_1 (IngressCarrierKey, EgressCarrierKey, RouteKey);

CREATE UNIQUE INDEX QoS_2_Key ON QoS_2 (IngressCarrierKey, EgressCarrierKey, RouteKey);

CREATE UNIQUE INDEX IngressRoutes_1_Key ON IngressRoutes_1 (RouteKey, AttributeKey);

CREATE UNIQUE INDEX IngressRoutes_2_Key ON IngressRoutes_2 (RouteKey, AttributeKey);

CREATE UNIQUE INDEX EgressRoutes_1_Key ON EgressRoutes_1 (RouteKey, AttributeKey);

CREATE UNIQUE INDEX EgressRoutes_2_Key ON EgressRoutes_2 (RouteKey, AttributeKey);

COMMIT;

BEGIN WORK;

ALTER TABLE Carriers_1 ADD FOREIGN KEY (RuleKey) REFERENCES Rules_1 (RuleKey) DEFERRABLE;

ALTER TABLE Carriers_1 ADD FOREIGN KEY (ListKey) REFERENCES Lists_1 (ListKey) DEFERRABLE;

ALTER TABLE Carriers_2 ADD FOREIGN KEY (RuleKey) REFERENCES Rules_2 (RuleKey) DEFERRABLE;

ALTER TABLE Carriers_2 ADD FOREIGN KEY (ListKey) REFERENCES Lists_2 (ListKey) DEFERRABLE;

ALTER TABLE ContactLists_1 ADD FOREIGN KEY (ListKey) REFERENCES Lists_1 (ListKey) DEFERRABLE;

ALTER TABLE ContactLists_1 ADD FOREIGN KEY (ContactKey) REFERENCES Contacts_1 (ContactKey) DEFERRABLE;

ALTER TABLE ContactLists_2 ADD FOREIGN KEY (ListKey) REFERENCES Lists_2 (ListKey) DEFERRABLE;

ALTER TABLE ContactLists_2 ADD FOREIGN KEY (ContactKey) REFERENCES Contacts_2 (ContactKey) DEFERRABLE;

ALTER TABLE Routes_1 ADD FOREIGN KEY (RuleKey) REFERENCES Rules_1 (RuleKey) DEFERRABLE;

ALTER TABLE Routes_2 ADD FOREIGN KEY (RuleKey) REFERENCES Rules_2 (RuleKey) DEFERRABLE;

ALTER TABLE MatchPatterns_1 ADD FOREIGN KEY (RouteKey) REFERENCES Routes_1 (RouteKey) DEFERRABLE;

ALTER TABLE MatchPatterns_2 ADD FOREIGN KEY (RouteKey) REFERENCES Routes_2 (RouteKey) DEFERRABLE;

ALTER TABLE Rules_1 ADD FOREIGN KEY (PreferredCarrierKey) REFERENCES Carriers_1 (CarrierKey) DEFERRABLE;

ALTER TABLE Rules_2 ADD FOREIGN KEY (PreferredCarrierKey) REFERENCES Carriers_2 (CarrierKey) DEFERRABLE;

ALTER TABLE IngressRouteAttributes_1 ADD FOREIGN KEY (CarrierKey) REFERENCES Carriers_1 (CarrierKey) DEFERRABLE;

ALTER TABLE IngressRouteAttributes_2 ADD FOREIGN KEY (CarrierKey) REFERENCES Carriers_2 (CarrierKey) DEFERRABLE;

ALTER TABLE EgressRouteAttributes_1 ADD FOREIGN KEY (CarrierKey) REFERENCES Carriers_1 (CarrierKey) DEFERRABLE;

ALTER TABLE EgressRouteAttributes_1 ADD FOREIGN KEY (ListKey) REFERENCES Lists_1 (ListKey) DEFERRABLE;

ALTER TABLE EgressRouteAttributes_2 ADD FOREIGN KEY (CarrierKey) REFERENCES Carriers_2 (CarrierKey) DEFERRABLE;

ALTER TABLE EgressRouteAttributes_2 ADD FOREIGN KEY (ListKey) REFERENCES Lists_2 (ListKey) DEFERRABLE;

ALTER TABLE QoS_1 ADD FOREIGN KEY (IngressCarrierKey) REFERENCES Carriers_1 (CarrierKey) DEFERRABLE;

ALTER TABLE QoS_1 ADD FOREIGN KEY (EgressCarrierKey) REFERENCES Carriers_1 (CarrierKey) DEFERRABLE;

ALTER TABLE QoS_1 ADD FOREIGN KEY (RouteKey) REFERENCES Routes_1 (RouteKey) DEFERRABLE;

ALTER TABLE QoS_2 ADD FOREIGN KEY (IngressCarrierKey) REFERENCES Carriers_2 (CarrierKey) DEFERRABLE;

ALTER TABLE QoS_2 ADD FOREIGN KEY (EgressCarrierKey) REFERENCES Carriers_2 (CarrierKey) DEFERRABLE;

ALTER TABLE QoS_2 ADD FOREIGN KEY (RouteKey) REFERENCES Routes_2 (RouteKey) DEFERRABLE;

ALTER TABLE IngressRoutes_1 ADD FOREIGN KEY (RouteKey) REFERENCES Routes_1 (RouteKey) DEFERRABLE;

ALTER TABLE IngressRoutes_1 ADD FOREIGN KEY (AttributeKey) REFERENCES IngressRouteAttributes_1 (AttributeKey) DEFERRABLE;

ALTER TABLE IngressRoutes_2 ADD FOREIGN KEY (RouteKey) REFERENCES Routes_2 (RouteKey) DEFERRABLE;

ALTER TABLE IngressRoutes_2 ADD FOREIGN KEY (AttributeKey) REFERENCES IngressRouteAttributes_2 (AttributeKey) DEFERRABLE;

ALTER TABLE EgressRoutes_1 ADD FOREIGN KEY (RouteKey) REFERENCES Routes_1 (RouteKey) DEFERRABLE;

ALTER TABLE EgressRoutes_1 ADD FOREIGN KEY (AttributeKey) REFERENCES EgressRouteAttributes_1 (AttributeKey) DEFERRABLE;

ALTER TABLE EgressRoutes_2 ADD FOREIGN KEY (RouteKey) REFERENCES Routes_2 (RouteKey) DEFERRABLE;

ALTER TABLE EgressRoutes_2 ADD FOREIGN KEY (AttributeKey) REFERENCES EgressRouteAttributes_2 (AttributeKey) DEFERRABLE;

\echo Foreign Keys done...

COMMIT;