This link was made available on the web site on 2012-01-10. To early reviewers: I thank you for your interest, but please understand that this was still a work in progress up until 2012-01-16. This file exists to try to document any material changes in the content since the first public availability. 2012-01-31 The new schema sticks to full-word column names, so I changed "Alias.exten" to "Alias.extension". 2012-01-30 Updated the tarball. Updated new-schema.sql to reflect what had been done in the rewrite quite some time back: changed all "key" columns to "id"; did away with the tri-state "Alias.active" idea. Now the distinction between virtual aliases and alias_maps is Address.domain=0. This has a "cost" of disabling unqualified localparts in as lookup keys in virtual_alias_maps, but there is no loss in functionality, because alias_maps has it covered. This revision also replaces the NOT NULL constraint from RClass.name; the special record RClass.id=-1 with RClass.name=NULL served no useful purpose. Some days back I hardlinked README to the original filename of "postfix-dovecot-sqlite.howto". This was because of Google having cached that file. I think their cache is now up-to-date, though. 2012-01-16 Discovered a bug in maps-alias.query. The file was correct in the narrative. Also cleaned up the presentation in two other query files. Not updating the tarball for this. At this point I have decided to fork my own project for the new schema. :) I will update this file and the ones in this directory as bugs are found, but the new schema will move to a new/ subdirectory. It will be a complete rewrite, and it will 503 until it gets to the point this one has reached ... shouldn't take long. The tarball dated 20120115 was more or less the final release; 20120116 was about whims and TODO, and at this point I am generating and uploading 20120116a as final. When "new" is mature I'll probably make this one "howto/old/" and move "howto/new/" to "howto/". Added a versions.old directory to keep the old tarballs out of the way. I don't think anyone should have any interest in them, but it seems more responsible and polite to keep a log of changes; if a reader saw me say "white" where I once said "black", said reader has the wherewithal to go back and verify his/her sanity. Also added a README. More changes to new-schema.sql, but I think it is stable now. The aforementioned schema overhaul is now out there as new-schema.sql. The shocking thing, for me: the Alias and VAlias tables are now merged into one, since their definitions differed only in the names. 2012-01-15 Added to the new TODO file: want to do away with the ugly camelCase column names. They were a crutch that helped me, but now I think "Table.plain-name" makes more sense. Announced completion on IRC. Mailing list announcement will probably come tomorrow. There might still be some mistakes and oversights, but at this point basic functionality has been tested and verified. Removing the "BETA" warnings from files and the link on the website. Testing showed that Postfix needs r-x access to the the database's directory for its "postfix" UID or GID, but Dovecot is fine with root:root mode 700 on /etc/dovecot/private. This means that the hardlink and dual private directories are not necessary. I'm leaving them as they are, but noting this in the 01-sqlite-schema.howto narrative. Minor inconsistency in dovecot/10-ssl.conf: different filenames than were used in postfix/main.cf. Fixed. Set up and tested prefetch userdb for SQL (virtual) users. Changed in dovecot/sql.conf.ext, dovecot/conf.d/auth-sql.conf.ext, and 03-dovecot-sqlite.howto. Additional files in postfix/ are provided: helo_checks, postscreen_access.cidr, and postscreen_dnsbl_reply_maps.pcre. These are needed for the sample main.cf to work, and they include comments. Since they are not relevant to the main point of this document, the narrative (02-postfix-sqlite.howto) only mentions them without inclusion. The point of the document is to use SQLite as much as possible; it is not possible to do that for postscreen nor for regexp/pcre lookups. Fixed dovecot/sql-deny.conf.ext "case" expression and found and fixed a bug in postfix/query/maps-local.query. 2012-01-14 https://github.com/SpiceMan/Grelfiqs (a pgsql project based on this one) added to bibliography. Testing begins! Found a bug in maps-valias.query, also added files which are referred to in main.cf. A few other minor issues found. Added another file, schema.sql, with just the schema. Added a symlink, "latest.tar.gz" to the most recent revision of the tarball. This means I'll be leaving the older tarballs on the site, so you can look back and giggle at my stupid mistakes. Those date back to 01-12. In mail.sql, changed aliasTarget values to unqualified names, and added a characteristically verbose comment on what this means. In testing I see that I missed one change in the schema, and the previous copy of mail.sql had a mistake, which was carried over into 01-sqlite-schema.howto as well. 2012-01-13 This file is now inverted! Most recent changes first. This should make it easier to follow. I hope. Big changes in the schema! Consistent table and column naming: now all table names are singular. This means the table names might need to be quoted in places. Addresses is Address, Aliases is Alias, Domains is Domain, VAliases is VAlias, VMailboxes is VMailbox. BScat, RClass, and Transport are the same. Thanks again (or blame?) to SpiceMan for guidance. Every table's columns now have consistent prefixes. The new Address prefix is "add" (changed addrNum to addNum), Alias prefix is "alias", RClass is "rc", Transport is "tp", VAlias is "va", VMailbox is "vm". BScat and Domain columns are unchanged. However, "active" columns everywhere are unchanged. No, it's probably not the best choice, but changing that would be painful. The other schema changes should be mostly amenable to global search and replace tactics. Alias, BScat, and VAlias tables have new integer primary key columns. Those columns (aliasKey, bsKey, and vaKey respectively) are not used anywhere, but it seems better to have a unique integer identifier for every row in every table. Other tables already had them. The main narrative file, postfix-dovecot-sqlite.howto, is now broken into parts: 00-overview.howto contains the preface and overview, part A. 01-sqlite-schema.howto is part B, Schema, and the introduction to Part C, Sample Data. Part D, Postfix Configuration, is in 02-postfix-sqlite.howto. The Dovecot part E is now 03-dovecot-sqlite.howto. The appendices and bibliography are all in 04-wrapup.howto. The numbering indicates the order in which they are to be taken. 2012-01-12 Caught a few minor bugs in the Dovecot files, notably the SQLite queries in dovecot/sql.conf.ext . Oops. A significant change coming up: it was suggested to add primary keys to each table, even where we won't directly use them. The biggest change will be to the VAliases table. This is not done yet. Dovecot narrative written, which mostly comprises the various modular configuration files. This was left as a separate file, rather than merging in with the schema+Postfix narrative. Eventually I'll break it all up into one file per major section. 2012-01-11 This is my first excursion into Dovecot since beginning the project. I came across a rather important feature which had slipped my mind: the "deny=yes" passdb. To implement it, I decided to change my "active=1" queries to "active!=0". Addresses.active is no longer a boolean; it is now a tri-state setting: Addresses.active Meaning ================ ======= 1 Account and address enabled 0 Account and address disabled -1 Account disabled, address enabled "Account" means the Dovecot credentials, the user's ability to login and retrieve (or AUTH to send) mail. "Address" means the ability to receive mail from any source. Only the Addresses table is affected by this change; all other "active" columns continue to be boolean 0|1. Why Addresses and not the VMailboxes table? Because we also have system users who login without the "@domain" in their username. Sample data was added with -1: each of a system and a virtual mailbox user. ALSO: Thanks, SpiceMan, for finding a syntax error and an omission. He put it up at Github, but I don't know if he is planning to keep it there. (Later: it seems to be gone.) FOR CONVENIENCE in downloading: I'm now including a tarball of all the files.