#!/usr/bin/env python

import sys; sys.path.append('../../../') # show python where the modules are

import web.database, web.database.object
connection = web.database.connect(
    adapter="snakesql", 
    database="database-object-related",
    autoCreate = 1,
)
cursor = connection.cursor()

person = web.database.object.Table("Person")
person.addColumn(web.database.object.String(name="firstName", unique=True, required=True, key=True))
person.addColumn(web.database.object.String(name="surname"))
person.addRelated(name="addresses", foreignTable="Address")

address = web.database.object.Table("Address")
address.addColumn(web.database.object.String(name="firstLine"))
address.addColumn(web.database.object.String(name="postcode", unique=True, required=True, key=True))
address.addRelated(name="people", foreignTable="Person")

database = web.database.object.Database()
database.addTable(person)
database.addTable(address)
database.init(cursor)

if not database.tablesExist():
    database.createTables()
    print "Created Table"

john = database['Person'].insert(firstName='John', surname='Smith')
owen = database['Person'].insert(firstName='Owen', surname='Jones')

friendlyPlace = database['Address'].insert(firstLine='12 Friendly Place', postcode='MK4 1AB')
crazyGardens  = database['Address'].insert(firstLine='3a Crazy Gardens', postcode='OX1 2ZX')
greatRoad     = database['Address'].insert(firstLine='124 Great Road', postcode='JG6 3TR')

john.relate(friendlyPlace)
owen.relate(greatRoad)
crazyGardens.relate(john)

print john['addresses'].keys()
for address in john['addresses'].values():
   print address['postcode']

print greatRoad['people'].keys()
print owen['addresses']['JG6 3TR']['people'].keys()

john['addresses']['MK4 1AB']['firstLine'] = 'The Cottage, 12 Friendly Place'
print database['Person']['John']['addresses']['MK4 1AB']['firstLine']

connection.close() # Close the connection without saving changes