Checking for previous quote entries?

Thanks ctmattice1 for your response. I am a bit confused about why checking for an exact match with an existing quote would be resource intensive. Could you elaborate a bit so that I better understand?

I agree that checking all of the existing quotes for the "close, but not exact" case (when no quote author has been specified) could be resource intensive. However, I do not imagine many users of this module have more than a hundred quotes from the same author, let alone thousands. What I am imagining the new code to do is something akin to the following (in the entry form validation section):

[A] First check - Query the data base to see if there is an exact match with the body of an existing quote.

[B] Second check - Query data base to see if new quote is sub-text from existing quote. Warn user if so.

[C] Third check - Naive attempt at closeness
1) Explode the new quote into an array of its component words. (May want to eliminate common words, but that brings in language complexities.)
2) If the quote author has been entered, select all quotes from that author; otherwise select all quotes.
3) For each quote in the 2) selection group, explode that quote into an array of component words.
4) Compute the percentage of words new quote and existing quote share in common.
5) If above a threshold percentage (say 80%), warn the user that quotes are very similar. (How should form processing then proceed?)

Does this make any sense? Your thoughts are welcome.

Cheers

Thanks Nancy for your comments. I very much appreciate your thoughts and they have caused me to pause, think and investigate further. I had not considered the use case of entering multiple quotes at the same time nor did I understand when the author field gets presented to the application.

I probably will be quiet for a bit as I work on my own experimenting and trying to get some alpha code working in a fast and efficient manner. Any further comments or thoughts are welcome in the interim.

Cheers

During the past week, I have spent some time investigating and working on this issue, paying particular attention to Nancy's thoughts in #4.

A close review of the existing code found that there is no validation being performed at all on the individual quote import records. I would suggest then that the existing import code be modified so that some minimal level of checking is performed in a private quotes function and then a module_invoke_all() call be made to incorporate any further validation in custom user modules via a custom quotes hook definition.

Before working on a patch to the quotes.module file, I would like to get some thoughts on this approach. Thanks.

The checking of a single quote entry for an exact match with those already in the data base was fairly easy. Given that I am reluctant to modify any third-party Drupal code like the quotes.module file itself, I implemented this functionality via a helper module. The key was to add an #element_validate to the quote text field and then call a helper validation function.

My assumption is that a particular user will not want to enter an exact duplicate quote and this condition should be treated as an error. At present, the extraction of data from the form and the actual checking for a match are in the same function. If we want to add duplicate validation to the bulk import side, this function will need to be broken into two parts: 1) deal with $form_state I/O and 2) perform the actual match check and set error flag.

This second function then also could be called out of the private validation function mentioned above. The question there will be what do we want to do if a duplicate is found (Skip?, How to highlight?,etc.) The resulting code can be tweaked so that it can be added to quotes.module file as a patch if there is sufficient interest.

The relevant code from my helper module is:

/**
 * Implements hook_form_FORM_ID_alter() for quotes_node_form.
 */
function (helper_module)_form_quotes_node_form_alter(&$form, &$form_state) {
  // Add custom validation checks for quotes_node_form
  // $form['#validate'][] = '_quotes_validate' ;

  // Add exact match check to quotes body field
  $form['body_field']['body']['#element_validate'][] = '_quotes_body_validate' ;
}

/**
 * Custom quote validation to check for exact match with previously entered quotes.
 */
function _quotes_body_validate($element, &$form_state) {
  $quote = '' ;  $user = '' ;
  if (isset($form_state['values']['body'])) {
    $quote = $form_state['values']['body'] ;
  }
  if (isset($form_state['values']['uid'])) {
    $user = $form_state['values']['uid'] ;
  }

  $sql = "SELECT n.nid,n.title, MD5(LOWER(nr.body)) AS checksum " .
    "FROM {node} n INNER JOIN {node_revisions} nr ON n.vid=nr.vid " .
    "WHERE type='quotes' AND n.uid=" . $user .
      " AND MD5(LOWER(nr.body))='" . md5(strtolower($quote)) . "' " .
    "ORDER BY created DESC" ;
  $records = db_query($sql) ;
  while ($record = db_fetch_object($records)) {
    // Skip this quote if already has been saved once (ie in update mode)
    if (isset($form_state['values']['nid']) &&
        $form_state['values']['nid'] == $record->nid) continue ;
    $link_start = '<a href="/node/' . $record->nid . '" onclick="' . "target='_blank';" . '">' ;
    $text = 'ERROR: This quote is a duplicate of another ' . $link_start .
      'quote</a> entitled ' . $link_start . $record->title . '</a>.' ;
    form_error($element, $text) ;
  }
}

