Domoticz

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

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

Thanks for your reply.
Can you share the way how you did the update?

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.

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

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.

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/release/t ... BMerger.7z Edit waltervl: updated download link

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

Did you check that domoticz.db is not root but pi and chown?

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.

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

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