CIDR/INET type and IANA/ICANN

as most of you know by now, jon postel, the original IANA, died last week.
i'm sorry that i've been so flakey in my correspondance here but it's been
a rough time for those of us who knew him and/or who were helping him turn
IANA into a new nonprofit corporation called ICANN.  see http://www.iana.org/
for more details.

the original "cidr" type was written as part of a prototype registry to let
IANA take back some of the duties it had outsourced to InterNIC.  so while
COM, NET, and ORG are likely to continue to be operated by NSI/InterNIC for
the next few years, and while the networks and ASNs are being handed out via
regional registries, there is a "top level" registry function that IANA, and
now ICANN, has to provide directly.

i wanted this to be done with postgres, and i am still working toward that.
here are some brief notes on the overall database structure i envisioned, in
case anyone is wondering about the application "cidr" was written for.  note
that the type will at present be called "inet" in pgsql 6.4.

i would be very happy if someone with access to the pgsql 6.4 sources would
add a comment to the top of the file which implements the CIDR/INET type,
to the effect of /* JBP RIP 16Oct98 */.  thanks.

---------------------------------------------------------------- countries

DROP TABLE countries;
CREATE TABLE countries (code    CHAR(2)        NOT NULL,    -- USname    VARCHAR(25)    NOT NULL    -- United States
);
CREATE UNIQUE INDEX country_codes ON countries ( code );
INSERT INTO countries VALUES ('US', 'United States');
INSERT INTO countries VALUES ('CA', 'Canada');
INSERT INTO countries VALUES ('MX', 'Mexico');
INSERT INTO countries VALUES ('UK', 'United Kingdom');
INSERT INTO countries VALUES ('SE', 'Sweden');
-- XXX more needed here
GRANT all ON countries TO www;

---------------------------------------------------------------- contacts

DROP TABLE contact_types;
CREATE TABLE contact_types (code    CHAR(1)        NOT NULL,    -- Tdescr    VARCHAR(25)    NOT NULL    -- Technical
);
CREATE UNIQUE INDEX contact_codes ON contact_types ( code );
INSERT INTO contact_types VALUES ('B', 'Billing');
INSERT INTO contact_types VALUES ('A', 'Administrative');
INSERT INTO contact_types VALUES ('R', 'Registry');
INSERT INTO contact_types VALUES ('T', 'Technical');

DROP TABLE notify_types;
CREATE TABLE notify_types (code    CHAR(1)        NOT NULL,    -- Adescr    VARCHAR(25)    NOT NULL    -- After
);
CREATE UNIQUE INDEX notify_codes ON notify_types ( code );
INSERT INTO notify_types VALUES ('B', 'Before');
INSERT INTO notify_types VALUES ('A', 'After');
INSERT INTO notify_types VALUES ('N', 'Never');

DROP TABLE contacts;
CREATE TABLE contacts (handle    VARCHAR(16)    NOT NULL,    -- PV15name    TEXT        NOT NULL,    -- Paul Vixieemail
  VARCHAR(96)    NOT NULL,    -- paul@vix.compmail    TEXT        NOT NULL,    -- 950 Charter Street
--Redwood City, CApcode    VARCHAR(64)    NOT NULL,    -- 94062country    CHAR(2)        NOT NULL,    -- USphone1
VARCHAR(64)   NOT NULL,    -- +1.650.779.7001phone2    VARCHAR(64)    ,pgpkid    CHAR(8)        NOT NULL,    --
8972C7C1ntype   CHAR(1)        NOT NULL,    -- notify Acomment    TEXT        ,www    VARCHAR(96)    ,        --
http://www.vix.com/format   FLOAT        ,        -- 1.0created    DATETIME    NOT NULL,updated    DATETIME    NOT
NULL
);
CREATE UNIQUE INDEX contact_handles ON contacts ( handle );
-- wish there was a way to require country to match a key in countries.
GRANT all ON contacts TO www;

---------------------------------------------------------------- hosts

DROP TABLE host_contacts;
CREATE TABLE host_contacts (host    VARCHAR(64)    NOT NULL,    -- gw.home.vix.comctype    CHAR(1)        NOT NULL,
--Thandle    VARCHAR(16)    NOT NULL,    -- PV15ntype    CHAR(1)                -- notify
 
);
CREATE UNIQUE INDEX host_contacts_index ON host_contacts                    ( host, ctype, handle );
GRANT all ON host_contacts TO www;

DROP TABLE host_addresses;
CREATE TABLE host_addresses (host    VARCHAR(64)    NOT NULL,    -- gw.home.vix.comaddr    CIDR        NOT NULL    --
192.5.5.1/32
);
CREATE UNIQUE INDEX host_addresses_index ON host_addresses                    ( host, addr );

