[ANN] sldb-0.1.0

[Ara.T.Howard]

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
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.


-a
--
===============================================================================
| email :: ara [dot] t [dot] howard [at] noaa [dot] gov
| phone :: 303.497.6469
| renunciation is not getting rid of the things of this world, but accepting
| that they pass away. --aitken roshi
===============================================================================