[pmwiki-devel] More database standards

[marc]

Ben Stallings said...
> Hi, Marc.  Thank you for posting that function excerpt; it makes things 
> much clearer.  I think I see now where our strategies differ.
> 
> Your function acts on three tables, which it has already decided it is 
> going to call 'products', 'orderitems', and 'orders', regardless of what 
> they may actually be called.  It is therefore necessary to tell the 
> function which actual tables these handles refer to, and in which 
> database those tables are located.  You propose doing this through a 
> global array $DBTables, defined in config.php, of the format
> 
> $DBTables[handle] = array('database'=>database, 'table'=>table)
> 
> Am I right so far?

Pretty much. Note that $DBTables[handle] may well contain more than two 
items - as with ADOdbConnennect, which passes the encryption method and 
user field.

> The recipes I'm working with, in contrast, are so general in purpose 
> that they don't refer to their tables by handles; they use variables 
> that they are passed.

I have a library of similar classes that I'm using for general db 
maintenance and building ad hoc objects. Nevertheless, I find the 
$DBTables idea useful because I can name an object or 'db handle' as a 
PmWiki markup arg and magic happens. Because all the rules relating to 
the objects are within the class, I can then create markup for these 
that perform whatever function is required. (I also have a generic 
markup that allows me to call any object method - I usually store the 
object temporarily in a session variable in such cases.)

> A separate object is created for interacting with 
> each database connection, and the connection name is passed when the 
> object is created and is stored as $object->db so that it need not be 
> passed in any of the function calls.

Sure, in some cases - like the maintenance hacks I mentioned above - 
that makes sense - in fact, my dbhack class starts:

  Class dbHacks {
    var $db;
    var $connected = false;

    function dbHacks($dbase) {
      $this->db = ADOdbConnect($dbase);
      if (is_object($this->db)) $this->connected = true;
    }

but it's not the sort of thing that's needed for general objects in the 
real world.

> The table names are passed in the 
> function calls because they tend to be different each time.

Not doing this was a conscious design decision on my part. Nevertheless, 
I retain that functionality, because I can change the values of 
$DBTables in the PmWiki Way by page or by markup, if I wish. The benefit 
from my perspective is not having to generate any code to handle and 
manage it. (If this wasn't PmWiki and I were doing it just for myself, 
then I'd simply make $DBTables a singleton object and encapsulate 
everything.)

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

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

It's plenty general, if it needs to be. All it does - basically - is 
provide a handle for a db/table pair i.e. uniquely identifies a table.

Let's go back to the original question: what is the unified approach 
that recipe writers should use to access db tables?

Are you suggesting that the standard should be that folk provided the 
db/table pair in each function call?

No? I thought not. But I don't understand what you are proposing.
 
> >> 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?
> 
> Well, I don't propose to do that, because we decided to go with ADOdb, 
> which doesn't support that.  If we were working with MySQL alone, 
> without ADOdb being involved, the SQL syntax would be, for example,
> 
> SELECT moodle.users.lastlogin,pmwiki.users.lastlogin FROM moodle.users, 
> pmwiki.users WHERE moodle.users.userid = pmwiki.users.userid AND 
> pmwiki.users.userid = $author;

Okay, but this will only work across one engine - and not all engines -  
and, I believe, only where the dbs are on the same machine. So, since we 
are dealing with multiple database types - and in my case, dbs on 
different continents - this is not a limitation of much substance.

> It's not really relevant now that I know what you're talking about, but 
> before I knew that I was speculating about why you wanted to specify the 
> database for every single individual field

Sorry? Who said or did that? Not me guv! I'm the one reducing the syntax 
by avoiding passing around dbs and tables. I'm all for an easy life, 
simplicity and uber-functionality.

> Thanks for helping me understand!

Well, fine, but we need to agree a 'standard'. Right now, there isn't 
one, and that's the reason why I raised the issue - to get a consensus. 
I've spelt out my approach and as yet, no alternative has been offered. 
Bring it on.

-- 
Best,
Marc