VMM:/{Alias,EmailAddress,Relocated}: use assertions for argument checks.
SETclient_encoding='UTF8';SETclient_min_messages=warning;CREATESEQUENCEtransport_id;CREATESEQUENCEmaillocation_id;CREATESEQUENCEdomain_gidSTARTWITH70000INCREMENTBY1MINVALUE70000MAXVALUE4294967294NOCYCLE;CREATESEQUENCEusers_uidSTARTWITH70000INCREMENTBY1MINVALUE70000MAXVALUE4294967294NOCYCLE;CREATETABLEtransport(tidbigintNOTNULLDEFAULTnextval('transport_id'),transportvarchar(270)NOTNULL,--smtps:[255-char.host.name:50025]CONSTRAINTpkey_transportPRIMARYKEY(tid),CONSTRAINTukey_transportUNIQUE(transport));--InsertdefaulttransportINSERTINTOtransport(transport)VALUES('dovecot:');CREATETABLEmaillocation(midbigintNOTNULLDEFAULTnextval('maillocation_id'),maillocationvarchar(20)NOTNULL,CONSTRAINTpkey_maillocationPRIMARYKEY(mid),CONSTRAINTukey_maillocationUNIQUE(maillocation));--InsertdefaultMaildir-foldernameINSERTINTOmaillocation(maillocation)VALUES('Maildir');CREATETABLEdomain_data(gidbigintNOTNULLDEFAULTnextval('domain_gid'),tidbigintNOTNULLDEFAULT1,--defualttransportdomaindirvarchar(40)NOTNULL,--/srv/mail/$RAND/4294967294CONSTRAINTpkey_domain_dataPRIMARYKEY(gid),CONSTRAINTfkey_domain_data_tid_transportFOREIGNKEY(tid)REFERENCEStransport(tid));CREATETABLEdomain_name(domainnamevarchar(255)NOTNULL,gidbigintNOTNULL,is_primarybooleanNOTNULL,CONSTRAINTpkey_domain_namePRIMARYKEY(domainname),CONSTRAINTfkey_domain_name_gid_domain_dataFOREIGNKEY(gid)REFERENCESdomain_data(gid));CREATETABLEusers(local_partvarchar(64)NOTNULL,--onlylocalpartw/o'@'passwdvarchar(74)NOTNULL,--{CRAM-MD5}+64hexnumbersnamevarchar(128)NULL,uidbigintNOTNULLDEFAULTnextval('users_uid'),gidbigintNOTNULL,midbigintNOTNULLDEFAULT1,tidbigintNOTNULLDEFAULT1,smtpbooleanNOTNULLDEFAULTTRUE,pop3booleanNOTNULLDEFAULTTRUE,imapbooleanNOTNULLDEFAULTTRUE,sievebooleanNOTNULLDEFAULTTRUE,CONSTRAINTpkey_usersPRIMARYKEY(local_part,gid),CONSTRAINTukey_users_uidUNIQUE(uid),CONSTRAINTfkey_users_gid_domain_dataFOREIGNKEY(gid)REFERENCESdomain_data(gid),CONSTRAINTfkey_users_mid_maillocationFOREIGNKEY(mid)REFERENCESmaillocation(mid),CONSTRAINTfkey_users_tid_transportFOREIGNKEY(tid)REFERENCEStransport(tid));CREATETABLEalias(gidbigintNOTNULL,addressvarchar(64)NOTNULL,--onlylocalpartw/o'@'destinationvarchar(320)NOTNULL,CONSTRAINTpkey_aliasPRIMARYKEY(gid,address,destination),CONSTRAINTfkey_alias_gid_domain_dataFOREIGNKEY(gid)REFERENCESdomain_data(gid));CREATETABLErelocated(gidbigintNOTNULL,addressvarchar(64)NOTNULL,destinationvarchar(320)NOTNULL,CONSTRAINTpkey_relocatedPRIMARYKEY(gid,address),CONSTRAINTfkey_relocated_gid_domain_dataFOREIGNKEY(gid)REFERENCESdomain_data(gid));CREATEORREPLACEVIEWdovecot_passwordASSELECTlocal_part||'@'||domain_name.domainnameAS"user",passwdAS"password",smtp,pop3,imap,sieveFROMusersLEFTJOINdomain_nameUSING(gid);CREATEORREPLACEVIEWdovecot_userASSELECTlocal_part||'@'||domain_name.domainnameASuserid,uid,gid,domain_data.domaindir||'/'||uidAShome,'~/'||maillocation.maillocationASmailFROMusersLEFTJOINdomain_dataUSING(gid)LEFTJOINdomain_nameUSING(gid)LEFTJOINmaillocationUSING(mid);CREATEORREPLACEVIEWpostfix_gidASSELECTgid,domainnameFROMdomain_name;CREATEORREPLACEVIEWpostfix_uidASSELECTlocal_part||'@'||domain_name.domainnameASaddress,uidFROMusersLEFTJOINdomain_nameUSING(gid);CREATEORREPLACEVIEWpostfix_maildirASSELECTlocal_part||'@'||domain_name.domainnameASaddress,domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'ASmaildirFROMusersLEFTJOINdomain_dataUSING(gid)LEFTJOINdomain_nameUSING(gid)LEFTJOINmaillocationUSING(mid);CREATEORREPLACEVIEWpostfix_relocatedASSELECTaddress||'@'||domain_name.domainnameASaddress,destinationFROMrelocatedLEFTJOINdomain_nameUSING(gid);CREATEORREPLACEVIEWpostfix_aliasASSELECTaddress||'@'||domain_name.domainnameASaddress,destination,gidFROMaliasLEFTJOINdomain_nameUSING(gid);CREATEORREPLACEVIEWpostfix_transportASSELECTlocal_part||'@'||domain_name.domainnameASaddress,transport.transportFROMusersLEFTJOINtransportUSING(tid)LEFTJOINdomain_nameUSING(gid);CREATEORREPLACEVIEWvmm_domain_infoASSELECTgid,domainname,transport,domaindir,count(uid)ASaccounts,(SELECTcount(DISTINCTaddress)FROMaliasWHEREalias.gid=domain_data.gid)ASaliases,(SELECTcount(gid)FROMrelocatedWHERErelocated.gid=domain_data.gid)ASrelocated,(SELECTcount(gid)FROMdomain_nameWHEREdomain_name.gid=domain_data.gidANDNOTdomain_name.is_primary)ASaliasdomainsFROMdomain_dataLEFTJOINdomain_nameUSING(gid)LEFTJOINtransportUSING(tid)LEFTJOINusersUSING(gid)WHEREdomain_name.is_primaryGROUPBYgid,domainname,transport,domaindir;CREATELANGUAGEplpgsql;CREATEORREPLACEFUNCTIONdomain_primary_trigger()RETURNSTRIGGERAS$$DECLAREprimary_countbigint;BEGINSELECTINTOprimary_countcount(gid)+NEW.is_primary::integerFROMdomain_nameWHEREdomain_name.gid=NEW.gidANDis_primary;IF(primary_count>1)THENRAISEEXCEPTION'There can only be one domain marked as primary.';ENDIF;RETURNNEW;END;$$LANGUAGEplpgsqlSTABLE;CREATETRIGGERprimary_count_insBEFOREINSERTONdomain_nameFOREACHROWEXECUTEPROCEDUREdomain_primary_trigger();CREATETRIGGERprimary_count_updAFTERUPDATEONdomain_nameFOREACHROWEXECUTEPROCEDUREdomain_primary_trigger();