from sqlalchemy import *
from sqlalchemy.ext.assignmapper import assign_mapper
from sqlalchemy.ext.sessioncontext import SessionContext

metadata = DynamicMetaData()
session_context = SessionContext(create_session)



articles = Table('articles', metadata,
	Column('id', Integer, primary_key=True),
	Column('topic', Unicode(256), nullable=False),
	Column('rank', Integer),
	Column('content', Unicode, nullable=False),
	mysql_engine='InnoDB'
)


articles_related_articles = Table('articles_related', metadata,
	Column('article_id', Integer, ForeignKey('articles.id'),
		index=True, nullable=False),
	Column('related_id', Integer, nullable=False),
	Column('bias', SmallInteger, default=0),
	mysql_engine='InnoDB'
)

class ArticleRelatedArticle(object):
	pass


class Article(object):
	pass

assign_mapper(session_context,ArticleRelatedArticle, articles_related_articles,
		primary_key=[articles_related_articles.c.article_id,
			articles_related_articles.c.related_id],
		properties={'article': relation(Article),}
	)
	
assign_mapper(session_context,Article, articles,
	properties={'related': relation(ArticleRelatedArticle, cascade="all, delete-orphan", lazy=True)},
)

if __name__ == '__main__':
	
	engine = create_engine("mysql://localhost/bench",pool_recycle=60)
	metadata.connect(engine)

	# Careful !!!  Delete tables' content
	articles_related_articles.delete().execute()
	articles.delete().execute()

	# create some
	a = Article(topic='blabla',content='oula')
	b = Article(topic='arf',content='marfmarf')
	r1 = ArticleRelatedArticle(article_id=a.id, related_id=b.id)
	r2 = ArticleRelatedArticle(article_id=b.id, related_id=a.id)
	a.related.append(r1)
	b.related.append(r2)
	session_context.current.flush()

	# clear session so start from scratch
	session_context.current.clear()

	# then delete
	c = Article.get_by(topic='blabla')	# <=> a
	c.delete()
	session_context.current.flush()
	d = Article.get_by(topic='arf')	# <=> b
	d.delete()
	d.flush()