# Ruby Firebird Extension Library
#
# Please see the RDoc documentation for API details.
#
# What follows is a brief overview of how to use this library.
# This file is executable.  However, you may have to adjust the database connection parameters.

# Load the library

require 'fb'

# The library contains on module, Fb.
# Within Fb are four primary classes, Fb::Database, Fb::Connection, Fb::Cursor and Fb::Error.
# For convenience, we'll include these classes into the current context.

include Fb

# The Database class acts as a factory for Connections.
# It can also create and drop databases.

db = Database.new(
  :database => "localhost:c:/var/fbdata/readme.fdb",
  :username => 'sysdba',
  :password => 'masterkey')

# :database is the only parameter without a default.

# Let's connect to the database, creating it if it doesn't already exist.

conn = db.connect rescue db.create.connect

# We'll need to create the database schema if this is a new database.

conn.execute("CREATE TABLE TEST (ID INT NOT NULL PRIMARY KEY, NAME VARCHAR(20))") if !conn.table_names.include?("TEST")

# Let's insert some test data using a parameterized query.  Note the use of question marks for place holders.

10.times {|id| conn.execute("INSERT INTO TEST VALUES (?, ?)", id, "John #{id}") }

# Here we'll conduct a spot check of the data we have just inserted.

ary = conn.query("SELECT * FROM TEST WHERE ID = 0 OR ID = 9")
ary.each {|row| puts "ID: #{row[0]}, Name: #{row[1]}" }

# Don't like tying yourself down to column offsets?

ary = conn.query(:hash, "SELECT * FROM TEST WHERE ID = 0 OR ID = 9")
ary.each {|row| puts "ID: #{row['ID']}, Name: #{row['NAME']}" }

# Let's change all the names.

total_updated = 0
conn.execute("SELECT ID FROM TEST") do |cursor|
  cursor.each do |row|
    updated = conn.execute("UPDATE TEST SET NAME = ? WHERE ID = ?", "John Doe #{row[0]}", row[0])
    total_updated += updated
  end
end
puts "We updated a total of #{total_updated} rows."

# Actually, I only need the first and last rows.

deleted = conn.execute("DELETE FROM TEST WHERE ID > ? AND ID < ?", 0, 9)
puts "Expecting to delete 8 rows, we have deleted #{deleted}."

# Using a simple, per-connection transaction strategy, we'll demonstrate rollback and commit.

conn.transaction

for i in 10..1000
  conn.execute("INSERT INTO TEST VALUES (?, ?)", i, "Jane #{i}")
end

# What was I thinking?  Let's roll that back.

conn.rollback

# Are they still there?

janes = conn.query("SELECT * FROM TEST WHERE ID >= 10")
puts "Expecting zero rows, we find #{janes.size} Janes."

# Let's try again.

conn.transaction

10.upto(19) do |i|
  conn.execute("INSERT INTO TEST (ID, NAME) VALUES (?, ?)", i, "Sue #{i}")
end

# That's more like it.

conn.commit

# It's important to close your cursor when you're done with it.

cursor = conn.execute("SELECT * FROM TEST")
while row = cursor.fetch(:hash)
  break if row['NAME'] =~ /e 13/
end
cursor.close

# You may find it easier to use a block.

conn.execute("SELECT * FROM TEST") do |cursor|
  while row = cursor.fetch(:hash)
    break if row['NAME'] =~ /e 13/
  end
end

# That way the cursor always gets closed, even if an exception is raised.
# Transactions work the same way.  Here's one that should work.

conn.transaction do
  20.upto(25) do |i|
    conn.execute("INSERT INTO TEST VALUES (?, ?)", i, "George #{i}")
  end
end

# The transaction is automatically committed if no exception is raised in the block.
# We expect trouble in this next example, on account of our primary key.

begin
  conn.transaction do
    execute("INSERT INTO TEST VALUES (0, 'Trouble')")
    puts "This line should never be executed."
  end
rescue
  puts "Rescued."
end

# Is it there?

trouble = conn.query("SELECT * FROM TEST WHERE NAME = 'Trouble'")
puts "Expecting zero rows, we find #{trouble.size} 'Trouble' rows."

# How about demonstrating a more advanced transaction?
# First, we'll start a snapshot transaction.
# This should give us a consistent view of the database.

conn.transaction("SNAPSHOT") do

# Then, we'll open another connection to the database and insert some rows.

  Database.connect(:database => "localhost:c:/var/fbdata/readme.fdb") do |conn2|
    for i in 100...110
      conn2.execute("INSERT INTO TEST VALUES (?, ?)", i, "Hi #{i}")
    end
  end
  
# Now, let's see if we see them.

  hi = conn.query("SELECT * FROM TEST WHERE ID >= ?", 100)
  puts "Expecting zero rows, we find #{hi.size} Hi rows."
end

# Now we will try our example again, only with a READ COMMITTED transaction.

conn.transaction("READ COMMITTED") do

# Then, we'll open another connection to the database and insert some rows.

  Database.connect(:database => "localhost:c:/var/fbdata/readme.fdb") do |conn2|
    for i in 200...210
      conn2.execute("INSERT INTO TEST VALUES (?, ?)", i, "Hello #{i}")
    end
  end
  
# Now, let's see if we see them.

  hello = conn.query("SELECT * FROM TEST WHERE ID >= ?", 200)
  puts "Expecting ten rows, we find #{hello.size}."
end

# Don't forget to close up shop.

conn.close

# We could have called conn.drop.
# We could still call db.drop

__END__
5. Changes

0.01 - 1999-06-11 - baseline 
0.02 - 1999-07-16 - memory bug in ibconn_cursor, English documents.
0.03 - ???
0.04 - 2001-08-17 - insert, fetch blobs as strings
0.05 - 2001-09-05 - add block syntax for Connection#connect and Connection#execute
0.06 - 2004-11-02 - ???
0.07 - 2004-11-03 - ???

Change Log

2006-05-18 - 2006-06-04

* Forked to create Fb extension.
* Converted from K&R to ANSI C.
* Added ability to create and drop databases.
* Created near-comprehensive unit tests.
* Renamed functions for consistency and improved style.
* Improved error message reporting.
* Added the option of returning rows as Hashes as well as tuples.
* Fixed bug inserting blobs larger than 64K.
* Added methods to retrieve lists of tables, generators, views, stored procedures and roles.
* Added .NET-style connection strings.
* Improved reporting on column types.
* Added the option to report column names in lowercase where they were not already in mixed case (this feature is for Rails).
* Changed automatic transaction semantics so automatically-started transactions are also committed automatically.
* Converted from using a single, global transaction to a transaction per connection.
* Converted from using global buffers to per-cursor input and output buffers.
* Added RDoc documentation.
* Insert, update and delete statements may be given an array of tuples for a parameter and will be executed once per tuple.
* Rewrote README to match new API.

2007-12-11

* Properly scaled NUMERIC and DECIMAL types when inserting with parameters.

2007-12-13

* Rounded instead of flooring NUMERIC and DECIMAL types.
* Replace deprecated isc_interprete method call for FB2.

2008-04-22

* Implement explicit end-of-data flag for cursors to avoid synchronization error in newer Firebird builds.
* Allocated longer buffers to avoid memory access error reported by valgrind
* Tightened up C syntax for stricter compilers.
* Factored symbol and regex creation out to global scope for efficiency.
* Removed global transaction functions that weren't exposed anyway.
* Removed remaining global state. This should allow for better concurrency.