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 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')" ALERT! trunk-only
createauthor META:CREATEINFO.author "createauthor='Bob'" "META:CREATEINFO.author='Bob'" ALERT! 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 ALERT! 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

ALERT! 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: How do I re-write DBQUERY as SEARCH{type
  • Some other topic's TopicTitle: How can I process lists in Foswiki, or what is the modern replacement for ForEachPlugin?
  • Topic with a TOPICTITLE meta-pref: Question Subject Category
  • Topic without any TopicTitle: WebHome

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]]"
}%

-- PaulHarvey - 14 Jan 2011

Support.FAQForm edit

TopicClassification FrequentlyAskedQuestion
Subject Extension, Search
Topic Summary Explains how to re-write DBQUERY expressions into QuerySearch expressions
Extension DBCachePlugin
Interested Parties PaulHarvey
Related Topics WriteAQueryCookbook, DBCacheContrib, DBCachePlugin, MongoDBPlugin, QuerySearch, VarQUERY, VarSEARCH
Topic revision: r16 - 22 Jan 2013, MichaelDaum
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy