We are using The Dude as a local monitoring tool for many devices and special snmp probes. It is really a great and very lean program for such a purpose. Since we started using the dude, its data base file got bigger and bigger and we experienced some missbehave of notifications and other stuff. Instead of a fresh clean install we tried to find a way to clean/repair the existing data base. Mainly because the effort for a full regonfiguration would be to high. So, thats how I found this thread, where at the beginning is described to vacuum the dude.db file (defragment it).
As I tried this with sqlite3, I always had the Error of "unique constraint failed". After some diagnosis, I found that the table <chart_values_raw> was corrupted and could not be editted.
So, I tried to get rid of this and found a way to clean it up.
Hope this will be helpful to anyone...
Dude database maintenance/repair describtion:
A1. Close Dude Service und Dude.exe
A2. Download sqlite3 and create the folder \Downloads\sqlite3 https://www.sqlite.org/download.html
A3. Search Dude database file, such as-> C:\Users\"user"\AppData\Local\VirtualStore\Program Files (x86)\Dude\data\dude.db
A4. copy dude.db in Downloads\sqlite3\...
B1. start sqlite3.exe
B2. execute <.open dude.db> (open dude.db)
B3. execute <pragma integrity_check;> (perform integrity check)
B4. execute <vacuum;> (defragment database)
B5. in case B3. and/or B4. fails -> go on to "Repair database"
B6. in case both commands run without Error, put dude.db file back in user folder
B7. in user folder, delete any existing <dude.db-journal> and <dude.view> files
B8. start dude and service
<<<All history data will be lost>>>
C1. run cmd with admin
C2. change into sqlite3 directory
C3. execute <echo .dump | sqlite3.exe dude.db > dude.sql> (export dude.db into sql text file)
C4. edit dude.sql file with notepad++
C5. save all lines starting with <INSERT INTO "objs"> into a new textfile <objs.sql>
C5. in user folder delete dude.db , dude.db-journal and dude.view files
C6. start dude (new empty dude.db file will be created)
C7. close dude and copy new dude.db file into sqlite3 folder
C8. start sqlite3 and execute <.open dude.db>
C9. execute <pragma integrity_check;> (check dude.db)
C10. with <delete from objs;> delete entries in objs
C11. with <.read objs.sql> data from objs.sql will be importet into dude.db
C12. <.quit> to close sqlite3
C13. put new dude.db file from sqlite3 folder back into user directory
C14. in user folder, delete any existing <dude.db-journal> and <dude.view> files
C15. start dude and service. all settings will be back, just without history values
C16. as soon, everithing works well, do a backup export out of dude. The new file should be much smaller in size