I just wrote this CLI PHP script to export mail from a 1.x series dbmail database. I thought it may be useful to someone else. Feel free to use it or modify it, but don't complain when it eats your entire hard drive, or causes any harm whatsoever. This should be run as the postmaster user, or as root, or it will likely fail.. so it's potentially very dangerous, blah blah.. you get the picture.

Modify the defines at the top, and it should run on the command line. This is made for exporting a single account at a time, but it would be easy enough to make it loop through all the accounts in the the `users` table.

If you update it to work on 2.x servers, or make it better, please post updates on this wiki.

#!/usr/bin/php
<?
/*
DBMail to maildir

This will go through a dbmail 1.x database, and pull messages for a specified user
and place them in the specified maildir, maintaining their mailbox structure. 

*/

define ("USER", "dbmail_username"); // this is the user to look up in the user table
define ("MAILDIR", "/home/user/maildir/"); // define maildir with ending slash
define ("IMPORTDIR", ".IMPORT_FROM_DBMAIL"); // set this to something other than '' if you want stuff in some subdir. 
						// this is recommended as the script will make a conflicting .INBOX otherwise.
						
define ("MAILDIR_OWNER", USER); 		// use this to set the file owner of the maildir and files created
define ("MAILDIR_GROUP", USER);	// maildir group
define ("MAILDIR_PERM", 0777); // maildir permissions, probably NOT 0777. in Octal. 
define ("SUBSCRIPTION", true); // set to true if a .subscription file is in the maildir specified and needs updating with new directories.
define ("HOSTNAME", "example.com"); // used in the creation of the maildir filename
// Set Mysql Variables Here:
$dbhost = "127.0.0.1";
$dbname = "dbmail";
$dbuser = "dbmail";
$dbpass = "password";


define ("DEBUG", false); // Debug mode, if set to true, SQL statements are printed onscreen 
			// so you can see what the script is trying to do. 




////////////////////////////////////  Mysql Functions



function connectdb() // Connect to MySQL
{
	global $dbh, $dbhost, $dbuser, $dbpass, $dbname;
	$dbh=mysql_connect ($dbhost, $dbuser, $dbpass) or die ('Cannot connect to Database');
	mysql_select_db ($dbname); 
	echo "Database connected...\n";
	
}

function query($query)  // send a query to MySQL
{
	if (DEBUG) {	 
		echo $query;
		}
	$ret = mysql_query($query) or die(mysql_error()."\n".$query."\n");
	return $ret;
}

function assoc($a) // Alias for mysql_fetch_assoc().
{
	return mysql_fetch_assoc($a);
}

function isdata($p) // checks if there was any data returned from the query.
{
	if (mysql_num_rows($p)==0){
		return false;
	}
	return true;
}

// Other functions 

function recurse_chown_chgrp($mypath, $uid, $gid)
{
   $d = opendir ($mypath) ;
   while(($file = readdir($d)) !== false) {
       if ($file != "." && $file != "..") {

           $typepath = $mypath . "/" . $file ;

           //print $typepath. " : " . filetype ($typepath). "<BR>" ;
           if (filetype ($typepath) == 'dir') {
               recurse_chown_chgrp ($typepath, $uid, $gid);
           }

           chown($typepath, $uid);
           chgrp($typepath, $gid);

       }
   }

 } 
function createmaildir($path)
{
	global $subs;
	if (is_file($path)) {
		return false;
	} // already a file, failed. 
	if (!is_dir($path)) 
		mkdir($path, MAILDIR_PERM);
	// create directories MAILDIR_PERM
	if (!is_dir("$path/new"))
		mkdir("$path/new", MAILDIR_PERM);
		chmod("$path/new", MAILDIR_PERM);
	if (!is_dir("$path/cur"))
		mkdir("$path/cur", MAILDIR_PERM);
		chmod("$path/cur", MAILDIR_PERM);
	if (!is_dir("$path/tmp"))
		mkdir("$path/tmp", MAILDIR_PERM);
		chmod("$path/tmp", MAILDIR_PERM);
	
	return true;
}

//////////////////////////////////////////
// MAIN ROUTINE
//////////////////////////////////////





// okay, setup db
connectdb();
// setup compatibility for file_put_contents
require_once 'PHP/Compat.php';

// load file_put_contents
PHP_Compat::loadFunction('file_put_contents');

// open subscription file if necessary
if (SUBSCRIPTION == true) {
	$subs = fopen(MAILDIR.".subscriptions", "a+");
	
	if (!$subs)
		die("Could not open subscriptions file.\n");
}

// get user info
echo "Searching in database for ".USER."\n";
$userp = query("select * from users where userid = '".USER."'");

if (isdata($userp)) {
	$user = assoc($userp);
} else {
	die (USER." user not found. Exiting.\n");
}
// create main import directory
if (IMPORTDIR != '') {
	echo "Creating import directory: ".IMPORTDIR."\n";
if (!createmaildir(MAILDIR.IMPORTDIR))
	die("Can't create import directory!\n");
else 
	fwrite($subs, IMPORTDIR."\n");
}
// get mailboxes
$mailboxes = query("select * from mailboxes where owner_idnr = '".$user['user_idnr']."'");
// go through each one.
while ($mailbox = assoc($mailboxes)) {
	echo "Mailbox: ".$mailbox['name']."\n";
	$totalmessages = 0;
	// fix the subdirectories
	$mailbox['name'] = str_replace("/", ".", $mailbox['name']);
	// create directory
	if (createmaildir(MAILDIR.IMPORTDIR.".".$mailbox['name'])) {
		
		// set subscription
		if (SUBSCRIPTION == true) 
		fwrite($subs, trim(IMPORTDIR.".".$mailbox['name'], ".")."\n");
		
		// get messages
		$messages = query("select * from messages where mailbox_idnr = '".$mailbox['mailbox_idnr']."'");
		// go through and create files for each
		while ($message = assoc($messages)) {
			
			// create filename
			$filename = $message['unique_id'].".".time().$message['message_idnr'].".".HOSTNAME.":2,";
			
			
			// look at some flags, this could be extended
			if ($message['answered_flag'] == 1) 
				$filename .= "R";
			if ($message['seen_flag'] == 1) 
				$filename .= "S";
			
			
			// get message contents
			// This assumes that the messageblks are put together sequentially by messageblk_idnr. 
			// This may be a bad assumption
			$contents = query("select * from messageblks where message_idnr = '".$message['message_idnr']."' order by messageblk_idnr ASC");
			$msg = "";
			while ($c = assoc($contents)) {
				$msg .= $c['messageblk'];
			}
			
			// write contents to file
			file_put_contents(MAILDIR.IMPORTDIR.".".$mailbox['name']."/cur/".$filename, $msg);
			chmod(MAILDIR.IMPORTDIR.".".$mailbox['name']."/cur/".$filename, MAILDIR_PERM);
			echo ".";
			$totalmessages++;
		}
	} else {
		echo "ERROR - Could not create directory ".MAILDIR.IMPORTDIR.".".$mailbox['name']."\n";
	}
	
	echo "\n$totalmessages Total Messages.\n\n";
}

recurse_chown_chgrp(MAILDIR, MAILDIR_OWNER, MAILDIR_GROUP);

if (SUBSCRIPTION == true)
	fclose($subs);

echo "Done. \n";
?>
 
exporting_dbmail_users_to_maildir_format._ver_1.x.txt · Last modified: 2012/02/27 21:37 by bas
 
DBMail is developed by Paul J Stevens together with developers world-wide