Hey guys,

I am (fairly) new to drupal, when I reccomended for my sporting organisation to go with a specific hosting company I didn't do my research properly. My search box didnt work, because my hosting provider does not give out the mysql CREATE_TEMPORARY_TABLES right/permission.
I tried using trip_search. I didnt like it. I tried some patches from other people and couldnt get it to fly without other problems.

I 'invented' a solution: remove drupals db_query_temporary function and replace it with my own.

Heres a quick rundown:
1. code calls db_query_temporary
2. perform the requested query
3. read first result of the query
3.1 for each column, guess the datatype
4. drop any tables that have been previously created here that are older than predefined time
5. find a table name that hasnt been used
6. use column definitions and new name to create a new persistant table
7. insert the query results into the new table
8. return the tablename, and a reference to the query restults in an associative array

Before I go further I'd like to say its far from perfect:
*Its not a seamless solution; i have changed the return type from a result-identifier, to an associative array: with keys of 'table' for new table name, and 'query' for a handle to the query result table.
* guessing a columns datatype based on 1 record could lead to datatype mismatches. (somebody else can add logic for more types if they'd like)
* the performance of guessing columns, searching, and inserting into a new table, with lookups into other tables is worse than the original situation. But we wouldn't have to do this if it worked in the first place ;)
* if no results are returned we assume a search is being done and create a table that matches a search result. Poeple just looking for a fix to search.module can remove the guessing code and leave this.

** Note you will have to execute some sql on your db server for this to work.
a) deploy the code, with line 67 uncommented. do a search. redeploy the code with line 67 commented out again. (ie the create_temporary_tables_index(); line.
b) copy & paste the sql from the create_temporary_tables_index function, replacing TEMPORARY_TABLE_INDEX (with fullstops and FULL quotes either side) with 'temporary_table_index'

There are two files to edit and one new one to add
change 1: modules/search.module
replace the do_search function like so (leave out the <?php and ?> tags)

<?php
function do_search($keywords, $type, $join1 = '', $where1 = '1', $arguments1 = array(), $select2 = 'i.relevance AS score', $join2 = '', $arguments2 = array(), $sort_parameters = 'ORDER BY score DESC') {
 
$query = search_parse_query($keywords);

  if (

$query[2] == '') {
   
form_set_error('keys', t('You must include at least one positive keyword with %count characters or more.', array('%count' => variable_get('minimum_word_size', 3))));
  }
  if (
$query === NULL || $query[0] == '' || $query[2] == '') {
    return array();
  }

 

// First pass: select all possible matching sids, doing a simple index-based OR matching on the keywords.
  // 'matches' is used to reject those items that cannot possibly match the query.
 
$conditions = $where1 .' AND ('. $query[2] .") AND i.type = '%s'";
 
$arguments = array_merge($arguments1, $query[3], array($type, $query[4]));
 
$result = db_query_temporary("SELECT i.type, i.sid, SUM(i.score * t.count) AS relevance, COUNT(*) AS matches FROM {search_index} i INNER JOIN {search_total} t ON i.word = t.word $join1 WHERE $conditions GROUP BY i.type, i.sid HAVING COUNT(*) >= %d", $arguments, '');

 

// Calculate maximum relevance, to normalize it
 
$normalize = db_result(db_query("SELECT MAX(relevance) FROM " . $result['table']));
  if (!
$normalize) {
    return array();
  }
 
$select2 = str_replace('i.relevance', '('. (1.0 / $normalize) .' * i.relevance)', $select2);

 

// Second pass: only keep items that match the complicated keywords conditions (phrase search, negative keywords, ...)
 
$conditions = '('. $query[0] .')';
 
$arguments = array_merge($arguments2, $query[1]);
 
$result = db_query_temporary("SELECT i.type, i.sid, $select2 FROM " . $result['table'] . " i INNER JOIN {search_dataset} d ON i.sid = d.sid AND i.type = d.type $join2 WHERE $conditions $sort_parameters", $arguments, '');

  if ((

$count = db_result(db_query("SELECT COUNT(*) FROM " . $result['table']))) == 0) {
    return array();
  }
 
$count_query = "SELECT $count";

 

// Do actual search query
 
$result = pager_query("SELECT * FROM " . $result['table'], 10, 0, $count_query);
 
$results = array();
  while (
$item = db_fetch_object($result)) {
   
$results[] = $item;
  }
  return
$results;
}
?>

change 2: includes/database.mysql
Insert the following after the '$Id:' line
include_once("temp_tables_hack.inc");
Also, remove the db_query_temporary function. yes all of it.

change 3/ new file
create a new fiile called 'temp_tables_hack.inc' place in it the following (including <?php and ?> tags)

