DBIQueryPlugin

Make complex database queries using DBI Perl module

Overview

This plugin is intended to provide Foswiki with ability to make complex database requests using DBI Perl module.

Any help with this documentation is very much welcome as it's really pretty far from being ideal. Thanks a lot!

Syntax Rules

Examples:

%DBI_QUERY{"db_identifier" ...}%
SELECT ...
.header
head
.body
%column%
%DBI_SUBQUERY{"name"}%
.footer
footer
%DBI_QUERY%

%DBI_DO{"db_identifier" ...}%
# Some Perl code.
%DBI_DO%

%DBI_DO{"db_identifier" topic="SomeTopic" script="some_script"}%

%DBI_CALL{"subquery"}%

%DBI_CODE{...}%
# Some Perl Code
%DBI_CODE%

Syntax

DBI_QUERY -- make simple requests to a database.

Each query consist of two parts: a query statement (mostly is a SELECT statement) and output formatting filters. SQL statement starts just after the leading %DBI_QUERY{...}% declaration. The filters are defined by .header, .body, and .footer keywords each starting at the beginning of line. Their meaning shall be obvious from their name:

Declaration	Description
.header	It is prepended to the query output once.
.body	It is repeated for each row of data being fetched from the database.
.footer	It is appended to the query output.

Parameters

Parameter	Description	Default	Required
"db_identifier"	Database ID as defined in the plugin configuration. See DatabaseContrib configuration section.	none	required
subquery="name"	Defines a subquery which does not produce immediate result but could be used from inside another query -- see %DBI_SUBQUERY%	none	optional
unquoted="col1 col2 ..."	List of columns to be left unquoted in the output. Read more in Quoting of Values section.	none	optional
protected="col1 col2 ..."	List of columns to be protected from processing by Foswiki engine.	none	optional

A small note on protected parameter. Say, one has an arbitrary data in a displayed column which could contain any kind of text strings. What happens if such a string looks like a Foswiki variable or macro? It's gonna be expaded for sure. Adding a column to the protected list makes data from this column displayed as is, unmodified.

DBI_SUBQUERY -- call a %DBI_XXX% subquery

%DBI_SUBQUERY{"name"}% (aliased as %DBI_EXEC%) doesn't exist as a separate variable but only as a part of header/body/footer processing mechanism. It's functionality is described in Variable Expansion and Subqueries sections of DBIQueryPlugin documentation.

Examples

%DBI_QUERY{"db_identifier" ...}%
SELECT ...
.header
head
.body
%column%
%DBI_SUBQUERY{"name"}%
.footer
footer
%DBI_QUERY%

DBI_DO -- do complex processing with Perl code.

As a matter of fact, %DBI_DO{...}% is nothing but a Perl script stored withing Foswiki. There are three ways to store it:

1. In place, just between starting %DBI_DO{...}% and ending %DBI_DO%.
2. Several scripts in a topic using %DBI_CODE{...}%.

Parameters

Parameter	Description	Default	Required
"db_identifier"	Database ID as defined in DatabaseContrib configuration. See database configuration section.	none	required
multivalued="par1 par2 ..."	Defines HTTP parameters expected to contain several values. These could be, for instance, either values from checkboxes or multiselection lists.	none	optional
subquery="name"	Defines a subquery which does not produce immediate result but could be used from inside another query. See %DBI_SUBQUERY% of %DBI_QUERY%	none	optional
topic="SomeTopic"	Topic to read script from.	none	optional
script="name"	Specific script defined by its name from several stored in a topic.	none	optional
name="do_name"	Informational parameter which defines in-place stored script name. Useful for debugging and error messages.	none	optional

DBI_CALL -- directly call a subquery.

%DBI_CALL{...}% directly calls a subquery and pass over optional named parameters simulating %DBI_SUBQUERY% call.

Parameters

Parameter	Description	Default	Required
"subquery"	Subquery to call.	none	required

