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.