Tags:
database2Add my vote for this tag import1Add my vote for this tag twiki_application1Add my vote for this tag web_application1Add my vote for this tag create new tag
, view all tags

DatabasePluginDev Discussion: Page for developer collaboration, enhancement requests, patches and improved versions on DatabasePlugin contributed by the TWikiCommunity.
• Please let us know what you think of this extension.
• For support, check the existing questions, or ask a new support question in the Support web!
• Please report bugs below

Feedback for the DatabasePlugin

-- TaitCyrus - 05 May 2003

If you could get the plugin to optionally return your data set as XML we could use the XmlXslPlugin to format it.

Just a thought.

-- MartinCleaver - 22 Jan 2002

Ok, I'll take a look at this.

I'm also planning on quickly adding additional functionality. The next thing I want to add is the ability to take an entire row from a database and place it in a single TWiki table cell (as apposed to each database field being placed in its own table cell).

I would also like to make the interface even more generic allowing a person to specify how to format the returned data (instead of it being forced into a table). Something that ?might? work well is the

%REPEAT% & %SPLIT%
constructs used by the search module, but I can't find any documentation on it. Any thoughts? Thanks

-- TaitCyrus - 23 Jan 2002

I don't understand how adding one level of indirection (the first level having the passwords to get into the second level) adds any real security - if someone breaks into the first level of passwords, held in the config.pm file, they can easily get into the second level of passwords, held in the database.

This seems like an unnecessary complication. Perhaps you could explain the rationale for this a bit more?

-- RichardDonkin - 14 Feb 2002

That is a very good question. I see that I need to better clarify some things in my main DatabasePlugin page. As I write the following, I see that although I had security on my mind when I designed this plugin (based on years of dealing with various Unix security issues -- password security, network security, etc.), it was more for ease of management and administration that I designed things the way I did, though security was one of the requirements.

Assumptions

  1. The web server is only a web server and people can't interactively log into it. Only ftp access is available (and then ftp has been set up to chroot() to their area so others could not access other users files).
  2. A database can be configured to have better security than a Unix flat file.

