Regex rules engine based on SQL

Question

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

Answer 1

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

Answer 2

I figured this out on my own. I'll describe what I did to write the query myself. I don't have the query to paste here since I deleted the query because I didn't need it anymore, but if anyone needs it, message me and I'll re-write it.

I created two tables, Rule(Id, Name) and Parameter(Id, Key, Value, RuleId, Type).

Rule table is self-explanatory, the Parameter table has two types of parameters for each rule, a condition or an action. Each rule is expected to have an arbitrary number of conditions and actions. Condition parameters are expected to have regex expressions to cover an IP subnet, or hostname convention etc

Then I created an SQL query to get the total number of conditions for each rule, and an SQL query to get the total number of conditions that match provided input from users; For example, if the conditions include hostname, IP, building_name, I get a list of rules where these conditions match using regex_like, then I do a count on ruleId and join that with the previous query on count and ruleId. This gives me the list of ruleIds where all conditions are satisfied, from this I can query the parameter table to get the list of corresponding actions for that rule.