Page 1 of 1

how to clean or compress CSDB

Posted: October 4th, 2019, 7:24 am
by Enkhbayar
does anyone know how to clean garbage from a csdb?

I did it using covert CSDB -> DAT -> CSDB. you can see the differences from the screenshot. both files contains exactly same cases.
EICV_A.csdb vs EICV_A_old.csdb & EICV_B.csdb vs EICV_B_old.csdb.

am looking for other way, any idea?

Re: how to clean or compress CSDB

Posted: October 4th, 2019, 7:35 am
by josh
It depends what you mean by "cleaning". By converting from csdb->text->csdb you are throwing away a lot of information. By doing the conversion from csdb to text you are discarding the notes, the revision history, the partial save status the deleted cases and the sync history. That might be ok if you don't need the notes, deleted cases and don't plan to use sync. Although in that case you might as well just use a text file rather than a csdb file. The advantage of csdb is that stores all that extra stuff in addition to the data. If you don't want the extra stuff then just use the old text format.

In addition, the database engine that is used for csdb, SQLITE, doesn't automatically reduce the file size when data is deleted from a file. It keeps that space around to reuse it as needed. So a csdb file that has undergone a lot of modifications will tend to get bigger over time even if the data inside hasn't grown much. With today's hardware this isn't a problem since disk space is abundant. If for some reason you really want the csdb to be smaller you can use SQLITE to remove that unused space with the VACUUM command - https://sqlite.org/lang_vacuum.html. I would recommend making a copy of the csdb file first before trying this just in case. I have not actually tried this since I'm not worried about the file size. If I need to archive or email a big csdb file I just zip it.

Re: how to clean or compress CSDB

Posted: October 4th, 2019, 8:02 am
by Enkhbayar
josh wrote: October 4th, 2019, 7:35 am It depends what you mean by "cleaning". By converting from csdb->text->csdb you are throwing away a lot of information. By doing the conversion from csdb to text you are discarding the notes, the revision history, the partial save status the deleted cases and the sync history. That might be ok if you don't need the notes, deleted cases and don't plan to use sync. Although in that case you might as well just use a text file rather than a csdb file. The advantage of csdb is that stores all that extra stuff in addition to the data. If you don't want the extra stuff then just use the old text format.

In addition, the database engine that is used for csdb, SQLITE, doesn't automatically reduce the file size when data is deleted from a file. It keeps that space around to reuse it as needed. So a csdb file that has undergone a lot of modifications will tend to get bigger over time even if the data inside hasn't grown much. With today's hardware this isn't a problem since disk space is abundant. If for some reason you really want the csdb to be smaller you can use SQLITE to remove that unused space with the VACUUM command - https://sqlite.org/lang_vacuum.html. I would recommend making a copy of the csdb file first before trying this just in case. I have not actually tried this since I'm not worried about the file size. If I need to archive or email a big csdb file I just zip it.

thanks a lot Josh, I got it.