Thread

CIDR/INET type and IANA/ICANN

Paul Vixie <paul@vix.com> — 1998-10-21T00:46:55Z

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@vix.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:

Re: [HACKERS] CIDR/INET type and IANA/ICANN

Bruce Momjian <maillist@candle.pha.pa.us> — 1998-10-21T03:11:32Z

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

Added.  I put this at the top of the inet.c file, and next to your name
on the TODO list.  It is on the web site TODO list now.  Hope it is OK. 
I have spelled out his full name:

    * Paul A Vixie <paul@vix.com> (Jon Postel RIP 16 Oct 1998)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] CIDR/INET type and IANA/ICANN

Matthew N. Dodd <winter@jurai.net> — 1998-10-21T16:33:06Z

On Tue, 20 Oct 1998, Paul A Vixie wrote:

CREATE FUNCTION check_primary_key ()
	RETURNS opaque
	AS '/opt/PGpgsql/modules/refint.so'	-- replace with real path
	LANGUAGE 'c'
;

CREATE FUNCTION check_foreign_key ()
	RETURNS opaque
	AS '/opt/PGpgsql/modules/refint.so'	-- replace with real path
	LANGUAGE 'c'
;

> CREATE TABLE countries (
> 	code	CHAR(2)		NOT NULL,	-- US
> 	name	VARCHAR(25)	NOT NULL,	-- United States

	PRIMARY KEY (code)

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

[snip]

> DROP TABLE contacts;
> CREATE TABLE contacts (
> 	handle	VARCHAR(16)	NOT NULL,	-- PV15
> 	name	TEXT		NOT NULL,	-- Paul Vixie
> 	email	VARCHAR(96)	NOT NULL,	-- paul@vix.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,

	-- note: while postgresql does not support 'foreign key'
	-- statements, we include it here just to remind ourselves
	-- that we are using the refint.so calls to do the same thing.
	FOREIGN KEY (country) REFERENCES countries
		ON DELETE CASCADE
		ON UPDATE CASCADE
> );
> 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;

CREATE TRIGGER t_countries_country
	BEFORE DELETE OR UPDATE ON countries
	FOR EACH ROW
	EXECUTE PROCEDURE
	check_foreign_key(1, 'cascade', 'code',
		'contacts', 'countries');

CREATE TRIGGER t_contacts_countries
	BEFORE INSERT OR UPDATE ON contacts
	FOR EACH ROW
	EXECUTE PROCEDURE
	check_primary_key('countries', 'countries', 'code');

[snip]

Look in contrib for the refint.c file and examples.

-- 
| Matthew N. Dodd  | 78 280Z | 75 164E | 84 245DL | FreeBSD/NetBSD/Sprite/VMS |
| winter@jurai.net |      This Space For Rent     | ix86,sparc,m68k,pmax,vax  |
| http://www.jurai.net/~winter | Are you k-rad elite enough for my webpage?   |

Dragana Obradovic <dobradov@suitesvr.hitel.com> — 1998-10-22T20:12:38Z

subscribe