Optional parameters are transfered to the subquery as if they are columns of a database record. Consider the following example:

%DBI_CALL{"example" uid="12"}%

%DBI_QUERY{"db_identifier" subquery="example"}%
SELECT
    name
  FROM
    Users
  WHERE
    id = %uid%
.header
....
%DBI_QUERY%

Read more in Variable Expansion section.

DBI_CODE -- storing a script for %DBI_DO%

%DBI_CODE{...}% is used for keeping several %DBI_DO% scripts within single topic. A script is kept between starting %DBI_CODE{...}% and ending %DBI_CODE%. Output is formatted as a table representing script's name and code.

Parameters

Parameter	Description	Default	Required
"script_name"	Name of the script. Must be unique within topic.	none	required

Note: Special support is provided for source highlighting plugins using %CODE% notation.

How it works

DBI_QUERY

This plugin has been written with the idea in mind that table is not the only way to represent database content. Therefore some more flexibility is required in order to format a query result. Yet, what could provide more control over the output than templates keeping it all as simple as possible?

With this view in mind we come to the following procedure:

1. Every query definition within topic is parsed and stored for further processing. This is done in two major steps:
   1. Query statement is extracted from the definition.
   2. Every newline within .header, .body, and .footer gets changed with space except for the last ones. They're removed. Whereas newline is needed \n escape sequence must be used. Consequently, \\n is translated into \n.
2. All queries are processed except for those declared as subqueries:
   1. .header filter is expanded with variable expansion mechanism and put into the output.
   2. The query statement is expanded using DBIQueryPlugin and Foswiki variable expansion mechanisms in the order they are mentioned here.
   3. Database is queried and data is fetched row-by-row. Each row data get quoted and then used for setting DBIQueryPlugin variables. .body filter is expanded using these values.
   4. .footer filter is expanded with DBIQueryPlugin mechanism and put into the output.
   5. Afterwards we let Foswiki to deal with the output (expand variables, pass it through other plugins, whatsoever).

Variable Expansion

The first step of expansion is done by changing every %column% variable found in a text being expanded with corresponding value from the database. Variable names are in fact table column names as they're declared in the SQL statement and returned by [[http://search.cpan.org/~timb/DBI-1.48/DBI.pm#fetchrow_hashref][DBI module]]. NAME_lc case conversion performed so that every name is in lowercase. For instance, the following SELECT:

SELECT
    Name,
    PersonalID,
    SomeOtherInfo
  FROM
    PersonData

would provide us with variables %name%, %personalid%, %someotherinfo%.

There are some special cases like MySQL's SHOW CREATE PROCEDURE where column names may contain spaces within them. These spaces are changed with underscore sign making it possible to refer to them as to database columns. I.e. 'Create Procedure' field may be referred as %create_procedure%.

The second step is subquery processing. %DBI_SUBQUERY{"subqueryname"}% statements are replaced with output from corresponding subqueries. All currently defined variables are passed to the subquery making it possible to use them for SQL statement, header and footer expansion.

Quoting of Values

Values fetched from database are quoted using CGI::escapeHTML() unless otherwise ordered by unquoted parameter. Then every newline character is changed with Foswiki variable %BR%.

Subqueries

Subqueries are processed in same manner as common queries. The only thing which makes them slightly different in behaviour is the fact that they can use column values (variables) from the parent queries. It is also possible to have a chain of subqueries: top_query -> subquery1 -> subquery2 -> ..., in which case all variables from all the calling queries are accessible.

For instance, in the following code:

%DBI_QUERY{...}%
SELECT
    col1, col2
  FROM
    someTable
  WHERE
    col3 = %parent_query_col1%
.body
...
%DBI_QUERY%

we choose only the rows which are somehow related to a row in a parent query. Of course, relatively similar approach would be to use nested SELECT in the parent query SQL statement. Yet, this would be faster. But there are old versions of MySQL where nested SELECT is not supported. And there are situations when some more output formatting is needed. Or one could form header and/or footer using data contained in database.