<?php

/**
 * temporary tables hack
 *
 * Pretend to make temporary tables, but do it with proper 'permanent' tables. 
 * We will drop the table later when its not being used anymore
 *
 * NOTE: only currently called from database_mysql, not mysqli r postgre (though mysqli may be the same)
 *
 * @version $Id$
 * @copyright 2006
 */

 //name for the lookup table
 

define('TEMPORARY_TABLE_INDEX', 'temporary_table_index');
 
//prefix for the actual tables
 
define('TEMPORARY_TABLE_PREFIX', 'z_temp_table_');
 
//define how long we will keep the tables for before we delete them (in minutes)
 
define('TEMPORARY_TABLE_DURATION', 30);
 
/**
 * Drop all the manually created temporary tables.
 * after all the temp tables have been dropped;
 * drop the index table also
 */
function drop_temporary_tables()
{
 
$sql = "SELECT tablename from " . TEMPORARY_TABLE_INDEX;

 

$handle = db_query( $sql );

  for(

$i = 0; $i < db_affected_rows( $handle ); $i++ ) {
   
$table = db_fetch_array( $handle );
   
db_query( "DROP TABLE IF EXISTS {$table[tablename]}" );
  }
 
db_query( "DROP TABLE IF EXISTS " . TEMPORARY_TABLE_INDEX );
}

/**
 * Create the table structure required to use the temp_table
 */
function create_temporary_tables_index()
{
 
$sql = "
  CREATE TABLE `"
. TEMPORARY_TABLE_INDEX . "` (
    `ttable_id` INT NOT NULL AUTO_INCREMENT ,
    `created` TIMESTAMP NOT NULL ,
    `tablename` VARCHAR( 255 ) NOT NULL ,
    PRIMARY KEY ( `ttable_id` ) ,
    INDEX ( `created` )
    ) COMMENT = 'Holds a list of manually-created temporary tables' CHARACTER SET utf8;
  "
;

 

db_query( $sql );
}

/**
* create temporary tables hack to work even though we dont have the CREATE TEMPORARY TABLE priv.
*
* Theres a long way this can go, but i'd start with the logic to determine column types
*
* If you wish to only use this hack for the 4.7 search module, remove lines: 88-117, 123 inclusive
*/
function db_query_temporary( $query )
{
 
//uncomment this line the first time the script is run
//  create_temporary_tables_index();

  // this first bit comes from the drupal db_ stuff, to get the full proper query
 

$args = func_get_args();
 
$tablename = array_pop( $args );
 
array_shift( $args );

  if ( isset(

$args[0] ) and is_array( $args[0] ) ) {
   
// 'All arguments in one array' syntax
   
$args = $args[0];
  }
 
 
_db_query_callback( $args, true );
 
$query = preg_replace_callback( DB_QUERY_REGEXP, '_db_query_callback', $query );

 

//perform the requested query
 
$original_query = db_query( $query );

 

// if a result was returned then use it to guess the column types, otherwise
  // return something predefined. 
 
if ( ( db_affected_rows( $original_query ) > 0 ) ) {
 
   
$first = db_fetch_array( $original_query );
   
   
// initalise stuff to be on the safe side
   
$rows = array();
   
$cols = "";
   
   
// try to guess at the column types
    // todo: perhaps take a sample, or find some tricky sql to do it for us
    // this doesnt take into account NULLs or other stuff like a perfect integer in an otherwise
    // FLOAT/DOUBLE column
    // TODO: write more datatype conditionals as required
   
foreach( $first as $key => $value ) {
      if ( (
is_numeric( $value ) == 1 ) && ( is_double( $value + 0 ) == 1 ) )
       
$rows[] = "`$key` DOUBLE";
      else if (
is_numeric( $value ) )
       
$rows[] = "`$key` INT";
      else if (
strlen( $value < 255 ) )
       
$rows[] = "`$key` VARCHAR(255)";
      else
       
$rows[] = "`$key` TEXT";
       
     
// construct a column list
     
$cols .= $key . ", ";
    }
  } else {
   
// no results, so just assume we are searching and return the default search columns
   
$cols = "type, sid, relevance, matches, "; //notice the extra ', '
   
$rows[] = '`type` varchar(255) default NULL';
   
$rows[] = '`sid` int(11) default NULL';
   
$rows[] = '`relevance` double default NULL';
   
$rows[] = '`matches` int(11) default NULL';
  }
 
 
// trim the column listing to remove the final ', '
 
$cols = substr( $cols, 0, strlen( $cols )-2 );
 
 
// conatenate the column types to form the bulk of the sql CREATE statment
 
$column_types = "";
  for(
$i = 0; $i < sizeof( $rows )-1; $i++ ) {
   
$column_types .= $rows[$i] . ", ";
  }
 
 
// we will always have one ', ' too many, so get rid of it here
 
$column_types .= $rows[sizeof( $rows )-1];
 
 
// find the new 'temporary table'
 
$table_name = create_temporary_table_record();
 
 
// perform the creation
 
$create_query = "CREATE TABLE `$table_name` (" . $column_types . " )";
 
db_query( $create_query );
 
db_query( "INSERT INTO `$table_name` ($cols) " . $query );
 
 
// return both the query and the new table name, because we can't guess the table name later
 
return array( "query" => $result_query, "table" => $table_name );
}

