from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref

Base = declarative_base()
metadata = Base.metadata

class Hash(Base):
    __tablename__ = "hashes"

    hash_id = Column(Integer, primary_key = True)
    hash = Column(Text(24), nullable = False)
    files = relationship("File", backref = backref("hash"), cascade="all")

    def __init__(self, hash):
        self.hash = hash

class File(Base):
    __tablename__ = "files"

    file_id = Column(Integer, primary_key = True)
    path = Column(Text(), nullable = False)
    name = Column(Text(), nullable = False)
    ext = Column(Text(), nullable = False)

    hash_id = Column(Integer, ForeignKey("hashes.hash_id"))
    #hash = relationship(Hash, backref = backref("files"), cascade="all, delete, delete-orphan")

    def __init__(self, path, name, ext):
        self.path = path
        self.name = name
        self.ext = ext



from sqlalchemy.interfaces import PoolListener
class SetTextFactory(PoolListener):
    def connect(self, dbapi_con, con_record):
        dbapi_con.text_factory = str
engine = create_engine('sqlite:///wpltester.sqlite', echo=True, listeners=[SetTextFactory()])
engine.connect().connection.connection.text_factory = str

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

metadata.create_all(engine)

hash = Hash("0123456789ABCDEF")

file = File("/gloubi", "boulga.mp4", ".mp4")
file.hash = hash
session.add(file)

session.commit()

session.query(Hash).delete()
session.commit()

print session.query(File).all()