from sqlalchemy import *
from sqlalchemy.orm import *
from datetime import datetime

engine = create_engine("sqlite://", echo=True)
meta = MetaData(engine)
Session = sessionmaker(bind=engine)
session = Session()

# Tables
channel_table = Table('channels', meta,
    Column('id', Integer, primary_key=True),
    Column('name', Unicode(30), nullable=False, unique=True),
    Column('description', Unicode(50)),
    Column('id_playlist', None, ForeignKey('playlists.id')),
)

playlist_table = Table('playlists', meta,
    Column('id', Integer, primary_key=True),
    Column('type', Unicode(8), nullable=False), # Longest: len('playlist')
    Column('name', Unicode(30), nullable=False),
    Column('description', Unicode(40)),
    Column('version', DateTime, nullable=False, default=datetime.now),
)

playlist_channel_table = Table('playlist_channels', meta,
    Column('id', None, ForeignKey('playlists.id'), primary_key=True),
    Column('id_channel', None, ForeignKey('channels.id')),
)

# Classes
class Channel(object):
    def __repr__(self):
        return "<%s: %s>" % (self.__class__.__name__, self.name)

class Playlist(object):
    def __repr__(self):
        return "<%s: %s>" % (self.__class__.__name__, self.name)

class PlaylistChannel(Playlist):
    pass

# Mappers
channel_mapper = mapper(Channel, channel_table,
    properties = {
        "playlists":relation(PlaylistChannel, backref="channel"),
        "active":relation(PlaylistChannel),
    },
    order_by=channel_table.c.name,
)

playlist_mapper = mapper(Playlist, playlist_table, polymorphic_on=playlist_table.c.type, polymorphic_identity='playlist', order_by=playlist_table.c.name)
playlist_channel_mapper = mapper(PlaylistChannel, playlist_channel_table, inherits=Playlist, polymorphic_identity='channel')

meta.create_all()

chan = Channel()
chan.name = "Test TV"

playlist1 = PlaylistChannel()
playlist1.name = "1"
playlist2 = PlaylistChannel()
playlist2.name = "2"

chan.playlists.append(playlist1)
chan.playlists.append(playlist2)

session.save(chan)
session.save(playlist1)
session.save(playlist2)
session.flush()
session.expire(chan)

print chan.playlists # should return a list of playlist1 and playlist2
print chan.active # should return None, as no playlist has be set as active yet.

chan.active = playlist2

session.flush()
session.expire(chan)

print chan.active