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
and what else can Home DB be of use
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 want to have some place to save status in case of shutdown , and a place to store default values
save sensor data…
Hi,
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.
Just my personal thought.
Install the Mapdb persistence
Follow the persistence docs on how to persist your items and restore on startup.
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.
Hi soory for late response, i was able to test this just now
steps:
Excpted: item to be on
Actual : item is null
what am i doing worng?
thanks!
Show us what you have done so far. Post all related configurations and error messages if you got any.
And there should be no need to reboot the whole machine. Restarting oh itself should be enough to test “restore on startup” functionality.
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.
Not reading the documentation!
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
OK, you found the important part in the mapDB doc - but it doesn’t tell you where to get help with that.
Try this
http://smarthomeblog.net/openhab-persistence-tutorial/
There are a few steps needed to get persistence working with OH. It does take a little bit of time to completely get your head around it though. So start by reviewing the information here; https://www.openhab.org/docs/configuration/persistence.html#persistence-configuration
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.
Regards,
Garry
As I promised here’s a very basic jdbc.persist configuration file.
Strategies {
default = everyUpdate
}
Items {
* : strategy = everyChange, restoreOnStartup
}
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
steps:
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
Cool, well done!
Please mark the tread as solved.
Thanks