DBCachePlugin

Description

DBCALL does not allow to call "external" pages as INCLUDE allows.

DBCALL's "warn" parameter can only be set to "on" and "off" and does not allow alternative content in case of a warning. The "pattern" feature used to extract a fragment from an INCLUDEd text using regular expression has been dropped. That aside, DBCALLs can be called recursively as INCLUDEs can, parametrized using key-value arguments and obeys to %STARTINCLUDE%, %STOPINCLUDE%, %STARTSECTION{"..."}% and %ENDSECTION{"..."}%.

Syntax

DBCALL

DBDUMP

DBQUERY

• $percnt: % sign
• $dollar: $ sign
• $quot: " sign
• $n: newline
• $nop: "empty string"
• $count: the number of hits
• $index: the current row index in the search
• $web: the web where the hits where found
• $formfield(<formfield-name>): the value of the given formfield
• $formatTime(<formfield-accessor> [, '<format>']): format the datefield pointed to by the <formfield-accessor> using the given <format>; the format can be in any form supported by Foswiki::Func::formatTime() (see the GMTIME documentation).
• $expand(<formfield-accessor>): return the formfield pointed to by the <formfield-accessor>
• $html(<formfield-accessor>): same as $expand() but expands macros and TML markup
• $d2n(<formfield-accessor>): converts a date formfield's value to epoch seconds
• $rss(...): encode entities so that the text can be included in an rss feed
• $encode(...): encode entities so that the text can be included in HTML input form elements
• $flatten(...): remove any special markup producing a flattened text representation
• $trunc(...,length): truncate a string to the given length
• $uc(...): converts a string to upper case
• $lc(...): converts a string to lower case
• $translate(...): translate a string using Foswiki's internal I18N or MultiLingualPlugin if installed

• $expand(@Ref.Headline): headline of the referred topic
• $expand(Nr and '.'): append a dot to the formfield value "Nr" if it exists
• $expand(Name or Author): expand to "Name" or "Author"

A formfield accessor can be computed using TopicMarkup. If the string starts with '%', it is expanded and its result is taken as the real formfield accessor. This is useful when the name of the formfield must be computed by different means.

DBRECURSE

• $web: the current web
• $topic: the current topic in the search
• $index: the topic index number in one recursion step, that is an enumeration in each recursion step
• $number: the "paragraph" number in the recursion, e.g. 1.2.1.5 depicts the path alternative in the recursion tree
• $count: number of found topics; this is the total number of found topics in the header and footer and the number of topics found so far during recursion
• $indent: 3 spaces per depth
• $indent(<string>): uses <string> for indenting the result in each depth
• $formfield(...): see #DBQUERY
• $expand(...): see #DBQUERY
• $formatTime(...): see #DBQUERY

• $dollar: $ sign
• $quot: " sign
• $n: newline
• $percnt: % sign

Example:

%DBRECURSE{"%SYSTEMWEB%.FrequentlyAskedQuestions"}%

• FAQAnApplicationWithWikiForm
• FAQDeleteOrRenameATopic
• FAQDeleteOrRenameAnAttachment
• FAQDownloadSources
• FAQEditDoesNotIncreaseTheRevision
• FAQGnuGeneralPublicLicense
• FAQHiddenUsersAndGroups
• FAQRebuildingWikiUsersTopic
• FAQSearchDoesNotWork
• FAQSimultaneousEdits
• FAQTemplate
• FAQTroubleshootingExtensions
• FAQWhatIsWikiWiki
• FrequentlyAskedQuestion

DBSTATS

• $key, $key1: the found key, this must match the first group in the pattern argument
• $key2, $key3, $key4,$key5: second, third, etc grouping in the pattern argument
• $count: the number of occurrences of the key
• $index: index of the key within the sorted result set
• $min: minimum count found in the search
• $max: maximum count found in the search
• $sum: sum of all counts
• $mean: mean occurrence of a key in the result set
• $keys: total number of keys found
• $web: the web we search in
• $topics: the list of all topics where the keys where found

%DBSTATS{"<db query>"
   field="Date"
   pattern="((\w+) (\d\d\d\d))"
   header="---++ Archive"
   format="   * <a href="...?month=$key2&year=$key3">$key</a>"
}%

Note, that the above pattern will crop away the day. Groups can be nested counting them from left to right, outside to inside: the first group matches the compete pattern, the second the month the third the year.

%DBSTATS{
  "form='Invoice'"
  field="PaidDate"
  process="$percntDATETIME{\"$value\" format=\"$month $year\"}$percnt"
  pattern="(.*?) (\d\d\d\d)"
...
}%

While the real value of a "PaidDate" formfield is stored in epoch seconds, statistics make more sense on a per month basis.

TOPICTITLE

• from a TOPICTITLE preference variable, or
• from a "TopicTitle" formfield attribute, or
• defaults to the topic name

DBPREV, DBNEXT

These two macros come in handy rendering a navigation among search results found by a DBQUERY. For example, the following code will render a navigation as found in blog applications linking to the previous and next blog entry in a list of postings ordered by created date:

%DBPREV{
  search="TopicType=~'\bBlogEntry\b' AND State != 'unpublished' AND topic != 'WebTopicEditTemplate'" 
  order="created"
  format="[[$web.$topic]]"
}%
%DBNEXT{
  search="TopicType=~'\bBlogEntry\b' AND State != 'unpublished' AND topic != 'WebTopicEditTemplate'" 
  order="created"
  format="[[$web.$topic]]"
}%

Syntax of search queries

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

Examples

• Find all topics that have been last modified before 1st January 2008

  %DBQUERY{"info.date EARLIER_THAN '1st January 2008'"}%

• Find all topics last modified by Peter

  %DBQUERY{"info.author =~ 'Peter'"}%

• Find all FAQs

  %DBQUERY{"topic =~ 'FAQ'"}%

• Find all topics that have the word "random" in it (case-insensitive) excluding the current topic

  %DBQUERY{"lc(text) =~ 'random' AND topic != 'DBCachePlugin'"}%

Perl API

use Foswiki::Plugins::DBCachePlugin;

Foswiki::Plugins::DBCachePlugin::registerIndexTopicHandler(sub {
  my ($db, $obj, $web, $topic, $meta, $text) = @_;

  ...
  $obj->set("foo", "bar");
});

my $db = Foswiki::Plugins::DBCachePlugin::getDB("Main");
my $hits = $db->dbQuery($query, undef, $order, $reverse, $include, $exclude);

while (my $obj = $hits->next) {
  my $topicName = $obj->fastget("topic");
  ...
}

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