Wednesday, May 04, 2011

Polyglot programmers: setting up multi-language access to data

For those of us who tend to use several programming languages doing some up front work to make sure that we have access to all required data stores in the languages we use then makes it possible to really pick the best language for each task. I have a side project that I have been coding on for over ten years, with lots of code in Common Lisp, Scheme, Java and Ruby. If I can ever get a long enough break from my consulting business I have a few good ideas how to monetize some of this work. I was working on this project during a recent cross-country flight and I started adding a bit of new functionality in Ruby, switched to Common Lisp, and the implementation was a bit easier. I needed access to a PostgreSQL database (using PostgreSQL's built in text indexing and search) I use and once I had Internet access after the flight (for some quick reference), I worked out the few lines of code to interface with my annotated news data collection. Here is a small code snippet in case you need to do the same thing (using the lower level pg library instead of clsql because I only need to hit PostgreSQL):
(ql:quickload "pg")

(pg:with-pg-connection
  (conn "kbsportal_development" "postgres"
         :host "127.0.0.1" :password "password")
  (postgresql::pgresult-tuples
    (pg:pg-exec conn "select * from news")))

;; search support:
(pg:with-pg-connection
  (conn "kbsportal_development" "postgres"
        :host "127.0.0.1" :password "password")
  (postgresql::pgresult-tuples
    (pg:pg-exec conn "select * from news where to_tsvector(content) @@ to_tsquery('obama | congress')")))
I added this snippet to a directory full of snippets for common tasks for each language that I use.