mercredi 29 juillet 2015

a way of limiting the number of records in a sqlite table

There have been some other posts on this subject, but I am not trying do something like "storing only the last 500 entries in an event log".

I have a system that is configured via an sqlite database (my user config file is a database). Some other user/system will load a sqlite database file into my system, and based on that file my system will configure its self. But I'd like to build some rules into the db its self to safe guard against things being configured incorrectly.

I have a table to store my firewall rules, it looks like this:

 CREATE TABLE firewall_rules (
    ip_addr_octet1  INTEGER NOT NULL,
    ip_addr_octet2  INTEGER NOT NULL,
    ip_addr_octet3  INTEGER NOT NULL,
    ip_addr_octet4  INTEGER NOT NULL,
    net_mask_octet1 INTEGER NOT NULL,
    net_mask_octet2 INTEGER NOT NULL,
    net_mask_octet3 INTEGER NOT NULL,
    net_mask_octet4 INTEGER NOT NULL,
    CONSTRAINT unique_ip UNIQUE(ip_addr_octet1, ip_addr_octet2, ip_addr_octet3, ip_addr_octet4)
    )

I am only able to use 3 firewall rules. That is an arbitrary limit I can't change. But I do have some control to specify how these database tables are created.

So here is what I am thinking:

CREATE TABLE firewall_rules (
    rule_number     INTEGER NOT NULL CHECK(rule_number < 4) PRIMARY KEY AUTOINCREMENT,
    ip_addr_octet1  INTEGER NOT NULL,
    ip_addr_octet2  INTEGER NOT NULL,
    ip_addr_octet3  INTEGER NOT NULL,
    ip_addr_octet4  INTEGER NOT NULL,
    net_mask_octet1 INTEGER NOT NULL,
    net_mask_octet2 INTEGER NOT NULL,
    net_mask_octet3 INTEGER NOT NULL,
    net_mask_octet4 INTEGER NOT NULL,
    CONSTRAINT unique_ip UNIQUE(ip_addr_octet1, ip_addr_octet2, ip_addr_octet3, ip_addr_octet4)
)

When I read from the db I will just do this:

select * from firewall_rules where (rule_number < 4)  AND (rule_number != 0) order by rule_number ASC

I think this will guarantee that I will always get at most 3 rows returned and whoever is building the database adding records to the database will get some early feedback about any potential issues.

Is there anything wrong with this approach that I am missing? Or is there some better way to do what I am trying to do?

Aucun commentaire:

Enregistrer un commentaire