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.
-->
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. 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).
- Check if TWiki user has an enty in
usermap.
- Check if TWiki user is a member of a group that has an entry in
usermap.
- Use
user and password keys of the database definition.
- 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
Related Topics: TWikiContribs, TWiki:Plugins.DatabasePlugin, TWiki:Plugins.DBIQueryPlugin, TWiki:Plugins.TracQueryPlugin, TWiki:Plugins.PeerReviewPlugin | 
Copyright © 1999-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.