/**
 * create a table record in the lookup table.
 *
 * If you have the LOCK_TABLES sql permission, uncomment the appropriate lines below
 */
function create_temporary_table_record()
{
 
clean_temporary_table_indexes();

 

// db_lock_table( TEMPORARY_TABLE_INDEX );
 
$index_query = db_query( "SELECT max(ttable_id) as ID from " . TEMPORARY_TABLE_INDEX );

 

$index_query_table = db_fetch_object( $index_query );

 

$table_id = ( $index_query_table['ID'] + 0 );

 

//If we couldnt select the max, generate one
 
if ( empty( $index_query_table['ID'] ) ) {
   
//watchdog( 'search', t( 'Unable to SELECT MAX() from temporary tables index' ), WATCHDOG_NOTICE );
   
while(1)
    {
    
$table_id = rand( 1, 1048576 );
     if(
db_affected_rows(db_query('SELECT 1 FROM %s LIMIT 1', $table_id)) != 0) //try again if the table already exists
     
break;
     
    
clean_temporary_table_indexes(); //no deadlocks here
   
}
  }

 

//make a table name, and then perform the table creation
 
$table_name = TEMPORARY_TABLE_PREFIX . $table_id;
 
$sql = "INSERT INTO " . TEMPORARY_TABLE_INDEX . " (ttable_id,created,tablename) VALUES($table_id, NOW(), '$table_name')";
 
db_query( $sql );
 
 
// db_unlock_table( TEMPORARY_TABLE_INDEX );

  //and return the table name that we generated. its a bit cryptic i'll admit
 

return $table_name;
}

/**
* Clean out the temporary table indexes that we arent using anymore
*/
function clean_temporary_table_indexes()
{
 
$sql = 'SELECT tablename FROM ' . TEMPORARY_TABLE_INDEX . ' WHERE created<(NOW() - INTERVAL '. TEMPORARY_TABLE_DURATION .' MINUTE)';
 
$r = db_query($sql);

 

//remove them all from the index
 
db_query('DELETE FROM ' . TEMPORARY_TABLE_INDEX . ' WHERE created<(NOW() - INTERVAL ' . TEMPORARY_TABLE_DURATION . ' MINUTE)');
 
 
//drop the tables
 
while($record = db_fetch_array($r))
   
db_query('DROP TABLE IF EXISTS `' . $record['tablename'] . '`');
}

?>

By all means have a discussion about it here if you'd wish. Im open to suggestions. I'll try to put up a link to full versions of the files for the less technically inclined. Contact me if I dont and you'd like a copy it

--manny
taslacrosse.org

Comments

manny’s picture

I should know better than to gold plate code. I tested the code; just didnt review the logs afterwards. (They get a bit filthied up)

So the create_temporary_tables_record function should read:

<?php
function create_temporary_table_record()
{
 
clean_temporary_table_indexes();

 

// db_lock_table( TEMPORARY_TABLE_INDEX );
 
$index_query = db_query( "SELECT max(ttable_id) as ID from " . TEMPORARY_TABLE_INDEX );

 

$index_query_table = db_fetch_object( $index_query );

 

$table_id = ( $index_query_table['ID'] + 0 );

 

//If we couldnt select the max, generate one
 
if ( empty( $index_query_table['ID'] ) ) {
   
//watchdog( 'search', t( 'Unable to SELECT MAX() from temporary tables index' ), WATCHDOG_NOTICE );
   
$table_name = TEMPORARY_TABLE_PREFIX . rand( 1, 1048576 );
  }

 

//make a table name, and then perform the table creation
 
$sql = "INSERT INTO " . TEMPORARY_TABLE_INDEX . " (ttable_id,created,tablename) VALUES($table_id, NOW(), '$table_name')";
 
db_query( $sql );
 
 
// db_unlock_table( TEMPORARY_TABLE_INDEX );

  //and return the table name that we generated. its a bit cryptic i'll admit
 

return $table_name;
}
?>

-manny