SqlMetaPlugin

Description

The SqlMetaPlugin gives fast access to data stored in TWiki Meta variables, e.g. TWikiForms or attachment information. This is done by transparently storing all META data in an SQL database.

Advantages over %SEARCH%

• Fast
• Queries specified in normal SQL
• Use the full power of your SQL engine

How it works

• Every time a changed page is saved, the plugin parses all META variables in the page and stores them in an SQL database
• Queries can be done in normal SQL syntax using the %SQLMETASEARCH% variable.

Usage

Database structure

In TWiki, meta data has the following structure:

• %META:<type>{key1="value1" key2="value2" ...}%
• For a more in-depth explanation see TWikiMetaData.

The plugin stores all metadata of all webs in a single database table, which is organized as follows:

Field name	Meaning
topic	Contains the name of the topic, including the web, e.g. "Main.WebIndex"
name	Name of the meta field, consisting of meta type and key, separated by a ".". Examples: "TOPICINFO.author" or "FILEATTACHMENT.size". Note: There is a special treatment for FIELD types from TWikiForms. The value of the "name" key is directly extracted and appended to the meta type. This allows you to access TWikiForms fields as e.g. "FIELD.class" if you have a form containing a "class" field.
value	The value of a META field

In other words, for each key in a META variable, there is one row in the database table, giving the topic, the key name and the value.

Example

The META variable %META:TOPICINFO{author="Martin" date="1132150146" format="1.0" version="1.9"}% of a topic MyTopic in the Sandbox web would translate to the following database rows:

topic	name	value
Sandbox.Mytopic	TOPICINFO.author	Martin
Sandbox.Mytopic	TOPICINFO.date	1132150146
Sandbox.Mytopic	TOPICINFO.format	1.0
Sandbox.Mytopic	TOPICINFO.version	1.9

Plain Queries

Queries are done using the %SQLMETASEARCH{ ... }% variable. The contents of this variable is directly executed in SQL and printed to the resulting page, without any separators between rows and columns. If you want to do any formatting (and you certainly want to do this), you need to insert formatting characters into the output of the SQL command.

Example: Print an index of all topics in the current web that were created by "Martin" as a bullet list, sorted alpabetically

%SQLMETASEARCH{ 
   SELECT '   * ', topic, '\n' 
   FROM twiki 
   WHERE name='TOPICINFO.author' AND value='Martin' AND topic LIKE '%WEB%.%'
   ORDER BY topic 
}%

If you require more than one key in your query, you have to JOIN the table with itself for each key.

Example: Print an index of the 10 most recently edited topics in the current web as a table with author and edit date, sorted by edit date. Note: The FROM_UNIXTIME function may only be available in MySQL.

| *Topic* | *Author* | *Date* |
%SQLMETASEARCH{ 
   SELECT '| ', twiki1.topic, ' | ', twiki1.value, ' | ', FROM_UNIXTIME( twiki2.value ), ' |\n' 
   FROM twiki AS twiki1 JOIN twiki AS twiki2
   WHERE  twiki1.topic = twiki2.topic AND
      twiki1.name='TOPICINFO.author' AND 
      twiki2.name='TOPICINFO.date' AND
      twiki1.topic LIKE '%WEB%.%'
   ORDER BY FROM_UNIXTIME( twiki2.value ) DESC
   LIMIT 10
}%

Simplified Queries

You certainly have noticed that manually joining the tables is tedious and error-prone for many variables. Therefore the SqlMetaPlugin supports a mechanism to create the JOIN and WHERE conditions automatically, giving a more natural way to work with TWiki Meta data. This is done by using the following shortcuts:

Shortcut variable	Meaning
$<metatype>.<key>	For each distinct <metatype>.<key> in the SELECT statement, a new JOIN clause is created and the variable is replaced by a reference to the corresponding value field.
$tables	Replaced by a big JOIN clause with a copy of the table for for each distinct <metatype>.<key> statement. To be used in the FROM part of the SQL statement.
$cond	Creates additional conditions to be used in the WHERE part of the SELECT statement.
$topic	A reference to the topic name

If you use shortcut variables, you always need to include the $tables and $cond variables in your SELECT statement.

Example: Print an index of the 10 most recently edited topics in the current web as a table with author and edit date, sorted by edit date. Note: THE FROM_UNIXTIME function may only be available in MySQL.

