[Solved] How to add data to domoticz.db

Moderator: leecollings

Post Reply
BartSr
Posts: 365
Joined: Sunday 03 July 2016 16:16
Target OS: Raspberry Pi / ODroid
Domoticz version: V2024.7
Location: Netherlands
Contact:

[Solved] How to add data to domoticz.db

Post by BartSr »

Hi!
I am aware of adding data to the database might be tricky.
Having said this here is my case.
I am using Domoticz for over 6 years.
I used to now and then make backups from database.
In the beginning it happened some times that Domoticz crashed. I then started from scratch with new database.
Now I have a healthy running system with valid data from about 4-5 years.
My goal is to update the current database with old data.
Idea: create csv or other format file from old database and ADD that data to correct table (presume mostly xxxx_calendar). I have some knowledge of SQL from the days I programmed Visual Foxpro.
By the way does anybody knows what happens if data is included twice in a table?

Thanks for your thoughts.
Bart
Last edited by BartSr on Monday 22 January 2024 14:10, edited 2 times in total.
Raspberry pi 3b
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
skippy711
Posts: 32
Joined: Friday 20 January 2017 17:52
Target OS: NAS (Synology & others)
Domoticz version: 2024.2
Location: Netherlands
Contact:

Re: How to add data to domoticz.db

Post by skippy711 »

In your graphs you will see 2 datapoints on a single date (assuming all old data matches the new database settings).
As you state is might be tricky...
If there is a mismatch you will flood your log with error messages that a certain line in the database is corrupt or cannot be read.
On the other hand: you can use that information to repair the database by removing that specific line. I did what you describe a couple of times (with DBbrowser for SQlite) and with a bit of patience and accurate working it should work.
Good luck
BartSr
Posts: 365
Joined: Sunday 03 July 2016 16:16
Target OS: Raspberry Pi / ODroid
Domoticz version: V2024.7
Location: Netherlands
Contact:

Re: How to add data to domoticz.db

Post by BartSr »

Thanks for your reply.
Can you share the way how you did the update?
Raspberry pi 3b
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
willemd
Posts: 628
Joined: Saturday 21 September 2019 17:55
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.1
Location: The Netherlands
Contact:

Re: How to add data to domoticz.db

Post by willemd »

If the format of the table has not changed in the meantime, it is quite easy to do with sqlite3 export and import of csv files. Google will show it.

By the way, the examples you will find usually put the "headers on" before export, but then you have to remove the first line before import, so don't do that.

Just shut down domoticz while you do this and make a backup first, in case you make errors.
BartSr
Posts: 365
Joined: Sunday 03 July 2016 16:16
Target OS: Raspberry Pi / ODroid
Domoticz version: V2024.7
Location: Netherlands
Contact:

Re: How to add data to domoticz.db

Post by BartSr »

Willemd,
thanks for your adivices.
I wonder when I export a table the table remains in the database I think.
Then I update the file and import again. Does this overwrite the table inside the db or just add it and if so what about table ID's? (auto inc value)
Bart
Raspberry pi 3b
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
willemd
Posts: 628
Joined: Saturday 21 September 2019 17:55
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.1
Location: The Netherlands
Contact:

Re: How to add data to domoticz.db

Post by willemd »

BartSr wrote: Sunday 21 January 2024 15:56 Willemd,
thanks for your adivices.
I wonder when I export a table the table remains in the database I think.
Then I update the file and import again. Does this overwrite the table inside the db or just add it and if so what about table ID's? (auto inc value)
Bart
Yes, if you export then the table remains intact. You are just creating an extract of the table (or part of it, depending on your selection) in a csv file.

If you import, then the rows are added to the existing table. Anything that is already in there remains intact.

You are exporting and importing without the table ID's. They are assigned during import/creation.

Of course the domoticz deviceRowID is part of the export/import, but not the automatically assigned rowid.
User avatar
Xenomes
Posts: 383
Joined: Tuesday 27 November 2018 19:05
Target OS: Linux
Domoticz version: 2024.7
Location: Netherlands
Contact:

