Hi so after two years of running OH+Node-red,
I taking the step and trying to build a DB for personal use,
but I have no clue where to start, in an aspect of how to design such schema the correct way
i have started by installing mysql, and have it working as a server and a client on my OH machine
i know a little SQL and i can google the how-to, but the right approach is something that i can’t find
if someone can give me some example for a working DB
As previously stated by @vzorglub in order to save ALL item states for the next startup use MAPDB This database is exactly build for that, read the documentation and follow it, you do not need any sql knowledge at all!
There is nothing to worry about. You just create a database and let openhab knows the name. Openhab will try to access the db and will see there is no information so it will create all the needed tables for you with the right structure.
Hope this helps.
Maybe like me you need to be lead by example, as that usually puts things into context. I’m not able to access my OH configuration at the moment so limited in able to help, but will be able to offer my very simple setup later on when I’m home.
Like with everything in OH persistence is very flexible. First, choose the database engine you want to use. For me that’s MySQL and influxDb. I’m using Grafana to graph a few items and follow follow this thread. If you decide to use MySQL then use the JDBC Persistence for MySQL as this seams to be very stable in my setup.
For the persistence to work you will need to configure the connection string as per the details here. Make sure the user account you use to connect to MySQL has permissions to both create and update existing fields and columns. The lazy way being to provide the user with full access, which is find in a closed environment.
To make sure that you have everything in place I would open a second console and “tail -f” openhab.log. This way if someting hasn’t been configured correctly then you’ll get an error here and can capture it. Very useful for when answering further follow up questions.
Once you have your database in place and don’t have any connection errors it’s time to get your database populated. This is where the persistence/mysql.persist files comes into play. What you need to do is save every change and restore that on a restart. This section will help you.
I hope that provides you with some direction and will update later with better real world examples from my setup.