Generate an SQL file from the file genericmailsql.sql

Sunday, 14 February 2010 16:32 Written by Ron Bassett

The following XML along with a Ruby script, and a sql template file are required to generate an SQL file for the Gentoo Linux Documentation -- Virtual Mailhosting System with Postfix Guide [gentoo.org]

The sample data from the SQL file genericmailsql.sql [gentoo.org] was replaced with keyword placeholders (**) which were then overwritten by the script.

file: virtualmail.xml




  

    mail_sqltemplate.txt

    genericmailsql.sql

  

  

     

      

        examplewebsite.com

        /home/{user}

        {homedir}/.maildir

        root postmaster

      

      

        rootpassword1Bob Smith

        grahampassword3Graham Black

      

    

     

      

        website01.com

        /home/vmail

        {homedir}/{domain}/{user}/.maildir/

      

      

        delilahpassword4Delilah Jones

        tompassword5Tom Jones



require 'rexml/document'

include REXML



doc = Document.new(File.open('virtualmail.xml','r').read)

local_domain, raw_alias = XPath.match(doc.root, 'records/domainx[1]/summary/*/text()').map(&:to_s).values_at(0,-1)

alias_list = raw_alias.split.each_with_index.map {|x, i| "(%s, '%s', '%s@%s')" % [i + 1, x, x, local_domain]}



domains = XPath.match(doc.root, 'records/domainx/summary/domain/text()').map(&:to_s)



transport_list = ["(1, '%s','local:')" % domains.shift]

transport_list << domains.each_with_index.map{|domain, i| "(%s, '%s','virtual:')" % [i + 2, domain]}

transport_list.flatten!



users_list = XPath.match(doc.root, 'records/domainx/records/user').each_with_index.map do |user,i|



  domain, homedir, maildir = XPath.match(user.parent.parent, 'summary/*/text()').map(&:to_s)

  user, password, name = XPath.match(user, '*/text()').map(&:to_s)

  

  h = homedir.sub('{user}',user)

  m = maildir.sub('{homedir}',homedir).sub('{domain}',domain).sub('{user}',user)

  a = [i + 1, user, domain, password, name, 1200 + i, 1200 +i, h, m]



  "(%s,'%s@%s','%s','%s',%s,%s,'%s','%s','','y')" % a

end



template_file = doc.root.text('summary/sql_template')

save_file = doc.root.text('summary/default_output')

buffer = File.open(template_file,'r').read

File.open(save_file,'w') do |f| 

  f.write buffer.sub('**alias**', alias_list.join(",\n"))  \

  .sub('**transport**', transport_list.join(",\n")) \

  .sub('**users**', users_list.join(",\n"))

end

file: genericmailsql.sql


-- MySQL dump 8.22

--

-- Host: localhost    Database: mailsql

-- Server version	3.23.52-log



--

-- Table structure for table 'alias'

--



DROP TABLE IF EXISTS alias;

CREATE TABLE alias (

  id int(11) NOT NULL auto_increment,

  alias varchar(128) NOT NULL default '',

  destination varchar(128) NOT NULL default '',

  PRIMARY KEY  (id)

) TYPE=MyISAM;



/*!40000 ALTER TABLE alias DISABLE KEYS */;



--

-- Dumping data for table 'alias'

--





LOCK TABLES alias WRITE;

INSERT INTO alias VALUES (1, 'root', '
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 '),

(2, 'postmaster', '
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 ');



/*!40000 ALTER TABLE alias ENABLE KEYS */;

UNLOCK TABLES;



--

-- Table structure for table 'relocated'

--



DROP TABLE IF EXISTS relocated;

CREATE TABLE relocated (

  id int(11) NOT NULL auto_increment,

  email varchar(128) NOT NULL default '',

  destination varchar(128) NOT NULL default '',

  PRIMARY KEY  (id)

) TYPE=MyISAM;



/*!40000 ALTER TABLE relocated DISABLE KEYS */;



--

-- Dumping data for table 'relocated'

--





LOCK TABLES relocated WRITE;



/*!40000 ALTER TABLE relocated ENABLE KEYS */;

UNLOCK TABLES;



--

-- Table structure for table 'transport'

--



DROP TABLE IF EXISTS transport;

CREATE TABLE transport (

  id int(11) NOT NULL auto_increment,

  domain varchar(128) NOT NULL default '',

  destination varchar(128) NOT NULL default '',

  PRIMARY KEY  (id),

  UNIQUE KEY domain (domain)

) TYPE=MyISAM;



/*!40000 ALTER TABLE transport DISABLE KEYS */;



--

-- Dumping data for table 'transport'

--





LOCK TABLES transport WRITE;

INSERT INTO transport VALUES (1, 'examplewebsite.com','local:'),

(2, 'website01.com','virtual:');



/*!40000 ALTER TABLE transport ENABLE KEYS */;

UNLOCK TABLES;



--

-- Table structure for table 'users'

--



DROP TABLE IF EXISTS users;

CREATE TABLE users (

  id int(11) NOT NULL auto_increment,

  email varchar(128) NOT NULL default '',

  clear varchar(128) NOT NULL default '',

  name tinytext NOT NULL,

  uid int(11) NOT NULL default '1101',

  gid int(11) NOT NULL default '1101',

  homedir tinytext NOT NULL,

  maildir tinytext NOT NULL,

  quota tinytext NOT NULL,

  postfix enum('n','y') NOT NULL default 'y',

  PRIMARY KEY  (id),

  UNIQUE KEY email (email)

) TYPE=MyISAM;



/*!40000 ALTER TABLE users DISABLE KEYS */;



--

-- Dumping data for table 'users'

--





LOCK TABLES users WRITE;

INSERT INTO users VALUES (1,'
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 ','password1','Bob Smith',1200,1200,'/home/root','/home/root/.maildir','','y'),

(2,'
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 ','password3','Graham Black',1201,1201,'/home/graham','/home/graham/.maildir','','y'),

(3,'
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 ','password4','Delilah Jones',1202,1202,'/home/vmail','/home/vmail/website01.com/delilah/.maildir/','','y'),

(4,'
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 ','password5','Tom Jones',1203,1203,'/home/vmail','/home/vmail/website01.com/tom/.maildir/','','y');



/*!40000 ALTER TABLE users ENABLE KEYS */;

UNLOCK TABLES;



--

-- Table structure for table 'virtual'

--



DROP TABLE IF EXISTS virtual;

CREATE TABLE virtual (

  id int(11) NOT NULL auto_increment,

  email varchar(128) NOT NULL default '',

  destination varchar(128) NOT NULL default '',

  PRIMARY KEY  (id)

) TYPE=MyISAM;



/*!40000 ALTER TABLE virtual DISABLE KEYS */;



--

-- Dumping data for table 'virtual'

--





LOCK TABLES virtual WRITE;



/*!40000 ALTER TABLE virtual ENABLE KEYS */;

UNLOCK TABLES;