from sqlalchemy import *

# 
# create our metadata and tables
# 

metadata = MetaData()

person_table = Table('person', metadata, 
    Column('id', Integer, primary_key=True),
    Column('name', Unicode),
    Column('email', Unicode),
    Column('password', Unicode)
)

article_table = Table('article', metadata,
    Column('id', Integer, primary_key=True),
    Column('title', Unicode),
    Column('description', Unicode),
    Column('content', Unicode),
    Column('author_id', Integer, ForeignKey(person_table.c.id))
)


#
# create our objects
#

class Person(object):
    def __init__(self, name=None, email=None, password=None):
        self.name = name
        self.email = email
        self.password = password

class Article(object):
    def __init__(self, title=None, description=None, content=None, author=None):
        self.title = title
        self.description = description
        self.content = content
        self.author = author


#
# create our mappers
#

mapper(Person, person_table, properties={
    'articles' : relation(Article)
})
mapper(Article, article_table, properties={
    'author' : relation(Person)
})


if __name__ == '__main__':
    metadata.bind = 'sqlite:///'
    metadata.create_all()
    
    jonathan = Person()
    jonathan.name = 'Jonathan'
    jonathan.email = 'jonathan-nospam@cleverdevil.org'
    jonathan.password = 's3cr3t'
    
    blog_post = Article()
    blog_post.title = 'Some Blog Post'
    blog_post.description = 'A blog post on some subject'
    blog_post.content = 'Draft content for blog post'
    blog_post.author = jonathan
    
    session = create_session()
    session.save(blog_post)
    session.flush()
    
    session = create_session()
    jonathan = session.query(Person).get_by(name='Jonathan')
    print jonathan.name
    print jonathan.articles[0].title
    print jonathan.articles[0].author.name