# we want to test backing up a DB which is being written to by another process

# create a table (this is taken from the schema of the fine LDAP server lldap: https://github.com/lldap/lldap/)

sqlite3  test.db <<EOF
CREATE TABLE IF NOT EXISTS "users" ( "user_id" text(255) NOT NULL PRIMARY KEY, "email" text(255) NOT NULL, "creation_date" text NOT NULL, "password_hash" blob, "totp_secret" text(64), "mfa_type" text(64), "uuid" text(36) NOT NULL , "display_name" text(255), "lowercase_email" text(255) NOT NULL DEFAULT 'UNSET');
CREATE UNIQUE INDEX "unique-user-email" ON "users" ("email");
CREATE UNIQUE INDEX "unique-user-uuid" ON "users" ("uuid");
CREATE UNIQUE INDEX "unique-user-lower-email" ON "users" ("lowercase_email");
EOF

# we create inserts with a commit after 100 inserts
echo 'BEGIN TRANSACTION;' > insert-users.sql

i=0

# drink a coffee for 5 minutes
while (( $i < 100000 )); do
  echo "insert into users (user_id, email, creation_date, uuid, lowercase_email) values ( '$i', '$i', '$(date +%Y-%m-%dT%H:%M:%S.000+00)', '$i', '$i');" >> insert-users.sql
  (( i = i + 1 ))
  if (( i % 100 == 0 )) ; then
    printf "COMMIT;\nBEGIN TRANSACTION;\n" >> insert-users.sql
  fi
done

# start the inserts in the background
( cat insert-users.sql | sqlite3 test.db ) &

# try to create backups every 1 s
n=0 && while (( n < 20 )) ; do sqlite3 test.db ".backup backup-$(date +%H%M%S).db" ; sleep 1 ; (( n = n + 1 )) ; done

Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
Runtime error near line 71808: database is locked (5)
Runtime error near line 71809: cannot start a transaction within a transaction
Error: database is locked
Runtime error near line 78132: database is locked (5)
Runtime error near line 78133: cannot start a transaction within a transaction
Error: database is locked
Error: database is locked
Error: database is locked
Error: database is locked
[2]-  Exit 1                  cat insert-users.sql | sqlite3 test.db

# ll
total 110444
drwxr-xr-x 2 root root     4096 Apr 25 12:28 ./
drwxr-xr-x 4 root root     4096 Apr 25 12:00 ../
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122804.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122805.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122806.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122807.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122808.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122809.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122810.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122811.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122812.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122813.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122814.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122815.db
-rw-r--r-- 1 root root        0 Apr 25 12:28 backup-122816.db
-rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122817.db
-rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122818.db
-rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122819.db
-rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122820.db
-rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122821.db
-rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122822.db
-rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122823.db
-rw-r--r-- 1 root root 14882579 Apr 25 12:19 insert-users.sql
-rw-r--r-- 1 root root 12271616 Apr 25 12:28 test.db


# clean up
sqlite3 test.db 'delete from users; vacuum; '
rm backup-*.db

# let's retry with WAL https://www.sqlite.org/wal.html
echo 'pragma journal_mode=WAL;' | sqlite3 test.db

( cat insert-users.sql | sqlite3 test.db ) &

# try to create backups every 1 s
n=0 && while (( n < 20 )) ; do sqlite3 test.db ".backup backup-$(date +%H%M%S).db" ; sleep 1 ; (( n = n + 1 )) ; done

# no errors

ll
total 112792
-rw-r--r-- 1 root root  3764224 Apr 25 13:10 backup-131050.db
-rw-r--r-- 1 root root  8331264 Apr 25 13:10 backup-131051.db
-rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131052.db
-rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131053.db
-rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131054.db
-rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131055.db
-rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131056.db
-rw-r--r-- 1 root root 14882579 Apr 25 12:19 insert-users.sql
-rw-r--r-- 1 root root 12271616 Apr 25 13:10 test.db

# what is the largest user_id in each of these backups?
for db in backup*.db ; do sqlite3 $db 'select * from users ' | tail -n 1 | cut -f 1 -d \| ; done
32099
68499
99999

# that's too fast, let's remove the transactions
egrep -v '(BEGIN|COMMIT)' insert-users.sql  > insert-users-no-transactions.sql

# clean up
sqlite3 test.db 'delete from users; vacuum; '
rm backup-*.db

( cat insert-users-no-transactions.sql | sqlite3 test.db ) &
n=0 && while (( n < 120 )) ; do sqlite3 test.db ".backup backup-$(date +%H%M%S).db" ; sleep 1 ; (( n = n + 1 )) ; done

# now it takes more than 2 minutes
ll
-rw-r--r-- 1 root root    36864 Apr 25 13:19 backup-131903.db
-rw-r--r-- 1 root root   118784 Apr 25 13:19 backup-131904.db
-rw-r--r-- 1 root root   188416 Apr 25 13:19 backup-131905.db
-rw-r--r-- 1 root root   258048 Apr 25 13:19 backup-131906.db
-rw-r--r-- 1 root root   327680 Apr 25 13:19 backup-131907.db
-rw-r--r-- 1 root root   397312 Apr 25 13:19 backup-131908.db
-rw-r--r-- 1 root root   466944 Apr 25 13:19 backup-131909.db
-rw-r--r-- 1 root root   536576 Apr 25 13:19 backup-131910.db
...
-rw-r--r-- 1 root root 12247040 Apr 25 13:21 backup-132143.db
-rw-r--r-- 1 root root 12271616 Apr 25 13:21 backup-132145.db
-rw-r--r-- 1 root root 12271616 Apr 25 13:24 backup-132443.db

for db in backup*.db ; do sqlite3 $db 'select * from users ' | tail -n 1 | cut -f 1 -d \| ; done  
180  
781  
1441 
2089 
2734 
3393 
4044 
4689 
5346 
5995 
6646 
7302 
7947 
8569 
9214 
9867 
10520
11173
11826
...
96000
96739
97437
98194
99008
99882
99999
99999

# Conclusion: it is feasible to create backups of a running sqlite DB, iff it is set to WAL mode