Start of topic | Skip to actions

FormQueryPlugin

The FormQueryPlugin provides a query interface to the TWiki::Contrib::DBCache module, giving a way to present query results as formatted tables.

WARNING: TWiki-4 only. If you want to use this extension with an earlier version of TWiki, please see here

When using the plugin each topic is treated as a container for data fields. These fields can be the fields in the form attached to the topic, or can be parsed out of the topic. The database is managed by the TWiki::Contrib::DBCache module, which must be installed for this plugin to work.

The tutorial section of this topic gives an introduction to formulating and formatting queries using this plugin, and is a good place to start.

Note: Since the last release the following macros have been deprecated. Please get compatible support by loading the appropriate plugins:

Macro	Plugin
`CALLMACRO`	TWiki:Plugins/MacrosPlugin
`SET`	TWiki:Plugins/MacrosPlugin
`ARITH`	Still supported in this release, but undocumented and should be replaced by %CALC% from SpreadSheetPlugin
`PROGRESS`	Deprecated in favour of GaugePlugin^?, though still supported because it doesn't require ay additional software to be installed (progress bars are pure HTML)
`WORKDAYS`	Still supported in this release though undocumented and deprecated on the basis that it should be in %CALC% at some point

The tasknotify script has also been removed and is now shipped separately as the TWiki:Plugins/NotifierAddon.

If the appropriate plugins and contributed code modules are loaded, this release should be fully compatible with previous releases of this plugin.

Features

Perform complex queries on the database
Extensive conditional formatting support
Automatic derivation of new topic names
Recognition and special formatting of key data strings

How the database gets built

The basic database is built using the DBCache. Refer to the documentation of that module for details of how it works.

As well as the form data loaded by the DBCache, the FormQueryPlugin adds the ability to extract embedded tables from topics.

Embedded tables that follow a specific format (defined using the EditTablePlugin) are added to the topic as an array of rows, each of which contains a map keyed on the fields and mapping to the values in the table. This array is added to the topic as a field named the same as the table type. Where there are multiple tables of the same type in a topic, they are concatenated together into a single array. See Settings for more information about how to set up tables.

Topics can also be automatically related to each other by name. The plugin uses something called Topic Relations that allow the creation of two-way relationships between topics. Topic relations from parent topics to child topics are stored in the map of the parent topic using the name of the relation as the field name and an array of references to the maps of child topics. Reverse relations from child to parent topics are also stored. See Settings for more information about how to set up topic relations.

Most of the rest of this documentation describes how to search this database and generate reports.

Syntax Rules

Queries

`%FORMQUERY`

%FORMQUERY is the basic query mechanism. A %FORMQUERY works either on the whole database or, if the query parameter is given, the results of another query.

Parameter	Description
`name`	Required, gives the name of the query result for use in `%SHOWQUERY` or another `%FORMQUERY`
`search`	Required, the search to perform (see Search operators)
`query`	Optional, the name of the query to refine. If missing, defaults to the whole database
`extract`	Optional, the name of a field in each of the matched maps to flatten out into the returned list.
`moan`	Optional, if set to "off" will disable match failed message

For example,

%FORMQUERY{name=AQuery search="Owner='Main\.Fred'"}%
%FORMQUERY{name=BQuery query=AQuery search="Product='Boiled Egg'"}%
%FORMQUERY{name=CQuery query=BQuery search="" extract=CookingTimes}%
%FORMQUERY{name=DQuery query=CQuery search="Time < '4'"}%

will search for all topics with a form field Owner set to Main.Fred, then filter that down to those topics that have Product set to Boiled Egg. Then it will extract and flatten out the embedded table CookingTimes in each matched topic. By "flatten out" we mean that future queries on CQuery must refer to the fields of the CookingTimes table, not the fields of the topic, i.e. CQuery will be an array of all the rows in the embedded table. Finally it will filter down to those rows that have the column Time < 4 minutes.

Of course there is more than one way to skin a cat. A faster way to formulate the same query would be to say:

%FORMQUERY{name=AQuery search="Owner='Main\.Fred' AND Product='Boiled Egg'" extract=CookingTimes}%
%FORMQUERY{name=DQuery query=AQuery search="Time < '4'"}%

FORMQUERY is normally silent i.e. it generates no output unless there is an error, in which case an error description is inserted. This error message can be disabled using the moan parameter which makes FORMQUERY totally silent.

Search operators

