Spam Filter Rules: DBQueryOne

The DBQueryOne statement performs a query on the spam filter database and returns a single value which is either the value of a record or an aggregate of several records.

The DBQueryOne statement requires a component name, key, operator and value, in the format

DBUpdate <component name> <type> <comparator> <keyvalue> <variable name>

• <type> may be 'max', 'min', 'maxnum', 'minnum', 'maxi', 'mini', 'count', 'sum', 'avg', 'first' or 'last'
  • max - returns the maximum value (treated as strings)
  • maxnum - returns the maximum value (treated as numbers)
  • maxi - returns the maximum value of lower case strings
  • min - returns the minimum value (treated as strings)
  • minnum - returns the minimum value (treated as numbers)
  • mini - returns the minimum value of lower case strings
  • count - returns the number of matching records
  • sum - returns the numeric sum of the matching records
  • avg - returns the numeric average of the matching records
  • first - returns the first matching record (by creation time)
  • last - returns the last matching record (by creation time)
• <comparator> can be one of many values
• If you set the variable ${_dbquerytime} to a PostgreSQL INTERVAL value (eg “5 minutes”) then the query will only search for records which have been created/updated within that period - eg within the last 5 minutes.

Example

DBQueryOne subject last = ${Subject} dbval

This will query the database for the last record for the 'subject' component where the key equals the contents of the variable ${Subject}, and place the value into the ${dbval} variable

If the previous value was not numeric, it will be treated as if it was zero.