From c63194c50e9f4d4b0f4b322f6ad565dd6d4a860c Mon Sep 17 00:00:00 2001 From: Rob Austein Date: Tue, 25 Sep 2007 17:57:33 +0000 Subject: Cleanup prior to hand-editing svn path=/docs/rpki-db-schema.pdf; revision=1023 --- docs/rpki-db-schema.pdf | Bin 7261 -> 5498 bytes docs/rpki-db-schema.sql | 417 ++++++++++++++++++---------------------------- docs/sample-irdb-data.sql | 130 +++++++-------- docs/sample-irdb.pdf | Bin 3145 -> 2340 bytes docs/sample-irdb.sql | 154 ++++++++--------- 5 files changed, 308 insertions(+), 393 deletions(-) diff --git a/docs/rpki-db-schema.pdf b/docs/rpki-db-schema.pdf index a4235859..077ad3d5 100644 Binary files a/docs/rpki-db-schema.pdf and b/docs/rpki-db-schema.pdf differ diff --git a/docs/rpki-db-schema.sql b/docs/rpki-db-schema.sql index f2873766..041454a9 100644 --- a/docs/rpki-db-schema.sql +++ b/docs/rpki-db-schema.sql @@ -1,251 +1,166 @@ -drop table if exists bsc; -drop table if exists bsc_cert; -drop table if exists ca; -drop table if exists ca_detail; -drop table if exists child_ca_link; -drop table if exists child; -drop table if exists child_ca_certificate; -drop table if exists ee_cert; -drop table if exists manifest; -drop table if exists parent; -drop table if exists repository; -drop table if exists roa; -drop table if exists route_origin; -drop table if exists self; -drop table if exists self_pref; -drop table if exists route_origin_range; - - -CREATE TABLE bsc ( - bsc_id SERIAL NOT NULL, - private_key_id LONGBLOB, - self_id BIGINT unsigned NOT NULL, - public_key LONGBLOB, - PRIMARY KEY (bsc_id) -); - - -CREATE TABLE bsc_cert ( - bsc_cert_id SERIAL NOT NULL, - cert LONGBLOB, - bsc_id BIGINT unsigned NOT NULL, - PRIMARY KEY (bsc_cert_id) -); - - -CREATE TABLE ca ( - ca_id SERIAL NOT NULL, - last_crl_sn BIGINT unsigned, - last_manifest_sn BIGINT unsigned, - next_manifest_update DATETIME, - next_crl_update DATETIME, - last_issued_sn BIGINT unsigned, - sia_uri TEXT, - parent_id BIGINT unsigned, - PRIMARY KEY (ca_id) -); - - -CREATE TABLE ca_detail ( - ca_detail_id SERIAL NOT NULL, - public_key LONGBLOB, - private_key_handle LONGBLOB, - latest_crl LONGBLOB, - latest_ca_cert_over_public_key LONGBLOB, - manifest_ee_private_key_handle LONGBLOB, - manifest_ee_public_key LONGBLOB, - latest_manifest_ee_cert LONGBLOB, - latest_manifest LONGBLOB, - ca_id BIGINT unsigned NOT NULL, - PRIMARY KEY (ca_detail_id) -); - - -CREATE TABLE child ( - child_id SERIAL NOT NULL, - peer_ta LONGBLOB, - self_id BIGINT unsigned NOT NULL, - bsc_id BIGINT unsigned NOT NULL, - PRIMARY KEY (child_id) -); - - -CREATE TABLE child_ca_certificate ( - child_id BIGINT unsigned NOT NULL, - ca_detail_id BIGINT unsigned NOT NULL, - cert LONGBLOB NOT NULL, - PRIMARY KEY (child_id, ca_detail_id) -); - - -CREATE TABLE child_ca_link ( - ca_id BIGINT unsigned NOT NULL, - child_id BIGINT unsigned NOT NULL, - PRIMARY KEY (ca_id, child_id) -); - - -CREATE TABLE parent ( - parent_id SERIAL NOT NULL, - peer_ta LONGBLOB, - peer_contact_uri TEXT, - sia_base TEXT, - self_id BIGINT unsigned NOT NULL, - bsc_id BIGINT unsigned NOT NULL, - repository_id BIGINT unsigned NOT NULL, - PRIMARY KEY (parent_id) -); - - -CREATE TABLE repository ( - repository_id SERIAL NOT NULL, - peer_contact_uri TEXT, - peer_ta LONGBLOB, - bsc_id BIGINT unsigned NOT NULL, - self_id BIGINT unsigned NOT NULL, - PRIMARY KEY (repository_id) -); - - -CREATE TABLE roa ( - route_origin_id BIGINT unsigned NOT NULL, - ee_cert LONGBLOB, - roa LONGBLOB NOT NULL, - ca_detail_id BIGINT unsigned NOT NULL, - PRIMARY KEY (route_origin_id, ca_detail_id) -); - - -CREATE TABLE route_origin ( - route_origin_id SERIAL NOT NULL, - as_number DECIMAL(24,0), - self_id BIGINT unsigned NOT NULL, - PRIMARY KEY (route_origin_id) -); - - -CREATE TABLE route_origin_range ( - start_ip VARCHAR(40), - end_ip VARCHAR(40), - route_origin_id BIGINT unsigned NOT NULL, - PRIMARY KEY (route_origin_id, start_ip, end_ip) -); - - -CREATE TABLE self ( - self_id SERIAL NOT NULL, - use_hsm BOOLEAN, - PRIMARY KEY (self_id) -); - - -CREATE TABLE self_pref ( - pref_name VARCHAR(100), - pref_value TEXT, - self_id BIGINT unsigned NOT NULL, - PRIMARY KEY (self_id, pref_name) -); - - -ALTER TABLE bsc - ADD FOREIGN KEY (self_id) - REFERENCES self; - - -ALTER TABLE bsc_cert - ADD FOREIGN KEY (bsc_id) - REFERENCES bsc; - - -ALTER TABLE ca - ADD FOREIGN KEY (parent_id) - REFERENCES parent; - - -ALTER TABLE ca_detail - ADD FOREIGN KEY (ca_id) - REFERENCES ca; - - -ALTER TABLE child - ADD FOREIGN KEY (bsc_id) - REFERENCES bsc; - - -ALTER TABLE child - ADD FOREIGN KEY (self_id) - REFERENCES self; - - -ALTER TABLE child_ca_certificate - ADD FOREIGN KEY (ca_detail_id) - REFERENCES ca_detail; - - -ALTER TABLE child_ca_certificate - ADD FOREIGN KEY (child_id) - REFERENCES child; - - -ALTER TABLE child_ca_link - ADD FOREIGN KEY (child_id) - REFERENCES child; - - -ALTER TABLE child_ca_link - ADD FOREIGN KEY (ca_id) - REFERENCES ca; - - -ALTER TABLE parent - ADD FOREIGN KEY (repository_id) - REFERENCES repository; - - -ALTER TABLE parent - ADD FOREIGN KEY (bsc_id) - REFERENCES bsc; - - -ALTER TABLE parent - ADD FOREIGN KEY (self_id) - REFERENCES self; - - -ALTER TABLE repository - ADD FOREIGN KEY (self_id) - REFERENCES self; - - -ALTER TABLE repository - ADD FOREIGN KEY (bsc_id) - REFERENCES bsc; - - -ALTER TABLE roa - ADD FOREIGN KEY (ca_detail_id) - REFERENCES ca_detail; - - -ALTER TABLE roa - ADD FOREIGN KEY (route_origin_id) - REFERENCES route_origin; - - -ALTER TABLE route_origin - ADD FOREIGN KEY (self_id) - REFERENCES self; - - -ALTER TABLE route_origin_range - ADD FOREIGN KEY (route_origin_id) - REFERENCES route_origin; - - -ALTER TABLE self_pref - ADD FOREIGN KEY (self_id) - REFERENCES self; - - - - +-- $Id$ + +DROP TABLE IF EXISTS bsc; + +DROP TABLE IF EXISTS self; + +CREATE TABLE self ( + self_id SERIAL NOT NULL, + use_hsm BOOLEAN, + PRIMARY KEY (self_id) +); + +DROP TABLE IF EXISTS self_pref; + +CREATE TABLE self_pref ( + pref_name VARCHAR(100), + pref_value TEXT, + self_id BIGINT unsigned NOT NULL, + PRIMARY KEY (self_id, pref_name), + FOREIGN KEY (self_id) REFERENCES self +); + +CREATE TABLE bsc ( + bsc_id SERIAL NOT NULL, + private_key_id LONGBLOB, + self_id BIGINT unsigned NOT NULL, + public_key LONGBLOB, + PRIMARY KEY (bsc_id), + FOREIGN KEY (self_id) REFERENCES self +); + +DROP TABLE IF EXISTS bsc_cert; + +CREATE TABLE bsc_cert ( + bsc_cert_id SERIAL NOT NULL, + cert LONGBLOB, + bsc_id BIGINT unsigned NOT NULL, + PRIMARY KEY (bsc_cert_id), + FOREIGN KEY (bsc_id) REFERENCES bsc +); + +DROP TABLE IF EXISTS repository; + +CREATE TABLE repository ( + repository_id SERIAL NOT NULL, + peer_contact_uri TEXT, + peer_ta LONGBLOB, + bsc_id BIGINT unsigned NOT NULL, + self_id BIGINT unsigned NOT NULL, + PRIMARY KEY (repository_id), + FOREIGN KEY (self_id) REFERENCES self, + FOREIGN KEY (bsc_id) REFERENCES bsc +); + +DROP TABLE IF EXISTS parent; + +CREATE TABLE parent ( + parent_id SERIAL NOT NULL, + peer_ta LONGBLOB, + peer_contact_uri TEXT, + sia_base TEXT, + self_id BIGINT unsigned NOT NULL, + bsc_id BIGINT unsigned NOT NULL, + repository_id BIGINT unsigned NOT NULL, + PRIMARY KEY (parent_id), + FOREIGN KEY (repository_id) REFERENCES repository, + FOREIGN KEY (bsc_id) REFERENCES bsc, + FOREIGN KEY (self_id) REFERENCES self +); + +DROP TABLE IF EXISTS ca; + +CREATE TABLE ca ( + ca_id SERIAL NOT NULL, + last_crl_sn BIGINT unsigned, + last_manifest_sn BIGINT unsigned, + next_manifest_update DATETIME, + next_crl_update DATETIME, + last_issued_sn BIGINT unsigned, + sia_uri TEXT, + parent_id BIGINT unsigned, + PRIMARY KEY (ca_id), + FOREIGN KEY (parent_id) REFERENCES parent +); + +DROP TABLE IF EXISTS ca_detail; + +CREATE TABLE ca_detail ( + ca_detail_id SERIAL NOT NULL, + public_key LONGBLOB, + private_key_handle LONGBLOB, + latest_crl LONGBLOB, + latest_ca_cert_over_public_key LONGBLOB, + manifest_ee_private_key_handle LONGBLOB, + manifest_ee_public_key LONGBLOB, + latest_manifest_ee_cert LONGBLOB, + latest_manifest LONGBLOB, + ca_id BIGINT unsigned NOT NULL, + PRIMARY KEY (ca_detail_id), + FOREIGN KEY (ca_id) REFERENCES ca +); + +DROP TABLE IF EXISTS child; + +CREATE TABLE child ( + child_id SERIAL NOT NULL, + peer_ta LONGBLOB, + self_id BIGINT unsigned NOT NULL, + bsc_id BIGINT unsigned NOT NULL, + PRIMARY KEY (child_id), + FOREIGN KEY (bsc_id) REFERENCES bsc, + FOREIGN KEY (self_id) REFERENCES self +); + +DROP TABLE IF EXISTS child_ca_certificate; + +CREATE TABLE child_ca_certificate ( + child_id BIGINT unsigned NOT NULL, + ca_detail_id BIGINT unsigned NOT NULL, + cert LONGBLOB NOT NULL, + PRIMARY KEY (child_id, ca_detail_id), + FOREIGN KEY (ca_detail_id) REFERENCES ca_detail, + FOREIGN KEY (child_id) REFERENCES child +); + +DROP TABLE IF EXISTS child_ca_link; + +CREATE TABLE child_ca_link ( + ca_id BIGINT unsigned NOT NULL, + child_id BIGINT unsigned NOT NULL, + PRIMARY KEY (ca_id, child_id), + FOREIGN KEY (child_id) REFERENCES child, + FOREIGN KEY (ca_id) REFERENCES ca +); + +DROP TABLE IF EXISTS route_origin; + +CREATE TABLE route_origin ( + route_origin_id SERIAL NOT NULL, + as_number DECIMAL(24,0), + self_id BIGINT unsigned NOT NULL, + PRIMARY KEY (route_origin_id), + FOREIGN KEY (self_id) REFERENCES self +); + +DROP TABLE IF EXISTS route_origin_range; + +CREATE TABLE route_origin_range ( + start_ip VARCHAR(40), + end_ip VARCHAR(40), + route_origin_id BIGINT unsigned NOT NULL, + PRIMARY KEY (route_origin_id, start_ip, end_ip), + FOREIGN KEY (route_origin_id) REFERENCES route_origin +); + +DROP TABLE IF EXISTS roa; + +CREATE TABLE roa ( + route_origin_id BIGINT unsigned NOT NULL, + ee_cert LONGBLOB, + roa LONGBLOB NOT NULL, + ca_detail_id BIGINT unsigned NOT NULL, + PRIMARY KEY (route_origin_id, ca_detail_id), + FOREIGN KEY (ca_detail_id) REFERENCES ca_detail, + FOREIGN KEY (route_origin_id) REFERENCES route_origin +); diff --git a/docs/sample-irdb-data.sql b/docs/sample-irdb-data.sql index 767d7436..09bc6278 100644 --- a/docs/sample-irdb-data.sql +++ b/docs/sample-irdb-data.sql @@ -1,65 +1,65 @@ - -INSERT INTO registrant (IRBE_mapped_id) VALUES ('ARIN'); -INSERT INTO registrant (IRBE_mapped_id) VALUES ('TIER1_ISP1'); -INSERT INTO registrant (IRBE_mapped_id) VALUES ('TIER1_ISP2'); -INSERT INTO registrant (IRBE_mapped_id) VALUES ('JOES_PIZZA'); - -INSERT INTO resource_class (subject_name, valid_until, registrant_id) -SELECT 'All ARIN resources', '2099-12-31', registrant_id -FROM registrant WHERE IRBE_mapped_id = 'ARIN'; - -INSERT INTO resource_class (subject_name, valid_until, registrant_id) -SELECT 'Tier 1 ISP foo subject name', '2008-12-31', registrant_id -FROM registrant WHERE IRBE_mapped_id = 'TIER1_ISP1'; - -INSERT INTO resource_class (subject_name, valid_until, registrant_id) -SELECT 'Tier 1 ISP foo subject name', '2009-06-30', registrant_id -FROM registrant WHERE IRBE_mapped_id = 'TIER1_ISP1'; - -INSERT INTO resource_class (subject_name, valid_until, registrant_id) -SELECT 'Tier 1 ISP bar subject name', '2007-07-31', registrant_id -FROM registrant WHERE IRBE_mapped_id = 'TIER1_ISP2'; - -INSERT INTO resource_class (subject_name, valid_until, registrant_id) -SELECT 'arbitrary characters', '2007-12-31', registrant_id -FROM registrant WHERE IRBE_mapped_id = 'JOES_PIZZA'; - -INSERT INTO net (start_ip, end_ip, version, resource_class_id) -SELECT 'DEAD:BEEF:0000:0000:0000:0000:0000:0000', 'DFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF', 6, resource_class_id -FROM resource_class WHERE subject_name = 'All ARIN resources'; - -INSERT INTO net (start_ip, end_ip, version, resource_class_id) -SELECT 'DEAD:BEEF:FACE:0000:0000:0000:0000:0000', 'DEAD:BEEF:FACE:FFFF:FFFF:FFFF:FFFF:FFFF', 6, resource_class_id -FROM resource_class WHERE subject_name = 'TIER 1 ISP foo subject name' AND valid_until = '2009-06-30'; - -INSERT INTO net (start_ip, end_ip, version, resource_class_id) -SELECT 'DEAD:BEEF:FACE:FADE:0000:0000:0000:0000', 'DEAD:BEEF:FACE:FADE:FFFF:FFFF:FFFF:FFFF', 6, resource_class_id -FROM resource_class WHERE subject_name = 'arbitrary characters' AND valid_until = '2007-12-31'; - -INSERT INTO net(start_ip, end_ip, version, resource_class_id) -SELECT '010.000.000.000', '010.255.255.255', 4, resource_class_id -FROM resource_class WHERE subject_name = 'All ARIN resources'; - -INSERT INTO net(start_ip, end_ip, version, resource_class_id) -SELECT '010.128.000.000', '010.191.255.255', 4, resource_class_id -FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2009-06-30'; - -INSERT INTO net(start_ip, end_ip, version, resource_class_id) -SELECT '010.000.000.000', '010.063.255.255', 4, resource_class_id -FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2009-06-30'; - -INSERT INTO net(start_ip, end_ip, version, resource_class_id) -SELECT '010.128.000.000', '010.191.255.255', 4, resource_class_id -FROM resource_class WHERE subject_name = 'arbitrary characters'; - -INSERT INTO asn(start_as, end_as, resource_class_id) -SELECT 12345, 12345, resource_class_id -FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2009-06-30'; - -INSERT INTO asn(start_as, end_as, resource_class_id) -SELECT 23456, 23457, resource_class_id -FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2009-06-30'; - -INSERT INTO asn(start_as, end_as, resource_class_id) -SELECT 34567, 34567, resource_class_id -FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2008-12-31'; + +INSERT INTO registrant (IRBE_mapped_id) VALUES ('ARIN'); +INSERT INTO registrant (IRBE_mapped_id) VALUES ('TIER1_ISP1'); +INSERT INTO registrant (IRBE_mapped_id) VALUES ('TIER1_ISP2'); +INSERT INTO registrant (IRBE_mapped_id) VALUES ('JOES_PIZZA'); + +INSERT INTO resource_class (subject_name, valid_until, registrant_id) +SELECT 'All ARIN resources', '2099-12-31', registrant_id +FROM registrant WHERE IRBE_mapped_id = 'ARIN'; + +INSERT INTO resource_class (subject_name, valid_until, registrant_id) +SELECT 'Tier 1 ISP foo subject name', '2008-12-31', registrant_id +FROM registrant WHERE IRBE_mapped_id = 'TIER1_ISP1'; + +INSERT INTO resource_class (subject_name, valid_until, registrant_id) +SELECT 'Tier 1 ISP foo subject name', '2009-06-30', registrant_id +FROM registrant WHERE IRBE_mapped_id = 'TIER1_ISP1'; + +INSERT INTO resource_class (subject_name, valid_until, registrant_id) +SELECT 'Tier 1 ISP bar subject name', '2007-07-31', registrant_id +FROM registrant WHERE IRBE_mapped_id = 'TIER1_ISP2'; + +INSERT INTO resource_class (subject_name, valid_until, registrant_id) +SELECT 'arbitrary characters', '2007-12-31', registrant_id +FROM registrant WHERE IRBE_mapped_id = 'JOES_PIZZA'; + +INSERT INTO net (start_ip, end_ip, version, resource_class_id) +SELECT 'DEAD:BEEF:0000:0000:0000:0000:0000:0000', 'DFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF:FFFF', 6, resource_class_id +FROM resource_class WHERE subject_name = 'All ARIN resources'; + +INSERT INTO net (start_ip, end_ip, version, resource_class_id) +SELECT 'DEAD:BEEF:FACE:0000:0000:0000:0000:0000', 'DEAD:BEEF:FACE:FFFF:FFFF:FFFF:FFFF:FFFF', 6, resource_class_id +FROM resource_class WHERE subject_name = 'TIER 1 ISP foo subject name' AND valid_until = '2009-06-30'; + +INSERT INTO net (start_ip, end_ip, version, resource_class_id) +SELECT 'DEAD:BEEF:FACE:FADE:0000:0000:0000:0000', 'DEAD:BEEF:FACE:FADE:FFFF:FFFF:FFFF:FFFF', 6, resource_class_id +FROM resource_class WHERE subject_name = 'arbitrary characters' AND valid_until = '2007-12-31'; + +INSERT INTO net(start_ip, end_ip, version, resource_class_id) +SELECT '010.000.000.000', '010.255.255.255', 4, resource_class_id +FROM resource_class WHERE subject_name = 'All ARIN resources'; + +INSERT INTO net(start_ip, end_ip, version, resource_class_id) +SELECT '010.128.000.000', '010.191.255.255', 4, resource_class_id +FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2009-06-30'; + +INSERT INTO net(start_ip, end_ip, version, resource_class_id) +SELECT '010.000.000.000', '010.063.255.255', 4, resource_class_id +FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2009-06-30'; + +INSERT INTO net(start_ip, end_ip, version, resource_class_id) +SELECT '010.128.000.000', '010.191.255.255', 4, resource_class_id +FROM resource_class WHERE subject_name = 'arbitrary characters'; + +INSERT INTO asn(start_as, end_as, resource_class_id) +SELECT 12345, 12345, resource_class_id +FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2009-06-30'; + +INSERT INTO asn(start_as, end_as, resource_class_id) +SELECT 23456, 23457, resource_class_id +FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2009-06-30'; + +INSERT INTO asn(start_as, end_as, resource_class_id) +SELECT 34567, 34567, resource_class_id +FROM resource_class WHERE subject_name = 'Tier 1 ISP foo subject name' AND valid_until = '2008-12-31'; diff --git a/docs/sample-irdb.pdf b/docs/sample-irdb.pdf index ab86a43a..459729fb 100644 Binary files a/docs/sample-irdb.pdf and b/docs/sample-irdb.pdf differ diff --git a/docs/sample-irdb.sql b/docs/sample-irdb.sql index 083577d0..1efdea9b 100644 --- a/docs/sample-irdb.sql +++ b/docs/sample-irdb.sql @@ -1,77 +1,77 @@ - -DROP TABLE IF EXISTS asn; - -CREATE TABLE asn ( - asn_id SERIAL NOT NULL, - start_as BIGINT unsigned NOT NULL, - end_as BIGINT unsigned NOT NULL, - resource_class_id BIGINT unsigned NOT NULL, - PRIMARY KEY (asn_id) -); - -CREATE UNIQUE INDEX XPKasn ON asn -( - asn_id -); - -DROP TABLE IF EXISTS net; - -CREATE TABLE net ( - net_id SERIAL NOT NULL, - start_ip VARCHAR(40) NOT NULL, - end_ip VARCHAR(40) NOT NULL, - version TINYINT unsigned NOT NULL, - resource_class_id BIGINT unsigned NOT NULL, - PRIMARY KEY (net_id) -); - -CREATE UNIQUE INDEX XPKnet ON net -( - net_id -); - -DROP TABLE IF EXISTS registrant; - -CREATE TABLE registrant ( - registrant_id SERIAL NOT NULL, - IRBE_mapped_id TEXT, - PRIMARY KEY (registrant_id) -); - -CREATE UNIQUE INDEX XPKregistrant ON registrant -( - registrant_id -); - -DROP TABLE IF EXISTS resource_class; - -CREATE TABLE resource_class ( - resource_class_id SERIAL NOT NULL, - subject_name TEXT, - valid_until DATETIME NOT NULL, - registrant_id BIGINT unsigned NOT NULL, - PRIMARY KEY (resource_class_id) -); - -CREATE UNIQUE INDEX XPKresource_class ON resource_class -( - resource_class_id -); - -ALTER TABLE asn - ADD FOREIGN KEY (resource_class_id) - REFERENCES resource_class - ON DELETE SET NULL - ON UPDATE SET NULL; - -ALTER TABLE net - ADD FOREIGN KEY (resource_class_id) - REFERENCES resource_class - ON DELETE SET NULL - ON UPDATE SET NULL; - -ALTER TABLE resource_class - ADD FOREIGN KEY (registrant_id) - REFERENCES registrant - ON DELETE SET NULL - ON UPDATE SET NULL; + +DROP TABLE IF EXISTS asn; + +CREATE TABLE asn ( + asn_id SERIAL NOT NULL, + start_as BIGINT unsigned NOT NULL, + end_as BIGINT unsigned NOT NULL, + resource_class_id BIGINT unsigned NOT NULL, + PRIMARY KEY (asn_id) +); + +CREATE UNIQUE INDEX XPKasn ON asn +( + asn_id +); + +DROP TABLE IF EXISTS net; + +CREATE TABLE net ( + net_id SERIAL NOT NULL, + start_ip VARCHAR(40) NOT NULL, + end_ip VARCHAR(40) NOT NULL, + version TINYINT unsigned NOT NULL, + resource_class_id BIGINT unsigned NOT NULL, + PRIMARY KEY (net_id) +); + +CREATE UNIQUE INDEX XPKnet ON net +( + net_id +); + +DROP TABLE IF EXISTS registrant; + +CREATE TABLE registrant ( + registrant_id SERIAL NOT NULL, + IRBE_mapped_id TEXT, + PRIMARY KEY (registrant_id) +); + +CREATE UNIQUE INDEX XPKregistrant ON registrant +( + registrant_id +); + +DROP TABLE IF EXISTS resource_class; + +CREATE TABLE resource_class ( + resource_class_id SERIAL NOT NULL, + subject_name TEXT, + valid_until DATETIME NOT NULL, + registrant_id BIGINT unsigned NOT NULL, + PRIMARY KEY (resource_class_id) +); + +CREATE UNIQUE INDEX XPKresource_class ON resource_class +( + resource_class_id +); + +ALTER TABLE asn + ADD FOREIGN KEY (resource_class_id) + REFERENCES resource_class + ON DELETE SET NULL + ON UPDATE SET NULL; + +ALTER TABLE net + ADD FOREIGN KEY (resource_class_id) + REFERENCES resource_class + ON DELETE SET NULL + ON UPDATE SET NULL; + +ALTER TABLE resource_class + ADD FOREIGN KEY (registrant_id) + REFERENCES registrant + ON DELETE SET NULL + ON UPDATE SET NULL; -- cgit v1.2.3