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 @@
+.\" This file is part of Vmod-sql -*- nroff -*-
+.\" Copyright (C) 2013 Sergey Poznyakoff
+.\"
+.\" Vmod-sql is free software; you can redistribute it and/or modify
+.\" it under the terms of the GNU General Public License as published by
+.\" the Free Software Foundation; either version 3, or (at your option)
+.\" any later version.
+.\"
+.\" Vmod-sql is distributed in the hope that it will be useful,
+.\" but WITHOUT ANY WARRANTY; without even the implied warranty of
+.\" MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+.\" GNU General Public License for more details.
+.\"
+.\" You should have received a copy of the GNU General Public License
+.\" along with vmod-sql. If not, see <http://www.gnu.org/licenses/>.
+.TH VMOD-SQL 1 "October 19, 2013" "VMOD-SQL" "User Reference"
+.SH NAME
+vmod-sql \- SQL access for Varnish Cache
+.SH SYNOPSIS
+.B import sql;
+.PP
+.BI "INT sql.connect(STRING " dbtype ", STRING " params ");"
+.PP
+.BI "VOID sql.connect_init(STRING " dbtype ", STRING " params ");"
+.PP
+.BI "BOOL sql.query(INT " cd ", STRING " query ", STRING " params ");"
+.PP
+.BI "STRING sql.result(INT " cd ", INT " row ", INT " col ");"
+.PP
+.BI "INT sql.affected(INT " cd ");"
+.PP
+.BI "VOID sql.disconnect(INT " cd ");"
+.PP
+.BI "INT sql.ntuples(INT " cd ");"
+.PP
+.BI "INT sql.nfields(INT " cd ");"
+.PP
+.SH DESCRIPTION
+.B Vmod-sql
+provides functions for accessing SQL databases from
+.B Varnish
+configuration files. It supports
+.B MySQL
+and
+.BR PostgreSQL .
+.PP
+Connection to a database is initiated by
+.B sql.connect
+function, which returns integer value, called
+.BR "connection descriptor" " (" cd ).
+This descriptor is passed as the first argument to the rest of
+the module's functions to identify the connection.
+.PP
+The first argument to
+.B sql.connect
+specifies the database type to use. The valid values are \fBmysql\fR
+(for \fBMySQL\fR) and \fBpgsql\fR (for \fBPostgreSQL\fR).
+.PP
+The
+.I params
+argument configures the connection. It is a list of
+\fINAME\fB=\fIVALUE\fR assignments separated with semicolons.
+The \fIVALUE\fR can be any sequence of characters, excepting white space
+and semicolon. If any of these have to appear in it, they must either
+be escaped by prepending them with a backslash, or the entire
+\fIVALUE\fR must be enclosed in a pair of (single or double) quotes.
+The following \fBescape sequences\fR are allowed for use in \fIVALUE\fR:
+.PP
+.nf
+.ta 8n 18n 42n
+.ul
+ Sequence Expansion ASCII
+ \fB\\\\\fR \fB\\\fR 134
+ \fB\\"\fR \fB"\fR 042
+ \fB\\a\fR audible bell 007
+ \fB\\b\fR backspace 010
+ \fB\\f\fR form-feed 014
+ \fB\\n\fR new line 012
+ \fB\\r\fR charriage return 015
+ \fB\\t\fR horizontal tabulation 011
+ \fB\\v\fR vertical tabulation 013
+.fi
+.sp
+Any other character following a backslash is output verbatim.
+.sp
+The valid parameters are:
+.TP
+\fBdebug\fR=\fIN\fR
+Set debugging level. \fIN\fR is a decimal number.
+.TP
+\fBserver\fR=\fIHOST\fR
+Name or IP address of the database server to connect to. If not
+defined, localhost (\fB127.0.0.1\fR) is assumed. For \fBMySQL\fR
+databases, if \fIHOST\fR begins with a slash (\fB/\fR), its value is
+taken to be the full pathname of the local UNIX socket to connect to.
+.TP
+\fBport\fR=\fINUM\fR
+Port number on the \fBserver\fR to connect to. Default is \fB3306\fR
+for \fBMySQL\fR and \fB5432\fR for \fBPostgres\fR.
+.TP
+\fBdatabase=\fINAME\fR
+The database name.
+.TP
+\fBconfig\fR=\fIFILE\fR
+(\fBMySQL\fR-specific) Read credentials from the \fBMySQL\fR options
+file \fIFILE\fR.
+.TP
+\fBgroup\fR=\fINAME\fR
+(\fBMySQL\fR-specific) Read credentials from section \fINAME\fR of the
+options file supplied with the \fBconfig\fR parameter. Default
+section name is \fBclient\fR.
+.TP
+\fBcacert\fR=\fIFILE\fR
+Use secure connection to the database server via SSL. The \fIFILE\fR
+is a full pathname to the certificate authority file.
+.TP
+\fBoptions\fR=\fISTRING\fR
+(\fBPostgres\fR-specific) Connection options.
+.TP
+\fBuser\fR=\fINAME\fR
+Database user name.
+.TP
+\fBpassword\fR=\fISTRING\fR
+Password to access the database.
+.PP
+Up to 16 connections can be opened simultaneously (see the
+.B BUGS
+section).
+.PP
+On error, the function returns -1. On success, the returned
+descriptor will be the lowest-numbered descriptor not currently
+open for any connection.
+.PP
+The function
+.B sql.connect0
+is equivalent to
+.BR sql.connect ,
+but succeeds only if the connection is assigned the descriptor
+\fB0\fR. This function is provided as a shortcut to use when only
+one database connection is needed.
+.PP
+The function
+.B sql.disconnect
+closes an existing database connection identified by descriptor
+.BR cd .
+.PP
+The function
+.B sql.query
+performs a database query given as its first argument. The second
+argument is a list of variable assignments separated with
+semicolons, similarly to the \fIparams\fR argument described above.
+Each assignment has the form \fINAME\fB=\fIVALUE\fR.
+.PP
+Before being executed, the query is expanded by replacing each
+occurrence of \fB$\fINAME\fR construct (a \fBvariable reference\fR)
+with the corresponding \fIVALUE\fR from the \fIparams\fR argument.
+Similarly to the shell syntax, the variable reference can be written
+as \fB${\fINAME\fB}\fR. This latter form can be used in contexts
+where the variable name is immediately followed by another letter, to
+prevent it from being counted as a part of the name.
+.PP
+Each expanded reference is then escaped to prevent SQL injection.
+.PP
+The function returns \fBTRUE\fR on success.
+.PP
+The result of the most recently executed query can be examined
+using the following functions:
+.TP
+.BI "INT sql.ntuples(INT " cd ");"
+Returns the number of tuples (rows) returned by the query.
+.TP
+.BI "INT sql.nfields(INT " cd ");"
+Returns the number of fields in each tuple returned by the query.
+.TP
+.BI "INT sql.affected(INT " cd ");"
+If the most recent query updated the database, returns the number of
+affected rows.
+.PP
+The values returned by the most recent query can be retrieved
+using the
+.B sql.result
+function. The desired value is identified by the row and column
+number (both 0-based).
+.PP
+.SH EXAMPLES
+The following \fBvcl\fR fragment can be used to keep a statistics
+of visits for each URL:
+.PP
+.EX
+vcl_init {
+ sql.connect0("mysql", "config=/etc/db.cnf");
+}
+
+vcl_recv {
+ if (sql.query(0,
+ {"UPDATE counter SET count=count+1
+ WHERE url='$url'"},
+ "url=" + req.url)) {
+ sql.query(0,
+ "INSERT INTO counter values ('$url', 1)",
+ "url=" + req.url)
+ }
+}
+.EE
+.\" The MANCGI variable is set by man.cgi script on Ulysses.
+.\" The download.inc file contains the default DOWNLOAD section
+.\" for man-based doc pages.
+.if "\V[MANCGI]"WEBDOC" \{\
+. ds package vmod-sql
+. ds version 1.0
+. so download.inc
+\}
+.SH "SEE ALSO"
+.BR vmod-dbrw (3),
+.BR vcl (7),
+.BR varnishd (1).
+.PP
+.ie "\V[MANCGI]"WEBDOC" \{\
+Complete documentation for
+.B vmod-sql
+in various formats is
+.URL http://puszcza.gnu.org.ua/software/vmod-sql/manual.html "available online" .
+\}
+.el \{\
+The full documentation for
+.B vmod-sql
+is maintained as a Texinfo
+manual. If the
+.B info
+program and
+.B vmod-sql
+module are properly installed at your site, the command
+.PP
+.nf
+.in +4
+.B info vmod-sql
+.in
+.fi
+.PP
+should give you access to the complete manual.
+\}
+.SH BUGS
+Maximum number of simultaneously opened connections is limited to 16.
+To change this value, define \fBMAXCONN\fR when configuring the
+package, e.g.:
+.PP
+.EX
+./configure CPPFLAGS=-DMAXCONN=32
+.EE
+.PP
+.SH AUTHORS
+Sergey Poznyakoff
+.SH "BUG REPORTS"
+Report bugs to <gray@gnu.org>.
+.SH COPYRIGHT
+Copyright \(co 2013 Sergey Poznyakoff
+.br
+.na
+License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
+.br
+.ad
+This is free software: you are free to change and redistribute it.
+There is NO WARRANTY, to the extent permitted by law.
+.\" Local variables:
+.\" eval: (add-hook 'write-file-hooks 'time-stamp)
+.\" time-stamp-start: ".TH [A-Z_][A-Z0-9_.-]* [0-9] \""
+.\" time-stamp-format: "%:B %:d, %:y"
+.\" time-stamp-end: "\""
+.\" time-stamp-line-limit: 20
+.\" end:

Return to:

Send suggestions and report system problems to the System administrator.