Erik's blog

Code, notes, recipes, general musings

a simple SQLite key/val store in Ruby

with 5 comments

motivation:

  • I just like having a place to store blobs of data on my local machine

environment/requirements:

  • Mac os x 10.5.8
  • ruby 1.8.6 (2008-08-11 patchlevel 287) [universal-darwin9.0]
  • SQLite3
  • sqlite3-ruby (1.2.1) gem

code:

require 'rubygems'
require 'sqlite3'
class Storage
  def self.init
    db = SQLite3::Database.new( 'sqlite' )
    db.execute( %{
      CREATE TABLE foo 
      (key varchar(100) PRIMARY KEY, 
      value varchar(1000), 
      modified timestamp(20))
    } )
  end
  def self.get(key)
    db = SQLite3::Database.new( 'sqlite' )
    db.get_first_row( %{
      SELECT * FROM foo WHERE key='#{key}'
    } )
  end
  def self.set(key, val)
    db = SQLite3::Database.new( 'sqlite' )
    result = db.execute( %{
      REPLACE INTO foo
      (key, value, modified) 
      VALUES ('%s', '%s', %d)
    } % [key, val, Time.now.to_i] )
  end
end

usage:

require 'rubygems'
require 'sqlite3'
require 'json'
...
# init table once (anyone have a table detection query?)
Storage.init
...
# put data in
value = {:foo => 'bar'}
Storage.set( '123', value.to_json )
...
# get data out
row = Storage.get( '123' )
...
Advertisements

Written by Erik

February 20, 2010 at 2:33 am

Posted in code

Tagged with ,

5 Responses

Subscribe to comments with RSS.

  1. […] my previous sqlite utility to allow for table name definition & auto table […]

  2. This seems to be just what I need – but how do you recover the value after the row has been selected? If instead I use a “SELECT value” command and then try to JSON.parse it, I get a “can’t convert Array into String” error . .

    Philip Rhoades

    March 17, 2011 at 10:07 pm

    • Hi Philip.

      Please have a look at my follow-up post titled “updated Ruby utility for simple SQLite3 key/val storage”:
      https://erikeldridge.wordpress.com/2010/02/20/updated-ruby-utility-for-sqlite3/

      If you want to use the code in this post, however, note that it selects all the fields from a given row, so it returns an array of elements. You can see this by printing the “row” variable:
      puts row –> [“123”, “{\”foo\”:\”bar\”}”, 1300429278]

      You would need to parse the second element in the array, which corresponds to the “value” field:
      data = JSON.parse(row[1])

      Good luck!

      Erik

      March 17, 2011 at 10:36 pm

  3. Erik,

    I was just about to post that I had worked that even when you select for value rather than “*” – an array is still returned so I found the array element trick. I will have a look at your updated post though.

    Thanks!

    Phil.

    Philip Rhoades

    March 17, 2011 at 10:50 pm

    • I’m glad you found a solution that worked 🙂

      Erik

      March 20, 2011 at 8:11 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: