This section contains the database structure needed to develop customer-provided applications on the query servers.
-- -- Create NpacRegion table -- -- One NpacRegion defines the configuration of the primary and secondary NPAC. -- CREATE TABLE NpacRegion ( -- Region name region VARCHAR(40) NOT NULL, -- SMS Name defined by NPAC npacSmsName TINYBLOB, -- OSI address of LSMS lsmsPsel TINYBLOB, lsmsSsel TINYBLOB, lsmsTsel TINYBLOB, lsmsNsap TINYBLOB, -- OSI address of primary NPAC primaryNpacPsel TINYBLOB, primaryNpacSsel TINYBLOB, primaryNpacTsel TINYBLOB, primaryNpacNsap TINYBLOB, primaryNpacFtpAddress TINYBLOB, -- OSI address of secondary NPAC secondaryNpacPsel TINYBLOB, secondaryNpacSsel TINYBLOB, secondaryNpacTsel TINYBLOB, secondaryNpacNsap TINYBLOB, secondaryNpacFtpAddress TINYBLOB, -- Region is active active BOOL NOT NULL DEFAULT 0, -- Component Info (stored as CSV string) componentInfo BLOB NOT NULL, -- Contact Info (stored as CSV string) contactInfo BLOB NOT NULL, -- Last changed timestamp set by npacagent lastChangedTimestamp CHAR(14) NOT NULL, -- Default now -- Current npac in use set by npacagent currentNpac ENUM("Primary", "Secondary") DEFAULT "Primary", -- Region name is primary key PRIMARY KEY (region) ) TYPE = MyIsam; INSERT INTO NpacRegion (region, npacSmsName, lsmsPsel, lsmsSsel, lsmsTsel, lsmsNsap, primaryNpacPsel, primaryNpacSsel, primaryNpacTsel, primaryNpacNsap, primaryNpacFtpAddress, secondaryNpacPsel, secondaryNpacSsel, secondaryNpacTsel, secondaryNpacNsap, secondaryNpacFtpAddress, componentInfo, contactInfo, lastChangedTimestamp) VALUES ("Canada", "Region8 NPAC Canada", "cw7", "cw7", "", "rk6", "", "", "", "0x00000000", "0.0.0.0", "", "", "", "0x00000000", "0.0.0.0", '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"', '"Lsms Admin","admin@tekelec.com","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1 234","9195551234"', DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")), ("MidAtlantic", "Mid-Atlantic Regional NPAC SMS", "cw1", "cw1", "", "rk6", "", "", "", "0x00000000", "0.0.0.0", "", "", "", "0x00000000", "0.0.0.0", '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"', '"Lsms Admin","admin@tekelec.com","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1 234","9195551234"', DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")), ("Midwest", "Midwest Regional NPAC SMS", "cw0", "cw0", "", "rk6", "", "", "", "0x00000000", "0.0.0.0", "", "", "", "0x00000000", "0.0.0.0", '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"', '"Lsms Admin","admin@tekelec.com","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1 234","9195551234"', DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")), ("Northeast", "Northeast Regional NPAC SMS", "cw2", "cw2", "", "rk6", "", "", "", "0x00000000", "0.0.0.0", "", "", "", "0x00000000", "0.0.0.0", '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"', '"Lsms Admin","admin@tekelec.com","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1 234","9195551234"', DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")), ("Southeast", "Southeast Regional NPAC SMS", "cw3", "cw3", "", "rk6", "", "", "", "0x00000000", "0.0.0.0", "", "", "", "0x00000000", "0.0.0.0", '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"', '"Lsms Admin","admin@tekelec.com","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1 234","9195551234"', DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")), ("Southwest", "Southwest Regional NPAC SMS", "cw4", "cw4", "", "rk6", "", "", "", "0x00000000", "0.0.0.0", "", "", "", "0x00000000", "0.0.0.0", '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"', '"Lsms Admin","admin@tekelec.com","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1 234","9195551234"', DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")), ("WestCoast", "WestCoast Regional NPAC SMS", "cw6", "cw6", "", "rk6", "", "", "", "0x00000000", "0.0.0.0", "", "", "", "0x00000000", "0.0.0.0", '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"', '"Lsms Admin","admin@tekelec.com","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1 234","9195551234"', DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")), ("Western", "Western Regional NPAC SMS", "cw5", "cw5", "", "rk6", "", "", "", "0x00000000", "0.0.0.0", "", "", "", "0x00000000", "0.0.0.0", '"NPAC","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"', '"Lsms Admin","admin@tekelec.com","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1 234","9195551234"', DATE_FORMAT(NOW(), "%Y%m%d%h%i%s")); -- -- Create LsmsServiceProvider table -- CREATE TABLE LsmsServiceProvider ( -- The service provider id (Primary Key) spid CHAR(4) NOT NULL, -- Description of the service provider description CHAR(80) NOT NULL, -- Contact Info (stored as comma separated value string) contactInfo BLOB NOT NULL, -- Primary key is the spid PRIMARY KEY (spid) ) TYPE = MyIsam; -- -- Create LsmsUser table -- CREATE TABLE LsmsUser ( -- The user name (Primary Key) name CHAR(64) NOT NULL, -- Description of the service provider golden BOOL NOT NULL DEFAULT 0, -- The Assigned permission group groupName CHAR(4) NOT NULL, -- The assigned inactivity timeout inactivityTimeout CHAR(11) NOT NULL DEFAULT '-1', -- Primary key is the user name PRIMARY KEY (name) ) TYPE = MyIsam; -- Create default 'golden' users INSERT INTO LsmsUser (name, golden) VALUES('lsmsadm',1), ('lsmsuser',1), ('lsmsview',1), ('lsmsall',1), ('lsmsuext',1); -- -- Create GttGroup table -- CREATE TABLE GttGroup ( -- The group name (Primary Key) name CHAR(64) NOT NULL, -- Description of the GttGroup description CHAR(80) NOT NULL, -- Services in GttGroup are for storing default TT/SSN values -- AIN Service ain_set BOOL NOT NULL DEFAULT 0, ain_tt TINYINT UNSIGNED NOT NULL, ain_dpc CHAR(9) NOT NULL, ain_ssn CHAR(3) NOT NULL, ain_xlat TINYINT UNSIGNED NOT NULL, ain_ri TINYINT UNSIGNED NOT NULL, ain_ngt TINYINT UNSIGNED NOT NULL, ain_rgta BOOL NOT NULL, -- IN Service in_set BOOL NOT NULL DEFAULT 0, in_tt TINYINT UNSIGNED NOT NULL, in_dpc CHAR(9) NOT NULL, in_ssn CHAR(3) NOT NULL, in_xlat TINYINT UNSIGNED NOT NULL, in_ri TINYINT UNSIGNED NOT NULL, in_ngt TINYINT UNSIGNED NOT NULL, in_rgta BOOL NOT NULL, -- CLASS Service class_set BOOL NOT NULL DEFAULT 0, class_tt TINYINT UNSIGNED NOT NULL, class_dpc CHAR(9) NOT NULL, class_ssn CHAR(3) NOT NULL, class_xlat TINYINT UNSIGNED NOT NULL, class_ri TINYINT UNSIGNED NOT NULL, class_ngt TINYINT UNSIGNED NOT NULL, class_rgta BOOL NOT NULL, -- LIDB Service lidb_set BOOL NOT NULL DEFAULT 0, lidb_tt TINYINT UNSIGNED NOT NULL, lidb_dpc CHAR(9) NOT NULL, lidb_ssn CHAR(3) NOT NULL, lidb_xlat TINYINT UNSIGNED NOT NULL, lidb_ri TINYINT UNSIGNED NOT NULL, lidb_ngt TINYINT UNSIGNED NOT NULL, lidb_rgta BOOL NOT NULL, -- ISVM Service isvm_set BOOL NOT NULL DEFAULT 0, isvm_tt TINYINT UNSIGNED NOT NULL, isvm_dpc CHAR(9) NOT NULL, isvm_ssn CHAR(3) NOT NULL, isvm_xlat TINYINT UNSIGNED NOT NULL, isvm_ri TINYINT UNSIGNED NOT NULL, isvm_ngt TINYINT UNSIGNED NOT NULL, isvm_rgta BOOL NOT NULL, -- CNAM Service cnam_set BOOL NOT NULL DEFAULT 0, cnam_tt TINYINT UNSIGNED NOT NULL, cnam_dpc CHAR(9) NOT NULL, cnam_ssn CHAR(3) NOT NULL, cnam_xlat TINYINT UNSIGNED NOT NULL, cnam_ri TINYINT UNSIGNED NOT NULL, cnam_ngt TINYINT UNSIGNED NOT NULL, cnam_rgta BOOL NOT NULL, -- WSMSC Service wsmsc_set BOOL NOT NULL DEFAULT 0, wsmsc_tt TINYINT UNSIGNED NOT NULL, wsmsc_dpc CHAR(9) NOT NULL, wsmsc_ssn CHAR(3) NOT NULL, wsmsc_xlat TINYINT UNSIGNED NOT NULL, wsmsc_ri TINYINT UNSIGNED NOT NULL, wsmsc_ngt TINYINT UNSIGNED NOT NULL, wsmsc_rgta BOOL NOT NULL, -- Primary key is the group name PRIMARY KEY (name) ) TYPE = MyIsam; -- -- Create GttGroupSpid table -- -- This table is used to associate a GttGroup to an authorized -- LsmsServiceProvider. The many-many relationship between the two -- is stored by this table a group-spid combinations. -- CREATE TABLE GttGroupSpid ( -- Group name gttGroup CHAR(64) NOT NULL, -- Spid spid char(4) NOT NULL, -- Force GttGroup,LsmsServiceProvider combinations to be unique PRIMARY KEY (gttGroup, spid), -- Not used by MySql but included for documentation FOREIGN KEY (gttGroup) REFERENCES GttGroup(groupName), FOREIGN KEY (spid) REFERENCES LsmsServiceProvider(spid) ) TYPE = MyIsam; -- -- Create LsmsUserSpid table -- -- This table is used to associate a LsmsUser to an authorized -- LsmsServiceProvider. The many-many relationship between the two -- is stored by this table a group-spid combinations. -- CREATE TABLE LsmsUserSpid ( -- User name lsmsUser CHAR(64) NOT NULL, -- Spid spid CHAR(4) NOT NULL, -- Force LsmsUser,LsmsServiceProvider combinations to be unique PRIMARY KEY (lsmsUser, spid), -- Not used by MySql but included for documentation FOREIGN KEY (lsmsUser) REFERENCES LsmsUser(name), FOREIGN KEY (spid) REFERENCES LsmsServiceProvider(spid) ) TYPE = MyIsam; -- -- Create DefaultGTT Table -- CREATE TABLE DefaultGtt ( -- The group this DefaultGtt belongs to groupName CHAR(64) NOT NULL, -- Foreign key -- NPA-NXX of the DefaultGtt npanxx CHAR(6) NOT NULL, -- The SPID that created the DefaultGtt spid CHAR(4) NOT NULL, -- AIN Service ain_set BOOL NOT NULL DEFAULT 0, ain_tt TINYINT UNSIGNED NOT NULL, ain_dpc CHAR(9) NOT NULL, ain_ssn CHAR(3) NOT NULL, ain_xlat TINYINT UNSIGNED NOT NULL, ain_ri TINYINT UNSIGNED NOT NULL, ain_ngt TINYINT UNSIGNED NOT NULL, ain_rgta BOOL NOT NULL, -- IN Service in_set BOOL NOT NULL DEFAULT 0, in_tt TINYINT UNSIGNED NOT NULL, in_dpc CHAR(9) NOT NULL, in_ssn CHAR(3) NOT NULL, in_xlat TINYINT UNSIGNED NOT NULL, in_ri TINYINT UNSIGNED NOT NULL, in_ngt TINYINT UNSIGNED NOT NULL, in_rgta BOOL NOT NULL, -- CLASS Service class_set BOOL NOT NULL DEFAULT 0, class_tt TINYINT UNSIGNED NOT NULL, class_dpc CHAR(9) NOT NULL, class_ssn CHAR(3) NOT NULL, class_xlat TINYINT UNSIGNED NOT NULL, class_ri TINYINT UNSIGNED NOT NULL, class_ngt TINYINT UNSIGNED NOT NULL, class_rgta BOOL NOT NULL, -- LIDB Service lidb_set BOOL NOT NULL DEFAULT 0, lidb_tt TINYINT UNSIGNED NOT NULL, lidb_dpc CHAR(9) NOT NULL, lidb_ssn CHAR(3) NOT NULL, lidb_xlat TINYINT UNSIGNED NOT NULL, lidb_ri TINYINT UNSIGNED NOT NULL, lidb_ngt TINYINT UNSIGNED NOT NULL, lidb_rgta BOOL NOT NULL, -- ISVM Service isvm_set BOOL NOT NULL DEFAULT 0, isvm_tt TINYINT UNSIGNED NOT NULL, isvm_dpc CHAR(9) NOT NULL, isvm_ssn CHAR(3) NOT NULL, isvm_xlat TINYINT UNSIGNED NOT NULL, isvm_ri TINYINT UNSIGNED NOT NULL, isvm_ngt TINYINT UNSIGNED NOT NULL, isvm_rgta BOOL NOT NULL, -- CNAM Service cnam_set BOOL NOT NULL DEFAULT 0, cnam_tt TINYINT UNSIGNED NOT NULL, cnam_dpc CHAR(9) NOT NULL, cnam_ssn CHAR(3) NOT NULL, cnam_xlat TINYINT UNSIGNED NOT NULL, cnam_ri TINYINT UNSIGNED NOT NULL, cnam_ngt TINYINT UNSIGNED NOT NULL, cnam_rgta BOOL NOT NULL, -- WSMSC Service wsmsc_set BOOL NOT NULL DEFAULT 0, wsmsc_tt TINYINT UNSIGNED NOT NULL, wsmsc_dpc CHAR(9) NOT NULL, wsmsc_ssn CHAR(3) NOT NULL, wsmsc_xlat TINYINT UNSIGNED NOT NULL, wsmsc_ri TINYINT UNSIGNED NOT NULL, wsmsc_ngt TINYINT UNSIGNED NOT NULL, wsmsc_rgta BOOL NOT NULL, -- DefaultGtt npanxx's are unique within each group PRIMARY KEY (groupName, npanxx), -- Not used by MySql but included for documentation FOREIGN KEY (groupName) REFERENCES GttGroup(name) ) TYPE = MyIsam; -- -- Create OverrideGtt Table -- CREATE TABLE OverrideGtt ( -- The group this OverrideGtt belongs to groupName CHAR(64) NOT NULL, -- Foreign key -- LRN of the OverrideGtt lrn CHAR(10) NOT NULL, -- The SPID that created the OverrideGtt spid CHAR(4) NOT NULL, -- CLASS Service class_set BOOL NOT NULL DEFAULT 0, class_tt TINYINT UNSIGNED NOT NULL, class_dpc CHAR(9) NOT NULL, class_ssn CHAR(3) NOT NULL, class_xlat TINYINT UNSIGNED NOT NULL, class_ri TINYINT UNSIGNED NOT NULL, class_ngt TINYINT UNSIGNED NOT NULL, class_rgta BOOL NOT NULL, -- LIDB Service lidb_set BOOL NOT NULL DEFAULT 0, lidb_tt TINYINT UNSIGNED NOT NULL, lidb_dpc CHAR(9) NOT NULL, lidb_ssn CHAR(3) NOT NULL, lidb_xlat TINYINT UNSIGNED NOT NULL, lidb_ri TINYINT UNSIGNED NOT NULL, lidb_ngt TINYINT UNSIGNED NOT NULL, lidb_rgta BOOL NOT NULL, -- ISVM Service isvm_set BOOL NOT NULL DEFAULT 0, isvm_tt TINYINT UNSIGNED NOT NULL, isvm_dpc CHAR(9) NOT NULL, isvm_ssn CHAR(3) NOT NULL, isvm_xlat TINYINT UNSIGNED NOT NULL, isvm_ri TINYINT UNSIGNED NOT NULL, isvm_ngt TINYINT UNSIGNED NOT NULL, isvm_rgta BOOL NOT NULL, -- CNAM Service cnam_set BOOL NOT NULL DEFAULT 0, cnam_tt TINYINT UNSIGNED NOT NULL, cnam_dpc CHAR(9) NOT NULL, cnam_ssn CHAR(3) NOT NULL, cnam_xlat TINYINT UNSIGNED NOT NULL, cnam_ri TINYINT UNSIGNED NOT NULL, cnam_ngt TINYINT UNSIGNED NOT NULL, cnam_rgta BOOL NOT NULL, -- WSMSC Service wsmsc_set BOOL NOT NULL DEFAULT 0, wsmsc_tt TINYINT UNSIGNED NOT NULL, wsmsc_dpc CHAR(9) NOT NULL, wsmsc_ssn CHAR(3) NOT NULL, wsmsc_xlat TINYINT UNSIGNED NOT NULL, wsmsc_ri TINYINT UNSIGNED NOT NULL, wsmsc_ngt TINYINT UNSIGNED NOT NULL, wsmsc_rgta BOOL NOT NULL, -- OverrideGtt lrns are unique within each group PRIMARY KEY (groupName, lrn), -- Not used by MySql but included for documentation FOREIGN KEY (groupName) REFERENCES GttGroup(name) ) TYPE = MyIsam; -- -- Create EmsInterface table. A row in the EmsInterface table can represent -- either a MpsInterface or a OapInterface object -- CREATE TABLE EmsInterface ( -- The CLLI (Primary Key) clli CHAR(10) NOT NULL, emsType ENUM("OAP", "MPS") NOT NULL, -- The IP address of the primary interface primaryAddress TINYBLOB NOT NULL, -- The IP address of the secondary interface secondaryAddress TINYBLOB NOT NULL, -- The method to use to verify the presence of the MPS pingMethod ENUM("PING", "SSH", "NONE") NOT NULL, -- The mate CLLI mateClli CHAR(10) NOT NULL, -- Point code pointCode CHAR(9) NOT NULL, -- Point code of the mate matePointCode CHAR(9) NOT NULL, -- Capability point code capabilityPointCode CHAR(9) NOT NULL, -- GttGroup assigned to the EmsInteraface gttGroup CHAR(64) NOT NULL DEFAULT "" REFERENCES GttGroup(name), -- TnFilter assigned to the EmsInteraface tnFilter CHAR(64) NOT NULL DEFAULT "" REFERENCES TnFilter, -- via FOREIGN KEY (ownerSpid, tnfilter) -- ServiceProvider to which this EmsInterface is assigned ownerSpid CHAR(4) NOT NULL DEFAULT "" REFERENCES LsmsServiceProvider(spid), -- Component Info (stored as CSV string) componentInfo BLOB NOT NULL, -- Contact Info (stored as CSV string) contactInfo BLOB NOT NULL, -- The last fields are only used when the row represents a -- OAP interface. The row is used to construct both OapInterface -- objects and MpsInterface objects which are subclasses of EmsInterface -- OAP dcmAddress dcmAddress TINYBLOB NULL DEFAULT NULL, -- OAP retry interval retryInterval INTEGER NULL DEFAULT NULL, -- OAP retry count retryCount INTEGER NULL DEFAULT NULL, -- Primary key is the CLLI name PRIMARY KEY (clli), -- Not used by MySql but included for documentation FOREIGN KEY (ownerSpid, tnFilter) REFERENCES TnFilter ) TYPE = MyIsam; -- -- Create TnFilter table. A row in the EmsInterface table can represent -- either a RegionTnFilter or a NpaNxxTnFilter object -- CREATE TABLE TnFilter ( -- The LsmsServiceProvider this TnFilter belongs to spid char(4) NOT NULL, -- Foreign key -- The name of the TnFilter name CHAR(64) NOT NULL, -- Description of the TnFilter description CHAR(80) NOT NULL, -- The filter type (NpaNxxTnFilter or RegionalTnFilter) filterType ENUM("Regional", "NpaNxx") NOT NULL, -- If RegionalTnFilter, the region to send regions SET("Not Used", "Canada", "MidAtlantic", "Midwest", "Northeast", "Southeast", "Southwest", "WestCoast", "Western") NOT NULL, -- If NpaNxxTnFilter, the filter type npanxxType ENUM("Include", "Exclude") NOT NULL, -- If NpaNxxTnFilter, the npa-nxxs to send npanxxs LONGBLOB NOT NULL, -- TnFilter names are unique within LsmsServiceProvider PRIMARY KEY (spid, name), -- Not used by MySql but included for documentation FOREIGN KEY (spid) REFERENCES LsmsServiceProvider(spid) ) TYPE = MyIsam; -- -- Create private and public key tables -- -- The first four fields define a base class Key in the object interface -- -- +-- PrivateKey -- Key <--| -- +-- PublicKey -- -- Each subclass and table has the key values for the key type. -- -- -- Create "Model" PrivateKey table -- CREATE TEMPORARY TABLE IF NOT EXISTS TempPrivateKey ( listId INT UNSIGNED, keyId INT UNSIGNED, status ENUM("Expired", "Valid", "InUse"), keyval BLOB -- Max length 1024 ) TYPE = MyIsam; -- Create CanadaPrivateKey table CREATE TABLE CanadaPrivateKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPrivateKey; -- Create NortheastPrivateKey table CREATE TABLE NortheastPrivateKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPrivateKey; -- Create MidAtlanticPrivateKey table CREATE TABLE MidAtlanticPrivateKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPrivateKey; -- Create MidwestPrivateKey table CREATE TABLE MidwestPrivateKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPrivateKey; -- Create SoutheastPrivateKey table CREATE TABLE SoutheastPrivateKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPrivateKey; -- Create SouthwestPrivateKey table CREATE TABLE SouthwestPrivateKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPrivateKey; -- Create WestCoastPrivateKey table CREATE TABLE WestCoastPrivateKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPrivateKey; -- Create WesternPrivateKey table CREATE TABLE WesternPrivateKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPrivateKey; -- -- Create "Model" PublicKey table -- CREATE TEMPORARY TABLE IF NOT EXISTS TempPublicKey ( listId INT UNSIGNED, keyId INT UNSIGNED, status ENUM("Expired", "Valid", "InUse"), exponent TINYBLOB, -- Max length 3 modulus TINYBLOB -- Max length 256 ) TYPE = MyIsam; -- Create CanadaPublicKey table CREATE TABLE CanadaPublicKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPublicKey; -- Create NortheastPublicKey table CREATE TABLE NortheastPublicKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPublicKey; -- Create MidAtlanticPublicKey table CREATE TABLE MidAtlanticPublicKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPublicKey; -- Create MidwestPublicKey table CREATE TABLE MidwestPublicKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPublicKey; -- Create SoutheastPublicKey table CREATE TABLE SoutheastPublicKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPublicKey; -- Create SouthwestPublicKey table CREATE TABLE SouthwestPublicKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPublicKey; -- Create WestCoastPublicKey table CREATE TABLE WestCoastPublicKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPublicKey; -- Create WesternPublicKey table CREATE TABLE WesternPublicKey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ) SELECT * FROM TempPublicKey; -- -- Create one measurements table for each region -- -- Create "Model" NpacMeasurements table CREATE TEMPORARY TABLE IF NOT EXISTS TempNpacMeasurements ( yyyydddhh INT UNSIGNED NOT NULL, Binds INT UNSIGNED NOT NULL DEFAULT 0, SuccessOps INT UNSIGNED NOT NULL DEFAULT 0, FailedOps INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (yyyydddhh) ) TYPE = MyIsam; -- Create CanadaNpacMeasurements table CREATE TABLE CanadaNpacMeasurements ( PRIMARY KEY (yyyydddhh) ) SELECT * FROM TempNpacMeasurements; -- Create NortheastNpacMeasurements table CREATE TABLE NortheastNpacMeasurements ( PRIMARY KEY (yyyydddhh) ) SELECT * FROM TempNpacMeasurements; -- Create MidAtlanticNpacMeasurements table CREATE TABLE MidAtlanticNpacMeasurements ( PRIMARY KEY (yyyydddhh) ) SELECT * FROM TempNpacMeasurements; -- Create MidwestNpacMeasurements table CREATE TABLE MidwestNpacMeasurements ( PRIMARY KEY (yyyydddhh) ) SELECT * FROM TempNpacMeasurements; -- Create SoutheastNpacMeasurements table CREATE TABLE SoutheastNpacMeasurements ( PRIMARY KEY (yyyydddhh) ) SELECT * FROM TempNpacMeasurements; -- Create SouthwestNpacMeasurements table CREATE TABLE SouthwestNpacMeasurements ( PRIMARY KEY (yyyydddhh) ) SELECT * FROM TempNpacMeasurements; -- Create WestCoastNpacMeasurements table CREATE TABLE WestCoastNpacMeasurements ( PRIMARY KEY (yyyydddhh) ) SELECT * FROM TempNpacMeasurements; -- Create WesternNpacMeasurements table CREATE TABLE WesternNpacMeasurements ( PRIMARY KEY (yyyydddhh) ) SELECT * FROM TempNpacMeasurements; -- -- Create DbConfig table -- CREATE TABLE DbConfig ( keyType ENUM("Canada", "MidAtlantic", "Midwest", "Northeast", "Southeast", "Southwest", "WestCoast", "Western", "R9", "R10", "R11", "R12", "R13", "R14", "R15", "R16", "R17", "R18", "R19", "R20", -- Future Regions "Internal", "Ebda", "Lsms") NOT NULL, keyName TINYBLOB NOT NULL, -- Max length 256 description TINYBLOB NOT NULL DEFAULT "", -- Max length 256 value BLOB NOT NULL DEFAULT "", -- Max length 64K -- keyName is unique within keyType PRIMARY KEY (keyType, keyName(255)) ) TYPE = MyIsam; INSERT INTO DbConfig (keyType, keyName, description, value) VALUES ("Canada", "REQUEST_RETRY_NUMBER", "Retry times for NPAC requests", "3"), ("Canada", "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC requests", "2"), ("Canada", "RECOV_RETRY_NUMBER", "Retry times for NPAC recovery requests", "3"), ("Canada", "RECOV_RETRY_INTERVAL", "Retry mintues for NPAC recovery requests", "5"), ("MidAtlantic", "REQUEST_RETRY_NUMBER", "Retry times for NPAC requests", "3"), ("MidAtlantic", "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC requests", "2"), ("MidAtlantic", "RECOV_RETRY_NUMBER", "Retry times for NPAC recovery requests", "3"), ("MidAtlantic", "RECOV_RETRY_INTERVAL", "Retry mintues for NPAC recovery requests", "5"), ("Midwest", "REQUEST_RETRY_NUMBER", "Retry times for NPAC requests", "3"), ("Midwest", "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC requests", "2"), ("Midwest", "RECOV_RETRY_NUMBER", "Retry times for NPAC recovery requests", "3"), ("Midwest", "RECOV_RETRY_INTERVAL", "Retry mintues for NPAC recovery requests", "5"), ("Northeast", "REQUEST_RETRY_NUMBER", "Retry times for NPAC requests", "3"), ("Northeast", "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC requests", "2"), ("Northeast", "RECOV_RETRY_NUMBER", "Retry times for NPAC recovery requests", "3"), ("Northeast", "RECOV_RETRY_INTERVAL", "Retry mintues for NPAC recovery requests", "5"), ("Southeast", "REQUEST_RETRY_NUMBER", "Retry times for NPAC requests", "3"), ("Southeast", "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC requests", "2"), ("Southeast", "RECOV_RETRY_NUMBER", "Retry times for NPAC recovery requests", "3"), ("Southeast", "RECOV_RETRY_INTERVAL", "Retry mintues for NPAC recovery requests", "5"), ("Southwest", "REQUEST_RETRY_NUMBER", "Retry times for NPAC requests", "3"), ("Southwest", "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC requests", "2"), ("Southwest", "RECOV_RETRY_NUMBER", "Retry times for NPAC recovery requests", "3"), ("Southwest", "RECOV_RETRY_INTERVAL", "Retry mintues for NPAC recovery requests", "5"), ("WestCoast", "REQUEST_RETRY_NUMBER", "Retry times for NPAC requests", "3"), ("WestCoast", "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC requests", "2"), ("WestCoast", "RECOV_RETRY_NUMBER", "Retry times for NPAC recovery requests", "3"), ("WestCoast", "RECOV_RETRY_INTERVAL", "Retry mintues for NPAC recovery requests", "5"), ("Western", "REQUEST_RETRY_NUMBER", "Retry times for NPAC requests", "3"), ("Western", "REQUEST_RETRY_INTERVAL", "Retry minutes for NPAC requests", "2"), ("Western", "RECOV_RETRY_NUMBER", "Retry times for NPAC recovery requests", "3"), ("Western", "RECOV_RETRY_INTERVAL", "Retry mintues for NPAC recovery requests", "5"), ("Internal", "MAX_SPIDS", "Maximum Service Providers allowed.", "32"), ("Internal", "EDR", "Enable Efficient Data Reperesentation (EDR).", "N" ), ("Internal", "SNMP", "Enable SNMP Agent.", "N" ), ("Internal", "AFT", "Enable Automatic File Transfer.", "N" ), ("Internal", "WSMSC", "Enable wireless service feature.", "N" ), ("Internal", "WSMSC_TO_EAGLE","Enable sending of WSMSA service to Eagle.", "N" ), ("Internal", "SPID_SECURITY", "Enable SPID based security.", "N" ), ("Internal", "MAX_USERS", "Maximum Number of Users", "8" ), ("Internal", "ENHANCED_FILTERS","Enable Group and Regional filter creation.", "N" ), ("Internal", "MAX_EAGLES", "Maximum number of eagles.", "16"), ("Internal", "REPORT_GEN", "Enable report generator.", "N" ), ("Internal", "REPORT_GEN_QUERY_ACTIVE","Report generator pid field", "0" ), ("Internal", "QUERY_SERVER", "Enable Query Server feature", "N" ), ("Internal", "INACTIVITY_TIMEOUT", "Gui and Shell inactivity timeout feature", "N" ), ("Internal", "SYSTEM_INACTIVITY_TIMEOUT", "System wide GUI and Shell inactivity timeout value", "15" ), ("Ebda", "CMD_ARGS", "EBDA command line arguments", ""), ("Lsms", "NPAC_SPID", "Spid used to connect to NPAC", ""), ("Lsms", "CONTACT_INFO", "Spid used to connect to NPAC", '"Lsms Admin","admin@tekelec.com","5200 Paramount Parkway","Morrisville","NC","","USA","27560","9194605500","8005551234","1 234","9195551234"'), ("Lsms", "COMPONENT_INFO", "Spid used to connect to NPAC", '"LSMS","TKLC","LSMS","Tekelec, Inc.","6.0","1.0"'); -- -- Create NpaSplit table -- CREATE TABLE NpaSplit ( -- The old npa oldNpa char(3) NOT NULL, -- The new npa newNpa CHAR(3) NOT NULL, -- The nxx nxx CHAR(3) NOT NULL, -- The start of the permissive dialing period startPDP CHAR(8) NOT NULL, -- The end of the permissive dialing period endPDP CHAR(8) NOT NULL, -- The region the split belongs to region ENUM("Canada", "MidAtlantic", "Midwest", "Northeast", "Southeast", "Southwest", "WestCoast", "Western", "R9", "R10", "R11", "R12", "R13", "R14", "R15", "R16", "R17", "R18", "R19", "R20"), -- Future Regions -- The status of the npa split status ENUM("NotSet", "Pending", "Active", "Error"), -- Old npa, new npa and nxx form primary unique key PRIMARY KEY (oldnpa, newnpa, nxx) ) TYPE = MyIsam;
-- -- Create SubscriptionVersion table -- -- The Fields are defined in the order and format that are defined by the -- NPAC bulk data file. This allows the SQL LOAD DATA command to be used -- to load tables which is extremely fast. -- -- Revision History -- 15-may-07 ARICENT Feature 110663: NANC 399 -- CREATE TABLE SubscriptionVersion ( -- Required field (Primary key) versionId INT NOT NULL, -- Required field (10 numeric character unique key) tn CHAR(10) NOT NULL, -- Optional field (10 numeric characters, Empty string means not present) lrn CHAR(10) NOT NULL DEFAULT "", -- Required field (1-4 characters) newCurrentSp CHAR(4) NOT NULL DEFAULT "0000", -- Required field (14 characters "YYYYMMDDHHMMSS") activationTimestamp CHAR(14) NOT NULL DEFAULT "00000000000000", -- Optional field (9 characters, Empty string means not present) classDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) classSSN CHAR(3) NOT NULL DEFAULT "", -- Optional field (9 characters, Empty string means not present) lidbDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) lidbSSN CHAR(3) NOT NULL DEFAULT "", -- Optional field (9 characters, Empty string means not present) isvmDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) isvmSSN CHAR(3) NOT NULL DEFAULT "", -- Optional field (9 characters, Empty string means not present) cnamDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) cnamSSN CHAR(3) NOT NULL DEFAULT "", -- Optional field (1-12 numeric characters, Empty string means not present) endUserLocationValue CHAR(12) NOT NULL DEFAULT "", -- Optional field (2 numeric characters, Empty string means not present) endUserLocationType CHAR(2) NOT NULL DEFAULT "", -- Required field (1-4 characters, Empty string means not present) billingId CHAR(4) NOT NULL DEFAULT "", -- Required field (lspp(0), lisp(1), pool(2)) lnpType TINYINT UNSIGNED NOT NULL DEFAULT 0, -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3) downloadReason TINYINT UNSIGNED NOT NULL DEFAULT 0, -- Optional field (9 characters, Empty string means not present) wsmscDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) wsmscSSN CHAR(3) NOT NULL DEFAULT "", -- Optional field (wireline(0), wireless(1), voIP(2), voWiFi(3), sv_type_4(4), sv_type_5(5), sv_type_6(6) ) svType TINYINT NOT NULL DEFAULT -1, -- Optional field (1-4 CHARACTERS) alternativeSPIDCHAR(4) NOT NULL DEFAULT "", -- Primay key is the Npac SubscriptionVersion id PRIMARY KEY (versionId), -- TN must be indexed and unique UNIQUE KEY tn (tn), -- Index lrn, for LSMS Subscription Version by LRN reports INDEX (lrn), -- Index lrn, for LSMS Subscription Version by SPID reports INDEX (newCurrentSp) ) TYPE = MyIsam; -- -- Create NumberPoolBlock table -- -- The Fields are defined in the order and format that are defined by the -- NPAC bulk data file. This allows the SQL LOAD DATA command to be used -- to load tables which is extremely fast. -- CREATE TABLE NumberPoolBlock ( -- Required field (Primary key) blockId INT NOT NULL, -- Required field (7 numeric characters, Unique key) npanxx_x CHAR(7) NOT NULL, -- Optional field (10 numeric characters, Empty string means not present) lrn CHAR(10) NOT NULL DEFAULT "", -- Required field (1-4 characters) newCurrentSp CHAR(4) NOT NULL DEFAULT "0000", -- Required field (14 characters "YYYYMMDDHHMMSS") activationTimestamp CHAR(14) NOT NULL DEFAULT "00000000000000", -- Optional field (9 characters, Empty string means not present) classDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) classSSN CHAR(3) NOT NULL DEFAULT "", -- Optional field (9 characters, Empty string means not present) lidbDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) lidbSSN CHAR(3) NOT NULL DEFAULT "", -- Optional field (9 characters, Empty string means not present) isvmDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) isvmSSN CHAR(3) NOT NULL DEFAULT "", -- Optional field (9 characters, Empty string means not present) cnamDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) cnamSSN CHAR(3) NOT NULL DEFAULT "", -- Optional field (9 characters, Empty string means not present) wsmscDPC CHAR(9) NOT NULL DEFAULT "", -- Optional field (1-3 characters, Empty string means not present) wsmscSSN CHAR(3) NOT NULL DEFAULT "", -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3) -- Changed DEFAULT from "" to 0 when migrated MySQL from 4.1.11 to 5.0.37 downloadReason TINYINT UNSIGNED NOT NULL DEFAULT 0, -- Optional field (wireline(0), wireless(1), voIP(2), voWiFi(3), sv_type_4(4), sv_type_5(5), sv_type_6(6) ) svType TINYINT NOT NULL DEFAULT -1, -- Optional field (1-4 CHARACTERS) alternativeSPID CHAR(4) NOT NULL DEFAULT "", -- Primay key is the Npac NumberPoolBlock id PRIMARY KEY (blockId), -- TN must be indexed and unique UNIQUE KEY npanxx_x (npanxx_x), -- Index lrn, for LSMS Number Pool Block by LRN reports INDEX (lrn), -- Index lrn, for LSMS Number Pool Block by SPID reports INDEX (newCurrentSp) ) TYPE = MyIsam; -- -- Create ServiceProvNetwork table -- -- The Fields are defined in the order and format that are defined by the -- NPAC bulk data file -- CREATE TABLE ServiceProvNetwork ( -- Required field (Primary key) serviceProvId CHAR(4) NOT NULL, -- Required field (1 - 40 characters) serviceProvName CHAR(40) NOT NULL DEFAULT "", -- Service Provider type serviceProvType ENUM("wireline", "wireless", "non_carrier", "sp_type_3", "sp_type_4", "sp_type_5") NULL DEFAULT NULL, -- Prmary key is the Service Provider ID PRIMARY KEY (serviceProvId) ) TYPE = MyIsam; -- -- Create ServiceProvLRN table -- -- The Fields are defined in the order that are defined by the -- NPAC bulk data file -- CREATE TABLE ServiceProvLRN ( -- Foreign key -> ServiceProvNetwork serviceProvId CHAR(4) NOT NULL, -- Required field (Primary key within each ServiceProvNetwork) id INT NOT NULL, -- Required field (10 numeric characters) lrn CHAR(10) NOT NULL, -- Required field (14 characters "YYYYMMDDHHMMSS") creationTimeStamp CHAR(14) NOT NULL DEFAULT "00000000000000", -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3) downloadReason TINYINT NOT NULL DEFAULT 0, -- Primary key is the Npac id within each ServiceProvNetwork PRIMARY KEY (serviceProvId, id), -- Lrn is unique key within each ServiceProvNetwork UNIQUE KEY lrn (serviceProvId, lrn), -- Index lrn INDEX (lrn), -- Not used by MySql but included for documentation FOREIGN KEY (serviceProvId) REFERENCES ServiceProvNetwork(serviceProvId) ) TYPE = MyIsam; -- -- Create ServiceProvNPA_NXX table -- -- The Fields are defined in the order defined by the NPAC bulk data file -- but the npac file formats the npanxx as 'npa-nxx'. -- CREATE TABLE ServiceProvNPA_NXX ( -- Foreign key -> ServiceProvNetwork serviceProvId CHAR(4) NOT NULL, -- Required field (Primary Unique Key) id INT NOT NULL, -- Required field (6 numeric characters) npanxx CHAR(6) NOT NULL, -- Required field (14 characters "YYYYMMDDHHMMSS") creationTimeStamp CHAR(14) NOT NULL DEFAULT "00000000000000", -- Required field (14 characters "YYYYMMDDHHMMSS") effectiveTimeStamp CHAR(14) NOT NULL DEFAULT "00000000000000", -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3) downloadReason TINYINT NOT NULL DEFAULT 0, -- Primary key is the Npac id within each ServiceProvNetwork PRIMARY KEY (serviceProvId, id), -- NpaNxx is unique key within each ServiceProvNetwork UNIQUE KEY npanxx (serviceProvId, npanxx), -- Index npanxx INDEX (npanxx), -- Not used by MySql but included for documentation FOREIGN KEY (serviceProvId) REFERENCES ServiceProvNetwork(serviceProvId) ) TYPE = MyIsam; -- -- Create ServiceProvNPA_NXX_X table -- -- The Fields are defined in the order defined by the NPAC bulk data file -- but the npac file formats the npanxx as 'npa-nxx-x'. -- CREATE TABLE ServiceProvNPA_NXX_X ( -- Foreign key -> ServiceProvNetwork serviceProvId CHAR(4) NOT NULL, -- Required field (Primary Unique Key) id INT NOT NULL, -- Required field (7 numeric characters) npanxx_x CHAR(7) NOT NULL, -- Required field (14 characters "YYYYMMDDHHMMSS") creationTimeStamp CHAR(14) NOT NULL DEFAULT "00000000000000", -- Required field (14 characters "YYYYMMDDHHMMSS") effectiveTimeStamp CHAR(14) NOT NULL DEFAULT "00000000000000", -- Required field (14 characters "YYYYMMDDHHMMSS") modifiedTimeStamp CHAR(14) NOT NULL DEFAULT "00000000000000", -- Required field (new(0), delete(1), modified(2), audit-descrepancy(3) downloadReason TINYINT NOT NULL DEFAULT 0, -- Primary key is the Npac id within each ServiceProvNetwork PRIMARY KEY (serviceProvId, id), -- NpaNxx is unique key within each ServiceProvNetwork UNIQUE KEY npanxx_x (serviceProvId, npanxx_x), -- Index npanxx_x INDEX (npanxx_x), -- Not used by MySql but included for documentation FOREIGN KEY (serviceProvId) REFERENCES ServiceProvNetwork(serviceProvId) ) TYPE = MyIsam;
-- Create Eagle Measurements Table -- $S is replaced by CLLI for EMS in Table Name (ie, STPAEagleMeasurments) CREATE TABLE $SEagleMeasurements ( yyyydddhh INT UNSIGNED NOT NULL, UpdTnSuccess INT UNSIGNED NOT NULL DEFAULT 0, UpdTnFail INT UNSIGNED NOT NULL DEFAULT 0, DelTnSuccess INT UNSIGNED NOT NULL DEFAULT 0, DelTnFail INT UNSIGNED NOT NULL DEFAULT 0, UpdDGttSuccess INT UNSIGNED NOT NULL DEFAULT 0, UpdDGttFail INT UNSIGNED NOT NULL DEFAULT 0, DelDGttSuccess INT UNSIGNED NOT NULL DEFAULT 0, DelDGttFail INT UNSIGNED NOT NULL DEFAULT 0, UpdOGttSuccess INT UNSIGNED NOT NULL DEFAULT 0, UpdOGttFail INT UNSIGNED NOT NULL DEFAULT 0, DelOGttSuccess INT UNSIGNED NOT NULL DEFAULT 0, DelOGttFail INT UNSIGNED NOT NULL DEFAULT 0, UpdSplitSuccess INT UNSIGNED NOT NULL DEFAULT 0, UpdSplitFail INT UNSIGNED NOT NULL DEFAULT 0, DelSplitSuccess INT UNSIGNED NOT NULL DEFAULT 0, DelSplitFail INT UNSIGNED NOT NULL DEFAULT 0, Binds INT UNSIGNED NOT NULL DEFAULT 0, LsmsRetries INT UNSIGNED NOT NULL DEFAULT 0, NERetries INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (yyyydddhh) ) TYPE = MyIsam;";