-- Sample Data
--
-- Syntax note and WARNING TO NON-SQLITE PORTERS:
--
-- SQLite kindly accepts NULL values in INTEGER PRIMARY KEY columns.
-- These are then converted to the next increment, the highest number
-- for that column plus one. This is a handy feature here. If you are
-- porting this schema and sample data to another RDBMS, I suggest that
-- you first import it into your own sqlite3, then ".dump" it and enjoy
-- your NULL values converted to INTEGER.
--
-- In our sample, user "ah" is UID 1000. He controls (as registrant) a
-- relay domain, example.net, and a virtual mailbox domain for his
-- family, family.example.net.
--
-- User "rw" is 1001, and he controls a relay domain, relay.example.org,
-- which is split between two backends. His example.org is a local
-- domain, and the machine's fully qualified domain name, also local, is
-- myhostname.example.org. His family are also system users, and
-- rw.example.org is a virtual alias domain for them. (All system users
-- here have primary GID 100, "users".) He also has one virtual mailbox
-- domain: tlug.example.org.
--
-- User "rob0" is UID 1002. Okay, that's a real person (FSVO real): it
-- is me. I control example.com as a virtual alias domain.
-- relay.example.com is also mine, a relay domain, and vfd.example.com
-- is my virtual mailbox domain. Finally, whn.example.com is my wife's
-- virtual alias domain (nominally under my control, but you know how
-- that goes!) and it has ... ugh! ... a wildcard or catchall address!
-- (Don't try this at home, kids!)
--
--
PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
--
CREATE TABLE "Transport" (id INTEGER PRIMARY KEY,
	active INTEGER DEFAULT 1, transport TEXT, nexthop INTEGER,
	mx INTEGER DEFAULT 1, port INTEGER,
	UNIQUE (transport,nexthop,mx,port));
-- Transport.nexthop would be a pointer to Domain.id, but without a
-- foreign key constraint because the Domain table has yet to be
-- created at this point.
--	A special record, id=0, is here for ":". In Postfix terms this
-- means "do not change the transport:nexthop as would have been used
-- for this address according to the configuration."
INSERT INTO "Transport" VALUES(0,1,'','',1,NULL);
-- These are various standard transports with null nexthop.
INSERT INTO "Transport" VALUES(NULL,1,'smtp',NULL,1,NULL);
INSERT INTO "Transport" VALUES(NULL,1,'virtual',NULL,1,NULL);
INSERT INTO "Transport" VALUES(NULL,1,'lmtp',NULL,1,NULL);
INSERT INTO "Transport" VALUES(NULL,1,'relay',NULL,1,NULL);
INSERT INTO "Transport" VALUES(NULL,1,'local',NULL,1,NULL);
-- This is how you'd enter a custom transport in addition to its
-- definition in the master.cf file.
INSERT INTO "Transport" VALUES(NULL,1,'throttled',NULL,1,NULL);
-- Further transports will be added after the Domain table exists and
-- has been populated, see the second transaction below.
--
CREATE TABLE "Domain" (id INTEGER PRIMARY KEY, name TEXT,
	active INTEGER DEFAULT 1, class INTEGER DEFAULT 0,
	owner INTEGER DEFAULT 0, transport INTEGER,
	rclass INTEGER DEFAULT 30, UNIQUE (name),
	FOREIGN KEY(transport) REFERENCES Transport(id));
-- This revision removes the NOT NULL constraint from Domain.name; we
-- will insert a special record Domain.id=0 with Domain.name=NULL,
-- which thus maintains a defacto NOT NULL constraint for other rows.
INSERT INTO "Domain" VALUES(0,NULL,NULL,NULL,NULL,NULL,NULL);
--
-- typical mydestination entries
INSERT INTO "Domain" VALUES(1,'localhost',1,1,0,NULL,0);
INSERT INTO "Domain" VALUES(2,'localhost.myhostname.example.org',1,1,0,NULL,0);
INSERT INTO "Domain" VALUES(3,'myhostname.example.org',1,1,0,NULL,10);
-- rw's domains
INSERT INTO "Domain" VALUES(4,'example.org',1,1,1001,NULL,30);
INSERT INTO "Domain" VALUES(5,'relay.example.org',1,2,1001,NULL,20);
INSERT INTO "Domain" VALUES(6,'rw.example.org',1,3,1001,NULL,20);
INSERT INTO "Domain" VALUES(7,'tlug.example.org',1,802,1001,NULL,40);
-- ah's domains
INSERT INTO "Domain" VALUES(8,'example.net',1,2,1000,NULL,30);
INSERT INTO "Domain" VALUES(9,'family.example.net',1,801,1000,NULL,20);
-- rob0's domains
INSERT INTO "Domain" VALUES(10,'example.com',1,3,1002,NULL,30);
INSERT INTO "Domain" VALUES(11,'relay.example.com',1,2,1002,4,20);
INSERT INTO "Domain" VALUES(12,'vfd.example.com',1,803,1002,NULL,30);
INSERT INTO "Domain" VALUES(13,'whn.example.com',1,3,1002,NULL,29);
-- external domains used for nexthops & alias targets, or transports
INSERT INTO "Domain" VALUES(NULL,'example.info',1,0,0,NULL,NULL);
INSERT INTO "Domain" VALUES(NULL,'example.edu',1,0,0,NULL,NULL);
INSERT INTO "Domain" VALUES(NULL,'y.example.com',1,0,0,6,NULL);
INSERT INTO "Domain" VALUES(NULL,'hairy.example.org',1,0,0,NULL,0);
INSERT INTO "Domain" VALUES(NULL,'custom.example.com',1,0,0,NULL,0);
INSERT INTO "Domain" VALUES(NULL,'no-mx.example.net',1,0,0,NULL,0);
INSERT INTO "Domain" VALUES(NULL,'no-mx.example.edu',1,0,0,NULL,0);
--
CREATE TABLE "Address" (id INTEGER PRIMARY KEY,
	localpart TEXT NOT NULL, domain INTEGER NOT NULL,
	active INTEGER DEFAULT 1, transport INTEGER, rclass INTEGER,
	FOREIGN KEY(domain) REFERENCES Domain(id),
	FOREIGN KEY(transport) REFERENCES Transport(id),
	UNIQUE (localpart, domain));
-- We will insert a special record Address.id=0 with Address.domain=0
-- to differentiate aliases(5) commands, paths or includes from
-- address targets. The localpart is a NUL character, which is not
-- allowed in email addresses.
INSERT INTO "Address" VALUES(0,X'00',0,NULL,NULL,NULL);
-- System users: id is their system UID*-1. These entries are only for
-- actual human users. Other system accounts, if they are to be mail
-- recipients, should be listed as aliases. Why use a negative number?
-- That way it does not affect the autoincrementing with id=NULL, or
-- when id is not specified. This aims at eventual integration with
-- nss-sqlite.
INSERT INTO "Address" VALUES(-1000,'ah',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(-1001,'rw',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(-1002,'rob0',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(-1003,'m1',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(-1004,'m2',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(-1005,'m3',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(-1006,'whn',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(-1007,'kid0',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(-1008,'kid1',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(-1009,'kid2',0,1,NULL,NULL);
-- SQL quoting of an apostrophe
INSERT INTO "Address" VALUES(-1010,'o''brien',0,1,NULL,NULL);
-- IMAP login disabled
INSERT INTO "Address" VALUES(-1011,'virus',0,-1,NULL,NULL);
-- local aliases(5) (alias_maps)
INSERT INTO "Address" VALUES(1,'root',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(2,'postmaster',0,1,NULL,40);
INSERT INTO "Address" VALUES(3,'abuse',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(4,'bin',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(5,'daemon',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(6,'MAILER-DAEMON',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(7,'named',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(8,'operator',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(9,'www',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(10,'us',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(11,'include.example',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(12,'command.example',0,1,NULL,NULL);
INSERT INTO "Address" VALUES(13,'file.example',0,1,NULL,NULL);
-- 5 - relay.example.org
INSERT INTO "Address" VALUES(NULL,'postmaster',5,1,NULL,40);
INSERT INTO "Address" VALUES(NULL,'abuse',5,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'tom',5,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'dick',5,1,NULL,NULL);
-- transport needed
INSERT INTO "Address" VALUES(NULL,'harry',5,1,2,1);
-- 6 - rw.example.org
INSERT INTO "Address" VALUES(NULL,'postmaster',6,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'abuse',6,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'rw',6,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'m1',6,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'m2',6,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'m3',6,1,NULL,NULL);
-- 7 - tlug.example.org
INSERT INTO "Address" VALUES(NULL,'postmaster',7,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'abuse',7,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'alice',7,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'bob',7,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'connie',7,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'geek1',7,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'geek2',7,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'rw',7,1,NULL,NULL);
-- 8 - example.net
INSERT INTO "Address" VALUES(NULL,'postmaster',8,1,NULL,40);
INSERT INTO "Address" VALUES(NULL,'abuse',8,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'ah',8,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'beth',8,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'sally',8,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'kathy',8,1,NULL,NULL);
-- 9 - family.example.net
INSERT INTO "Address" VALUES(NULL,'postmaster',9,1,NULL,40);
INSERT INTO "Address" VALUES(NULL,'abuse',9,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'ah',9,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'daddy',9,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'mama',9,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'sissy',9,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'boy',9,1,NULL,NULL);
-- 10 - example.com
INSERT INTO "Address" VALUES(NULL,'postmaster',10,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'abuse',10,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'webmaster',10,1,NULL,10);
-- redirected to local delivery
INSERT INTO "Address" VALUES(NULL,'rob0',10,1,5,NULL);
INSERT INTO "Address" VALUES(NULL,'info',10,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'jobs',10,1,NULL,NULL);
-- 11 - relay.example.com
INSERT INTO "Address" VALUES(NULL,'postmaster',11,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'abuse',11,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'manager',11,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'foreman1',11,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'foreman2',11,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'foreman3',11,1,NULL,NULL);
-- transport
INSERT INTO "Address" VALUES(NULL,'techie',11,1,5,NULL);
-- 12 - vfd.example.com
INSERT INTO "Address" VALUES(NULL,'postmaster',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'abuse',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'rob0',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'chief',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'asst-chief',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'capt',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'secretary',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'tim',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'jason',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'tom',12,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'lisa',12,1,NULL,NULL);
-- IMAP login disabled
INSERT INTO "Address" VALUES(NULL,'dewayne',12,-1,NULL,NULL);
-- 13 - whn.example.com
INSERT INTO "Address" VALUES(NULL,'postmaster',13,1,NULL,10);
INSERT INTO "Address" VALUES(NULL,'abuse',13,1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'rob0',13,1,NULL,10);
INSERT INTO "Address" VALUES(NULL,'whn',13,1,NULL,NULL);
-- the dreaded wildcard
INSERT INTO "Address" VALUES(NULL,'',13,1,NULL,NULL);
--
CREATE TABLE "Alias" (id INTEGER PRIMARY KEY,
	address INTEGER NOT NULL, active INTEGER DEFAULT 1,
	target INTEGER NOT NULL, extension TEXT,
	FOREIGN KEY(address) REFERENCES Address(id)
	FOREIGN KEY(target) REFERENCES Address(id)
	UNIQUE(address, target, extension));
-- This revision changes Alias.name to Alias.address, because INTEGER
-- fields should not be called "name" (to me that implies TEXT.) If
-- Alias.target=0, that tells us that the Alias.extension contains a
-- /file/name, or a |command, or an :include:/file/name. Otherwise,
-- Alias.extension contains an address extension.  Differentiation of
-- local(8) aliases(5) from virtual(5) is in the Address table, where
-- Address.domain=0, the special null record in the Domain table.
--
INSERT INTO "Alias" VALUES(1,2,1,1,NULL);
INSERT INTO "Alias" VALUES(2,3,1,1,NULL);
INSERT INTO "Alias" VALUES(3,4,1,1,NULL);
INSERT INTO "Alias" VALUES(4,5,1,1,NULL);
INSERT INTO "Alias" VALUES(5,6,1,1,NULL);
INSERT INTO "Alias" VALUES(6,7,1,1,NULL);
INSERT INTO "Alias" VALUES(7,8,1,1,NULL);
INSERT INTO "Alias" VALUES(8,9,1,1,NULL);
INSERT INTO "Alias" VALUES(9,10,0,-1000,'us');
INSERT INTO "Alias" VALUES(10,10,1,-1001,'us');
INSERT INTO "Alias" VALUES(11,10,1,-1002,'us');
INSERT INTO "Alias" VALUES(12,11,1,0,'|"cat >> /tmp/command.example"');
INSERT INTO "Alias" VALUES(13,12,1,0,':include:/tmp/include.example');
INSERT INTO "Alias" VALUES(14,13,1,0,'/tmp/file.example');
-- VMailbox is populated in the second transaction, below
--
CREATE TABLE "BScat" (id INTEGER PRIMARY KEY,
	sender TEXT NOT NULL, priority INTEGER,
	target TEXT NOT NULL, UNIQUE (sender, priority));
--
INSERT INTO "BScat" VALUES(1,'<>',1,'z-semBscat');
INSERT INTO "BScat" VALUES(2,'<>',2,'z-bscatOrg');
INSERT INTO "BScat" VALUES(3,'postmaster',1,'z-semBscat');
INSERT INTO "BScat" VALUES(4,'postmaster',2,'z-bscatOrg');
INSERT INTO "BScat" VALUES(5,'mailer-daemon',1,'z-semBscat');
INSERT INTO "BScat" VALUES(6,'mailer-daemon',2,'z-bscatOrg');
--
COMMIT;
--
-- Second transaction: these are records which depend on the existence
-- of other records. The prerequisites were all in the transaction
-- above, so now these can be added (or changed).
--
BEGIN TRANSACTION;
-- These records are to be added to the Transport table, but we did not
-- know the nexthop value until those records were added to the Domain
-- table. There are lots of ways to approach this. We could have added
-- the records with NULL or anything as nexthop, which is how the Domain
-- records were done, following.
INSERT INTO "Transport" VALUES(NULL,1,NULL,
    (SELECT id FROM "Domain" WHERE name IS 'relay.example.org'),0,1025);
INSERT INTO "Transport" VALUES(NULL,1,NULL,
    (SELECT id FROM "Domain" WHERE name IS 'hairy.example.org'),1,NULL);
INSERT INTO "Transport" VALUES(NULL,1,NULL,
    (SELECT id FROM "Domain" WHERE name IS 'example.net'),0,NULL);
INSERT INTO "Transport" VALUES(NULL,1,'custom',
    (SELECT id FROM "Domain" WHERE name IS 'custom.example.com'),1,2525);
INSERT INTO "Transport" VALUES(NULL,1,NULL,
    (SELECT id FROM "Domain" WHERE name IS 'custom.example.com'),1,NULL);
INSERT INTO "Transport" VALUES(NULL,1,NULL,
    (SELECT id FROM "Domain" WHERE name IS 'no-mx.example.net'),0,NULL);
INSERT INTO "Transport" VALUES(NULL,1,'smtp',
    (SELECT id FROM "Domain" WHERE name IS 'no-mx.example.edu'),0,587);
-- Some people think it's a good idea to throttle mail to Yahoo! In a
-- few cases they may be right. Here's a sample.
UPDATE "Domain" SET transport=(SELECT id FROM "Transport"
    WHERE transport IS 'throttled') WHERE name IS 'y.example.com';
-- external addresses which will be aliases
INSERT INTO "Address" VALUES(NULL,'alien',
    (SELECT id FROM "Domain" WHERE name IS 'example.info'),1,NULL,NULL);
INSERT INTO "Address" VALUES(NULL,'martian',
    (SELECT id FROM "Domain" WHERE name IS 'example.edu'),1,NULL,NULL);
-- Virtual aliases, two forms of entry, pay attention!
-- "e,a,t" form where "a" & "t" are both queries, uses 3 lines
INSERT INTO "Alias" (extension, address, target) VALUES('relay',
    (SELECT id FROM "Address" WHERE localpart IS 'tom' AND domain=5),
    (SELECT id FROM "Address" WHERE localpart IS 'tom' AND domain=12));
-- "t,e,a" where only "a" is query, fits in only two lines
INSERT INTO "Alias" (target, extension, address) VALUES(-1001,'rw.pm',
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=6));
INSERT INTO "Alias" (target, extension, address) VALUES(-1001,'rw.abuse',
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=6));
INSERT INTO "Alias" (target, extension, address) VALUES(-1003,NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'm1' AND domain=6));
INSERT INTO "Alias" (target, extension, address) VALUES(-1004,NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'm2' AND domain=6));
INSERT INTO "Alias" (target, extension, address) VALUES(-1005,NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'm3' AND domain=6));
INSERT INTO "Alias" (extension, address, target) VALUES('tlug.pm',
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=7),
    (SELECT id FROM "Address" WHERE localpart IS 'rw' AND domain=7));
INSERT INTO "Alias" (extension, address, target) VALUES('tlug.abuse',
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=7),
    (SELECT id FROM "Address" WHERE localpart IS 'rw' AND domain=7));
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=9),
    (SELECT id FROM "Address" WHERE localpart IS 'ah' AND domain=9));
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=9),
    (SELECT id FROM "Address" WHERE localpart IS 'ah' AND domain=9));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'pm.com',
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=10));
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=10),
    (SELECT id FROM "Address" WHERE localpart IS 'techie' AND domain=11));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'ab.com',
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=10));
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=10),
    (SELECT id FROM "Address" WHERE localpart IS 'techie' AND domain=11));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'com',
    (SELECT id FROM "Address" WHERE localpart IS 'rob0' AND domain=10));
