[V4MD] Translate search string to SQL

[Scott Runkel]

> Sure, I'm interested :-)

OK...by "popular" demand is my down-and-dirty script for converting a user-typed
search string into basic SQL. I threw in some very basic comments and modified
the handler so it is more general; give it your own table and column names as
parameters.

I'm sure it would be easy to screw up this handler by tossing it garbage, but it
seems to work OK for basic stuff like:

human and "bone loss"
john or cindy
etc.

-- Parameters:
--     searchString : what the user typed
--     table :        the name of the table to search
--     column :       the name of the column to search
-- Returns: a list whose first element is the SQL and whose second element is
the first "component" of the
-- search, such as the first word or the first quoted phrase (I use this later
for frequency assessment)
on convertSearchTextToSQL searchString, table, column
  -- Remove "or" or "and" if either first or last word
  if searchString.word[1] = "or" or searchString.word[1] = "and" then
    delete word 1 of searchString
  end if
  if searchString.word[searchString.words.count] = "or" or
searchString.word[searchString.words.count] = "and" then
    delete the last word of searchString
  end if

  wordCount = searchString.words.count

  -- "components" are the items that make up the search, such as words or
phrases
  components = []

  -- lookForQuote is set to 1 when we encounter a quote and start looking for
its match
  lookForQuote = 0
  componentBuild = ""
  repeat with x = 1 to wordCount
    currWord = searchString.word[x]
    if lookForQuote = 1 then
      -- looking for closing quote
      put " " & currWord after componentBuild
      if currWord.char[currWord.chars.count] = quote then
        -- Found a closing quote
        delete the last char of componentBuild
        append components, componentBuild
        lookForQuote = 0
        componentBuild = ""
        next repeat
      else
        -- no closing quote on this one
        -- are we at the end?
        if x = wordCount then
          -- last word
          append components, componentBuild
        end if
        next repeat
      end if

    else
      -- Not looking for closing quote
      -- is it a special word?
      if currWord = "or" OR currWord = "and" then
        append components, currWord
        next repeat
      end if

      if currWord.char[1] = quote then
        -- start looking for the next quote, to close the component
        -- maybe it's on the same word?
        if currWord.char[currWord.chars.count] = quote then
          -- just one word was quoted
          append components, currWord
          next repeat
        else
          -- the current word doesn't have an end quote
          componentBuild = currWord
          delete char 1 of componentBuild
          lookForQuote = 1
          next repeat
        end if
      end if

      -- if here, then just a regular word
      append components, currWord
    end if
  end repeat

  compCount = components.count
  SQL = ""

  -- Go through the components and build the SQL string
  repeat with currComp = 1 to compCount
    currCompText = components[currComp]
    -- the following line requires textCruncher Xtra; could be modified to do
without
    currCompText = replaceAll(currCompText, "\'", "'")
    case currCompText of
      "or":
        nextPiece = " OR "
      "and":
        nextPiece = " AND "
      otherwise:
        nextPiece = "(" & column & " like '" & currCompText & "')"
    end case

    -- Add "and" by default between any two pieces of SQL
    -- if there is nothing else connecting them
    if SQL.char[SQL.chars.count] = ")" AND not(nextPiece = " AND ") and
not(nextPiece = " OR ") then
      SQL = SQL & " AND " & nextpiece
    else
      SQL = SQL & " " & nextPiece
    end if
  end repeat

  if SQL = "" then
    SQL = "SELECT * FROM " & table
  else
    put "SELECT * FROM " & table & " WHERE" before SQL
  end if

  return list(SQL, components[1])
end


-SR

Scott Runkel
Director of Technology
503-722-2123