[pmwiki-devel] More database standards

[marc]

Crisses said...
> 
> On Dec 15, 2006, at 11:46 AM, Ben Stallings wrote:
> 
> > Because I'm coming from this different perspective, I didn't see the
> > point of your $DBTables array.  I couldn't understand why the function
> > needed to look up the names of the tables it was supposed to work  
> > with,
> > because in my framework the names are part of the function call.
> >
> > So now I wonder if maybe the $DBTables array, useful as it is for your
> > purposes, is not general enough to need a place in the Database  
> > Standard
> > but could just be part of the configuration for your recipe(s).
> 
> I'm getting pretty lost.  I could read everything 4 times and maybe  
> understand what's going on, but mainly I'm being left in the dust.

Get with the program, Crisses :-)
 
> What does any of this do with creating a generic standard from which  
> databases can share a connection.  We have something generic,  
> flexible, and so far it's usable.

The shared connection issue is done. However, there are two completely 
different approaches to handling the connection function.

The 'standard' proposes managing $DB and returning true/error message 
when a connection is asked for, while I maintain the 'legacy' of $DB :-) 
but always return the db object.

Beyond that there's nothing defined for recipe writers. I've shown what 
I do, which is a generic solution to the problem, but in the 'standard' 
there is nothing.

> There are definite *problems* such  
> as the database error never getting to the browser.  Is it fixable?

In my setup, db errors are emailed - it's inbuilt. It could be 
parameterised to provide all the usual options. Email might not work for 
those without procmail, although we could easily write something to 
manage multiple emails relating to the same problem.
 
> Once we get into table-level discussions, we're beyond the purpose of  
> allowing many recipes to use the same database connections.

And it's mistaken in this context.

> That's  
> my opinion, and if someone wants to dumb the discussion down into why  
> a database standard is mucking around on the table level, I'd like to  
> hear about it.

Recipes. My 'products' table is your 'prod' table is Fred's 'prd' 
table... How does Mr Recipe writer reference a table?

  SELECT prodesc??? FROM products????? WHERE prodcode??? = 'fred';
 
> Once we get into dictating *yet more* how database recipe authors are  
> going to write their database calls, we've got a serious problem.

Why 'dictating'?

> ADOdb is class based and not every recipe author gets classes as it  
> is.

Well, time to learn, if you are a recipe writer. It'll only take an 
afternoon to understand the basics.
 
> So, if the dust settles and I can understand what's going on, I'd  
> love to help out.  So far, this conversation is pretty far beyond me  
> and I can no longer determine if this newer recipe idea would be  
> usable by recipe authors.

Crisses, all that needs to be agreed is how a recipe writer references a 
table in a recipe. (And in the cases where fields are important, those 
too. For example, 'userfield' in ADOdbConnect().)

The db connections are easy - although Ben prefers to pass them around 
in function parameters, whereas my objects find them on their own 
(although you can dynamically specify a connect if you like).

What this amount to in the real world is something like:

  Ben's world
  (:displayproduct prod=xmaspud db=somedb :)

  Marc's world
  (:displayproduct xmaspud :)
  or override with
  (:displayproduct prod=xmaspud db=someotherdb:)

However, Ben wants to keep table dynamic also, so we have 

  Ben's world
  (:displayproduct prod=xmaspud table=products db=somedb :)

  Marc's world
  (:displayproduct xmaspud :)
  or override with
  (:displayproduct prod=xmaspud db=someotherdb:)
  or 
  (:displayproduct prod=xmaspud table=products db=somedb :)

Just to be clear. I can do this on a PmWiki page right here [1], right 
now. In fact, I can go a lot further than that by applying multiple 
functions to an object and then writing it back to the db, right off a 
PmWiki page. 

Earlier, I gave this SQL as an example - it's hardly typical since it 
joins three tables, but it helps to make the point:

  $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)";

This is a few more characters than the native SQL statement (see below), 
but it can be simplified if all we are concerned about is the number of 
characters in it. This works, but is harder to maintain:

  $sql = "SELECT sidebarDesc, 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 ((userName = '$member') AND (active = 1)) and
         (orderDate >= now() - INTERVAL 12 MONTH)";

The native SQL would be something like:

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

Frankly, for the extra flexibility, I'm at a loss to understand why this 
is being perceived negatively. But hey ho, whatever floats your boat.

-- 
Best,
Marc

[1] If you are new to objects, then a quick read through PHP's 
introspection functions might help here.