vmod-dbrw User Manual (split by node):   Section:   Chapter:FastBack: Configuration   Up: Top   FastForward: Rewrite   Contents: Table of ContentsIndex: Concept Index

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:

id

An integer uniquely identifying the row. It is convenient for managing the table (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 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 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 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

vmod-dbrw User Manual (split by node):   Section:   Chapter:FastBack: Configuration   Up: Top   FastForward: Rewrite   Contents: Table of ContentsIndex: Concept Index