--
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
- 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).
- A database can be configured to have better security than a Unix flat file.
My requirements were
- No clear text database names, user names, and passwords in TWiki pages so this information has to be hidden somewhere.
- Users of this plugin would not need to know Perl to manage things
- Support access to multiple databases from a single TWiki page
- 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.
- 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:
- 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.
- 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
--
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:
- 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.
- 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
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