diff options
Diffstat (limited to 'doc/vmod-sql.3')
-rw-r--r-- | doc/vmod-sql.3 | 270 |
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 | ||
18 | vmod-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 | ||
40 | provides functions for accessing SQL databases from | ||
41 | .B Varnish | ||
42 | configuration files. It supports | ||
43 | .B MySQL | ||
44 | and | ||
45 | .BR PostgreSQL . | ||
46 | .PP | ||
47 | Connection to a database is initiated by | ||
48 | .B sql.connect | ||
49 | function, which returns integer value, called | ||
50 | .BR "connection descriptor" " (" cd ). | ||
51 | This descriptor is passed as the first argument to the rest of | ||
52 | the module's functions to identify the connection. | ||
53 | .PP | ||
54 | The first argument to | ||
55 | .B sql.connect | ||
56 | specifies the database type to use. The valid values are \fBmysql\fR | ||
57 | (for \fBMySQL\fR) and \fBpgsql\fR (for \fBPostgreSQL\fR). | ||
58 | .PP | ||
59 | The | ||
60 | .I params | ||
61 | argument configures the connection. It is a list of | ||
62 | \fINAME\fB=\fIVALUE\fR assignments separated with semicolons. | ||
63 | The \fIVALUE\fR can be any sequence of characters, excepting white space | ||
64 | and semicolon. If any of these have to appear in it, they must either | ||
65 | be escaped by prepending them with a backslash, or the entire | ||
66 | \fIVALUE\fR must be enclosed in a pair of (single or double) quotes. | ||
67 | The 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 | ||
84 | Any other character following a backslash is output verbatim. | ||
85 | .sp | ||
86 | The valid parameters are: | ||
87 | .TP | ||
88 | \fBdebug\fR=\fIN\fR | ||
89 | Set debugging level. \fIN\fR is a decimal number. | ||
90 | .TP | ||
91 | \fBserver\fR=\fIHOST\fR | ||
92 | Name or IP address of the database server to connect to. If not | ||
93 | defined, localhost (\fB127.0.0.1\fR) is assumed. For \fBMySQL\fR | ||
94 | databases, if \fIHOST\fR begins with a slash (\fB/\fR), its value is | ||
95 | taken to be the full pathname of the local UNIX socket to connect to. | ||
96 | .TP | ||
97 | \fBport\fR=\fINUM\fR | ||
98 | Port number on the \fBserver\fR to connect to. Default is \fB3306\fR | ||
99 | for \fBMySQL\fR and \fB5432\fR for \fBPostgres\fR. | ||
100 | .TP | ||
101 | \fBdatabase=\fINAME\fR | ||
102 | The database name. | ||
103 | .TP | ||
104 | \fBconfig\fR=\fIFILE\fR | ||
105 | (\fBMySQL\fR-specific) Read credentials from the \fBMySQL\fR options | ||
106 | file \fIFILE\fR. | ||
107 | .TP | ||
108 | \fBgroup\fR=\fINAME\fR | ||
109 | (\fBMySQL\fR-specific) Read credentials from section \fINAME\fR of the | ||
110 | options file supplied with the \fBconfig\fR parameter. Default | ||
111 | section name is \fBclient\fR. | ||
112 | .TP | ||
113 | \fBcacert\fR=\fIFILE\fR | ||
114 | Use secure connection to the database server via SSL. The \fIFILE\fR | ||
115 | is 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 | ||
121 | Database user name. | ||
122 | .TP | ||
123 | \fBpassword\fR=\fISTRING\fR | ||
124 | Password to access the database. | ||
125 | .PP | ||
126 | Up to 16 connections can be opened simultaneously (see the | ||
127 | .B BUGS | ||
128 | section). | ||
129 | .PP | ||
130 | On error, the function returns -1. On success, the returned | ||
131 | descriptor will be the lowest-numbered descriptor not currently | ||
132 | open for any connection. | ||
133 | .PP | ||
134 | The function | ||
135 | .B sql.connect0 | ||
136 | is equivalent to | ||
137 | .BR sql.connect , | ||
138 | but succeeds only if the connection is assigned the descriptor | ||
139 | \fB0\fR. This function is provided as a shortcut to use when only | ||
140 | one database connection is needed. | ||
141 | .PP | ||
142 | The function | ||
143 | .B sql.disconnect | ||
144 | closes an existing database connection identified by descriptor | ||
145 | .BR cd . | ||
146 | .PP | ||
147 | The function | ||
148 | .B sql.query | ||
149 | performs a database query given as its first argument. The second | ||
150 | argument is a list of variable assignments separated with | ||
151 | semicolons, similarly to the \fIparams\fR argument described above. | ||
152 | Each assignment has the form \fINAME\fB=\fIVALUE\fR. | ||
153 | .PP | ||
154 | Before being executed, the query is expanded by replacing each | ||
155 | occurrence of \fB$\fINAME\fR construct (a \fBvariable reference\fR) | ||
156 | with the corresponding \fIVALUE\fR from the \fIparams\fR argument. | ||
157 | Similarly to the shell syntax, the variable reference can be written | ||
158 | as \fB${\fINAME\fB}\fR. This latter form can be used in contexts | ||
159 | where the variable name is immediately followed by another letter, to | ||
160 | prevent it from being counted as a part of the name. | ||
161 | .PP | ||
162 | Each expanded reference is then escaped to prevent SQL injection. | ||
163 | .PP | ||
164 | The function returns \fBTRUE\fR on success. | ||
165 | .PP | ||
166 | The result of the most recently executed query can be examined | ||
167 | using the following functions: | ||
168 | .TP | ||
169 | .BI "INT sql.ntuples(INT " cd ");" | ||
170 | Returns the number of tuples (rows) returned by the query. | ||
171 | .TP | ||
172 | .BI "INT sql.nfields(INT " cd ");" | ||
173 | Returns the number of fields in each tuple returned by the query. | ||
174 | .TP | ||
175 | .BI "INT sql.affected(INT " cd ");" | ||
176 | If the most recent query updated the database, returns the number of | ||
177 | affected rows. | ||
178 | .PP | ||
179 | The values returned by the most recent query can be retrieved | ||
180 | using the | ||
181 | .B sql.result | ||
182 | function. The desired value is identified by the row and column | ||
183 | number (both 0-based). | ||
184 | .PP | ||
185 | .SH EXAMPLES | ||
186 | The following \fBvcl\fR fragment can be used to keep a statistics | ||
187 | of visits for each URL: | ||
188 | .PP | ||
189 | .EX | ||
190 | vcl_init { | ||
191 | sql.connect0("mysql", "config=/etc/db.cnf"); | ||
192 | } | ||
193 | |||
194 | vcl_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" \{\ | ||
219 | Complete documentation for | ||
220 | .B vmod-sql | ||
221 | in various formats is | ||
222 | .URL http://puszcza.gnu.org.ua/software/vmod-sql/manual.html "available online" . | ||
223 | \} | ||
224 | .el \{\ | ||
225 | The full documentation for | ||
226 | .B vmod-sql | ||
227 | is maintained as a Texinfo | ||
228 | manual. If the | ||
229 | .B info | ||
230 | program and | ||
231 | .B vmod-sql | ||
232 | module 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 | ||
240 | should give you access to the complete manual. | ||
241 | \} | ||
242 | .SH BUGS | ||
243 | Maximum number of simultaneously opened connections is limited to 16. | ||
244 | To change this value, define \fBMAXCONN\f |