Re: How to add data to domoticz.db

Post by Xenomes »

There is a tool to merge data from an old database to a new one. It is a command-line program. Always make a spare copy of your database before tinkering!
https://releases.domoticz.com/releases/ ... BMerger.7z

Code: Select all

--inputdb=inputdatabase.db --outputdb=outputdatabase.db <commands>
commands:
-----------------
--merge-rain            Merges the rain table
--merge-fan             Merges the fan table
--merge-meter           Merges the meter/multi-meter table
--merge-percentage      Merges the percentage table
--merge-temp            Merges the temperature table
--merge-uv              Merges the UV table
--merge-wind            Merges the wind table
--merge-all             Merges all tables

--transfer-old=idx --transfer-new=idx

--overwrite             Overwrites destination values(row) if exists (default false)
HP T630 (32GB SSD/8GB Mem) - Ubuntu 22.04.4 LTS (64Bit) - Domoticz 2024.7 with Machinon theme - RFLink - KaKu - Sonoff - Tasmota - Shelly - MQTT2Zigbee - OpenTherm Gateway - Tinytuya - IR Blaster - P1 Smart Meter - NPN Watermeter - Google Assistant
BartSr
Posts: 365
Joined: Sunday 03 July 2016 16:16
Target OS: Raspberry Pi / ODroid
Domoticz version: V2024.7
Location: Netherlands
Contact:

Re: How to add data to domoticz.db

Post by BartSr »

Thanks so far to all of you.

I updated a table in a test-environment.
It seems domoticz-protection has been extended as after I tried to start Domoticz with updated database I had to use credentials.
All of them which are surely correct are not accepted. Also starting domoticz from shortkey xxxx -nowwwpwd fail.
Any idea's / wotkarounds?

-Bart
Last edited by BartSr on Sunday 21 January 2024 21:34, edited 1 time in total.
Raspberry pi 3b
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
HvdW
Posts: 525
Joined: Sunday 01 November 2015 22:45
Target OS: Raspberry Pi / ODroid
Domoticz version: 2023.2
Location: Twente
Contact:

Re: How to add data to domoticz.db

Post by HvdW »

Did you check that domoticz.db is not root but pi and chown?
Bugs bug me.
willemd
Posts: 628
Joined: Saturday 21 September 2019 17:55
Target OS: Raspberry Pi / ODroid
Domoticz version: 2024.1
Location: The Netherlands
Contact:

Re: How to add data to domoticz.db

Post by willemd »

BartSr wrote: Sunday 21 January 2024 20:57 Thanks so far to all of you.

I updated a table in a test-environment.
It seems domoticz-protection has been extended as after I tried to start Domoticz with updated database I had to use credentials.
All of them which are surely correct are not accepted. Also starting domoticz from shortkey xxxx -nowwwpwd fail.
Any idea's / wotkarounds?

-Bart
The issues you have should in no way be related to an update of a xxx_calendar table.
BartSr
Posts: 365
Joined: Sunday 03 July 2016 16:16
Target OS: Raspberry Pi / ODroid
Domoticz version: V2024.7
Location: Netherlands
Contact:

Re: How to add data to domoticz.db

Post by BartSr »

it's in testenvironment on windows
I have a feeling somewhere in the database is a checksum which get updated once d b is changed outside domoticz

in vfp-files there were stored procedures in database
not that familar with sqlie I wonder if such might trigger changes
Raspberry pi 3b
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
BartSr
Posts: 365
Joined: Sunday 03 July 2016 16:16
Target OS: Raspberry Pi / ODroid
Domoticz version: V2024.7
Location: Netherlands
Contact:

Re: How to add data to domoticz.db

Post by BartSr »

mmm. seems to have to do how to restart Domoticz.
Once I stopped the Domoticz service I could do a restart.
That way credentials were accepted whereas localhost:8080 refused the credentials.
Bart
Raspberry pi 3b
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests