Tags:
create new tag
, view all tags

FormQueryPlugin Tutorial

The idea behind FormQueryPlugin is really very simple, but it can sometimes be difficult to acquire all the tricks to use it effectively. This tutorial is divided into three parts; first, we describe the theory behind the plugin; second, we illustrate a range of simple TWikiApplications that leverage the plugin, giving details of how the queries in the application work. Finally, we present a FAQ section. This topic is open to anyone who wants to edit it, but if you do please follow the following etiquette:

  1. Follow the existing structure of the document
  2. Format FAQ questions the same as existing questions
  3. Give examples in the FAQ if possible.

Note the FormQueryPlugin is only necessary because TWiki doesn't provide effective basic facilities to handle databases. Some day, we'll be able to throw all this away smile

The idea

The FormQueryPlugin treats TWiki topics in a web as the data files for a simple database. It supports simple SQL-like queries over this data, and the formatting of query results in tables. It doesn't do anything you can't do using the TWiki standard %SEARCH and %METASEARCH - it just does it a lot more cleanly, and a heck of a lot faster. It's appropriate in any slightly-more-than-trivial database application of TWiki, such as

  • Bug database
  • Customer relationship management database
  • Workflow management
  • Requirements management

How TWiki data is represented

The easiest way to understand how to formulate queries is to understand how TWiki data is represented internally (sorry!). The FormQueryPlugin uses two simple data structures,_Map_ and Array. A Map (a.k.a hash, or associative array) is an array that is indexed by a string, for example the topic name, and an Array is an array indexed by an integer. Maps and Arrays have entries that are or type string, integer or reference. A _reference is simply a pointer to another Map or Array.

The database is really just a big Map of all the topics in the web, indexed by the topic name. Each topic is itself a Map that has a simple fixed schema, which looks like this:

  • name - string name of the topic
  • parent - string name of parent topic
  • _up - reference to the Map of the parent topic, if it exists
  • attachments - Array of reference, each of which refers to a Map which contains:
    • _up - reference to the Map for the topic
    • name - string attachment name
    • attr - string e.g hidden
    • comment - string attachment comment
    • path - string client path used to upload attachment
    • size - integer size in Kb
    • user - string who uploaded the attachment
    • version - string e.g. 1.3
  • info - reference to a Map containing:
    • _up - reference to the Map for the topic
    • author - string most recent author
    • date - integer date of last change, in # of seconds since Jan 1, 1970
    • format - string topic format version
    • version - string topic version number
  • moved - reference to a Map containing:
    • _up - reference to the Map for the topic
    • by - string who moved it
    • date - string when they moved it
    • from - string where they moved it from
    • to - string where they moved it to
  • form - string name of the form type
  • formname - reference to a Map containing a key-value pair for each field in the form. e.g. if a "MyForm" is attached, formname will be MyForm.
  • text - string raw text of the topic

The sub-Maps created for info, formname, moved, and each row in attachments all have a reference back to the topic Map, called _up.

Advanced Other fields may be added by topic relations and the parsing of tables embedded in the topics.

How to use it

Basic Usage

The FormQueryPlugin has two basic operations, FORMQUERY and SHOWQUERY. A FORMQUERY is a query over a set of data, akin to a SQL SELECT statement. A FORMQUERY is given a name, so you can refer to it later, and a search, which it the equavalent of the SQL WHERE clause. For example, the SQL

SELECT * WHERE age > 30
would be written as
%<nop>FORMQUERY{name=myquery search="age>30"}%
we can also extract fields of the matched records:
SELECT name WHERE age > 30
would be written as
%<nop>FORMQUERY{name=myquery search="age>30" extract="name"}%
by default FORMQUERY operates over the entire top-level Map of topics, though it can also take another query as input.

SHOWQUERY is simply used to display the results of a query. SHOWQUERY can either use a one-off format, or it can re-use a format defined elsewhere using TABLEFORMAT.

Imagine this scenario. We have a web of topics, each of which may contain a form of type MyForm and may contain a table of attachments.

The first thing we want to do is produce a summary table of all attachments in the web with size > 1024.

