DBCacheContrib

Reusable code that treats forms as if they were table rows in a database

Reusable code that caches Foswiki topics, and provides fast searches of the content.

Summary of Contents

This module supports fast structured queries over topics in an arbitrarily-sized web. It uses a database to cache topic data to deliver much more scaleable search performance. Different back-end techologies can be used to implement the cache, to allow simple tradeoffs between scaling and raw performance.

Historically this module was designed to be used with a companion plugin, such as Foswiki:Extensions.FormQueryPlugin or Foswiki:Extensions.DBCachePlugin, which support queries and the display of query results. When used this way, the DBCacheContrib supports queries made using a simple query language.

The module can also be used in "standard schema mode" to support plugins that accelerate core functions, such as the QueryAcceleratorPlugin.

Features

• Perform efficient structured queries on data in forms

Using the built-in query language

The query language supported by DBCacheContrib is very similar to the query language used with %SEARCH, which was derived from it. The contrib can be used in two modes; compatible schema mode (as used by FormQueryPlugin and DBCachePlugin) and standard schema mode (as described in QuerySearch).

In standard schema mode the schema of the DB is as described in QuerySearch. In compatible schema mode, the underlying schema is somewhat different, to support the extended requirements of the plugins that use it.

The Compatible Schema

You can think of the database as an map of all the topics in a web. Each entry is itself a map (or hash, in perl terms) that maps a set of field names to values.

Each topic in the web automatically gets a number of standard fields, generated by reading the metadata from the topic (see MetaData)

• name - name of the topic
• parent - name of parent topic
• attachments - array of maps, each of which contains:
  • name - attachment name
  • attr - e.g hidden
  • comment - attachment comment
  • path - client path used to upload attachment
  • size - size in Kb
  • user - who uploaded the attachment
  • version - e.g. 1.3
• info - map containing:
  • author - most recent author
  • date - date of last change
  • format - topic format version
  • version - topic version number
• moved - map containing:
  • by - who moved it
  • date - when they moved it
  • from - where they moved it from
  • to - where they moved it to
• preferences - array of maps, each of which contains:
  • name - preference name
  • type - either Set or Local
  • value - the value of the named preference
• form - form type
• form name - e.g. if a "MyForm" is attached, this will be MyForm. This is a reference to a map containing a key for each field in the form. Each key maps to the value in the form data for that key.
• text - raw text of the topic)

Other fields may be added by subclasses. Refer to the documentation for the plugin that is using the DBCache for more details.

Query operators

Fields are given by name, and values by strings or numbers. Strings should always be surrounded by 'single-quotes'. Strings which are regular expressions (RHS of =, != =~ operators) use 'perl' regular expression syntax (google for perlre for help). Numbers can be signed integers or decimals. Single quotes in values may be escaped using backslash (\).

The following operators are available:

Operator	Result	Meaning
=	Boolean	LHS exactly matches the regular expression on the RHS. The expression must match the whole string.
!=	Boolean	Inverse of =
=~	Boolean	LHS contains RHS i.e. the RHS is found somewhere in the field value.
<	Boolean	Numeric <
>	Boolean	Numeric >
>=	Boolean	Numeric >=
<=	Boolean	Numeric <=
@	Node	Access node referenced by LHS. e.g. ProjectLeader@TopicTitle returns the formfield TopicTitle of the topic stored in the formfield ProjectLeader
lc	String	Unary lower case
displayValue	String	return display value of a formfield, e.g. displayValue('State')
uc	String	Unary UPPER CASE
d2n	Number	Convert a date string into epoch seconds
n2d	String	Convert epoch seconds into a date string
length	Number	Length of an array, e.g. length(attachments) to return the number of attachments
!	Boolean	Unary NOT
AND	Boolean	AND
OR	Boolean	OR
ALLOWS	Boolean	LHS is a topic that allows to perform RHS by the current user e.g. topic ALLOWS VIEW is true when the current user is allowed to view the given topic
()	any	Bracketed subexpression
IS_DATE	Boolean	Compare two dates e.g. '1 Apr 2003' IS_DATE '1 Apr 2004'
EARLIER_THAN	Boolean	Date is earlier than the given date
EARLIER_THAN_OR_ON	Boolean	Date is earlier than, or on, the given date
LATER_THAN	Boolean	LHS is later than the given date
LATER_THAN_OR_ON	Boolean	LHS is later than the given date
WITHIN_DAYS	Boolean	Date (which must be in the future) is within n working days of todays date

Working with dates

DBCacheContrib can parse dates during indexing time, that is when the topic is saved and further stored into its internal database format. Dates must be explicitly marked as being of such a data type by means of a DataForm definition. A date formfield is stored in epoch seconds as well as in a secondary attribute named <fieldName_origvalue>. This lets you access the original value as it was edited by the user.

As an effect, dates may be compared and sorted directly, i.e. without any further conversion during search time. Two dates can be compared directly against each other using numerical comparison operators. This means that the operators IS_DATE, EARLIER_THAN, EARLIERTHAN_OR_ON, LATER_THAN and LATER_THAN_OR_ON can be replaced with =, <, <, => and >= respectively.

In the case that you have to specify date strings explicitly as part of the query, use d2n(<date-string) to first convert it into an integer before comparing it against a date formfield.

WITHIN_DAYS works out the number of working days assuming a 5 day week (i.e. excluding Saturday and Sunday). Apologies in advance if your weekend is offset ± a day! Integers will automatically be converted to dates, by assuming they represent a number of seconds since midnight GMT on 1st January 1970. You can also use the d2n operator to convert a date string to such an integer.

Date formats

Dates must be dates in the format expected by Time::ParseDate. In addition the following formats are understood as well:

• dd.mm.yyyy
• yyyymmddThhmmss
• yyyymmddThhmmssZ<offset>

The cache

To achieve best perfomance the plugin caches the data read from topics in a database. The database is stored in the work area for the DBCacheContrib (see {WorkAreaDir} in configure). If any topic changes in the web, this cache is automatically updated.

Detailed Documentation

Clients use the DBCache by defining a subclass of the Foswiki::Contrib::DBCacheContrib class. Implementors are stongly recommended to read the POD documentation in the code:

Installation Instructions

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server.

Open configure, and open the "Extensions" section. "Extensions Operation and Maintenance" Tab → "Install, Update or Remove extensions" Tab. Click the "Search for Extensions" button. Enter part of the extension name or description and press search. Select the desired extension(s) and click install. If an extension is already installed, it will not show up in the search results.

You can also install from the shell by running the extension installer as the web server user: (Be sure to run as the webserver user, not as root!)

cd /path/to/foswiki
perl tools/extension_installer <NameOfExtension> install

If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See https://foswiki.org/Support/ManuallyInstallingExtensions for more help.

Copyright ©

This code is based on an original development of Motorola Inc. and is protected by the following copyrights:

• Copyright © 2002-2003 Motorola Inc. All Rights Reserved.
• Portions copyright © 2004 Crawford Currie http://www.c-dot.co.uk.
• Portions copyright © 2013-2022 Foswiki Contributors

Dependencies

Name	Version	Description
Storable	>=2.07	Required.
Sereal	>=3.00	Optional
BerkeleyDB	>=0	Optional, still experimental.

Change History

