Installing DBSlayer on Mac OS X Snow Leopard

February 2, 2010

DBSlayer is a tool that will wrap your MySQL database with an HTTP REST/JSON API. Here's how to get it installed on Snow Leopard using Macports. First, make sure you have all the dependencies install via Macports:

$ sudo port install db46
$ sudo port install apr
$ sudo port install apr-util
$ sudo port install mysql5-server

Then, if you try to download the source and install it:

$ cd ~/src
$ wget http://code.nytimes.com/downloads/dbslayer-beta-12.tgz
$ tar xzf
$ cd dbslayer
$ ./configure
$ make
$ make install

You'll run into this error:

ld: library not found for -ldb-4.6
collect2: ld returned 1 exit status
make[1]: *** [dbslayer] Error 1
make: *** [all-recursive] Error 1

Instead, pass these options to configure:

$ ./configure CFLAGS="-L/opt/local/lib/db46" \
--with-apr-1-config=/opt/local/bin/apr-1-config \
--with-apu-1-config=/opt/local/bin/apu-1-config \
--with-mysql-config=/opt/local/lib/mysql5/bin/mysql_config

Now try to install again:

$ make clean
$ make
$ sudo make install

Next, create the config file, which at it's most basic, should look something like this:

[my_db]
database=my_db
host=localhost
user=root

Now when you start dbslayer, make sure to give it the full path to the config file:

$ dbslayer -c ~/stuff/my_db.cnf -s my_db

dbslayer starts in the background, so to restart it, you have to find the process and kill it manually. It also doesn't say whether it worked or not, it just backgrounds with no output. Here's a little Ruby script to try it out:

require 'open-uri'
require 'rubygems'
require 'json'
require 'pp'

def query_url(sql)
  query_hash = { "SQL" => sql }
  url_args = URI.encode(query_hash.to_json)
  "http://localhost:9090/db?#{url_args}"
end

def exec_query(sql)
  url = query_url(sql)
  open(url) do |f|
    yield JSON.parse(f.read)
  end
end

exec_query "select * from stuff limit 1" do |res|
  pp res
end

Posted in Technology | Tags MacOSX, MySQL, REST, SnowLeopard, DBSlayer | 6 Comments

Correlated Subqueries

January 6, 2008

The Rails' ORM ActiveRecord has support for counter caches. Say you have a blog, like this one, which has articles, and each article has many comments. If you are going to show a list of articles with the number of comments each article has, without a counter cache, you will end up with N+1 SQL queries. One query to get the articles, and one select count(*) from comments where article_id = ? query for each article.

To avoid the N+1 select, you can add a column in the articles table that contains the number of comments the article has. With some ORM frameworks, this becomes somewhat of a pain, because you have to keep that count column updated, but ActiveRecord makes it easy. If you just create a table called <table_name>_count on a parent table, it will keep the total of the records that belong to that record in that column. So then when you call article.comments_count, there is no SQL query executed.

This is nice, but what happens if you have a site in production and you want to add a counter cache column? You've got to create the column and then get the count updated. You can do this in a migration, but you can also do it in SQL using a correlated subquery. Using MySQL, you can do it like this:

alter table articles add column comments_count int(11) default 0;
update articles set articles.comments_count = 
  (select count(comments.id) from comments where articles.id = comments.article_id);

Posted in Technology | Tags MySQL, ActiveRecord | 0 Comments

PaulBarry.com 3.0

January 28, 2007

So I've finally finished the latest version of my site. The first version of my site went online on March 3rd of 2006 using WordPress. Then on my last birthday, I switched to Typo. Now, I've got up the first version of my site that I have built myself, using Java, Stripes, Spring and Hibernate. I'm using Resin as the Web/Application server and MySQL for the database. The OS is Ubuntu and it is hosted with VPSLand.

I hoping that this version will be more stable than the previous version. If you are having any problems using the site, let me know.

Posted in Technology | Tags Resin, Spring, Hibernate, Stripes, MySQL, Java, Ubuntu | 6 Comments