Database Contrib Package

Provides subroutines useful in writing plugins that access a SQL database

Summary of Contents

This contrib provides subroutines that come in handy when accessing a SQL database.

  • db_connect connects to a SQL database
  • db_connected verifies that a connection exists for a database
  • db_disconnect disconnects from all databases
  • db_allowed tests for additional access permissions

This contrib is used among others, by TWiki:Plugins.DBIQueryPlugin or TWiki:Plugins.TracQueryPlugin. The hope is that we can consolidate the many different database connection schemes currently in use into this single contrib.

Detailed Documentation

This plugin has its origins in Vadim Belman's excellent TWiki:Plugins.DBIQueryPlugin. Additional capabilities have been migrated from other database connection mechanisms deployed in various TWiki plugins.

This plugin uses the database independent access methods in DBI to facilitate access to the SQL database. In the following $dbh refers to the database handle abstraction of DBI.

db_connect ( $dbname ) -> ( $dbh )

Connects to the database indicated by $dbname. The database can then be queried or updated.

db_connected ( $dbname ) -> ( 0|1 )

Finds the database handle for the indicated database.

db_disconnect ( )

Disconnects from all databases that have been connected to in this session.

db_allowed ( $dbname, $topic )

Verifies that the current user is allowed to perform queries that could change the database destructively. (See Access control below).

Database Definition

The databases that one may connect to are defined through the configure script. The connection information is inserted in the DatabaseContrib section.

Example:
   message_board => {
       user => 'dbuser',
       password => 'dbpasswd',
       driver => 'mysql',
       database => 'message_board',
       codepage => 'utf8',
       allow_do => {
      default => [qw(TWikiAdminGroup)],
      'Sandbox.CommonDiscussion' => [qw(TWikiGuest)],
       },
       host => 'localhost',
   }

This example defines a database message_board and the necessary information to access this database. Additional databases can be added, as a comma-separated list of Perl hash refs.

The following parameters can be used to specify a database. The first level key are the database names used in the above functions. Each database has its own set of parameters defined in the hash.

Key Description Default Required
database Database name on the server. none required
user Default database account name. none optional
password Default database account password. none optional
driver DBI driver used to access the server, (such as mysql, sqlite, oracle).1 none required
dsn Complete dsn string to be used when creating the connection. See your DBD driver documentation.TIP With this key defined both database and driver keys are ignored. none optional
init Initialization command to be sent to the database server just after the connection is initiated. none optional
host DB server hostname. localhost optional
codepage Client-side codepage of this connection.2 none optional
usermap Hash ref mapping TWiki users or groups to database accounts. See Access control below. none optional
allow_do Additional topic-level access control support (see Access control below). default => [qw(TWikiAdminGroup)] optional

1 Only MySQL support has been tested. 2 Only MySQL support provided for this feature. Support for other servers is not implemented yet.

Access Control

The contrib relies on TWiki for authentication and basic access control, and the database server for enforcing security.

Database server-side access control works through mapping TWiki users into database server user accounts by means of the usermap key in the configuration setting (see Database definition above).

  1. Check if TWiki user has an enty in usermap.
  2. Check if TWiki user is a member of a group that has an entry in usermap.
  3. Use user and password keys of the database definition.
  4. If a user was found, connect to the database.

Additional controls are possible at a topic level, if needed. The configuration key allow_do maps individual topics into lists of users or groups with access permission for a query executed from that topic. The key default is used, if a matching key cannot be found for the given topic.

In the example above, members of the TWikiAdminGroup may perform queries onany topic; TWikiGuest is allowed only for topic Sandbox.CommonDiscussion.

Settings

Settings are stored as preferences variables. To reference a setting write %<plugin>_<setting>%, e.g. %DATABASECONTRIB_DEBUG%

  • One line description:
    • Set SHORTDESCRIPTION = Provides subroutines useful in writing plugins that access a SQL database

Installation Instructions

Note: You do not need to install anything on the browser to use this module. The following instructions are for the administrator who installs the module on the TWiki server.

  • 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 Plugins home (see below).
    • Unzip DatabaseContrib.zip in your twiki installation directory. Content:
      File: Description:
      data/TWiki/DatabaseContrib.txt Contrib topic
      lib/TWiki/Contrib/DatabaseContrib.pm Contrib Perl module
      lib/TWiki/Contrib/DatabaseContrib/Config.spec Configuration specification
      lib/TWiki/Configure/Types/TEXT.pm Perl module supporting text areas in configure script
    • Set the ownership of the extracted directories and files to the webserver user.

  • Contrib configuration and testing:
    • Verify access and ownership settings for the new scripts.
    • Edit your .htaccess file to require a valid user for the savesection script (if needed).

Contrib Info

Author: TWiki:Main.ThomasWeigert
Copyright: © 2009 TWiki:Main.ThomasWeigert
© 2009-2011 TWiki:TWiki.TWikiContributor
License: GPL (GNU General Public License)
Plugin Version: 2011-05-14
Change History:  
2011-05-14: TWikibug:Item6701: Small fix in Config.spec and MANIFEST -- TWiki:Main.PeterThoeny
2011-03-13: TWikibug:Item6661: Import into SVN; adding build stuff -- TWiki:Main.DipuDeshmukh
2009-05-20: Initial version
CPAN Dependencies: CPAN:DBI
Other Dependencies: Libraries CPAN:DBI depends on
Perl Version: 5.005
Plugin Home: http://TWiki.org/cgi-bin/view/Plugins/DatabaseContrib
Feedback: http://TWiki.org/cgi-bin/view/Plugins/DatabaseContribDev
Appraisal: http://TWiki.org/cgi-bin/view/Plugins/DatabaseContribAppraisal

Related Topics: TWikiContribs, TWiki:Plugins.DatabasePlugin, TWiki:Plugins.DBIQueryPlugin, TWiki:Plugins.TracQueryPlugin, TWiki:Plugins.PeerReviewPlugin

Topic revision: r0 - 2011-05-15 - TWikiContributor
 
This site is powered by the TWiki collaboration platform Powered by Perl This site is powered by the TWiki collaboration platformCopyright © 1999-2024 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.DatabaseContrib.