Difference: DBIQueryPlugin (1 vs. 9)

Revision 903 Jun 2006 - Main.VadimBelman

Line: 1 to 1
 

DBI Query Plugin

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

Line: 147 to 147
 would provide us with variables %name%, %personalid%, %someotherinfo%.
Added:
>
>
There are some special cases like SHOW CREATE PROCEDURE query where column names may contain spaces within them. These spaces are changed with undersocre sign making it possible to refer to them as to database columns. I.e. 'Create Procedure' 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.

Line: 311 to 313
 
  1. If none of the above is fulfilled then user and password keys of plugin configuration are used. If there is no user key then plugin refuses connection.
  2. Plugin attempts to connect.
Changed:
<
<
Additional control implemented for %DBI_DO%. Another configuration key named allow_do introduced which maps individual topic into list of users or groups with permission to use the feature. Special subkey default of this key defines default mapping. The implicit default is TWikiAdminGroup.
>
>
Additional control implemented for %DBI_DO%. Another configuration key named allow_do introduced which maps individual topic into list of users or groups with permission to use the feature. Special subkey default of this key defines default mapping. There is no default value for this subkey.

TIP WARNING! Using subkey default of allow_do key must be considered as a serious security whole and better be avoided.

 In the following example:
Line: 387 to 391
 
password Default database account password. none optional
database Database name on the server. none required
driver DBI driver used to access the server. none required
Added:
>
>
dsn Complete dsn string to be used when creating connection. See your DBD driver documentation.

TIP Note: 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 initiated. none optional
 
host DB server hostname. localhost optional
codepage Client-side codepage of this connection.* none optional
usermap Maps particular TWiki users or groups into database accounts. See Access Control section. none optional
Line: 429 to 435
 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 create some headache.
Added:
>
>

Changelog

  • 1.2
    • Added 'dsn' and 'init' parameters of configuration file.
    • Character set support for PostgreSQL? .
    • No default value for 'allow_do' parameter of configuration file.
    • Support for column names with spaces.
 

Plugin Settings

Plugin settings are stored as preferences variables. To reference

Line: 458 to 472
 
Plugin Author: TWiki:Main.VadimBelman
Plugin Version: 13 Oct 2005
Change History:
<-- versions below in reverse order -->
 
Added:
>
>
3 Jun 2006 1.2
 
17 Oct 2005: 1.1
13 Oct 2005: Initial version
CPAN Dependencies: DBI, Error

Revision 821 Feb 2006 - Main.VadimBelman

Line: 1 to 1
Changed:
<
<

DBI Query Plugin

>
>

DBI Query Plugin

 This plugin is intendent to provide TWiki with ability to make complex database requests using DBI Perl module.
Added:
>
>
 

Syntax Rules

  • Syntax:
Line: 471 to 471
 Related Topics: TWikiPreferences, TWikiPlugins
Changed:
<
<
-- TWiki:Main.VadimBelman - 13 Oct 2005
>
>
-- TWiki:Main.VadimBelman - 20 Feb 2006
 

Revision 720 Feb 2006 - Main.VadimBelman

Line: 1 to 1
 

DBI Query Plugin

Line: 395 to 395
  * Only MySQL support provided for this feature. Support for other servers is not implemented yet.
Added:
>
>

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 quiestions 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 if Perl is here? 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 significally 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 who understand, 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 under %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 create some headache.

 

Plugin Settings

Plugin settings are stored as preferences variables. To reference

Revision 618 Feb 2006 - Main.VadimBelman

Line: 1 to 1
 

DBI Query Plugin

Line: 48 to 48
 
  • Parameters:

Parameter Description Default Required
Changed:
<
<
"db_identifier" Database ID as defined in the plugin configuration. See Plugin Installation section. none required
>
>
"db_identifier" Database ID as defined in the plugin configuration. See plugin configuration section. none required
 
subquery="name" Defines a subquery which does not produce immediate result but could be used from inside another query 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 TWiki engine. none optional
Line: 61 to 61
 As a matter of fact, %DBI_DO{...}% is nothing but a Perl CGI script stored withing TWiki. There are three ways to store it:

  1. In place, just between starting %DBI_DO{...}% and ending %DBI_DO%.