My requirements were

  1. No clear text database names, user names, and passwords in TWiki pages so this information has to be hidden somewhere.
  2. Users of this plugin would not need to know Perl to manage things
  3. Support access to multiple databases from a single TWiki page
  4. Support an environment where a different person manages:
    • the web server (normal sysadmin),
    • TWiki (setting up of webs etc),
    • the database(s), and
    • managing the actual TWiki web content.
  5. Management of the hidden database information could be performed via a web page (a person didn't have to log onto or ftp to the web server to manage)
Passwords could not be in TWiki pages and had to be hidden somewhere. The choices were either a flat file or a database. There were several problems with a flat file which included:
  • Users would have to hand edit this file (a challenge especially if the server is a Unix box and users don't know how to use Unix tools to edit).
  • If a config.pm they would need to know some Perl to edit.
  • If a free formated file, they would need to know this format to edit.
  • Did not satisfy my requirement of being editable from a web page
Instead, I choose a database since it is manageable from a web (using phpMyAdmin) and doesn't require having telnet/ftp access to the web server.

So given this, to address your questions:

  1. Since people can't log onto the web server, they should not have access to the config.pm and then access to the clear text password information contained in it.
  2. Even if people somehow got ahold of the config.pm file, I'm also assuming that the DatabasePlugin database is only accessible from 'localhost' so having the password information from the config.pm file would not be useful to anyone outside of 'localhost'.
So maybe design was more a factor than security, though security was considered.

Does that help?

-- TaitCyrus - 15 Feb 2002

Thanks for the clarification - seems like the key point is being able to change the main passwords for the DB(s) through a web interface to the DatabasePlugin DB that manages their usernames/passwords. I can see why you went this route, but it's worth noting that unless the server is hacked (disclosing the passwords), changing these passwords doesn't really make any difference to security - someone who has access to the config.pm file can still get into the DatabasePlugin DB, in which case the changed passwords don't make any difference.

It's useful to be able to change the passwords, but IMO the important one is the config.pm password. I think the key issue is whether you regard DBs as more secure than filesystems, but of course you are reliant on config.pm security in any case, so I can't see that this adds security.

However, the passwords to the main DBs are of course more maintainable in your design than if they were held in config.pm, which is a benefit. If the DB administrator regularly changes passwords anyway, this is important. Personally, I would allocate a long, hard-to-guess password for use by the TWiki site, and never change it (since it never leaves that server yet must be stored on the server), but I'm sure some DBAs would not agree with this. This avoids the new password going over the network when being changed by phpMyAdmin (unless you are using an SSL server of course).

Interesting issue though - I'm sure this comes up a lot in web-based DB applications. Some apps have a similar issue with secret keys, where the app must start up unattended, meaning that the secret key must be stored on the box. As long as that key never leaves the box, and the system is suitably hardened against intrusions, you should be OK.

See SecureSetup for a bit more on securing TWiki, and also search for PHP in Codev - since you have PHP enabled, it's conceivable that someone could upload a PHP file as part of an attack (though I think TWiki has closed this loophole).

-- RichardDonkin - 16 Feb 2002

OK. I just uploaded the latest version (1.2). This version adds editing ability. Since I'm lazy and don't have the time to do this right, I just set it up so that editing creates a popup window that is framed. The top frame contains a 'close window' link which closes the popup window. The bottom frame source is designed to come from a phpMyAdmin page editing a single table. For security reasons it is assumed (and recommended) that the new DatabasePluginEdit routine in bin has been set up such that to run it will require that the user logs in. It is assumed that this level of security is sufficient to allow the user to edit the specified table. Once 'logged in' using whatever security mechanism you have in place for TWiki's bin, the script DatabasePluginEdit then calls phpmyadmin passing in a username and password. I know that this is probably not the best of security solutions, but like I said, I don't have time to do much more than this.

-- TaitCyrus - 20 Mar 2002

Hi. After 1/2 night trying to get this plugin running I cry for help (If I should ask elsewhere, don't flame, I will just move this comment). My local TWiki does not recognise the DatabasePlugin. It just does not turn up in the list of installed plugins in the TWikiPreferences page. It even refuses to load, when I include it in the INSTALLEDPLUGINS variable. Any idea or help?

-- MartinRehker - 30 Sep 2002

Plugins somtimes fail to load without any warning if there is an error. See TracePluginSyntaxErrors

-- PeterThoeny - 01 Oct 2002

I just did a bunch of hacking on this plugin. Basically I have hacked up so that the database it uses can now be datasources other than mysql. I had to alter some the fundamental config table, but it wasnt too bad.

Presently I have it working solely with DBD::SQLite (which really screams by the way...) without mysql anywhere in the mix.

After I clean up my code, Ill post my updated 1.3 version.

-- JohnCavanaugh - 22 Nov 2002

I have an updated version of this plugin that works with all DBI data sources (DBD::SQLite, DBD::CSV, etc). In the process of facilitating this I have made some incompatible changes relating to the format of the config tables etc. I also removed the code for editing (the plugin is now solely read-only access to databases) since it depends on PHP which I assume most folks do not have installed.

My question is should I release this as 1.3 or release it as a new plugin, perhaps named, DbiPlugin??

Any thoughts, opinions??

-- JohnCavanaugh - 03 Jan 2003

I would certainly support calling the new release something else given the loss of editing (compatbuster). Thus, anyone looking for the latest version of DatabasePlugin would not be losing functionality.

-- AnthonPang - 05 Jan 2003

I just updated to version 1.3. The main changes are:

  • Added support for Oracle (at least the Oracle environment I have available to me)
    • This required adding the idea of SID
  • Added support for a Local database. Since my Oracle environment is such that I only have read access to the DB, I was not able to put the redirection DB into the DB itself so I added the ability to put the redirection DB into bin/DatabasePluginConfig.pm. This is slightly less secure than putting the info into a real DB, but given that this file should already be secure, it was used.

-- TaitCyrus - 05 May 2003

Is anybody else having problems with using DatabasePlugin together with DBD::Sybase? For some reason DBD::Sybase doesn't like having host and database in the dsn. I fixed the problem locally by editing DatabasePlugin.pm to read:

    my $fix_for_sybase = "host"; # initialize to non-sybase value
    if ($db_driver =~ /Sybase/i) {
        $fix_for_sybase = "server";
    }
    my $db = DBI->connect("DBI:$db_driver:database=$database;$fix_for_sybase=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});

instead of

    my $db = DBI->connect("DBI:$db_driver:database=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});

-- SimonHardyFrancis - 14 May 2003

Just a question about the "any sql command" style of tag ...

What does happen if I put commands that change the database? (I have'nt had the time to try it myself)

-- AndreaSterbini - 09 Jul 2003

In this plugin I missed an interface to PostgreSql. Using CPAN, I installed on my machine the DBD::Pg library and noticed a few strange things, which I 'corrected'.

first of all: your plugin says 'database' where the DBI->connect on my machine expects 'dbname'. I modified this wherever necessary in the DatabasePlugin.pm. it did sound strange to me, but I really could not figure out anything else.

then I added this single elsif block that allows your plugin to read from postgres:

   if ($db_driver eq "Oracle") {
     $cmd = "SELECT COLUMN_NAME FROM all_tab_columns WHERE TABLE_NAME = '$table'";
+     } elsif ($db_driver eq "Pg") {
+    $cmd = "SELECT attname FROM pg_attribute WHERE attrelid=(SELECT oid FROM pg_class WHERE relname='$table') AND attnum>0";
      } else {
     $cmd = "DESCRIBE $table";
   }

also, you could explain me what is the state of that 'dbname'/'database' problem, which leaves me quite perplex.

-- MarioFrasca - 27 Oct 2003

Mario:
Postgres expects dbname in the Data Source Name whereas mysql expects database ( as does Oracle I assume ). Therefore the real solution is to avoid hardcoding either string and to interpolate a variable in the connect calls

my %driver_db_string = ( mysql => 'database', Pg => 'dbname', Oracle => 'database' );
:
 my $db = DBI->connect("DBI:$db_driver:$driver_db_string{$db_driver}=$database;host=$hostname$sid"
, $user, $password, {PrintError=>1, RaiseError=>1});

I am going to have a go at producing an appropriate patch.

-- EdMcGuigan - 02 Dec 2003

postgres patch
Here is the diff file for edits made to the file lib/Twiki/Plugins/DatabasePlugin.pm to get it to support postgres databases.

It incorporates a modification suggested by Mario Frasca and allows for simultaenous support of postgres, oracle and mysql.

116a117
>    %driver_db_string
119a121,126
> #
> # Initialize the hash used to insert the appropriate
> # field name string in the DSN for DBI->connect calls
> # based on the driver being used
> %driver_db_string = ( mysql => 'database', Pg => 'dbname', Oracle => 'database' );
> 
160c167
<    my $db = DBI->connect("DBI:$DatabasePluginConfig::db_driver:database=$DatabasePluginConfig::db_database;host=$DatabasePluginConfig::db_hostname$sid", $DatabasePluginConfig::db_username, $DatabasePluginConfig::db_password, {PrintError=>1, RaiseError=>0});
---
>    my $db = DBI->connect("DBI:$DatabasePluginConfig::db_driver:$driver_db_string{$DatabasePluginConfig::db_driver}=$DatabasePluginConfig::db_database;host=$DatabasePluginConfig::db_hostname$sid", $DatabasePluginConfig::db_username, $DatabasePluginConfig::db_password, {PrintError=>1, RaiseError=>0});
256c263
<     my $db = DBI->connect("DBI:$db_driver:database=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});
---
>     my $db = DBI->connect("DBI:$db_driver:$driver_db_string{$db_driver}=$database", $user, $password, {PrintError=>1, RaiseError=>1});
329c336
<     my $db = DBI->connect("DBI:$db_driver:database=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});
---
>     my $db = DBI->connect("DBI:$db_driver:$driver_db_string{$db_driver}=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});
401c408
<     my $db = DBI->connect("DBI:$db_driver:database=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});
---
>     my $db = DBI->connect("DBI:$db_driver:$driver_db_string{$db_driver}=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});
477c484
<     my $db = DBI->connect("DBI:$db_driver:database=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});
---
>     my $db = DBI->connect("DBI:$db_driver:$driver_db_string{$db_driver}=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});
542c549
<     my $db = DBI->connect("DBI:$db_driver:database=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});
---
>     my $db = DBI->connect("DBI:$db_driver:$driver_db_string{$db_driver}=$database;host=$hostname$sid", $user, $password, {PrintError=>1, RaiseError=>1});
548a556,557
>     } elsif ($db_driver eq "Pg") {
> +    $cmd = "SELECT attname FROM pg_attribute WHERE attrelid=(SELECT oid FROM pg_class WHERE relname='$table') AND attnum>0";

-- EdMcGuigan - 02 Dec 2003

this patch looks great! would you attach a unified diff (diff -U or cvs diff) to this topic please? it makes patching a lot easier, especially if you're not running identical (but still pretty close) sources. thanks.

i'm just starting to experiment with database access via the wiki. glad to see postgresql support smile

-- WillNorris - 03 Dec 2003

TaitCyrus, I am still confused on how this will work with Oracle, as you state above. To access an oracle database using the Oracle client, normally one sets up a tnsnames.ora file with the information on the database. This file has lines such as

my_facourite_db =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 111.222.333.444)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = XYZ)
    )
)

I assume that you still require the oracle client, right? Am I correct that host will be your $db_hostname and the service defined (above my_facourite_db) would be in your $db_database?. What in your setup corresponds to the sets of tables that Oracle tables are organized in?

-- ThomasWeigert - 05 Feb 2004

> The DatabasePlugin zip file as well as my other plugins zip files should
> be the latest code so please feel free to check it into CVS.
> ++Tait

DatabasePlugin and ChartPlugin cvs have been updated to match the zip versions; Thanks Tait!

-- MattWilkie - 01 Apr 2004

Hi. Has anyone gotten this plugin to work across multiple tables in one SQL query? It's not obvious how to do this. A mutli-table query that works on the database command line returns "DBD::mysql::st execute failed: Query was empty" when put into TWiki.

-- ChrisMcCubbin - 06 Jul 2004

I recently installed version 1.3 of this plug-in. I used it initially with PostgreSQL (using the above patch). I also found another problem -- if any of the "$sth->execute" calls failed (because someone, say, me, had entered incorrect information into the query) the entire page would throw an error. This makes it difficult for users to correct their mistakes. I wrapped all the sth->execute statements with an eval to catch this and handle that through the regular in-page error handling. Anyway, I've generated a unified diff patch including the above changes and the error handling and will attach it to this page.

- HeatherSherman - 23 Jul 2004

We recently installed SpeedyCGI at our site, and that has given us a peculiar problem with this plugin.

In DatabasePluginConfig.pm, we setup an environment variable $ENV{NLS_LANG} = "danish"; to use danish chars with Oracle. And! - This works great, as long as the plugin is used on the first page that is served by the server - but if the first page is a "default" TWiki-page, the environment variable never takes effect, and we get ?'es instead of our regional chars in selects.

Can I move the line containing the $ENV{NLS_LANG} = "danish"; to somewhere else, so it will always take effect, no matter what page is loaded first? (Again, this is only a problem with SpeedyCGI, so I suspect it has to do with some kind of global variable or the like).

-- SteffenPoulsen - 22 Sep 2004

Seems like there are still many plugins that are not mod_perl and SpeedyCGI comptible.

Do you run this plugin on Cairo? I would like to know before I even consider trying it on a production server.

-- KennethLavrsen - 01 Nov 2004

Trying to install DatabasePlugin on cygwin, but failing to install DBI. Getting complaint about using multi-threaded perl on CPAN install. Could someone tell me what I'm doing wrong? Thanks

Also, in the description for install, you refer to DBD-mysql. Should that be DBD::mysql?

-- AnthonyEWong - 23 Nov 2004

I'd like to include a function to DatabasePlugin.pm that returns a database handle to a selected configured database. This function can then be used by other Addons or Plugins to cooperate with DatabasePlugin, see GuestBookPluginDev.

What do you think about it?

-- FrankZimper - 27 Dec 2004

Although this is a possibly solution, wouldn't it make more sense to add a generic DatabasePlugin routine that allows users to specify a function call to iterate over the various data? My concern at returing a DB handle is moving DB functionality outside of the DatabasePlugin plugin.

Can you provide an example of what functionality you would like to see in regards to the GuestBookPlugin?

-- TaitCyrus - 07 Jan 2005

Hi there,

I just downloaded the DatabasePlugin.zip from http://twiki.org/cgi-bin/view/Plugins/DatabasePlugin and it works great, just what I was looking for. Except a small error where a parameter was forgotten, here's a little patch to fix this:

--- old/lib/TWiki/Plugins/DatabasePlugin.pm     2005-02-14 03:28:55.161807672 +0100
+++ new/lib/TWiki/Plugins/DatabasePlugin.pm     2005-02-14 03:28:00.463123136 +0100
@@ -389,7 +389,7 @@
     # Since columns might be '*', we need to get the column names that
     # will be returned by '*'.
     if ($tmp eq "*") {
-       @columns = get_column_names($db_driver, $database, $hostname, $user, $password, $table);
+       @columns = get_column_names($db_driver, $database, $db_sid, $hostname, $user, $password, $table);
     } else {
        @columns = split( /,\s*/, $tmp );
     }
@@ -456,7 +456,7 @@
        # NOTE: This assumes that the table columns are actually what is being
        # returned and not some other SQL type information that really doesn't
        # have anything to do with the specified table (db stats for example)
-       @columns = get_column_names($db_driver, $database, $hostname, $user, $password, $table);
+       @columns = get_column_names($db_driver, $database, $db_sid, $hostname, $user, $password, $table);
     } else {
        @columns = split( /,\s*/, $tmp );
     }

To apply the patch, 'cd' to your top-level directory (eg. twiki/) and say 'patch -p1 < thisPatchInAFile'.

-- AlexanderHouben - 14 Feb 2005

Thanks all for a great plugin! We use this for reporting on Jira data. I can report that with a minor tweak (see diff below) to Heather's patch, and a combination of DBD::ODBC, unixODBC and FreeTDS we are successfully retrieving data from MS SQL Server 2000 (yeah, I know, but our DBAs don't do MySQL). If anyone wants further details, I'd be happy to post them.

124c124
< %driver_db_string = ( mysql => 'database', Pg => 'dbname', Oracle => 'database' );
---
> %driver_db_string = ( mysql => 'database', Pg => 'dbname', Oracle => 'database', ODBC => 'dsn' );

-- MartinRothbaum - 02 Mar 2005

Martin, please post detail... Thanks....

  • I'm also very interested in more detail to the bits and pieces that made it work for you, Martin. I struggled with a MSSQL connection for some time, but without any luck. BTW: Are you running TWiki on *nix or Win? -- SteffenPoulsen - 03 Mar 2005

-- ThomasWeigert - 02 Mar 2005

Just some Oracle usage notes:

In order to install DBD::Oracle off of CPAN, I had to:

  • install OCI using the Oracle Client Installer
  • set my ORACLE_HOME environment variable (which is not required by 9i and up versions of Oracle, but which is required by the DBD::Oracle setup)
  • force install DBD::Oracle, because otherwise the tests wouldn't run properly (everything was okay after though, so I think it's not harmful)

Secondly, the SID property is no longer required (or supported, as far as I can tell) by 9i and higher versions of Oracle. Given an entry in TNSNAMES.ORA like this...

NAME_OF_DB_ENTRY =  
   (DESCRIPTION = 
      (ADDRESS_LIST = 
         (ADDRESS = (PROTOCOL = TCP)
                    (HOST = HOST_NAME_OF_SERVER)
                    (PORT = 1521)
         )
      )
      (CONNECT_DATA = (SERVICE_NAME = name_of_db_entry.domain)
      )
   )
...you'd need to set up your DatabasePluginConfig.pm connection entry like this:
@dbinfo = (
    [
    "description",      # description
    "Oracle",         # DB driver
    "NAME_OF_DB_ENTRY",      # DB name
    "NAME_OF_DB_ENTRY",      # DB sid - in 9i and higher db's, just put the db name out of TNSNAMES
    "dbtable",         # DB table
    "usr",         # DB username
    "pwd",         # DB password
    "HOST_NAME_OF_SERVER"   # DB host
    ] 
);

It was the SID setting that particularly threw me for a loop. You just need to duplicate the DB name out of TNSNAMES. You can completely ignore the SERVICE_NAME, which is mildly confusing because that's where you used to put the SID in Oracle 8 and lower DBs.

-- RonScott - 03 Mar 2005

As requested, more detail on talking to MS SQL. I'm running TWiki on RedHat 7.3 and 8.0, so I can't vouch for Windows. I built and installed FreeTDS, unixODBC, DBI and DBD::ODBC from source in the usual manner. On RedHat or Fedora, you should be able to get FreeTDS and unixODBC as binary RPMs from Dag Wieers. Be careful with DBI though - Dag doesn't provide a DBD::ODBC package and I wasn't able to get DBD::ODBC to compile against his version of DBI for RedHat 7.3. Also note that you need to tell the DBD::ODBC Makefile.PL where to find the unixODBC drivers (eg: perl Makefile.PL -o /usr/local).

Once all the components are installed, you need to configure ODBC. I based my configuration on the "ODBC only" example from FreeTDS user guide - the advantage is that no FreeTDS configuration is required.

My /usr/local/etc/odbc.ini looks something like this:

[jira_dev]
Description = Jira development instance
Driver      = /usr/local/lib/libtdsodbc.so
Trace       = No
Server      = mydbserver.mydomain.com
Port        = 1433
TDS_Version = 8.0
Database    = jira

Finally, my DatabasePluginConfig.pm connection entry looks like this:

@dbinfo = (
  [
    "jira_dev",         # description
    "ODBC",             # DB driver
    "jira_dev",         # DB name (put the ODBC DSN here)
    "",                 # DB sid
    "",                 # DB table
    "usr",              # DB username
    'pwd',              # DB password
    ""                  # DB host (not used as is specified in odbc.ini)
  ]
);

I've also extended Heather's idea of using eval to handle SQL errors nicely to the configuration itself which has saved a degree of hair pulling when debugging the connection settings. I've attached a unified patch against the original 1.3 version which includes Heathers patch and my additions.

I'm happy to help out further off-line if you want to email me directly.

-- MartinRothbaum - 04 Mar 2005

Does anybody have more detail on the format of the Local file, if one wants to use that as a database, as the doco says that it is possible in v. 1.3? Thanks....

-- ThomasWeigert - 23 Mar 2005

In response to DavidGerisch appraisal: database connectivity is big functionality and our goal is to expose CPAN to users not rewrite it. Did you follow all the instructions on CpanReadme?

-- MartinCleaver - 07 Apr 2005

For Oracle, the prepare needs to be wrapped in eval too, so I did that and attached this patch.

-- StevenLumos - 22 Sep 2005

Nested Queries: Here's a patch to enable nested queries (loops within loops). It extends the DATABASE_SQL_REPEAT syntax to include an optional label (e.g. DATABASE_SQL_REPEAT_MYLOOP) and is backward compatible with existing syntax - so it won't break any current usage. Nested queries can use clauses from any parent (or grand-parent) query result-set (e.g. where foo='%bar%' ); make sure you choose column names that won't conflict with other loops' exposed column names.

The LABEL below should be named differently for each query loop:

  • %DATABASE_SQL_REPEAT_LABEL{description="table_description" command="..SQL COMMAND.." columns="col1,col2,col3"}% .... user formatting .... %DATABASE_SQL_REPEAT_LABEL%

Example:

---++ Three levels of nested queries
%DATABASE_SQL_REPEAT_OUTER{description="foo" command="my-outer-query" columns="cat"}%
---+++ %cat%
%DATABASE_SQL_REPEAT_MIDDLE{description="bar" command="my-middle-query where flub='%cat%'" columns="group"}%
---++++ %group%
| *cat* | *group* | *detail* |%DATABASE_SQL_REPEAT_INNER{description="flub" command="my-inner-query where where flub='%cat%' and group='%group%'" columns="cat,group,detail"}%
| %cat% | %group% | %detail% |%DATABASE_SQL_REPEAT_INNER%
%DATABASE_SQL_REPEAT_MIDDLE%
%DATABASE_SQL_REPEAT_OUTER%

The patch: Just one new line and two regexs updated. To apply this patch cd to /your-specific-path/twiki/lib/TWiki/Plugins and enter patch DatabasePlugin.pm DatabasePlugin-nested-queries.patch

*** DatabasePlugin-prev.pm      2006-05-20 13:26:52.532669904 +0100
--- DatabasePlugin.pm   2006-05-20 13:26:56.584054000 +0100
***************
*** 493,496 ****
--- 493,497 ----
            (my $fix = $row[$index]) =~ s/\r\n/<BR>/g;
            $repeat_info_copy =~ s/%$columns[$index]%/$fix/g;
+             $repeat_info_copy =~ s/%DATABASE_SQL_(REPEAT(?:_\S+)?){(.*?)}%(.*?)%DATABASE_SQL_\1%/&do_sql_repeat($2, $3)/seog;
        }
        $line .= $repeat_info_copy;
***************
*** 575,579 ****
      $_[0] =~ s/%DATABASE_SQL_TABLE{(.*)}%/&do_sql_table($1)/eog;
      $_[0] =~ s/%DATABASE_REPEAT{(.*?)}%(.*?)%DATABASE_REPEAT%/&do_repeat($1, $2)/seog;
!     $_[0] =~ s/%DATABASE_SQL_REPEAT{(.*?)}%(.*?)%DATABASE_SQL_REPEAT%/&do_sql_repeat($1, $2)/seog;
      $_[0] =~ s/%DATABASE_EDIT{(.*)}%/&do_edit($1)/eog;
  }
--- 576,580 ----
      $_[0] =~ s/%DATABASE_SQL_TABLE{(.*)}%/&do_sql_table($1)/eog;
      $_[0] =~ s/%DATABASE_REPEAT{(.*?)}%(.*?)%DATABASE_REPEAT%/&do_repeat($1, $2)/seog;
!     $_[0] =~ s/%DATABASE_SQL_(REPEAT(?:_\S+)?){(.*?)}%(.*?)%DATABASE_SQL_\1%/&do_sql_repeat($2, $3)/seog;
      $_[0] =~ s/%DATABASE_EDIT{(.*)}%/&do_edit($1)/eog;
  }

Notes:

  1. I've only extended the syntax for DATABASE_SQL_REPEAT as that is all I needed. It should be obvious how to extend the other methods.
  2. I suspect the regex may need tightening if you use DATABASE_SQL_REPEAT and DATABASE_SQL_TABLE methods within the same topic. I think this regex would fix that: s/%DATABASE_SQL_REP(EAT(?:_\S+)?){(.*?)}%(.*?)%DATABASE_SQL_REP\1%/&do_sql_repeat($2, $3)/seog; (not tested though).

-- KarlSkidmore - 20 May 2006

I am the beginner to use Twiki. I set up the Twiki in our local server and want to connect to our existing DB. I have installed DB plug-in, but I don't know the details of how to configure some of the variables, such as $db_driver, $db_database; because I am not sure where to set the path to the existing DB. Moreover, I don't know where and how to test whether my configuration and installation of DB plugin is successful. I would appreciate you could give me more detailed explanations.

-- FeifeiLi - 24 Nov 2006

Looks like that plugin needs someone to maintain it. No one uploaded a new version since 2003. Let me count, let me count (1, 2, 3) that's almost 4 years smile I'm not sure how to get the edition working and by the look of %TABLE_EDIT% it seems to me that the edition was not flexible anyway. Surely there must be a way to send INSERT SQL statement using that DBI module.

-- StephaneLenclud - 19 Jan 2007

Update of this plugin is just in time. Thanks CrawfordCurrie!

Do I understand correctly that I can only use 1 database? (Since Crawford's latest updates?) I see a number of database settings in configure that obviously are for one database. I need to connect to a phone database application. But next week I might to connect to a different database. How would I do this?

-- ArthurClemens - 19 Mar 2007

You can have as many databases as you want. The config array is an array of database specifications, each indexed by their description.

-- CrawfordCurrie - 19 Mar 2007

Can I use an IP Address in the hostname field instead of localhost?

-- BrianBeaudet - 25 Apr 2007

I am using TWiki release 4.0.5 and just recently installed the latest release of the DatabasePlugin. When I go into the configure page it displays this error.

"Undefined subroutine &TWiki::_PROMPT_FOR_PERL called at /var/www/twiki_4_0_5/bin/configure line 1133."

Is there some type of compatibility issue here? This error only occurs after I have entered the DatabasePlugin configuration into the LocalSite.cfg file.

-- JoieySeeley - 30 Apr 2007

Sorry, it looks like there is a problem with the Config.spec with 4.0.5. Please raise a bug in Bugs:WebHome. In the interim, enter your config manually.

-- CrawfordCurrie - 11 May 2007

We would like to conditionally execute SQL based on form input. We are able to get this to work for queries. Insert statements, however, seem to ignore the conditional rules.

For example, the following query is only executed if fundingyear is not empty.

%IF{ "$'URLPARAM{fundingyear}'!=''" then="
%DATABASE_SQL{description="ILC damping rings R&D" sql="SELECT * FROM Resources WHERE FundingYear='%URLPARAM{fundingyear}%' ORDER BY ID desc;" format="| $ID | $R&DActivity | $FundingYear |" header="| ID | R&D Activity | Funding Year |"}%
" }%

However, the following INSERT is done regardless of the status of fundingyear:

%IF{ "$'URLPARAM{fundingyear}'!=''" then="
%DATABASE_SQL{description="ILC damping rings R&D" sql="INSERT INTO Resources (FundingYear, ScientistFTE, EngineerFTE, TechnicianFTE, MaterialsServices, Travel, FundingStatus, FundingSource) VALUES ('%URLPARAM{fundingyear}%' , '%URLPARAM{sci}%' , '%URLPARAM{eng}%' , '%URLPARAM{tec}%' , '%URLPARAM{mat}%' , '%URLPARAM{trav}%' , '%URLPARAM{fundstat}%' , '%URLPARAM{fundsrc}%');"}%
" }%

We see the exact same behavior using standard IfStatements or the IfDefinedPlugin. I realize the SQL database could be configured to not allow empty values, but then I believe an error would be seen every time the page is viewed (until the specified value isn't empty).

Any suggestions would be greatly appreciated.

-- DevinBougie - 05 Jun 2007

We are trying to use conditional formatting in a database query, which seems to fail. Our requirement is to display nothing if the tsport is set to 0. Is there any other way to achieve this..?

%DATABASE_SQL{description="twiki"  format="|$slot|$blade|$ip|%IF{"0 < $ tsport " then="[[telnet://$tsip:$tsport][$tsport]]" else="" }%|" header="| *Slot #* | *Blade* | *IP Address* | *TS Port Number* |" sql="SELECT slot,blade,ip,tsport,ts.tsip FROM rnc,ts where rnc.rnc = ts.rnc"}%
-- PvSujith - 02 Jul 2007

To get this plugin to work with a remote database, you will have to modify line 46 of DatabasePlugin.pm as follows:

#my $sth = $dbinfo->{$db}->prepare($cmd);
my $sth = $db->prepare($cmd);
This is because when the plugin connects to a remote database, the configuration is fetched from the configuration table in the remote database and not from the configuration hash specified in the LocalSite.cfg

-- AlokNarula - 20 Jul 2007

I've updated the DATABASE_SQL.pm file so that it is possible to use functions in the SQL code. For example;

%DATABASE_SQL{description="mysql" sql="SELECT count(name) from myserverstable WHERE status='Live' " header="*Number of servers:* " separator=" " format="$count(name)"}%

The change to the file is as below;

myserver # diff -c DATABASE_SQL.pm DATABASE_SQL.pm.orig
*** DATABASE_SQL.pm     Thu Dec  6 10:57:25 2007
--- DATABASE_SQL.pm.orig        Tue Apr 24 18:06:07 2007
***************
*** 20,28 ****
              my $row = $format;
              # reverse sort so we handle longer keys first
              foreach my $k (reverse sort keys %$res) {
!               ($z = $k ) =~ s/\(/[\(]/g;
!               $z         =~ s/\)/[\)]/g;
!               $row =~ s/\$$z/$res->{$k}/g;
              }
              $result .= $row.$separator;
          }
--- 20,26 ----
              my $row = $format;
              # reverse sort so we handle longer keys first
              foreach my $k (reverse sort keys %$res) {
!                 $row =~ s/\$$k/$res->{$k}/g;
              }
              $result .= $row.$separator;
          }

-- SallyHoughton - 06 Dec 2007

I filed a bug for SQLite support, Bugs:Item5401. There is example of how I got it workingwith both mysql and sqlite. If it's correct then a "real" developer could fix it in subversion.

-- LarsEik - 29 Feb 2008

I've got this DatabasePlugin working on my TWiki 4.2.0, Windows Server 2000 SP4, Apache 2.2.8, mod perl, mod_auth_sspi, ActiveState Perl 5.8.8 build 822, Oracle 10g Express installation. Here is how I did it for your reference:

How I setup the DatabasePlugin

1. Install the plugin by copying the contents of the zip file to the TWiki folder.

2. Pasted the following into my lib\LocalSite.cfg

#---+ DatabasePlugin
# Database access information can either be configured here,
# or it can be recovered from a remote database.
# <p />
# If a remote database is used, it must contain a table with the
# following fields:
# <ul>
# <li>description - A symbolic name for the database.</li>
# <li>driver - Database driver</li>
# <li>db_name - The name of the database</li>
# <li>db_sid - The SID of the database (optional: required for Oracle)</li>
# <li>ro_username - The user name to use</li>
# <li>ro_password - The password for the specified ro_username</li>
# <li>hostname - The name of the host on which the specified db_name lives</li>
# </ul>
# The table specifies all the available databases.
# <p />
# **SELECT Local,Remote**
# Specifies where to get database configuration information from. 'Local'
# gets it here, 'Remote' looks up a database.
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigSource} = 'Local';
# **STRING 30**
# <h2> Setup for Remote database table</h2>
# If {ConfigSource} is Remote, specifies the name of the host that
# serves the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigHost} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the driver for the DB that
# serves the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigDriver} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the name of the database that
# contains the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigDB} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the sid of the database that
# contains the database info table (not required for MySQL)
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigSID} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the name of the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigTable} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the username for the database that
# contains the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigUsername} = '';
# **STRING 30**
# If {ConfigSource} is Remote, specifies the password to the database that
# contains the database info table
$TWiki::cfg{Plugins}{DatabasePlugin}{ConfigPassword} = '';
# **STRING 30**
# <h2> Setup for DATABASE_EDIT (optional)</h2>
$TWiki::cfg{Plugins}{DatabasePlugin}{EditURLPrefix} = 'http';
# **STRING 50**
# This can either be a relative path or absolute.  If absolute, then it
# would look something like machine.com/path_to_place_for/phpMyAdmin
$TWiki::cfg{Plugins}{DatabasePlugin}{EditURLPath} = 'appsweb.argyll-bute.gov.uk:8080/interfaces/';
# **PERL**
# <h2>Setup for Local databases table</h2>
# Table of configuration info for all the databases you might access.
# Used if {ConfigSource} is Local.
# This structure is an array of database definitions. Each database
# is defined using a hash where the fields of the array are:
# <ol>
# <li> description - Symbolic name for this database</li>
# <li> driver - DB driver - values like: mysql, Oracle, etc.</li>
# <li> hostname - DB host</li>
# <li> database - DB name</li>
# <li> sid - DB sid (Oracle, ignore for MySQL)</li>
# <li> username - DB username</li>
# <li> password - DB password</li>
# <li> table_name - optional table name, used with DATABASE_TABLE and DATABASE_REPEAT to predefine which table to look up
# </ol>
$TWiki::cfg{Plugins}{DatabasePlugin}{Databases} = [
                                                    {
                                                      'sid' => 'xe',
                                                      'hostname' => 'appsweb',
                                                      'description' => 'interfaces_database',
                                                      'username' => 'interface',
                                                      'database' => 'xe',
                                                      'password' => '1nt3rf4ce',
                                                      'table_name' => '',
                                                      'driver' => 'Oracle'
                                                    }
                                                  ];
$TWiki::cfg{Plugins}{DatabasePlugin}{Enabled} = 1;

3. Installed DBD-Oracle 1.17 for ActiveState Perl 5.8.8 using the ppm utility. Use "ppm gui" command to make it real easy for you.

4. Since I already had Oracle 10g Express installed on the same server I next ensured that my tnsnames.ora contained the correct entry for the database "xe". The necessary entry was already there and here it is for your info:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = appsweb.argyll-bute.gov.uk)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

The tnsnames.ora file was located in D:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN

5. Set the ORACLE_HOME environment variable in Control Panel > System > Advanced > Environment Variables > System Variables:

ORACLE_HOME was set to have a value of D:\oraclexe\app\oracle\product\10.2.0\server

6. Restart Apache 2.2.8 web server.

7. Hey presto it works!

-- JamesGMoore - 01 Jul 2008

If there is a fault in query and/or database server is hanging, then it's currently not possible to have a look at the page itself. Is there an option to set a timeout?

-- MichaelDeckert - 06 Aug 2008

I installed Twiki on windows machine. Also installed Database plug-in through configure interface. I am from Windows background (.Net) so new to perl script. I want to use Microsoft SQL Server 2005 as database for Database plug-in. Where I want to specify machine name where db hosted, db name, user name and password to connect the database using database plug-in.

Please, can you help me. Any pointer to these queries will be a great help.

-- RahulWagh - 13 Aug 2008

How to execute Stored procedure or function? I tried Function execution which has complex query, it throws exception. Complex query mean it contains Outer Join, Inner Join etc.

Any pointer will be helpful.

-- RahulWagh - 19 Sep 2008

I cannot get this working with an MSSQL database. This plugin needs an update so that it can connect to Oracle and MSSQL out of the box which are the 2 main databases out there.

-- JamesGMoore - 19 Sep 2008

Does this work against SQLite? Thanks.

-- MartinCleaver - 30 Sep 2008

I had to add a little check in Connect.pm since SQLite has a different connect-string:

sub connect {
    my $this = shift;

    unless ($this->{db}) {
        my $sid = $this->{sid} ? ";sid=$this->{sid}" : '';
        my $dbn;
        if (lc( $this->{driver}) eq 'sqlite' ) {
                $dbn = "dbname";
        } else {
                $dbn = "database";
        }

        my $db = DBI->connect(
            #"DBI:$this->{driver}:database=$this->{database};host=$this->{hostname}$sid",
            "DBI:$this->{driver}:$dbn=$this->{database};host=$this->{hostname}$sid",
            $this->{username}, $this->{password},
            {PrintError=>1, RaiseError=>1});
        if (! $db ) {
            die "Can't open database specified by description '$description'";
        }

        $this->{db} = $db;
    }
}

-- LarsEik - 30 Sep 2008

What is wrong when I use a "group by" function then just the first and last row will be displayed?

-- PeterDobler - 24 Oct 2008

I use this plugin to allow users to modify contents of my database. The problem is that I would like to restrict certain users from being able to modify data, even though all users have access to the twiki page. The DBIQueryPlugin does this through Usermaps, where the twiki user is mapped to a specific set of database access credentials, even though the same connection name is used. How can I achieve the same with this plugin? Can I, for instance, use a twiki variable, as my connection name? That way, I could change the twiki variable depending on the logged on user. Are there any plans to support twiki user to database user mapping?

-- DipuDeshmukh - 2009-09-27

This looks like a sensible enhancement for this plugin. TWiki is an open source project. You can get involved in defining the spec, and if you are a programmer you could contribute a patch. ReadmeFirst has the details how to get started.

-- PeterThoeny - 2009-09-28

does this database plugin support tsql, ms sql connections? thanks.

-- JeremiahJester - 2012-04-10

if not, is there an available twiki plugin that does?

-- JeremiahJester - 2012-04-10

Hi, looking for a way to perform nested queries using only DATABASE_SQL. As the provided patch is pretty old, it seems to refer to the deprecated macros.

%DATABASE_SQL{ 
description = "twiki"  
sql = "SELECT * FROM my_table"  
format = " %DATABASE_SQL{ description="twiki" sql="SHOW COLUMNS FROM my_table" format=" $Field, " }% "  
}%

Tried different escaping solutions (included that provided for nested search), but no luck.

Any idea?

Thanks

-- Thomas Fozzi - 2017-01-17

Topic attachments
I Attachment History Action Size Date Who Comment
Unknown file formatpatch DatabasePlugin-nested-queries.patch r1 manage 1.1 K 2006-05-20 - 13:14 KarlSkidmore Nested query loops patch
Unknown file formatpatch MSSQL-and-More-Error-handling.patch r1 manage 7.4 K 2005-03-04 - 09:04 MartinRothbaum Patch to 1.3 for MSSQL and further error handling (includes Heather's patch)
Unknown file formatpatch PG-and-Error-handling.patch r1 manage 6.4 K 2004-07-23 - 22:56 HeatherSherman Patch to 1.3 for PostgreSQL support and more rror handling
Unknown file formatpatch more-and-more-error-handling.patch r2 r1 manage 8.1 K 2005-09-22 - 20:26 StevenLumos Protect prepare statements with eval too as required by Oracle. (Includes Martin and Heather's patches.)
Edit | Attach | Watch | Print version | History: r75 < r74 < r73 < r72 < r71 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r75 - 2017-01-17 - ThomasFozzi
 
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.