The available search operators are described in the documentation for the DB Cache Plugin^?. Search operators work on the fields of each map, be it a topic or an embedded table row.

Fields can be simple field names or can be more complex, and may even contain embedded searches. See Fields below for more information.

At present there is no way to constrain a search on the contents of a contained table, such as an embedded table or forward relation. However there are usually ways around this; for example, a %FORMQUERY that uses extract to flatten all the sub-tables, and then use of the parent relation to complete the search. For example:

%FORMQUERY{name=AQuery search="Product='Boiled Egg'" extract=CookingTimes}%
%FORMQUERY{name=DQuery query=AQuery search="Time < '4' AND CookingTimes_of.Owner='Main\.Fred'"}%

Fields

Field references can be as simple as the name of a field, or may be more complex expressions that can even include embedded searches. The precise interpretation of the syntax depends on the type (Map or Array) of the object being referenced. Arrays

N where $n is a number will get the Nth entry in the array e.g. attachments.9
[_N_] where $n is a number will also get the Nth entry in the array e.g. attachments[9]
X will return the sum of the subfield X of each entry e.g. TaskTable.Effort will sum the Effort column in a table called TaskTable.
[?search] will perform the given search over the entries in the array. Always returns an array result, even when there is only one result. For example: attachments[?name='pitcha.gif'] will return an array of all the entries that have their subfield name set to Sam.
[*X] will get a new array made from field X of each entry in this array. For example attachments[*size] will get an array of the sizes of each attachment.

Maps

X will get the subfield named X. For example, Product will get the formfield named Product.
X.Y will get the subfield Y of the subfield named X.
[X] = will get the subfield named =X (so X[Y] and X.Y are synonymous).

In all cases

# means "reset to root". So #.Y will return the subfield Y of the Map at the root of the query.

Reports

`%TABLEFORMAT`

%TABLEFORMAT provides a template for the display of values extracted from a query (like the old Fortran 'format' statement).

Parameter	Description
`name`	Required, gives the name of the format for use in `%SHOWQUERY` or another `%FORMQUERY`
`format`	Required, gives the format of the body rows (or the format to subclass)
`header`	Optional, gives the format of the header row
`footer`	Optional, gives the string to use to terminate the table
`sort`	Optional, defines the sort order
`help`	Optional, if present then a helpful message will be output for undefined fields. So if you see a table entry that is blank and you don;t think it should be, add the `help` parameter and it should give some guidance.
`moan`	Optional, if set to "off" disables error output

The header, footer and format parameters are very similar in application to the parameters used in the standard %SEARCH TWiki command. For the uninitiated, the header is used to define the first row of the results table:

header="| *Topic* | *Summary* |"
header="<table><tr><th>Topic</th><th>Summary</th></tr> |"
footer="</table>"

The format parameter is overloaded so it can also be used to refer to a table format previously defined using %TABLEFORMAT. This allows you to derive new formats (e.g. with different sort orders) For example,

%TABLEFORMAT{ name=TBLFMT format="|$topic|" sort="Owner,Priority"}%
%TABLEFORMAT{ name=SUBFMT format=TBLFMT sort="Priority,Owner" }%

Any of the header, footer or sort parameters in the referenced table format can be overridden by giving new values in the %SHOWQUERY.

The footer is usually only used for tables generated directly in HTML, in which case it is usually set to footer="</table>"

footer="</table>"

The format is used to define the rows in the body of the table:

format="| $topic | $Summary |"

Any fields in the form in the topic can be output by putting a $ sign before the name of the field. The two special fields $topic and $form are available for outputting the name of the topic and the type of the form. You can also refer to fields in related topics using the syntax $field.field where $field is the name of the relation and field is a field in the related topic; for example, format="|$Child_of.topic|".

It is also possible to expand the contents of tables embedded in the topic, in two ways:

using the syntax $field[format=FORMAT] where $field is the table type, which is named the same as the table topic, and FORMAT is the name of a format previously defined using %TABLEFORMAT and suitable for expanding the sub-table.
using the syntax $field.subfield where subfield is a numeric field in the contents of the embedded table (i.e. it contains a number). In this case the result will be the sum of all subfield values over all the rows in the table.

