Difference: DBIQueryPlugin (5 vs. 6)

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

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