Looks very good - thanks! I had to change the ADD statement in the 
upgrade script a little, and I added the change in the whups.sql 
script, but otherwise this seems to work perfectly. Committed, thanks 
much.

The upgrade script version without index creation.

Also note that creating index on LOG_VALUE_NUM is optional unless you 
want to execute sort or lookup operations on LOG_VALUE_NUM field.

The primary performance goal is reached without index - corresponding 
table's primary keys are used for fast lookup.

So the index adding code may be removed from production upgrade.php script...

The patch for php code

Okay I've returned back and fixed this issue.

The solution I enclose contains of:

1. adding LOG_VALUE_NUM field of integer type to WHUPS_LOGS table

2. creating index on LOG_VALUE_NUM for fast JOIN's

3. filling LOG_VALUE_NUM field with numbers from LOG_VALUE field

4. fixing whups/lib/Driver/sql.php code to use LOG_VALUE_NUM in 
JOIN's, cross-type comparisons

5. fixing whups/lib/Driver/sql.php code to insert corresponding value 
into LOG_VALUE_NUM together with LOG_VALUE.



The upgrade.php script does 1,2,3 and driversql.patch does 4,5.

Note that upgrade.php script pretends to be database-portable, no

Oracle-specific stuff is used, SQL operators are ANSI-compliant.

I meant thanks for doing the benchmarking, of course. Sheesh. :)

Thanks very much for doing the typecasting. I'll look into doing the 
optimization that you suggested. If you happen to whip up a patch 
(including conversion script, ideally) for this, that'd be fabulous, 
but I'll try to work on it soonish.

Well I've conducted some benchmarking tests as you asked me for.

In general, the efficiency of both LIKE and CAST() is mostly 
database-specific,

and results vary from one RDBMS to another.

Regarding ORACLE, when no special measures taken, both methods have 
approximately the same performance because both of them cause full 
scan of table WHUPS_LOGS, the most expensive seek operation, which 
overweights the computational costs of LIKE and CAST() evaluation.

When the LOG_VALUE field is made well-indexed (using functional 
index), the CAST() method appears to be a little bit faster.

But this is a minor difference, and requies non-portable DB optimization,

so both methods are bad enough for performance. The reengineering of 
table WHUPS_LOGS is unconditionally preferred... Please see my 
previous comment on this.

BTW, if the goal is performance, the table WHUPS_LOGS should be reingeneered,

i.e. column LOG_VALUE_INT of type INT should be added.

Of course, the php code that maintains this table should be fixed also,

by inserting CHARs into LOG_VALUE and INTs into LOG_VALUE_INT.

When the JOINs are necessary, you use comparison for LOG_VALUE_INT and

foreign table's ID.

This achieves the best performance due to foreign primary key scan;

eliminates cross-field-type comparison, and the performance hits introduced by

any implicit/explicit typecasting, LIKE predicate overhead which introduces

full table scans. And obviously this technique is guaranteed portable.

So if you won't make these architectural changes (however they are 
preferred), I'll do the benchmarking you asked for, there's no problem.

Well, I'm not thinking of anything other than either LIKE or casting. 
Would you be willing to do a little benchmarking of each solution? I'd 
like to know just how much the LIKE solution would hurt performance 
(or not) before making a decision.



Thanks again for all your Oracle work and feedback.

Hmm, I just have one idea - using LIKE operator here instead of comparison.

This causes implicit conversion to char type for ID fields,

and probably is much more portable than explicit typecasting.

For example (whups/lib/Driver/sql.php, line 822):



         $join .= 'LEFT JOIN whups_queues ON whups_logs.log_type = 
\'queue\' AND whups_logs.log_value LIKE whups_queues.queue_id ';



I don't pretend that this idea is clever enough, however it may be a 
solution :-)

Urg. I don't think that cast() solution is really going to work across 
all of the databases we try to support.



Unless you have another clever idea, my next best one is to add 
support for cross-field-type comparisons to Horde_SQL and get the 
clauses from there.

Or even cast it to VARCHAR sufficient length to contain full ID value:



$join .= 'LEFT JOIN whups_queues ON whups_logs.log_type = \'queue\' 
AND whups_logs.log_value = cast(whups_queues.queue_id as varchar(16)) ';

Attempt to fetch comments, file whups/lib/Driver/sql.php,v 1.195,

function _getHistory().

The JOINs fail on Oracle, for example line 823:



$join .= 'LEFT JOIN whups_queues ON whups_logs.log_type = \'queue\' 
AND whups_logs.log_value = whups_queues.queue_id ';



The clause whups_logs.log_value = whups_queues.queue_id causes an 
error ORA-017222: invalid number because whups_queues.queue_id is 
NUMBER, whups_logs.log_value is VARCHAR and contains non-numeric 
values (comments itself). So implicit type conversion is failed.

Suggest explicit portable typecasting for NUMBER fields in all JOINs, 
something like this:



$join .= 'LEFT JOIN whups_queues ON whups_logs.log_type = \'queue\' 
AND whups_logs.log_value = cast(whups_queues.queue_id as character) ';