test_directory.py

"""
test bom structure, use sqlalchemy
"""

from flask.ext.sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)


class Directory(db.Model):
    __tablename__ = "directory"

    Id = db.Column(db.Integer, primary_key=True)
    Name = db.Column(db.String(10))
    Parents = db.relationship("ParentDirectory", backref="directory")


class ParentDirectory(db.Model):
    __tablename__ = "parent_directory"

    Id = db.Column(db.Integer, primary_key=True)
    Pid = db.Column("parent_id", db.Integer)
    Cid = db.Column("child_id", db.Integer, db.ForeignKey("directory.Id"))


if __name__ == "__main__":
    db.drop_all()
    db.create_all()

    A = Directory(Name="A")
    B = Directory(Name="B")
    C = Directory(Name="C")
    D = Directory(Name="D")
    E = Directory(Name="E")

    db.session.add(A)
    db.session.add(B)
    db.session.add(C)
    db.session.add(D)
    db.session.add(E)
    db.session.commit()

    #
    # make a structure as follow:
    # A and E are parents of B, B is the parent of C and D.
    #
    B.Parents.append(ParentDirectory(Pid=A.Id))
    B.Parents.append(ParentDirectory(Pid=E.Id))
    C.Parents.append(ParentDirectory(Pid=B.Id))
    D.Parents.append(ParentDirectory(Pid=B.Id))

    db.session.commit()

    ChildOfB = db.session.query(ParentDirectory) \
                 .filter(ParentDirectory.Pid == B.Id).all()
    for c in ChildOfB:
        print c.Cid     # 3 and 4