Note: Subqueries could also be called recursively. Although a single query could not be called more than 100 times in a row. This number is presently hardcoded but will become part of plugin settings in future.

DBI_DO

First of all it shall be stated that %DBI_DO% can implement all required functionality. In other words, one could say that %DBI_QUERY% becomes obsolete. This is obvious from the syntax description. But it also implies that %DBI_DO% is:

• a security risk (see Access Control);
• too complicated for most queries;

Besides, %DBI_QUERY% hides quite a number of boring implementation details from a user.

So, let's define %DBI_DO% as a last resort method when nothing else could do the job. Or, in other words, it is another way of creating a Foswiki application.

Implementation

As it was stated in syntax section, %DBI_DO% can fetch a script from another topics which would either represent the whole script or contain %DBI_CODE% declarations. In both cases the script is visible on the topic's page. For instance, the following declaration:

%DBI_CODE{"test"}%
if ($varParams{test}) {
    $rc = "This is test.";
} else {
    $rc = "This is for real.";
}
%DBI_CODE%

would output table like this:

Script name	test
Script code	if ($varParams{test}) { $rc = "This is test."; } else { $rc = "This is for real."; }

One can make it looking a bit more pretty with a kind of source highlighting plugin¹:

%DBI_CODE{"test"}%
%CODE{"perl"}%
if ($varParams{test}) {
    $rc = "This is test.";
} else {
    $rc = "This is for real.";
}
%ENDCODE%
%DBI_CODE%

Script name	test
Script code	if ($varParams{test}) { $rc = "This is test."; } else { $rc = "This is for real."; }

¹ The sample has been generated using outdated SourceHighlighPlugin. Other plugins may generate different output.

%DBI_DO% knows about existence of %CODE%/%ENDCODE% and attempts to strip these tags out when the script is been fetched from a topic. After that Perl code becomes a part of an anonymous sub. Several variables are available to the code:

Variable	Description
$dbc	DatabaseContrib object.
$dbh	Database connection handle.
$request	A request object as returned by Foswiki::Func::getRequestObject(). Mostly compatible with CGI module API.
$varParams	Parameters specified in %DBI_DO{...}%. User can put any number of addition parameters there besides those described in syntax section.
$dbRecord	Last fetched by %DBI_QUERY% database record or %DBI_CALL% parameters.
%httpParams	HTTP parameters as returned by CGI::param() method. Note the multivalued parameter in the syntax section.

Since the sub is executed within plugin's module namespace all internal functions and variables are directly accessible. The most useful of them are described below.

There is one special variable $rc. A value assigned to it is the value returned by sub and put into the output then. In this way one could display a error message or notification or form any kind of Foswiki/HTML code.

Useful functions

DatabaseContrib API is available to a script. Additionally the following plugin functions could be handy within a script:

subQuery($subquery, $dbRecord) -> $text

Implements %DBI_SUBQUERY% and %DBI_CALL%. $subquery is the name of subquery to be called. $dbRecord has the same meaning as corresponding sub parameter. Returns output of the subquery.

expandColumns($text, $dbRecord) -> $text

Expands variables within $text as described in DBIQueryPlugin Expansion and returns the result.

protectValue($text) -> $text

Returns $text modified in a way that prevents it from Foswiki processing.

wikiErrMsg(@msg) -> $errorMessage

Formats error messages.

Database connection configuration

This plugin relies on the DatabaseContrib to provide the connection to a DBI database. Please see the contrib for documentation of how to specify the database connection.

