import demo
from demo import Console

from sqlalchemy import create_engine
from sqlalchemy.orm import mapper, clear_mappers, relationship, backref
from sqlalchemy import text

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.orm import sessionmaker, joinedload

from sqlalchemy import select

from sqlalchemy import func
from sqlalchemy.orm import column_property


# connection strings are RFC-1738 style urls
# DIALECT+DRIVER://USERNAME:PASSWORD@HOST:PORT/DATABASE
# engine1 = create_engine('mysql://sa_test:@localhost/test')
# engine2 = create_engine('sqlite:///test_db.sqlite')

engine = create_engine('sqlite:///:memory:')

Session = sessionmaker(bind=engine)

# the object that stores sqlalchemy's understanding of the schema
metadata = MetaData()

# traditional way to define tables
users_table = Table('users', metadata,
     Column('id', Integer, primary_key=True),
     Column('name', String(255)),
     Column('fullname', String(255)),
     Column('password', String(255))
)

addresses_table = Table('addresses', metadata,
    Column('id', Integer, primary_key=True),
    Column('street', String(255)),
    Column('city', String(255)),
    Column('user_id', Integer, ForeignKey("users.id"), nullable=False)
)


demo.set_logging()

def step1():
    '''Create the defined tables from the metadata.'''
    print "Create the defined tables from the metadata."
    metadata.create_all(engine)


def step2():
    '''raw SQL example.'''
    print __doc__
    conn = engine.connect()
    results = conn.execute("SELECT date()")
    for row in results:
        print row
    conn.close()


def step3():
    '''
    Example of running SQL Expressions as well as a multi-insert.
    Populates users into the users table.
    '''
    # SQL Expression
    ##################################################################
    insert_query = users_table.insert()

    print "INSERT QUERY\n", "="*80
    print insert_query # note the bind parameters are in there, but won't show
    conn = engine.connect()
    result = conn.execute(insert_query,
            [{"name":"alice", "fullname":"alice jones", "password":"secret"},
             {"name":"bob", "fullname":"bob jones", "password":"bobcat"},
             {"name":"carly", "fullname":"carly carbottom", "password":"cars"},
             {"name":"dan", "fullname":"dan diggity", "password":"1234"}])


    address_insert_query = addresses_table.insert()
    result = conn.execute(address_insert_query,
            [{"street":"1 Sesame St", "city":"LA", "user_id":2}])
    print address_insert_query
    conn.close()


def step4():
    '''Example of using SQLAlchemy to handle bind parameters.'''
    # Letting SA handle bind paramters and SQL filtering
    # No Little Johnny Drop Tables
    ##################################################################
    conn = engine.connect()
    result = conn.execute(text("""SELECT name FROM users WHERE id=:user_id"""),
                            user_id=1)
    print result.fetchall()
    result.close()
    conn.close()



def step5():
    '''Build queries programatically using the Expression Language.'''
    #select([COLUMNS], WHERE)
    conn = engine.connect()
    select_query = select([users_table, addresses_table],
                          (users_table.c.id==addresses_table.c.id) &
                          (users_table.c.name=="bob"))

    print "The __str__ rep on the query returns what SA thinks the Query will be (internal format).\n"
    print select_query

    result = conn.execute(select_query)
    for row in result:
        print row

    result.close()
    conn.close()


# ORM examples
##################################
##################################
# "Standard" examples
##################################
class User(object):
    '''Example User object, note it's a 'regular' object. '''
    def __init__(self, name=None, fullname=None, password=None):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name,
                                            self.fullname,
                                            self.password)


class Address(object):
    def __init__(self, street, city):
        self.street = street
        self.city = city


def step6():
    '''
    Uses the SA mapper to connect the user table to the User class and the
    address table to the Address class. Adds a relationship between User
    and Address.
    '''
    # The mappers are defined here
    mapper(User, users_table)
    mapper(Address, addresses_table,
                properties={ 'user': relationship(User, backref="addresses")}
                )

    # create the ORM session
    session = Session()

    # query the session for a User
    u = session.query(User).filter_by(id=1).one()

    # u.name
    # u.fullname
    # lazy load ... the ORM will issue the address request lazily
    # u.addresses

    # the ORM allows you to specify things to eagerload if you know you'll
    # need them.
    u = session.query(User).filter_by(id=1).options(joinedload("addresses")).one()
    session.commit()


# Declarative example
# ActiveRecord like Declarative syntax
# more familliar for Django
######################################################################
######################################################################
######################################################################
import sqlalchemy.ext.declarative
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(bind=engine, metadata=metadata)


class NewsPaper(Base):
    __tablename__ = "newspapers"
    id = Column(Integer, nullable=False, primary_key=True)
    name = Column(String(255))
    def __repr__(self):
        return '''<name={0}>'''.format(self.name)


class Article(Base):
    __tablename__ = "articles"
    id = Column(Integer, nullable=False, primary_key=True)
    title = Column(String(255))
    newspaper_id =  Column(Integer, ForeignKey('newspapers.id'))
    newspaper = relationship(NewsPaper, backref=backref('articles') )

    def __repr__(self):
        return '''<title={0}>'''.format(self.title)


def step7():
    global session
    session = Session()
    session.query(NewsPaper).all()
    session.query(Article).all()

    article = Article(title="The Great Story")
    newspaper = NewsPaper(name="The Globe")

    newspaper.articles.append(article)

    session.add(newspaper)
    session.commit()

def step8():
    '''The ORM tracks changes.'''
    session = Session()
    user = session.query(User).filter(User.id==2).one()

    # now lets add a new address to this user
    user.addresses.append(Address(street="Boardwalk", city="Atlantic City"))

    # things to note,
    #    * the ORM tied the relationships together automatically
    #    * We didn't explicitly save the address...
    #    * The user is already tracked by the session so it knows to modify the user
    session.commit()


console = Console(locals=locals())
console.interact('Demo' )