INSERT INTO "Alias" (extension, address, target) VALUES('info',
    (SELECT id FROM "Address" WHERE localpart IS 'info' AND domain=10),
    (SELECT id FROM "Address" WHERE localpart IS 'foreman2' AND domain=11));
INSERT INTO "Alias" (extension, address, target) VALUES('info',
    (SELECT id FROM "Address" WHERE localpart IS 'info' AND domain=10),
    (SELECT id FROM "Address" WHERE localpart IS 'foreman3' AND domain=11));
INSERT INTO "Alias" (extension, address, target) VALUES('jobs',
    (SELECT id FROM "Address" WHERE localpart IS 'jobs' AND domain=10),
    (SELECT id FROM "Address" WHERE localpart IS 'manager' AND domain=11));
INSERT INTO "Alias" (extension, address, target) VALUES('jobs',
    (SELECT id FROM "Address" WHERE localpart IS 'jobs' AND domain=10),
    (SELECT id FROM "Address" WHERE localpart IS 'foreman1' AND domain=11));
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'webmaster' AND domain=10),
    (SELECT id FROM "Address" WHERE localpart IS 'techie' AND domain=11));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'pm.r.com',
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=11));
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=11),
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=11));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'ab.r.com',
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=11));
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=11),
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=11));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'pm.vfd',
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=12));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'ab.vfd',
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=12));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'vfd',
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=12));
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'chief' AND domain=12),
    (SELECT id FROM "Address" WHERE localpart IS 'tim' AND domain=12));