28 Apr 2022	new translate() operator; improve parsing of data form fields; use strict and use warnings everywhere and shut down some of the resulting perl warnings; fixed a few memory leaks in archivists; fixed BerkeleyDB archivist, i.e. with regards to unicode strings
15 Oct 2020	new n2d() operator to convert epoch seconds to date string; new displayValue() operator to query not only for storage values but also for their displayed and translated form; translating display values using the core I18N module or Foswiki:Extensions/MultiLingualPlugin if installed; read default formfield values while indexing a topic; removed dependency on Time::ParseDate; fixed unit tests; addess perl critics
26 Nov 2018	don't interpret any 8 digits as yyyymmdd; updated dependency on Time::PaseDate
01 Oct 2018	rationalized parsing dates
25 May 2018	added allows operator to match standard query language
20 Feb 2018	extend ref operator to dereference multi-value formfields
28 Jul 2017	Foswikitask:Item14449: numeric operands may cause a perl warning when parameters aren't numeric; trying to fill archive segments in a more efficient way
16 Jan 2017	Foswikitask:Item14270: oo-ify core; fixed parsing numbers
09 Jul 2016	Foswikitask:Item14111: remove inline data images before indexing
18 Oct 2015	Foswikitask:Item13824: fixed dbcache failing to update topics in subwebs under certain conditions
29 Sep 2015	Foswikitask:Item13763: performance improvement: use fastget() instead of get() wherever possible
25 Sep 2015	Foswikitask:Item13753: ref operator fails under certain conditions
12 Jan 2015	Foswikitask:Item13148: added support for CPAN:Sereal
29 Apr 2014	Foswikitask:Item12877: web parameter might be tainted
18 Mar 2014	Foswikitask:Item12789: fix dereferencing unblessed cache map
12 Dec 2013	Foswikitask:Item12673: allow to store keys with dots in it
10 Jul 2013	Foswikitask:Item12542: cache non-standard %META data and make it searchable
28 Mar 2013	Foswikitask:Item12458: fix op_ref in search queries
14 Mar 2013	Foswikitask:Item12425: make preferences searchable by caching them into a map instead of an array
25 Jan 2013	Foswikitask:Item12369: fixed loading cache from disk on a change
07 Jan 2013	Foswikitask:Item8195: extract and cache preference settings; Foswikitask:Item12333: implement an archivist caching a web in segments;
01 Oct 2012	Foswiktask:Item11752: don't fail to build the cache for formfield names with dots in it
10 Jan 2012	Foswikitask:Item11406: remove redundant reference to archivist from all stored values
25 Aug 2011	Foswikitask:Item11070: working around odd defaults of normalizeWebTopicName
28 Mar 2011	Foswikitask:Item9375: disabling {AlwaysUpdateCache} by default and making an expert option with appropriate warnings
17 Nov 2009	Foswikitask:Item8327: series of robustness fixes (Foswiki:Main.MichaelDaum)
30 Jun 2009	Foswikitask:Item8153: make dirs for path to cache; Foswikitask:Item8194: incoporated patch from Foswiki:Main.MichaelDaum Foswikitask:Item8195: extract and cache permissions settings
18 Jun 2009	Foswikitask:Item8183: fixed problem with Scalar::Util::weaken that was causing DBCachePlugin problems
6 Jun 2009	Foswikitask:Item1691: changes to support Foswiki:Extensions.QueryAcceleratorPlugin
7 Apr 2009	Foswikitask:Item5440: fixed negative values in SUMFIELD Foswikitask:Item8106: add back in the Map methods to the main class, as they are used by subclasses Foswikitask:Item8063: fix the accidental encoding of field values in the cache
28 Jan 2009	Foswikitask:Item453: Foswiki version; added Berkeley DB support, dropped plain-file support. Added Michael Daum's EARLIER_THAN_OR_ON and LATER_THAN_OR_ON ops. Fixed a number of bugs.
12346	fixing uptodate() for Andrew File Systems; fixed memory leak on persistent perl
16347	remove META data from text hash; include META data in all hash. Foswiki:Main.MichaelDaum
16346	caching all topic elements to an all field to allow th search in all of the text and the formfields like the normal grep-based SEARCH does. Foswiki:Main.MichaelDaum
15868	fixed WITHIN_DAYS and EARLIER_THAN. Foswiki:Main.MichaelDaum
15583	made query parser pluggable so that other plugins can implement their own predicates. Foswiki:Main.MichaelDaum
15019	added {DBCacheContrib}{AlwaysUpdateCache} to remove the updateCache from every operation. Foswiki:Main.SvenDowideit
13562	Bugs:Item3985 - fixed failures with hierarchical webs
13527	Moved the cache into the extensions work areas, instead of the web directory
12943	Bugs:Item3659: added automatic conversion of integers to dates
12923	added REF operator; added link to web object to hashes; fixed parent relation to end in System; added "web" property to topic hashes; caching META:PREFERENCES now
11537	Added lc and uc operators for case-insensitive searches
9303	TWikibug:Item1844 - don't die on broken symlinks
8682	TWikibug:Item1580 - one-char fix that makes the difference
8110	TWikibug:Item663 - formatting and text fixes
7552	TWikibug:Item997 - test update
7274	TWikibug:Item719 - onReload() is not a static method.
7262	TWikibug:Item719 - Foswiki:Main.MichaelDaum's patch (almost) to correct parameters to onReload
7260	TWikibug:Item727 - made it clean the form name using normaliseWebTopicName
6353	TWikibug:Item380 - do as the man says; make all $/ local
5720	Updated tests
5719	Fix for correct handling of parent relations
5229	Small improvement to the way it handles errors from Storable and Archive
5223	Documentation fixes, adding gifs.
5048	Cairo readiness
5036	Split from SharedCode
5031	Moving to new name
5030	About to rename
5019	Improved topic data model, cleaned up tests
5008	Added extended access syntax, [?], [*] etc.
5006	Doc fixes
5005	Poddified documentation
5003	Initial version
8 Jul 2004	Initial version, split out from FormQueryPlugin