Backup and Restore from SQLite.Net

Permalink | Tags:  c#
Posted: Monday, 02 September 2013
Share:  

Without a server process to run maintenance tasks, applications that use SQLite to store data must perform those tasks themselves. Backup and restore is the most commonly needed of these tasks. So I'll cover how I do that here.

Backup

API Support

SQLite.net provides an API method on a SQLiteConnection object to perform the SQLite backup operation.

In order to call this method you need to pass the following parameters:   

  • destination - An open SQLiteConnection for the destination database; 
  • destinationName - "main" to backup to the main database file, "temp" to backup to the temporary database file, or the name specified after the AS keyword in an ATTACH statement for an attached database;
  • sourceName - "main" to backup from the main database file, "temp" to backup from the temporary database file, or the name specified after the AS keyword in an ATTACH statement for an attached database;
  • pages - the number of pages on disk to back up with every iteration of the algorithm, -1 will backup the whole database in one iteration;
  • callback - a function that is called between every iteration, returns true to continue, or false to stop the algorithm;
  • retryMilliseconds - number of milliseconds to wait before retrying a failed iteration of the algorithm.

Extending with IObservable<T> and IObserver<T>

The following class wraps the iterative algorithm within an IObservable<SqliteBackupEvent> object. The SqliteBackupEvent class just contains the properties returned to the callback:

public class SqliteBackup : IObservable<SqliteBackupEvent>
{
   private readonly List<IObserver<SqliteBackupEvent>> _observers;

   public SqliteBackup()
   {
       _observers = new List<IObserver<SqliteBackupEvent>>();
   }

   public void Execute(
        string sourceConnectionString,
        string destinationConnectionString,
        int pagesToBackupInEachStep)
   {
       try
       {
           using (var srcConnection = new SQLiteConnection(sourceConnectionString))
           using (var destConnection = new SQLiteConnection(destinationConnectionString))
           {
               srcConnection.Open();
               destConnection.Open();

               // Need to use the "main" names as specified at
               // http://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupinit
               srcConnection.BackupDatabase(destConnection,
                   "main",
                   "main",
                   pagesToBackupInEachStep,
                   Callback,
                   10);

               destConnection.Close();
               srcConnection.Close();
           }
       }
       catch (Exception ex)
       {
           foreach (var observer in _observers)
               observer.OnError(ex);
       }

       foreach (var observer in _observers)
           observer.OnCompleted();
   }

   protected virtual bool Callback(
        SQLiteConnection srcConnection,
        string srcName,
        SQLiteConnection destConnection,
        string destName,
        int pages,
        int remaining,
        int pageCount,
        bool retry)
   {
       var @event = new SqliteBackupEvent(pages, remaining, pageCount, retry);

       foreach (var observer in _observers)
           observer.OnNext(@event);

       return true;
   }

   public IDisposable Subscribe(IObserver<SqliteBackupEvent> observer)
   {
       if (!_observers.Contains(observer))
           _observers.Add(observer);

       return new Unsubscriber(_observers, observer);
   }

   private class Unsubscriber : IDisposable
   {
       private readonly List<IObserver<SqliteBackupEvent>> _observers;
       private readonly IObserver<SqliteBackupEvent> _observer;

       public Unsubscriber(
           List<IObserver<SqliteBackupEvent>> observers,
           IObserver<SqliteBackupEvent> observer)
       {
           this._observers = observers;
           this._observer = observer;
       }

       public void Dispose()
       {
           if (_observer != null && _observers.Contains(_observer))
               _observers.Remove(_observer);
       }
   }
}

For completeness the SqliteBackupEvent class should be:

public class SqliteBackupEvent
{
     public int Pages { get; private set; }
     public int Remaining { get; private set; }
     public int PageCount { get; private set; }
     public bool Retry { get; private set; }

     public SqliteBackupEvent(int pages, int remaining, int pageCount, bool retry)
     {
         Pages = pages;
         Remaining = remaining;
         PageCount = pageCount;
         Retry = retry;
     }
}

 

This can be used to update a GUI or some other form of output, such as logging, with the status of the backup operation:   

public class ConsoleWriterObserver : IObserver<SqliteBackupEvent>
{
    public void OnNext(SqliteBackupEvent value)
    {
         Console.WriteLine(
             "{0} - {1} - {2} - {3}",
             value.Pages,
             value.PageCount,
             value.Remaining,
             value.Retry);
    }

    public void OnError(Exception error)
    {
         Console.WriteLine(error.Message);
    }

    public void OnCompleted()
    {
         Console.WriteLine("Complete");
    }
}

 

The use of these classes in your appilcation then becomes something like:

const string srcConnectionString = @"Data Source="".\data.db"";Version=3;";

const string destConnectionString = @"Data Source="".\newdata.db"";Version=3;";

var backup = new SqliteBackup();

using (var unsubscriber = backup.Subscribe(new ConsoleWriterObserver()))
  backup.Execute(srcConnectionString, destConnectionString, 50);

Console.ReadLine();

 

Summary

I hope someone finds this useful, leave a comment if you have a better way of achieving this.