[pmwiki-devel] More database standards

[Ben Stallings]

marc wrote,
> Anyway, I've settled on a PmWiki-ish way to handle this that may or may 
> not work for others. It can still utilise the SDV() approach - to 
> maintain that mechanism.
> 
> The nub of it is a two-dimensional array that would usually live in 
> (farm)config.php. Something like:
> 
>   $DBTables = array (
>     'products' => array ('database' => $liveDb, 'table' => 'products')
>     'members'  => array ('database' => $testDb, 'table' => 'users')
>   );
> 
> This can then be used in recipes via [1]:
> 
>   global $DBTables;
> 
> and, say:
> 
>   $db = ADOdbConnect($DBTables['products']['database']);
> and
>   $sql = "SELECT * FROM {$DBTables['products']['table']}
> 
> Hopefully, this makes the problem, and my suggested solution, a bit 
> clearer.

A bit, but could you please POST THE SCRIPT YOU'RE USING to the 
DatabaseStandard page so that we can work with something functional 
rather than just snippets of code out of context?  I don't mean to 
repeat myself -- maybe you only have email access where you are, in 
which case I apologize, but when you do have upload capability at your 
disposal, please upload a script in its entirety.  Thank you.  If I 
sound frustrated, it's only because I'm excited by the work you're doing 
and am eager to try it out.

That said, I have some concerns about where you're going...

You are correct that connection names in the current DatabaseStandard 
are arbitrary; however, they are there for a good reason.  Consider a 
case where you want one recipe to have read-only access to a database 
and another to have full access to the same database.  They need two 
different logins and passwords, which means they need two different 
connections.  If you remove the connection name and just refer to the 
connection by the name of the database (or by the name of a table in 
that database), you lose that control.

Your proposal for a $DBtables array seems to be built on the premise 
that a recipe doesn't know (or hasn't been told) what database 
connection it's supposed to be using, only the table name.  I think 
that's a dangerous assumption, because if you've got more than one 
database or more than one connection to the same database, it's probably 
*because* you don't want every recipe having free reign over all your 
data.  Otherwise you'd just put all your eggs in one basket, right? 
Each recipe should be explicitly told -- or at least provide a way to be 
explicitly told -- which connection(s) to use rather than just given a 
list of which tables belong to which database connections.

I can see how a lookup array of tables and databases would be a useful 
feature, but setting aside for a minute the question of whether it 
compromises security, I fear that setting that array in config.php is 
not the best way to go about it... it requires the admin to do an awful 
lot of typing just to provide information that the server already knows. 
  Rather than specifying the tables you want to use, why not specify 
only the ones you *don't* want to use (by defining them as null) or the 
ones to use under a different name (in case two databases have tables 
with the same name), and pull the names for all the rest using 
MetaTables()?  The connection script could then build the $DBtables 
array (using SDVA so as not to overwrite customized values) 
automatically when it makes the connection, sparing the admin all that 
typing.

Finally... although I see how $DBtables would be useful for reference, 
it seems to me it would be very cumbersome in practice... instead of

SELECT * FROM `$table1`,`$table2` where `$table1.$field1` = 
`$table2.$field2`

you would have to write,

SELECT * FROM `{$DBTables['$table1']['table']}` AS a 
,`{$DBTables['$table2']['table']}` AS b WHERE `a.$field1` = `b.$field2`

How is that an improvement?  It doesn't get past the limitation (imposed 
by ADOdb) that both tables must be on the same database in order to use 
them in the same query.  If the two tables are not in the same database, 
the second notation will fail just as surely as the first.  Please give 
an example where this is an improvement, so I can understand why you 
want to do it.

I don't mean to be critical; I just don't see where you're coming from 
yet.  Thanks again for all your contributions!  --Ben S.