The other day, my incoming stream of email froze. After narrowing down the culprit, it turned out to be my spam filter choking on one particular message. My son John had sent me an 11MB mpg attachment (Queensryche’s “Art of Life”), and I hadn’t anticipated that breaking a message of that size into words and checking each combination of one, two, and three adjacent words would require such a long time that the POP connection getmail relied upon timed out.

So, I added a switch to isspam (-M, or –Maxsize) that allows you to exclude any message over a specified size. Links below.

See Chip sweat

Not knowing where to look for SQLite3 maximums at the time (here they are), I became a little nervous as my Bayesian spam filter database approached 2 gigabytes. When it passed that milestone and continued working, I breathed a sigh of relief. Then one day, I noticed that the spam totals were wrong — my total number of spam messages had been replaced with a nil!

Fortunately, I discovered the problem on the day it first occurred. I restored the database from the previous day’s backup — then started to try to figure out what was wrong. I was just about certain that 2GB was the culprit. Probably a pointer being cast to int somewhere in the implementation of SQLite3 or the gem. Using irb I verified that I could reproduce the problem with a simple insert. So I logged an issue on the sqlite3-ruby Google Group.

Luis Lavena responded almost immediately. In trying to help him reproduce the problem without uploading 2GB of data, I tried out each step myself as well. Suddenly I found that I couldn’t get an insert to work, even on an empty database! That let the 2GB boundary off the hook. What could I have possibly screwed up?

It turns out that all I had done was to upgrade the gem, which came through on a portupgrade. Even though I still can’t find it anywhere in the docs, Luis let me know that the parameters to a query need to be passed as an array now, instead of as individual arguments (UPDATE: it’s been added now). Be warned, and please upgrade your copy of isspam before you get version 1.3.0 or greater of the sqlite3 gem. As usual, the tarball link is below, or you can pull it from Bitbucket.

In case you’re curious, SQLite3 supports a maximum of 1073741823 pages out of the box, with a default page size of 1024. That means a maximum file size of about 1 terabyte. But if you tweak the page size, you can approach 32 terabytes.

See Chip smack his forehead

I had a V-8 moment when I discovered that the sqlite3 rubygem allows you to pass a code block to its database queries. That block gets called once per row as they are being extracted from the database. That made my paged query obsolete, so I ripped it out.

See Chip code another feature just for the fun of it

Ever wonder how an individual word affects spam score? Of course, you could always do echo word | isspam -p, but that only gives you that word’s spam score. It doesn’t tell you how many times that word has been seen in spam or non-spam. So I added a -w option, which takes all the phrases from the input and gives you the detailed statistics on those phrases. You can control the length of phrase to analyze with -m, so if you only want individual words, use -m 1.

As my isspam Bayesian spam filter learns about the spamminess of new words and phrases, its SQLite database grows correspondingly. It’s now up to 1.8 gigabytes, containing more than 6.5 million unique phrases. Because it’s an SQL database, the size doesn’t impact performance much, except when gathering database-wide statistics. The ‘isspam -d’ command, which dumps all of the data to stdout, would run for hours and exhaust available memory (in the previous version). That’s because SQLite/Ruby wants to return the result of a SELECT operation as an array of rows. 6.5 million rows.

That just won’t do, especially as the database continues to grow. So I reworked the ‘dump’ option to read the database in pages of 10,000 rows at a time on one thread, while the main thread formats the output to the screen. I generalized the concept slightly by monkeypatching SQLite3::Database to add a ‘paged_execute’ method to which you can pass the desired page size, and which returns a PagedRows object that has an ‘each’ method and a ‘count’ attribute. Thus, you can use it in place of a Database#execute for most operations that will potentially return an enormous number of rows.

The main thread self-balances its sleep time between checking for input from the database query thread, in order to minimize overall runtime.

I’ve included the code and docs in the tarball below, or you can grab them from the BitBucket repository. But in case you’re not interested in all that spam filter code, here’s the bit that adds paged queries to SQLite3::Database:

# class to create an array-like set of paged database query results
class PagedRows
  # count of items retrieved so far
  attr :count

  # Create a paged query
  #  db = SQLite3::Database
  #  pagesize = int, number of rows per query
  #  query = select statement without limit or offset
  #  param = parameter for query
  def initialize(db, pagesize, query, param)
    @db = db
    @query = query
    @param = param
    @pagesize = pagesize
    @count = 0
  end

  # Iterate over all rows from multiple queries
  def each(&block)
    queue = []			# pages yet to be processed
    done = false
    mutex = Mutex.new

    # worker thread for database queries
    t = Thread.new {
      offset = 0
      begin
	# grab a page
        enq = @db.execute(@query + " LIMIT #{@pagesize} OFFSET #{offset}", @param)
	mutex.synchronize do
	  queue << enq		# add to queue
	end
        offset += @pagesize
        @count += enq.size
      end until enq.size < @pagesize
      enq = nil			# so last page gets released after processing
      done = true
    }

    nsleep = 0.5		# how long to wait for worker thread
    deq = nil			# establish scope of deq
    while (!done)
      sleep nsleep
      while queue.size > 0	# any rows already fetched?
	mutex.synchronize do
       	  deq = queue.shift
        end
	deq.each &block		# process the page of rows
	deq = nil		# release for GC
	Thread.pass		# give the worker thread a chance
	nsleep = [0.001, (nsleep*0.6666666667)].max	# Ok, we waited long enough (will be bumped below)
      end
      nsleep *= 1.5		# wait a little longer next time
    end

    t.join			# this should be a formality
  end
