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, -- US
name 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, -- T
descr 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, -- A
descr 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, -- PV15
name TEXT NOT NULL, -- Paul Vixie
email VARCHAR(96) NOT NULL, -- paul(at)vix(dot)com
pmail TEXT NOT NULL, -- 950 Charter Street
-- Redwood City, CA
pcode VARCHAR(64) NOT NULL, -- 94062
country CHAR(2) NOT NULL, -- US
phone1 VARCHAR(64) NOT NULL, -- +1.650.779.7001
phone2 VARCHAR(64) ,
pgpkid CHAR(8) NOT NULL, -- 8972C7C1
ntype CHAR(1) NOT NULL, -- notify A
comment TEXT ,
www VARCHAR(96) , -- http://www.vix.com/
format FLOAT , -- 1.0
created 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.com
ctype CHAR(1) NOT NULL, -- T
handle VARCHAR(16) NOT NULL, -- PV15
ntype 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.com
addr 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.com
descr TEXT ,
comment TEXT , -- see http://www.vix.com/
format FLOAT , -- 1.0
created 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/23
ctype CHAR(1) NOT NULL, -- T
handle VARCHAR(16) NOT NULL, -- PV15
ntype 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/23
host 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/23
descr TEXT ,
comment TEXT ,
zonekey TEXT ,
whois VARCHAR(64) , -- whois.vix.com
www VARCHAR(96) , -- http://www.vix.com/
format FLOAT , -- 1.0
created 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.COM
ctype CHAR(1) NOT NULL, -- T
handle VARCHAR(16) NOT NULL, -- PV15
ntype 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.COM
host 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.COM
descr TEXT ,
comment TEXT ,
zonekey TEXT ,
whois VARCHAR(64) , -- whois.vix.com
www VARCHAR(96) , -- http://www.vix.com/
format FLOAT , -- 1.0
created 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-BLK
ctype CHAR(1) NOT NULL, -- T
handle VARCHAR(16) NOT NULL, -- PV15
ntype 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-BLK
first INT NOT NULL, -- 199
last INT NOT NULL, -- 203
descr TEXT ,
comment TEXT ,
whois VARCHAR(64) , -- whois.vix.com
www VARCHAR(96) , -- http://www.vix.com/
format FLOAT , -- 1.0
created DATETIME ,
updated DATETIME NOT NULL
);
CREATE UNIQUE INDEX asblk_names ON asblks ( asblk );
GRANT all ON asblks TO www;

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