Database Contrib Package

<--
   Contributions to this contrinb module are appreciated. Please update the contrib page at
   http://twiki.org/cgi-bin/view/Plugins/DatabaseContrib or provide feedback at
   http://twiki.org/cgi-bin/view/Plugins/DatabaseContribDev.
   If you are a TWiki contributor please update the contrib in the SVN repository.
-->

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. 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

¹ Only MySQL support has been tested. ² 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

<-- versions below in reverse order -->

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