[pmwiki-devel] More database standards

[marc]

Ben Stallings said...
> 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'll try to construct a package for the authuser process tomorrow. As I 
have said, I think that it is liable to confuse folk, since it doesn't 
follow the "PmWiki database standard", but if you are prepared to forego 
that concern then I'm happy to do this. I'll package something that 
works with a clean install, if that makes the most sense.

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

I can't compare with Dan's enthusiasm, but yes it is proving to be 
enormously productive. The fact that one can write static methods 
(within a class) and then use them in a few seconds with some additional 
markup on PmWiki pages is very useful - we can probably do this 
generically (for any static method) by using an object factory and 
exploiting PmWiki's markup and ParseArgs. I'm also storing objects 
(mainly singletons) as session variables which is reducing database 
activity in a measurable way - I should write a class to manage this, 
really. Also, populating objects and displaying their contents is 
proving useful - simply via:

  (:displayobject member=someid:)

- it's like having context aware on-tap SQL queries. Sometime you don't 
need formatted data, just being able to get your hands on it is enough.
 
> That said, I have some concerns about where you're going...

Well, I've already gone there, but I have tried to obtain a consensus.
 
> 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.

Sure. Logically, you can think of that as two unrelated db connections. 
No problem. It's kind of odd, but a simple use case.
 
> 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.

On the contrary, that's what it seeks to clarify.

What I have is an arbitrary number of db connections. e.g.:

$Databases['localdb'] = array(
	'driver'	=> 'mysql',
	'hostname'	=> 'localhost',
	'database'	=> 'livedb',
	'username'	=> 'fred',
	'password'	=> 'somepass');
$Databases['testdb'] = array(
	'driver'	=> 'mysql',
	'hostname'	=> 'localhost',
	'database'	=> 'testdb',
	'username'	=> 'testdude',
	'password'	=> 'funkypass');

In addition, I have, say:

$DBTables = array (
	'members'  => array (
		'database'	=> $liveDb,
		'table'	=> 'members',
		'userfield'	=> 'userName',
		'encrypt_f'	=> 'md5'),
	'productsreadonly' => array (
		'database' => 'testdb',
		'table' => 'products')
	'products' => array (
		'database' => $liveDb,
		'table' => 'products')
);


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

It's more than that. Not just each recipe, but each object needs to 
'know'. However, at some point, you have to map the code to a physical 
db/table. Whether you have appropriate rights at that point is an admin 
config choice.

However, my thinking is the opposite of what you stated. I don't think 
of the recipe/object as being told, but that the object/recipe 
interrogates to discovery what it should be using. This is key - I 
absolutely do want to be passing that information around.
 
> 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

It doesn't.

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

But how does it know?

Sure you can create an array of db => tables on db connect, but these 
have to be labelled/keyed so that they can be mapped for usage. You 
could make a rule that said: If I'm using 'db connection' and it has a 
table called 'products' then use the table called 'products', but that's 
horrible once converted to a bunch of conditional code spread all over 
the place.

(And, as it happens, my version of $AUDBaseTable has four entries 
compared to thirteen in the cookbook version, because the rest are 
implicit in the class, so it's less config in reality.)

I'm using three dbs and twenty five tables currently. These comprise 
fifty lines in config.php. It's really not a problem. The very cool 
thing, though, is that I can switch to a different db setup by changing 
one variable. Live to test in a second. Shiny!

> 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()?

I don't understand. Say I have schema with... looks at client's 
schema... one hundred and twenty odd tables, why would I want to spend 
time not defining them? (Sorry, Brit irony.)

The problem of duplicate table names isn't a problem because they are 
defined as a db/tablename pair. Clearly, you can't have the same table 
name twice (or more) within a schema, so this solves that 

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

There isn't much typing. But, even if there were, the upside is 
significant. In most cases, all you need for a table is:

 'products' => array ('database' => $liveDb, 'table' => 'products')
 
> 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`

Yup, it's not pretty, but that's the cost of flexibility.

That said, please examine the PHP function 'extract'.

What I tend to do is:

  global $DBTables;
  extract ($DBTables['members']);

where the names are reduced to simple $-variables. However, where there 
are two or more tables being used, one can use:

  global $DBTables;
  extract ($DBTables['members'], EXTR_PREFIX_ALL, "memb" );
  extract ($DBTables['products'], EXTR_PREFIX_ALL, "prod");

I dealt with the following today:

  $sql = "SELECT products.sidebarDesc,  products.vidUrl
          FROM orders
          INNER JOIN order_items ON (orders.transId=order_items.transId)
          INNER JOIN products ON
            (order_items.productCode=products.productCode)
          WHERE ((orders.userName = '$member') AND
                (order_items.active = 1)) and
                (orders.orderDate >= now() - INTERVAL 12 MONTH)";

This becomes:

  $sql = "SELECT $prod_table.sidebarDesc, $prod_table.vidUrl
          FROM $orders_table
          INNER JOIN $orditems_table ON
            ($orders_table.transId=$orditems_table.transId)
          INNER JOIN $prod_table ON
             ($orditems_table.productCode=$prod_table.productCode)
          WHERE (($orders_table.userName = '$member') AND
                ($orditems_table.active = 1)) and
                ($orders_table.orderDate >= now() - INTERVAL 12 MONTH)";

by using:

  global $DBTables;
  extract ($DBTables['products'],EXTR_PREFIX_ALL,'prod');
  extract ($DBTables['orderitems'],EXTR_PREFIX_ALL,'orditems');
  extract ($DBTables['orders'],EXTR_PREFIX_ALL,'orders');

> How is that an improvement?

This really isn't the point. The improvements are elsewhere, more in 
terms of flexibility not the trivial addition syntax required to support 
it.

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

And how do you propose to do that? What would be your SQL syntax?

> 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 have no idea what you are asking.

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

LOL

-- 
Best,
Marc