create table packet (
	packet_id INT AUTO_INCREMENT NOT NULL,
	packet BLOB NOT NULL,

	PRIMARY KEY (packet_id)
);

/* tag 6 */
create table public_key (
	packet_id INT NOT NULL,
	pubkey_id INT NOT NULL, /* same as packet_id for a pubkey */

	subkey INT NOT NULL, /* 0 if pubkey, 1 if subkey */

	/* parsed from pubkey */
	modlength INT NOT NULL,
	pkalg INT NOT NULL,
	created DATETIME NOT NULL,
	flags INT NOT NULL,

	/* V3: parsed from pubkey
	   V4: imported from self-sig */
	expires DATETIME NOT NULL,

	/* computed from pubkey */
	keyid VARCHAR(8) NOT NULL,
	fingerprint VARCHAR(20) NOT NULL,

	/* set to 'now' when modified, only for pubkey */
	last_modified DATETIME NOT NULL,

	INDEX (pubkey_id),
	INDEX (created),
	INDEX (expires),
	INDEX (keyid),
	INDEX (fingerprint),
	INDEX (last_modified)
);

/* tag 2 */
create table signature (
	packet_id INT NOT NULL,
	pubkey_id INT NOT NULL,

	/* parsed from signature */
	sigtype INT NOT NULL,

	created DATETIME NOT NULL,
	issuer VARCHAR(8) NOT NULL,
	pkalg INT NOT NULL,
	hashalg INT NOT NULL,

	/* imported from self-sig (V4 only) */
	expires DATETIME;

	/* implicit in packet ordering */

	subject_key INT,	/* used for types 0x10-0x13 0x18 0x1f 0x20 */
	subject_subkey INT,	/* used for types 0x18 0x28 */
	subject_userid INT,	/* used for types 0x10-0x13 */

	/* parsed from the primary user id subpacket, if the
	   subtype is a self-sig (type 0x10-0x13, issuer == key.keyid) */
	primary_userid INT, /* boolean */

	INDEX (pubkey_id),
	INDEX (created),
	INDEX (expires),
	INDEX (issuer)
);

/* tag 13 */
create table userid (
	packet_id INT NOT NULL,
	pubkey_id INT NOT NULL,

	/* parsed from userid */
	printable TEXT NOT NULL,

	INDEX (pubkey_id)
);

create table userid_word (
	word VARCHAR(255) NOT NULL,
	pubkey_id INT NOT NULL,

	INDEX (word)
)

/*

select pubkey_id,count(word)
  from userid_word
  where word in ("marc","mit","edu")
  group by pubkey_id
  having count(word) = 3



select word,count(word)
  from userid_word
  where word in ("marc","mit","edu")
  order by count(word)

select w1.pubkey_id
  from userid_word as w1,
       userid_word as w2,
       userid_word as w3
  where w1.word = "marc"
    and w2.word = "mit" and w2.pubkey_id = w1.pubkey_id
    and w3.word = "edu" and w3.pubkey_id = w1.pubkey_id

*/

;