Tags:
create new tag
, view all tags

Using a Table as Searchable Database

ThomasWeigert - 01 Jan 2004

Introduction

Who has not been tempted to create a wiki topic with a large table representing structured data in each row, and then wanted to search that table for a subset of the represented data? We all know that this is not the wiki way, and that you should create a separate topic for each row (with the data stored in a form) and then create the table through a formated search. But over and over again we fall into that trap. And indeed, sometimes it just does not seem right to create, say, a hundred topics with three pieces of data in a form.

I finally gave in to the urge to use a table as a database and created a small extension to ShawnBradford's extremely useful SimpleTableEntryUsingForms. Using this extension, you can write formatted searches very much like the standard searches, but have them applied to tables stored in metadata.

Usage

Begin by creating tables storing structured data, using SimpleTableEntryUsingForms. You can then search through data in all tables in some or all webs, and filter the results based on the search expression. The result of the search can be presented in a formatted table.

ALERT! Whenever I mention "table", I am referring to tables in the style of SimpleTableEntryUsingForms.

A new variable %METATABLESEARCH% is added, with features very much like .

Variable: Expanded to:
%METATABLESEARCH{"text" ...}% Inline search, shows a search result embedded in a topic. Parameters are the search term, web, scope, order and many more:
Parameter: Description: Default:
"text" Search term. Is a regular expression, where ";" is used to mean and e.g. "search;agrep" will find all topic containing search and agrep. Required
search="text" (Alternative to above) N/A
format="..." Custom format results: see FormattedSearch for usage, variables & examples Required
web="Name"
web="Main, Know"
web="all"
Wiki web to search: A web, a list of webs separated by comma, or all webs. Current web
casesensitive="on" Case sensitive search Ignore case
header="..." Custom format results: see FormattedSearch for usage, variables & examples Results in table
separator=", " Line separator between hits Newline "$n"

Of the variables that can be used in the format string, $text, $summary, and $pattern are not supported, as they apply to a whole topic, and make no sense for a matched row.

As additional variables are provided:

Name: Expands To:
$editlink Link to the matched row. By clicking on this link the row can be edited in its respective table.
$tablename Name of the table (see SimpleTableEntryUsingForms)
$template Name of the form structuring the table (see SimpleTableEntryUsingForms)

Example

Assume that you have created one or more tables using the VerificationForm attached to SimpleTableEntryUsingForms. The following search will find all rows in tables in the current web where the Hardware Matched field is set to Yes. The results are presented in a table of three columns: the first column shows the name of the topic in which the row was matched as a link to the row, the second column shows the Requirement Statement field, and the final column shows the Scenario Created field.

| *Edit* | *Requirement Statement* | *Scenario Created* | 
%METATABLESEARCH{ "[H]ardwareMatched=.Yes" 
   format="| [[$editlink][$topic]] | $formfield(RequirementStatement)
           | $formfield(ScenarioCreated) |" }% 

Installation

The patch below should be applied to TablePlugin.pm after the patch in SimpleTableEntryUsingForms has been applied.

Discussion

See also FormQueryPlugin, that already does this.

-- CrawfordCurrie - 16 Jun 2004

Crawford, you are correct in that FormQueryPlugin subsumes this functionality. However....

(rest of discussion and response moved to FormQueryPluginDev)

Topic attachments
I Attachment History Action Size Date Who Comment
Unknown file formattablesearch TablePlugin.pm.tablesearch r1 manage 15.1 K 2004-01-01 - 03:54 ThomasWeigert Patch to lib/TWiki/Plugins/TablePlugin.pm
Edit | Attach | Watch | Print version | History: r7 < r6 < r5 < r4 < r3 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r7 - 2004-11-12 - SamHasler
 
  • 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.