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
What is your purpose?
OH already comes with persistence with a number of databases
The REST API can retrieve data from the database as well as the persistence methods for items
I have a similar scenario and I’m using mySQL (now Mariadb). It’s free, you can find very easily information about it in the webt and OH already includes the needed connection.
yes i am trying to understand what tables to create and what values to store and what is the best way
for example is it better to store as 1 and 0? or ON OFF? for performance lets say
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.
A missing step appears to configuring your new mapdb service to tell it
(a) which Items to persist, and when, and
(b) which Items to restore-on-startup.
ok read the docs again still not able to understand
missing steps i see
in the runtime cfg file i set mapdb as defult
org.eclipse.smarthome.persistence:default**=mapdb**
but i cannot understand where i do the item config
i cannot find the folder with the
All item and event related configuration is done in the file persistence/mapdb.persist.
in ubuntu its all spread out , so i am not even sure where to create it
and how to create it with correct syntex
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.
As far as the where to create it part, the persistence and all the other configuration folders should be under /etc/openhab2 on ubuntu if you installed with apt-get. That’s where mine is anyway
in config > Persistence > defined mapdb as Default Service
also in the runtime cfg file i set mapdb as defult
org.eclipse.smarthome.persistence:default**=mapdb**
created file with etc/openhab2/persistence/mapdb.persist
Strategies {
}
Items {
*: strategy = everyChange, restoreOnStartup
}
4. set an item as on for testing
5. rebooted the machine
Excepted and working: item on
thanks all i left steps for more noobs like me