Advantages over %SEARCH%
How it works
META
variables in the page and stores them in an SQL database
%SQLMETASEARCH%
variable.
%META:<type>{key1="value1" key2="value2" ...}%
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.
%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 |
%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: TheFROM_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 }%
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: THEFROM_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 }%
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 }%
SqlMetaPlugin.zip
file into your twiki directory.
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.
GRANT SELECT, UPDATE, DELETE ON twiki.twiki TO twiki IDENTIFIED BY '';
Please exchange the <password>
by the real database password.
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 |
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.
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.