| *Topic* | *Author* | *Date* |
%SQLMETASEARCH{ 
   SELECT '| ', $topic, ' | ', $TOPICINFO.author, ' | ', FROM_UNIXTIME( $TOPICINFO.date ), ' |\n' 
   FROM $tables
   WHERE $cond AND $topic LIKE '%WEB%.%'
   ORDER BY FROM_UNIXTIME( $TOPICINFO.date ) DESC
   LIMIT 10
}%

Working with TWikiForms

SqlMetaPlugin has a special treatment for FIELD META variables, in which TWikiForms stores its data. That allows you to access form fields more naturally as $FIELD.<fieldname> in the simplified syntax. Note that if you have multiple forms that have a field with the same name, you probably also want to specify the form name in $FORM.name.

Example: Show the rooms of all classes taught by Einstein in the year 1912 and give links to the class page.

| *Class* | *Room* |
%SQLMETASEARCH{ 
   SELECT '| [[', $topic, '][', $FIELD.name, ']] | ', $FIELD.room, ' |\n' 
   FROM $tables
   WHERE $cond AND $FORM.name='ClassForm' AND $FIELD.teacher='Einstein' AND $FIELD.year=1912
}%

Installation

Requirements

• DBI module
• SQL Database (e.g. MySQL) with Perl DBD driver

Extracting the files

Just unzip the attached SqlMetaPlugin.zip file into your twiki directory.

Database setup

Before running the plugin, you need to create a new table in the database and (optionally) a new database user with limited access rights.

Creating the table

The SqlMetaPlugin needs one database table to store the META data of the TWiki webs. It can be created using the SQL statements given below (tested for MySQL only). If you need to, you can change the names of the database and the table.

To run the SQL commands for initializing the database, you can either use it's command line tools or a graphical web-frontend such as phpMyAdmin. Note that these statements usually need to be run as the database master user (e.g. root on MySQL).

CREATE DATABASE IF NOT EXISTS twiki;

CREATE TABLE IF NOT EXISTS `twiki.twiki` (
  `topic` varchar(48) default NULL,
  `name` varchar(48) default NULL,
  `value` varchar(255) default NULL,
  KEY `bytopic` (`topic`(32),`name`(20),`value`(20)),
  KEY `byname` (`name`(20),`value`(20))
);

If you need larger field sizes, you can increase the number in the SQL statements above.

Creating a special user

It is also recommended to create a special database user that only has the access rights necessary to update and read information from the one table. In MySQL this can be done with the following command:

GRANT SELECT, UPDATE, DELETE ON twiki.twiki TO twiki IDENTIFIED BY '';

Please exchange the <password> by the real database password.

Configuring the plugin

Edit the lib/TWiki/Plugins/SqlMetaSearch.pm file and modify the following constants at the beginning of the file:

Variable	Default	Meaning
$DbDatabase	"DBI:mysql:twiki"	Perl DBI path of the database. If your database is not called "twiki", you need to change the name here. You can also exchange mysql with another database engine.
$DbTable	"twiki"	Name of the database table used by the plugin.
$DbUser	"twiki"	Username for accessing the database
$DbPassword	""	Password for accessing the database

(Re-)indexing an existing TWiki installation

After installing the plugin, you need to index all your webs to copy the META variables into the database. This is done by executing the SqlMetaReIndex.pl script in the lib directory.

If your database for some reason went inconsistent with the META data in the TWiki file storage, you can repeat this procedure at any time.

Security issues

Basically, you need to trust all your TWiki users who have edit rights on pages, as they can execute arbitrary commands on the database. However, some access restriction is provided by the database user (if constructed as above), who is only allowed to execute select, update and delete commands on the twiki table.

Also, all TWiki users with edit right can view and modify stored META data of all topics and webs by executing arbitrary SELECT/UPDATE/DELETE commands on the twiki table. However, if somebody changes the META data in the database, this only affects the results of %SQLMETASEARCH% commands, not the META data that TWiki is using internally. Also, the original META data from the TWiki-Storage can be restored to the database by simply reindexing the directory structure using the SqlMetaReIndex.pl script.

If you store sensitive information in META data (e.g. TWikiForms), protected by TWiki passwords, you should not use this plugin.