VMOD-SQL

NAME
SYNOPSIS
DESCRIPTION
EXAMPLES
DOWNLOADS
SEE ALSO
BUGS
AUTHORS
BUG REPORTS
COPYRIGHT

NAME

vmod-sql - SQL access for Varnish Cache

SYNOPSIS

import sql;

INT sql.connect(STRING dbtype, STRING params);

VOID sql.connect_init(STRING dbtype, STRING params);

BOOL sql.query(INT cd, STRING query, STRING params);

STRING sql.result(INT cd, INT row, INT col);

INT sql.affected(INT cd);

VOID sql.disconnect(INT cd);

INT sql.ntuples(INT cd);

INT sql.nfields(INT cd);

DESCRIPTION

Vmod-sql provides functions for accessing SQL databases from Varnish configuration files. It supports MySQL and PostgreSQL.

Connection to a database is initiated by sql.connect function, which returns integer value, called connection descriptor (cd). This descriptor is passed as the first argument to the rest of the module’s functions to identify the connection.

The first argument to sql.connect specifies the database type to use. The valid values are mysql (for MySQL) and pgsql (for PostgreSQL).

The params argument configures the connection. It is a list of NAME=VALUE assignments separated with semicolons. The VALUE 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 VALUE must be enclosed in a pair of (single or double) quotes. The following escape sequences are allowed for use in VALUE:

Sequence

Expansion

ASCII

\\

\

134

\"

"

042

\a

audible bell

007

\b

backspace

010

\f

form-feed

014

\n

new line

012

\r

charriage return

015

\t

horizontal tabulation

011

\v

vertical tabulation

013

Any other character following a backslash is output verbatim.

The valid parameters are:
debug
=N

Set debugging level. N is a decimal number.

server=HOST

Name or IP address of the database server to connect to. If not defined, localhost (127.0.0.1) is assumed. For MySQL databases, if HOST begins with a slash (/), its value is taken to be the full pathname of the local UNIX socket to connect to.

port=NUM

Port number on the server to connect to. Default is 3306 for MySQL and 5432 for Postgres.

database=NAME

The database name.

config=FILE

(MySQL-specific) Read credentials from the MySQL options file FILE.

group=NAME

(MySQL-specific) Read credentials from section NAME of the options file supplied with the config parameter. Default section name is client.

cacert=FILE

Use secure connection to the database server via SSL. The FILE is a full pathname to the certificate authority file.

options=STRING

(Postgres-specific) Connection options.

user=NAME

Database user name.

password=STRING

Password to access the database.

Up to 16 connections can be opened simultaneously (see the BUGS section).

On error, the function returns -1. On success, the returned descriptor will be the lowest-numbered descriptor not currently open for any connection.

The function sql.connect_init is equivalent to sql.connect, but succeeds only if the connection is assigned the descriptor 0. This function is provided as a shortcut to use when only one database connection is needed.

The function sql.disconnect closes an existing database connection identified by descriptor cd.

The function sql.query performs a database query given in argument query. Params argument is a list of variable assignments separated with semicolons, similarly to the params argument described above. Each assignment has the form NAME=VALUE.

Before being executed, the query is expanded by replacing each occurrence of $NAME construct (a variable reference) with the corresponding VALUE from the params argument. Similarly to the shell syntax, the variable reference can be written as ${NAME}. 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.

Each expanded reference is then escaped to prevent SQL injection.

The function returns TRUE on success.

The result of the most recently executed query can be examined using the following functions:
INT sql.ntuples(INT
cd);

Returns the number of tuples (rows) returned by the query.

INT sql.nfields(INT cd);

Returns the number of fields in each tuple returned by the query.

INT sql.affected(INT cd);

If the most recent query updated the database, returns the number of affected rows.

The values returned by the most recent query can be retrieved using the sql.result function. The desired value is identified by the row and column number (both 0-based).

EXAMPLES

The following vcl fragment can be used to keep a statistics of visits for each URL:

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)

}
}

DOWNLOADS

Vmod-sql is available for download from this location.

The latest version is vmod-sql-1.5.

Recent news, changes and bugfixes can be tracked from the project's development page.

SEE ALSO

vmod-dbrw(3), vcl(7), varnishd(1).

Complete documentation for vmod-sql in various formats is available online.

BUGS

Maximum number of simultaneously opened connections is limited to 16. To change this value, define MAXCONN when configuring the package, e.g.:

AUTHORS

Sergey Poznyakoff

BUG REPORTS

Report bugs to <gray@gnu.org>.

COPYRIGHT

Copyright © 2013-2022 Sergey Poznyakoff
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law.


Manpage server at man.gnu.org.ua.

Powered by mansrv 1.1