Re: Simple Ruby DB apps/programs ...

On Tue, 6 Jan 2004, Useko Netsumi wrote:

> Date: Tue, 06 Jan 2004 00:18:42 -0500
> From: Useko Netsumi <[e-mail]>
> Reply-To: [e-mail]
> Newsgroups: comp.lang.ruby
> Subject: Re: Simple Ruby DB apps/programs ...
> 
> Ara.T.Howard wrote:
> > On Mon, 5 Jan 2004, Useko Netsumi wrote:
> > 
> > 
> >>Date: Mon, 05 Jan 2004 19:25:27 -0500
> >>From: Useko Netsumi <[e-mail]>
> >>Reply-To: [e-mail]
> >>Newsgroups: comp.lang.ruby
> >>Subject: Re: Simple Ruby DB apps/programs ...
> >>
> >>Carl Youngblood wrote:
> >>
> >>>Useko Netsumi wrote:
> >>>
> >>>
> >>>>I was wondering if there are some example of small Ruby(1.8.1) 
> >>>>Database Apps/Programs. Preferably using Relational Database such as 
> >>>>MySQL(4 or 5) or Oracle.
> >>>>
> >>>>I'd love to see some example of storing name(first,last), 
> >>>>address(addr1,addr2,city,zip),phone(home,work,mobile), and a photo image.
> >>>>
> >>>>It took me a while to write it in PHP but perhaps I can do it in Ruby 
> >>>>more cleanly while learning this great language.
> >>>>
> >>>>Thanks
> >>>>
> >>>>/useko
> >>>>
> >>>
> >>>My favorite DBMS for small client apps is sqlite.  No other DB comes 
> >>>close to it in terms of convenience and speed, as long as you're not 
> >>>running a distributed type of an application with hundreds of clients 
> >>>accessing the database at once.  There is no need for a database server 
> >>>at all.  All necessary code for accessing the database is compiled in, 
> >>>and databases are just plain old files.  And it is ACID-compliant. Check 
> >>>out this ruby extension for it here:
> >>>
> >>>http://sqlite-ruby.sourceforge.net/
> >>>
> >>>Carl
> >>>
> >>
> >>Thanks to all.
> >>
> >>Perhaps y'all can give me some advice. My apps are running a web photo 
> >>apps with mutiple tables in the database. I do not store the image in 
> >>the DB but just the /image/file/path and other textual information such 
> >>as location, date, time, who took the pictures, and comment fields. 
> >>User(s) can only browse, search, and list the information for now. And, 
> >>I do not expect more than 20 users accessing it at any given time. Will 
> >>it work with SQLITE? Or do I need MySQL or more advanced(more expensive) 
> >>RDBMS to handle those tasks.
> >>
> >>Thanks
> >>
> >>/useko
> >>
> > 
> > 
> > i use pstore for alot of web stuff - it works fine:
> > 
> > ~/eg/ruby > cat photo.rb 
> > require 'pstore'
> > 
> > class DB
> >   def initialize path = 'photo.db'
> >     @pstore = PStore.new path
> >   end
> >   def []= name, record 
> >     address, phone = record
> > 
> >     @pstore.transaction do
> >       @pstore[name] = [address, phone]
> >     end
> >   end
> >   def [] name
> >     @pstore.transaction(read_only = true){ @pstore[name] }
> >   end
> >   def each(&block)
> >     @pstore.transaction do
> >       @pstore.roots.each{|name| block.call(name, @pstore[name])}
> >     end
> >   end
> >   def << record
> >     first,last,addr1,addr2,city,zip,home,work,mobile = record
> >     name    = Name[first, last]
> >     address = Address[addr1,addr2,city,zip]
> >     phone   = Phone[home,work,mobile]
> >     self[name] = [address, phone]
> >   end
> > 
> >   Name    = Struct.new "Name", :first, :last
> >   Address = Struct.new "Address", :addr1, :addr2, :city, :zip
> >   Phone   = Struct.new "Phone", :home, :work, :mobile
> > 
> >   [Name, Address, Phone].each{|c| class << c; alias [] new; end}
> > end
> > 
> > if $0 == __FILE__
> >   db = DB.new
> > 
> >   records = [
> >     %w(john doe foo bar boulder 80304 1 2 3),
> >     %w(jane doe bar foo boulder 80305 3 2 1),
> >   ]
> > 
> >   records.each{|record| db << record}
> > 
> >   john = DB::Name['john', 'doe']
> >   jane = DB::Name['jane', 'doe']
> > 
> >   db.each do |name, address, phone|
> >     printf "name: %s\naddress: %s\nphone: %s\n\n", 
> >       name.inspect, address.inspect, phone.inspect
> >   end
> > 
> >   p db[john]
> >   p db[jane]
> > end
> > 
> > 
> > ~/eg/ruby > ruby photo.rb 
> > 
> > name: #<struct Struct::Name first="john", last="doe">
> > address: [#<struct Struct::Address addr1="foo", addr2="bar", city="boulder", zip="80304">, #<struct Struct::Phone home="1", work="2", mobile="3">]
> > phone: nil
> > 
> > name: #<struct Struct::Name first="jane", last="doe">
> > address: [#<struct Struct::Address addr1="bar", addr2="foo", city="boulder", zip="80305">, #<struct Struct::Phone home="3", work="2", mobile="1">]
> > phone: nil
> > 
> > [#<struct Struct::Address addr1="foo", addr2="bar", city="boulder", zip="80304">, #<struct Struct::Phone home="1", work="2", mobile="3">]
> > [#<struct Struct::Address addr1="bar", addr2="foo", city="boulder", zip="80305">, #<struct Struct::Phone home="3", work="2", mobile="1">]
> > 
> > 
> > you can obviously just store the path to the photo this way...  the thing with
> > pstore is that you can tailor the object to meet your needs: perhaps a hash
> > would be better: it's up to you.  also check out madeleine (on RAA) for this
> > purpose.
> > 
> > 
> > -a
> 
> Thanks Ara, I'll definitely will try your example above. And check out
> madeline as well.
> 

> One question though, have you tried the pstore for multi-user or this is
> mainly for single user only? 

i have used it for multi-user in house applications.  i seem to remember you
saying this was for a round 20 users or so - in which case it would be fine.
if you want more you really need to go with postgresql and 'set isolation
level serializable' or deal with transaction conflicts yourself.  pstore uses
flock so it supports single writer many reader semantics.  note that this is
the same as sqlite - it too uses flock and coarse grained locking internally.

> What happen if 2 or more user accessing the same db at the same time? Thanks

one will block.  this is generally pretty easy to deal with by simply
minimizing the amount of time you are writing to the database, for example
use:

  value = cgi['value']

  pstore.transaction do
    pstore[:key] = value
  end

vs.

  pstore.transaction do
    value = cgi['value']
    pstore[:key] = value
  end

unless you db/updates are _very_ big the time blocked will probably be about
what the connection time would be been if using a rdbms server.

and use read_only = true when possible.  note that, if you really want true
concurency your cgi script will become pretty compilcated - especially for
multi page apps which can span several minutes...  simply using mysql or
postgresql won't solve think for you.

i would reccomend this:

  * abstract the database layer as a class which get records by key, adds
    records, deletes records, etc.

  * develop your app using pstore or other embedded db

move to a db server iff throughput becomes a problem.  the ability to develop
quickly, back up your db using 'tar', move to a new server using 'scp', etc.
are real benefits - plus you do not move outside the ruby installation and
need to involve sysads (eek).  rdbms are great but sometimes a little
overkill.

you also might want to check out 'bdb' which is very good and has transaction
support.

-a
-- 

ATTN: please update your address books with address below!

===============================================================================
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: [phone]
| ADDRESS :: E/GC2 325 Broadway, Boulder, CO 80305-3328
| STP     :: http://www.ngdc.noaa.gov/stp/
| NGDC    :: http://www.ngdc.noaa.gov/
| NESDIS  :: http://www.nesdis.noaa.gov/
| NOAA    :: http://www.noaa.gov/
| US DOC  :: http://www.commerce.gov/
|
| The difference between art and science is that science is what we
| understand well enough to explain to a computer.  
| Art is everything else.  
|   -- Donald Knuth, "Discover"
|
| /bin/sh -c 'for l in ruby perl;do $l -e "print \"\x3a\x2d\x29\x0a\"";done' 
===============================================================================