Rtl_433: RFE: Native support for logging decoded data to sqlite

[dagbdagb]

Seeing how each device now has somewhat structured output with well-defined datatypes, would it make sense to extend the output options to sqlite?
Or would that be considered bloating rtl_433?

I am using Jim Midkiffs python script now. It certainly works, but requires modification so it fits the specific device.

Having a common database with individual tables for each devicetype could be one approach, and is certainly doable by rewriting Jim's script above. But having it logged natively by rtl_433 means that new devices are supported (for logging) out of the box. A potential drawback is that changing the output for a particular devicetype suddenly becomes more involved, if you still want to use the old db.

I do not claim the competence or experience to hack this. But someone else may, if the concept isn't nixed from the start.

[merbanan]

Would that add a dependency onto sqlite? I would accept this if the sqlite code is its own file and a compile time option. Can you describe the database model you propose ? I don't see how it could work.

[dagbdagb]

I am not a database guy. Nor someone who codes... regularly. You may now roll eyes.
Optional dependency is fine by me.

If files under src/devices/ held variables defining a tablename and the sql statement required to build the table for this particular type of device, then we keep the device specific sql gunk out of generic code. Generic code handles the main database creation and then executes whatever sql statements the individual decoders hands it, on startup.

rtl_433 does currently spit out a string identifying the type of device producing the decoded data. (True for all two(!) devicetypes I have.) Assuming they all do, it should be trivial to direct decoded data at the correct table.

If the json format is something that can be automatically "adapted" to sql, this may turn out even simpler.

[rct]

I feel this is the right type of thing for a wrapper to do rather than rtl_433 itself. Ideally we could build a collection of wrappers, contributed to the repository, that people are using for processing the output in ways that are useful to them.

There are tons of different ways to make the output useful. SQL for time series data doesn't seem terribly useful to me. Most of my stuff goes into RRDtool. If I started from scratch, I'd probably use something newer like Graphite. Though maybe I'd go the direct some others have gone and try to put the data in a lightweight pub-sub system like MQTT. Then just have whatever pubsub clients make sense.

However, I wouldn't want to introduce lots of compile time dependencies on rtl_433 for pumping data into the various systems. No need for the complexity. Wrappers can handle that with pipes just fine.

[dagbdagb]

I do not see a collection of wrappers as an ideal situation at all. Way to much reinvention of the wheel. That said, I am not opposed to RRD or MQTT. I just went with sqlite as that was what the sample script used. Which possibly demonstrates why a collection of wrappers is bad.

Better to have it done right from the outset. Makes for a much better chance of having a polished front-end for presenting data as well, if all devices can share the base of the front-end code. I am not suggesting that front-end code should live in rtl_433. Although that _is_ the case today.

A wrapper will never be much better than a webscraper.

IMHO, of course.

[zuckschwerdt]

I patched rtl_433 to broadcast the data over UDP. Relaying UDP to various backends is trivial and robust. Currently I'm collecting from three rtl_433 "probes" into a central Graphite and also some custom scripts to monitor heartbeat and exceptional events.
I'd don't like the idea of pipes and wrappers too much. Something like UDP will give you bus-like topology, decentralization and isolated fault-recovery (I use supervisord) for free.

[dagbdagb]

So of the 4 "subtasks" receive, decode, store, present, you propose to limit rtl_433 to the first two? That will certainly keep it slimmer.

But again, it will disconnect the storage process from the decode process, such that it occasionally will be out of sync w.r.t. what devices are supported.

Anyways, I believe this is beyond my resources and capabilities. Hope it offers food for thought for someone.

[merbanan]

Yes rtl_433 is only supposed to do the first 2. I want to add better ways to interface with rtl_433, like mqtt or something else that there is a suitable standard for. But I have not done any work in that direction so far. So far people have managed with scripts.

[matthewwall]

please do not make rtl_433 do anything other than spit out data; it should do one thing and do it well.

use other systems to process and store the data.

for example, weewx takes data from any source (typically hardware, but can be network or anything else), saves it to local database, then optionally generates reports and/or uploads to any number of services, from graphana to influx to weatherunderground to thingspeak. it does this with independent modules that are much easier to maintain than any monolithic rtl_433 could hope to do.

the weewx-sdr driver is the module that lets weewx get data from rtl-sdr.

https://github.com/matthewwall/weewx-sdr

so please do not push rtl_433 beyond its one thing.

[panarom]

@dagbdagb does it have to be sqlite? I'm currently dumping the JSON output directly into mongo, which requires no modification to rtl_433 (though I'll admit JSON support isn't fully available for every sensor)

[dagbdagb]

@panarom No. It does not have to be sqlite.
rrd would be nice, but may be considered yesterday's news by some.
Anything that automatically adapts to the structure of the output data and enables intelligent querying of the stored data would be ace.
If the output from rtl_433 isn't structured (json), for some devicetypes, I'd say it is a task for whoever feels like it. It should not impact a generic solution, which positively must have structured input.

How does your json2mongo-thing work?

[panarom]

@dagbdagb it's just a bash script, wrapping up the JSON output into a set of mongo save statements:
rtl_433 -F json -U -T 30 -R 53 -f 433765000 2>/dev/null |sed "s/^/db.environmental.save(JSON.parse('/" |sed "s/$/'))/"|mongo environmental
There may be a more efficient way to do it (e.g. passing the output first through uniq to remove duplicate ouptut)

[zerog2k]

+1 for leaving rtl_433 to be minimal, doing one (or two) things well, e.g. protocol decoding. Taco Bell programming.

Some related thoughts FWIW:
We had a similar discussion a year (or two?) ago about how to handling the disparate way the protocol decoders output data. Regardless of implemented under the hood, I think this is a good time to reiterate on having one (or two) standard output formats for machine readability, e.g. json (and maybe xml, tsv, csv, etc). Diving deeper, I do think this means that there needs to be some standardization on internal message format of protocol decoder output, such that the presentation layer can be separated from the decoding layers, i.e. you only need to worry about writing your protocol decoder for some new wireless device instead of worrying how to format the output. Now that rtl_433 seems to be handling all sorts of wireless devices from weather-ish stuff to keyfobs, such a message format would either have to be very generic, or have multiple general variants/classes of messages (one for weather devices, one for keyfobs, etc) and be extended frequently, which could get messy.
Of course doing such a thing would be a pretty significant re-factoring, but I really think this is the only way to keep rtl_433 sane and manageable as more and more devices are added.

The real issue here is the presentation and decoding logic are jumbled together, with no true standardization on output format. IMHO these need to be split out...
(I dont mean this to be a critique of anyone - I think it's a natural by-product of the organic growth of this wonderful tool.)

[vestom]

It is very easy to make rtl_433 output its data to mqtt using mosquitto:
$ rtl_433 -F json -U | mosquitto_pub -t home/rtl_433 -l

Instead of adding any new interfaces to rtl_433, I would suggest adding the above line to README and making it the recommended/official way of network enabling rtl_433 and interfacing to e.g. home automation program like OpenHAB or Home Assistant. That will make it feasible for these other programs to do plugins for rtl_433.

I am using it that way to interface to my own python code. It is easy to receive the mqtt data in python and then put it in SQL or whatever. (I could add a small sample program to /test if there is interest).

+1 to keep rtl_433 simple with only stdout interface. We just need to firm up on the JSON output.

[merbanan]

Making rtl_433 more easy to use without bloating it to much is what lies on the road map. Native logging to a database is not really in that scope though. Issue closed.