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:
An integer uniquely identifying the row. It is useful for table management purposes (e.g. deleting the row).
Host part of the incoming request.
URL part of the incoming request.
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.
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
the same as in the previous example. The meaning of
described below. Other columns are (see regex matching):
The value to be compared with the pattern.
Regular expression to use.
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
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
url column can contain a path prefix,
which can be matched using the
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
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:
This document was generated on February 15, 2019 using makeinfo.Verbatim copying and distribution of this entire article is permitted in any medium, provided this notice is preserved.