I think getting this to work responsively with the SQL data source would be very tricky. There is no way to modify the select statement on the fly or force a poll conveniently. For these reasons, i would solve this with an SSH data source. To your other questions, H2 and MySQL JDBC drivers are supplied with Mango, but all that is required is that the JDBC jar be on the classpath somewhere, so you can add other JDBC drivers you may need to Mango/overrides/lib/
To use the SSH data source to perform this, I see the control as follows. Presumably the database has some kind of shell.
- RFID reader supplies value into a data point,
- Point link sets SSH point to the value of the card's key, SSH point is defined like...
queryable: true, query command: if [ ! -z $keyAuthorized ]; then echo true; else echo false; fi;
settable: true, set command: keyAuthorized=$(mysql --user=user --password=password db <<< "select key from authorizedKeys where key='VALUE'" | grep 'VALUE');
- Point link the SSH point to the relay control point, toggling it when true.
You may wish to create a user specifically for the SSH data source, such that it doesn't have Mango's privileges. This will mean adding an ssh key to that user's authorized ssh keys. If you wish to conceal your database user and password from being plaintext in a Mango export you could move that logic into a "validateKey" script you invoke, i.e.
Which will offer you a place to sanitize the SQL if you wish (i.e. keyProspect=$(echo $1 | grep -o -Pe "^[^ ]$") or some such). The whole idea here is that we'll set a variable if the key is true and read it in the same SSH channel. As written here by me, if the keyAuthorized variable is set to any output from the validateKey script/command, then it will return true. If nothing is output, then the point will be false.
Two extra considerations are that you probably don't need to utilize polling on the SSH data source, so you can set it's poll period very long. This datasource may be one we could consider making polling an option. The other is that perhaps you are not interested in the value 'false' being set via point link to the control point for your relay, in which case I would advise something like this for the step (3) point link:
if(source.value === false)
throw "Unauthorized key exception";
You can set the exception/error event level for Point Links on the system settings page, in the events section.