from sqlalchemy import *
from sqlalchemy import __version__ as sa_version
from sqlalchemy.orm import *


# version check
if sa_version != 'svn':
    version_tuple = tuple(int(i) for i in sa_version.split('.'))
    if version_tuple < (0, 5, 2):
        raise Exception('Please upgrade to at least SA 0.5.2: easy_install -U sqlalchemy')


# engine, Session
engine = create_engine('sqlite:///:memory:')
metadata = MetaData()
metadata.bind = engine
Session = sessionmaker(bind=engine)


# table definitions
addresses = Table('email_addresses', metadata,
    Column('address_id', Integer, Sequence('address_id_seq', optional=True), primary_key=True),
    Column('address', String(40)),
)

users = Table('users', metadata,
    Column('user_id', Integer, Sequence('user_id_seq', optional=True), primary_key=True),
    Column('address_id', Integer, ForeignKey(addresses.c.address_id)),
    Column('name', String(40)),
    mysql_engine='innodb'
)

orders = Table('orders', metadata,
    Column('order_id', Integer, Sequence('order_id_seq', optional=True), primary_key=True),
    Column('user_id', Integer, ForeignKey(users.c.user_id)),
    Column('description', String(50)),
    Column('isopen', Integer, ColumnDefault(1)),
)

orderitems = Table('items', metadata,
    Column('item_id', INT, Sequence('items_id_seq', optional=True), primary_key=True),
    Column('order_id', INT, ForeignKey("orders")),
    Column('item_name', VARCHAR(50)),
)

keywords = Table('keywords', metadata,
    Column('keyword_id', Integer, Sequence('keyword_id_seq', optional=True), primary_key=True),
    Column('name', VARCHAR(50)),
)

itemkeywords = Table('itemkeywords', metadata,
    Column('item_id', INT, ForeignKey("items")),
    Column('keyword_id', INT, ForeignKey("keywords")),
)


# declare Base -- we add a __repr__ that prints Column values
from sqlalchemy.ext.declarative import declarative_base
class MyBase(object):
    def __repr__(self):
        L = []
        for k in self.__class__.__table__.c.keys():
            value = getattr(self, k, '')
            L.append("%s=%r" % (k, value))
        return '%s(%s)' % (self.__class__.__name__, ','.join(L))
Base = declarative_base(cls=MyBase)


# create, drop, data functions
def create():
    metadata.create_all()

def drop():
    metadata.drop_all()
        
def data():
    for t in reversed(metadata.sorted_tables):
        t.delete().execute()
    
    # with SQLITE, the OID column of a table defaults to the primary key, if it has one.
    # so to database-neutrally get rows back in "insert order" based on OID, we
    # have to also put the primary keys in order for the purpose of these tests
    addresses.insert().execute(
        dict(address_id = 1, user_id = 7, address = "jack@bean.com"),
        dict(address_id = 2, user_id = 8, address = "ed@wood.com"),
    )
    users.insert().execute(
        dict(user_id = 7, address_id = 1, name = 'jack'),
        dict(user_id = 8, address_id = 2, name = 'ed'),
        dict(user_id = 9, name = 'fred')
    )
    orders.insert().execute(
        dict(order_id = 1, user_id = 7, description = 'order 1', isopen=1),
        dict(order_id = 2, user_id = 9, description = 'order 2', isopen=0),
        dict(order_id = 3, user_id = 7, description = 'order 3', isopen=1),
        dict(order_id = 4, user_id = 9, description = 'order 4', isopen=1),
        dict(order_id = 5, user_id = 7, description = 'order 5', isopen=0)
    )
    orderitems.insert().execute(
        dict(item_id=1, order_id=2, item_name='item 1'),
        dict(item_id=2, order_id=2, item_name='item 2'),
        dict(item_id=3, order_id=3, item_name='item 3'),
        dict(item_id=4, order_id=3, item_name='item 4'),
        dict(item_id=5, order_id=3, item_name='item 5'),
    )
    keywords.insert().execute(
        dict(keyword_id=1, name='blue'),
        dict(keyword_id=2, name='red'),
        dict(keyword_id=3, name='green'),
        dict(keyword_id=4, name='big'),
        dict(keyword_id=5, name='small'),
        dict(keyword_id=6, name='round'),
        dict(keyword_id=7, name='square')
    )
    
    # this many-to-many table has the keywords inserted
    # in primary key order, to appease the unit tests.
    # this is because postgres, oracle, and sqlite all support 
    # true insert-order row id, but of course our pal MySQL does not,
    # so the best it can do is order by, well something, so there you go.
    itemkeywords.insert().execute(
        dict(keyword_id=2, item_id=1),
        dict(keyword_id=2, item_id=2),
        dict(keyword_id=4, item_id=1),
        dict(keyword_id=6, item_id=1),
        dict(keyword_id=5, item_id=2),
        dict(keyword_id=3, item_id=3),
        dict(keyword_id=4, item_id=3),
        dict(keyword_id=7, item_id=2),
        dict(keyword_id=6, item_id=3)
    )