sort is used to define the sort order, and is a list of field names (without $'s) separated by commas. You can reverse the sort order for a column by prepending '-' to the field name. For example,

sort="Owner,-Priority"

The default sort method is string comparison. If the field data is numeric, then you can put a '#' in front of the field name to force the sort to be numeric. For example,

sort="Owner,#Priority"

sort="Owner,-#Priority"

%TABLEFORMAT is silent i.e. it produces no output on the page unless there is an error, in which case a descriptive error message is output. This message can be disabled using the moan parameter.

`%SHOWQUERY`

Used to show the results of a query, %SHOWQUERY 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,

%SHOWQUERY{ query=AFORMQUERY format=ATBLFMT }%

Note: If you render your results in TWiki table format and limit the rows displayed using row_count (e.g. first 10 from 99 rows), and also use TablePlugin to allow sorting on the headers, then only the 10 displayed rows will be sorted by TablePlugin (and not the full 99 rows in the table).

Automatic topic naming

When using relations (as described above) it is useful to be able to automatically generate new topics using the relation to define the new topic name. This tag supports this. This section can safely be ignored unless you want to generate topics using names derived from relations.

`%TOPICCREATOR`

Used to create a new topic generator button using rules defined in topic relations.

Parameter	Description
`relation`	Required, name of the relation to use to derive the next topic name
`text`	Required, text of the button
`base`	Optional, name of base topic to apply relation to. Defaults to the current topic.
`form`	Optional, type of the form to include with the new topic. The wikiname of the topic containing the form definition.
`template`	Optional, the template topic to use

The base is used as the RHS of the relation and a new topic name is derived. This is best shown by an example. If we have the relation:

Dir%A_%B subdir Dir%A

and the base is Dir75, and topics Dir75_1 through Dir75_54 already exist, the generator will generate topic Dir75_55. Note that even if there are gaps in the sequence e.g. Dir75_53 doesn't exist, the generator will still create Dir75_55 i.e. one more than the largest existing topic. The behaviour of the generator is undefined if the base does not match the RHS of the relation.

A special hard-coded relation, copy, is also provided by default that will create a new topic by incrementing a number at the end of the current topic name. For example, if the base is Dir75 and we say

%TOPICCREATOR{relation=copy}%

then the next topic created will be Dir76 (or whatever the next available topic is). If the current topic does not end in a number, it's behaviour is undefined.

The topic generator works by creating the topic name at the time the button is pressed i.e. you can dislay a topic containing a generator, wait a day, hit the button to create the new topic and the name of the new topic created will not conflict with other topics created by other people using the generator in the interim. To do this it uses the autocreate script installed in the twiki bin directory.

Other helpful macros

This section describes a number of other helpful macros provided by the plugin. It can safely be ignored unless you specifically want to use any of the functionality described here.

`%PROGRESS` - DEPRECATED, use TWiki:Plugins/GaugePlugin instead

Used to display a three-part progress bar.

Parameter	Description
`total`	Required, total length of the bar in arbitrary units
`actual`	Required, where we really are
`target`	Optional, where we should expect to be

If target is omitted, then it creates a standard two-part progress bar. If all three parameters are used, then it creates bars like this:

If actual < target, e.g. %PROGRESS{total=10 target=5 actual=3}%

If actual > target, e.g. %PROGRESS{total=10 target=5 actual=7}%

Tutorial

See TWiki::Plugins/FormQueryPluginTutorial for a tutorial describing how to use the plugin in real-world applications.

FormQueryPlugin Settings

One line description, shown in the TextFormattingRules topic:

Set SHORTDESCRIPTION = Provides query capabilities across a database defined using forms and embedded tables in TWiki topics.

Set to 1 to enable internal consistency checks and other debug states.

Set DEBUG = 0

Set to 0 if you do not want to use the Storable module even if it is installed. The plugin is much faster if Storable is installed and enabled.

Set STORABLE = 1

The plugin can be enabled gloablly, or on a per-web basis. If it is to be enabled globally, then the setting

Set FQP_ENABLE = 1

must appear in this topic. Otherwise the plugin can be enabled on a per-web basis by putting the FQP_ENABLE setting into the WebPreferences topic for the web. The remaining settings are defined on a per-web basis.

Topic Relations

Topic relations are defined by setting the FQRELATIONS variable in the WebPreferences topic of the web to be processed. This defines a semicolon-separated list of rules for deriving relationships between topics using common portions of names. This is best illustrated by an example. Let's say we set FQRELATIONS to be:

ReQ%Ax%B SubReq ReQ%A; TiT%An%B TestItem ReQ%A

This describes two rules, the first of which says "Any topic named "ReQ<something>x<something>" is a SubReq of another topic called "ReQ<something>". Single upper case characters with a preceding percent sign are used to define the <something>s. So, in this example, ReQ1456 has the SubReq relation to ReQ1456x7. The reverse relation is also inserted, but appending _of to the relation name, so ReQ1456x7 has the SubReq_of relation to ReQ1456.

Obviously a single topic may have many topics that have the same relation to it (i.e. they are one-to-many), so forward relations are stored as arrays in the maps that represent topics. Reverse relations are many_to_one so they are stored as a simple field in the other topic. Relations are stored as pointers to the maps of the other topic so they can't be printed as if they were simple names. Instead you have to use them to refer to fields within the other topic. For example, in a %SHOWQUERY showing ReQReQ1456x7 you can't refer simply to $SubReq_of to get ReQ1456 (the name of the related topic); you have to use $SubReq_of.topic instead.

The default value of FQRELATIONS is ReQ%Ax%B SubReq ReQ%A; TiT%An%B TestItem ReQ%A

Tables

Tables to be read into the database must be formatted to use the Edittable plugin using the 'include=' feature that defined the format of the tables in another topic. Tables to be read are defined by setting the FQTABLES variable in the WebPreferences topic of the web. This is a list of the names of the topics that EditTablePlugin uses to find it's table formats. Tables are read in as arrays of maps, with each map keyed on a field name generated by stripping all non-alphabetic characters from the column header. Note that the column header where the table is defined is used, not the column header in the EditTable include= topic.

Two special map entries are added to each table row, topic and the equivalent of the reverse relation described above, represented by the name of the table with "_of" appended. So if you load a table type "CookingTimes", each row will have topic set to the name of the topic it was loaded from and CookingTimes_of set to the map of that topic. This allows you to refer to the fields of the topic that contains a table even after the table has been extracted.

The default value of FQTABLES is TaskTable

Formatting field values

A feature of the plugin is that special words in the fields of %SHOWQUERY can be recognised and formatted to your preference.

For example, let's say you want to detect the words "No progress" in a table field and highlight it in bold red text. This is done through a special topic in the web that contains a set of mappings from perl regular expressions that match the words to be recognised and the special formatting. For example, the highlight map might contain the following:

	* /(No progress)/ = <font color=red><b>$1</b></font>

The /(No progress)/ defines the perl RE to be matched, and the HTML on the right of the equals defines what to replace it with. The $1 inserts whatever was matched by the RE. The RE must match the whole string in the table field, so the above map will NOT match "No progress with editing"; to match that, you'd have to write:

	* /(No progress)(.*)/ = <font color=red><b>$1</b></font>$2

Only the first RE in the map matched by the string is expanded. Note that spaces between the '=' and the first non-whitespace character of the RHS are ignored. If you want a space here use  

The default name of the map topic is HighlightMap though you can change this by setting the variable FQHIGHLIGHTMAP in WebPreferences to the name of the topic to use instead. For example

	* Set FQHIGHLIGHTMAP = MyHighlightMap

Just about any formatting can be included on the LHS of the map; for example, you may want to leverage the SmiliesPlugin and translate "pass" to a smiley face and "fail" to a frown.

	* /pass/ = :-)
	* /fail/ = :-(

Plugin Installation Instructions

Note: These instructions are for the TWiki maintainer. No changes to your browser are required.

Download the ZIP file from the Plugin web (see below). Contents:

File:	Description:
`lib/TWiki/Plugins/FormQueryPlugin.pm`	Main plugin script
`lib/TWiki/Plugins/FormQueryPlugin/Arithmetic.pm`	Arithmetic interpreter
`lib/TWiki/Plugins/FormQueryPlugin/ColourMap.pm`	Highlight map class
`lib/TWiki/Plugins/FormQueryPlugin/TableFormat.pm`	Table formatter
`lib/TWiki/Plugins/FormQueryPlugin/TableDef.pm`	Table format definition class
`lib/TWiki/Plugins/FormQueryPlugin/Relation.pm`	Topic relation class
`lib/TWiki/Plugins/FormQueryPlugin/ReqDBSupport.pm`	Extra features
`lib/TWiki/Plugins/FormQueryPlugin/WebDB.pm`	Subclass of Map for a whole web
`data/TWiki/FormQueryPlugin.txt`	Plugin topic
`bin/autocreate`	Topic creation script
`bin/tasknotify`	Mailer script

Unzip FormQueryPlugin.zip in your twiki installation directory.
Optionally, run FormQueryPlugin_installer to automatically check and install other TWiki modules that this module depends on. You can also do this step manually.

Alternatively, manually make sure the dependencies listed in the table below are resolved.

Name	Version	Description
TWiki::Contrib::DBCacheContrib	Required. Download and install from the TWiki:Plugins/DBCacheContrib code library.
Time::ParseDate	Required. Available from CPAN.
Storable	2.13 recommended; accelerates cache handling. Available from CPAN
TWiki::Plugins::MacrosPlugin	Recommended; used for parameterised includes. Download from TWiki:Plugins/MacrosPlugin repository.

(Dakar) Visit configure in your TWiki installation, and enable the plugin in the {Plugins} section.
The variable %FORMQUERYPLUGIN_ENABLE% must be set either globally or in the web where you want to use it.
- Set ENABLE = 1
By default the plugin is enabled globally (on all webs). This may have an unwelcome performance impact, as the plugin may create a cache of topics in a web where it is not intended to be used. To enable the plugin for selected webs only, comment out the previous line (put a # in front of the word 'Set') and put the following line (without the #) into the WebPreferences topic of the web you want to enable it on:
- #Set FORMQUERYPLUGIN_ENABLE = 1
Note: If you are using it, TWiki:Plugins/MacrosPlugin must appear in the Plugins evaluation order before FormQueryPlugin. You can ensure this by putting MacrosPlugin immediately after DefaultPlugin in the INSTALLEDPLUGINS list in TWikiPreferences.

Plugin Info

Plugin Author: TWiki:Main/CrawfordCurrie

Plugin Version: 8224

Change History:

6277 Item196 updates to FQP, including minor core bugfixes found while testing, and extension to registerTagHandler to allow registration of handlers using alternate parameter syntax. Also enabled the table plugins by default.

5517 Corrected path to module

5267 doc changes, and efficiency improvements

5223 Documentation fixes, adding gifs.

5048 Cairo readiness

5020 Moved a lot of stuff out to DBCachePlugin^?

4959 Switched over to centralise fixtures for tests and changed to perl build methodology

4917 Patches from TimSlidel^? for autocreate, collapsing tables and mod_perl

4899 Maintenance updates

4877 Bugfixes and minor enhancements

4858 Bugfixes and optimised cache

4855 Range of fixes

4850 Missing tests and fixes to locking

4849 Initial checkin

16 Jul 2004 Added option to switch on/off "Undefined" messages for undefined field values

16 Jul 2004 Expanded fields no longer have spaces around them. This is more correct, but requires care in format definitions.

16 Jul 2004 Split into sub-components

02 Apr 2004 SimonHardyFrancis^? bugfixes incorporated. TimSlidel^? patches incoporated for mod_perl, autocreate, and collapsing same-type tables in a single topic

05 Feb 2004 Bugfix version

11 Jul 2003 Initial version

Dependencies:

Name	Version	Description
TWiki::Contrib::DBCacheContrib	Required. Download and install from the TWiki:Plugins/DBCacheContrib code library.
Time::ParseDate	Required. Available from CPAN.
Storable	2.13 recommended; accelerates cache handling. Available from CPAN
TWiki::Plugins::MacrosPlugin	Recommended; used for parameterised includes. Download from TWiki:Plugins/MacrosPlugin repository.

Perl Version: 5.0

Plugin Home: http://TWiki.org/cgi-bin/view/Plugins/FormQueryPlugin

Feedback: http://TWiki.org/cgi-bin/view/Plugins/FormQueryPluginDev

Demo URL: http://www.owiki.org/FQP/

Copyright

This code is based on an original development of Motorola Inc. and is protected by the following copyrights:

Extending or customising

Extension or customisation is welcome, as long as all extensions are described and code provided back to the author.

The module is shipped with a perl build file, which should be used for installation and testing. Testing is done using Test::Unit, and is invoked using the 'test' build target. Writing tests is a useful way of feeding back bugs as well. I can't encourage you enough to maintain and extend the tests!

License

As required for the publication of all extensions to TWiki, this software is published under the terms of the GNU General Public License.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details, published at http://www.gnu.org/copyleft/gpl.html

-- TWiki:Main/CrawfordCurrie - 13:19:24 28 February 2006

End of topic
Skip to actions | Back to top

TWiki