aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--docs/rpki-db-schema.pdfbin7261 -> 5498 bytes
-rw-r--r--docs/rpki-db-schema.sql417
-rw-r--r--docs/sample-irdb-data.sql130
-rw-r--r--docs/sample-irdb.pdfbin3145 -> 2340 bytes
-rw-r--r--docs/sample-irdb.sql154
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
--- a/docs/rpki-db-schema.pdf
+++ b/docs/rpki-db-schema.pdf
Binary files 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
--- a/docs/sample-irdb.pdf
+++ b/docs/sample-irdb.pdf
Binary files 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;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-12 20:31:31 +0000