Remember, the web is represented as a Map indexed by the topic name. Within this map each topic is itself represented by a Map, within which there is always a field named attachments. This field contains a reference to an Array of Maps, one for each attachment. Each attachment Map is indexed by the name of a parameter of the record describing the attachment viz. name, attr, comment, date, path, size, user, and version. Pictorially:

  • TopicOne (Map)
    • attachments (Array)
      1. (Map)
        • name=pitcha.gif
        • size=2048
      2. (Map)
        • name=image.jpg
        • size=556
  • TopicTwo (Map)
    • attachments (Array)
  • TopicThree
etc. The first thing we want to do is extract a single table of all the attachments in all the topics.
%FORMQUERY{name=Allatts search="" extract="attachments"}%
An empty search matches all entries, so Allatts will now be set to the result of this query i.e. a flat Array of all attachments. We can narrow this down to attachments with size > 1024:
%FORMQUERY{name=Bigatts query=Allatts search="size>'1024'"}%
Now we can generate a table of these using SHOWQUERY:
%SHOWQUERY{query=Bigatts header="|*Topic*|*Name*|*Size*|" format="|$_up.topic|$name|$size|"}%
Note that we have to refer to $_up.topic to get the name of the topic because each attachment entry contains a reference to the parent topic's Map ( _up ), but not the topic's name.

To extract the total size of the big attachments we can:

%SUMFIELD{query=Bigatts field="size"}%

Now let's look at something a bit more complex. Let's say we want to extract all topics whose form attachment is of type MyForm, and then show the topic referenced in the Othertopic field of MyForm.

%FORMQUERY{name=formed search="form='MyForm'"}%
%SHOWQUERY{query=formed header="| *Topic* |" format="| $Othertopic |"}%

