Blockly's saved in a folder instead of in the database

[ActionHenk]

Hi,

Is it possible to save the blockly's outside the database?

I have a very stable domoticz sd-card setup, and sometimes if i screw arround with settings, updates or plugins that don't work i return to a sd-card backup from a few days earlyer.
Only thing is, i have to redo all the blockly changes i did after the last backup-time. I do that by taking screenshots of all blockly's before shutting the pi down, so after writing the backup to my sd-card with win32diskmanager i have to recheck all the blockly's with 2 lcd screens next to eachother (one screen with the backup images of blockly, the other screen running the fresh domoticz), really lame actually.

Ofcourse Saving and loading the database would be the way to go but i don't thrust it since it is not Always 100% secure, sometimes after loading a backup database the screen just never returns to a working domoticz so i have to sudo restart domoticz, and its still running the actual database instead of the one i uploaded. So i don't use this option.

So i would really like that domoticz saves the blockly scripts in a folder so i can retrieve them with winscp, and after a sd-backup i can inject my blocklys back in the folder.

Would that be possible? that would be really great!! I have running like 70 blockly's that are really big so rechecking them takes a lot of time, i just did that and its 4 hours later.

[FearNaBoinne]

Wouldn't exporting to text file (and importing if necessary) do the trick as well?

[gizmocuz]

Exporting and importing the database should just work, maybe you have to wait longer.
Another way is to stop Domoticz, copy the database, or stop Domoticz and place the database back
Best option is to keep it in the database.
If you database is larger then let's sat 15MB, it might also be a good idea to check which tables are so large and see if there is any reason for this (maybe a script is causing 1000 log lines a day)
In the feature i would like to make 2 databases, one with the configuration, and another with the logs.
For me the logs (years of history) are more important then the configuration

[waaren]

Is it possible to save the blockly's outside the database?

below bash script dumps Blockly's to files

Code: Select all

#!/bin/bash
#
# Dump all Blockly's to separate files in XML format
#
# change to reflect your situation / requirements
#
domoticzDir='/opt/domoticz/'
BlocklyDir='/tmp/blockly/'

# no changes required below this line
#
sudo mkdir -p $BlocklyDir
sudo cp $domoticzDir/domoticz.db /tmp/tmp.db 

sudo sqlite3 /tmp/tmp.db "select name,status,XMLSTatement from eventMaster WHERE interpreter = 'Blockly'" | while read line; do
    NAME=$(echo $line | awk -F'|' '{ print $1 }')
    STATUS=$(echo $line | awk -F'|' '{ print $2 }')
    XML=$(echo $line | awk -F'|' '{ $1=$2="" ; print $0 }') 
    EXT=".xml"
    if [ $STATUS == 0 ] ;then
        EXT=".xml_inactive"
    fi
    sudo rm  -f "$(echo $BlocklyDir$NAME.xml)"
    sudo rm  -f "$(echo $BlocklyDir$NAME.xml_inactive)"
    sudo echo $XML > "$(echo $BlocklyDir$NAME$EXT)" 
    echo created $BlocklyDir$NAME$EXT 
done
sudo rm /tmp/tmp.db

but easiest is probably to just dump the complete eventMaster and eventRules tables to a file.

Code: Select all

sqlite3 domoticz.db
sqlite> .out events.sql
sqlite> .dump eventMaster 
sqlite> .dump eventRules
sqlite> .q

[ActionHenk]

Thank you all for your answers.

My database files where between 15 and 24 mb large, i manage to keep it smaller between 4 and 8 mb by making the log sensor days smaller.

Anyway, last night i did write the backup image with win32diskmanager to another samsung sd-card, and it seems i can load now database files without problems. No matter if i load the 24 mb large databasefiles or the small 4 mb database files, so i guess i can rely again on the database backup.

Well its kinda strange i don't know why.

Exporting to text, can i do that? and how wil i import those text again to domoticz?

Waaren, your methode looks also promising, but it seems its only dumping the blockly's, how do i inject/import them?

Anyway, thank you all for your help!!

[waaren]

Waaren, your methode looks also promising, but it seems its only dumping the blockly's, how do i inject/import them?

cat /tmp/blockly/scriptName
select from <xml to </xml> and copy to clipboard
Goto event editor, press + , choose Blockly, press import and paste clipboard.

[ActionHenk]

Oke this is entirely new for me, thanks! I'm gonna sort that out.

Another question, when after a fault, crash, or whatever, can i be absolute sure that ,when uploading a database file to a fresh running sd-card image doesnt contain any shit that will break my fresh image running domoticz?

[waaren]

.. when after a fault, crash, or whatever, can i be absolute sure that ,when uploading a database file to a fresh running sd-card image doesnt contain any shit that will break my fresh image running domoticz?

Absolute sure ?? No such thing
But you can get close wrt database consistency using the same sequence of steps used when repairing a database.

service domoticz stop
sudo cp domoticz.db workcopy.db
sudo sqlite3 workcopy.db
sqlite> .out domoticz.sql
sqlite> .dump
sqlite> .q

sudo sqlite3 consistent.db < domoticz.sql

if no errors encountered in these steps.
sudo cp consistent.db domoticz.db
service domoticz start

[ActionHenk]

Thank you very much!

I will look into that,

I guess the best way to secure your working setup is to backup the sd-card to a image. Its a little time consuming but, at least i'm sure it is 100% safe!

Anyway, thanks all, really really love domoticz!

[lucky58]

but easiest is probably to just dump the complete eventMaster and eventRules tables to a file.

Code: Select all

sqlite3 domoticz.db
sqlite> .out events.sql
sqlite> .dump eventMaster 
sqlite> .dump eventRules
sqlite> .q

Hello,

what is the easiest way to put the 'dump' of events.sql with sqlite3 back in the domoticz.db?

kind regards,

Luc

[waaren]

what is the easiest way to put the 'dump' of events.sql with sqlite3 back in the domoticz.db?

assuming you started "fresh" (both table do have ID as primary key preventing overwriting by a row with the same ID) . If you want to replace an existing entry you first have to remove that entry from the domoticz.db before yo do the actions described below.

Code: Select all

cd <domoticz dir>
sudo service domoticz stop
sudo cp domoticz.db domoticz.safe
sudo sqlite3 domoticz.db

sqlite> load events.sql
sqlite> .q
sudo service domoticz start