aboutsummaryrefslogtreecommitdiff
path: root/doc/vmod-sql.3
diff options
context:
space:
mode:
Diffstat (limited to 'doc/vmod-sql.3')
-rw-r--r--doc/vmod-sql.3270
1 files changed, 270 insertions, 0 deletions
diff --git a/doc/vmod-sql.3 b/doc/vmod-sql.3
new file mode 100644
index 0000000..28dc2d4
--- /dev/null
+++ b/doc/vmod-sql.3
@@ -0,0 +1,270 @@
1.\" This file is part of Vmod-sql -*- nroff -*-
2.\" Copyright (C) 2013 Sergey Poznyakoff
3.\"
4.\" Vmod-sql is free software; you can redistribute it and/or modify
5.\" it under the terms of the GNU General Public License as published by
6.\" the Free Software Foundation; either version 3, or (at your option)
7.\" any later version.
8.\"
9.\" Vmod-sql is distributed in the hope that it will be useful,
10.\" but WITHOUT ANY WARRANTY; without even the implied warranty of
11.\" MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12.\" GNU General Public License for more details.
13.\"
14.\" You should have received a copy of the GNU General Public License
15.\" along with vmod-sql. If not, see <http://www.gnu.org/licenses/>.
16.TH VMOD-SQL 1 "October 19, 2013" "VMOD-SQL" "User Reference"
17.SH NAME
18vmod-sql \- SQL access for Varnish Cache
19.SH SYNOPSIS
20.B import sql;
21.PP
22.BI "INT sql.connect(STRING " dbtype ", STRING " params ");"
23.PP
24.BI "VOID sql.connect_init(STRING " dbtype ", STRING " params ");"
25.PP
26.BI "BOOL sql.query(INT " cd ", STRING " query ", STRING " params ");"
27.PP
28.BI "STRING sql.result(INT " cd ", INT " row ", INT " col ");"
29.PP
30.BI "INT sql.affected(INT " cd ");"
31.PP
32.BI "VOID sql.disconnect(INT " cd ");"
33.PP
34.BI "INT sql.ntuples(INT " cd ");"
35.PP
36.BI "INT sql.nfields(INT " cd ");"
37.PP
38.SH DESCRIPTION
39.B Vmod-sql
40provides functions for accessing SQL databases from
41.B Varnish
42configuration files. It supports
43.B MySQL
44and
45.BR PostgreSQL .
46.PP
47Connection to a database is initiated by
48.B sql.connect
49function, which returns integer value, called
50.BR "connection descriptor" " (" cd ).
51This descriptor is passed as the first argument to the rest of
52the module's functions to identify the connection.
53.PP
54The first argument to
55.B sql.connect
56specifies the database type to use. The valid values are \fBmysql\fR
57(for \fBMySQL\fR) and \fBpgsql\fR (for \fBPostgreSQL\fR).
58.PP
59The
60.I params
61argument configures the connection. It is a list of
62\fINAME\fB=\fIVALUE\fR assignments separated with semicolons.
63The \fIVALUE\fR can be any sequence of characters, excepting white space
64and semicolon. If any of these have to appear in it, they must either
65be escaped by prepending them with a backslash, or the entire
66\fIVALUE\fR must be enclosed in a pair of (single or double) quotes.
67The following \fBescape sequences\fR are allowed for use in \fIVALUE\fR:
68.PP
69.nf
70.ta 8n 18n 42n
71.ul
72 Sequence Expansion ASCII
73 \fB\\\\\fR \fB\\\fR 134
74 \fB\\"\fR \fB"\fR 042
75 \fB\\a\fR audible bell 007
76 \fB\\b\fR backspace 010
77 \fB\\f\fR form-feed 014
78 \fB\\n\fR new line 012
79 \fB\\r\fR charriage return 015
80 \fB\\t\fR horizontal tabulation 011
81 \fB\\v\fR vertical tabulation 013
82.fi
83.sp
84Any other character following a backslash is output verbatim.
85.sp
86The valid parameters are:
87.TP
88\fBdebug\fR=\fIN\fR
89Set debugging level. \fIN\fR is a decimal number.
90.TP
91\fBserver\fR=\fIHOST\fR
92Name or IP address of the database server to connect to. If not
93defined, localhost (\fB127.0.0.1\fR) is assumed. For \fBMySQL\fR
94databases, if \fIHOST\fR begins with a slash (\fB/\fR), its value is
95taken to be the full pathname of the local UNIX socket to connect to.
96.TP
97\fBport\fR=\fINUM\fR
98Port number on the \fBserver\fR to connect to. Default is \fB3306\fR
99for \fBMySQL\fR and \fB5432\fR for \fBPostgres\fR.
100.TP
101\fBdatabase=\fINAME\fR
102The database name.
103.TP
104\fBconfig\fR=\fIFILE\fR
105(\fBMySQL\fR-specific) Read credentials from the \fBMySQL\fR options
106file \fIFILE\fR.
107.TP
108\fBgroup\fR=\fINAME\fR
109(\fBMySQL\fR-specific) Read credentials from section \fINAME\fR of the
110options file supplied with the \fBconfig\fR parameter. Default
111section name is \fBclient\fR.
112.TP
113\fBcacert\fR=\fIFILE\fR
114Use secure connection to the database server via SSL. The \fIFILE\fR
115is a full pathname to the certificate authority file.
116.TP
117\fBoptions\fR=\fISTRING\fR
118(\fBPostgres\fR-specific) Connection options.
119.TP
120\fBuser\fR=\fINAME\fR
121Database user name.
122.TP
123\fBpassword\fR=\fISTRING\fR
124Password to access the database.
125.PP
126Up to 16 connections can be opened simultaneously (see the
127.B BUGS
128section).
129.PP
130On error, the function returns -1. On success, the returned
131descriptor will be the lowest-numbered descriptor not currently
132open for any connection.
133.PP
134The function
135.B sql.connect0
136is equivalent to
137.BR sql.connect ,
138but succeeds only if the connection is assigned the descriptor
139\fB0\fR. This function is provided as a shortcut to use when only
140one database connection is needed.
141.PP
142The function
143.B sql.disconnect
144closes an existing database connection identified by descriptor
145.BR cd .
146.PP
147The function
148.B sql.query
149performs a database query given as its first argument. The second
150argument is a list of variable assignments separated with
151semicolons, similarly to the \fIparams\fR argument described above.
152Each assignment has the form \fINAME\fB=\fIVALUE\fR.
153.PP
154Before being executed, the query is expanded by replacing each
155occurrence of \fB$\fINAME\fR construct (a \fBvariable reference\fR)
156with the corresponding \fIVALUE\fR from the \fIparams\fR argument.
157Similarly to the shell syntax, the variable reference can be written
158as \fB${\fINAME\fB}\fR. This latter form can be used in contexts
159where the variable name is immediately followed by another letter, to
160prevent it from being counted as a part of the name.
161.PP
162Each expanded reference is then escaped to prevent SQL injection.
163.PP
164The function returns \fBTRUE\fR on success.
165.PP
166The result of the most recently executed query can be examined
167using the following functions:
168.TP
169.BI "INT sql.ntuples(INT " cd ");"
170Returns the number of tuples (rows) returned by the query.
171.TP
172.BI "INT sql.nfields(INT " cd ");"
173Returns the number of fields in each tuple returned by the query.
174.TP
175.BI "INT sql.affected(INT " cd ");"
176If the most recent query updated the database, returns the number of
177affected rows.
178.PP
179The values returned by the most recent query can be retrieved
180using the
181.B sql.result
182function. The desired value is identified by the row and column
183number (both 0-based).
184.PP
185.SH EXAMPLES
186The following \fBvcl\fR fragment can be used to keep a statistics
187of visits for each URL:
188.PP
189.EX
190vcl_init {
191 sql.connect0("mysql", "config=/etc/db.cnf");
192}
193
194vcl_recv {
195 if (sql.query(0,
196 {"UPDATE counter SET count=count+1
197 WHERE url='$url'"},
198 "url=" + req.url)) {
199 sql.query(0,
200 "INSERT INTO counter values ('$url', 1)",
201 "url=" + req.url)
202 }
203}
204.EE
205.\" The MANCGI variable is set by man.cgi script on Ulysses.
206.\" The download.inc file contains the default DOWNLOAD section
207.\" for man-based doc pages.
208.if "\V[MANCGI]"WEBDOC" \{\
209. ds package vmod-sql
210. ds version 1.0
211. so download.inc
212\}
213.SH "SEE ALSO"
214.BR vmod-dbrw (3),
215.BR vcl (7),
216.BR varnishd (1).
217.PP
218.ie "\V[MANCGI]"WEBDOC" \{\
219Complete documentation for
220.B vmod-sql
221in various formats is
222.URL http://puszcza.gnu.org.ua/software/vmod-sql/manual.html "available online" .
223\}
224.el \{\
225The full documentation for
226.B vmod-sql
227is maintained as a Texinfo
228manual. If the
229.B info
230program and
231.B vmod-sql
232module are properly installed at your site, the command
233.PP
234.nf
235.in +4
236.B info vmod-sql
237.in
238.fi
239.PP
240should give you access to the complete manual.
241\}
242.SH BUGS
243Maximum number of simultaneously opened connections is limited to 16.
244To change this value, define \fBMAXCONN\f