maddy

  1# SQL query mapping
  2
  3The table.sql_query module implements table interface using SQL queries.
  4
  5Definition:
  6
  7```
  8table.sql_query {
  9	driver <driver name>
 10	dsn <data source name>
 11	lookup <lookup query>
 12
 13	# Optional:
 14	init <init query list>
 15	list <list query>
 16	add <add query>
 17	del <del query>
 18	set <set query>
 19}
 20```
 21
 22Usage example:
 23
 24```
 25# Resolve SMTP address aliases using PostgreSQL DB.
 26modify {
 27	replace_rcpt sql_query {
 28		driver postgres
 29		dsn "dbname=maddy user=maddy"
 30		lookup "SELECT alias FROM aliases WHERE address = $1"
 31	}
 32}
 33```
 34
 35## Configuration directives
 36
 37### driver _driver name_ 
 38**Required.**
 39
 40Driver to use to access the database.
 41
 42Supported drivers: `postgres`, `sqlite3` (if compiled with C support)
 43
 44---
 45
 46### dsn _data source name_
 47**Required.**
 48
 49Data Source Name to pass to the driver. For SQLite3 this is just a path to DB
 50file. For Postgres, see
 51[https://pkg.go.dev/github.com/lib/pq?tab=doc#hdr-Connection\_String\_Parameters](https://pkg.go.dev/github.com/lib/pq?tab=doc#hdr-Connection\_String\_Parameters)
 52
 53---
 54
 55### lookup _query_
 56**Required.**
 57
 58SQL query to use to obtain the lookup result.
 59
 60It will get one named argument containing the lookup key. Use :key
 61placeholder to access it in SQL. The result row set should contain one row, one
 62column with the string that will be used as a lookup result. If there are more
 63rows, they will be ignored. If there are more columns, lookup will fail.  If
 64there are no rows, lookup returns "no results". If there are any error - lookup
 65will fail.
 66
 67---
 68
 69### init _queries..._
 70Default: empty
 71
 72List of queries to execute on initialization. Can be used to configure RDBMS.
 73
 74Example, to improve SQLite3 performance:
 75
 76```
 77table.sql_query {
 78	driver sqlite3
 79	dsn whatever.db
 80	init "PRAGMA journal_mode=WAL" \
 81		"PRAGMA synchronous=NORMAL"
 82	lookup "SELECT alias FROM aliases WHERE address = $1"
 83}
 84```
 85
 86---
 87
 88### named_args _boolean_
 89Default: `yes`
 90
 91Whether to use named parameters binding when executing SQL queries
 92or not.
 93
 94Note that maddy's PostgreSQL driver does not support named parameters and
 95SQLite3 driver has issues handling numbered parameters:
 96[https://github.com/mattn/go-sqlite3/issues/472](https://github.com/mattn/go-sqlite3/issues/472)
 97
 98---
 99
100### add _query_<br>list _query_<br>set _query_ <br>del _query_
101Default: none
102
103If queries are set to implement corresponding table operations - table becomes
104"mutable" and can be used in contexts that require writable key-value store.
105
106'add' query gets :key, :value named arguments - key and value strings to store.
107They should be added to the store. The query **should** not add multiple values
108for the same key and **should** fail if the key already exists.
109
110'list' query gets no arguments and should return a column with all keys in
111the store.
112
113'set' query gets :key, :value named arguments - key and value and should replace the existing
114entry in the database.
115
116'del' query gets :key argument - key and should remove it from the database.
117
118If `named_args` is set to `no` - key is passed as the first numbered parameter
119($1), value is passed as the second numbered parameter ($2).
120