As always, thoughts and comments are welcome. Cheers!

A possible failing with the code offered in comment #6 deals with filtering. Drupal has a great policy of never changing user entered content. Hence, we probably need to deal with filtering here as well. For instance, sometimes I will embed URL links within a posting (although generally of the 'blog' type).

Suppose that one is dealing with the quote "Wise men put their trust in ideas and not in circumstances." Occasionally, I will embed a link around the term like 'trust' that connects with some specific external page about this quote/term. On other quote pages, I might use the characters '--' or '—' to represent a long dash in the middle of a quote. The current code assumes that one is dealing with "pure" text both on what has been entered for the current quote and what is stored in the data base. Hence, the same quote with a link and one without would be considered to be different. That obviously is wrong.

A probable improvement to the validation routine involves "cleaning up" both the stored values and the new quote text string before the md5 comparison is conducted. The "clean up" function would filter out all HTML links as well as "rationalizing" dashes and such. Obviously, this type of function will require more overhead per comparison.

My inclination is to implement this more compute-intensive preprocessing method before the comparison is actually performed. Any thoughts before I proceed?

Cheers!

Nota Bene - This issue came up in pre-processing the quote to gain an accurate word count. Obviously, there 'mdash' should not be considered a word!!

This morning I started by cleaning up some open @todo items. One of those concerned breaking out the node comparison functionality so it also could be used in the batch import portion of this module (from Item #6 above). I am posting the resulting code so that others might use it as well.

/**
 * Custom quote validation to check for exact match with previously entered quotes.
 */
function _quotes_body_validate($element, &$form_state) {

  $nid = 0 ; $quote = '' ;  $user = '' ;
  if (isset($form_state['values']['nid'])) {
    $nid = $form_state['values']['nid'] ;
  }
  if (isset($form_state['values']['body'])) {
    $quote = $form_state['values']['body'] ;
  }
  if (isset($form_state['values']['uid'])) {
    $user = $form_state['values']['uid'] ;
  }

  $duplicates = check_for_node_duplicates($nid, $quote, $user, 'quotes' ) ;
  if ($duplicates['number'] > 0) {
    foreach ($duplicates['nodes'] as $nid => $error_message) {
      form_error($element, $error_message) ;
    }
  }
}


/**
 * Checks for duplicate nodes.
 */
function check_for_node_duplicates($nid, $body, $user = NULL, $type = NULL) {
  // Declare return array and structure elements
  $duplicates = array() ;
  $duplicates['number'] = 0 ;
  $duplicates['nodes'] = array() ;

  $sql = "SELECT n.nid,n.title, MD5(LOWER(nr.body)) AS checksum " .
    "FROM {node} n INNER JOIN {node_revisions} nr ON n.vid=nr.vid " .
    "WHERE MD5(LOWER(nr.body))='" . md5(strtolower($body)) . "' " ;
  if (isset($user) && $user >= 0) {
    $sql .= "AND n.uid=" . $user . " " ;
  }
  if (isset($type) && !empty($type)) {
    $sql .= "AND n.type='" . $type  . "' " ;
  }
  $sql .= "ORDER BY created DESC" ;
  $records = db_query($sql) ;
  while ($record = db_fetch_object($records)) {
    // Skip this quote if already has been saved once (ie in update mode)
    if (isset($nid) && $nid == $record->nid) continue ;
    $link_start = '<a href="/node/' . $record->nid . '" onclick="' . "target='_blank';" . '">' ;
    $text = 'ERROR: This text is a duplicate of another ' . $link_start .
      'node</a> entitled ' . $link_start . $record->title . '</a>.' ;
    $duplicates['nodes'][$record->nid] = $text ;
    $duplicates['number'] += 1 ;
  }
  return $duplicates ;
}

Nancy,

Thanks for your comments. For me at least, there appears to be no performance hit doing a simple text comparison (running against about 1200 existing quotes). There surely will be more of performance hit when I get the "filtered" text comparison code implemented. I have no sense of how much that performance hit might be though.

The way my code works now a form_error flag is raised for a single quote entry that is a duplicate. I am not presently doing any validation against bulk imports because I am uncertain about how to handle error handling. Let's say a duplicate is determined on the xth of y entries. Should the import process stop with that quote? Back out quotes successfully processed? Continue to end of file without storing data on the error quote?

Thoughts are welcome.

Cheers!