Changed:
<
<
  1. In a separate topic which is the script on its own.
>
>
  1. In a separate topic which would be then the script on its own.
 
  1. Several scripts in a topic using %DBI_CODE{...}%.

  • Parameters
Line: 100 to 100
 %DBI_QUERY%
Changed:
<
<
ALERT! Read more in DBIQueryPlugin Expansion section.
>
>
ALERT! Read more in Variable Expansion section.
 

DBI_CODE

Line: 111 to 111
 
Parameter Description Default Required
"script_name" Name of the script. Must be unique within topic. none required
Changed:
<
<
TIP Note: Special support is provided for TWiki:Plugins.SourceHighlightPlugin. Read more in DBI_DO section.
>
>
TIP Note: Special support is provided for SourceHighlightPlugin. Read more in DBI_DO implementation section.
 

How it works.

Line: 125 to 125
 
    1. Query statement is exctracted 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.
  1. All queries are processed except for those declared as subqueries:
Changed:
<
<
    1. .header filter is expanded with DBIQueryPlugin mechanism and put into the output.
>
>
    1. .header filter is expanded with variable expansion mechanizm and put into the output.
 
    1. The query statement is expanded using DBIQueryPlugin and TWiki variable expansion mechanisms in the order they are mentioned here.
    2. 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.
    3. .footer filter is expanded with DBIQueryPlugin mechanism and put into the output.
    4. Afterwards we let TWiki to mangle with the output (expand variables, pass it through other plugins, whatsoever).

Changed:
<
<

DBIQueryPlugin Expansion

>
>

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 DBI module. NAME_lc case conversion performed so that every name is in lowercase. For instance, the following SELECT:
Line: 196 to 196
 TIP 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.
Added:
>
>
 

DBI_DO

First of all it shall be stated that %DBI_DO% could 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:

Line: 209 to 210
 

Implementation

Changed:
<
<
Perl code used by a particular %DBI_DO% becomes a part of an anonymous sub. Several variables are available to the code:
>
>
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.";
}

It would look much better with SourceHighlightPlugin:

%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.";
}

%DBI_DO% knows about existence of %CODE%/%ENDCODE% and attempts to strip these tags out just after the script has 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
$dbh Database connection handle.
Line: 220 to 287
 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.
Changed:
<
<
There is one special variable $rc where one puts a value which is returned by the sub then. The value is put into the output then. In this way one could display a error message or notification or form any kind of TWiki/HTML code.
>
>
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 TWiki/HTML code.
 

Useful functions

Line: 265 to 332
 ALERT! Note: The access control scheme is very much raw at the moment and may be subject to change in future plugin version. Yet, comments are welcome!

Changed:
<
<

DBIQueryPlugin.cfg Configuration File

>
>

DBIQueryPlugin.cfg Configuration File

Most of plugin configuration is done with DBIQueryPlugin.cfg file located in lib/ subdir under the TWiki root dir. Basicaly, this file contains nothing but initialization code for %dbi_connections hash. Typically, it would look like the following code:

