DatabasePlugin < TWiki

Tags: view all tags
---+!! 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.
-->
<sticky><div style="float:right; background-color:#EBEEF0; margin:0 0 20px 20px; padding: 0 10px 0 10px;">
%TOC{title="Page contents"}%
</div></sticky>

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

=%<nop>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 %SYSTEMWEB%.FormattedSearch for more on how =format=, =header= and =separator= work.

---+++ DATABASE_SQL_TABLE

=%<nop>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
   * %X% This syntax is maintained for compatibility. You are recommended to use DATABASE_SQL instead.

---+++ DATABASE_SQL_REPEAT

=%<nop>DATABASE_SQL_REPEAT{description="description" command="..SQL COMMAND.." columns="col1,col2,col3"}% .... user formatting .... %<nop>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
   * %X% This syntax is maintained for compatibility. You are recommended to use DATABASE_SQL instead.

---+++ DATABASE_TABLE

=%<nop>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
   * %X% This command *requires* a table to be specified in the configuration.
   * %X% This syntax is maintained for compatibility. You are recommended to use DATABASE_SQL instead.

---+++ DATABASE_REPEAT

=%<nop>DATABASE_REPEAT{description="table_description" table="mytable" columns="col1,col2,col3"}% .... user formatting .... %<nop>DATABASE_REPEAT%=

   * =description= - identifies the database+table configuration to use
   * =columns= - The columns in the table to return. Default "*"
   * %X% This command *requires* a table to be specified in the configuration.
   * %X% This syntax is maintained for compatibility. You are recommended to use DATABASE_SQL instead.

---+++ DATABASE_EDIT

=%<nop>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

=%<nop>DATABASE_SQL{description="mysql_user_info" format="| $User | $Select_priv |" header="| <nop>*User Name* | <nop>*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:

=%<nop>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.

=%<nop>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&lt;br />$subject&lt;br />$body"}%=

---++ Plugin Installation Instructions

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server where TWiki is running.

Like many other TWiki extensions, this module is shipped with a fully
automatic installer script written using the Build<nop>Contrib.
   * If you have TWiki 4.2 or later, you can install from the =configure= interface (Go to Plugins->Find More Extensions)
      * See the [[http://twiki.org/cgi-bin/view/Plugins/BuildContribInstallationSupplement][installation supplement]] on TWiki.org for more information.
   * If you have any problems, then you can still install manually from the command-line:
      1 Download one of the =.zip= or =.tgz= archives
      1 Unpack the archive in the root directory of your TWiki installation.
      1 Run the installer script ( =perl &lt;module&gt;_installer= )
      1 Run =configure= and enable the module, if it is a plugin.
      1 Repeat for any missing dependencies.
   * If you are *still* having problems, then instead of running the installer script:
      1 Make sure that the file permissions allow the webserver user to access all files.
      1 Check in any installed files that have existing =,v= files in your existing install (take care *not* to lock the files when you check in)
      1 Manually edit !LocalSite.cfg to set any configuration variables.

%IF{"defined 'SYSTEMWEB'" else="<div class='twikiAlert'>%X% WARNING: SYSTEMWEB is not defined in this TWiki. Please add these definitions to your %MAINWEB%.TWikiPreferences, if they are not already there:<br><pre>   * <nop>Set SYSTEMWEB = %<nop>TWIKIWEB%<br>   * <nop>Set USERSWEB = %<nop>MAINWEB%</pre></div>"}%

   * Use =configure= to set up the plugin.

---++ Plugin Info

   * One line description, is shown in the %SYSTEMWEB%.TextFormattingRules topic:
      * Set SHORTDESCRIPTION = Provide access to data in a SQL database

|  Plugin Author: | TWiki:Main.TaitCyrus |
|  Copyright: | &copy; 2002-2007 TWiki:Main.TaitCyrus <br /> &copy; 2007-2011 TWiki:TWiki.TWikiContributor |
|  License: | GPL ([[http://www.gnu.org/copyleft/gpl.html][GNU General Public License]]) |
|  Plugin Version: | 22417 (2011-11-11) |
|  Change History: | <!-- versions below in reverse order -->&nbsp; |
|  2011-11-11: | TWikibug:Item6800: Fix for using qw(...) as parentheses, which is deprecated in Perl 5.14 -- TWiki:Main.PeterThoeny |
|  2011-05-14: | TWikibug:Item6701: Small fix in Config.spec -- TWiki:Main.PeterThoeny |
|  2011-02-19: | TWikibug:Item6638: Doc improvements; changing TWIKIWEB to SYSTEMWEB -- TWiki:Main.PeterThoeny |
|  17 Sep 2007 | Bugs:Item4343 Minor corrections to Config.spec - TWiki:Main.CrawfordCurrie |
|  18 March 2007 | Rewritten for efficiency and clarity, and added DATABASE_SQL - TWiki:Main.CrawfordCurrie |
|  5 May 2003 (v1.3) | Add support for the primary DB to be in a local file instead of in a DB.  It is acknowledged that this reduces security somewhat<BR>Also added support for Oracle (by adding in the concept of a SID) |
|  20 Mar 2002 (v1.2) | Added table editing ability |
|  18 Feb 2002 (v1.11): | Removed hard coded $debug=1; |
|  16 Feb 2002 (v1.1): | Added the two REPEAT functions |
|  20 Jan 2002 (v1.0): | Initial version  |
|  CPAN Dependencies: | CPAN:DBI |
|  Other Dependencies: | phpmyadmin (optional, to support DATABASE_EDIT) |
|  Perl Version: | 5.0 (tested with 5.6.1 [mysql] and 5.8.0 [Oracle and Local] ) |
|  Plugin Home: | http://TWiki.org/cgi-bin/view/Plugins/%TOPIC% |
|  Feedback: | http://TWiki.org/cgi-bin/view/Plugins/%TOPIC%Dev |

__Related Topics:__ %SYSTEMWEB%.TWikiPreferences, %SYSTEMWEB%.TWikiPlugins
Topic revision: r4 - 2016-07-04 - TWikiAdminUser
Account
- Log In
Edit
Attach
Copyright © 1999-2025 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
Note: Please contribute updates to this topic on TWiki.org at TWiki:TWiki.DatabasePlugin.