DROP TABLE hosts;
CREATE TABLE hosts (host    VARCHAR(64)    NOT NULL,    -- gw.home.vix.comdescr    TEXT        ,comment    TEXT
,       -- see http://www.vix.com/format    FLOAT        ,        -- 1.0created    DATETIME    NOT NULL,updated
DATETIME   NOT NULL
 
);
CREATE UNIQUE INDEX host_names ON hosts ( host );
GRANT all ON hosts TO www;

---------------------------------------------------------------- netblocks

DROP TABLE netblock_contacts;
CREATE TABLE netblock_contacts (net    CIDR        NOT NULL,    -- 192.5.4/23ctype    CHAR(1)        NOT NULL,    --
Thandle   VARCHAR(16)    NOT NULL,    -- PV15ntype    CHAR(1)                -- notify
 
);
CREATE UNIQUE INDEX netblock_contacts_index ON netblock_contacts                    ( net, ctype, handle );
GRANT all ON netblock_contacts TO www;

DROP TABLE netblock_nameservers;
CREATE TABLE netblock_nameservers (net    CIDR        NOT NULL,    -- 192.5.4/23host    VARCHAR(64)    NOT NULL    --
gw.home.vix.com
);
CREATE UNIQUE INDEX netblock_nameservers_index ON netblock_nameservers                    ( net, host );
GRANT all ON netblock_nameservers TO www;

DROP TABLE netblocks;
CREATE TABLE netblocks (net    CIDR        NOT NULL,    -- 192.5.4/23descr    TEXT        ,comment    TEXT
,zonekey   TEXT        ,whois    VARCHAR(64)    ,        -- whois.vix.comwww    VARCHAR(96)    ,        --
http://www.vix.com/format   FLOAT        ,        -- 1.0created    DATETIME    NOT NULL,updated    DATETIME    NOT
NULL
);
CREATE UNIQUE INDEX netblock_nets ON netblocks ( net );
GRANT all ON netblocks TO www;

---------------------------------------------------------------- domains

DROP TABLE domain_contacts;
CREATE TABLE domain_contacts (domain    VARCHAR(64)    NOT NULL,    -- VIX.COMctype    CHAR(1)        NOT NULL,    --
Thandle   VARCHAR(16)    NOT NULL,    -- PV15ntype    CHAR(1)                -- notify
 
);
CREATE UNIQUE INDEX domain_contacts_index ON domain_contacts                    ( domain, ctype, handle );
GRANT all ON domain_contacts TO www;

DROP TABLE domain_nameservers;
CREATE TABLE domain_nameservers (domain    VARCHAR(64)    NOT NULL,    -- VIX.COMhost    VARCHAR(64)    NOT NULL    --
gw.home.vix.com
);
CREATE UNIQUE INDEX domain_nameservers_index ON domain_nameservers                    ( domain, host );
GRANT all ON domain_nameservers TO www;

DROP TABLE domains;
CREATE TABLE domains (domain    VARCHAR(64)    NOT NULL,    -- VIX.COMdescr    TEXT        ,comment    TEXT
,zonekey   TEXT        ,whois    VARCHAR(64)    ,        -- whois.vix.comwww    VARCHAR(96)    ,        --
http://www.vix.com/format   FLOAT        ,        -- 1.0created    DATETIME    NOT NULL,updated    DATETIME    NOT
NULL
);
CREATE UNIQUE INDEX domain_domains ON domains ( domain );
GRANT all ON domains TO www;

---------------------------------------------------------------- asblks

DROP TABLE asblk_contacts;
CREATE TABLE asblk_contacts (asblk    VARCHAR(64)    NOT NULL,    -- BARRNET-BLKctype    CHAR(1)        NOT NULL,    --
Thandle   VARCHAR(16)    NOT NULL,    -- PV15ntype    CHAR(1)                -- notify
 
);
CREATE UNIQUE INDEX asblk_contacts_index ON asblk_contacts                    ( asblk, ctype, handle );
GRANT all ON asblk_contacts TO www;

DROP TABLE asblks;
CREATE TABLE asblks (asblk    VARCHAR(64)    NOT NULL,    -- BARRNET-BLKfirst    INT        NOT NULL,    -- 199last
INT       NOT NULL,    -- 203descr    TEXT        ,comment    TEXT        ,whois    VARCHAR(64)    ,        --
whois.vix.comwww   VARCHAR(96)    ,        -- http://www.vix.com/format    FLOAT        ,        -- 1.0created
DATETIME   ,updated    DATETIME    NOT NULL
 
);
CREATE UNIQUE INDEX asblk_names ON asblks ( asblk );
GRANT all ON asblks TO www;

---------------------------------------------------------------- :EOF: