# Relationship graph:
#
# Key:
# MapperClass(sqltable[, join])
#   ----< Child of
#   *<--- Subclass of
#   >---< N-N Relationship
#
# Project(projects) ----< Node(nodes)
#                           *<---- Parameter(nodes, Node.c.type == 1)
#                           *<---- Algorithm(nodes, Node.c.type == 2)
from sqlalchemy import *

class kw_init(object):
    def __init__(self, **kwargs):
        for key, value in kwargs.iteritems():
            setattr(self, key, value)

class Project(kw_init):
    pass

class Node(kw_init):
    pass

class Parameter(Node):
    pass

class Algorithm(Node):
    pass

metadata = DynamicMetaData()

Project.table = Table('projects', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(40)))
Project.mapper = mapper(Project, Project.table)    

Node.table = Table('nodes', metadata,
    Column('project_id', Integer, ForeignKey('projects.id')),
    Column('id', Integer, primary_key=True),
    Column('type',Integer),
    Column('name', String(30)))
Node.mapper = mapper(Node, Node.table,
    polymorphic_on=Node.table.c.type)

Parameter.mapper = mapper(Parameter, 
    inherits=Node.mapper,
    polymorphic_identity=1)

Algorithm.mapper = mapper(Algorithm, 
    inherits=Node.mapper,
    polymorphic_identity=2)

Project.mapper.add_property('nodes', relation(Node,
    backref='project', cascade='all, delete-orphan'))
Project.mapper.add_property('algorithms', relation(Algorithm))
Project.mapper.add_property('parameters', relation(Algorithm))

engine = create_engine('sqlite:///:memory:')
metadata.connect(engine)
metadata.create_all()
session = create_session(bind_to = engine)

#Setup finished, begin example usage:
print 'Starting test...'
p = Project(name='test')

p.nodes.append(Parameter(name='p1'))
session.save(p)
session.flush()
print 'len(p.nodes):',len(p.nodes),'should be',1
print 'len(p.parameters):',len(p.parameters),'should be',1
print 'len(p.algorithms):',len(p.algorithms),'should be',0

p.parameters.append(Parameter(name='p2'))
session.flush()
print 'len(p.nodes):',len(p.nodes),'should be',2
print 'len(p.parameters):',len(p.parameters),'should be',2
print 'len(p.algorithms):',len(p.algorithms),'should be',0

del p.nodes[-1]
session.flush()
print 'len(p.nodes):',len(p.nodes),'should be',1
print 'len(p.parameters):',len(p.parameters),'should be',1
print 'len(p.algorithms):',len(p.algorithms),'should be',0

p.algorithms.append(Algorithm(name='a1'))
session.flush()
print 'len(p.nodes):',len(p.nodes),'should be',2
print 'len(p.parameters):',len(p.parameters),'should be',1
print 'len(p.algorithms):',len(p.algorithms),'should be',1