# -*- coding: utf-8 -*-

from sqlalchemy import Table, Column, ForeignKey, Integer, String
from sqlalchemy import Text, MetaData, create_engine
from sqlalchemy.orm import relationship, mapperi
from sqlalchemy.orm import create_session, sessionmaker, scoped_session

engine = create_engine('sqlite:///test.db', echo=True)
metadata = MetaData()
metadata.bind = engine
engine.connect()
    
# M:N Relational Table.
sequence_sequenceitem = Table('sequence_sequenceitem', metadata,
                    Column('sequence_id', ForeignKey('sequence.id')),
                    Column('sequenceitem_id', Integer,
                           ForeignKey('sequenceitem.id')))

# Sequence-Item Table.
sequenceitem_table = Table('sequenceitem', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('data', String, nullable=False, unique=True))

# Sequence Table.
sequence_table = Table('sequence', metadata,
                      Column('id', Integer, primary_key=True),
                      Column('name', String, nullable=False))

class Sequence(object):
    def __init__(self, name):
        self.name = name
        # N SequenceItems instances.
        #sequenceitems = []

class SequenceItem(object):
    def __init__(self, data):
        self.data = data

# Mappers.
mapper(SequenceItem, sequenceitem_table)
sequence_map_properties = {'sequenceitems':relationship(SequenceItem,
                                     secondary=sequence_sequenceitem,
                                     backref='sequences')}
mapper(Sequence, sequence_table, properties=sequence_map_properties)


metadata.create_all()

# Set up the session
sm = sessionmaker(bind=engine, autoflush=True, autocommit=False,
                  expire_on_commit=True)
dbsession = scoped_session(sm)


# Test Code.
if __name__ == '__main__':

    si1 = SequenceItem("One")
    si2 = SequenceItem("Two")
    si3 = SequenceItem("Three")

    s = Sequence("123123")
    s.sequenceitems = [si1,si2,si3,si1,si2,si3]

    dbsession.add(si1)
    dbsession.add(si2)
    dbsession.add(si3)
    dbsession.add(s)
    # 6 Items,
    # sqlalchemy.engine.base.Engine.0x...bc50:
    # INSERT INTO sequence_sequenceitem (sequence_id, sequenceitem_id)i
    # VALUES (?, ?)
    # ((1, 1), (1, 1), (1, 3), (1, 3), (1, 2), (1, 2))
    print "** Number of sequenceitems before commit: %d" % len(s.sequenceitems)

    dbsession.commit()

    # 3 Items left.
    print "** Number of sequenceitems after commit: %d" % len(s.sequenceitems)

    s = dbsession.query(Sequence).filter_by(id=1).first()
    print "** List of sequenceitems: %s" % s.sequenceitems
    print "** Number of sequenceitems, fetched: %d" % len(s.sequenceitems)
    print "** Please delete test.db"