note that this section hasn't been tested yet Actually, this isn't quite enough. What we really want to do is show the name of the parent of the topic referenced by Othertopic (don't ask me why, we just do wink ). To do this we need to go back to the root and look that topic up. The data a FORMQUERY operates over is called the root of the query; by default this is all the topics in the web. The root of a query is accessed using the special field reference '#'. So we can look up a topic in the root using the name of the topic extracted from the Othertopic field, and then get the parent name from that:

%SHOWQUERY{query=formed header="| *Topic* |" format="| $#[Othertopic].parent |"}%

Using Topic Relations

Topic Relations are automatically extracted relationships between topics, that are calculated based on the names of the topics. For example, I might have a topic called CountrySpain, and topics CountrySpainCityBarcelona and CountrySpainCityMadrid. The FormQueryPlugin can deduce the relationship between CountrySPain and CountrySpainCityBarcelona automatically, and use it to add new fields to the Map for the topic. See FormQueryPlugin for details.

Using Embedded Tables

The FormQueryPlugin can use the DBCacheContrib's ability to parse topic text to extract tables from the topic text. See FormQueryPlugin for details.

Here is my ultra short guide to using the plugin:

Step 1: Tell FormQueryPlugin which tables to search.

You can use the plugin to search twiki forms and/or twiki tables.

If you want to search twiki tables (then you must have the EditTablePlugin installed) then you must have at least one 'TaskTable' page which describes the format of the table you want to search, e.g.:

%EDITTABLE{ header="| *n1* | *n2* | *imda* | *imti* | *mikd* |" format= "| row,-1 \ text, 16, init | text, 16, init |" changerows="on"}%
| *n1* | *n2* | *imda* | *imti* | *mikd* |

I'm not sure but I think that FormQueryPlugin is only interested in the headers defined on the TaskTable page - at least I haven't filled in any correct format info.

The TaskTable page must be defined in your WebPreferences as per Crawford's instructions at FormQueryPlugin#Tables

Step 2: Create a table for FormQueryPlugin to search.

Now let's imagine a page with a twiki table and twiki form that you want to search:

You must declare your table data to FormQueryPlugin by using %EDITTABLE, e.g.:

%EDITTABLE{ include="TaskTable" }%
| *n1* | *n2* | *imda* | *imti* | *mikd* | *au* | *bmza* | *ciza* | *zaok* | *zi* | *qm* | *eur* | *tel* | *ed* | *xml* |
| 1 | ... row 1
| 2 | ... row 2
... etc

When FormQueryPlugin scans pages for tables then it looks for %EDITTABLE and only caches table data where the 'include' points to one of the 'TaskTable' pages (see above).

Obviously you have to already know about twiki forms and how to create them in order to use FormQueryPlugin...

Step 3: Create a page to show FormQueryPlugin search results.

The FormQueryPlugin statements and what they do:

%FORMQUERY{ name=AFORMQUERY search="MyForm='20040207'" extract=TaskTable }%

Let's imagine that we have n TWiki pages, each with a TWiki form and TWiki table (as created in step 2). In the above example then FormQueryPlugin searches for the form where the form field 'MyForm' has the value '20040207'. Then it 'extract's the table on the page which is defined on twiki page TaskTable.

%FORMQUERY{ name=AFORMQUERY2 query=AFORMQUERY search="mikd =~ 'Mi3Kd4'" }%

The table extracted has many rows. We want all rows where the column 'miki' has the value 'Mi3Kd4'.

%TABLEFORMAT{ name=ATBLFMT header="~ *Topic* ~ *Mi-Kd* ~ *Field1* ~$n" format="~$topic~$MiKd~$Field1~" }%

Tell FormQueryPlugin how to output the rows found. Note: The ~ syntax only works with the development version of FormQueryPlugin available on this page. It will render the resulting table in TWiki table format and if TablePlugin is installed and sorting by headers is switched on then you should be able to click on the headers to sort.

%SHOWQUERY{ query=AFORMQUERY2 format=ATBLFMT }%

Tell FormQueryPlugin to output the result table. Other installed plugins, such as TablePlugin, can then work their magic on the table.

That's it! But obviously FormQueryPlugin does a lot more complicated stuff than I describe in the example above.

-- SimonHardyFrancis - 18 Feb 2004 (moved from Dev by MartinCleaver)

the TaskTable is described in 'step 1' (above). It's just an empty %EDITTABLE definition... but containing the header names that FormQueryPlugin will use to access all the other TWiki tables (in the same format, i.e. same header) on other TWiki pages.

Here, I'll try and graphically illustrate (as best as I can on a TWiki) how all the pages hang together. The following is a list of possible TWiki page names followed by hyphens intended to depict elements on that page:

TaskTable
- %EDITTABLE statement               <-- (1 line) containing header definition
- TWiki table header                 <-- (1 line) I'm not sure if this is necessary or not
- TWiki table body                   <-- (n lines) this is *not* necessary !

TableToSearch1
- %EDITTABLE{ include="TaskTable" }% <-- (1 line) containing 'include' to our table defintion
- TWiki table header                 <-- (1 line) conform to the header in TaskTable
- TWiki table body                   <-- (n lines) the table rows that we want to search
- Form name                          <-- (1 line) the form name that we want to associate with the TaskTable type of table
- Form field                         <-- (1 line) some type of attribute which is unique to the table on this page, in my case a date; MyForm = 20040207

TableToSearch2
- %EDITTABLE{ include="TaskTable" }% <-- (1 line) containing 'include' to our table defintion
- TWiki table header                 <-- (1 line) conform to the header in TaskTable
- TWiki table body                   <-- (n lines) the table rows that we want to search
- Form name                          <-- (1 line) the form name that we want to associate with the TaskTable type of table
- Form field                         <-- (1 line) some type of attribute which is unique to the table on this page, in my case a date; MyForm = 20040208

TableToSearch3
- %EDITTABLE{ include="TaskTable" }% <-- (1 line) containing 'include' to our table defintion
- TWiki table header                 <-- (1 line) conform to the header in TaskTable
- TWiki table body                   <-- (n lines) the table rows that we want to search
- Form name                          <-- (1 line) the form name that we want to associate with the TaskTable type of table
- Form field                         <-- (1 line) some type of attribute which is unique to the table on this page, in my case a date; MyForm = 20040209

SomeOtherPage
- TWiki table header                 <-- (1 line) this table will be ignored because there is no associated %EDITTABLE statement,
- TWiki table body                   <-- (n lines) and most importantly no include="TaskTable" parameter
- Other form name                    <-- (1 line) 
- Other form field                   <-- (1 line) this form field will be ignored during the search below because it's not named MyForm

FormQueryTest
- %FORMQUERY{ name=AFORMQUERY search="MyForm='20040207'" extract=TaskTable }%                               <-- (1 line) limit our search to TableToSearch1 & extract the table
- %FORMQUERY{ name=AFORMQUERY2 query=AFORMQUERY search="mikd =~ 'Mi3Kd4'" }%                                <-- (1 line) grab all those rows where column name mikd =~ Mi3Kd4
- %TABLEFORMAT{ name=ATBLFMT header="~ *Topic* ~ *Mi-Kd* ~ *Field1* ~$n" format="~$topic~$MiKd~$Field1~" }% <-- (1 line) create a new table to show the results formated like this
- %SHOWQUERY{ query=AFORMQUERY2 format=ATBLFMT }%                                                           <-- (1 line) generate the new table

Unfortunately I don't know of a public TWiki installation where FormQueryPlugin is installed... if you point me at one then I'll create a working example for you...

-- SimonHardyFrancis - 18 Feb 2004 (moved from Dev by MartinCleaver)

Questions and answers

Why not just use %METASEARCH and %SEARCH

Go ahead, if you think you can hehe! As the capability of these two tags has grown, so the need for FormQueryPlugin has diminished. But it's still a lot easier to use, IMHO.

What is this file _FormQueryCache?

The plugin maintains a cache of all topic contents in a cache database in each web. This file is named _FormQueryCache. It can safely be deleted at any point; the only impact will be that the next query over the data in that web will run more slowly.

How do I show attachments from last week?

You should be able to use WITHIN_DAYS. Example script forthcoming...

How do I limit the number of results?

Use row_count: %SHOWQUERY{ query=myQuery format=myFormat row_from=0 row_count=10 }%

Is it possible to use a bullet list instead of a table?

Yes, just use format=" * $myvariable"

Is there a way to combine searches?

For instance: Query1 that searches all attachments, Query2 that searches form fields, and Query3 that creates a subset of the two, with the possiblity to access fields from both queries?

There's no way to do an INTERSECTION at present. If I ever support full SQL (which is a possibility since I found CPAN:SQL::Statement ) then it becomes a possibility.

Is the presence of a Form on the pages mandatory?

No.

What release supports formatting into the TablePlugin style mentioned here?

Where can I get the Devel or Release zip of the plugin that supports formatting the output table of SHOWQUERY into the TablePlugin style? The copy I downloaded from the FormQueryPlugin page does not seem to support this? -- GregFulk - 04 Jan 2007

Examples

Accessing table fields

%EDITTABLE{include="TaskTable"}%
|*Nr*|*Text field*|*Drop down*|*Mood*|*Timestamp*|
| 1  | hello      | two       | :-)  | 26 Jun 2004 |
| 2  | hello      | three       | :-(  | 23 Apr 2005 |

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

%SHOWQUERY{query="q2" format="$topic($TaskTable.Nr, $TaskTable.Timestamp, $TaskTable.1.Timestamp), "}%

%FORMQUERY{name="q3" search="" extract="TaskTable[?Timestamp=~'2004']"}%
%SHOWQUERY{query="q3" format="$topic($Nr, $Timestamp), "}%

Using %URLPARAM to create interactive queries

<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>

Using %SHOWCALC

%FORMQUERY{name="Bquery" search="Productlineownership='Mesh'"  extract="$form"}%

%TABLEFORMAT{name="tbla" query="Bquery" format="| $_up.name | $KLOC |"}%
%SHOWQUERY{query="Bquery" format="tbla"}%

%QUERYTOCALC{query="Bquery" format="tbla" }%

Components=%SHOWCALC{"$ROW()"}% 
Size=%SHOWCALC{"$SUM(R1:C2..R$ROW():C2)"}% 

last row=%SHOWCALC{"$T(R$ROW():C1)"}%
Edit | Attach | Watch | Print version | History: r10 < r9 < r8 < r7 < r6 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r10 - 2007-01-04 - GregFulk
 
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.