I’m new to IoTaWatt and I’m still figuring out what the circuits in my panel actually power, so I’ve been renaming inputs and moving CTs between circuits to find the ones I want to monitor. That churn has affected a number of my inputs such that they now monitor something else, or perhaps data collected under one input now actually belongs under another (because I changed the CT while playing around).
Does the API let me move data between inputs?
If I can’t move it, does the API let me delete data before a certain date/time?
If the API doesn’t let me do any of this, can I pull the database off the unit, dump the database to a flat file, use a text editor or Perl script to make the changes I need, reload the database from the repaired file, and then put it back in the unit?
If I can’t do any of the above, is there a “factory reset - ALL CLEAR” button that starts everything from scratch?
The datalog records the cumulative data of each of the 15 inputs every 5 seconds. It does not record the name assigned to the input at any particular time. When queries or status requests are made through the various APIs, the names are resolved to inputs based on the current inputs configuration.
Outputs, which include any script based calculated values, are resolved using the current script associated with the name. Scripts do not use names to reference inputs. They use the input numbers as defined at the time the script was last edited., and when editing, they convert those input numbers to the current name associated with any inputs.
It’s a very simple system, as keeping track of the historical names associated with each input would be very difficult given the limited resources of the ESP8266.
There is no factory reset per-se, but you can wipe the datalogs using the browser URL
I’m not asking for a historical record of input names in this thread. (I made a comment in another thread about the goodness of immediately propagating name changes into saved graphs so that the graphs stay valid.)
Here I’m asking: rather than wiping all my data just because I moved some CTs around on some inputs and the numbers no longer match up, please help me understand where the data is being kept (what are the file names) and what the data format is, so that I might dump and edit that data to renumber some old data to match the new input numbers, and also delete the old data before the time(s) that no longer match(es) the new input(s). (Being able to delete all data for a single input before a given time strikes me as a generally useful API function, and a good alternative to the drastic “delete all data for all inputs”.)
And perhaps most importantly: If I can dump and repair the database(s), can I safely replace the existing database(s) with the fixed one(s) while the system is running? Does the unit keep in-memory cached information that would conflict with having the database(s) changed while the system is running (which means that unless the unit can be booted in “single-user” or "maintenance’ mode, I can’t safely replace existing databases with fixed ones, so I shouldn’t bother trying to save my old data).
(I am truly delighted with the graphing display in IoTaWatt. My own graphing (gnuplot) is not nearly as nice and responsive, but I need to be able to overlay temperature and thermostat setpoint data on my energy use. I wish I could import my data into your wonderful graph rather than export the energy use into gnuplot!)
You seem to be operating under the misconception that everything is a simple matter of programming. I wish it were so, but reality is not always convenient.
The ESP8266 is a pretty nice little 32-bit machine, but it has limitations. As the engine driving IoTaWatt, you have to keep in mind that its primary job is sampling the inputs and generating the data. That consumes 2/3 of the available processor time. Moreover, the time is divided between sampling for 16.6ms and everything else 8.3ms (20ms/10ms @50 Hz).
In that series of 8.3ms slices, the IoTaWatt processes the sampling data, records the data in the datalog, maintains the history log and any integrators, services requests via the web-server, uploads data to external databases, provides data for the status display, checks for updates to the clock and firmware. It also accepts, verifies and installs configuration changes seamlessly on the fly.
Trust me when I say that adding the features you would expect in an industrial strength database are a bridge too far.
That’s why IoTaWatt makes it easy to upload your data to a couple of very good, free and open source databases. You can upload the raw data to them and subsequently enjoy all of the things you are asking for as those databases can be hosted on a processor with plenty of CPU, memory and fast reliable external storage.
If you want to know more about how IoTaWatt manages to keep up to 10 years of data at high resolution on an 8GB SD card and how it can retrieve that data in various metrics in a few seconds, you can look at the source on Github.
I do think that Graph+ is a very easy intuitive way to view the data, and is quite a bit easier to use than Grafana for what it does. If your only ask is to get external data into those graphs from another source, the approach I’d explore would be to modify Graph+ itself to do it directly from the other source. It would be fairly easy to define some new units and data sources and have the javascript/browser query that data using whatever API is available. If the IoTaWatt query is executed first, the response will provide the begin and end times as well as the intervals to be used in the additional query.
I’m not insisting that IoTaWatt have the tools to renumber and delete inputs; I’m asking: If I can dump and repair the database(s) [on my own machine], can I safely replace the existing database(s) with the fixed one(s) while the system is running? Is it safe to swap databases on a running system?
(I am truly delighted with the graphing display in IoTaWatt. My own graphing (gnuplot) is not nearly as nice and responsive, but I need to be able to overlay temperature and thermostat setpoint data on my energy use. I wish I could import my data into your wonderful graph rather than export the energy use into gnuplot!)
Finally, a requirement of what you are trying to achieve. This is EXACTLY why I use and recommend InfluxDB and Grafana. The combination will give you exactly what you want with excellent control over that data and the look and feel of it. Grafana makes beautiful looking graphs fairly easily. It has a huge amount of flexibility to pull from different data sources. It works well on both desktop and mobile (a few issues). I have been using them for many years and they have both had significant upgrades over the years, but the basics have stayed pretty much the same.
Grafana has some well defined time periods for graphs that make it easy to get to the interesting parts. Years ago I submitted a PR to add something like that to the original graph. Bob expanded on that when he made the newer Graph+. Grafana doesn’t have the zoom in/out as buttons, but can zoom in on desktop with a mouse. Not sure how to do that on mobile.
Like every tool, there is a learning curve and you still need to think about how you want to visualize the data. Grafana gives you lots of choices, which can be overwhelming.
Then I do have a “simple matter of programming” suggestion. When the system starts up, before it opens the database or any file that can’t be swapped out live during runtime, swap in the new file(s) before things get going:
FOR file IN ( database1 database2 config1 config2 etc ) DO
IF file.new exists and is not empty THEN
previous=file.previous # or use the current date file.YYYYMMDDHHMMSS
rename file to previous
rename file.new to file
log "New 'file' installed. Old one is 'previous'"
ENDIF
ENDFOR
That would make what I want to do (database repair) at least possible. If you think this would be useful, I could look up trying to code it and send you the code. (I’m a sysadmin by trade, not a programmer, but I’ll do my best!)
I also said I already have a basic graphing infrastructure set up. I want to import the new IoTaWatt data into my existing system, not install a bunch of new databases and packages and dependencies and then have to port my whole existing system to use the new stuff that I then have to learn. Yes, I may make that leap some day, but not today. All I want is the data out of IoTaWatt as efficiently as possible.
I’ve used the query API via wget to overlay IoTaWatt data records onto my existing graphs. My next step is to figure out one of the Data Uploader protocols so that I can keep my own historical log and use the data from that without loading down IoTaWatt with a query on every graph.
As Bob said, if you want to do it YOUR way, you can. That is the beauty of open source. If you want somebody else to do it your way, you need to figure out how to get them to see the brilliance of your way. The best way I have found to do that is to show them, by doing it and then demonstrating the brilliance of it. Sometimes it works. Sometimes only I see my own brilliance, others just think I am nuts.
Just curious but what’s so special about your setup that you can’t afford a few minutes downtime? Why not just unplug the iotawatt, pull the sd card and make a backup. Make 2 backups in case you mess up, then work on one of the backups until you get the data how you want it. Then, swap the files, and plugin it back in. Seems simple enough. Or make the backup and plug it back in right away. Take your time to write your script, then grab a fresh backup to run your script on before swapping the files.
Given that the databases can’t be edited in real time, I’m always going to have a few minutes of downtime between when I download them, fix them to have the right input numbers, then upoad them and reboot the device to install the new copies, but I can do all that from the comfort of my second floor office. Since I can script the fixes, the downtime will be fairly short, much shorter than going downstairs to the basement, unscrewing the unit from the wall, taking it apart to pull out the SD card, etc., then putting it all back together again, knowing that I’ll have to do this all over again the next time I want to edit something. I’m a software guy; I’m much more likely to do something if it can all be done in software and doesn’t require a trip to the basement with a screwdriver. If Bob asks for the code that enables a database swap on boot, I’ll look into it.
I understand your problem/concern. I actually agree it would be nice to be able to make changes easily like you suggested. But, this is typically only something that gets done before one settles on the set up. Once you have the channels fixed(no longer changing them regularly) the need to update the database of channel names is no longer an issue.
This is part of the reason that I have NodeRED in-between my other devices and InfluxDB. It gives me the ability to make changes to the names on the fly.
I think you will likely do better to put some thought into which circuits you want to monitor and not worry about the data that has gone by. In a short while all the data you have collected (up to now) will be of very little value, so deleting all the old data is not really such an imposition. You can also play with stopping upload and renaming things and resetting the time to start uploading to now and restart upload.
Oh wait, you can’t do that, because you aren’t using an uploader. There are multiple tools because while both a scalpel and a bone saw cut things, they aren’t really replacements for each other and bad things can happen if you use one to do the other’s job.
But, I really do understand the desire to not lose any data as irrational as that probably sounds to many people.
I didn’t realize the log/database files were accessible from the web interface. I’m still working on fixing all the firefox issues because all the javascript is broken. But if those files are accessible from the web interface, they probably shouldn’t be. Assuming the firmware keeps those files open while it’s running, there’s a very high chance of corrupting them. So you should probably still shut it down to make your backup.
Only if you write to them. Since the files can’t be edited in-place, any edit operation will involve downloading the database, dumping the downloaded database into a serial format flat file, at which point any incomplete records can be detected and discarded. Then I can process the flat file into another flat file to renumber things and discard or fix old data, then pack the new flat file back into database format and upload it. There are a few places where sanity checks can be made to avoid uploading a corrupt database. (All this is conjecture because I haven’t looked at the format of the database.)
But none of this is even possible unless the unit is modified to swap in new database files on boot, or (better) have a way to boot the unit into “single-user” or “maintenance” mode where the file editor is running but not any of the data capture, so the database files are not being used and can be replaced safely. If Bob doesn’t want to make any sort of change to the supported firmware to enable database replacement, all this is moot.
I’m not thinking of forking the code (yet), but I wonder if I could trim the code down to create a little single-user interface (no data collection; just the web editor) that would let me do my database changes safely, then after the edits I could re-load the stock firmware. That might be a fun project.
I don’t need to edit the names in the database; names aren’t even recorded in the database. I need to make the following kind of edits to make the IoTaWatt database consistent with my current CTs:
Fix the input numbers on some early records so that they match the current input numbers.
Modify some early data where I mistakenly had “double” set on an input.
Delete some old data where I moved the CT to a different circuit and no longer collect that data.
The above churn is because I’m still learning about how to best use this great product, but I don’t want to hesitate to make changes in future (because making any changes would mean throwing away all my historical data). I want the ability to fix the historical data now and in future so that I can be fearless in making changes to my configuration.
I would use an uploader in an instant, if it were as simple as the HubitatMaker API uploader, which only required me to type in one URL and two lines of shell script at that URL to append the uploaded JSON records to a file. Alas, with IoTaWatt I have to decode one of the three Data Uploader protocols to do the same thing. It’s on the todo list.
Indeed. I want the freedom to change my configuration in future without losing what I have. “Delete the databases and start over” is far too blunt a tool.
You said you’re a “software guy” right? The code is available on github. Maybe you could add a “maintenance mode” that keeps the webserver running but disables all sensor readings, closes all database files, and releases any file locks. This would allow you to remotely download a clean copy as well as overwrite the original once you are done. If it’s useful to others then maybe you can submit a pull request once you’re done. I’m a software guy too. Actually a 20 year software engineer. I’d write it but I don’t really have a use for it. However, I’m always willing to help out other programmers and answer questions if i can. My expertise is web and mobile apps but i’ve done a bit of everything over the years.
It sounds like you have figured out a path that will work for you. There are many paths that can work. Having years of experience with Iotawatt, I will say you will want more than it provides natively after you use it awhile. That is not meant as a negative for Iotawatt, but rather that once you really see the value of having great visualizations, what Iotawatt can do will seem limited. But, doing more requires more resources than an esp8266 and the investment in learning the other tools. Both InfluxDB/Grafana and emoncms provide the ability to aggregate and visualize data from many sources. They are generally only limited by your imagination.
Finally, trusting an SD card to hold all your data is probably not so great. I have mine on a mini-UPS so they are much less likely to have an issue. Prior to that I had some corruption issues, so it can happen.