Couldn't this be solved by having a headers table like this?
Create table headers(
hid serial primary key,
physmessage_id int not null,
header varchar not null,
value varchar not null,
);
create index headers_physid_header on headers(physmessage_id,header);
This would let us do the following:
Select value from headers where physmessage_id='4' AND header='From';
Actually, normalize this one step further for a rather large space
savings (and potentially a speed boost):
CREATE TABLE headers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE UNIQUE INDEX headers_name_udx ON headers(name);
CREATE TABLE header_msg_map (
id SERIAL8 PRIMARY KEY,
physmessage_id INT NOT NULL,
header_id INT NOT NULL,
value TEXT NOT NULL,
hdr_order INT2 NOT NULL,
FOREIGN KEY (header_id) REFERENCES headers(id)
);
CREATE INDEX header_msg_map_idx ON header_msg_map(physmessage_id,
header_id);
How many "From", "Message-Id", "Date", "Subject" headers do you think
there are in a typical dbmail install? Whatever that number is,
replacing it with a 4 byte value would be much better than whatever the
current setup is. Anyway, when a message is inserted, rip out the
headers, stuff new, unknown headers into the headers table, then stuff
the message into header_msg_map. I added a the hdr_order column that
way it'd be possible to do something like 'SELECT h.name, hmm.value,
hmm.physmessage_id FROM headers h, header_msg_map hmm WHERE h.id =
hmm.header_id ORDER BY hdr_order' to retrieve the headers in the same
order they were sent to the system. The headers table would grow
infinitely for the most part. Anyway, just my $0.02.
On average, most headers are going to be more than 4 bytes... if
someone knows how to figure that out, the difference in bytes would be
roughly the difference of the following pseudo queries: 'SELECT 4 *
COUNT(*) FROM total_num_headers' and 'SELECT SUM(LENGTH(msg_headers))
FROM all_headers'.
If someone points me in the right direction, I'll crank out the code
necessary to make that happen. -sc
--
Sean Chittenden
