Keep data ordered in db by order number in Flask-sqlalchemy

Question

I have a db(sqlite) which contains firewall and policy rule definitions in it. And i need to keep each records ordered in their firewalls. My Firewall and PolicyRule models relationship is many-to-many. So i stored the rank(order number) in the association model. How can i keep them ordered even if i insert data middle of the records?

For example:

I get my records with order_by in this order:

1- Rule A 
2- Rule B 
3- Rule C

And then i want to add the Rule D between Rule B and Rule C. So my next query result must be like this:

1- Rule A
2- Rule B
3- Rule D
4- Rule C

I need to know rules exact order because of i apply them on iptables and iptables policys must be exact same order with users.

Here is my models:

class PolicyRule(db.Model):
    __tablename__ = 'policy_rule'
    id = db.Column(db.Integer(), primary_key=True)
    active = db.Column('is_active', db.Boolean(), nullable=False, server_default='1')
    name = db.Column(db.String(255, collation='NOCASE'), nullable=False, unique=True)
    rule_type = db.Column(db.String(255, collation='NOCASE'), nullable=False) # IPv4 or IPv6
    direction = db.Column(db.Text())
    action = db.Column(db.Text())
    comment = db.Column(db.Text())
    log = db.Column(db.Boolean(), nullable=False, server_default='1')
    firewalls = db.relationship("FwPolicyRules", back_populates="rule")

    # Foreign key assignments for relationships
    src_addr_id = db.Column(db.Integer(), db.ForeignKey(Address.id, ondelete='CASCADE'))
    dst_addr_id = db.Column(db.Integer(), db.ForeignKey(Address.id, ondelete='CASCADE'))
    src_service_id = db.Column(db.Integer(), db.ForeignKey(Service.id, ondelete='CASCADE'))
    dst_service_id = db.Column(db.Integer(), db.ForeignKey(Service.id, ondelete='CASCADE'))
    interface_id = db.Column(db.Integer(), db.ForeignKey(Interface.id, ondelete='CASCADE'))
    time_profile_id = db.Column(db.Integer(), db.ForeignKey(TimeProfile.id, ondelete='CASCADE'))
    
    # Relationship definitions for access the objects directly like "policy_rule.src_addr".
    src_addr = db.relationship("Address", foreign_keys=[src_addr_id], lazy='subquery', backref=db.backref("policy_src_addr", uselist=True))
    src_port = db.relationship("Service", foreign_keys=[src_service_id], lazy='subquery',backref=db.backref("policy_src_port", uselist=True))
    dst_addr = db.relationship("Address", foreign_keys=[dst_addr_id], lazy='subquery',backref=db.backref("policy_dst_addr", uselist=True))
    dst_port = db.relationship("Service", foreign_keys=[dst_service_id], lazy='subquery',backref=db.backref("policy_dst_port", uselist=True))
    interface = db.relationship("Interface", foreign_keys=[interface_id], lazy='subquery',backref=db.backref("policy_interface", uselist=True))
    time_profile = db.relationship("TimeProfile", foreign_keys=[time_profile_id], lazy='subquery',backref=db.backref("policy_time_profile", uselist=True))


class Firewall(db.Model):
    __tablename__ = 'firewall'
    id = db.Column(db.Integer(), primary_key=True)
    name = db.Column(db.String(255, collation='NOCASE'), nullable=False, unique=True)

    policy_rules = db.relationship("FwPolicyRules", back_populates="firewall", lazy='subquery', cascade="delete-orphan")
    nat_rules = db.relationship("FwNatRules", back_populates="firewall", lazy='subquery', cascade="delete-orphan")
    routing_rules = db.relationship("FwRoutingRules", back_populates="firewall", lazy='subquery', cascade="delete-orphan")

    interfaces = db.relationship('Interface', secondary=interfaces, lazy='subquery', backref=db.backref('used_firewalls', lazy=True, uselist=True))

class FwPolicyRules(db.Model):
    __tablename__ = 'fw_policy_rules'

    id = db.Column(db.Integer, primary_key=True)
    firewall_id = db.Column(db.Integer, db.ForeignKey('firewall.id', ondelete='cascade'))
    policy_rule_id = db.Column(db.Integer, db.ForeignKey('policy_rule.id', ondelete='cascade'))
    rank = db.Column(db.Integer, autoincrement=True)
    rule = db.relationship("PolicyRule", back_populates="firewalls", lazy='subquery')
    firewall = db.relationship("Firewall", back_populates="policy_rules", lazy='subquery')

EDIT: I think although of bunch of explanation no one was exactly understand my question. To be clear i want to many-to-many version of this: https://docs.sqlalchemy.org/en/13/orm/extensions/orderinglist.html

Not a formal answer, but the general pattern you follow with SQLite is to not worry about the internal order of your data in SQL. This is because, in general, there really isn't any internal order at all. Instead, if you want to view your data in a certain order, just use an appropriate ORDER BY clause. And, possibly consider tuning your query with indices which also can help the sorting step. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 24, 2020 at 6:03
Perhaps your "rank" could be a float instead of an integer, so that if you need to insert a new record in between two existing records, you can set it in between their "rank" values. — Blue Star
– Blue Star, Commented Aug 24, 2020 at 6:06

Zekeriya Akgül · Accepted Answer · 2020-08-26 13:12:11Z

Finally i found the solition

Here is the my solition with python way:

i imported the ordering list with this:

from sqlalchemy.ext.orderinglist import ordering_list

and then i modified my Firewall model and added the default order_by parameter. After that collection_class=ordering_list('rank') parameter did the trick.

class Firewall(db.Model):
__tablename__ = 'firewall'
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255, collation='NOCASE'), nullable=False, unique=True)

policy_rules = db.relationship("FwPolicyRules", back_populates="firewall", lazy='subquery', cascade="save-update, merge, delete, delete-orphan", order_by="FwPolicyRules.rank" ,collection_class=ordering_list('rank'))
nat_rules = db.relationship("FwNatRules", back_populates="firewall", lazy='subquery', cascade="save-update, merge, delete, delete-orphan", order_by="FwNatRules.rank" ,collection_class=ordering_list('rank'))
routing_rules = db.relationship("FwRoutingRules", back_populates="firewall", lazy='subquery', cascade="save-update, merge, delete, delete-orphan", order_by="FwRoutingRules.rank" ,collection_class=ordering_list('rank'))

interfaces = db.relationship('Interface', secondary=interfaces, lazy='subquery', backref=db.backref('used_firewalls', lazy=True, uselist=True))

and then i just add new rule to firewall like this:

db.session.autoflush = False # This is important because of "cascade = delete-orphan" parameter. Otherwise PolicyRule will be deleted before its added.
new_rule = PolicyRule(...)
a = FwPolicyRules(rule=new_rule)
fw.policy_rules.insert(rank, a) # Alternatively you can use "fw.policy_rules.append(a)" for auto add rule to the end
db.session.autoflush = True
db.session.commit()

for change existing rule's order i wrote a function:

def change_rule_rank(rule_rank, new_rank):
    db.session.autoflush = False
    rule_assoc = fw.policy_rules.pop(rule_rank)
    a = FwPolicyRules(rule=rule_assoc.rule)
    fw.policy_rules.insert(new_rank, a)
    db.session.autoflush = True
    db.session.commit()

Hope it helps someone.

Collectives™ on Stack Overflow

Keep data ordered in db by order number in Flask-sqlalchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related