How do I re-write DBQUERY as SEARCH{type="query"}?

This FAQ is intended to allow the DBQUERY user to transition to SEARCH and QUERY or vice versa.

Things you will miss

• No TOPICTITLE. See replacing TOPICTITLE
• No ATTACHMENTS. See replacing ATTACHMENTS
• $expand(). Use $percentQUERY{...}$percent for now, although this won't help the $expand(ThisField or ThatField) case.
• $expand(TopicTitle) did some magic to determine a TopicTitle for you, regardless of whether it came from META:PREFERENCE, * Set TOPICTITLE, or a DataForms field named TopicTitle.
• No $rss(), $encode(), $flatten(), $trunc()
• No DBRECURSE et. al. (there is TopicRecursePlugin in SVN)
• You'll keep forgetting to add type="query" to your SEARCHes.
• Unless you're using DBIStoreContrib or MongoDBPlugin, the searches will be slower.
• skip/limit parameters. SEARCH offers a paging function which isn't drop-in compatible.
• WITHIN_DAYS and friends. Date-strings must be wrapped in d2n() in QuerySearch, times are assumed to be stored on the topic in epoch seconds.
• Change exclude="sometopic" to excludetopic="sometopic".
• Add nonoise="on" to suppress output of search statistics.
• Change topics="sometopic" to topic="sometopic".
• Change exclude="sometopic" to excludetopic="sometopic".
• Change sort="info.date" to order="modified".

Re-writing queries

DBQUERY has a slightly different "map" of topic property accessors.

• DBQUERY "map" is documented at DBCacheContrib
• QuerySearch map documented at QuerySearch. See also WriteAQueryCookbook

DBQUERY	QuerySearch	DBQUERY Example	QuerySearch Example	Notes
form	form.name	"form~'*Foo'"	"form.name~'*Foo'"
parent	parent.name	"parent~'*Foo'"	"parent.name~'*Foo'"
createdate	META:CREATEINFO.date	"createdate EARLIER_THAN '2010-09-01'"	"META:CREATEINFO.date < d2n('2010-09-01')"	trunk-only
createauthor	META:CREATEINFO.author	"createauthor='Bob'"	"META:CREATEINFO.author='Bob'"	trunk-only
all	fields.value and text	"all ~ 'Foo*'"	"text =~ 'Foo' and fields.value =~ 'Bar'"	search in formfields and in text of topic

Re-writing format strings

See FormattedSearch for SEARCH

DBQUERY	QuerySearch	Note
$expand(...)	$percentQUERY{...}$percent	Not exact replacement. See things you will miss
$expand(@SomeTopic.FieldName)	$percentQUERY{'SomeTopic'/FieldName}$percent
$count	$nhits	Beware there's a totally different $count in SEARCH
$formatTime(...)	$percentDISPLAYTIME{...}$percent	Not exact replacement. See DISPLAYTIME
$rss(...)	$percentENCODE{...}$percent	TODO: Equivalent ENCODE expression
$encode(...)	$percentENCODE{...}$percent	TODO: Equivalent ENCODE expression
$flatten(...)	?
$trunc(...)	?
$d2n(...)	$percentQUERY{d2n(...)}$percent
$uc(...)	$percentQUERY(uc(...)}$percent
$lc(...)	$percentQUERY(lc(...)}$percent
$createauthor	$createusername	Maybe $createauthor is the wikiname, if so, try $createwikiname

Replacing TOPICTITLE

This example assumes Foswiki trunk. It won't work on Foswiki 1.1, but you could re-write using EasyMacroPlugin

A simple replacement for TOPICTITLE would be as follows:

%QUERY{"preferences[name='TOPICTITLE'].value"}%

to extract the preference on the current topic, or

%QUERY{"'Some/Other.Topic'/preferences[name='TOPICTITLE'].value"}%

on some other topic.

A more complete replacement to use either the TopicTitle formfield or the TOPICTITLEPREFERENCE or default to the plain TOPIC name is

%IF{
       "'%OBJECT%'/preferences[name='TOPICTITLE'] or '%OBJECT%'/fields[name='TopicTitle']"    
       then="$percntQUERY{\"'%OBJECT%'/preferences[name='TOPICTITLE'].value\"}$percnt$percntQUERY{\"'%OBJECT%'/fields[name='TopicTitle'].value\"}$percnt"" 
       else ="%OBJECT%"
}%

Where %OBJECT% is your web.topic to extract the topic title for.

However, this isn't a complete replacement. If the topic title is specified in both a formfield TopicTitle and in a preference variable TOPICTITLE, you'll get them both in the output.

This won't work with TOPICTITLE set via Set statements (at least not until SynchroniseInlineAndMetaPrefs is implemented).

As another alternative, on Foswiki trunk, we can invent a parameterised TOPICTITLE preference which could live in your Main.SitePreferences.

Until Tasks.Item10187 is finished, the regex match on '^%DEFAULT' is necessary:

   * Set TOPICTITLE = %INCLUDE{"Support.Faq40" section="TOPICTITLE" ref="%IF{"'%DEFAULT%'=~'^%DEFAULT'" then="%TOPIC%" else="%DEFAULT%"}%"}%
%STARTSECTION{"TOPICTITLE"}%%IF{
  "'%ref%'/preferences[name='TOPICTITLE']"
  then="$percentQUERY{\"'%ref%'/preferences[name='TOPICTITLE'].value\"}$percent"
  else="$percentIF{\"'%ref%'/TopicTitle\"
    then=\"$percentQUERY{\"'%ref%'/TopicTitle\"}$percent\"
    else=\"%ref%\"
  }$percent"
}%%ENDSECTION{"TOPICTITLE"}%
   * This topic's !TopicTitle: %TOPICTITLE%
   * Some other topic's !TopicTitle: %TOPICTITLE{"Faq39"}%
   * Topic with a =TOPICTITLE= [[System.PreferenceSettings][meta-pref]]: %TOPICTITLE{"BestPracticeTipsCategory"}%
   * Topic without any !TopicTitle: %TOPICTITLE{"WebHome"}%

• This topic's TopicTitle:
• Some other topic's TopicTitle:
• Topic with a TOPICTITLE meta-pref: Question Subject Category
• Topic without any TopicTitle:

Replacing ATTACHMENTS

Not all ATTACHMENTS features may reproduced with standard Foswiki core macros, however basic listing and querying can using a QUERY inside of a FORMAT, using type="string":

SVG attachments by ArthurClemens:

%FORMAT{
  "%QUERY{"'Community.BrandLogoTalk'/attachments[lc(name)=~'\.svg$' AND user='ArthurClemens'].name"}%"
  type="string"
  format="   * [[%PUBURLPATH%/Community/BrandLogoTalk/$item][$item]]"
}%

• Foswiki_logo.svg
• Foswiki_logo_updrop.svg
• Foswiki_logo_glyph.svg
• Foswiki_logo_leaf.svg
• Foswiki_logo_font.svg
• Foswiki_logo_horizontal.svg
• Foswiki_logo_mark.svg
• Foswiki_logo_vertical.svg

-- PaulHarvey - 14 Jan 2011