4 Writing Queries
The query supplied to the
config function depends on the
database schema and on the kind of matching required. To ensure the
best performance of the module it is important to design the database
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 convenient for managing the table (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 is to look for a row that has ‘host’ and ‘url’ matching the incoming request and to redirect it to the URL in the ‘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 the rows that could match the request. Then the
vmod-dbrw engine will do the rest, by iterating over them
and finding the one that actually does. 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 SQL wildcard pattern,
in which case the query will look like:
SELECT dest,pattern,value,flags FROM rewrite WHERE host='$host' AND '$url' like $url ORDER BY weight
This document was generated on August 6, 2017 using makeinfo.Verbatim copying and distribution of this entire article is permitted in any medium, provided this notice is preserved.