INSERT INTO "Alias" (active, address, target) VALUES(1,
    (SELECT id FROM "Address" WHERE localpart IS 'asst-chief' AND domain=12),
    (SELECT id FROM "Address" WHERE localpart IS 'jason' AND domain=12));
INSERT INTO "Alias" (active, address, target) VALUES(0,
    (SELECT id FROM "Address" WHERE localpart IS 'asst-chief' AND domain=12),
    (SELECT id FROM "Address" WHERE localpart IS 'dewayne' AND domain=12));
INSERT INTO "Alias" (active, address, target) VALUES(1,
    (SELECT id FROM "Address" WHERE localpart IS 'capt' AND domain=12),
    (SELECT id FROM "Address" WHERE localpart IS 'tom' AND domain=12));
INSERT INTO "Alias" (active, address, target) VALUES(0,
    (SELECT id FROM "Address" WHERE localpart IS 'capt' AND domain=12),
    (SELECT id FROM "Address" WHERE localpart IS 'jason' AND domain=12));
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'secretary' AND domain=12),
    (SELECT id FROM "Address" WHERE localpart IS 'lisa' AND domain=12));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'vfd',
    (SELECT id FROM "Address" WHERE localpart IS 'rob0' AND domain=12));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'pm.whn',
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=13));
INSERT INTO "Alias" (target, extension, address) VALUES(-1006,'pm.whn',
    (SELECT id FROM "Address" WHERE localpart IS 'postmaster' AND domain=13));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'ab.whn',
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=13));
INSERT INTO "Alias" (target, extension, address) VALUES(-1006,'ab.whn',
    (SELECT id FROM "Address" WHERE localpart IS 'abuse' AND domain=13));
