#!/usr/bin/perl
#
# mksqlmailman - TheSin <thesin@southofheaven.org>
#
# Script to generate SQL in order to migrate list users from Mailman pickle
# files into the MysqlMembership adaptor format, the 'wide' version.
#
# Wed, 14 Jul 2004 14:39:10 -0600
#
# Comments added, and incorportated into the MysqlMembership adaptor
# distribution by Kev Green <kyrian@users.sourceforge.net>, 13/12/2004.
#
# Use at your own risk, this script is included for utility purposes only
# and is in no way warrantied or guaranteed by Kev Green or oRe Net.
#

use strict;
use warnings;

my $list_lists = "/usr/sbin/list_lists";
my $list_members = "/usr/sbin/list_members";

my (@lists, $list);

my @tmp_lists = `$list_lists`;

foreach $list (@tmp_lists) {
	chomp($list);
	if ($list =~ '^\s+(.+) - .*') {
		push(@lists, $1);
	}
}

foreach $list (@lists) {
	print "DROP TABLE IF EXISTS `$list`;\n";
	print "CREATE TABLE IF NOT EXISTS `$list` (\n".
"  address varchar(255) NOT NULL,\n".
"  hide enum('Y','N') NOT NULL default 'N',\n".
"  nomail enum('Y','N') NOT NULL default 'N',\n".
"  ack enum('Y','N') NOT NULL default 'Y',\n".
"  not_metoo enum('Y','N') NOT NULL default 'Y',\n".
"  digest enum('Y','N') NOT NULL default 'N',\n".
"  plain enum('Y','N') NOT NULL default 'N',\n".
"  password varchar(255) NOT NULL default '!',\n".
"  lang varchar(255) NOT NULL default 'en',\n".
"  name varchar(255) default NULL,\n".
"  one_last_digest enum('Y','N') NOT NULL default 'N',\n".
"  user_options bigint(20) NOT NULL default 0,\n".
"  delivery_status varchar(255) NOT NULL default \"None\",\n".
"  topics_userinterest varchar(255) default NULL,\n".
"  delivery_status_timestamp varchar(255) NOT NULL default \"None\",\n".
"  bi_cookie varchar(255) default NULL,\n".
"  bi_score double NOT NULL default '0',\n".
"  bi_noticesleft double NOT NULL default '0',\n".
"  bi_lastnotice date NOT NULL default '0000-00-00',\n".
"  bi_date date NOT NULL default '0000-00-00',\n".
"  PRIMARY KEY  (address)\n".
") TYPE=MyISAM;\n\n";

	my (@members, $member, $name, $address);

	my @tmp_members = `$list_members -f $list`;

	foreach $member (@tmp_members) {
		chomp($member);
		if ($member =~ '^(.+) +\<(.+)\>') {
			$name = $1;
			$address = $2;
			$name =~ s/'/\\'/g;
			print "INSERT INTO `$list` SET name='$name', address='$address';\n";
		}
	}
	print "\n";
}