Below is an example of the configuration of two database connections, connection1 and test, to be inserted into the DatabaseContrib section of the configure script.

    connection1 => {
        usermap => {
            AdminGroup => {
                user => 'dbuser1',
                password => 'dbpassword1',
            },
            SpecialGroup => {
                user => 'specialdb',
                password => 'specialpass',
            },
        },
        user => 'guest',
        password => 'guestpass',
        driver => 'mysql',
        database => 'some_db',
        codepage => 'koi8r',
        host => 'your.server.name',
    },
    test => {
        usermap => {
            AdminGroup => {
                user => 'dbuser2',
                password => 'dbpassword2',
            },
            SomeUser => {
                user => 'someuser',
                password => 'somepassword',
            },
        },
        allow_do => {
            default => [qw(AdminGroup)],
            'Sandbox.SomeUserSandbox' => [qw(AdminGroup SpecialGroup)],
        },
        allow_query => {
            'Sandbox.SomeQueryTopic' => [qw(WikiGuest)],
        },
        #user => 'nobody',
        #password => 'never',
        driver => 'mysql',
        database => 'test',
        # host => 'localhost',
    }

Access Control

This plugin relies on the DatabaseContrib [[DatabaseContrib#AccessControl][access control] API. For middle-level access control DBIQueryPlugin uses two keys to check for two levels of access:

Key name	Variable	Inherits from	Description
allow_do	[[#DbiDoSyntax][=DBI_DO=]		Access granted to possibly destructive actions
allow_query	DBI_QUERY,DBI_CALL	allow_do	Access granted for querying only

Drawback and problems

Working with a database isn't a simple task, in common. With this plugin I was trying to make it both as simple as possible and flexible same time. Balancing between these two extremes led to some compromises and side effects.

The biggest compromise was usage of Perl inlines for %DBI_DO%. The first approach was to make it working much like %DBI_QUERY%, using sections of declarations. But the more questions like:

• how to check data consistency?
• how to validate data?
• how to generate error messages?

And several others of the kind was arising, the more final structure was looking like a new language. So, why developing a new one when Perl is out there? But then again, as it was mentioned before, this way is not secure-enough and an administrator must take serious considerations before allowing usage of %DBI_DO% to a user.

The other issue is about plugin execution order. As one can see from MessageBoard example, attached to this topic, usage of other plugins could significantly improve control over DBIQueryPlugin output. However, it is not guaranteed that another plugin would not be called in first place causing unpredictable results like unwanted changes in a Perl script.

Considering this issue the decision was made that DBIQueryPlugin must act as a preprocessor. For those familiar with Foswiki guts, it does all the job in beforeCommonTagsHandler() routine. This approach has three major drawbacks:

• First of all, it doesn't really follow the guidelines.
• It breaks common logic of page analysis. Consider the following example:

         %CALC{"$SET(var,1)"}%
         %DBI_QUERY{"..."}%
         SELECT ...
           WHERE
             field = %CALC{"$GET(var)"}%
         %DBI_QUERY%
         

One will not get what would be expected because at the time %CALC{"$GET(var)"}% is executed %CALC{"$SET(var,1)"}% has not been called yet! The only way to have it be done properly is to put the latter just next to %DBI_QUERY{...}% line.

• %INCLUDE{}% would not work because beforeCommonTagsHandler() is not called for included topics.

The last issue was the cause to implement classic plugin handling when it is requested during the inclusion procedure. Possible side effects of this hack are not studied yet and may cause some headache.

Plugin Settings

Databases available to end user have to be preconfigured with DatabaseConfig database configuration using Foswiki configure script.

Additionally the following configuration keys of {Plugins}{DBIQueryPlugin} configuration section are available:

Configuration Key	Default value	Description
Debug	false	Generate additional debug output
maxRecursionLevel	100	How many nested subquery calls of a same query are allowed before it's considered a too deep recursion

Installation

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. Use "Find More Extensions" to get a list of available extensions. Select "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 http://foswiki.org/Support/ManuallyInstallingExtensions for more help.

Info

Dependencies:

Name	Version	Description
DBI	>=1.5	Database independent interface
CGI	>4.0	CGI interface
Error	>=0.17	try/catch implementation
Foswiki::Contrib::DatabaseContrib	>=1.01	DatabaseContrib
Foswiki::Func	>=0	Basic API
Foswiki::Plugins	>=0	Plugins API