[kronolith] kronolith and oci8 - success!

[Liam Hoekenga]

I think I've suceeded in making kronolith work with oracle 8.

The first big problem is Oracle's default date format... Oracle can be made to 
use a date format that's more like MySQL by setting the NLS_DATE_FORMAT 
variable.  The easiest place to do this is in init.ora (which will make it a 
system wide change).  You'd add a line like:

nls_date_format = 'yyyy-mm-yy hh24:mm:ss'

But that's not so good if you're not the DBA and don't know who you'll be 
screwing up by changing that setting.  Acc'd to all of the stuff I've read, you 
/should/ be able to set that as an environment variable (Apache's SetEnv, PHP's 
putenv, etc).  In practice, this doesn't do a damn thing (at least on my 
machines.. solaris 8, oracle 8.1.7 client, 8.1.5 on the server).  I had to hack 
the PEAR::DB oci8 driver..  This is super ugly, but, I added this at the end of 
the connect function, right before "return DB_OK":

    $stmt = @OCIParse($conn, 
        "alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'");
    @OCIExecute($stmt, OCI_DEFAULT);

theoretically altering it for each oracle session that's opened via PEAR.

After this, I could see that kronolith was actually able to store information 
in the database, but not retrieve it.  So.. I dug deeper (adding a bunch of 
logMessages - to kronolith/lib/Driver/sql.php).  I made these two changes:

        $etime = sprintf("'%04d-%02d-%02d 00:00:00.000'", 
            $endDate->year, $endDate->month, $endDate->mday);
        if (isset($startDate)) {
            $stime = sprintf("'%04d-%02d-%02d 00:00:00.000'", 
                $startDate->year, $startDate->month, $startDate->mday);
        }

to:

        $etime = sprintf("'%04d-%02d-%02d 00:00:00'", $endDate->year, 
            $endDate->month, $endDate->mday);
        if (isset($startDate)) {
            $stime = sprintf("'%04d-%02d-%02d 00:00:00'", 
                $startDate->year, $startDate->month, $startDate->mday);
        }

Even with the more MySQL-esque date format, it couldn't handle the milliseconds 
in the query.  When I took those out, it seemed to be happy.

In short..
- change NLS_DATE_FORMAT in ora.tab, or maybe see if the PEAR maintainers 
  might be willing to add another 'portability' option, to force the date
  format change w/ each session
- Do the queries really have to be down to the millisecond?
- All of the horde modules I've played with to date have debugging code in 
  them.  Kronolith doesn't.  Not a Horde::logMessage to be seen anywhere.
  When I added them to the sql.php file (basically lifting the one from
  Turba), I also tried to standarize the variable names with those found in
  Turba's sql.php and Horde's lib/Prefs/sql.php ($query for the $query 
  instead of $q, $results for results instead of $qr, etc).

Might you guys consider dropping the milliseconds from the queries and adding 
some debugging?

I've not really used turba much (not having a database that it'd work with 
until now), so I'm not sure if it's working completely.  It does seem to be 
working tho.

Liam