How to deal with database files for an app?

[bogs]

AxWald, I want you at our next business meeting, that was priceless

[LCMark]

SQLite is probably the most robust, most well-tested and reliable piece of open-source software in existence (its worth reading the pages on www.sqlite.org about how they test it to see what they do). Indeed, I believe it has also been certified to use in avionics software, which is no mean feat.

SQLite data-file corruption will only really occur because (1) the file is being misused (being opened by two separate processes simultaneously in a situation it can't detect) or (2) hardware issues or data transport issues (i.e. where the binary content of the file is corrupted outwith sqlite's control).

I think you'll be safe storing all data in a single database file (although SQLite does make it easy to talk to multiple files through a single connection - see the 'ATTACH DATABASE' command).

If you want to give your users an 'extra-safe' backup option then add the ability to export and import a human-readable plain text file which can be mapped 1-1 with the contents of the database - at least then, as a last resort, any issues can be much more easily determined by a human eye.

The other advantage of providing high-quality, fully-faithful text import/export is that it makes it much easier for others to write code which generates data which can be imported into your accounting app (e.g. from legacy back-office systems, any online payment portal you could imagine etc.)

[ I once spent more time than I'd like to quantify attempting to get SAGE importing data from a number of invoice data sources we used to have because its text import facilities were so poor - it was not fun, the code was not pretty, and I really wish I could have spent some time doing something else! ]

[MichaelBluejay]

Thank you, LCMark.

Despite everyone's assurances that data corruption is rare to impossible, and that backups solve everything, I did actually suffer data corruption, and my backups were not helpful for reasons I've already stated. (Yes, the failure might have been the physical disk rather than the fault of SQLite, so what? My data was still corrupted, resulting in my having to waste a lot of time re-doing lots of data entry.) It's for these reasons that I'm trying to be cautious about how my app handles data.

I've decided to store all data in a single file, and to make it easy for users to store separate years in separate files if they wish.

[FourthWorld]

Are any of those SQLite files in folders synced via Dropbox or other public cloud sync?

[MichaelBluejay]

No, all local.

[FourthWorld]

Dropbox and similar sync services works on local files, but if these aren't in synced folder we can safely rule out the known issues between such services and paged formats like SQLite.

Other tips on possible sources of corruption from SQLite, FWIW:
https://www.sqlite.org/howtocorrupt.html

[MichaelBluejay]

The file was never synced anywhere.

Incidentally, I do know that it was physical file corruption (rather than a logic error, where the program recorded data wrong and then didn't know how to deal with it), because when I opened the file in an SQLite browser, I remember getting some weird symptoms, I don't remember exactly what, but I think it showed four rows where it was trying to load data but was never able to load it.

[jacque]

I'll just put in a word for single files here too. We have (mostly Windows) users who delete or move files to suit their own concept of organization and routinely separate executables from their resource files. Then everything breaks and of course it's because our app is "buggy".

[LCMark]

So I think I perhaps didn’t explain my point very well.

Data corruption will in all likelyhood happen at some point to one of your users - because hardware can be flaky.

SQLite is robust enough that having a single or multiple files is unlikely to make a difference if your app is accessing them concurrently and the hardware is faulty or some extraordinary event occurs.

Redundancy is the best option - and redundancy in a less compact / more human grokable format is much better than just a copy of the sqlite files as if the worse happens (and the backups are not on a separate system) it is much easier to piece together fragments of text than fragments of corrupted binary data files.

(TBH though one year per file does make a lot of sense for accounting software - after all you aren’t meant to rewrite history after your ‘year end’ is done!)

[MichaelBluejay]

Another idea: The export options for the last app I used were pretty weak. I'll have my app have pretty robust exporting options. For a given date range (including any whole year):

* CSV
* Human-readable
* SQLite, openable by my app as an independent document.

So, users can save a known-good year if they need to return to it, and they can archive that year's file along with all their other documents (e.g., tax reports) for that year.

By the way, one other reason that users might want to keep separate years separate is performance. The old app I used got noticeably slower as I added new years' worth of data.

[bogs]

By the way, one other reason that users might want to keep separate years separate is performance. The old app I used got noticeably slower as I added new years' worth of data.

Sounds like a code inefficiency issue, like they were just sucking the entire db into the program instead of chunks at a time.

[okk]

Other tips on possible sources of corruption from SQLite, FWIW:
https://www.sqlite.org/howtocorrupt.html

That was a scary read. Thanks for sharing

[FourthWorld]

Other tips on possible sources of corruption from SQLite, FWIW:
https://www.sqlite.org/howtocorrupt.html

That was a scary read. Thanks for sharing

There's little there that doesn't apply to nearly any type of file, esp. complex page-from-disk formats.

Apple isn't the only company that uses SQLite extensively. As formats go, like Mark said, it's among the most robust.

Didn't seem all that scary to me, just a sober reminder of the value of a good redundant set of backups. You know, like every computer user has in place.

Portable USB drives go for $50/TB, and many cloud services offer at least 15GB free, with much more for modest prices. A mix of different types can get a business up and running after a natural disaster or ransomware attack in just hours.

#SleepRestfully #LongLiveRsync #NextcloudFTW

[ghettocottage]

I wanted to chime-in here on backups:

For backups for a small office/home business a basic set-up I do (and is generally the first order of business when I am setting up an office):

• two 2tb hard-drives (or two 4tb hard drives if you can afford them or have huge amounts of files)
• rsync for linux or mac computers and Cobian Backup for Windows computers.
• all computers backup their files/folders each night to the backup server hard-drive1 (which is shared on the network) where each computer has its own folder to backup to.
  -the backups are incremental rather than full backups...meaning it looks for files/folders that have changed since last backup and updates that on the backup drive.
• once per week the server backs up hard-drive1 to a folder in hard-drive2 (which is NOT shared on the network) called Weekly
• once per month the server backs up hard-drive 1 to a folder for that month in a folder called Monthly
  -monthly folder will eventually have a folder for every month of the year (Jan, Feb, Mar, etc).

so now we have Daily, Weekly and Monthly backups, with a full year of Monthly backups.

Lastly, I usually have a VPN connection to the business-owners house or other office where hard-drive1 is backed up weekly to another hard-drive


Does that seem excessive? Well, it is until you need it.

Both rsync and Cobian Backup will chug away at their jobs unnoticed for years.
Since the backups are incremental, they won't (usually) overload the hard-drives unless you suddenly have tons of new files.
An older Intel 2-core with 4gb or RAM computer can be had for less than $50 and can easily handle all of the above backup routines and file sharing.


In summary:
Multiple copies of the same backups spread across various time increments and different hard-drives can be automated and set up rather cheaply and can save your bacon in an emergency.

#####################################################
here is an rsync script I have been using for the past few years
it will also backup your database files from MySQL or MariaDB
it can be altered to fit different situations...rsync can also
use SSH to login to a remote server and backup there.
#######################################################

Code: Select all

#!/bin/bash

#ver. 4 

#this is the primary backup script
#run script with name of backup frequency
#(for example:  ~/.backup weekly  )

#Options##############

#your database info####
   DBHOST='localhost'
   DBUSER='user_backup'
   DBPW='YOURDBPSSWD'
   
#your databases to backup
#for individual databases:
#DBNAME=(  'mas' )
#for all databases on server:
  DBNAME=( `echo "show databases" |  mysql --user=$DBUSER --password=$DBPW --host=$DBHOST  | tail -n+3 `)


#your folders to backup 
    FOLDERS=(  '/var/www' '/mnt/backups1'  '/my/other/folder' )
    MODE='-q'  
#End Options #stop editing####

#get the name of the script
SCRIPTNAME=$1

#get the current month
MONTH=$(date +%b)

  if  [ "$SCRIPTNAME" != "yearly" ] ;  
  then

      #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "check if local .backups directory exists. if not create it";
       fi

       #check if local .backups directory exists. if not create it
       if [ ! -d "~/.backups/$SCRIPTNAME/db" ]; then
       mkdir -p ~/.backups/$SCRIPTNAME/db ;
       fi

       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "remove db files older than 1 day";
       fi
       
       find ~/.backups/$SCRIPTNAME/db/*.sql -type f -daystart -mtime +0 -exec rm {} \;  

       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "get recent version of databases";
       fi

       #get recent version of databases from array
       for i in "${DBNAME[@]}"
       do
       mysqldump --opt --user=$DBUSER --password=$DBPW --host=$DBHOST $i --lock-tables=false --ignore-table=mas.mas_cards   > ~/.backups/$SCRIPTNAME/db/$i.`date +\%Y-\%m-\%d_\%H-\%M-\%S`.sql
       done

       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "back up your folders to local .backups folder";
       fi
       
       for i in "${FOLDERS[@]}"
       do
	rsync -rlptD  -t -q $MODE --delete --inplace --size-only   --copy-links  ~/$i  ~/.backups/$SCRIPTNAME/   
       done

       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "all done!"
       fi
exit 0




   else

      #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "running as yearly";
       echo "check if local .backups directory exists. if not create it" ;
       fi

    #check if local .backups directory exists. if not create it
       if [ ! -d "~/.backups/$SCRIPTNAME/$MONTH/db" ]; then
       mkdir -p ~/.backups/$SCRIPTNAME/$MONTH/db ;
       fi


 #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "remove db files older than 1 day" ;
       fi
       find ~/.backups/$SCRIPTNAME/$MONTH/db/*.sql -type f -daystart -mtime +1 -exec rm {} \;  
       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "get recent version of databases" ;
       fi

       #get recent version of databases from array
       for i in "${DBNAME[@]}"
       do
       mysqldump --opt --user=$DBUSER --password=$DBPW --host=$DBHOST $i --lock-tables=false   --ignore-table=mas.mas_cards  > ~/.backups/$SCRIPTNAME/$MONTH/db/$i.`date +\%Y-\%m-\%d_\%H-\%M-\%S`.sql 
       done


       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "back up your folders to local .backups folder" ;
       fi

      for i in "${FOLDERS[@]}"
       do
	rsync -rlptD  -t $MODE --delete --inplace --size-only    --copy-links  ~/$i  ~/.backups/$SCRIPTNAME/$MONTH/   
       done

   
       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "all done!" ;
       fi
exit 0
 fi

[FourthWorld]

Super, ghettocottage. Thanks for posting that. Bash is to the modern world of systems administration what AppleScript was in its day - and it's cross platform (Windows 10 Pro now includes it as part of the Linux Subsystem). It's great to see bash examples in our community, inspiring us to enhance our automation tooling for more productive development.