# This script demonstrates how to link a field in the table to a subset
# of records from another table (i.e. a "one to many" link in database
# lingo).

# In this example, we have an order table and an order_item table.  Each
# record in the order table represents a customer order.  The order_item
# table holds the detail items for each order. We create a one-to-many link
# between the order table and the order_item table by providing extra
# information about the order.items field when we create the order table.

require 'kirbybase'

db = KirbyBase.new

# To run as a client in a multi-user environment, uncomment next line.
# Also, make sure kbserver.rb is running.
#db = KirbyBase.new do |d|
#    d.connect_type = :client
#    d.host = 'localhost'
#    d.port = 44444
#end

# If table exists, delete it.
db.drop_table(:order) if db.table_exists?(:order)
db.drop_table(:order_item) if db.table_exists?(:order_item)

# Create an order item table.  This is the child table to the order table.
# Create child table before creating parent table so that KirbyBase can
# take advantage of any indexes.
order_item_tbl = db.create_table(:order_item,
 :item_id, :Integer,
 :order_id, :Integer,
 :descr, :String,
 :qty, :Integer,
 :price, :Float
)

# Create a table.  We are telling KirbyBase that the items field is
# to be linked to the order_item table by comparing the order.order_id
# field to the order_item.order_id field.  By specifying :Link_many, we are
# telling KirbyBase to make this a one-to-many link.  The result of this is
# that when you do a select, the items field of the order table is going to
# hold a reference to a ResultSet (i.e. Array) holding all order_item
# records whose order_id field match the order_id field in the order record.
order_tbl = db.create_table(:order,
 :order_id, :Integer,
 :customer, :String,
 :items, {:DataType=> :ResultSet, :Link_many=> [:order_id, :order_item,
 :order_id]}
)

# Insert some order records.
order_tbl.insert({:order_id=>345, :customer=>'Ford'})
order_tbl.insert({:order_id=>454, :customer=>'Microsoft'})
order_tbl.insert({:order_id=>17, :customer=>'Boeing'})

# Insert some order item records.
order_item_tbl.insert(1,345,'Steel',30,19.99)
order_item_tbl.insert(2,345,'Glass',5,4.15)
order_item_tbl.insert(5,454,'Floppies',750000,0.5)
order_item_tbl.insert(3,17,'Wheels',200,2500.0)
order_item_tbl.insert(4,17,'Wings',25,1000000.0)

# Print all orders.  Under each order print all items in that order.
order_tbl.select.each do |r|
    puts '%3d %s' % [r.order_id, r.customer]
    r.items.each { |i|
        puts "\t%d %15s %6d %10.2f" % [i.item_id, i.descr, i.qty, i.price]
    }
end