Programming languages

Tuesday was not the greatest day, but at least each setback resulted in a triumph...

First, the periodical proposal for schema.org--that I have poured a good couple of months of effort into--took a step closer to reality when Dan Brickley announced on the public-vocabs list that he had created a test build that incorporated the RDFS that I had written up. Excitement rapidly turned to horror, though, as I realized that I had made a classic copy/paste error, in which I had changed the displayed name of the domainIncludes value but had not changed the actual URI... Long story short, the test build looked nothing like what the schemabibex group had agreed on, and I was terribly embarrassed.

Luckily, after I fixed the RDFS, Dan was able to put together a revised test build later that day that actually reflected our intentions. So that can continue moving forward...

Second, our Evergreen instance started acting up rather badly. All of the connections to the database server were being gobbled up, and we were scrambling to figure out why. While I'm on sabbatical I'm not really supposed to be involved in the day-to-day operations, but when a core service stops running it's okay for research to wait for a little bit... Eventually I tracked down a fix for a potential denial of service problem (Search result rendering can crush the system) that hadn't been merged into our production system (the fix came out after the start of my sabbatical), and shortly after I put that into production we were back up and running.

Third, after the Evergreen problem was resolved, Bill Dueber pinged me innocently on IRC. He had run into a problem with File_MARC; when serializing MARC as MARC-in-JSON format, fields with a subfield $0 were getting trashed. Data corrupting bugs are one of the most serious classes of bugs for any package maintainer, so I jumped on this problem too... After a little bit of analysis, I figured out that PHP's type coercion for integer-like keys when creating arrays and its json_encode() implementation were combining to ruin the MARC-in-JSON serialization in this one particular case. Faced with rewriting the entire serialization logic, I did what any (in)sane programmer would and ended up running a regex against the result of json_encode() to turn the array-ified subfield $0 back into a key/value pair. File_MARC 1.1.1 is now available at your nearest PEAR mirror...

Way back in 2006, I thought "It's a shame there is no PHP library for parsing MARC records!", and given that much of my most recent coding experience was in the PHP realm, I thought it would be a good way of contributing to the world of code4lib. Thus File_MARC was born in October 2006. At the time, I had aspirations of quickly iterating to a 1.0.0 stable release. Hah!

Seven years later, I have finally cut a 1.0.0 stable release of the File_MARC library for PHP. The major reason I had been hesitating was that I really wanted to cut over to the native SplDoublyLinkedList class instead of my own user-space linked list. In the end, I still had to add a small shim to enable nodes to be inserted anywhere in the linked list (SplDoublyLinkedList::add() was not added to PHP until 5.5.0, to which few users will have easy access for a while), but I'm much happier about the result.

The best thing about this release is that it means you no longer have to add -beta to your install command, which tripped many people up, I'm sure. That is, you can simply install it with:

pear install File_MARC

Thanks to Bill Dueber and Ross Singer for pestering me to go ahead and get this thing stable, and to Demian Katz and Mark Jordan for testing out the final code!

Update: More conventional versions of the slides are available from Google Docs or in on Speakerdeck (PDF)

On August 10, 2013, I gave the following talk at the PyCon Canada 2013 conference:

I’m a systems librarian at Laurentian University.

For the past six years, my day job and research have enabled me to contribute pretty heavily to Evergreen, an open source library system written largely in Perl and built on PostgreSQL.

But when I have the opportunity to create a project from scratch, for work or play, Python is my go-to language.

I promised to provide an example of a full-text search engine built with Flask and PostgreSQL written in under 200 lines of code; you can find that at either Gitorious or Github.

Last summer, the Laurentian University Library digitized 50 years of student newspapers - over 1,000 issues.

We posted them all to the Internet Archive and got OCR’d text as a result.

But finding things within a particular collection on the Internet Archive can be difficult for most people, so we felt the need to create a local search solution.

We were already using PostgreSQL to track all of the individual issues, with attributes like newspaper name, volume, edition, publication date.

This gave us the ability to filter through the issues by year and issue number, which was a good start. But we also wanted to be able to search the full text for strings like “Pierre Trudeau” or “Mike Harris”.

A common approach is to feed the data into a dedicated search engine like Solr or Sphinx, and build a search interface on top of that.

But PostgreSQL has featured full-text search support since the 8.3 release in 2008. So I opted to keep the moving parts to a minimum and reuse my existing database as my search platform.

Our example table contains a “doc” column of type TEXT; that’s where we store the text that we want to search and display. We also have a “tsv” column of type TSVECTOR to store the normalized text.

The TSVECTOR column is typically maintained by a trigger that fires whenever the corresponding row is created or updated. So... you just insert TEXT into the doc column, and the trigger maintains the tsv column for you.

PostgreSQL includes the tsvector_update_trigger() for your convenience, as well as a trigger that uses different language-oriented normalizations based on the value of a specified column. Naturally, you can define your own trigger that invokes the to_tsvector() function.

