>
> | Database Plugin
<--
Contributions to this plugin are appreciated. Please update the plugin page at
http://twiki.org/cgi-bin/view/Plugins/DatabasePlugin or provide feedback at
http://twiki.org/cgi-bin/view/Plugins/DatabasePluginDev.
If you are a TWiki contributor please update the plugin in the SVN repository.
-->
Provide secure access (read and write) to data in an SQL database, with flexible results formatting.
Any database that has a CPAN:DBI interface can be used.
Usage
DATABASE_SQL
%DATABASE_SQL{description="table_description" sql="sql"}%
This is the most general of the commands. It allows you to execute arbitrary SQL statements, and provides flexible formatting of the results.
-
description - identifies the database configuration to use (required)
-
sql - The SQL to execute (required)
-
format - (optional) Format to display results in. if you don't give this parameter results will be ignored.
-
header - (optional) The header to display on the results table.
-
separator - string that separates results. Default newline.
If the SQL statement doesn't return a result (e.g. an UPDATE statement) you must not give a format parameter. The format parameter is a string describing the required format for the results, where each $colname will expand to the value of that column. See FormattedSearch for more on how format, header and separator work.
DATABASE_SQL_TABLE
%DATABASE_SQL_TABLE{description="db1" headers="hdr1,hdr2,hdr3" command="sql command"}%
-
description - identifies the database configuration to use
-
headers - Table headers.
-
command - Any SQL command that returns rows
-
 This syntax is maintained for compatibility. You are recommended to use DATABASE_SQL instead.
DATABASE_SQL_REPEAT
%DATABASE_SQL_REPEAT{description="description" command="..SQL COMMAND.." columns="col1,col2,col3"}% .... user formatting .... %DATABASE_SQL_REPEAT%
-
description - identifies the database configuration to use
-
columns - The columns in the table to return. Default "*"
-
command - Any SQL command that returns values
- This syntax is maintained for compatibility. You are recommended to use DATABASE_SQL instead.
DATABASE_TABLE
%DATABASE_TABLE{description="table_description" headers="hdr1,hdr2,hdr3" columns="col1,col2,col3"}%
-
description - identifies the database+table configuration to use (required)
-
columns - The columns in the table to return. Default "*"
-
headers - Table headers
- This command requires a table to be specified in the configuration.
- This syntax is maintained for compatibility. You are recommended to use DATABASE_SQL instead.
DATABASE_REPEAT
%DATABASE_REPEAT{description="table_description" table="mytable" columns="col1,col2,col3"}% .... user formatting .... %DATABASE_REPEAT%
-
description - identifies the database+table configuration to use
-
columns - The columns in the table to return. Default "*"
- This command requires a table to be specified in the configuration.
- This syntax is maintained for compatibility. You are recommended to use DATABASE_SQL instead.
DATABASE_EDIT
%DATABASE_EDIT{description="table_description" display_text="HTML link text"}%
Creates a frame and invokes an external database editor.
-
description - identifies the database configuration to use
-
display_text - (optional) The columns in the table to return
Examples
%DATABASE_SQL{description="mysql_user_info" format="| $User | $Select_priv |" header="| *User Name* | *Select Privs* |"}%
You will get back a table with one row for each matching database entry.
Using the format functionality, you can define how the database data is displayed wrapping it in any formatting you choose. For example, if you wanted to create a single table cell containing the information for 3 fields of the Kalendus calendar, you could use the following:
%DATABASE_SQL{description="calendar_events" sql="SELECT * from calendar" format="| $startdate $subject $body |"}%
Or let's say you wanted to display the next two upcoming scheduled events in the Kalendus calendar.
%DATABASE_SQL{description="calendar_events" command="SELECT subject,body,startdate FROM kalendus_event WHERE to_days(startdate) > to_days(now()) order by startdate limit 2" format="$startdate<br />$subject<br />$body"}%
Plugin Installation Instructions
- For an automated installation, run the configure script and follow "Find More Extensions" in the in the Extensions section.
- Or, follow these manual installation steps:
- Download the ZIP file from the extension home on twiki.org (see below).
- Unzip
DatabasePlugin.zip in your twiki installation directory.
- Set the ownership of the extracted directories and files to the webserver user.
- Install the dependencies (if any).
- Plugin configuration and testing:
- Run the configure script and enable the plugin in the Plugins section.
- Configure additional plugin settings in the Extensions section if needed.
- Test if the installation was successful using the examples provided.
- Use
configure to set up the plugin.
Plugin Info
- One line description, is shown in the TextFormattingRules topic:
- Set SHORTDESCRIPTION = Provide access to data in a SQL database
Related Topics: TWikiPreferences, TWikiPlugins |
Copyright © 1999-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.