INSERT INTO "Alias" (target, extension, address) VALUES(-1002,'whn',
    (SELECT id FROM "Address" WHERE localpart IS 'rob0' AND domain=13));
INSERT INTO "Alias" (target, extension, address) VALUES(-1006,'whn',
    (SELECT id FROM "Address" WHERE localpart IS 'whn' AND domain=13));
-- whn wildcard
INSERT INTO "Alias" (target, extension, address) VALUES(-1006,'wildcard',
    (SELECT id FROM "Address" WHERE localpart IS '' AND domain=13));
COMMIT;
--
-- Third transaction: these are records which depend on the existence
-- of other records added or changed in the second transaction.
--
BEGIN TRANSACTION;
-- These domains needed transports that did not exist before the
-- second transaction
UPDATE "Domain" SET transport=(SELECT T.id FROM "Transport" AS T
    JOIN "Domain" ON T.nexthop=Domain.id
    WHERE name IS 'relay.example.org') WHERE name IS 'relay.example.org';
UPDATE "Domain" SET transport=(SELECT T.id FROM "Transport" AS T
    JOIN "Domain" ON T.nexthop=Domain.id
    WHERE name IS 'example.net') WHERE name IS 'example.net';
UPDATE "Domain" SET transport=(SELECT T.id FROM "Transport" AS T
    JOIN "Domain" ON T.nexthop=Domain.id
    WHERE name IS 'custom.example.com' AND port=2525)
    WHERE name IS 'relay.example.com';
