14. Database Access

# There are dbm, sdbm, gdbm modules
# and the bdb module for accessing the berkeley db
# sdbm seem to be available on the most systems,
# so we use it here
#
require "sdbm"
SDBM.open("filename", 0666) { |dbobj|
    # raises exception if open error
    
    # the returned sdbm-dbobj has most of the methods of a hash
    v = dbobj["key"]
    dbobj["key"] = "newvalue"
    if dbobj.has_key?("key")
        # ...
    end
    dbobj.delete("key2")
}
# database is open only inside the block.

# It is also possible to use a open .. close pair:
dbobj = SDBM.open("filename", 0666)
#.. do something with dbobj
dbobj.close

#!/usr/bin/ruby -w
# userstats - generate statistics on who is logged in
# call with usernames as argument to display the totals
# for the given usernames, call with "ALL" to display all users

require "sdbm"
filename = '/tmp/userstats.db'
SDBM.open(filename, 0666) { |dbobj|
    if ARGV.length > 0
        if ARGV[0] == "ALL"
            # ARGV is constant, so we need the variable userlist
            userlist = dbobj.keys().sort()
        else
            userlist = ARGV
        end
        userlist.each { |user|
            print "#{user}\t#{dbobj[user]}\n"
        }
    else
        who = `who`
        who.split("\n").each { |line|
            md = /^(\S+)/.match(line)
            raise "Bad line from who: #{line}" unless md
            # sdbm stores only strings, so "+=" doesn't work,
            # we need to convert them expicitly back to integer.
            if dbobj.has_key?(md[0])
                dbobj[md[0]] = dbobj[md[0]].to_i + 1
            else
                dbobj[md[0]] = "1"
            end
        }
    end
}

# using open and clear
dbobj = SDBM.open("filename", 0666)
dbobj.clear()
dbobj.close()
# deleting file and recreating it
# the filenames depend on the flavor of dbm you use,
# for example sdbm has two files named filename.pag and filename.dir,
# so you need to delete both files
begin
    File.delete("filename")
    # raises Exception if not exist
    dbobj = SDBM.open("filename", 0666)
rescue
    # add error handling here
end

# sdbm2gdbm: converts sdbm database to a gdbm database
require "sdbm"
require "gdbm"

unless ARGV.length == 2
    fail "usage: sdbm2gdbm infile outfile"
end
infile = ARGV[0]
outfile = ARGV[1]

sdb = SDBM.open(infile)
gdb = GDBM.open(outfile, 0666)
sdb.each { |key, val|
    gdb[key] = val
}
gdb.close
sdb.close

#!/usr/bin/ruby -w
# dbmmerge: merges two dbm databases
require "sdbm"

unless ARGV.length == 3
    fail "usage: dbmmerge indb1 indb2 outdb"
end
infile1 = ARGV[0]
infile2 = ARGV[0]
outfile = ARGV[2]

in1 = SDBM.open(infile1, nil)
in2 = SDBM.open(infile2, nil)
outdb = SDBM.open(outfile, 0666)

[in1, in2].each { |indb|
    indb.each { |key, val|
        if outdb.has_key?(key)
            # decide which value to set.
            # set outdb[key] if necessary
        else
            outdb[key] = val
        end
    }
}
in1.close
in2.close
outdb.close

# we write a tie method that extends the Array class.
# It reads the file into the memory, executes the code block
# in which you can manipulate the array as needed, and writes
# the array back to the file after the end of the block execution
class Array
    def tie(filename, flags)
        File.open(filename, flags) { |f|
            f.each_line { |line|
                self.push(line.chomp)
            }
            yield
            f.rewind
            each { |line|
                if line
                    f.puts(line)
                else
                    f.puts ""
                end
            }
        }
    end
end

array = Array.new
array.tie("/tmp/textfile.txt", File::RDWR|File::CREAT) {
    array[4] = "a new line 4"
}

# The tied array can be manipulated like a normal array,
# so there is no need for a special API, and the recno_demo program
# to demonstrate is API is useless


# tied array demo: show how to use array with a tied file
filename = "db_file.txt"
lines = Array.new
File.unlink(filename) if File.exists?(filename)
lines.tie(filename, File::RDWR | File::CREAT) {
    # first create a textfile to play with
    lines[0] = "zero"
    lines[1] = "one"
    lines[2] = "two"
    lines[3] = "three"
    lines[4] = "four"

    # print the records in order.
    # Opposed to perl, the tied array behaves exactly as a normal array
    puts "\nOriginal"
    for i in 0..(lines.length-1)
        puts "#{i}: #{lines[i]}"
    end

    #use push and pop
    a = lines.pop
    lines.push("last")
    puts("The last line was [#{a}]")

    #use shift and unshift
    a = lines.shift
    lines.unshift("first")
    puts("The first line was [#{a}]")

    # add record after record 2
    i = 2
    lines.insert(i + 1, "Newbie")

    # add record before record one
    i = 1
    lines.insert(i, "New One")

    # delete record 3
    lines.delete_at(3)

    #now print the records in reverse order
    puts "\nReverse"
    (lines.length - 1).downto(0){ |i|
        puts "#{i}: #{lines[i]}"
    }

}

