Regex rules engine based on SQL

database-recommendationh2

I have a table which I use to determine actions based on conditions provided. This is what it looks like right now.

IPAddress, MACAddress, Hostname, ScriptName, Argument1, Argument2, Argument3
10.2.1.* ,  .*       , .*      , diagnostics.sh, -runAll,        ,

In this table, the first three columns are the conditions, and the last four columns are the actions. Values of the condition cells are regex values that I do REGEX_LIKE on, so when I am looking up the correct script to run on a device, I look at it's IP (or MAC I'm given one or the other, not both) and hostname and if the regex matches, then I get the script name that needs to be run and arguments for that script.

Some scripts have one argument, some have two and others have three.

How do I modify this so I can an arbitrary list of conditions and actions. e.g. I am given a Key, Value map (attribute name, attribute value) for conditions, and once a rule has been satisfied, I retrieve a list of action Key, Value elements that are relevant for that device.

To keep things simple, there won't be any updates done to this database once it is initialized. It's initialized on start up by reading rules from a flat file, and the used at runtime to determine rules that have been satisfied for provided input.

This is the schema I think is better suited for this, but I don't know if this is correct and I don't know how to get the matching rules either.

Table Name: Column List
Attributes: attributeId, name, value
Conditions: conditionId, ruleId, attributeId
Actions   : actionId, ruleId, attributeId
Rules     : ruleId

Best Answer

PostgreSQL

I'm not sure why you would want to use regexes for this. PostgreSQL already has specific types.

CREATE TABLE whatev (
  IPAddress  inet,
  MACAddress macaddr,
  Hostname   text,
  ScriptName text,
  CHECK ( IPAddress IS NULL OR MACAddress IS NULL )
);

INSERT INTO whatev
VALUES ( '10.2.1/24', null, 'Evan Box', 'rmrf' );

Don't store ip's, netmasks, or macaddresses as text.

SELECT * FROM whatev
WHERE ipaddress >> '10.2.1.4';

I don't much understand the rest of what you want to do. I would also point you to ip4r which is awesomely great at this.

As far as storing conditions I'm not entirely sure what you mean, but you can certainly store conditions as inet there are a rich set of operators for it

Related Question