SQLite 3 and Ruby

As per my wife's request, I built a financial management spreadsheet application, because "we" needed to keep better track of our finances.. I'll probably go into that program some other day as it's actually proven useful. But enough about that, today I want to talk about the database I used for it. This database was SQLite 3. If you haven't heard of it, SQLite is an ACID-compliant relational database management system contained in a relatively small C library where the SQLite library is linked in and thus becomes an integral part of the program with the database accessed through simple function calls. Yes, that long run-on sentence was courtesy of wikipedia, copy, and paste. In my words: SQLite is a database that exists as an easy to access text file (via c function calls) instead of requiring an external database server to be running somewhere waiting for requests. A free and tiny embedded database with great SQL support.

My program was written in ruby. For database access, I used the sqlite3-ruby gem, which wraps up those c function calls with ruby. If you're unfamiliar with gems, they're simply packaged ruby applications that allow easy installs. Simply go to your command prompt and type in "gem install sqlite3." This will work as long as you've installed ruby with gems and have an internet connection.

Once you've installed the gem, you're all set. To show how easy it is to use, I'll go through a short example. This example creates a database with a table describing teams and a table describing how a certain team scored on a certain game. It then queries the database in order to print out team rankings. Yeah, it's an odd way to get that information, but it was part of a homework assignment I had. I'm unsure whether the query is very good, but it's going to serve as my example.

First, create your connection and (possibly) insert information thusly.

require 'sqlite3'

if( !File.exists?( 'test.db' ) )
  db = SQLite3::Database.new( 'test.db' ) #The database is automatically created
  db.execute( "create table team ( id INTEGER PRIMARY KEY, name VARCHAR(255) )" )
  db.execute( "create table team_gm ( id INTEGER PRIMARY KEY, game_id INTEGER, team_id INTEGER, score INTEGER )" )
  db.execute( "insert into team_gm values( 1, 1, 1, 5 )" )
  db.execute( "insert into team_gm values( 2, 1, 2, 5 )" )
  db.execute( "insert into team_gm values( 3, 2, 1, 3 )" )
  db.execute( "insert into team_gm values( 4, 2, 2, 6 )" )
else
  db = SQLite3::Database.new( 'test.db' )
end

Then query and manipulate data thusly. You've got lots of freedom here. I suggest to at least read the FAQ if you don't want to read all the documentation.

query = "select z.name, sum(case when x.score > y.score then 1 else 0 end) as wins, " +
"sum(case when x.score < y.score then 1 else 0 end) as losses, sum(case when x.score = y.score then 1 else 0 end) as ties from team_gm x " +
"join team z on x.team_id = z.id " +
"join team_gm y on x.game_id = y.game_id AND x.team_id <> y.team_id group by x.team_id " +
"order by wins-losses-ties desc"

db.execute( query ) do |row|
  print row[0],": ",row[2]," Wins, ",row[3]," Losses, ",row[4]," Ties\n"
end

And finally close your connection thusly.

db.close

This database was exactly what I wanted and the sqlite ruby access is very simple. Granted, I'm probably way behind for not having used this before (I've been using HSQLDB for my "easy" databases), but at least I know now. I bet I'll be using it a lot now.