Rules getting additional data from db?

Setup, Configuration and Use Scripts & Rules
1

I am slowly adding more stuff to my HA system. Including more Battery powered devices. Nothing makes me want to jump out a window than something with a dead battery. So I have rules to alert me when battery levels get low. However, I don’t know what battery types are in what. I was wondering if I add a new table to the mysql db called “batterydevices” and put in the item id from the items table with a column for what kind of battery is in that device.

Is there a way to query that table in some way to pull the battery type for my alert/email?

2

I would just include the battery type in the label …
… although this doesn’t answer your question, sorry :innocent:

3

I was thinking about that. Or maybe even a String item associated with the device that statically holds the battery type.

4

Not really. The Persistence engine has a very limited interface for a reason, it lets us use such varied databases as MapDB, Mongo, db40, rrd4j, and “standard” SQL databases like MySQL. Consequently it has no ability to create custom SQL (or what these other DBs use as a query language) from within Rules.

If you can get the DB JDBC driver into your classpath you could set up your on DB connection and create the query from with your rules, but it will probably be far easier to just use a String Item.

NOTE: You will need to bootstrap the value in the String Item using a System started rule. However, if you use persistence with restoreOnStartup and a persistence engine that supports String Items (e.g. MapDB) you only have to do this one time.

5

Thanks! Maybe i will just create an additional items file called batterys.items and keep the string items in there. so as to not clutter.

6

Given this is going to be static information for display on your sitemap, you might consider just putting the data into an html file, put that file in your webapps folder and put it on your sitemap using a webview element.

7

Definitely doable, but I was hoping to include it in my notification email so it says

Wallswitch battery 15%, please replace 1 cr2032 battery.
Thermostat battery 5%, please replace 1 AA battery.

So if I put some static string items in I can probably getaway with this.

8

Hey Jason

You can store the battery types into a value at the top of your rule.
It looks like that you have to insert the information manually regardless where you store it.
You can add the information of your variables into your mail.

9

You know Michael… you are 100% correct. I literally never thought of just adding the battery types to variables. I could go even further and do 2 dimensional arrays for item:battery

sometimes the answer is way simpler than the engineers want it to be.

10

NOTE: Arrays and most especially multi-dimensional arrays are a huge pain in the Rules DSL. I personally never try to use them where I can help it. But you will need to use an ArrayList of ArrayLists to get a 2D array. And populating the this construction will be very very ugly.

For this, I would probably use a HashMap with the name of the light being the key and battery String as the value. It will be cleaner to set up and you don’t need to keep track of indices and the like. Here is an example adopted from the wiki:

var HashMap<String, String> batteries = 
        newLinkedHashMap(
            "Wallswitch" -> "cr2032",
            "Thermostat" -> "1 AA"
        )
1 Like
11

Even better. I am doing some updates to my system this evening. I will give this a try!

12

I set this up and it works like a champ!!!