DBIPlugin

Database middle layer to manage connections and schemes

Description

This plugin offers a middle layer for Foswiki extensions to ease connecting to SQL databases and manage schemes of table and index definitions. The idea is to keep things as lean as possible without imposing any additional structure. DBIPlugin will maintain a plugin's database scheme to make sure it is created and updated as required.

A plugin sub-classes Foswiki::DBI::Schema which is then loaded before connecting to a database. While connecting to the database the schema then is added to the database.

DBIPlugin supports any database for which a DBD perl driver is available. A plugin may support multiple databases where the schemes differ in parts. For example the schema for SQLite differs from the one for MariaDB, MySQL or PostgreSQL. The plugin will then implement:

• Foswiki::DBISchema::SQLite
• Foswiki::DBISchema::MariaDB
• Foswiki::DBISchema::MySQL
• Foswiki::DBISchema::PostgreSQL

The syntax of each of them are custom tailored towards the respective database vendor. Note however that from there on it is the plugin's responsibility to cope with further differences among databases beyond just schema definitions.

Have a look at DBIPluginPerlAPI for further information.

Creating a Foswiki database

The base assumption of DBIPLugin is that all plugins share a single database that all tables and indexes are created within. This means that plugins must pay attention to naming them. Best practices is to prefix any table or index with the name of the plugin. For example LikePlugin stores its data in a table called LikePlugin_likes.

Before configuring DBIPlugin you need to create a database and a user/password for this plugin (except for SQLite). For instance a shell script like this will do:

#!/bin/sh

database_server=$1
database_name=$2
database_user=$3
database_password=$4

echo "creating database $database_name on $database_server"
cat <<HERE | mysql -h $database_server
CREATE USER IF NOT EXISTS $database_user@$database_server IDENTIFIED BY '$database_password';
CREATE DATABASE IF NOT EXISTS $database_name;
GRANT ALL ON $database_name.* TO $database_user@$database_server;
FLUSH PRIVILEGES;
HERE

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.

Open configure, and open the "Extensions" section. "Extensions Operation and Maintenance" Tab -> "Install, Update or Remove extensions" Tab. Click the "Search for Extensions" button. Enter part of the extension name or description and press search. Select the desired extension(s) and click install. If an extension is already installed, it will not show up in the search results.

You can also install from the shell by running the extension installer as the web server user: (Be sure to run as the webserver user, not as root!)

cd /path/to/foswiki
perl tools/extension_installer <NameOfExtension> install

If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See https://foswiki.org/Support/ManuallyInstallingExtensions for more help.

Dependencies

Name	Version	Description
DBI	>=1.0	Required
DBD::MariaDB	>=1.54	Recommended
DBD::SQLite	>=1.54	Optional
DBD::Pg	>=3	Optional
DBD::mysql	>=4	Optional

Change History

17 Jan 2024	fixed mechanism upgrading database schemes; better default encoding of databases
27 Apr 2022	initial release