[Solved] How to add data to domoticz.db
Moderator: leecollings
-
- 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
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
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
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
-
- 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
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
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
-
- 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
Thanks for your reply.
Can you share the way how you did the update?
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
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
-
- 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
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.
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.
-
- 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
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
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
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
-
- 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
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.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
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.
- 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
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
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
-
- 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
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
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
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
-
- 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
Did you check that domoticz.db is not root but pi and chown?
Bugs bug me.
-
- 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
The issues you have should in no way be related to an update of a xxx_calendar table.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
-
- 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
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
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
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
-
- 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
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
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
Arduino
KAKU
RfxCom
Zwave2MQTT
OTGW
Chinese sensors temp (Dallas),movement
Tasmota
Esp8266 / 32 espeasy
Zigbee2MQTT
Who is online
Users browsing this forum: No registered users and 0 guests