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: