Feedback on FormQueryPlugin

Summary

Three sentence summary of what this plugin does:

1. Like %SEARCH allows you to list topics which match a particular search criteria (e.g. where a particular topic form field is set to a particular value), FormQueryPlugin allows you to list only those rows of TWiki tables which match a particular search criteria.
2. FormQueryPlugin is a kind of cross between %SEARCH and a SQL select statement and (for those experienced with SQL) can be thought of as SELECT column1, column2, etc FROM table_type WHERE column2 > 5 AND column2 < 10.
3. So the 'search granularity' for FormQueryPlugin is finer than that of %SEARCH because whereas %SEARCH only lists topic names, FormQueryPlugin lists rows (in tables) in topics.

See the FormQueryPluginTutorial for examples of usage and more detailed explanations.

Open issues

While the documentation discusses using square brackets [...] to access array members, the parsing code only recognizes colon for accessing either map fields or array members. (The code for get supports the bracket notation, but not the parser in Search.pm. So at this time you need to write

%FORMQUERY{name="q2" search="TaskTable.0.Timestamp=~'2004'"}%

not

%FORMQUERY{name="q2" search="TaskTable[0].Timestamp=~'2004'"}%

-- ThomasWeigert - 01 Jun 2005

When using double quotes (") or carriage returns in a form field, the special rendering of these characters for storage in the text file (" and ) are not cleaned up when you "get" a field. So when I use FormQueryPlugin to display some field data (e.g. my "special" text) I don't get the quotes (e.g. my "special" text). Can this be corrected in the plugin, or do I need to check for these strings in my own code?

-- PeterWeatherdon - 16 Mar 2005

Feature wishlist

Question about %TABLEFORMAT:

If I choose to render the table using TWiki markup (e.g. header="| Topic | Summary |") then why doesn't the TablePlugin format the newly generated TWiki markup table as expected? Instead TablePlugin doesn't appear to have any influence whatsoever.

-- SimonHardyFrancis - 11 Feb 2004

I don't know. Some unexplained interaction with TablePlugin? Evaluation order, perhaps?

-- CrawfordCurrie - 11 Feb 2004

And here is my wishlist for the plugin:

• SET command should not be delimited by end of line so you can assign longer values
• It would be nice if you could include more Twiki commands within the SET value. I was able to use SEARCH but some searches were more than 1 line, so I had to kludge. Had no luck with spreadsheet GET command or CALLMACRO (by design as SET is evaluated first). Is there a defined rule as to what syntax can be successfuly assigned to a variable?
• It would be nice to be able to nest CALLMACRO commands.

-- JeffEaston - 25 Jun 2004

After some more experimenting I have a wishlist of features:

• Make parser more resilient and support escaped double quote characters. It was quite a challenge to replace all the quotes in a conventional search by single quotes.
• Allow multiline header in format. Currently one cannot build dynamic queries in the style of TWikiInstallation.
• Tack the footer (or a table row following a %SHOWQUERY% immediately after the result so that one could use SpreadSheetPlugin to perform some math on the resultant table.
• Limit the cache generated to the topics that match a given regular expression, as typically we do not need (or want) all the topics in a web in the cache.
• But the most important is: figure out how to apply the SpreadSheetPlugin to results of a query, either a resultant flattened list, or a generated table.

The latter would be a real killer ap. The SpreadSheetPlugin has a huge number of functions which otherwise one would have to reimplement in FormQueryPlugin. I am wondering whether we could have a transformation %QUERYASLIST{query=Aquery}% or something like that which would generate the result of the query as something that SpreadSheetPlugin can operate on, so that you can write

%CALC{"$SUM( %QUERYASLIST{query=Aquery}% )"}%

or something like that...

If we could just use these two plugins together just as we can use the SpreadSheetPlugin together with the core search mechanism, we would have a real winner.

-- ThomasWeigert - 25 May 2005

Please keep in mind that by the FQP the searching for attachments could be made much more comfortable. That could be another killer application of this great plugin.

-- FranzJosefSilli - 31 May 2005

Franz, can you describe what you find difficult with searching for attachments... I might take a look.

-- ThomasWeigert - 31 May 2005

FQP does already basically solve my concerns about SearchForAttachments resp. SearchingForAttachments. The FormQueryPluginTutorial is quite helpful either but I am imaging a searchable (resp. filterable) replacement (resp. addendum) to the attachment table at the bottom of topics. The one reason it doesn't work yet is the inconsistency of the date format provided back by the FQP. I haven't been able to use the SpreadSheetPlugin to fill the Date column properly with Crawfords latest release (didn't try yours yet). This would make it possible to get multiple attachment tables on one topic or use a filter technique like that in TWikiInstallation to search for a list of attachments satisfying some criteria.

Another concern I have with FQP: it does (did) not support formatting by the TablePlugin cause it replies (replied) HTML-tables instead of TML-tables so that FQP-results always somehow stick out.

-- FranzJosefSilli - 01 Jun 2005

Is there a way to specify the web?

-- N.N.

I discovered the following behavior (I am not quite sure why this is, though): You cannot reuse the same query name several times.

E.g., if you search

%FORMQUERY{name=Aquery search="..."}%
%SHOWQUERY{query=Aquery ...}%
%FORMQUERY{name=Aquery search="..."}%
%SHOWQUERY{query=Aquery ...}%

you will find that the query does not work correctly.

This is due to that the plugin evaluates all its tags in order, i.e., first it does all the searches, then it does all the formats, etc. Consequentially, only the last query is kept, if one reuses the name.

-- ThomasWeigert - 26 May 2005

Development discussion for FormQueryPlugin

Usage questions

Attachments

How to render the date of found attachments in human readable fashion? %CALC{"$FORMATGMTIME($date, $day $mon $year)"}% does only show todays's date

• Answer: use the following (requires modifications by ThomasWeigert) -- ThomasWeigert

%SHOWQUERY{query="big" format="|$_up.web.$_up.name|$name|$percntCALC{$dollarFORMATGMTIME($date, $dollarday $dollarmon $dollaryear)}$percnt|"}%

How to render the file size as in the attachment table, with K instead of bytes? I can't use SpreadSheetPlugin inside the format it seems. Earlier comment: change the loading order of plugins. New question: what should the order be?

• Answer: use the following (requires modifications by ThomasWeigert) -- ThomasWeigert

%SHOWQUERY{query="big" format="|$_up.web.$_up.name|$name|  $percntCALC{$dollarROUND($dollarEVAL($size / 1024), 1)k}$percnt|"}%

I want to have a list of attachments, and next to each file link the topic's (where the attachment is in) name, which is a form field. So the question: how to access form fields from a topic when I only have a list of attachments? Noting that there's no way to do an INTERSECTION at present.

• Answer: From the attachment, use _up to navigate to the topic, and then you can get to the form easily... But for what you describe you want to achieve, you can just follow the above examples... -- ThomasWeigert

Exclude topics

Is there an equivalent to the exclude= function of search? My query is returning the template topic.

• I would add "AND topic!=templateName" to the search parameter -- PeterWeatherdon - 05 Jan 2005

Error on empty table cells

Annoying problem: Let's imagine I'm extracting twiki tables and in one particular column I have integers or an empty string (in case no integer value is available). Problem is that when FormQueryPlugin renders those rows containing an empty string (instead of an integer) then instead of rendering an empty string it renders 'Undefined column name '. Is this a bug or a feature?

It's a feature. The plugin has no way to know you intended empty string to be a valid entry, and in general it isn't, so you get the whine. -- CrawfordCurrie

-- SimonHardyFrancis - 16 Feb 2004

Query tables without %EDITTABLE% decoration

There are instances where the plugin needs to query tables created by external applications. In this case, the topics SHOULD NOT be edited, so needing to placing an edittable carrys unwanted semantics.

-- MartinCleaver - 31 Dec 2004

Martin, if you can think of a way to specify a UID for such tables such that they can get an entry in the topic hash, go for it. I coded this functionality expressley so that such extensions would be easy.

And the main value of the FQP is far more to do with high performance searching and structured queries than simply searching embedded tables.

-- CrawfordCurrie - 07 Jan 2005

Use of %URLPARAM%, SpreadSheetPlugin

I think the problem with the date rendering - see table above - is the order of plugins. I'm having all sorts of such similar problems trying to get URLPARAMs passed to FQP, and using CALCs as well. It seems to me to be a plugin ordering problem. I haven't sussed out which order I need things in, and whether changing it for this purpose will break other bits of the twiki I use at work. Sometimes it seems I need some of the plugins to run twice; once before FQP on its input; and then once after on its output. (Maybe I also don't fully understand how FQP can be driven, and thus eliminate the need for this... I'm trying to do a form/selectable list of values to search across Form data.)

-- ScottClaridge - 16 Mar 2005

Yes, you are probably right, Scott. The only order dependency I was aware of is that MacrosPlugin has to be first in the list, but there may be other issues. Yes, there are cases where interaction with other plugins suggests a need to render twice. The few times I've encountered this I've just lived with it. Though I think from reading the Plugins.pm code, you should be able to list a plugin twice in INSTALLEDPLUGINS.

You don't say which plugins you think are giving the problem?

-- CrawfordCurrie - 17 Mar 2005

Re my ordering problem... I basically want to set up a search/filter page based around topic TWikiForms. A little bit user friendly and extensive (I guess mostly having the ability to select multiple values for fields from lists without having to write regexs) than Plugins/GenerateSearchPlugin and similar approaches. FormQueryPlugin seemed much better than trying to get SEARCH to work in this manner. (I hope that this doesn't replicate anything else - I've browsed/searched extensively to look for similar solutions.)

Anyway, the crux seems to be getting the URLPARAM into the plugin. First off I couldn't drive (didn't see) the multiple or default options, but even after working that out it never seemed to work. And I spent some time on it. Putting in "absolute", hand crafted values worked, so I know the FQP is working OK.

My efforts came down to something like:

%FORMQUERY{name="classedtopics" search="form='TopicMetadata'"}%

%FORMQUERY{name="TT1" query="classedtopics" search="TopicType=~'%URLPARAM{"topictype" multiple="on" separator="|" encode="entity" default=".*"}%'"}%
%FORMQUERY{name="SA1" query="TT1" search="SubjectArea=~'%URLPARAM{"subjectarea" multiple="on" separator="|" encode="entity" default=".*"}%'"}%

Topics of %CALC{"$IF($LENGTH(%URLPARAM{"topictype" multiple="on" separator="|" encode="entity"}%)==0, any TopicType, _%URLPARAM{"topictype" multiple="on" separator=", " encode="entity"}%_ TopicTypes )"}%, with %CALC{"$IF($LENGTH(%URLPARAM{"subjectarea" multiple="on" separator="|" encode="entity"}%)==0, any SubjectArea, _%URLPARAM{"subjectarea" multiple="on" separator=", " encode="entity"}%_ SubjectArea )"}%:

%SHOWQUERY{query="SA1" sort="name" format="   * $topic - $TopicSummary <em>$_up.info.version $_up.info.date</em>"}%

My current plugin order is =DefaultPlugin, SpreadSheetPlugin, LdapPlugin, MacrosPlugin, FormQueryPlugin, ActionTrackerPlugin, CalendarPlugin, CommentPlugin, EditTablePlugin, GaugePlugin, InterwikiPlugin, ProjectPlannerPlugin, RenderListPlugin, SlideShowPlugin, SmiliesPlugin, TablePlugin =

Rendering the dates also become a problem, and is noted above elsewhere. (I see that the two items at the top of the Open Questions table are in fact the same issue.)

-- ScottClaridge - 22 Mar 2005

Scott, I have used %URLPARAM without any problems. Look at the example below for inspiration. This sets up an interactive query in the style of TWikiInstallation:

<form action="%SCRIPTURLPATH%/view%SCRIPTSUFFIX%/%WEB%/%TOPIC%"> 
%FORMQUERY{name="q1" search="name='ComponentEntry.*' AND Productlineownership='Mesh' AND Classification='%URLPARAM{"qClass"}%' AND Subclassification=~'%URLPARAM{"qSub"}%' AND AssetDeliverableName=~'%URLPARAM{"qAss"}%' AND Componentname=~'%URLPARAM{"qComp"}%' AND Status='%URLPARAM{"qStatus"}%' AND DeterminedtobeReusable='%URLPARAM{"qReuse"}%'"}%

|*View, edit:*|*Classification*|*Sub-classification*|*Asset/Deliverable*|*Component*|*KLOC*|*Status*|*Reusable*|
|*<input type="submit" value="Filter" />*|*<select name="qClass" size="1"> <option>%URLPARAM{"qClass"}%</option> <option></option> <option>Devices</option> <option>Networks</option> <option>Solutions</option> <option>Tools</option> </select>*|*<input type="text" name="qSub" value="%URLPARAM{"qSub"}%" size="20" />*|*<input type="text" name="qAss" value="%URLPARAM{"qAss"}%" size="20" />*|*<input type="text" name="qComp" value="%URLPARAM{"qComp"}%" size="30" />*|*&nbsp;*|*<select name="qStatus" size="1"> <option>%URLPARAM{"qStatus"}%</option> <option></option> <option>In Process</option> <option>Active</option> <option>Archived</option> <option>Obsolete</option> </select>*|*<select name="qReuse" size="1"> <option>%URLPARAM{"qReuse"}%</option> <option></option> <option>Yes</option> <option>No</option> </select>*|
%SHOWQUERY{query="q1" format="| [[$web.$topic][<img src='%PUBURLPATH%/%TWIKIWEB%/TWikiDocGraphics/viewtopic.gif' border='0' alt='View entry' />]] [[%SCRIPTURL%/edit%SCRIPTSUFFIX%/$web/$topic?t=%GMTIME{"$hour$min$sec"}%][<img src='%PUBURLPATH%/%TWIKIWEB%/TWikiDocGraphics/edittopic.gif' border='0' alt='Edit entry' />]] | $Classification | $Subclassification | $AssetDeliverableName | $Componentname | $KLOC | $Status | $DeterminedtobeReusable |" }%| ~Total: %CALC{"$ROW(-3)"}% |  |  |   |    | ~Total: %CALC{"$SUM(R3:C$COLUMN()..R$ROW(-1):C$COLUMN())"}%  |   |   |
</form>

-- ThomasWeigert - 25 May 2005

%SUMFIELD

Crawford, I was wondering regarding the feature SUMFIELD, is this one deprecated?, as I mentioned the FormQueryPlugin works great with the new Sep 2004 Twiki version. However the SUMFIELD is not working any longer (it works with your example of attachements though). If I do a query on an specific attribute (for instance the time in days spent in an activity) and I want to obtain a sum (without having to show the full query), the SUMFIELD allowed to do so by doing SUMFIELD{query=myquery attribute=timespentdays}, however now it returns 0. I have looked into the code and somehow the result of sumQuery(), is not coming correct when using attributes defined in a form. Do you know what could be the problem?, or is there another way I could do this? Thanks a lot for the feedback it would really help us since we have spent a good time upgrading all our Twiki DB and this is the only issue we have opened. Thanks again.

-- GustavoAdolfoLopez - 29 Mar 2005

Gustavo, I have just built a query involving %SUMFIELD% and I can attest that it does work. You have to be very careful, though, what you are summing over: you do need to keep in mind the way how the web is represented in the data base.

Take this example: You want to sum up all the "size" contained in topics with a from named CodeForm. So first you need to write a query to find you all the topics that have the CodeForm, as in

%FORMQUERY{name=qA search="form = 'CodeForm'"}%

If you now try to sum up the size fields you will find that the result is 0. Why? Because what you are looking for is the fields of the topic, which are not there. You need to look into the form itself. To do so, extract the form from the found topics:

%FORMQUERY{name=qB search=qA extract="CodeForm"}%

Now you can sum the fields:

%SUMFIELDS{query=qB field="size"}%

Note that you could, of course, combine the extract into the first query qA. Note further that with my patch below you could write extract="$form" rather than use the name of the form, in case you don't care what form you take the field from.

Note also that writing

%SUMFIELD{query=qA field="Codeform.size" }%

does not work, as the sum function is only implemented for arrays, and Codeform expands into a Map. However, if you apply the following patch to Contrib/Array.pm, subroutine sum, and replace

   $sum += $fieldval->sum( $subfields );

by

   $sum += $fieldval->get( $subfields, undef );

the latter form also works, without requiring the extract. However, I am not clear on what it might break (as I cannot quite imagin what situations would return an array as a subfield (probably subfield searches, but I have not gotten my head around that yet).

-- ThomasWeigert - 25 May 2005

Extensions and enhancements

Colourmap enhancements

I also have something for the wish list: What about allowing $2, $3, etc in the HighLight page? This way one can not only high light but also reformat. Or is there already an alternative way to reformat fields?

-- SimonHardyFrancis - 10 Feb 2004

For those who would like even more power in the HighlightMap page then try out this patch for ColourMap.pm. It allows the right-hand string to contain $2, $3, etc in addition to the standard $1.

/lib/TWiki/Plugins/FormQueryPlugin # diff ColourMap.pm ColourMap.pm.orig
39,47c39,41
<       if ( $test =~ m/^($e)$/ ) {
<         my $c = $entry->{colour};
<         my @matches;
<         foreach ( 1..$#+ ) {
<           $matches[$_] = substr($test, $-[$_], $+[$_] - $-[$_]);
<         }
<         foreach my $index ( 1..$#matches ) {
<           $c =~ s/\$$index/$matches[$index]/g;
<         }
---
>       if ( $test =~ m/^$e$/ ) {
>       my $c = $entry->{colour};
>       $c =~ s/\$1/$test/o;

Example of usage:

Let's image that $topic is MyTable20040216 and rather than colouring it then we want to reformat it into a more compact link. The following HighlightMap line will convert MyTable20040216 into a link shown as 16-02-2004:

   * /MyTable(\d{4})(\d{2})(\d{2}).*/ = [[MyTable$2$3$4][$4-$3-$2]]

Provide this in the form of a patch and I'll put it in. -- CrawfordCurrie

-- SimonHardyFrancis - 16 Feb 2004

%MATCHCOUNT%

I was trying to use the FormQueryPlugin to query a set of forms, and summarise the number of pages with a specific form, and certain attributes (e.g. count all of the pages whose form is TestForm, and whose Colour field is Blue).

I couldn't quite seem to get what I was after, so added a %MATCHCOUNT macro to the Plugin, that returns the number of matches for a specified query.

For example, if TestForm is defined as follows:

Name:	Type:	Size:	Values:	Tooltip message:
Colour	select	1	Red, Blue, Green	Choose a colour

Then I can summarise the status of the forms as follows:

%FORMQUERY{name=AllQuery search="form='TestForm'"}%
%FORMQUERY{name=BlueQuery query=AllQuery search="Colour='Blue'"}%
%FORMQUERY{name=RedQuery query=AllQuery search="Colour='Red'"}%

| Blue  | %MATCHCOUNT{ query=BlueQuery }% |
| Red   | %MATCHCOUNT{ query=RedQuery }% |
| Total | %MATCHCOUNT{ query=AllQuery }% |

The code changes for this are as follows:

Update sub commonTagsHandler in FormPluginQuery.pm to include the new macro:

sub commonTagsHandler {
  ### my ( $text, $topic, $web ) = @_;   # do not uncomment, use $_[0], $_[1]... instead

  return unless ( $_[0] =~ m/%(FQPDEBUG|FORMQUERY|WORKDAYS|SUMFIELD|ARITH|TABLEFORMAT|SHOWQUERY|MATCHCOUNT|TOPICCREATOR|PROGRESS)/o );

  return unless ( _lazyInit() );

  my $text = "";

  $_[0] =~
   s/%FQPDEBUG{(.*?)}%/&_handleFQPInfo($1)/geo;
  $_[0] =~
   s/%(FORMQUERY){(.+?)}%/&_handleFormQuery($1,$2)/geo;
  $_[0] =~
   s/%(WORKDAYS){(.+?)}%/&_handleWorkingDays($1, $2,$_[2],$_[1])/geo;
  $_[0] =~
   s/%(SUMFIELD){(.+?)}%/&_handleSumQuery($1,$2)/geo;
  $_[0] =~
   s/%(ARITH){\"(.+?)\"}%/&_handleCalc($2)/geo;
  $_[0] =~
   s/%(TABLEFORMAT){(.+?)}%/&_handleTableFormat($1,$2)/geo;
  $_[0] =~
   s/%(SHOWQUERY){(.+?)}%/&_handleShowQuery($1,$2)/geo;
  $_[0] =~
   s/%(TOPICCREATOR){(.+?)}%/&_handleTopicCreator($1,$2,$_[2],$_[1])/geo;
  $_[0] =~
   s/%(MATCHCOUNT){(.+?)}%/&_handleMatchCount($1,$2)/geo;

  $_[0] =~ s/%(PROGRESS){(.+?)}%/&_handleProgress($1,$2,$_[2],$_[1])/geo;
}

And add sub matchCount to FormQueryPlugin /WebDB.pm:

  # PUBLIC return the number of matches for the specified query. 
  sub matchCount {
    my ( $this, $macro, $params ) = @_;
    my $attrs = new TWiki::Contrib::Attrs( $params );

    my $name = $attrs->get( "query" );
    if ( !defined( $name )) {
      return moan( $macro, $params, "'query' not defined", 0 );
    }

    my $matches = $this->{_queries}{$name};
    if( defined( $matches ) ) {
      return $matches->size();
    }
    return 0;
  }    

Hope this is the right place to put a suggestion like this....

-- JeremyGold - 25 Apr 2005

If you want the %MATCHCOUNT% feature above to work correctly, you also need to add the following code into FormQueryPlugin.pm:

sub _handleMatchCount {
  return $db->matchCount( @_ );
}

%MATCHCOUNT% is a very useful enhancement...

-- ThomasWeigert - 27 May 2005

Querying other webs

Just to keep you informed....

I just finished an addition to allow querying other webs than the web the topic is in, as well as querying multiple webs. If querying multiple webs, all hits are collected together.

I have added $web to reference the web that a hit was found in.

-- ThomasWeigert - 09 Jun 2005

Interface to SpreadSheetPlugin

As I described above, I got hooked on FormQueryPlugin, but I was disturbed by the obvious overlap with %SEARCH%Plugins.SpreadSheetPlugin. So I wrote an integration component that allows SpreadSheetPlugin to be applied to the results of a FormQueryPlugin query:

%QUERYTOCALC

Used to transform the results of a query into a format which SpreadSheetPlugin can operate on. %QUERYTOCALC accepts all the same parameters as %TABLEFORMAT (except name), and also the following parameters:

Parameter	Description
query	Required, gives the name of the query to show
row_from	Optional, first row to display
row_count	Optional, number of rows to display

For example,

%QUERYTOCALC{ query=AFORMQUERY format=ATBLFMT }%

The query must be formatted as a table. This variable produces no output, but must precede any calculation on the resultant query.

%SHOWCALC

Operates on the result of a query represented as a table, using a previous %QUERYTOCALC. Accepts a single, unnamed parameter:

Parameter	Description
	Required; the calculation to perform

The parameter is text as required by the $CALC statement (see SpreadSheetPlugins); all operations supported by %CALC are supported by %SHOWCALC. This command behaves identical to %CALC when used following a table; it operates on the implicit table constructed by a preceding %QUERYTOCALC statement. Arbitrary many %SHOWCALC can be performed; all apply to the preceeding implicit table.

Example:

%SHOWCALC{"$ROW()"}%

returns the number of matches of a query that had been formatted as an implicit table.

This enhancement is attached below. (The reason for the two step process is that I wanted to be able to use %CALC% commands unchanged, without having to add escapes, etc.) The update also includes

• the %MATCHCOUNT% update (albeit that is now redundant as you could just use the %SHOWCALC% as in the example above)
• a MOAN preference setting which determines whether the query "moans" upon failure
• the ability to format tables in TWiki ML rather than always translating to HTML format.

Note further that I have replaced the attribute parser by the parser from Dakar, to be able to get the precise input format of SpreadSheetPlugin. Please be aware that the consequences are that you have to quote arguments as for other TWikiVariables as this parser is less forgiving (but handles escaped quotes which is important...).

-- ThomasWeigert - 28 May 2005

Remove non-essential features

Just to keep you informed...

I have started stripping out non-essential features of this plugin such as the support for topic creation or highlighting of text in the formatted output.

I will try to make this plugin as close as possible to the standard TWiki %SEARCH and the SpreadSheetPlugin. In particular, I'd like the input formats to be as compatible as feasible. I don't like different date formats, etc., to be used in different parts of TWiki....

-- ThomasWeigert - 31 May 2005

What does stripping out mean in context with topic creation and text highlighting? Will there emerge additional XxxxContrib code? Dynamic topic creation is one of the key features lots of TWikiApplications would benefit from.

-- FranzJosefSilli - 31 May 2005

Topic creation and text highlighting are orthogonal to searching the web. These should be pushed into different plugins or contribs, so that they become usable outside of the FormQueryPlugin. I was not planning to do that at this point...

-- ThomasWeigert - 31 May 2005

Discussion

Discussions of solved issues factored out into FormQueryPluginOldIssues by ThomasWeigert, 01 Jun 2005

I am testing in my Test web, and have purposely created a single page called Test.DeleteMe which contains some info on which I am going to run queries. It has a form called BugForm, and its TopicClassification field is set to BugReport.

On my page Test.TestPage1 I have the following:

%FORMQUERY{name=AQuery search="TopicClassification='BugReport'"}%
%TABLEFORMAT{name=TBLFMT format="$topic..."}%
%SHOWQUERY{query=AQuery format=TBLFMT }%

The first two lines seem to work fine (at least they produce no visible errors, but clearly no results either!). It's only when I include the SHOWQUERY line when I get the 'software error':

Can't locate object method "get" via package "DeleteMe" (perhaps you forgot to
load "DeleteMe"?) at ../lib/TWiki/Plugins/FormQueryPlugin/Map.pm line 63.

Now, note that DeleteMe is the only page on the Test web which should be returned by the FORMQUERY query.

-- RossC - 16 Jul 2003

I suspect you're a victim of some rather cavalier subfield detection code. If you want to refer to a subfield in a form (eg an embedded table) you use a "." after the field name to get the subfield. So format="$topic..." tells it to display the subfield ".." of the field "$topic" (since it's recursive you are actually asking for the field value of $topic.blank.blank.blank). Since "$topic" is a text field, and has no such subfield, then the get fails. If you change it to "$topic ..." (or "$topic&ellipsis;" is such an HTML entity exists) then it should work. Methinks some better error detection would be of value here!

-- CrawfordCurrie - 17 Jul 2003

Crawford, thank you - yes, that fixed it. "$topic(space)..." works fine. Now on to do some more real testing

-- RossC - 17 Jul 2003

Crawford, interesting Plugin.

A minor detail: I added the SHORTDESCRIPTION setting and your signature to the Plugins topic so that the PluginPackage index table shows that. Best to include that in the next release.

Also, it looks like you are using your own parsing for %VARIABLES{...}%. To reduce user confusion may I suggest to document the parameter values with "proper" quotes, e.g.:

%FORMQUERY{name="AQuery" search="TopicClassification='BugReport'"}%
%TABLEFORMAT{name="TBLFMT" format="$topic..."}%
%SHOWQUERY{query="AQuery" format=TBLFMT }%

instead of:

%FORMQUERY{name=AQuery search="TopicClassification='BugReport'"}%
%TABLEFORMAT{name=TBLFMT format="$topic..."}%
%SHOWQUERY{query=AQuery format=TBLFMT }%

FYI, SpreadSheetPlugin formulas can be used outside of tables.

-- PeterThoeny - 19 Jun 2003

"Interesting" as in "may you live in interesting times"? Well, it addresses a need.

I'll fix the SHORTDESCRIPTION; thanks for pointing it out.

Yes, I use my own parsing; it's the same parser as used by the ActionTrackerPlugin. It's OO, and rather more adaptable than the built-in parser.

I tried using %CALC, but because it uses a straight 'eval' my users found it very hard to debug when things went wrong. The ARITH tag uses a proper arithmetic expression parser and evaluator. But I'll have a look, and possibly retire it.

-- CrawfordCurrie - 20 Jul 2003

Would there be a benefit in moving your parser code into the Core?

-- MartinCleaver - 20 Jul 2003

Better not since the parser does not support the (legacy) non-quoted nameless parameter (e.g. %INCLUDE{OtherTopic}%) and is less strict in the rule (making a possible conversion to XML more difficult)

-- PeterThoeny - 20 Jul 2003

I'd love to see some examples of how this is used at Motorola. I'm still trying to wrap my head around the concept of using a wiki as a very flexible database that meshes presentation and data structures together.

-- BradDixon - 02 Oct 2003

Here's how we used it at Motorola:

We had a lot of requirements for a software product that had to be organised somehow. Because the product was mature, the requirements were mainly to do with incremental improvements to existing functions. Many of these changes spread across several "areas" of the product and traditional hierarchical decomposition just wouldn't work for us. So we created each requirement as a page with a standard form attached that could be completed to describe and classify the requirement. This page would then be hierarchically decomposed in some cases to create sub and sub-sub requirements. We would also capture test items and individual tasks required to implement the requirements in the same way.

We needed to collate all these requirements, to generate - for example - pages of prioritised lists of requirements and their related test items and tasks. To do this, one of our best engineers (HenningSpruth) wrote a plugin that was tailored to this application. Henning did a super job but the plugin was rather fragile, and because we were constantly learning, we often wanted to add new fields or collation methods. This meant that the maintenance load for the plugin was high. Enter the FormQueryPlugin, which basically just generalised Henning's work but allowed the programme managers and team leaders to do most of the maintenance, writing their own queries and such like.

As well as the basic database features, you will find plan and resource planning and tracking features features in there as well - for example, automatic man-day computation, progress-against-plan bars, etc.

Steve, I'm sorry about the munging of the searches; I assume this is on the Austin server, and it's Kaushik's pages that are getting munged? I just can't imagine what's going on there. My motivation to maintain the plugin is not presently strong (anything that doesn't earn money is taking a back shelf at the moment) but if you want to send me a canned example, I can have a look when I get a breathing space.

-- CrawfordCurrie - 09 Dec 2003

I would like to take this opportunity to say that this plugin is excellent (when one takes the time to get into it).

Performance with large tables: I've tested queries on a table with over 1000 rows and performance is reasonable (results appear within a few seconds on a cheap Linux server). However, in my case, the _FormQueryCache file is large at around 550 KB (using short column names). This could become a limitation if one considers using FormQueryPlugin with many pages with long tables. Looking at _FormQueryCache then perhaps it could be optimzed. How? If we have a column called MyColumn on PageX in a table with 1000 rows then the word MyColumn will be stored in _FormQueryCache 1000 times. After optimization then ideally it would be stored only once... thus saving potentially 100s KB and allowing the luxury of longer column names. As a work-around then I am currently using very short columns names

-- SimonHardyFrancis - 11 Feb 2004

Do the searchable tables need to be in WebForms? If so, how to rhyme the format of the WebForm with above (free form) example? Or can I just use any EDITTABLE table on a page?

-- ArthurClemens - 18 Feb 2004

I found FormQueryPlugin very difficult to understand. As it is a very complex and powerful application, it really needs more documentation, maybe even a tutorial. I would love to deploy your plugin, as it seems very encompassing. But to tell you the truth, I was scared away by the apparent complexity and concern about performance. Hopefully these are unwarranted, but some additional documentation certainly would help getting your plugin into wider use. I have also checked with the local people at your old shop, and was warned about the difficulties. So I ended up writing this little patch which can be used just like the standard search routine. I realize that asking for more documentation is quite a lot to ask... but...

-- ThomasWeigert - 17 Jun 2004

That's a familiar tune. And having revisited the plugin recently one I have some sympathy with! I'm in the process of rewriting the documentation (the first pass is already up at FormQueryPlugin) but the need to earn a living keeps getting in the way. BTW if your performance concerns come from my former colleagues, it's because their server and network is crap and TWiki performance is generally awful, not because of the FQP.

Part of the problem is that the FormQueryPlugin is actually several plugins rolled into one package. It could be split into:

• database query and report (FORMQUERY, TABLEFORMAT and SHOWQUERY)
  • overlaps with Thomas' meta field search
• general purpose notification from a cron (tasknotify)
  • Not aware of this being done anywhere else
• Parameterised includes (CALLMACRO)
  • not aware of this being done anywhere else (except OWiki, where it is syntactically different)
• Progress against plan bars (PROGRESS)
  • overlaps with GaugePlugin, but unlike GaugePlugin has no external dependencies (uses XHTML TABLEs)
• Automatic topic name derivation (TOPICCREATOR)
  • not aware of this being done anywhere else (derives new topic name from existing topic name according to rules)
• General purpose arithmetic (ARITH, SET)
  • ARITH overlaps with SpreadSheetPlugin, could be thrown away, but I think SET is unique

The reason these were all rolled together was that together they provide the necessary bits for a requirements management database system. By putting them together, the end user only has to install one plugin instead of six. By splitting them apart, then each unit would become significantly more understandable, and the individual bits could be cherry-picked.

-- CrawfordCurrie - 17 Jun 2004

First, let me apologize again for whining about the documentation. It is really just out of concern that your wonderful work goes unused and point solutions that duplicate your efforts are being introduced (as evidenced, e.g., by the extension I cobbled together).

Secondly, there is actually no basis regarding my concern about performance. I was merely wondering whether due to the apparent complexity of what the plugin accomplishes there might be performance implications. From your answer above I conclude that you are confident that there are not.

Now regarding your question... I think you have already answered your own question. If there are completely unrelated pieces of functionality in the plugin, then bunching them together

• forces the user to include all "sub-plugins" even if only one piece of functionality is wanted
• makes it much harder to maintain, enhance, and understand the plugin due to the larger volume of code.

I have to admit that I had not noticed this fact when reading the documentation. I would venture that it would be better to split the plugin into its pieces. This has the additional benefit that one can focus any documentation efforts on the parts that are most likely in high demand. E.g., so far most interest and comments were regarding the formquery part, as most of the difficulty in understanding are on this particular portion of the overall plugin. But by splitting the plugin, there may be users interested in the parameterized includes (remember the discussions raised by Michael on this topic), or other pieces....

-- ThomasWeigert - 19 Jun 2004

Also one of my filenames gets rendered as wikilink. So I would like to nop the filename, but how?

I found out there is put space around the variable in _expandField in TableFormat.pm. If you remove this all works well - see the simple patch.

• Well, the reason for the spaces was so that wikiwords in the expanded field got expanded. For simple fields this was right, but is clearly wrong as we gain experience with filling in field values. By removing the spaces you may see some wikiwords in table fields not being correctly rendered with the patch in place. However, that's much more correct, so I will patch the source for the next version. Thanks Arthur! -- CrawfordCurrie - 29 Sep 2004

The query then becomes:

%FORMQUERY{name=Allatts search="" extract="attachments"}%
%SHOWQUERY{query=Allatts header="| *File* | *Topic* | *Size* | *Date* |" format="| [[%PUBURL%/%WEB%/$_up.name/$name][$name]] |  $_up.name | $size | $date |"}%

Wow. Great plugin - thanks for the tutorial, it makes all the difference!

-- MartinCleaver - 30 Sep 2004

Simon's tutorial for querying InTopicTables moved to FormQueryPluginTutorial

-- MartinCleaver - 31 Dec 2004

Is there an equivalent of the $pattern syntax as used in the %SEARCH variable. I would like to extract a paragraph from the general topic text. -- PeterWeatherdon - 16 Dec 2004

Answered my own question. After discovering the HighlightMap feature, I discovered that this worked well on fields but not on $text. I modified the map function of ColourMap.pm so that a regular =~ operation is performed on the text passed in using $entry->{colour} as the replacement string and then using eval to do the actual substitution. Is this a reasonable fix?

-- PeterWeatherdon - 17 Dec 2004| |

It sounds ok, but overall performance, especially with large tables, is highly dependent on the performance of the HighlightMap code, so I would have to see comparative benchmarks before commenting further.

-- CrawfordCurrie - 07 Jan 2005

Just to let you all know I'm not ignoring your questions; I'll get to them as soon as I'm able.

-- CrawfordCurrie - 25 Apr 2005

I finally ran into a problem which a think cannot be solved using the builtin TWiki search mechanism. So I installed FormQueryPlugin in the hope this would help.

After some struggle to get everything working it indeed solved my problem. And not only that: I ran some benchmarks on complex queries over a large number of topics. The FormQueryPlugin is consistently twice as fast as the core search mechanism, on identical queries.

What a great improvement... and once you get the hang of it there are numerous ways of using this plugin.... I am hooked already... the one alternative approach which seems even better would be to store topics in a database and use the query mechanism of the database to do what FormQueryPlugin does.

Truth in advertising... to solve my problem I had to make a small modification to FormQueryPlugin: in Contrib/Map.pm, subroutine get, I added the following lines

    } elsif ( $key =~ m/^\$(.*)$/o ) {
        my $field = $this->get( $1, $root );
        return $this->{keys}{$field};

before the final else. This expands a field, such as $form and thus allows one to, for example, extract the form attached to the topic in a query. (I needed this as I had a number of topics with different forms, but I wanted to flatten these and sum up a particular field present in all the forms.)

-- ThomasWeigert - 25 May 2005

FYI... while working on some of the features above I have also addressed a number of bugs I ran into... for example, now a extract="xxx[?zzz]" does not return erroneous results... I have also enhanced the parser so as to not run into trouble with statements like format="$topic..." (see a bug report above). And TablePlugin can now format tabular output from FormQueryPlugin....

I am still trying to figure out how to publish these enhancements without stepping on Crawford's toes...

-- ThomasWeigert - 09 Jun 2005

Bug: TimSlidel 's idea is realized faulty... FormQueryPlugin::WebDB::readTopicLine eats the line following an edit table (and even if fixed to return that line, it would still treat it as part of text, even if it is a META line). This function, which overrides code in DBCachePlugin, must take care of proper handling of meta or somehow look ahead instead of consuming characters from the file which are not meant for it...

I fixed this in my version, but it relies on that there is always an empty line between text and the meta data that follows it. This is true today, but...

-- ThomasWeigert - 17 Jun 2005

Don't worry about stepping on my toes; the CVS repository is up to date with my local code, and you are free to check in to it. The only constraints I would put on that is that if you change functionality, rather than just fixing or enhancing things, you talk it over with me first (mail, IRC). I would also ask that you run the tests and make sure they all pass, and add new tests as appropriate.

-- CrawfordCurrie - 28 Jun 2005

Thanks, Crawford... here is what I have done so far:

• In DBCacheContrib I have made changes that hopefully are non-controversial. In detail I
  • made some fixes
  • replaced the parser with a table-driven parser in order to have more flexibility
  • support summing of subfields
  • handle subfield searches
  • allow expansion of fields on rhs of query expression
  • add caseinsensitivity as option
  • add web to cache for topics
  • integrate the Attribute parser from DakarRelease
  • interpret bare string as text=~'string' query
  • allow use of SpreadSheetPlugin in search expression
  • deleted the time-specific operations in favor of using SpreadSheetPlugin to convert dates to numeric format
• In FormQueryPlugin the situation is not as clear. By now, this is almost a different plugin with much functionality and code borrowed from your original work. My main goal was to get as close to %SEARCH% as possible to be able to replace any %SEARCH% by a form query. In detail, I
  • added support for searches over multiple webs
  • support %MATCHCOUNT% as suggested above
  • allow SpreadSheetPlugin computation to be applied to the result of a query as discussed above
  • made "moan" a preference rather than a per call option
  • removed %TOPICCREATOR%
  • removed %ARITHMETIC% (as you can use the SpreadSheetPlugin)
  • removed the color map feature
  • extended %FQPINFO% to support showing results of queries
  • integrate the Attribute parser from DakarRelease
  • add caseinsensitivity as option
  • handle one line at a time so that query memory can be reused in another query
  • added the special variables supported by FormattedSearch
  • changed some of the option names to be more consistent with TWikiSearch (e.g., row_count)
  • numerous fixes
  • more flexibility for using tables
  • add a %DOANDSHOWQUERY% tag
  • support embedding of %SEARCH% and %FORMQUERY% in the format option to allow searches and queries to be applied to the result of a query (a poor man's intersection)

I am still planning to change the handling of dates in DBCacheContrib to be more consistent with SpreadSheetPlugin. There is also the question of whether the combinators (OR, AND) should be made more consistent with the other TWiki search mechanisms.

My suggestion is to merge the changes to DBCacheContrib into the current version. With respect to the changes to FormQueryPlugin, we can either make two separate plugins, or decide how to combine them. Please advise on the strategy so I can package this up accordingly... you probably won't have much time for inspections...

I continue to run all your tests (that are applicable, after having updated the test database) and have added additional ones. I am planning to add tests for the new features.

-- ThomasWeigert - 28 Jun 2005

I attached my updates to this plugin below. Please be aware that this also needs the updated DBCacheContrib and AttrsContrib, both attached to DBCacheContribDev.

-- ThomasWeigert - 01 Jul 2005

Further updates to DBCacheContrib to support search expressions utilizing computations with SpreadSheetPlugin. As we can use the SpreadSheetPlugin to convert dates to numbers, removed the date-sprecific search operators to eliminate the dependency on ParseDate and ensure consistency with other date manipulations.

-- ThomasWeigert - 07 Jul 2005

Added support for tables defined using EditTablerowPlugin. Fixed test cases...

-- ThomasWeigert - 09 Jul 2005

I have installed the attached updates to this plugin on http://modeldrivenengineering.org, in case you want to experiment with it.

-- ThomasWeigert - 11 Jul 2005

This is a great plugin! I have a question though about using SpreadSheetPlugin in the search expression. Can I use a %CALC{$GET())}% as my search criteria on the RHS? (i.e. search="name='$percntCALC{$GET(var))}$percnt'") I've tried this but it doesn't seem to work. Could it be my Plugin order? My order is: DefaultPlugin, FormQueryPlugin, MacrosPlugin, SpreadSheetPlugin.

-- CharlesJohnson - 31 Aug 2005

Yes, plugin order is the most likely cause. try putting Spreadsheet before FormQuery. it should work, unless your CALC produces something that breaks the parser in the FQP.

-- CrawfordCurrie - 31 Aug 2005

Crawford, I've tried different combinations without success. If I move Spreadsheet ahead of Formquery I can't use CALC in the format= portion of the query/show. Could you give me the plugin order from a site where CALC can be used in both the search criteria and the format?

-- CharlesJohnson - 31 Aug 2005

You need to have the following order: DefaultPlugin, MacrosPlugin, FormQueryPlugin, SpreadSheetPlugin, the rest can be arbitrarily ordered.

-- ThomasWeigert - 31 Aug 2005

Thomas, I think I must be going nuts. I've set up everything as described, and still no output! For instance, I have a topic named ConfigItem1001 and have set the variable "var" with its name.

%CALC{"$SET(var, ConfigItem1001)"}%

I then construct a doandshow:

%DOANDSHOWQUERY{ "name='$percntCALC{\"$dollarGET(var)\"}$percnt'" format="$topic" }%

I get no output. If I just put ConfigItem1001 as the search criterion I get the expected output of course. Could I be escaping something incorrectly? I'm using the September2 release along with the dev versions of FormQuery and DBCache. All other functions I use are working fine.

-- CharlesJohnson - 01 Sep 2005

Charles, I see now that you are trying to use the result of the %CALC% in the search expression. I have never tried that, and am suspicious that it might not work. I have only used the %CALC%

• in the format statement, where the code carefully controls the evaluation
• as the search expression, where it specifically is evaluated.

It never occurred to me to use %CALC% in the way you describe, but it does make sense, of course. I just don't know how far to go here. Does the whole argument have to be a %CALC%? Or can this appear anywhere within the string? E.g., name='Web$percntCALC{\"$dollarGET(var)\"}$percnt'.

As a work around, you could try to construct the whole search expression in the calc statement (including the name="..." part).

-- ThomasWeigert - 02 Sep 2005

Thomas, I tried numerous workarounds, constructing a whole FORMQUERY with CALC and then trying a get in the format portion of the doandshow to see if it would parse, but to no avail.

To answer your question above, I was initially trying to do the get within the string like your example illustrates. It would add a ton of flexibility in my opinion as it would allow dynamic constructs of queries based on expressions built by %CALC%. What do you think? Is it possible?

-- CharlesJohnson - 02 Sep 2005

I have a strange issue with FormQueryPlugin and I'm unsure whether it is the plugin or TWiki itself. When I used nested %DOANDSHOWQUERY{}% I get an unintentional line feed at the fourth level of the nested search (i.e. $dollardollarpercntDOANDSHOWQUERY). What's strange is that I get it even if I escape it with a $nop. If I change the name of the function to something other than DOANDSHOWQUERY (DOANDSHOWQUERa for example) I don't get the linefeed. If I use any other function such as %CALC% for instance at this level I don't get the linefeed either. Any idea what could be causing this? It does not occur in nested searches less than four levels deep.

-- CharlesJohnson - 27 Sep 2005

Are there any plans to make FormQueryPlugin compatible with the Dakar release? I have a lot of applications built with it (it's just so fast and flexible :cool:). Any idea of if and when it will be?

-- CharlesJohnson - 03 Feb 2006

To the Plugin maintainer: If possible, please keep this Plugin compatible with Cairo and Dakar codebase. HandlingCairoDakarPluginDifferences has more.

-- PeterThoeny - 08 Feb 2006

So, no compatibility here.

Suggestions:

• Do not link to oneself (espcape top header link and other ones)
• Avoid "here" links labels. Instead of "WARNING: TWiki-4 only. If you want to use this extension with an earlier version of TWiki, please see here", you could write: "__NOTE:__ This version is TWiki 4.0 specific. Download the previous version if you want to use this extension with an earlier version of TWiki."
• Link to old version points to old topic version, but if you click on zip file you get the latest version. Better to link directly to older zip file
• Left & right cell align problem in Info table
• Standard "related topics" line missing

-- PeterThoeny - 28 Feb 2006

I saw the update for TWiki 4.0 for this plugin but what happened to all the neat features in the development version? I especially like the %DOANDSHOWQUERY% which I utilize quite a bit in some key applications.

-- CharlesJohnson - 06 Mar 2006

Charles, I also rely on these features in some of my apps, so I definitely plan on moving this plugin over to Dakar. However, I am really swamped right now, so there will be some delay...

-- ThomasWeigert - 21 Mar 2006

Charles, Thomas, I'm sure it was my fault the "new features" got zapped. I build and install from the subversion repository, so if those features weren't checked in (I guess the weren't) then they will get zapped.

-- CrawfordCurrie - 22 Mar 2006

Could anyone confirm the "Dakar" version works with TablePlugin? I didn't manage to get TablePlugin 's formatting/sorting with TWiki 4.01 release and FQP of 28 feb..

-- NikolayUgarov - 10 Aug 2006

A suggested update to the Plugin Installation Instructions: In recent TWiki versions the INSTALLEDPLUGINS variable is no longer supported. You can set the Plugins execution order using the configure interface ie. run http://.../twiki/bin/configure, select Plugins and add MacrosPlugin to {PluginsOrder} at the bottom.

-- KeithBurdis - 08 Sep 2006

Hi Thomas, any idea when you're going to put the %DOANDSHOWQUERY% functionality back in for interoperability with TWiki 4.0?

-- CharlesJohnson - 28 Sep 2006

I have this ported to TWiki4 already. I had hoped that we would have a merge of these plugins by now, but it appears that that may still take some time.

I will put my version on line in the meantime.

-- ThomasWeigert - 28 Sep 2006

Thanks Thomas. Did you post this elsewhere? I looked here and under FormQueryPlugin. Just to clarify, your version is compatible with TWiki4 and includes the %DOANDSHOWQUERY%?

-- CharlesJohnson - 02 Oct 2006

Charles, I have uploaded to YetAnotherFormQueryPlugin. You will also need YetAnotherDBCacheContrib. Sorry Crawford

I am still hopeful that we will merge these two plugins, but for the time being I'll make my version available separately.

-- ThomasWeigert - 03 Oct 2006

Thanks Thomas. At least now I can migrate to TWiki4

-- CharlesJohnson - 03 Oct 2006

FYI I am seriously considering obsoleting this plugin, and letting YetAnotherFormQueryPlugin take it's place (I wish Thomas had just uploaded changes here, but what the heck). Anyone have a problem with that?

-- CrawfordCurrie - 17 Nov 2006

Of course I would merge the changes into here. YetAnotherFormQueryPlugin is just a misbehaving child of your excellent plugin... I would have long stopped using TWiki for web-based applications were it not for your foresight in developing this app...

-- ThomasWeigert - 17 Nov 2006

-- JayWagner - 28 Nov 2006

I am new to the T-WiKi environment and am trying to get the FormQueryPlugin working. Based on the FormQueryPluginTutorial I've followed steps one and two and now have a populated functional table to query; however, I'm having trouble creating a page that displays the results I'm looking for. My formquery, tableformat and showquery syntax is as follows: %FORMQUERY{name=SURVAYQUERY search="Survay='HDL'"}%

%FORMQUERY{name=AREAQUERY query=SURVAYQUERY search="Area='Area 4'"}%

%TABLEFORMAT{ name=TBLFMT header="~ # ~ Survay ~ Area ~ Type ~ Month ~ Year ~ Number Received ~ Number Processed ~$n" format="~$#~$Survay~$Area~$Type~$Month~$Year~$Number Received~$Number Processed~" }%

%SHOWQUERY{ query=AREAQUERY format="TBLFMT" }%

I get the following error:

No values returned in FORMQUERY{name="SURVAYQUERY" search="Survay='HDL'"}

Query ' SURVAYQUERY ' not defined in FORMQUERY{name="AREAQUERY" query=" SURVAYQUERY " search="Area='Area 4'"}

Query 'AREAQUERY' returned no values in SHOWQUERY{format="TBLFMT" query="AREAQUERY"}

There is only a single table I would like to query and I have added the following web preferences:

o Set FQTABLES = TaskTable o FQP_ENABLE

If I had to guess I would say that I do not have the syntax for tableformat correct, but I'm not sure. If anyone has any suggestions as to what I might need to change, or if there is something right in front of my face that I'm missing because I've been looking at my screen for too long, I'd appreciate a head up. Thanks.

-- JayWagner - 28 Nov 2006

First please make sure that you have the spelling in the topics right; you might have used "survey" rather than "survay"...

Then, the table needs to be formatted using the include style; please verify that this is the case, and that TaskTable is defined.

To make sure you don't have any spacing issues in your table, try to query with the =~ operator.

Then FQP_ENABLE must be set to 1 in your preferences.

You could also make sure that your installation is working by running the unit tests for FQP in directory test/unit/FormQueryPlugin

-- ThomasWeigert - 29 Nov 2006

The plugin seems to be installed properly with no errors per the installed plugin page on the web. I have defined TaskTable (i.e. created a table using the EditTable plugin) and have included it in my search table (e.g. {include="TaskTable"}). Also, I have changed my web prefreences so that FQP_Enable is set to 1. I think I'm set on these items but it is still not working. Can you elaborate on potential spacing issues that could arise?

I've double checked my spelling (guess I had been staring too long) and have even changed my query parameter, but still no luck. Its almost as if the include style is not working and that the topic I created my search table under is no being identified as needing extraction (if that makes sense).

Another thought I had was regarding forms. Are forms needed for this plugin to work? I've seen many references to creating forms within the context of this plugin, but am under the impression that forms are used when you use the EditTableRow plugin, not the EditTable plugin. Thanks.

-- JayWagner - 29 Nov 2006

You do need to define the table schema in the task table topic...

-- ThomasWeigert - 30 Nov 2006

That's the first time I'm using that plugin I can't get sort to work on a 4.1.0 installation. How comes I'm getting some results but not sorted?

-- StephaneLenclud - 19 Jan 2007

http://develop.twiki.org/~twiki4/cgi-bin/view/Bugs/Item3525

-- StephaneLenclud - 29 Jan 2007

Jay - did you ever resolve your problem? I'm getting exactly the same symptoms. Thanks.

-- AlexHamilton - 16 Feb 2007

I updated to the latest version and my pages are all broken now. See http://immo.dreamhosters.com/bin/view/Immo/AdsQuery.

%FORMQUERY{name="AQUERY" search="Name='Nice.*'"}% :Plugin initialisation failed %TABLEFORMAT{format="| $Name | $Room |" header="| Name | Room |" name="AFORMAT" sort="Name"}% :Plugin initialisation failed %SHOWQUERY{format="AFORMAT" query="AQUERY"}% :Plugin initialisation failed

I had a quick look at the code but could not figure what was wrong. I found the error message though. I also update DBCacheContrib, DBCachePlugin and installed MacrosPlugin.

Later, I think I was missing:

• Set FROMQUERYPLUGIN_ENABLE = 1

-- StephaneLenclud - 26 Apr 2007

I am having a similar situation to the issue JayWagner posted about above. I have checked the settings on the FQP many times, and have also checked my queries and topics many times. I am able to get results from querying my forms, but cannot get results from querying my tables.

I have a topic named RiskRatingTableDefinition which defines my EDITTABLE.

%EDITTABLE{format="| text, 16 | "}% |" changerows="off" editbutton="hide" header="|*Rating*|" }% 

I then have a topic, RiskManagementFormTemplate, that includes that table with:

%EDITTABLE{include="RiskRatingTableDefinition"}%

I then have a third topic which queries the previous one (I use the literal of the topic name for ease of query execution).

%FORMQUERY{name="MyRisks2"  search="topic='RiskManagementFormTemplate'"}%
%SHOWQUERY{query="MyRisks2"  header="| *Risk* | *Rating* |" format="|$topic|$RiskRatingTableDefinition.0.Rating|" }% 

The only think that displays is the topic name, and not the Rating value.

Here's what's in my WebPreferences:

   * Set FORMQUERYPLUGIN_ENABLE = 1
   * Set FORMQUERYPLUGIN_MOAN = on
   * Set FORMQUERYPLUGIN_TABLES = all

I can't see where I'm going wrong... but nothing is coming up. I'm sure I'm missing something stupid, but I can't figure it out.

Thanks so much!

-- TriciaBall - 16 May 2007

We looked into this a bit further.... The field we were trying to grab is a calculated field using the SpreadSheetPlugin. When we try to grab static fields it works. It appears we cannot query tables with calculated values. Is this correct?

Thanks!

-- TriciaBall - 17 May 2007

Correct.

-- CrawfordCurrie - 18 May 2007

Using $createdate or $createwikiname in format returns a perl error:

%SHOWQUERY{format="BFORMAT" query="BQUERY"}% :Can't use string ("Release") as a HASH ref while "strict refs" in use at /srv/www/vhosts/twiki/lib/TWiki/Search.pm line 1075. at /srv/www/vhosts/twiki/lib/TWiki/Search.pm line 1075 TWiki::Search::_getRev1Info('Release', 'TestRelease000', 'wikiname') called at /srv/www/vhosts/twiki/lib/TWiki/Plugins/FormQueryPlugin/TableFormat.pm line 240 TWiki::Plugins::FormQueryPlugin::TableFormat::formatTable('TWiki::Plugins::FormQueryPlugin::TableFormat=HASH(0x95a9e5c)', 'TWiki::Contrib::DBCacheContrib::Array=HASH(0xac3fd34)', 'undef', 'undef', 'undef', 'undef', 'TestQuery', 'Release', 'sl', ...) called at /srv/www/vhosts/twiki/lib/TWiki/Plugins/FormQueryPlugin/WebDB.pm line 440 TWiki::Plugins::FormQueryPlugin::WebDB::showQuery('BQUERY', 'BFORMAT', 'TWiki::Attrs=HASH(0x97242d4)', 'TestQuery', 'Release', 'sl', 'TWiki') called at /srv/www/vhosts/twiki/lib/TWiki/Plugins/FormQueryPlugin.pm line 237 TWiki::Plugins::FormQueryPlugin::__ANON__() called at /srv/www/vhosts/twiki/lib/CPAN/lib//Error.pm line 379 eval {...} called at /srv/www/vhosts/twiki/lib/CPAN/lib//Error.pm line 371 Error::subs::try('CODE(0x94d7a80)', 'HASH(0x9502bf8)') called at /srv/www/vhosts/twiki/lib/TWiki/Plugins/FormQueryPlugin.pm line 244 TWiki::Plugins::FormQueryPlugin::_SHOWQUERY('TWiki=HASH(0x94ab0ec)', 'TWiki::Attrs=HASH(0x97242d4)', 'TestQuery', 'Release') called at /srv/www/vhosts/twiki/lib/TWiki/Func.pm line 1809 TWiki::Func::__ANON__('TWiki=HASH(0x94ab0ec)', 'TWiki::Attrs=HASH(0x97242d4)', 'TestQuery', 'Release') called at /srv/www/vhosts/twiki/lib/TWiki.pm line 2403 TWiki::_expandTagOnTopicRendering('TWiki=HASH(0x94ab0ec)', 'SHOWQUERY', ' query=BQUERY format=BFORMAT ', 'TestQuery', 'Release') called at /srv/www/vhosts/twiki/lib/TWiki.pm line 2323 TWiki::_processTags('TWiki=HASH(0x94ab0ec)', '\x{a}\x{a}%TABL...', 'CODE(0x8fa23f4)', 16, 'TestQuery', 'Release') called at /srv/www/vhosts/twiki/lib/TWiki.pm line 2249 TWiki::_expandAllTags('TWiki=HASH(0x94ab0ec)', 'SCALAR(0x8fa2afc)', 'TestQuery', 'Release') called at /srv/www/vhosts/twiki/lib/TWiki.pm line 2598 TWiki::handleCommonTags('TWiki=HASH(0x94ab0ec)', '\x{a}\x{a}%TABL...', 'Release', 'TestQuery') called at /srv/www/vhosts/twiki/lib/TWiki/UI/View.pm line 375 TWiki::UI::View::_prepare('\x{a}\x{a}%TABL...', 'TWiki=HASH(0x94ab0ec)', 'Release', 'TestQuery', 'TWiki::Meta=HASH(0x952a1ac)', 0) called at /srv/www/vhosts/twiki/lib/TWiki/UI/View.pm line 359 TWiki::UI::View::view('TWiki=HASH(0x94ab0ec)') called at /srv/www/vhosts/twiki/lib/TWiki/UI.pm line 159 TWiki::UI::__ANON__() called at /srv/www/vhosts/twiki/lib/CPAN/lib//Error.pm line 379 eval {...} called at /srv/www/vhosts/twiki/lib/CPAN/lib//Error.pm line 371 Error::subs::try('CODE(0x94c3b5c)', 'HASH(0x9529648)') called at /srv/www/vhosts/twiki/lib/TWiki/UI.pm line 199 TWiki::UI::run('CODE(0x86041a8)', 'view', 1) called at /srv/www/vhosts/twiki/bin/view line 31 ModPerl? ::ROOT::ModPerl::Registry::srv_www_vhosts_twiki_bin_view::handler('Apache2::RequestRec=SCALAR(0x94ec9c4)') called at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/ModPerl/RegistryCooker.pm line 203 eval {...} called at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/ModPerl/RegistryCooker.pm line 203 ModPerl? ::RegistryCooker::run('ModPerl::Registry=HASH(0x96264a8)') called at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/ModPerl/RegistryCooker.pm line 169 ModPerl? ::RegistryCooker::default_handler('ModPerl::Registry=HASH(0x96264a8)') called at /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/ModPerl/Registry.pm line 30 ModPerl? ::Registry::handler('ModPerl::Registry', 'Apache2::RequestRec=SCALAR(0x94ec9c4)') called at -e line 0 eval {...} called

Release is the name of the web. TWiki is 4.1.2.

-- StephaneLenclud - 08 Aug 2007

I just noticed that SUMFIELD doesn't like negative amounts. I've tried many formatting methods (as noted in the SpreadSheetPluginDevArchive negative about a similar problem there with '-'s duobling up and becoming decrement operators) but without success. Has anyone else had this problem and come across a solution? (My superficial read of the code shows my Perl skills not up to proposing any solution.)

-- ScottClaridge - 08 Mar 2008

Stephane, sorry for the delay, please try the following patch and tell me if it cures the problem.

Index: lib/TWiki/Plugins/FormQueryPlugin/TableFormat.pm
===================================================================
--- lib/TWiki/Plugins/FormQueryPlugin/TableFormat.pm   (revision 16418)
+++ lib/TWiki/Plugins/FormQueryPlugin/TableFormat.pm   (working copy)
@@ -236,10 +236,21 @@
                         $row =~ s/\$username/$users->login($user)/ges;
                     }
                 }
-                $row =~ s/\$createdate/TWiki::Search::_getRev1Info( $sub->get("web"), $topic, "date" )/ges;
-                $row =~ s/\$createusername/TWiki::Search::_getRev1Info( $sub->get("web"), $topic, "username" )/ges;
-                $row =~ s/\$createwikiname/TWiki::Search::_getRev1Info( $sub->get("web"), $topic, "wikiname" )/ges;
-                $row =~ s/\$createwikiusername/TWiki::Search::_getRev1Info( $sub->get("web"), $topic, "wikiusername" )/ges;
+                my $search = $TWiki::Plugins::SESSION->{search};
+                if ($search && $search->can('_getRev1Info')) {
+                    $row =~ s/\$createdate/
+                      $search->_getRev1Info( $sub->get("web"), $topic,
+                                             "date" )/ges;
+                    $row =~ s/\$createusername/
+                      $search->_getRev1Info( $sub->get("web"), $topic,
+                                             "username" )/ges;
+                    $row =~ s/\$createwikiname/
+                      $search->_getRev1Info( $sub->get("web"), $topic,
+                                             "wikiname" )/ges;
+                    $row =~ s/\$createwikiusername/
+                      $search->_getRev1Info( $sub->get("web"), $topic,
+                                             "wikiusername" )/ges;
+                }
             }
 
             $row =~ s/\r?\n/$newLine/gos if( $newLine );

Scott, if you can nail down a testcase, please create a bug report and I'll look at it.

-- CrawfordCurrie - 08 Mar 2008

I'll give it a shot when I don't find myself thinking about it at 12am on a Sunday!

-- ScottClaridge - 08 Mar 2008

So I tried to capture the essence of the problem in http://develop.twiki.org/~twiki4/cgi-bin/view/Bugs/Item5440. Hope it lead to some resolution. Let me know if there is anything else I can try. (I note a FQPDEBUG in the code, but can't quite see how to use it.)

-- ScottClaridge - 14 Mar 2008

I have been having a tough time finding information about why this plugin refuses to install. I keep getting this error when I install:

Subroutine install redefined at /usr/share/twiki/FormQueryPlugin_installer.pl line 890.

I tried google, but nothing came up. I am really new to Twiki, so I am not sure this problem has been addressed yet already.

-- JoelDeJesus - 18 Mar 2008

Hi,

I found this Plugin trying to do multi-nested searchs with conditional formatting... but I get an error message : Plugin initialisation failed (this message is all over my local documentation of the plugin) I think DBCacheContrib works as DBCachePlugin works? both Plugins appear in

%ACTIVATEDPLUGINS%

Looking at the activity around this Plugin I wonder if it has been tested with last version of twiki... Any idea on how to solve the problem? or any other Plugin suggestion?

-- Sebastien Digard - 2015-10-20

As indicated, this plugin has not been updated since TWiki-4.0. It likely needs some TLC to make it run smoothly on the latest TWiki version.

Personally I have not seen a need for this plugin. For complex multi-table queries I use a SEARCH per table to populate SpreadSheetPlugin hashes or SetGetPlugin JSON objects, then do the join and display based on the saved variables.

-- Peter Thoeny - 2015-10-20

Ok, I'll look into it. I just deactivated the mentioned plugin because they caused my

[[+topic]]

links to fail.

-- Sebastien Digard - 2015-10-21