-- These addresses needed transports that did not exist before the
-- second transaction
UPDATE "Address" SET transport=(SELECT T.id FROM "Transport" AS T
    JOIN "Domain" ON T.nexthop=Domain.id
    WHERE name IS 'hairy.example.org') WHERE localpart IS 'harry';
UPDATE "Address" SET transport=(SELECT T.id FROM "Transport" AS T
    JOIN "Domain" ON T.nexthop=Domain.id
    WHERE name IS 'custom.example.com' AND port IS NULL)
    WHERE localpart IS 'techie';
-- Alias one of our addresses to an external address: these days not
-- generally a good idea unless you know the other site postmaster. When
-- you forward spam to a big receiver, they will flag you as a spammer.
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'info' AND domain=10),
    (SELECT id FROM "Address" WHERE localpart IS 'alien' AND domain=
    (SELECT id FROM "Domain" WHERE name IS 'example.info')));
-- Alias an external address to one of ours. Not much reason to do this
-- in the real world, but it can be done! Here's how.
INSERT INTO "Alias" (extension, address, target) VALUES(NULL,
    (SELECT id FROM "Address" WHERE localpart IS 'martian'),
    (SELECT id FROM "Address" WHERE localpart IS 'us' AND domain=0));
-- Note that "us" itself is an alias, so that too will be expanded. This
-- happens in alias_maps and virtual_alias_maps alike.
COMMIT;
