URLS

http://raa.ruby-lang.org/search.rhtml?search=sldb
http://codeforpeople.com/lib/ruby/sldb

ABOUT

sldb is a multi-thread, multi-process, and nfs safe abstraction of sqlite
databases.  the sldb module is a class generator for concrete databases.  the
programmer is freed from worrying about 'database locked' errors and
transactions are automatically retried using a linear cyclical backoff time.
in the case where all access is via the generated class sldb can even detect
certain bugs in nfs daemons and auto-recover.  the class is not limited to nfs
use whatsover and can be a useful way to use an embedded sql based databases
in your ruby code with minimal effort while still providing
multi-process/multi-thread concurrency - something which is quite difficult if
using the raw sqlite-ruby interface.

AUTHOR

ara [dot] t [dot] howard [at] noaa [dot] gov

SAMPLES

<========< sample/a.rb >========>

~ > cat sample/a.rb

  require 'sldb'

  #
  # use the factory method to specify your database class
  #
  DB =
    SLDB::class {
      schema 'create table t ( a, b, c )'
      path 'sldb'
    }

  #
  # create and instance and use it - it will be created and initialized with the
  # schema on the first use
  #
  db = DB::new

  db.transaction do
    db.execute 'insert into t values (0,1,2)'
    db.execute('select * from t'){|tuple| p tuple}
  end

~ > ruby sample/a.rb

  ["0", "1", "2"]

<========< sample/b.rb >========>

~ > cat sample/b.rb

  require 'csv'
  require 'sldb'

  DB = SLDB::class :schema => 'create table t ( a, b, c )'
  db = DB::new 'sldb'

  #
  # sldb uses arrayfields so many operations are natural since tuples are arrays,
  # yet can be indexed by field
  #
  db.transaction do
    db.execute "insert into t values ( 'A', 'B', 'C' )"
    db.execute('select * from t') do |tuple| 
      puts "tuple => #{ tuple.inspect }"
      tuple.fields.each{|f| puts "  tuple[#{ f }] => #{ tuple[f] }"}
    end
  end

  puts

  #
  # csv generation is an example of something which is much more natural with
  # arrays
  #
  CSV::generate('csv') do |csv|
    db.ro_transaction{db.execute('select * from t'){|t| csv << t}}
  end
  puts(IO::read('csv'))

~ > ruby sample/b.rb

  tuple => ["A", "B", "C"]
    tuple[a] => A
    tuple[b] => B
    tuple[c] => C

  A,B,C

<========< sample/c.rb >========>

~ > cat sample/c.rb

  require 'yaml'
  require 'sldb'

  DB = SLDB::new { 
    schema <<-sql
      create table t0 ( a, b, c);
      create table t1 ( x, y, z);
    sql

    path 'sldb'
  }

  db = DB::new

  #
  # many utility methods exist to make working with the databases easier
  #
  db.transaction do
    db.tablenames.each do |tablename|
      tuple = db.tuple_for tablename
      tuple.fields.each{|f| tuple[f] = db.timestamp 'local' => true}
      values = db.quote tuple
      sql = "insert into #{ tablename } values (#{ values.join ',' })"
      db.execute sql
    end
  end

  db.read_only_transaction do
    db.tablenames.each do |tablename|
      db.execute("select * from #{ tablename }") do |t|
        t.map!{|f| db.stamptime f, 'local' => true}
        y t.to_hash
      end
    end
  end

~ > ruby sample/c.rb

  --- 
  a: 2005-05-13 14:13:40.754680 -06:00
  b: 2005-05-13 14:13:40.754882 -06:00
  c: 2005-05-13 14:13:40.754910 -06:00
  --- 
  x: 2005-05-13 14:13:40.755928 -06:00
  y: 2005-05-13 14:13:40.755980 -06:00
  z: 2005-05-13 14:13:40.756005 -06:00

<========< sample/d.rb >========>

~ > cat sample/d.rb

  require 'yaml'
  require 'sldb'

  DB = SLDB::new { schema 'create table t ( tid, time )'; path 'sldb' }
  db = DB::new

  #
  # multi-processed/multi-threaded applications may simoultaneously access the db 
  #

  4.times do
    unless fork
      pid = $$
      threads = []
      2.times do |i|
        threads << 
          Thread::new(i, db) do |tid, db|
            sleep rand
            tuple = db.tuple_for 't'
            tuple['tid'] = "#{ pid }:#{ tid }" 
            tuple['time'] = Time::now.to_f
            values = db.quote tuple
            db.transaction{db.execute "insert into t values(#{ values.join ',' })"}
          end
      end
      threads.each{|t| t.join}
      exit
    end
  end

  4.times{ Process::wait }

  report = Hash::new{|h,k| h[k] = []} 

  db.transaction{db.execute("select * from t"){|t| report['t'] << t.to_hash}}

  y report

~ > ruby sample/d.rb

  --- 
  t: 
    - 
      time: "1116015221.16824"
      tid: 9697:1
    - 
      time: "1116015221.2326"
      tid: 9697:0
    - 
      time: "1116015221.64353"
      tid: 9696:0
    - 
      time: "1116015221.83744"
      tid: 9695:0
    - 
      time: "1116015221.85167"
      tid: 9695:1
    - 
      time: "1116015222.25714"
      tid: 9698:0
    - 
      time: "1116015222.27658"
      tid: 9698:1
    - 
      time: "1116015221.81659"
      tid: 9696:1

<========< sample/e.rb >========>

~ > cat sample/e.rb

  require 'sldb'

  #
  # the SLDB factory method handles - as a special case - a pathname being passed
  # in as meaning : create the class AND give me and instance of it which allows
  # connecting to dbs even when the schema is unknown
  #

  dbklass = SLDB::new 'schema' => 'create table t ( answer )', 
                      'path'  => 'sldb'
  db = dbklass::new
  db.transaction{ db.execute 'insert into t values ( 42 )' }

  fork do
    db = SLDB::new 'sldb'  # here we don't know schema
    db.transaction{ db.execute('select * from t'){|t| puts t['answer']}}
  end

  Process::wait

~ > ruby sample/e.rb

  42

CAVEATS

this library is __highly__ experimental and subject to change.