# mysql < maptest.sql

use test;

drop table SMTPmap;

CREATE TABLE if not exists SMTPmap
(
  user varchar(200) NOT NULL default '',
  domain varchar(200) NOT NULL default '',
  target_address varchar(200) NOT NULL default '',
  PRIMARY KEY  (user,domain)
);

# select SMTPmap.target_address from SMTPmap, SMTPmap as Domains
# where (SMTPmap.user like ? or SMTPmap.user like "\%")
# and (SMTPmap.domain like ?
# or (SMTPmap.domain like "\%" and Domains.domain like ?))
# order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

insert ignore into SMTPmap values("postmaster","%","postmaster@localhost");
insert ignore into SMTPmap values("hostmaster","%","hostmaster@localhost");

insert ignore into SMTPmap values("hostmaster","mydomain","user@localhost");
insert ignore into SMTPmap values("%","mydomain","mydomain-catchall@localhost");
insert ignore into SMTPmap values("usera","mydomain","user1@localhost");
insert ignore into SMTPmap values("userb","mydomain","user5@localhost");
insert ignore into SMTPmap values("userc","mydomain","user1@localhost");

insert ignore into SMTPmap values("hostmaster","myotherdomain","otheruser@localhost");
insert ignore into SMTPmap values("postmaster","myotherdomain","otheruser@localhost");
insert ignore into SMTPmap values("%","myotherdomain","myotherdomain-catchall@localhost");
insert ignore into SMTPmap values("usera","myotherdomain","user2@localhost");
insert ignore into SMTPmap values("userb","myotherdomain","user2@localhost");
insert ignore into SMTPmap values("userc","myotherdomain","user3@localhost");
insert ignore into SMTPmap values("userd","myotherdomain","user4@localhost");

insert ignore into SMTPmap values("usera","my3rddomain","user1@localhost");
insert ignore into SMTPmap values("userb","my3rddomain","user6@localhost");
insert ignore into SMTPmap values("userc","my3rddomain","user7@localhost");

# postmaster       mydomain -->   postmaster@localhost
# postmaster       myotherdomain -->      otheruser@localhost
# postmaster       my3rddomain -->        postmaster@localhost
# hostmaster       mydomain -->   user@localhost
# hostmaster       myotherdomain -->      otheruser@localhost
# hostmaster       my3rddomain -->        hostmaster@localhost
# usera    mydomain -->   user1@localhost
# usera    myotherdomain -->      user2@localhost
# usera    my3rddomain -->        user1@localhost
# userb    mydomain -->   user5@localhost
# userb    myotherdomain -->      user2@localhost
# userb    my3rddomain -->        user6@localhost
# userc    mydomain -->   user1@localhost
# userc    myotherdomain -->      user3@localhost
# userc    my3rddomain -->        user7@localhost
# userd    mydomain -->   mydomain-catchall@localhost
# userd    myotherdomain -->      user4@localhost
# usere    mydomain -->   mydomain-catchall@localhost
# usere    myotherdomain -->      myotherdomain-catchall@localhost

select "postmaster", " mydomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "postmaster" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "mydomain" or (SMTPmap.domain like "\%" and Domains.domain like "mydomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "postmaster", " myotherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "postmaster" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "myotherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "myotherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "postmaster", " my3rddomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "postmaster" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "my3rddomain" or (SMTPmap.domain like "\%" and Domains.domain like "my3rddomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "postmaster", " otherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "postmaster" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "otherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "otherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "hostmaster", " mydomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "hostmaster" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "mydomain" or (SMTPmap.domain like "\%" and Domains.domain like "mydomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "hostmaster", " myotherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "hostmaster" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "myotherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "myotherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "hostmaster", " my3rddomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "hostmaster" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "my3rddomain" or (SMTPmap.domain like "\%" and Domains.domain like "my3rddomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "hostmaster", " otherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "hostmaster" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "otherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "otherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "usera", " mydomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "usera" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "mydomain" or (SMTPmap.domain like "\%" and Domains.domain like "mydomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "usera", " myotherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "usera" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "myotherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "myotherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "usera", " my3rddomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "usera" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "my3rddomain" or (SMTPmap.domain like "\%" and Domains.domain like "my3rddomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "usera", " otherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "usera" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "otherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "otherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userb", " mydomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userb" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "mydomain" or (SMTPmap.domain like "\%" and Domains.domain like "mydomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userb", " myotherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userb" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "myotherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "myotherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userb", " my3rddomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userb" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "my3rddomain" or (SMTPmap.domain like "\%" and Domains.domain like "my3rddomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userb", " otherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userb" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "otherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "otherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userc", " mydomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userc" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "mydomain" or (SMTPmap.domain like "\%" and Domains.domain like "mydomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userc", " myotherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userc" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "myotherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "myotherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userc", " my3rddomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userc" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "my3rddomain" or (SMTPmap.domain like "\%" and Domains.domain like "my3rddomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userc", " otherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userc" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "otherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "otherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userd", " mydomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userd" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "mydomain" or (SMTPmap.domain like "\%" and Domains.domain like "mydomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userd", " myotherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userd" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "myotherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "myotherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userd", " my3rddomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userd" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "my3rddomain" or (SMTPmap.domain like "\%" and Domains.domain like "my3rddomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "userd", " otherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "userd" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "otherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "otherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "usere", " mydomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "usere" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "mydomain" or (SMTPmap.domain like "\%" and Domains.domain like "mydomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "usere", " myotherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "usere" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "myotherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "myotherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "usere", " my3rddomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "usere" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "my3rddomain" or (SMTPmap.domain like "\%" and Domains.domain like "my3rddomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

select "usere", " otherdomain -->", SMTPmap.target_address from SMTPmap, SMTPmap as Domains
 where (SMTPmap.user like "usere" or SMTPmap.user like "\%")
 and (SMTPmap.domain like "otherdomain" or (SMTPmap.domain like "\%" and Domains.domain like "otherdomain"))
 order by concat(SMTPmap.user,'@',SMTPmap.domain) desc limit 1;