%dbi_connections = (
	 connection1 => {
		  usermap => {
				TWikiAdminGroup => {
					 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 => {
				TWikiAdminGroup => {
					 user => 'dbuser2',
					 password => 'dbpassword2',
				},
				SomeUser => {
					 user => 'someuser',
					 password => 'somepassword',
				},
		  },
		  allow_do => {
				default => [qw(TWikiAdminGroup)],
				'Sandbox.SomeUserSandbox' => [qw(TWikiAdminGroup SpecialGroup)],
		  },
		  #user => 'nobody',
		  #password => 'never',
		  driver => 'mysql',
		  database => 'test',
		  # host => 'localhost',
	 },
);

First-level keys of the hash are connection names used by %DBI_QUERY%, %DBI_DO% (db_identifier parameter). Each connection has it's own set of parameters defined by second-level keys. These are:

Key Description Default Required
user Default database account name. none optional
password Default database account password. none optional
database Database name on the server. none required
driver DBI driver used to access the server. none required
host DB server hostname. localhost optional
codepage Client-side codepage of this connection.* none optional
usermap Maps particular TWiki users or groups into database accounts. See Access Control section. none optional
allow_do Specifies users or groups allowed to use %DBI_DO% in particular topics. See Access Control section. default => [qw(TWikiAdminGroup)] optional

* Only MySQL support provided for this feature. Support for other servers is not implemented yet.

 

Plugin Settings

Revision 517 Feb 2006 - Main.VadimBelman

Line: 1 to 1
 

DBI Query Plugin

Line: 36 to 36
 

DBI_QUERY

Changed:
<
<
Each query consist of two parts: a query statement (SELECT) 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:
>
>
Each query consist of two parts: a query statement (SELECT) 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.
Line: 59 to 55
 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 a TWiki variable is found in a string? It gets expanded by TWiki, for sure. Adding this columns to the protected list prevents the expansion. Precisely saying, the whole purpose of protection is displaying of data as is, without any modification.
Added:
>
>
 

DBI_DO

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

Line: 114 to 111
 
Parameter Description Default Required
"script_name" Name of the script. Must be unique within topic. none required
Changed:
<
<
ALERT! Note: Special support is provided for TWiki:Plugins.SourceHighlightPlugin. Read more in DBI_DO section.
>
>
TIP Note: Special support is provided for TWiki:Plugins.SourceHighlightPlugin. Read more in DBI_DO section.
 

How it works.

Line: 135 to 132
 
    1. Afterwards we let TWiki to mangle with the output (expand variables, pass it through other plugins, whatsoever).

Changed:
<
<

DBIQueryPlugin Expansion

>
>

DBIQueryPlugin 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 DBI module. NAME_lc case conversion performed so that every name is in lowercase. For instance, the following SELECT:
Line: 153 to 150
 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.

Changed:
<
<

Quoting of Values

>
>

Quoting of Values

 Values fetched from database are quoted using CGI::escapeHTML() unless contrary behaviour dictated by unquoted parameter. Then every newline character is changed with TWiki variable %BR%.

Changed:
<
<

Subqueries

>
>

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.
Line: 180 to 177
 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.

Changed:
<
<
ALERT! Warning: Column names may overlap with parent queries. In this case parent has influence over child's SQL statement, header and footer definitions; whereas .body uses subquery column names. Take care of this! Best of all avoid this situation by using SQL aliasing:
>
>
ALERT! Warning: Column names may overlap with parent queries. In this case parent has influence over child's SQL statement, header and footer definitions; whereas .body uses subquery column names. Take care of this! Best of all avoid this situation by using SQL aliasing:
 Parent:
Line: 197 to 194
 
Changed:
<
<
ALERT! 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.
>
>
TIP 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% could 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. The most typical use for it would be database editing.

Implementation

Perl code used by a particular %DBI_DO% becomes a part of an anonymous sub. Several variables are available to the code:

Variable Description
$dbh Database connection handle.
$cgiQuery A CGI object as returned by TWiki::Func::getCgiQuery().
$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 where one puts a value which is returned by the sub then. The value is put into the output then. In this way one could display a error message or notification or form any kind of TWiki/HTML code.

Useful functions

The following plugin functions could be useful while creating a script:

db_connect($db_identifier)
Useful when connection to another database needed. $db_identifier parameter is database ID as specified in the plugin configuration.
subQuery($subquery, $dbRecord)
Implements %DBI_SUBQUERY% and %DBI_CALL%. $subquery is the name of subquery to be called. $dbRecord has the same meaning as corresponding sub parameter.
expandColumns($text, $dbRecord)
Expands variables within $text as described in DBIQueryPlugin Expansion.
protectValue($text)
Returns $text value modified in a way that prevents it from TWiki processing.
wikiErrMsg(@msg)
Use it for presenting error messages in a uniform way.

Access Control

This plugin does nothing but a little with regard to access control and user rights. It has been considered more reasonable to delegate this functionality to database server. The only exception is %DBI_DO% which we will talk about later.

Database server-side access control works through mapping TWiki into DB server user accounts by means of usermap key in plugin configuration. This is done in the following way:

  1. Plugin checks if TWiki user is explicitly defined in usermap.
  2. Then it checks if TWiki user is a member of a group defined in usermap.
  3. If none of the above is fulfilled then user and password keys of plugin configuration are used. If there is no user key then plugin refuses connection.
  4. Plugin attempts to connect.

Additional control implemented for %DBI_DO%. Another configuration key named allow_do introduced which maps individual topic into list of users or groups with permission to use the feature. Special subkey default of this key defines default mapping. The implicit default is TWikiAdminGroup.

In the following example:

%dbi_connections = (
	 some_db => {
		  ...
		  allow_do => {
				'Sandbox.SomeUserSandbox' => [qw(SomeUser TWikiAdminGroup)],
				'Main.SomeTopic' => [qw(SpecialGroup)],
		  },
	 },
);

A user SomeUser will be allowed to use %DBI_DO% within SomeUserSandbox topic of Sandbox web and within SomeTopic of Main web if he is a member of SpecialGroup.

ALERT! Note: The access control scheme is very much raw at the moment and may be subject to change in future plugin version. Yet, comments are welcome!

DBIQueryPlugin.cfg Configuration File

 

Plugin Settings

Line: 213 to 281
 

Plugin Installation Instructions

Changed:
<
<
Note: You do not need to install anything on the browser to use this plugin. The following instructions are for the administrator who installs the plugin on the server where TWiki is running.
>
>
TIP Note: You do not need to install anything on the browser to use this plugin. The following instructions are for the administrator who installs the plugin on the server where TWiki is running.
 
  • Download the ZIP file from the Plugin web (see below)
  • Unzip DBIQueryPlugin.zip in your twiki installation directory. Content:

Revision 417 Feb 2006 - Main.VadimBelman

Line: 1 to 1
Changed:
<
<

DBIQuery TWiki Plugin

>
>
 
Changed:
<
<
This plugin is intedent to provide TWiki with ability to make complex database requests using DBI Perl module.
>
>

DBI Query Plugin

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

 

Syntax Rules

Line: 10 to 12
 
%DBI_QUERY{"db_identifier" ...}%
SELECT ...

Changed:
<
<
.head
>
>
.header
 head .body %column%
Changed:
<
<
%DBI_SUBQUERY{"name"}%
>
>
%DBI_SUBQUERY{"name"}%
 .footer footer %DBI_QUERY%
Added:
>
>
%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%

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

DBI_QUERY

Each query consist of two parts: a query statement (SELECT) 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
Changed:
<
<
.head It is prepended to the query output once.
>
>
.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.
Line: 34 to 54
 
Parameter Description Default Required
"db_identifier" Database ID as defined in the plugin configuration. See Plugin Installation section. none required
subquery="name" Defines a subquery which does not produce immediate result but could be used from inside another query none optional
Changed:
<
<
unquoted="col1 col2 ..." List of columns to be left unquoted in the output. 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 TWiki 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 a TWiki variable is found in a string? It gets expanded by TWiki, for sure. Adding this columns to the protected list prevents the expansion. Precisely saying, the whole purpose of protection is displaying of data as is, without any modification.

DBI_DO

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

  1. In place, just between starting %DBI_DO{...}% and ending %DBI_DO%.
  2. In a separate topic which is the script on its own.
  3. Several scripts in a topic using %DBI_CODE{...}%.

  • Parameters

Parameter Description Default Required
"db_identifier" Database ID as defined in the plugin configuration. See Plugin Installation 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 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. none optional

DBI_CALL

%DBI_CALL{...}% directly calls a subquery.

  • Parameters

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

How it works.

>
>
Moreover, named parameters are transfered to a 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%

ALERT! Read more in DBIQueryPlugin Expansion section.

DBI_CODE

%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

ALERT! Note: Special support is provided for TWiki:Plugins.SourceHighlightPlugin. Read more in DBI_DO section.

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?
Line: 44 to 126
 
  1. Every query definition within topic is parsed and stored for further processing. This is done in two major steps:
    1. Query statement is exctracted from the definition.
Changed:
<
<
    1. Every newline within .head, .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.
>
>
    1. 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.
 
  1. All queries are processed except for those declared as subqueries:
Changed:
<
<
    1. .head filter is expanded with DBIQueryPlugin mechanism and put into the output.
    2. The query statement is expanded using DBIQueryPlugin and TWiki 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.
>
>
    1. .header filter is expanded with DBIQueryPlugin mechanism and put into the output.
    2. The query statement is expanded using DBIQueryPlugin and TWiki 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.
 
    1. Afterwards we let TWiki to mangle with the output (expand variables, pass it through other plugins, whatsoever).

Line: 73 to 155
 

Quoting of Values

Changed:
<
<
Values fetched from database are quoted using CGI::escapeHTML(). Then every newline character is changed with <br> tag.
>
>
Values fetched from database are quoted using CGI::escapeHTML() unless contrary behaviour dictated by unquoted parameter. Then every newline character is changed with TWiki variable %BR%.
 

Subqueries

Line: 97 to 179
 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.
Added:
>
>
 ALERT! Warning: Column names may overlap with parent queries. In this case parent has influence over child's SQL statement, header and footer definitions; whereas .body uses subquery column names. Take care of this! Best of all avoid this situation by using SQL aliasing:

Parent:

Line: 112 to 195
 SELECT col1 as subquery_col1 ...
Added:
>
>

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

 

Plugin Settings

Line: 119 to 205
 a plugin setting write %<plugin>_<setting>%, i.e. %DBIQUERYPLUGIN_SHORTDESCRIPTION%

Changed:
<
<
    • Set SHORTDESCRIPTION = This plugin allows to make complex queries to a database using DBI Perl module.
>
>
    • Set SHORTDESCRIPTION = Make complex database queries using DBI Perl module
 
  • Debug plugin: (See output in data/debug.txt)
Changed:
<
<
    • Set DEBUG = 1
>
>
    • Set DEBUG = 0
 

Plugin Installation Instructions

Line: 142 to 228
 
Plugin Author: TWiki:Main.VadimBelman
Plugin Version: 13 Oct 2005
Change History:
<-- versions below in reverse order -->
 
Added:
>
>
17 Oct 2005: 1.1
 
13 Oct 2005: Initial version
CPAN Dependencies: DBI, Error
Other Dependencies: none
Perl Version: 5.8
Added:
>
>
License: GPL (GNU General Public License)
TWiki:Plugins/Benchmark: GoodStyle nn%, FormattedSearch nn%, DBIQueryPlugin nn%
 
Plugin Home: http://TWiki.org/cgi-bin/view/Plugins/DBIQueryPlugin
Feedback: http://TWiki.org/cgi-bin/view/Plugins/DBIQueryPluginDev
Added:
>
>
Appraisal: http://TWiki.org/cgi-bin/view/Plugins/DBIQueryPluginAppraisal
 Related Topics: TWikiPreferences, TWikiPlugins
Changed:
<
<
-- VadimBelman? - 13 Oct 2005
>
>
-- TWiki:Main.VadimBelman - 13 Oct 2005

<--
vim: ft=twiki et
-->
 

Revision 330 Jan 2006 - Main.VadimBelman

Line: 1 to 1
 

DBIQuery TWiki Plugin

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

Revision 213 Oct 2005 - Main.VadimBelman

Line: 1 to 1
 

DBIQuery TWiki Plugin

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

Line: 7 to 7
 
  • Syntax:
Changed:
<
<
%DBI_QUERY{"db_identifier" ...}%
>
>
%DBI_QUERY{"db_identifier" ...}%

 SELECT ... .head head
Line: 18 to 18
 .footer footer %DBI_QUERY%
Changed:
<
<
>
>
 
Changed:
<
<
Each query consist of two parts: a query and output formatting rules. The query starts just after the leading %DBI_QUERY{...}% declaration. The formatting rules are defined by .head, .body, and .footer keywords each starting at the beginning of line. Their meaning shall be obvious from their name:
>
>
Each query consist of two parts: a query statement (SELECT) and output formatting filters. SQL statement starts just after the leading %DBI_QUERY{...}% declaration. The filters are defined by .head, .body, and .footer keywords each starting at the beginning of line. Their meaning shall be obvious from their name:
 
Declaration Description
.head It is prepended to the query output once.
Line: 32 to 32
 
  • Parameters:

Parameter Description Default Required
Changed:
<
<
"db_identifier" Database ID as defined in the plugin configuration. See Plugin Installation section. none required
>
>
"db_identifier" Database ID as defined in the plugin configuration. See Plugin Installation section. none required
 
subquery="name" Defines a subquery which does not produce immediate result but could be used from inside another query none optional
unquoted="col1 col2 ..." List of columns to be left unquoted in the output. none optional

How it works.

Changed:
<
<
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?
>
>
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:
Changed:
<
<
  1. The query is expanded using DBIQueryPlugin and TWiki variable expansion mechanism in the order they are mentioned here. The former is used for subquery processing as described below.
  2. Database is queried and data is fetched row-by-row.
  3. Each row gets passed through a filter, defined by .body declaration. Using each column name (converted into lowercase) as a variable name (%column_name%) we substitute these variables with column values. Each value gets quoted, unless otherwise defined by unquoted parameter of the query. Resulting text is appended to the output.
  4. Header and footer defined by .head and .footer are prepended and appended to the output repsectively.
  5. Afterward we let TWiki to mangle with the output (expand variables, pass it through other plugins, whatsoever).
>
>
  1. Every query definition within topic is parsed and stored for further processing. This is done in two major steps:
    1. Query statement is exctracted from the definition.
    2. Every newline within .head, .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. .head filter is expanded with DBIQueryPlugin mechanism and put into the output.
    2. The query statement is expanded using DBIQueryPlugin and TWiki 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 TWiki to mangle with the output (expand variables, pass it through other plugins, whatsoever).
 
Changed:
<
<
Following rules are applied to the filters:
>
>

DBIQueryPlugin Expansion

 
Changed:
<
<
  • Every newline withing filter gets removed and changed with space allowing some better formatting. For instance, one may wish to use SpreadSheetPlugin for processing table data. Formulas used for this purpose might be quite lengthy. Therefore we could form it like this:
>
>
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 DBI module. NAME_lc case conversion performed so that every name is in lowercase. For instance, the following SELECT:
 
Changed:
<
<
.body
|%CALC("...")%
|%CALC("..."%
|%some_column%|\n
>
>
SELECT
	 Name,
	 PersonalID,
	 SomeOtherInfo
  FROM
	 PersonData
 
Changed:
<
<
Note the \n at the end. This is the way we eventually let newline be presented in the output.
>
>
would provide us with variables %name%, %personalid%, %someotherinfo%.
 
Changed:
<
<
  • Quoting of column values consist of two steps. First, every "dangerous" symbol like & or < is converted into HTML representation (&amp; and &lt; respectively). Then every newline withing fetched data is changed with <br> tag.
>
>
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.
 
Changed:
<
<
  • The .body declaration is checked for the presence of %DBI_SUBQUERY{"name"}% as well. Being found such a reference is replaced with output of corresponding subquery if such one is found. Subquery can be defined anywhere within current topic as a normal query with subquery parameter defined.
>
>

Quoting of Values

 
Changed:
<
<
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 from the parent queries. It is also possible to have a chain of subqueries: top_query -> subquery1 -> subquery2 -> ..., in which case all column values from all the calling queries are accessible. Yet, it is possible to use these values withing database query and this is the only reason for subqueries to be ever developed.
>
>
Values fetched from database are quoted using CGI::escapeHTML(). Then every newline character is changed with <br> tag.

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:
Changed:
<
<
>
>

 %DBI_QUERY{...}% SELECT col1, col2
Line: 80 to 93
 .body ... %DBI_QUERY%
Changed:
<
<
>
>
 
Changed:
<
<
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. At any rate, one could think out several more reasons for the way it's been done. smile
>
>
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.
 
Changed:
<
<
ALERT! Note: Column names may overlap with parent queries. In this case parent has influence over the SQL statement whereas .body uses subquery column names. Take care of this! Best of all avoid this situation by using SQL aliasing:
>
>
ALERT! Warning: Column names may overlap with parent queries. In this case parent has influence over child's SQL statement, header and footer definitions; whereas .body uses subquery column names. Take care of this! Best of all avoid this situation by using SQL aliasing:
 Parent:
Changed:
<
<
>
>

 SELECT col1 as parent_col1 ....
Changed:
<
<
>
>
 Subquery:
Changed:
<
<
>
>

 SELECT col1 as subquery_col1 ...
Changed:
<
<
>
>
 

Plugin Settings

Line: 111 to 124
 
  • Debug plugin: (See output in data/debug.txt)
    • Set DEBUG = 1
Added:
>
>
 

Plugin Installation Instructions

Changed:
<
<
Just unpack the archive in the root of your TWiki installation and edit DBIQueryPlugin.cfg.
>
>
Note: You do not need to install anything on the browser to use this plugin. The following instructions are for the administrator who installs the plugin on the server where TWiki is running.

  • Download the ZIP file from the Plugin web (see below)
  • Unzip DBIQueryPlugin.zip in your twiki installation directory. Content:
    File: Description:
    data/TWiki/DBIQueryPlugin.txt Plugin topic
    data/TWiki/DBIQueryPlugin.txt,v Plugin topic repository
    lib/TWiki/Plugins/DBIQueryPlugin.pm Plugin Perl module
    lib/DBIQueryPlugin.cfg Plugin configuration
 

Plugin Info

Changed:
<
<
Plugin Author: TWiki:Main.AndreaSterbini, TWiki:Main.PeterThoeny
Plugin Version: 21 Mar 2003
>
>
Plugin Author: TWiki:Main.VadimBelman
Plugin Version: 13 Oct 2005
 
Change History:
<-- versions below in reverse order -->
 
Changed:
<
<
21 Mar 2004: Added afterSaveHandler
14 Jul 2001: Changed to plug&play
27 Feb 2001: Initial version
CPAN Dependencies: none
>
>
13 Oct 2005: Initial version
CPAN Dependencies: DBI, Error
 
Other Dependencies: none
Changed:
<
<
Perl Version: 5.0
TWiki:Plugins/Benchmark: GoodStyle 99%, FormattedSearch 99%, DBIQueryPlugin 99%
>
>
Perl Version: 5.8
 
Plugin Home: http://TWiki.org/cgi-bin/view/Plugins/DBIQueryPlugin
Feedback: http://TWiki.org/cgi-bin/view/Plugins/DBIQueryPluginDev
Changed:
<
<
Related Topics: TWikiPreferences, TWikiPlugins, DefaultPlugin?
>
>
Related Topics: TWikiPreferences, TWikiPlugins
 
Changed:
<
<
-- TWiki:Main.AndreaSterbini - 27 Feb 2001
-- TWiki:Main.PeterThoeny - 21 Mar 2004
>
>
-- VadimBelman? - 13 Oct 2005
 

Revision 112 Oct 2005 - Main.VadimBelman

Line: 1 to 1
Added:
>
>

DBIQuery TWiki Plugin

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

Syntax Rules

  • Syntax:

<strong>%RED%<pre>This DBI connection is not defined.</pre>%ENDCOLOR%</strong>

Each query consist of two parts: a query and output formatting rules. The query starts just after the leading %DBI_QUERY{...}% declaration. The formatting rules are defined by .head, .body, and .footer keywords each starting at the beginning of line. Their meaning shall be obvious from their name:

Declaration Description
.head 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.

Read below on how this plugin works in order to get more detailed explanation of the meaning of each syntax element.

  • Parameters:

Parameter Description Default Required
"db_identifier" Database ID as defined in the plugin configuration. See Plugin Installation section. none required
subquery="name" Defines a subquery which does not produce immediate result but could be used from inside another query none optional
unquoted="col1 col2 ..." List of columns to be left unquoted in the output. none optional

How it works.

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?

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

  1. The query is expanded using DBIQueryPlugin and TWiki variable expansion mechanism in the order they are mentioned here. The former is used for subquery processing as described below.
  2. Database is queried and data is fetched row-by-row.
  3. Each row gets passed through a filter, defined by .body declaration. Using each column name (converted into lowercase) as a variable name (%column_name%) we substitute these variables with column values. Each value gets quoted, unless otherwise defined by unquoted parameter of the query. Resulting text is appended to the output.
  4. Header and footer defined by .head and .footer are prepended and appended to the output repsectively.
  5. Afterward we let TWiki to mangle with the output (expand variables, pass it through other plugins, whatsoever).

Following rules are applied to the filters:

  • Every newline withing filter gets removed and changed with space allowing some better formatting. For instance, one may wish to use SpreadSheetPlugin for processing table data. Formulas used for this purpose might be quite lengthy. Therefore we could form it like this:

.body
|%CALC("...")%
|%CALC("..."%
|%some_column%|\n

Note the \n at the end. This is the way we eventually let newline be presented in the output.

  • Quoting of column values consist of two steps. First, every "dangerous" symbol like & or < is converted into HTML representation (&amp; and &lt; respectively). Then every newline withing fetched data is changed with <br> tag.

  • The .body declaration is checked for the presence of %DBI_SUBQUERY{"name"}% as well. Being found such a reference is replaced with output of corresponding subquery if such one is found. Subquery can be defined anywhere within current topic as a normal query with subquery parameter defined.

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 from the parent queries. It is also possible to have a chain of subqueries: top_query -> subquery1 -> subquery2 -> ..., in which case all column values from all the calling queries are accessible. Yet, it is possible to use these values withing database query and this is the only reason for subqueries to be ever developed.

For instance, in the following code:

%<nop>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. At any rate, one could think out several more reasons for the way it's been done. smile

ALERT! Note: Column names may overlap with parent queries. In this case parent has influence over the SQL statement whereas .body uses subquery column names. Take care of this! Best of all avoid this situation by using SQL aliasing:

Parent:

SELECT col1 as parent_col1
....

Subquery:

SELECT col1 as subquery_col1
...

Plugin Settings

Plugin settings are stored as preferences variables. To reference a plugin setting write %<plugin>_<setting>%, i.e. %DBIQUERYPLUGIN_SHORTDESCRIPTION%

  • One line description, is shown in the TextFormattingRules topic:
    • Set SHORTDESCRIPTION = This plugin allows to make complex queries to a database using DBI Perl module.

  • Debug plugin: (See output in data/debug.txt)
    • Set DEBUG = 1

Plugin Installation Instructions

Just unpack the archive in the root of your TWiki installation and edit DBIQueryPlugin.cfg.

Plugin Info

Plugin Author: TWiki:Main.AndreaSterbini, TWiki:Main.PeterThoeny
Plugin Version: 21 Mar 2003
Change History:
<-- versions below in reverse order -->
 
21 Mar 2004: Added afterSaveHandler
14 Jul 2001: Changed to plug&play
27 Feb 2001: Initial version
CPAN Dependencies: none
Other Dependencies: none
Perl Version: 5.0
TWiki:Plugins/Benchmark: GoodStyle 99%, FormattedSearch 99%, DBIQueryPlugin 99%
Plugin Home: http://TWiki.org/cgi-bin/view/Plugins/DBIQueryPlugin
Feedback: http://TWiki.org/cgi-bin/view/Plugins/DBIQueryPluginDev

Related Topics: TWikiPreferences, TWikiPlugins, DefaultPlugin?

-- TWiki:Main.AndreaSterbini - 27 Feb 2001
-- TWiki:Main.PeterThoeny - 21 Mar 2004

 
Pixeon Medical Systems - Todos os direitos reservados. 2020
http://www.pixeon.com.br/