#!/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-query",
    autoCreate = 1,
)
cursor = connection.cursor()

import datetime

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

queryExample = web.database.object.Table('QueryExample')
queryExample.addColumn(web.database.object.Date(name="testDate"))
queryExample.addColumn(web.database.object.Integer(name="testInteger"))
queryExample.addColumn(web.database.object.Integer(name="testNumber"))
queryExample.addColumn(web.database.object.Email(name="email"))

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

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

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

database['QueryExample'].insert(
    testDate=datetime.date(2004,7,11),
    testInteger = 10,
    testNumber = 15,
    email = 'james@example.com'
)

where = database['Person'].column['firstName'] == "John"
print where

rows = database['Person'].select(where=where)
print rows

column = database['queryExample'].column

print column['testDate'] == datetime.date(2003,12,12)
print column['testInteger'] < 5

print column.AND(column['email'] == 'james@jimmyg.org', column['testInteger'] < 5)
print column.NOT(column['email'] == 'james@jimmyg.org')

print (column['email'] == 'james@jimmyg.org') & (column['testInteger'] < 5)
print ~(column['email'] == 'james@jimmyg.org')

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