Added a lot more context to the sqlite queries and options

1 files changed, 112 insertions, 30 deletions

diff --git a/extras/tables/table-sqlite/table-sqlite.5 b/extras/tables/table-sqlite/table-sqlite.5
index a2d8c21..9d1da30 100644
--- a/extras/tables/table-sqlite/table-sqlite.5
+++ b/extras/tables/table-sqlite/table-sqlite.5
@@ -28,8 +28,10 @@ mail daemon.
 The format described here applies to tables as defined in
 .Xr smtpd.conf 5 .
 .Sh SQLITE TABLE
-A SQLite table allows the storing of usernames, passwords, aliases, and domains
-in a format that is shareable across various machines that support SQLite3
+A sqlite table allows the storing of usernames, passwords, aliases, and domains
+in a format that is shareable across various machines that support
+.Xr sqlite3 1
+(SQLite version 3).
 .Pp
 The table is used by
 .Xr smtpd 8
@@ -37,43 +39,123 @@ when authenticating a user, when user information such as user-id and/or
 home directory is required for a delivery, when a domain lookup may be required,
 and/or when looking for an alias.
 .Pp
-A SQLite table consists of one or more SQLite3 Databases with one or more tables.
+A sqlite table consists of one or more
+.Xr sqlite3 1
+databases with one or more tables.
 .Pp
 If the table is used for authentication, the password should be
 encrypted using the
 .Xr crypt 3
-function.
-Such passwords can be generated using the
+function. Such passwords can be generated using the
 .Xr encrypt 1
 utility or
 .Xr smtpctl 8
 encrypt command.
+
 .Sh SQLITE TABLE CONFIG FILE
-.Cd dbpath
-.Dl This is the path to where the DB is located with the dbname.
-.Cd query_alias
-.Dl This is used to provide a query to look up aliases. All the question marks are replaced
-.Dl with the appropriate data, in this case it would be the left hand side of the SMTP address.
-.Dl This expects one varchar to be returned with the user name the alias resolves to.
-.Cd query_domain
-.Dl This is used to provide a query for a domain query call. All the question marks are replaced
-.Dl with the appropriate data, in this case it would be the right hand side of the SMTP address.
-.Dl This expects one varchar to be returned with a matching domain name.
-.Cd query_userinfo
-.Dl This is used to provide a query for looking up user information.
-.Dl All the question marks are replaced with the appropriate data, in this case it
-.Dl would be the left hand side of the SMTP address.
-.Dl This expects three fields to be returned an int containing a UID, an int containing a GID
-.Dl and a varchar containing the home directory for the user.
-.Cd query_credentials
-.Dl This is used to provide a query for looking up credentials. All the question marks are replaced
-.Dl with the appropriate data, in this case it would be the left hand side of the SMTP address.
-.Dl the query expects that there are two varchars returned one with a
-.Dl user name one with a password in
-.Xr encrypt 1
-.Dl format.
+
+The following configuration options are available:
+.Pp
+.Bl -tag -width Ds
+.It Xo
+.Ic dbpath
+.Ar file
+.Xc
+This is the path to where the DB is located with the dbname.
+For example:
+.Bd -literal -offset indent
+dbpath /etc/mail/smtp.sqlite
+.Ed
+.Pp
+
+.It Xo
+.Ic query_alias
+.Ar SQL statement
+.Xc
+This is used to provide a query to look up aliases. The question mark
+is replaced with the appropriate data. For alias it is the left hand side of
+the SMTP address. This expects one VARCHAR to be returned with the user name
+the alias resolves to.
+.Pp
+
+.It Xo
+.Ic query_credentials
+.Ar SQL statement
+.Xc
+This is used to provide a query for looking up user credentials. The question
+mark is replaced with the appropriate data. For credentials it is the left
+hand side of the SMTP address. The query expects that there are two VARCHARS
+returned, one with a user name and one with a password in
+.Xr crypt 3
+format.
+.Pp
+
+.It Xo
+.Ic query_domain
+.Ar SQL statement
+.Xc
+This is used to provide a query for looking up a domain. The question mark
+is replaced with the appropriate data. For the domain it would be the
+right hand side of the SMTP address. This expects one VARCHAR to be returned
+with a matching domain name.
+.El
+
+A generic SQL statement would be something like:
+.Bd -literal -offset indent
+query_ SELECT value FROM table WHERE key=?;
+.Ed
+
+.Sh EXAMPLES
+Those provided here a known to work.
+
+.Ic Pa sqlite3 schema
+.Bd -literal -compact
+CREATE TABLE aliases (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    email VARCHAR(255) NOT NULL,
+    destination VARCHAR(255) NOT NULL
+);
+CREATE TABLE credentials (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    email VARCHAR(255) NOT NULL,
+    password VARCHAR(255) NOT NULL
+);
+CREATE TABLE domains (
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    domain VARCHAR(255) NOT NULL
+);
+.Ed
+
+.Ic Pa /etc/mail/sqlite.conf
+.Bd -literal -compact
+dbpath /etc/mail/smtp.sqlite
+query_alias SELECT destination FROM aliases WHERE email=?;
+query_credentials SELECT email, password FROM credentials WHERE email=?;
+query_domain SELECT domain FROM domains WHERE domain=?;
+.Ed
+
+.Ic Pa /etc/mail/smtpd.conf
+.Bd -literal -compact
+table domains sqlite:/etc/mail/sqlite.conf
+table virtuals sqlite:/etc/mail/sqlite.conf
+table credentials sqlite:/etc/mail/sqlite.conf
+.Ed
+
+.Sh FILES
+.Bl -tag -width "/etc/mail/sqlite.conf" -compact
+.It Pa /etc/mail/sqlite.conf
+Suggested
+.Xr table-sqlite 8
+configuration file.
+.It Pa /etc/mail/smtp.sqlite
+Suggested
+.Xr sqlite3 1
+database file.
+.El
+
 .Sh SEE ALSO
-.Xr encrypt 1 ,
 .Xr smtpd.conf 5 ,
 .Xr smtpctl 8 ,
-.Xr smtpd 8
+.Xr smtpd 8 ,
+.Xr encrypt 1 ,
+.Xr crypt 3