end

class SQLite3::Database
  # Create a paged query
  #  pagesize = number of rows per query
  #  query = database select statement
  #  param = parameter for query
  def paged_execute(pagesize, query, param)
    PagedRows.new self, pagesize, query, param
  end
end

Jerome has released a 2.0 beta version of his famous Jerome’s Keywords plugin, on which my tag cloud widget is based. Some of the functions I used in my widget are no longer available, so the previous version of my widget will not work with it. Thus, I have updated the widget, which you can download below.

Jerome seems to have fixed the problems in the plugin itself that I mentioned here and here. When he releases this version from beta, I’ll remove my corrected versions. I’ll go ahead now and add updates to let people know about the beta.

Jerome also included all of my widget’s options (other than Title) in a new “Tag Cloud Display” section of the plugin’s options. So, I have removed those options from the widget option panel. Please go into “Options/Jerome’s Keywords” and update your tag cloud options there.

Note also that this version of the widget is not compatible with earlier versions of the plugin. If you need a widget for earlier versions of Jerome’s Keywords, get version 1.1 of the widget here.

UPDATE: Get the latest version with improved cache control here.

Mike Koepke reported a problem he was having with the Jerome’s Keywords plugin when he searched on a tag that contained a single quote:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Creek' OR jkeywords_meta.meta_value LIKE 'Wilson's Creek,%' OR jkeywords_meta.' at line 1] SELECT DISTINCT * FROM wp_posts LEFT JOIN wp_postmeta AS jkeywords_meta ON (wp_posts.ID = jkeywords_meta.post_id) WHERE 1=1 AND post_date_gmt


I was unable to reproduce the behavior on my version, but it appears from the error message that the quote is not getting “quoted“. So, I added the following line to the “keywords_postsWhere” function in jeromes-keywords.php:

$keyword = str_replace("'", "''", $keyword);

Using the simple quote-doubling escape route. This solved the problem for Mike, but it didn’t work with my version, and from the resulting error message I could see that in my version the quote was already being escaped via a backslash (\). Modifying my fix to use backslash quoting didn’t work for me, either, because then the backslash gets backslashed.

Comparing versions, I was using MySQL 3.23.54 with PHP 4.4.1. Mike is on MySQL 4.1.14 and PHP 5.0.4. Then Mike tried this out on a local server with MySQL 5.0.22 and PHP 5.14, and got the same results as I did. So we both concluded that something got broken in MySQL or PHP between my early version and Mike’s live version, but has since been fixed again. Or was there some option that needed enabling on Mike’s live server? My Google searches for an explanation of this phenomenon were fruitless.

Clusty to the rescue. Clusty lead me to this post. It turns out that PHP has a runtime option, magic_quotes_gpc, that is enabled by default. This option automagically escapes quotes, backslashes, and nulls in a GET, POST, or cookie with a backslash. Bingo.

As with most (all?) PHP options, you can query the setting of magic_quotes_gpc at runtime via (20 guesses?) get_magic_quotes_gpc(), natch. So I changed my fix as follows:

if (!get_magic_quotes_gpc()) {
$keyword = addslashes($keyword);
}

You can download the corrected version below. If Jerome would like to adopt this fix in his master version, I’ll take down my copy and point to him instead. Johannes, you might want to implement a similar test in your Jerome’s Keywords Manager plugin. Sorry to give you one more place to go after your “final destination”.

UPDATE: Jerome has released a 2.0 beta version of the plugin that appears to address this issue. Mike Koepke, can you verify? If you’re using my tag cloud widget, you’ll need version 2.0 of that as well.

I use the Jerome’s Keywords plugin for WordPress to do tagging on my blogs. I love it, but it has one problem: if you search on a tag that is contained within another tag, you get the aggregate results of both tags. That’s because Jerome uses a SQL WHERE clause containing LIKE ‘%keyword%’ to match tags that are in a comma-delimited list in the metadata value field.

I left a comment on Jerome’s blog about this issue, but it looks like it never even made it past moderation. In fact, no new comments on this post since May. Where’s Jerome?

I mentioned this problem to Johannes Jarolim, author of the Jerome’s Keywords Manager plugin. Johannes came up with the correct WHERE sub-clause (value LIKE ‘keyword,%’ OR value LIKE ‘%,keyword’ OR value LIKE ‘%,keyword,%’ OR value = ‘keyword’) and implemented that in his plugin. But alas, that did not help my widget, which relies on the ?tag=keyword link operation provided by Jerome.

So, I felt like my best alternative would be to correct Jerome’s plugin and post it here. If Jerome adopts this correction (or something equivalent) and lets me know about it, I’ll take down this download and point over to his site instead.

UPDATE: I made yet another correction to this plugin, so head on over here for the latest and greatest.

UPDATE: Jerome released a 2.0 beta of the plugin that corrects this problem. Get the whole scoop.