cross
New Foswiki release 2.1.6 is available with important security fixes.
Sourceforge foswiki email lists being discontinued. Subscribe to the new Foswiki announce and discuss lists at MailingLists
This question about Topic Markup Language and applications: More info required

DBQUERY with variable search argument

Discussion

Is there a way to construct DBQUERY search arguments dynamically so that a query could be refined? I used to do this with the FormQueryPlugin, which could construct successive queries by taking previous queries as input. This produced something like the effect of creating temporary tables which could themselves be queried.

I upgraded an installation from 1.1.4 to 1.1.9 recently and it seems that the FormQueryPlugin finally stopped working with the newer DBCacheContrib. I read elsewhere that formal support for FormQueryPlugin has ended. The suggestion was to use the DBCachePlugin to run queries.

With that in mind, I thought I would create a refined search with some IF macros with nested calls to SESSION_VARIABLE gradually adding on more search terms to a query string based on user input.

Unfortunately, it seems that I can't get DBQUERY itself to accept a variable argument (via a SESSION_VARIABLE call in place of the usual "search" string). The query exits with an error, printing the offending query that doesn't work - which appears exactly the way I want and DOES work if I literally type the DBQUERY with a regular string constant as argument.

I imagine there must be some meta / extra character information attached to the SESSION_VARIABLE macro expansion (or within my constructed string) that is messing up the DBQUERY. The SESSION_VARIABLE contents render like I expect to the screen, but then I have no idea what meta/escape characters might have been removed in the rendering process.

Can DBQUERY simply not handle a MACRO in its search argument?

Thanks for suggestions.

-- JohnRoberts - 17 Apr 2015

Hi John, how about you add your experimental wiki app in here to illustrate in terms of CODE what you are trying to do.

-- MichaelDaum - 17 Apr 2015

Hi Michael, I'm not actually writing an app per se, but filling a Wiki page with DBQUERY results. Originally, I used FORMQUERY chains. See the FORMQUERY example below. I don't want to revive the FORMQUERY plugin, but would like to duplicate such chaining with DBQUERY.

Now to duplicate the chaining effect that FORMQUERY provided, but using DBQUERY, two possibilities occur to me:
  1. Constructing and succesively refining the search string fed to DBQUERY.
    • The refinements would be added to the search string and then the string itself fed to a DBQUERY once.
    • I have attempted this approach and have failed. DBQUERY doesn't seem able to take a variable argument.
    • I will upload some examples.
  2. Capturing DBQUERY output topic list and then feeding that to the next DBQUERY as the include list of topics.
    • I have not yet tested this approach.
    • Similar to taking a variable search string, the include option would need to be able to accept a variable argument.

Update: A single SESSION_VARIABLE substitution is working for the search string. I'm still having problems with a more complicated search. Let me put this on hold until I can figure out my trouble with even this simple example.

I modified the test and things seem to be working now for this simple example. I think one issue I was having problems with was how to address the form variables in the DBQUERY search: whether to use the map X.Y format or to use just the Y field. It seems I should use the Y field.

Using X.Y mapping syntax in my DBQUERY may have been the source of my earlier query failures rather than using a variable argument.

I'm going to take a look at my more complicated search page and see if I can correct the query problems that first prompted this question.

-- JohnRoberts - 17 Apr 2015

 

FormQuery Comparison

Here's an example of how the FORMQUERY chaining used to work. This is a bit of wiki to run on the System web that searches for topics with the FAQForm. It then refines the search to select only those with "how" in the summary.

Wiki Text Of Test

---++ !FormQuery
---+++ Original Search
<verbatim>
%FORMQUERY{name=QueryFAQ search="(form.name='FAQForm') AND (topic != 'FAQForm') AND (topic != 'FAQTemplate')"}%
%SHOWQUERY{query=QueryFAQ header="|  *Topic*  |  *Summary*  |" format="|$topic|$TopicSummary|"}%
</verbatim>
Result: 
%FORMQUERY{name=QueryFAQ search="(form.name='FAQForm') AND (topic != 'FAQForm') AND (topic != 'FAQTemplate')"}%
%SHOWQUERY{query=QueryFAQ header="|  *Topic*  |  *Summary*  |" format="|$topic|$TopicSummary|"}%

---+++ Refine Search
Feed the original search back into itself to create a refined search.  This is the useful part.  Rather than hard coding the 'how' as in this example, the !TopicSummary could be matched against a string controlled by a URL parameter from an html form.  Or, you might imagine surrounding these FORMQUERY query refinements in IF macros to control what refinements were performed.
<verbatim>
%FORMQUERY{name=QueryFAQ query=QueryFAQ search="lc(TopicSummary) =~ 'how'"}%
%SHOWQUERY{query=QueryFAQ header="|  *Topic*  |  *Summary*  |" format="|$topic|$TopicSummary|"}%
</verbatim>
Result: 
%FORMQUERY{name=QueryFAQ query=QueryFAQ search="lc(TopicSummary) =~ 'how'"}%
%SHOWQUERY{query=QueryFAQ header="|  *Topic*  |  *Summary*  |" format="|$topic|$TopicSummary|"}%

Give me a minute and I'll post the results as seen on a 1.1.4 and 1.1.9 foswiki.

On a 1.1.4 Server

  • Form query search as implemented on a 1.1.4 foswiki.:
    01 OriginalSearch 1.1.4.png
  • Refined form query search on 1.1.4. Query is fed back to itself with further search terms.:
    02 RefinedSearch 1.1.4.png

On a 1.1.9 Server

  • Form query search on foswiki 1.1.9. Error seems to be more an issue of showquery not expanding variables, because the number of rows indicates that some results were found.:
    03 OriginalSearch 1.1.9.png
  • Refining the search on a foswiki 1.1.9 generates errors.:
    04 RefinedSearch 1.1.9.png

DBQUERY

SESSION_VARIABLE Argument

Example Wiki Text

---+++ Simple Variable Argument
---++++ Hardcoded DBQUERY
---+++++ Original
%DBQUERY{"(form.name =~ 'FAQForm')" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%
---+++++ Refined
%DBQUERY{"(form.name =~ 'FAQForm') AND (lc (TopicSummary) =~ 'how')" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%
---++++ DBQUERY With Simple SESSION_VARIABLE Input
%SESSION_VARIABLE{"MyDBQUERY" set="(form.name =~ 'FAQForm')"}%

%SESSION_VARIABLE{"MyDBQUERY"}% 

%DBQUERY{"%SESSION_VARIABLE{"MyDBQUERY"}%" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%

---++++ DBQUERY With Concatenated SESSION_VARIABLE Input
%SESSION_VARIABLE{"MyDBQUERY" set="(form.name =~ 'FAQForm')"}%

%SESSION_VARIABLE{"MyDBQUERY" set="%SESSION_VARIABLE{"MyDBQUERY"}% AND (lc(TopicSummary) =~ 'how')"}%

%SESSION_VARIABLE{"MyDBQUERY"}% 

%DBQUERY{"%SESSION_VARIABLE{"MyDBQUERY"}%" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%

---++++ DBQUERY With Concatenated SESSION_VARIABLE Input, NOOP Variation
%SESSION_VARIABLE{"MyDBQUERY" set="(form.name =~ 'FAQForm')"}%

%SESSION_VARIABLE{"MyDBQUERY" set="%SESSION_VARIABLE{"MyDBQUERY"}% AND (lc(%NOP%TopicSummary) =~ 'how')"}%

%SESSION_VARIABLE{"MyDBQUERY"}% 

%DBQUERY{"%SESSION_VARIABLE{"MyDBQUERY"}%" exclude="FAQ(Form|Template)" hidenull="on" format="|$topic|$formfield(TopicSummary)|"}%

Result on Foswiki 1.1.9

At the moment, the very simple variable substitution is working. However, if I refine the search, it fails. I need to test this and come back with results.

I've tested it and now variable substitution with concatenated SESSION_VARIABLE usage is working.

Providing for Search Refinement Using URL Parameters

John - Are you wishing to retain the search results past viewing an individual search page? That's the only reason I can see for using session variables. There are several ways to pass the results from an initial search to subsequent searches within the same page. Perhaps I'm missing something regarding what you're trying to accomplish, however it possible to create search forms which allow users to refine the search simply by taking the url params from the initial search and feed them back into the search form itself.

Here's what it would look like using FAQ topics as example:

Search FAQ

Subject Areas:
Topic Summary:
Extension:

Searched: form.name='FAQForm' AND name!= 'FAQTemplate'
Topic Summary
Cron webnotify and mailercontrib on Ubuntu server
(Email and Notifications, Operating system, Setup, MailerContrib)
How to enable cron for email notifications on Ubuntu server
How to install Foswiki on Mac OS X Leopard
(Installation, Operating system, )
 
How do you handle errors resulting from calls to CGI executables or REST handlers?
(Development, )
How to handle errors resulting from calls to CGI executables or REST handlers
Where can I find professional support?
(Help and Support, Installation, Usage, )
 
How can I manually approve registrations?
(Registration or Authentication or Authorisation, )
You have installed Foswiki, and the site is running nicely. However everyone and his uncle is registering on your site, including some "undesireables". How can you set up Foswiki so you can manually filter registrations?
Where is the preview mode when editing
(Editing, )
Description of editors and preview.
What are the rules on form field names? Eg. Can they have spaces?
(Data forms, )
Highlighting documentation in the System.DataForms topic regarding field naming rules/possibilities
How can I set a topic variable using a HTML form button/elements?
(Extension, Macros or Variables, SetVariablePlugin)
Using Extensions.SetVariablePlugin to set a topic variable from user input/HTML form
Why does FastCGI cause the browser to pause 15+ seconds on each view?
(Accelerators or modperl or FastCGI, )
Some installations suffer a delay equal to the KeepAliveTimeout value configured in apache.conf
Why can't I use BasicAuth to protect configure with FastCGI?
(Registration or Authentication or Authorisation, )
 
Does Foswiki have encrypted groups or any other technique to restrict access all the way?
(Access, )
 
My SEARCHes are a mess. How can I write them more clearly?
(Search, )
Writing searches using sectional includes
NatSkin and TinyMCE editor
(Editing, Extension, NatEditPlugin, NatSkin)
 
How do I stop a System.FormattedSearch using $formfield() from breaking TML tables?
(Search, )
Tables constructed with TML markup are sensitive to newlines, so usage of $formfield() values containing them demands special attention
configure tells me that I am "..using a version of $CGI that is know to have issues with Foswiki"
(Configure, )
Some OS distributions ship versions of the perl CGI library that are known to have issues with Foswiki
How can I rebuild my WikiUsers topic?
(Registration or Authentication or Authorisation, )
Rebuild your WikiUsers topic with this search. Foswiki 1.1+ only
I keep getting mysterious errors from RCS
(Errors, )
What to do about inexplicable RCS errors
How do I put raw text / Javascript / CSS in my topics?
(Development, Editing, Frontend or Javascript or CSS, Usage, )
 
How should I set filesystem access modes?
(Setup, )
How do I set up filesystem access modes correctly as an unprivileged user
Does Foswiki require Javascript?
(Setup, Usage, Other..., )
Information about Foswiki's use of Javascript
A plugin I installed is not working. How can I troubleshoot it?
(Debugging, Extension, )
Some plugins fail to work properly when they are installed. Here are some steps to get them going.
How do I move data forms to the top of the topic?
(Data forms, Editing, Usage, PatternSkin)
How do I move data forms to the top of the topic?
How can I use LESS for CSS using Less.js
(Editing, Frontend or Javascript or CSS, )
 
How do I redirect users from old twiki install to new foswiki install urls?
(Web server, )
Migrating to Foswiki - URL Rewrite rules
How can I show the user's e-mail address in the Users topic?
(Registration or Authentication or Authorisation, )
Adding the user e-mail address to the UserForm
How can I stop the WYSIWYG editor leaving tables as HTML?
(Editing, Extension, )
Explains how to use the WYSIWYG_STICKYBITS preference setting to ensure tables always convert to TML
How can I tell what DEFs are generating certain parts of my skin/template output?
(Development, Templates, )
Explains how to enable TRACE mode for debugging templates
How can I process lists in Foswiki, or what is the modern replacement for ForEachPlugin?
(Extension, Usage, ForEachPlugin)
How to process lists in Foswiki, and convert usage of ForEachPlugin
Login for foswiki-beginners-vm
(Registration or Authentication or Authorisation, )
accessing the virtual machine
How do I re-write DBQUERY as SEARCH{type="query"}?
(Extension, Search, DBCachePlugin)
Explains how to re-write DBQUERY expressions into System.QuerySearch expressions
Do I really have to hard-code a formatted search to suit a given form and its fields?
(Extension, Search, FlexFormPlugin)
A generic "pretty print" recipe for displaying all the formfields on a set of topics.
How do I (bulk) import data into Foswiki?
(Usage, Other..., )
Collection of resources related to bulk-importing data into Foswiki
How can I stop the WYSIWYG editor converting tables to TML?
(Editing, Extension, )
 
My Foswiki shows times in UTC/GMT, how do I fix that?
(Configure, Internationalisation or Localisation, Setup, )
Explains how to set the {DisplayTimeValues} configure setting
Basic configuration for Cyrillic support
(Configure, Internationalisation or Localisation, Setup, )
 
How do I create two tables side by side?
(Editing, Frontend or Javascript or CSS, )
Use div tags to make two tables appear side by side
How do I configure Apache to always redirect users to my secure (https) site?
(Security, Web server, )
A rewrite rule to redirect users to the https:// version of a site
The new PatternSkin broke my Foswiki 1.0 system
(Extension, Frontend or Javascript or CSS, Upgrading, PatternSkin)
PatternSkin on 1.1 has moved away from backwards compatibility with Foswiki1.0.
Create a Solr search form on any page
(Extension, Search, SolrPlugin)
 
How do I dump my topics' formfields out as XML?
(Data forms, Other..., )
Demonstrate usage of QUERY and FORMAT to export formfield data in XML
Can a SEARCH take another SEARCH's output as input? How to search all webs and subwebs, while excluding subwebs of a particular web
(Search, )
Example of exploiting "inside-out, left-to-right" macro expansion behaviour to exclude a web (and its subwebs) from a SEARCH using an inner (nested) SEARCH
Is there a nice way to make a (pre-computed) version of a slow page available, so Foswiki doesn't have to expand all its macros on every single view?
(Other..., )
Demonstrates the output of a nested search that can be saved to a topic at the push of a button; useful for generating "static" versions of slow pages
How can I SEARCH for topics which have some metadata (Eg. parent.name) that I want to change?
(Search, )
Demonstrate usage of SEARCH on parent.name with a way to more easily bulk-update pages.
How do I capture a selection in a jqgrid for use in my wiki application?
(Extension, Frontend or Javascript or CSS, JQGridPlugin)
Demonstrates usage of Extensions.JQGridPlugin's multiselect and gridComplete parameters
How can I report a list of topic children, with a total number of topics under each?
(Search, )
Explains a nested search
How can I more easily create topics that use System.DataForms - do I really need to write HTML code each time?
(Data forms, Extension, User interface, FlexFormPlugin)
Describes a helper topic that uses Extensions.FlexFormPlugin to make a reusable UI for creating and summarising System.DataForms topics
How do I search for all child topics, including their descendants?
(Extension, Search, DBCachePlugin, TreePlugin)
Shows three options for searching through topics which are parent of a parent-child hierarchy
When creating a topic from a template, can I make it so that %MACROs% are expanded on save?
(Editing, Usage, )
Demonstrate usage of System.VarSTARTSECTION's expandvariables parameter in topic templates
How do I remotely update topics (form fields)?
(Other..., )
 
How can I create a simple Google Sitemap for Search Engine Optimisation?
(Public websites, Search, )
 
How can I re-write image tag URLs with FilterPlugin's SUBST macro?
(Extension, FilterPlugin)
Demonstrates usage of Extensions.FilterPlugin's SUBST macro to re-write <img> tag URLs
How can I broadcast a message to my wiki users, Eg. for maintenance reasons?
(Macros or Variables, Usage, Other..., )
Explains the BROADCASTMESSAGE preference setting
How can I use JQueryTextboxList on my own HTML input field (without using data forms)?
(Extension, Frontend or Javascript or CSS, JQueryPlugin)
 
How can I dynamically list only fields with values on a topic?
(Data forms, Search, )
 
How can I create my own login box in a topic of my choice?
(Registration or Authentication or Authorisation, )
 
How can I force WYSIWYG to save content as HTML (prevent conversion to TML?)
(Editing, Extension, TinyMCEPlugin, WysiwygPlugin)
 
Enabling word count in the editor
(Editing, Extension, TinyMCEPlugin)
Explains how to use the TINYMCEPLUGIN_ADDITIONAL_MCEPLUGINS setting to enable TinyMCE's wordcount plugin
How can I determine which plugin might be causing me an issue
(Debugging, )
Isolating performance or rendering issues to a plugin can be difficult. Here is one approach.
How do I solve email issues
(Configure, Debugging, Email and Notifications, Errors, Operating system, Registration or Authentication or Authorisation, )
Hints and tips for configuring and debugging email issues.
How do I prevent Foswiki from automatically linking WikiWords?
(Editing, Usage, )
 
Why can't I get a stack trace from Foswiki after a failure when using FastCGI?
(Accelerators or modperl or FastCGI, Debugging, Development, FastCGIEngineContrib)
Explains why CGI::Carp's fatalsToBrowser fails to emit a stack-trace on FastCGI Foswikis
How do I enter file paths into a link? (Both local and UNC Names)
(Browser, Editing, Security, Usage, )
Information on how to enter and use file system paths in html links.
Search doesn't find words written in my language (Eg. Chinese), help!
(Configure, Foswiki, Installation, Internationalisation or Localisation, )
Lists some troubleshooting steps for working with non-western languages in Foswiki.
Can I hide the System web
(Configure, Installation, Registration or Authentication or Authorisation, Security, )
Information on hiding the System web
programatically Rename a Topic
(Help and Support, )
 
Limitations in support for Dates
(Internationalisation or Localisation, )
Dates between 1901 and 1951 don't work correctly
What does "Max recursive depth reached" in the error log mean?
(Debugging, )
Explaining recursive macros
Can I use the Xxx editor with Foswiki?
(Editing, Extension, User interface, )
 
How do I debug calls to third-party programs?
(Debugging, Other..., )
How to debug problems that appear to come from programs that Foswiki calls
How to make wiki text editing ("raw edit") default
(Editing, )
How to make Raw Edit default
How can I secure my Foswiki against hostile attacks?
(Security, Web server, )
How can I secure my Foswiki against hostile attacks?
[[Support.FaqTemplate][]]
(, )
 
(obsolete) How do I safely upgrade a customized Foswiki installation
(Upgrading, )
 
How does Foswiki find Perl libraries? And how do I change that?
(Setup, )
Foswiki uses a number of external Perl libraries. This is how it finds them, and what you can do to change that.
How do I create a Table of Contents?
(Editing, Usage, )
 
Installation on Redhat
(Installation, )
Installation on Redhat Linux distributions
How do I find topics not listed on a topic?
(Search, )
Stub topic template to create Frequently Asked Question topics with
How do I use WYSIWYG with NTLM?
(Editing, Extension, Registration or Authentication or Authorisation, )
Tips for using WYSIWYG with NTLM
What is a "structured wiki", and what does it mean in Foswiki?
(Data forms, Foswiki, )
A basic overview of structured data and it may be used in Foswiki
Number of topics: 79

A few additional notes:
  • If it was installed, I would use FlexFormPlugin to grab the form values and render the input elements dynamically.
  • I didn't bother coding for the textboxlist form elements. FlexFormPlugin would take care of that, or else once could manually create those elements but would involve invoking the jquery plugins and I didn't want to bother with that for this example.
  • I used QuerySearch since DBCachePlugin isn't installed. The syntax is similar but not exactly the same.
  • The key part of how this work is 1) putting the url params into the search form element value attributes (so they are redisplayed in the form), and 2) putting a series of IfStatements in the search string attribute to include those extra clauses only if the asssociated VarURLPARAM is defined.
  • I got a little carried away with providing extra feedback in the search results regarding Subject and Extensions. That's what all the nested IfStatements in the "format" attribute are about. Hope they don't distract from the basic concept being illustrated...
  • Now if you wanted the 1st search form to remain as it was, but add additional form elements you could either:
    • Hide the secondary form elements until the "showrpt" urlparam is defined (similar to how I hid the search results above until there was something to show).
    • Add a separate secondary search form and results and use VarQUERYPARAMS to pass the set of search parameters from the initial search to the secondary search.
  • I'll mention one other "trick" that I some times use for taking the results of one search and then slicing and dicing it in different ways in a page without having to repeat the initial search each time. This achieves something similar to FormQueryPlugin's 'result sets' and involves using VarINCLUDE to define a parameter that is a hash of the initial search results. Rather than trying to illustrate this technique here, I'd point you to SupportQuestions where I used this technique. Take a look at the raw code and you'll see an INCLUDE near the top of the page that looks like this:
    %INCLUDE{"%WEB%.%TOPIC%" 
         section="subjects" 
         QUESTIONLIST="%INCLUDE{"%WEB%.%TOPIC%" section="grabQuestions"}%"
    }%
    Because the QUESTIONLIST is defined by an include (rather than the search itself), the results are passed along as a static string that can be processed further.

Hope this gives you some ideas for different ways to approach your problem. -- LynnwoodBrown - 18 Apr 2015

QuestionForm edit

Subject Topic Markup Language and applications
Extension DBCachePlugin
Version Foswiki 1.1.9
Status More info required
Related Topics
Topic attachments
I Attachment Action Size Date Who Comment
01_OriginalSearch_1.1.4.pngpng 01_OriginalSearch_1.1.4.png manage 74 K 17 Apr 2015 - 16:40 JohnRoberts Form query search as implemented on a 1.1.4 foswiki.
02_RefinedSearch_1.1.4.pngpng 02_RefinedSearch_1.1.4.png manage 41 K 17 Apr 2015 - 16:41 JohnRoberts Refined form query search on 1.1.4. Query is fed back to itself with further search terms.
03_OriginalSearch_1.1.9.pngpng 03_OriginalSearch_1.1.9.png manage 17 K 17 Apr 2015 - 16:43 JohnRoberts Form query search on foswiki 1.1.9. Error seems to be more an issue of showquery not expanding variables, because the number of rows indicates that some results were found.
04_RefinedSearch_1.1.9.pngpng 04_RefinedSearch_1.1.9.png manage 102 K 17 Apr 2015 - 16:44 JohnRoberts Refining the search on a foswiki 1.1.9 generates errors.
Topic revision: r4 - 18 Apr 2015, LynnwoodBrown - This page was cached on 20 Aug 2018 - 22:30.

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