# example to store complex data in a database
# uses marshall from the standard library
require "sdbm"
db = SDBM.open("pleac14-8-database", 0666)

# convert the Objects into strings and back by using the Marshal module.
# Most normal objects can be converted out of the box,
# but not special things like procedure objects,
# IO instance variables, singleton objects

db["Tom Christiansen"] = Marshal.dump(["book author",  "tchrist@perl.com"])
db["Tom Boutell"] = Marshal.dump(["shareware author",
"boutell@boutell.com"])

name1 = "Tom Christiansen"
name2 = "Tom Boutell"

tom1 = Marshal.load(db[name1])
tom2 = Marshal.load(db[name2])

puts "Two Toming: #{tom1} #{tom2}"

if tom1[0] == tom2[0] && tom1[1] == tom2[1]
   puts "You're having runtime fun with one Tom made two."
else
   puts "No two Toms are ever alike"
end

# To change parts of an entry, get the whole entry, change the parts,
# and save the whole entry back
entry = Marshal.load(db["Tom Boutell"])
entry[0] = "Poet Programmer"
db["Tom Boutell"] = Marshal.dump(entry)
db.close

# example to make data persistent
# uses Marshal from the standard lib
# Stores the data in a simple file,
# see 14.8 on how to store it in a dbm file

# The BEGIN block is executed before the rest of the script
# we use global variables here because local variables
# will go out of scope and are not accessible from the main script

BEGIN {
   $persistent_store = "persitence.dat"
   begin
     File.open($persistent_store) do |f|
       $stringvariable1 = Marshal.load(f)
       $arrayvariable2 = Marshal.load(f)
     end
   rescue
     puts "Can not open #{$persistent_store}"
     # Initialisation if this script runs the first time
     $stringvariable1 = ""
     $arrayvariable2 = []
   end
}

END {
   File.open($persistent_store, "w+") do |f|
     Marshal.dump($stringvariable1, f)
     Marshal.dump($arrayvariable2, f)
   end
}

# simple test program
puts $stringvariable1
puts $arrayvariable2
$stringvariable1 = "Hello World"
$arrayvariable2.push(5)
puts $stringvariable1
puts $arrayvariable2

#!/usr/bin/ruby -w
# Ruby has a dbi module with an architecture similar
# to the Perl dbi module: the dbi module provides an unified
# interface and uses specialized drivers for each dbms vendor
#
begin
    DBI.connect("DBI:driver:driverspecific", "username", "auth") {
        |dbh|

        dbh.do(SQL1)

        dbh.prepare(SQL2){ |sth|
            sth.execute
            sth.fetch {|row|
                # ...
            }
        } # end of block finishes the statement handle
    } # end of block closes the database connection
rescue DBI::DatabaseError => e
    puts "dbi error occurred"
    puts "Error code: #{e.err}"
    puts "Error message: #{e.errstr}"
end

#!/usr/bin/ruby -w
# dbusers - example for mysql which creates a table,
# fills it with values, retrieves the values back,
# and finally destroys the table.

require "dbi"

# replacement for the User::pwnt module
def getpwent
    result = []
    File.open("/etc/passwd") {|file|
        file.each_line {|line|
            next if line.match(/^#/)
            cols = line.split(":")
            result.push([cols[2], cols[0]])
        }
    }
    result
end

begin
    DBI.connect("DBI:Mysql:pleacdatabase", "pleac", "pleacpassword") {
        |conn|

        conn.do("CREATE TABLE users (uid INT, login CHAR(8))")

        users = getpwent

        conn.prepare("INSERT INTO users VALUES (?,?)") {|sth|
            users.each {|entry|
                sth.execute(entry[0], entry[1])
            }
        }

        conn.execute("SELECT uid, login FROM users WHERE uid < 50") {|sth|
            sth.fetch {|row|
                puts row.collect {|col|
                    if col.nil?
                        "(null)"
                    else
                        col
                    end
                }.join(", ")
            }
        }

        conn.do("DROP TABLE users")
    }
rescue DBI::DatabaseError => e
    puts "dbi error occurred"
    puts "Error code: #{e.err}"
    puts "Error message: #{e.errstr}"
end