     [file version.texi not included]

Published by the Free Software Foundation, 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

Copyright © 2013-2018 Sergey Poznyakoff

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover, and no Back-Cover texts. A copy of the license is included in the section entitled ``GNU Free Documentation License''.

vmod-dbrw
*********

This edition of the vmod-dbrw User Manual, last updated UPDATED, documents vmod-dbrw Version VERSION.

Introduction to vmod-dbrw
*************************

Vmod-dbrw is a module for Varnish Cache (https://www.varnish-cache.org) which implements database-driven rewrite rules.  These rules may be similar to RewriteRule directives implemented by mod_rewrite (http://httpd.apache.org/docs/current/rewrite/) module in Apache or to Redirect directives of its mod_alias module.  What distinguishes the vmod-dbrw rules from these, is that they are handled by Varnish, before the request reaches the httpd server, and that they are stored in an SQL database, which makes them easily manageable.

Some web sites implement thousands of rewrite rules.  The purpose of this module is to facilitate deploying and handling them.

Overview
********

Rewrite rules are stored in a MySQL or PostgreSQL database.  The vmod-dbrw module does not impose any restrictions on its schema.  It only needs to know the SQL query which is to be used to retrieve data.  This query is supplied to the module, along with the credentials for accessing the database, by calling the config function in the vcl_recv subroutine of the Varnish configuration file.

Once the module is configured, the rewrite function can be called in the appropriate place of the Varnish configuration file.  Its argument is a list of variable assignments separated by semicolons, each assignment having the form NAME=VALUE.  When called, rewrite expands the SQL query registered with the prior call to config by replacing each $NAME construct (a variable reference) with the corresponding VALUE from its argument.  Similarly to the shell syntax, the variable reference can also be written as ${NAME}. This latter form can be used in contexts where the variable reference is immediately followed by a letter, digit or underscore, to prevent it from being counted as a part of the name. Special syntax is available for substituting default values and invoking built-in functions during the expansion of the query. See Expansions, for a detailed description of these.

Having undergone expansions, the query is sent to the database server. The returned set of records (if non-empty) is processed depending on the number of fields it contains.

If the returned set has one or two columns, only the first tuple is used and rewrite returns the value of its first column.

Otherwise, if the returned set has three or more columns, the regular expression matching is performed.  For the purpose of this discussion, let's refer to the columns  as follows: RESULT, REGEXP, VALUE and FLAGS.  The FLAGS column is optional.  Any surplus columns are ignored.

For each returned tuple, the VALUE column undergoes variable expansion, using the same algorithm as when preparing the query, and the resulting string is matched with the REGEXP column, which is treated as an extended POSIX regular expression.  If the value matches the expression, the RESULT column is expanded by replacing backreferences: each occurrence of $DIGIT (where DIGIT stands for a decimal digit from 0 through 9) is replaced by the contents of the DIGITs parenthesized subexpression in REGEXP.  For compatibility with the traditional usage, the \DIGIT notation is also allowed.  The resulting value is then returned to the caller.

Optional FLAGS column is a comma-separated list of flags that modify regular expression handling:

NC
nocase
     Treat REGEXP as case-insensitive regular expression.

case
     Treat REGEXP as case-sensitive (default).

QSA
qsappend
     Treat the resulting value as URL; append any query string from the original VALUE to it.

QSD
qsdiscard
     Treat the resulting value as URL; discard any query string attached to the original VALUE.

redirect=CODE

R=CODE
     On success, set the X-VMOD-DBRW-Status header to CODE, which must be a valid HTTP status code.

If REGEXP or VALUE is NULL, strict matching is assumed (see strict matching).

If FLAGS is NULL, it is ignored.

Configuration
*************

 -- function: void config (string DBTYPE, string PARAMS,
     string QUERY)
     This function configures the module and provides it with the data necessary to connect and use the database.  It is normally called from the vcl_recv subroutine.

     Arguments:

     dbtype
          Type of the database to use.  Valid values are mysql and pgsql.

     params
          Database connection parameters.  This is a list of NAME=VALUE assignments separated by semicolons. The VALUE part can be any sequence of characters, excepting white space and semicolon.  If VALUE contains any of these, they either must 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:

          Table: Backslash escapes

          Sequence   Replaced by
          \a   Audible bell character (ASCII 7)
          \b   Backspace character (ASCII 8)
          \f   Form-feed character (ASCII 12)
          \n   Newline character (ASCII 10)
          \r   Carriage return character (ASCII 13)
          \t   Horizontal tabulation character (ASCII 9)
          \v   Vertical tabulation character (ASCII 11)

          If a backslash is immediately followed by a symbol not listed in the above table, it is removed and the symbol is reproduced verbatim.

          Valid parameters are:

          debug=N
               Set debugging level.  Argument 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=N
               Port number on the server to connect to.  Default is 3306 for MySQL and 5432 for Postgres.

          database=NAME
               The name of the database to use.

          config=FILENAME
               (MySQL-specific) Read database access credentials and other parameters from the MySQL options file FILENAME.

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

          cacert=FILENAME
               Use secure connection to the database server via SSL.  The FILENAME argument is a full pathname of the certificate authority file.

          timeout=N
               Sets idle timeout for a single connection.  The connection will be closed and opened again if the module is to use it after N or more seconds since its last use. Set timeout=-1 to disable idle timeout (session will remain open until the SQL server closes it). Set timeout=0 to close the connection after each request (not recommended).

               The default depends on the selected SQL backend.  For MySQL, it equals the value of the wait_timeout global variable.  For PostgreSQL, it is -1.

          options=STRING
               (Postgres-specific) Connection options.

          user=NAME
               Database user name.

          password=STRING
               Password to access the database.

     query
          The SQL query to use.  It can contain variable references ($NAME or ${NAME}), which will be expanded to the actual value of the NAME argument to the function rewrite. See Expansions, for details.

The example below configures vmod-dbrw to use MySQL database rewrite, with the user name varnish and password guessme.

     import dbrw;

     sub vcl_recv {
         dbrw.config("mysql",
                "database=rewrite;user=varnish;password=guessme",
                {"SELECT dest
                     FROM redirects
                    WHERE host='$host'
                     AND url='$url'"});
     }

Expansions
==========

The query argument to the dbrw.config function normally contains variable references.  A variable reference has the form $VARIABLE or ${VARIABLE}, where VARIABLE is the variable name.  When the dbrw.rewrite function (see Rewrite) is called, each such reference is expanded to the actual value of VARIABLE passed in the argument to that function.

The two forms are entirely equivalent.  The form with curly braces is normally used if the variable name is immediately followed by an alphanumeric symbol, which will otherwise be considered a part of it. This form also allows for specifying the action to take if the variable is undefined or expands to an empty value.

During variable expansion, the forms below cause dbrw.rewrite to test for a variable that is unset or null (i.e., whose value is an empty string).  Omitting the colon results in a test only for a variable that is unset.

${VARIABLE:-WORD}
     Use Default Values.  If VARIABLE is unset or null, the expansion of WORD is  substituted.   Otherwise, the value of VARIABLE is substituted.

${VARIABLE:=WORD}
     Assign Default Values.  If VARIABLE is unset or null, the expansion  of WORD is assigned to variable.  The value of VARIABLE is then substituted.

${VARIABLE:?WORD}
     Display Error if Null or Unset.  If VARIABLE is null or unset, the expansion of WORD (or a message to that effect if WORD is not present) is output to the current logging channel.  Otherwise, the value of VARIABLE is substituted.

${VARIABLE:+WORD}
     Use Alternate Value.  If VARIABLE is null or unset, nothing is substituted, otherwise the expansion of WORD is substituted.

After expanding variables, the query undergoes command expansion.  Syntactically, a command invocation is

     $(CMD ARGS)

where CMD is the command name, and ARGS is a list of arguments separated by whitespace.  Arguments can in turn contain variable and command references.

During command expansion, each invocation is replaced by the result of the call to function CMD with the supplied arguments.

As of version VERSION of vmod-dbrw, only one function is declared:

 -- Command: urlprefixes URI
     Expands to comma-separated list of path prefixes contained in URI, starting from the longest one (URI itself, with eventual query part stripped off).  Single / is not included in the list.  Each list item is quoted.  The expansion can be used in the IN () SQL conditional.

Writing Queries
***************

The query supplied to the config function depends on the database schema and on the desired kind of matching (e.g. exact vs. wildcard).  To ensure the best performance of the module it is important to design the schema and the query so that the database look up be as fast as possible.

Suppose that you plan to use vmod-dbrw to implement redirection rules based on strict matching (see strict matching).

The simplest database structure for this purpose (assuming MySQL) will be:

     CREATE TABLE redirects (
       id INT AUTO_INCREMENT,
       host varchar(255) NOT NULL DEFAULT '',
       url varchar(255) NOT NULL DEFAULT '',
       dest varchar(255) DEFAULT NULL,
       PRIMARY KEY (host,url)
     );

The columns and their purpose are:

id
     An integer uniquely identifying the row.  It is useful for table management purposes (e.g. deleting the row).

host
     Host part of the incoming request.

url
     URL part of the incoming request.

dest
     Destination URL to redirect to.

The rewrite function looks up a row that has host and url matching the incoming request and, if found, returns the value of its dest column.  The corresponding query is:

     SELECT dest FROM redirects WHERE host='$host' AND url='$url'

The variables host and url are supposed to contain the actual host and URL parts of the incoming request.

Handling regular expression matches is a bit trickier.  Your query should first return such rows that could possibly match the request. Then the vmod-dbrw engine will do the rest, by iterating over the returned set and finding the row that actually matches the request.  It will iterate over the rows in the order they were returned by the database server, so it might be necessary to sort them by some criterion beforehand.

The following is an example table structure:

     CREATE TABLE rewrite (
       id INT AUTO_INCREMENT,
       host varchar(255) NOT NULL DEFAULT '',
       url varchar(255) NOT NULL DEFAULT '',
       dest varchar(255) DEFAULT NULL,
       value varchar(255) DEFAULT NULL,
       pattern varchar(255) DEFAULT NULL,
       flags char(64) DEFAULT NULL,
       weight int NOT NULL DEFAULT '0',
       KEY source (host,url)
     );

The meaning of id, host, and dest is the same as in the previous example.  The meaning of url is described below.  Other columns are (see regex matching):

value
     The value to be compared with the pattern.

pattern
     Regular expression to use.

flags
     Optional flags.

weight
     Relative weight of this row in the set.  Rows will be sorted by this column, in ascending order.

The simplest way to select candidate rows is by their host column:

     SELECT dest,pattern,value,flags
     FROM rewrite
     WHERE host='$host'
     ORDER BY weight

One can further abridge the returned set by selecting only those rows whose url column is the prefix of the requested URL:

     SELECT dest,pattern,value,flags
     FROM rewrite
     WHERE host='$host'
     AND LOCATE(url,'$url')==1
     ORDER BY weight

Furthermore, the url column can contain a path prefix, which can be matched using the IN conditional:

     SELECT dest,pattern,value,flags
     FROM rewrite
     WHERE host='$host'
     AND url IN ($(urlprefixes $url))
     ORDER BY weight

Notice the use of the $(urlprefixes $url). This invokes the built-in function urlprefixes, which expands to comma-separated list of properly quoted pathname prefixes, constructed from its argument. For example, if $url is /local/user/local?a=1, then the expansion of $(urlprefixes $url) is:

     '/local/user/local','/local/user','/local'

The rewrite Function
********************

 -- function: string rewrite (string ARGS)
     This function is the working horse of the module.  It rewrites its argument using the database configured in the previous call to config and returns the obtained value.

     To do so, it performs the following steps:

       * Parameter parsing

          The ARGS parameter must be a list of NAME=VALUE pairs separated by semicolons.  The function parses this string and builds a symbol table.

       * Variable expansion

          Using the symbol table built in the previous stage, each occurrence of $NAME or ${NAME} is replaced by the actual value of the variable NAME from the table.  Expanding an undefined variable is considered an error.

       * Establishing the database connection

          Unless the connection has already been established by a prior call to rewrite, the function establishes it using the parameters supplied earlier in a call to config.  If the connection fails, the function returns NULL immediately.

          Database connections are persisting and thread-specific.  This means that each thread keeps its own connection to the database and attempts to re-establish it if it goes down for some reason.

       * Query execution

          The query is sent to the server and the resulting set collected from it.

       * Result interpretation

          The resulting set is interpreted as described in result interpretation.  This results in a single value being returned to the caller.

Assuming the database structure similar to the one discussed in the previous chapter, the following example illustrates how to use rewrite to redirect the incoming request.

     sub vcl_recv {
         dbrw.config("mysql",
                "database=rewrite;user=varnish;password=guessme",
                {"SELECT dest
                     FROM redirects
                    WHERE host='$host'
                     AND url='$url'"});
         set req.http.X-Redirect-To =
             dbrw.rewrite("host=" + req.http.Host + ";" +
                          "url=" + req.url);
         if (req.http.X-Redirect-To != "") {
             return(synth(301, "Redirect"));
         }
     }

The synth sub must be provided in order to construct redirection responses:

     import std;

     sub vcl_synth {
         if (resp.status == 301) {
             set resp.http.Location = req.http.X-Redirect-To;
             if (req.http.X-VMOD-DBRW-Status != "") {
                 set resp.status =
                    std.integer(req.http.X-VMOD-DBRW-Status, 301);
             }
             return (deliver);
         }
     }

The X-VMOD-DBRW-Status header, if set, contains the status code to be returned to the client (see X-VMOD-DBRW-Status).  Notice the use of the vmod_std module to cast it to integer.

How to Report a Bug
*******************

Email bug reports to gray@gnu.org.

As the purpose of bug reporting is to improve software, please be sure to include a detailed information when reporting a bug. The minimum information needed is:

  * Module version you use.

  * A description of the bug.

  * Conditions under which the bug appears.

  * It is often helpful to send the contents of config.log file along with your bug report. This file is created after running ./configure in vmod-dbrw source root directory.

GNU Free Documentation License
******************************

     [file fdl.texi not included]

Concept Index
*************

This is a general index of all issues discussed in this manual