[pmwiki-users] most user-friendly method of database configuration

[marc]

Ben Stallings said...
> At risk of belaboring this thread... although we seem to have agreed on 
> a $Databases global variable for ADODB configuration, we have yet to 
> agree on the parameters, i.e. the keys of the subarray.
> 
> I had been using 'type','server','name','user', and 'pass', but 
> referring to the ADODB documentation I see
> 
>   The dsn format is:
>   $driver://$username:$password@hostname/$database?options[=value]
> 
> So now, for consistency with the documentation, I'm inclined to use
> 
> $Databases['connection_name'] = array(
>   'driver' => 'mysql',
>   'hostname' => 'db.example.net',
>   'database' => 'database_name',
>   'username' => 'user_name',
>   'password' => 'example_password');
> 
> But this is complicated by the Microsoft databases, which require both a 
> database type and a driver to be specified!
> 
> Marc, you said you've used ADODB in recipes before... do you have a good 
> generalized connection routine that the rest of us could copy 
> shamelessly?

There's not much you can do to shorten it, as far as I know. It would be 
good to be able to extend ADOConnection so that you could use something 
like:

  $mysqldb = new datab($mysqldsn);
  $postgdb = new datab($postgdsn);

or

  $pmwikidb['blah'] = new ...

in our case. That way, we could wrap up a lot of this stuff in class.

But there's a fair bit of stuff in ADONewConnection that needs taking 
care of. You might also want to look at ADOdb lite, which I find to be 
quite a bit faster than its parent. You can extend ADOConnection in this 
case, but the author has used that to do, such as:

  class mysql_driver_ADOConnection extends ADOConnection

Nevertheless, we could replace ADONewConnection and wrap things up in a 
class more easily using it.

Anyway, I just crudely do this:

  $db = ADONewConnection($sys_dbtype);
  if ($dbdebug) $db->debug = true;
  $db->NConnect($sys_dbhost,$sys_dbuser,$sys_dbpasswd,$sys_dbname);
  if (!$db->IsConnected()) {
    do stuff
  }

Note that ADONewConnection creates the object, so (is_object) will 
succeed on it even when a subsequent connect fails. So, recipe writers 
certainly need to check ->IsConnected /after/ the suggested

  if(!is_object($$MyNamespaceConnection)) {
      $$MyNamespaceConnection = NewADOConnection($Databases 
      [$MyNamespaceConnection]['driver']);
      $$MyNamespaceConnection->Connect(
         $Databases[$MyNamespaceConnection]['hostname'], 
         $Databases[$MyNamespaceConnection]['username'], 
         $Databases[$MyNamespaceConnection]['password'], 
         $Databases[$MyNamespaceConnection]['database']);
  }

irrespective of whether they perform the connect.

I have a habit of reporting all sorts of failures via email; mainly so 
that when I travel I'm notified asap with enough info to hopefully 
diagnose the problem. We might want to consider providing a number 
generic reporting methods that folk can activate or override.

It's important not to halt processing just because the db connect has 
failed. For example, the script could be the callback from a bank 
transaction, so we will still want to, say, email a bunch of data that's 
been passed through, and send the customer something reassuring.

What I do have is a method for assigning specific IPs from
  $_SERVER['HTTP_HOST']
This is so that I don't have to fiddle about with db configs too often 
when checking out from Subversion on any particular machine. The 
database can be anywhere, but are usually localhost or a fixed IP. So, 
it's just a mapping of hostname to database IP (which is rarely the web 
server IP, in my case). But this is better placed outside a public 
recipe, of course.

So, not a script as such, but a few things to consider moving forward.

-- 
Best,
Marc