change dblog.module as SQL friendly

this patch include v1.0, in addition modify the GROUP BY query into more formal format.

the original query trying to get variables from GROUP BY result, without locate it within GROUP BY. although it is function within mysql, but it is not oracle friendly; on the other hand, the patched version should give out the same result as original.

i check dblog.install but:

<?php
// $Id: dblog.install,v 1.3 2007/05/25 12:46:44 dries Exp $

/**
 * Implementation of hook_install().
 */
function dblog_install() {
  // Create tables.
  drupal_install_schema('dblog');
}

/**
 * Implementation of hook_uninstall().
 */
function dblog_uninstall() {
  // Remove tables.
  drupal_uninstall_schema('dblog');
}
?>

seems the update of watchdog table from previous version is still not yet handle currently... i have no idea about how to do so ;)

thanks for bug fix, patch also function under oracle :)

@bjaspan: agree about your point of view, but some more question: as LOBs patch is now almost ready (http://drupal.org/node/147947#comment-278902), should "message" and "variables" using "clob:big", rather than "text:big"?

we will face some conflict, if using "text:big". in case of MySQL, it means we are allowing a GROUP BY action with huge data (text:big => longtext => max. 4GB), queries may need to be review in order to prevent such actions; on the other hand, in case of latest version of Oracle driver implementation (http://drupal.org/node/39260#comment-278926), we will only have 4000 characters size for storage ("text:big" => VARCHAR2(4000) => max. 4000 characters), which may be not enough for our log message :(

according to LOBs patch, we may choose "clob:big" as alternative, but still need some changes. "clob:big" provide enough storage space within all DB driver implementation (MySQL's "clob:big" => longtext => max. 4GB; Oracle's "clob:big" => CLOB => max. 128TB), but also means we should never use it for SELECT DISTINCT, ORDER BY or GROUP BY queries (please refer to "Restrictions on LOBs"). similar as above problem, we still need to rewrite some core queries, for those GROUP BY actions.

my suggestions: 1. fields should reset as suitable type, to prevent conflict with old DB schema; 2. queries should also review, to prevent GROUP BY with huge data (not enterprise friendly...); 3. "clob:big" may also be a good choice: to clarify its storage nature, with suitable handling :)

i use the following code to check the current status of my blob's "watchdog" table, and it return $length as 3361. i keep logs for 8 weeks, so this checking should be meaningful:

<?php
$result = db_query("SELECT message FROM {watchdog}");
$length = 0;
while ($row = db_fetch_array($result)) {
  if ($length < strlen($row['message'])) {
    $length = strlen($row['message']);
  }
}
print_r($length);
?>

if this checking is meaningful, setting watchdog's fields as "text:big" should be acceptable: 1. record size is small enough, according to GROUP BY query (it is not query friendly, but acceptable...); 2. it will be suitable for oracle implementation, as "text:big" => VARCHAR2(4000), which support maximum 4000 characters (will not generate error message...)

P.S. we shall consider about change these fields into CLOB type and rewrite some queries, if it is possible to record somethings larger than 4000 characters into "watchdog" :)

such "3361" error message is generated by aggregator.module, beased on duplicate insert (the error message include the feed body without any trimming...). as we never know how large of incoming feed (what will happened if we face duplicate insert of feed with 10000+ characters?), it may be a needs for changing fields into CLOB and rewrite some queries into query friendly :(

P.S. some minor suggestion: may be don't log the "complete" message, but just part of it? it will be much query friendly, if we trim log message into acceptable size :)

patch in #12 should be acceptable within MySQL, and latest oracle driver implementation (http://drupal.org/node/39260#comment-280402). it should be ready for commit, and rollback the incorrect data type for backward compatibility.

it is no longer a problem, at least for oracle driver rc4. since oracle driver map all "text" type into VARCHAR2(4000) (the best that oracle can handle...), rc4 try to trim all %s substitution into maximum 4000 characters. no error message will be generated even inserting a dblog with 10000+ characters, oracle can handle first 4000 only, and so do it :)

on the other hand, i try to review the GROUP BY query: it is purely based on "message" and "variables", which is "text:big" in size. as we all understand the trade-off for running GROUP BY with something which MAYBE in maximum size of 4GB (within MySQL implementation), but still implement such new feature within dblog, just feel free to keep it on going :)

in case of MySQL, it is an existing implementation. we choose functionality even understanding the trade off, so just let it be; in case of oracle, it can't handle something larger than 4000, so we can only try the best, to save log message as long as it can. this will no longer become a "query friendly" problem for oracle, since trimmed data will within acceptable sizing, based on oracle limitation :(