#!/usr/bin/env ruby
#
# Sample Ruby DBI program for CS445
#
# dkulp - 3/2/07

require 'rubygems'
require 'dbi'

begin

  # This establishes a database connection "handle".
  # A database with the same name as your username has been created for each student.  
  # There is no password required because authentication is based on successful login.
  dbh = DBI.connect("dbi:Pg:database=#{ENV['USER']};host=db-edlab;port=7445", ENV['USER'])

  # The normal order of calls to run an SQL statement is:
  # - prepare: generates a "statement handle" object that contains the query
  # - execute: tell the statement handle to actually run the query
  # - fetch: iterate through the query results, row by row
  # Depending on the approach, you may also have to close the statement handle, but
  # if you use the style that I demonstrate below then all of the clean-up is taken
  # care of as variables go out of scope.  

  # Here's my preferred approach...

  # %{ ... } is an alternative to double quotes that allows for multi-line strings.
  # It's good for SQL statements.  Note, to avoid SQL injection problems, don't perform
  # variable substitution in your strings.  Use positional parameters, instead.  For example:
  query = %{
        --- Get the names of stores that sold fruit of the specified season.
        SELECT distinct st.store, f.name
          FROM stores st, fruits f, sales sa
         WHERE sa.fruit_id = f.id
           AND sa.store_id = st.id
           AND f.season = ?
  }

  # 'prepare' creates a statement handle and
  # passes it to the enclosed block.
  dbh.prepare(query) do |sth|

     # I'm iterating over each of the words passed on the command line.
     # This is to demonstrate how you can execute the same prepared query
     # multiple times with different parameter values.
     ARGV.each do |season|
	puts season
        sth.execute(season)              # The string season gets interpolated at the first '?'
        sth.fetch do |row|               # Iterate over each row

           # The row object that fetch returns is a fancy/smart object that behaves
           # like both an array and a hash.  If you use square braces and an integer or range,
           # then it will behave like an array lookup and return the columns.  If you pass
           # a string, then it behaves like a hash and returns the value of the named column.
           # This second approach is better since it protects you from bugs when you change
           # the columns in the query.  (row.inspect is a ruby method available for all objects.
           # It usually just calls the to_s method to generate a string from the object.)
           puts row.inspect; 
           puts row['store'], row[1]
        end
     end
  end


  # If you just have a static query, i.e. one that doesn't change based on user input,
  # then you can simplify things by calling execute() with the query string directly.
  # This will do a prepare, execute the query, and pass the statement handle to the block.
  # In this example I also show how you can retrieve all of the data using fetch_all 
  # and stuff it into an array of row objects.
  dbh.execute("SELECT * FROM fruits") do |sth|
    rows = sth.fetch_all
    col_names = sth.column_names

    puts "There are #{rows.size} rows.  Here's a fancy method for pretty-printing tables."

    DBI::Utils::TableFormatter.ascii(col_names, rows)
  end

  # Finally, if you want to ensure that a set of modifications to the database are atomic,
  # then you should enclose them in a transaction.  If an error occurs (either out of your
  # control or by calling raise), then the transaction will be automatically rolled back
  # because the transaction method will catch the error and issue a rollback.
  # Note that the transaction will catch and rollback an error, but the error will still
  # get thrown up the call stack, so if you want to continue your program, you need to
  # catch it.  In Ruby, catching errors is easy.  The exception object (which can be 
  # treated like a string) is stored in the special variable $! 
  dbh['AutoCommit'] = false
  begin
    dbh.transaction() do |dbh|

     # Again, please note that you should use the '?' position parameter approach, instead
     # of making the SQL strings yourself.  This avoids SQL injection problems and also
     # allows you to avoid quoting issues.
      dbh.do("UPDATE stores SET zip = ? WHERE store = ?", '01003', "Stan's Fruit Stand")
      raise "Demonstrating that an error will cause rollback"
      dbh.do("INSERT INTO fruits VALUES (99, 'Grape', ?, ?)", 'Venus', 'Early')
    end
  rescue
    puts "Oops.  Something bad happened (#{$!}), but I'm continuing."
  end

# For finer control, you may want to put begin/rescue blocks around individual
# database calls instead of around the entire program.
rescue RuntimeError => e
  puts e	# this is usually a connection failure

rescue DBI::DatabaseError => e
  puts "An error occurred"
  puts "Error code: #{e.err}"
  puts "Error message: #{e.errstr}"
ensure
  # disconnect from server
  dbh.disconnect if dbh
end