[SOLVED] Trying to create Database for Home use


(Gad Ofir) #1

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


(Vincent Regaud) #2

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


(Gad Ofir) #3

I want to have some place to save status in case of shutdown , and a place to store default values
save sensor data…


(Daniel Calvo Gonzalez) #4

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.


(Vincent Regaud) #5

Install the Mapdb persistence
Follow the persistence docs on how to persist your items and restore on startup.


(Gad Ofir) #6

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


(Jürgen Baginski) #7

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!


(Daniel Calvo Gonzalez) #8

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.


(Gad Ofir) #9

Hi soory for late response, i was able to test this just now

steps:

  1. installed mapdb Persistence
  2. in config > Persistence > defined mapdb as Default Service
  3. set an item as on for testing
  4. rebooted machine

Excpted: item to be on

Actual : item is null

what am i doing worng?
thanks!


(Miika Jukka) #10

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.


(Rossko57) #11

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.


(Jürgen Baginski) #12

Not reading the documentation!


(Gad Ofir) #13

ok read the docs again :slight_smile: 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


(Rossko57) #14

OK, you found the important part in the mapDB doc - but it doesn’t tell you where to get help with that.
Try this


(Garry McCandless) #15

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


(Garry McCandless) #16

As I promised here’s a very basic jdbc.persist configuration file.

Strategies {
  default = everyUpdate
}

Items {
  * : strategy = everyChange, restoreOnStartup
}

(zolakk) #17

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


(Gad Ofir) #18

steps:

  1. installed mapdb Persistence
    1. 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**
  2. 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 :slight_smile:


(Vincent Regaud) #19

Cool, well done!
Please mark the tread as solved.
Thanks