Vmod-dbrw
	Database-driven rewrites for Varnish Cache	Sergey Poznyakoff

• Main
• Downloads
• Documentation

4 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'

This document was generated on April 9, 2020 using makeinfo.