To provide good performance, as with any relational table, you need to define appropriate indexes. In the case of full-text search, you want to define a GIN (or GiST) index on the TSVECTOR column.

Note: GIN indexes take longer to build, but provide faster lookup than GiST.

Finally, we INSERT the text documents into the database. The ID and TSVECTOR columns are automatically generated for us.

Each text document is:

1. Parsed into tokens (words, white space, URIs, file paths)
2. Each token is then normalized with one or more dictionaries (word tokens may get stemming and stop words; URIs might have “www.” stripped; everything gets folded to lower case)
3. Et voila: the text-search vector for our text document has been created!

PostgreSQL bundles a number of language-specific dictionaries to support different stemming algorithms and default sets of stopwords.

In this example, we can see that PostgreSQL has stemmed “sketching” to remove the verb suffix, removed “the” altogether as a stop word, and stemmed “trees” to its singular form.

You can also see that the TSVECTOR stores the position where each token occurs, to support relevancy ranking algorithms that boost the relevancy of terms that are located closely together.

But it's a doozy of a query!

Yes, there is a lot going on here.

First, this is just a regular SQL statement that happens to use the WITH clause to define named subqueries (“q” and “ranked”).

The to_tsquery() function takes an incoming full-text search query and converts that into a parsed, normalized query.

The ts_rank_cd() function compares the TS_VECTOR column against the query to determine its relevancy score.

We need to restrict it to rows that match our query, so we use the @@ operator (PostgreSQL allows data-type specific operators like this) and then take the top ten.

Note: the query, limit, and offset are hardcoded here for illustrative purposes, but in the actual application these are supplied as parameters to the prepared SQL statement.

Finally, we use the ts_headline() function to give us a highlighted snippet of the results.

The harvester is a Python 3 application that uses the ‘postgresql’ module to connect to the database.

The REST and Web applications in the IA Harvester application are Python 2, largely because they use Flask (which iswas Python 2 only). But in the demo application, I’ve converted them to Python 3.

While I could have simply written the Web and REST applications as a single Flask Web app that talks directly to the database, I opted to couple them via a JSON interface for a few reasons:

• Decoupling the web front end from the search back end means that I can augment or swap out either piece entirely without affecting the overall experience.
• If performance becomes an issue, I could add caching where profiling warrants it.
• If the service ends up being incredibly popular and PostgreSQL turns into a bottleneck, even with caching, I could set up a Solr or Sphinx instance instead.

I can change from Flask to another Web application framework on either piece.

I can separate the hosts if I need to throw more hardware at the service, and/or virtualize it on something like Google App Engine.

With so many Python web frameworks available, why Flask?

• Routes as decorators was admittedly the biggest draw, simplifying routing enormously!
• Solid Unicode support was a must as well, as our newspapers are bilingual (English / French).

At the time I opted to try out Flask, the project’s public stance towards Python 3 was not warm. However, with the 0.10 release in June 2013, all that has (thankfully!) changed; Python 3.3 is supported.

Flexible format:

• query: gives us the original query so we can echo that back to the user
• results: is a list of the highlighted snippets that we returned from our full-text search
• years: a facet that for the years in which hits were found, including the number of hits per year
• collections: a facet for the collections in which hits were found, including the number of hits per collection
• meta:
  • total: the total number of hits
  • page: the page we’re on
  • limit: the number of hits per page
  • results: the number of hits on this page of results

We’ve already seen the decorator-route pattern before, and of course we need to quote, encode, and decode our search URL and results.

The Flask-specific parts are helper methods for getting GET params from the query string, and rendering the template (in this case, via Jinja2), by passing in values for the template placeholders.

At this point, the UI is functional but spartan. I’m a database / code guy, not a designer. Luckily, I have a student working on improving the front end (hi Emily!)

Further information:

• Demonstration application:
  • Gitorious repository / Github
• Internet Archive harvester project: https://gitorious.org/ia-harvester
• PostgreSQL full-text search:
  • PostgreSQL official documentation
  • Seek and ye shall find: PostgreSQL full-text search (presentation at PostgresOpen 2012)
• Flask official docs
• Jinja2 official docs

On August 10, 2013, I'll be giving a twenty-minute talk at PyCon Canada on A Flask of full-text search with PostgreSQL. I'm very excited to be talking about Python, at a Python conference, and to be giving the Python audience a peek at PostgreSQL's full-text search capabilities. With a twenty minute slot, I'll be leaning on my code4lib experience to compress the right amount of technical information into an entertaining package.

Setting aside my talk, the line-up for PyCon Canada looks fantastic; the keynote speakers are Karen Brennan, Hilary Mason, and Jakob Kaplan-Moss, and there are a ton of great talks. Did I mention that I'm really looking forward to this conference?

Update 2013-07-11: Now that the schedule is official, the presentation URL needed to be updated. Also, the impetus for this proposal came straight from PGCon 2013, where the PostgreSQL community was urged to get the good word out about PostgreSQL to other communities. Et voila!