Readability

svn path=/docs/sample-irbe.sql; revision=1019

1 files changed, 67 insertions, 57 deletions

diff --git a/docs/sample-irbe.sql b/docs/sample-irbe.sql
index 7cc75d30..1a57860f 100644
--- a/docs/sample-irbe.sql
+++ b/docs/sample-irbe.sql
@@ -1,11 +1,5 @@
-drop table if exists net;

-drop table if exists asn;

-drop table if exists resource_class;

-drop table if exists registrant;

-

-

-

 

+DROP TABLE IF EXISTS asn;

 CREATE TABLE asn (

        asn_id               SERIAL NOT NULL,

        start_as             BIGINT unsigned NOT NULL,

@@ -18,10 +12,10 @@ CREATE UNIQUE INDEX XPKasn ON asn
        asn_id

 );

 

-

 ALTER TABLE asn

        ADD PRIMARY KEY (asn_id);

 

+DROP TABLE IF EXISTS net;

 

 CREATE TABLE net (

        net_id               SERIAL NOT NULL,

@@ -36,11 +30,12 @@ CREATE UNIQUE INDEX XPKnet ON net
        net_id

 );

 

-

 ALTER TABLE net

        ADD PRIMARY KEY (net_id);

 

 

+DROP TABLE IF EXISTS registrant;

+

 CREATE TABLE registrant (

        registrant_id        SERIAL NOT NULL,

        IRBE_mapped_id       TEXT

@@ -51,10 +46,10 @@ CREATE UNIQUE INDEX XPKregistrant ON registrant
        registrant_id

 );

 

-

 ALTER TABLE registrant

        ADD PRIMARY KEY (registrant_id);

 

+DROP TABLE IF EXISTS resource_class;

 

 CREATE TABLE resource_class (

        resource_class_id    SERIAL NOT NULL,

@@ -68,25 +63,21 @@ CREATE UNIQUE INDEX XPKresource_class ON resource_class
        resource_class_id

 );

 

-

 ALTER TABLE resource_class

        ADD PRIMARY KEY (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

@@ -94,48 +85,67 @@ ALTER TABLE resource_class
                              ON UPDATE SET NULL;

 

 

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