script for email archiving

johanvdw

Hi, does anyone know a good script (if possible php, may be perl) to archive email?
I've been searching on the internet, but that's very difficult because the words archive and email are together on all email-listarchives.
I've found one, http://php-mail-arc.sourceforge.net/ , but unfortunately, it uses postgresql, and my provider doesn't have that.

Otherwise, I'll just have to 'reverse engineer' some webmailscript I guess, or use something like the Horde to be able to use some already written API's.

dannys

So why not just replace the postgresql code with the relevent code for your db ?

Unless the project uses loads of pgsql specific queries (which is unlikely) you shouldn't have too much trouble converting it to use mySql or whatever.

johanvdw

Originally posted by dannys
So why not just replace the postgresql code with the relevent code for your db ?

Unless the project uses loads of pgsql specific queries (which is unlikely) you shouldn't have too much trouble converting it to use mySql or whatever.

I haven't checked thouroughly (will do that next week). But there were 'nested' querys like

select ml.msg_id, date_sent, msg_from, msg_subject, msg_base_subject
from msg_list ml, msg_from mf, msg_subject ms, msg_base_subject mbs
where ml.msg_id = mf.msg_id
and ml.msg_id = ms.msg_id
and ml.msg_id = mbs.msg_id
and date_sent >= '2/6/2000'
and ml.msg_id in (
select distinct ml.msg_id
from msg_list ml, msg_text mt
where ml.list_name = 'open-source-discussion'
and date_sent >= '2/6/2000'
and ml.msg_id = mt.msg_id
and msg_text ~ '.test.*'
)
order by date_sent asc;

Those query's aren't supported in Mysql I thought.

dannys

No Subselects aren't supported by MySQL (why doesn't your host support PGSQL anyway?) but there's nothing to stop you sending two queries.


$result=mysql_query("select distinct ml.msg_id 
from msg_list ml, msg_text mt 
where ml.list_name = 'open-source-discussion' 
and date_sent >= '2/6/2000' 
and ml.msg_id = mt.msg_id 
and msg_text ~* '.*test.*'");

$msgIds="";
while ($row=mysql_fetch_row($result))
	$msgIds.=$row[0]." ";
$msgIds=substr($msgIds,0,-1);

$result=mysql_query("
select ml.msg_id, date_sent, msg_from, msg_subject, msg_base_subject 
from msg_list ml, msg_from mf, msg_subject ms, msg_base_subject mbs 
where ml.msg_id = mf.msg_id 
and ml.msg_id = ms.msg_id 
and ml.msg_id = mbs.msg_id 
and date_sent >= '2/6/2000' 
and ml.msg_id in ( $msg_ids ) 
order by date_sent asc");

etc.

I'll admit, it'll be irritating as hell to do though (why can't people write db independant code?).

johanvdw

Thanks for the code. I was considering to do it that somehow that way.
I'm planning to rewrite the code with a database extraction layer, and add different attachment handling.
Maybe I'll just start over from scratch, I'll have to see what code I can recycle.
There are some pretty good libraries like the Horde I already know a little, and maybe it's less work to start from scratch using those libraries, than to start from this old email archiver.