Description:
This snippet in B4A is the full code that allows you to make a vacuumed copy of an open SQLite database and reduces its size. SInce this feature was introduced in SQLite version 27, the code checks and makes sure you are you using a device that has a version of 27 and higher. It is particularly uuseful if the open database has ballooned in size due to multiple updates, inserts and/or deletions.
If the vesrsion is earlier than 27, you can still vacuum a database using: SQL1.ExecNonQuery("Vacuum") to reduce the size of the open database itself without making a copy.
@Chris2 has a similar snippet for B4J. If you like to check it out it is here:
Tags: SQLite, Vacuum
Dependency: SQL Library
This snippet in B4A is the full code that allows you to make a vacuumed copy of an open SQLite database and reduces its size. SInce this feature was introduced in SQLite version 27, the code checks and makes sure you are you using a device that has a version of 27 and higher. It is particularly uuseful if the open database has ballooned in size due to multiple updates, inserts and/or deletions.
B4X:
Sub VacuumToaFile
Dim backupfile As String = "testbackup.db"
If File.Exists(xui.DefaultFolder, backupfile) Then
File.Delete(xui.DefaultFolder, backupfile)
End If
Dim sqliteversion As String =SQL.ExecQuerysingleresult( "SELECT SQLite_version()")
' Log(sqliteversion)
Dim sv() As String =Regex.Split("\.", sqliteversion)
If sv(1) >= 27 Then
' SQL.ExecNonQuery2("VACUUM INTO ?", Array($"${xui.DefaultFolder}/${backupfile}"$)) 'this works too
SQL.ExecNonQuery2("VACUUM INTO ?", Array(File.combine(xui.defaultfolder,backupfile))) 'this works too
Log($"Main database was vacuumed into a new file: ${backupfile}"$)
For Each f As String In File.ListFiles(xui.DefaultFolder)
If f.EndsWith(".db") And f.Contains("backup") Then
Log(f)
End If
Next
Else
Log($"Device SQLlte version is earlier than version 27. Cannot vacuum into a new file"$)
End If
End Sub@Chris2 has a similar snippet for B4J. If you like to check it out it is here:
Backup SQLite DB with VACUUM INTO
Less of a tutorial, more to point out another option which doesn't seem to have a mention anywhere on here yet.... I was searching for options to create a backup copy of an SQLite database. There are already options detailed here, and here, for example. But another option that seemed quite...
Tags: SQLite, Vacuum
Dependency: SQL Library