#
# Copyright (C) 2005-2008 Christoph Rupp (chris@crupp.de).
#
# This program is free software; you can redistribute it and/or modify it
# under the terms of the GNU General Public License as published by the
# Free Software Foundation; either version 2 of the License, or 
# (at your option) any later version.
#
# See file COPYING.GPL2 and COPYING.GPL3 for License information.
#

#
# An environment with a customer and an order-database is created; a third
# database is created which manages the 1:n relationship between the 
# other two.
#

# set the library path, otherwise hamsterdb.so/.dll is not found
# these lines are specific for the hamsterdb-python distribution; you do
# not need to add them, if your hamsterdb-python library is properly
# installed
import os
import sys
import distutils.util
p    = distutils.util.get_platform()
ps   = ".%s-%s" % (p, sys.version[0:3])
sys.path.insert(0, os.path.join('build', 'lib'+ps))
sys.path.insert(1, os.path.join('..', 'build', 'lib'+ps))
import hamsterdb

# a Customer class
class Customer:
    def __init__(self, id, name):
        self.id=id
        self.name=name

    def get_key(self):
        return "%d" % self.id

    def get_record(self):
        return self.name

# an Order class
class Order:
    def __init__(self, id, customer_id, assignee):
        self.id=id
        self.customer_id=customer_id
        self.assignee=assignee

    def get_key(self):
        return "%d" % self.id

    def get_c2o_key(self):
        return "%d" % self.customer_id

    def get_record(self):
        return self.assignee

def run_demo():
    DBNAME_CUSTOMER=1
    DBNAME_ORDER   =2
    DBNAME_C2O     =3
    env=hamsterdb.env()

    customers=[
        Customer(1, "Alan Antonov Corp."),
        Customer(2, "Barry Broke Inc."),
        Customer(3, "Carl Caesar Lat."),
        Customer(4, "Doris Dove Brd.")
    ]

    orders=[
        Order(1, 1, "Joe"),
        Order(2, 1, "Tom"),
        Order(3, 3, "Joe"),
        Order(4, 4, "Tom"),
        Order(5, 3, "Ben"),
        Order(6, 3, "Ben"),
        Order(7, 4, "Chris"),
        Order(8, 1, "Ben")
    ]

    # create a new database environment
    env.create("test.db")

    # create a database for the customers, one for the orders and a third
    # to manage our 1:n relation (and therefore needs to enable duplicate keys)
    db=[
        env.create_db(DBNAME_CUSTOMER),
        env.create_db(DBNAME_ORDER),
        env.create_db(DBNAME_C2O, hamsterdb.HAM_ENABLE_DUPLICATES)
    ]

    # create a cursor for each database
    cursors=[
        hamsterdb.cursor(db[0]),
        hamsterdb.cursor(db[1]),
        hamsterdb.cursor(db[2])
    ]

    # insert the Customer objects in the customer table
    #
    # INSERT INTO customers VALUES (1, "Alan Antonov Corp.");
    # INSERT INTO customers VALUES (2, "Barry Broke Inc.");
    # etc
    for c in customers:
        db[0].insert(c.get_key(), c.get_record())

    # insert the Order objects in the order table
    #
    # INSERT INTO orders VALUES (1, "Joe")
    for o in orders:
        db[1].insert(o.get_key(), o.get_record())

    # and now the 1:n relationships; the flag HAM_DUPLICATE creates a
    # duplicate key, if the key already exists
    #
    # INSERT INTO c2o VALUES (1, 1);
    # INSERT INTO c2o VALUES (1, 2);
    # etc
    for o in orders:
        db[2].insert(o.get_c2o_key(), o.get_key(), hamsterdb.HAM_DUPLICATE)

    # now start the query - we want to dump each customer with his
    # orders
    # 
    # loop over the customer; for each customer, loop over the 1:n talbe
    # and pick those orders with the customer id. then load the order
    # and print it
    #
    # the outer loop is similar to
    # SELECT * FROM customers WHERE 1;
    while True:
        try:
            cursors[0].move_to(hamsterdb.HAM_CURSOR_NEXT)
        except hamsterdb.error, (errno, message):
            # reached the end of the table?
            if errno == hamsterdb.HAM_KEY_NOT_FOUND:
                break
            raise

        c=Customer(int(cursors[0].get_key()), cursors[0].get_record())
        print "customer", c.id, ("('%s')" % c.name)

        # loop over the 1:n table
        #
        # before we start the loop, we move the cursor to the first
        # duplicate key
        #
        # SELECT * FROM customers, orders, c2o
        #   WHERE c2o.customer_id=customers.id AND
        #      c2o.order_id=orders.id;
        try:
            cursors[2].find(c.get_key())
        except hamsterdb.error, (errno, message):
            # no orders for this customer?
            if errno == hamsterdb.HAM_KEY_NOT_FOUND:
                continue
            raise

        while True:
            customer_id=int(cursors[2].get_key())

            # load the order
            # SELECT * FROM orders WHERE id = order_id;
            order_id=int(cursors[2].get_record())
            cursors[1].find("%d"%order_id)
            o=Order(order_id, customer_id, cursors[1].get_record())

            print "  order:", order_id, ("(assigned to %s)"%o.assignee)

            # the flag HAM_ONLY_DUPLICATES restricts the cursor movement
            # to the duplicates of this key
            try:
                cursors[2].move_to(hamsterdb.HAM_CURSOR_NEXT
                                   | hamsterdb.HAM_ONLY_DUPLICATES)
            except hamsterdb.error, (errno, message):
                # no more orders for this customer?
                if errno == hamsterdb.HAM_KEY_NOT_FOUND:
                    break
                raise

    print "success!"
    return 0

try:
    exit(run_demo())
except hamsterdb.error, (errno, message):
    print "unexpected exception", errno, "-", message
    exit(-1)