Add keyword filters for push notifications

[DanrwAU]

Add the ability to only send push notifications on certain keywords for each alias.

Will require a DB Change.

[DanrwAU]

@davidmckenzie i sort of started this here https://github.com/DanrwAU/pagermon/commit/15e1a4da0bfff52388e1783cd01c8c354ad3e076 couldn't wrap my head around how to make the gui edit the database.

I'm fairly sure i can do the message processing during the post but the DB Stuff has me flumoxed. Any ideas? 😛

Ignore the messy commit - git mangled it from my dev box :|

[davidmckenzie]

Still haven't had a chance to look at this, but just going through old issues this is kinda similar to #34 - could potentially knock that one over at the same time

[DanrwAU]

My thoughts are a separate table with filters, linking to the capcode using foreign ID. then pull the filters during message processing for the applicable capcode. I don't quite have any sort of knowledge how to make it work, but i was gunna plug on and see if i could get it going. :-)

[davidmckenzie]

This is where I really wish we weren't using sqlite ;)

So for arguments sake, lets say you have 1000 aliases, and on all of those aliases you want to push if the keywords "firecall", "alert", or "end of the world" are present. So would you have 3000 rows in the keyword table with foreign keys to the alias?

The keywords themselves don't carry any additional metadata, right, just their existence is all that's needed? So perhaps a better option would be to store it as a JSON style array on the alias, stored in the db as a string.

If we want to get really fancy and forwards-compatible, we could add a "metadata" column to the capcodes table, then store a JSON formatted object in there. That column could be used for storing any data that would never ever be needed to be queried directly (i.e. you're never going to want to do a select on all aliases that contain a certain keyword match, right?)

An example of this sort of thing is in the sessions table I was just looking at:

sqlite> select * from sessions;
sid|expired|sess
l-snip|1542007407017.0|{"cookie":{"originalMaxAge":604800000,"expires":"2018-11-12T07:23:27.017Z","httpOnly":true,"path":"/"}}
sqlite> .schema sessions
CREATE TABLE sessions (sid PRIMARY KEY, expired, sess);

[DanrwAU]

This is why i'm in Ops and not Dev.... lol.

So for arguments sake, lets say you have 1000 aliases, and on all of those aliases you want to push if the keywords "firecall", "alert", or "end of the world" are present. So would you have 3000 rows in the keyword table with foreign keys to the alias?

I wasn't considering someone may be crazy enough to try and push the same keyword on 1000 capcodes 😆. But yeah that was the general idea. 😭.

The keywords themselves don't carry any additional metadata, right, just their existence is all that's needed? So perhaps a better option would be to store it as a JSON style array on the alias, stored in the db as a string.

I wouldn't think so, my original end goal was to possibly have the priority/sound set differently for keywords, but hadn't got that far down the rabbit hole.

If we want to get really fancy and forwards-compatible, we could add a "metadata" column to the capcodes table, then store a JSON formatted object in there. That column could be used for storing any data that would never ever be needed to be queried directly (i.e. you're never going to want to do a select on all aliases that contain a certain keyword match, right?)

The only use case i could see for querying them all at once, is some sort of admin page. An example right now is you can't see the pushover settings for all aliases, you need to do a DB query or check them individually. Not sure if that is something that would ever be required, but it may be handy for someone with alot of capcodes or filters 🤷‍♂️

[davidmckenzie]

The only use case i could see for querying them all at once, is some sort of admin page. An example right now is you can't see the pushover settings for all aliases, you need to do a DB query or check them individually. Not sure if that is something that would ever be required, but it may be handy for someone with alot of capcodes or filters 🤷‍♂️

Right, yep that makes sense. So storing it as an array in a string column probably wouldn't be too bad, cos you could then still sort of look it up, like so:

> SELECT filters FROM capcodes WHERE filters LIKE '%dong%';
['thing','other thing','dongles']

Not the nicest, but pretty much the only way without going down the path of join query hell, and means you wouldn't have to add any extra queries on to anything.

Alternatively if you did want to have different priorities or sounds based on the filter, you could do it as an array of objects, but then you lose your ability to query accurately:

> SELECT filters FROM capcodes WHERE filters LIKE '%dong%';
[{name:'thing',priority:'bigly'},{name:'other thing',priority:'bigly'},{name:'dongles',priority:'less bigly'}]

This would also match searches on the keys, not just the values, so could be dodgy:

> SELECT filters FROM capcodes WHERE filters LIKE '%name%';
[{name:'thing',priority:'bigly'},{name:'other thing',priority:'bigly'},{name:'dongles',priority:'less bigly'}]

[DanrwAU]

Thanks,

Looks like i've bitten off more than i can chew, but i have 2 1/2 more weeks of paternity leave to figure it out lol!