DatabasePlugin < TWiki

---+!! 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

   * For an __automated installation__, run the [[%SCRIPTURL{configure}%][configure]] script and follow "Find More Extensions" in the in the __Extensions__ section.
      * See the [[http://twiki.org/cgi-bin/view/Plugins/BuildContribInstallationSupplement][installation supplement]] on TWiki.org for more information.

   * Or, follow these __manual installation__ steps:
      * Download the ZIP file from the extension home on twiki.org (see below).
      * Unzip ==%TOPIC%.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 [[%SCRIPTURL{configure}%][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 %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-2021 TWiki:TWiki.TWikiContributor |
|  License: | GPL ([[http://www.gnu.org/copyleft/gpl.html][GNU General Public License]]) |
|  Plugin Version: | 2021-04-11 |
|  Change History: | <!-- versions below in reverse order -->&nbsp; |
|  2021-04-11: | TWikibug:Item7927: Copyright update to 2021 |
|  2019-12-26: | TWikibug:Item7851: Allow DATABSE_SQL to span multiple lines -- TWiki:Main.SteffenLoeffler |
|  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
This topic: TWiki > DatabasePlugin
Topic revision: r2 - 2024-03-02 - TWikiAdminUser
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.