Cleanup prior to hand-editing

svn path=/docs/rpki-db-schema.pdf; revision=1023

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
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
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;