How to use EF core sqlite with OneDrive?

[sjb-sjb]

What is the recommended approach to maintaining EF Core Sqlite files using OneDrive? Currently I am creating the database file directly in a OneDrive folder, however, won’t the lock prevent OneDrive from replicating the file? I am using EF to create and access the storage for a WinUi 3 app. The user may have the app running for a long time although in that case there will be a significant amount of time when it is not actually being used.

[ajcvickers]

@sjb-sjb We don't have any specific guidance around this, since it doesn't relate to EF or even the ADO.NET provider, but rather to SQLite itself.

[sjb-sjb]

@ajcvickers OK, thanks, any comments or suggestions based on your experience?

[ErikEJ]

@sjb-sjb My experience says you should not do it.

[ajcvickers]

Totally agree with @ErikEJ. Don't do it.

[sjb-sjb]

@ErikEJ , @ajcvickers ... OK but in that case, assuming the end user is backing up their documents with OneDrive, how am I supposed to ensure the database is backed up? I believe that Microsoft guidance says that files in the AppData folder are meant to contain non-essential information only, e.g. it can get wiped out for various reasons such as re-installing the app, moving to a new machine, etc.

Am I supposed to copy the file myself from AppData to the document directory? That seems a bit retrograde. Besides, to do that, if the program is long-running, I need to release the pooled context connection / lock before copying the file. If I'm doing that then I may as well leave the file on OneDrive and let OneDrive do the backup while the lock is off (? contrary to the "don't do it" advice).

I don't see how one is supposed to use Sqlite to manage a document file, with this kind of constraint. Should I be using some other DB to read/write my document?

[roji]

@sjb-sjb note that this is the EF repo - we really don't have any specific expertise on how to use SQLite in shared contexts or anything like that; we're just a layer above that. I suggest going to a SQLite-specific forum for that.

[sjb-sjb]

Thx

[sjb-sjb]

I appreciate the "do not do it" advice given above as relates to opening Sqlite files directly on OneDrive or similar. For those who would like to store their Sqlite files in the AppDir and then copy them occasionally into the user's Documents directory, let me provide some free code. This method assumes there are methods SetProgress and ClearProgress for reporting the progress of the copy.

    /// <summary>
    /// Copies the Sqlite database in the workingFile to the resultingFile.
    /// This does not interfere with the ongoing use of the working database. 
    /// No backup is made of the current resultingFile before it is overwritten.
    /// An InvalidOperationException may be thrown if there is a system error in copying the file, 
    /// for example out of memory, destination file could not be opened or copied to, etc. 
    /// See https://www.sqlite.org/backup.html
    /// </summary>
    static public async Task CopyDatabaseAsync( this StorageFile workingFile, StorageFile resultingFile, DelegateTokens? delegateTokens)
    {
        // "working" file means the actual database being used currently and that needs to be copied over to
        // the resulting file. It should be in the working directory (within the AppDir).
        string workingFoldername = Path.GetDirectoryName(workingFile.Path) ?? throw new SystemFailureNotification(FrameworkStrings.SqliteDbManager_FileError, $"Could not get directory name for '{workingFile.Path}'.");
        StorageFolder workingFolder = await StorageFolder.GetFolderFromPathAsync(workingFoldername) ?? throw new SystemFailureNotification(FrameworkStrings.SqliteDbManager_FileError, $"Could not find working folder '{workingFoldername}'.");

        // "resulting" file means the end-user file, usually not in the working directory, probably
        // stored on OneDrive or similar, that is to be updated based on the working file. 
        int i = resultingFile.Name.LastIndexOf('.');
        Debug.Assert(resultingFile.FileType == resultingFile.Name[i..]);

        // "temporary" file means an intermediate copy of the working file, in the working directory,
        // produced by this method and then copied over to the resulting file.
        // We leave it lying around after the process is complete just as an extra backup copy.
        string temporaryFilename = resultingFile.Name[..i] + “ - Backup” + resultingFile.Name[i..];
        StorageFile? temporaryFile = await workingFolder.TryGetItemAsync(temporaryFilename) as StorageFile;
        if (temporaryFile != null) { 
            await temporaryFile.DeleteAsync();
            temporaryFile = null;
        }
        string temporaryFilepath = Path.Join(workingFolder.Path, temporaryFilename);

        const int OK = SQLitePCL.raw.SQLITE_OK;
        const int BUSY = SQLitePCL.raw.SQLITE_BUSY;
        const int LOCKED = SQLitePCL.raw.SQLITE_LOCKED;
        //const int DONE = SQLitePCL.raw.SQLITE_DONE;

        sqlite3? workingDb = null;
        sqlite3? temporaryDb = null;

        int rcOpen = SQLitePCL.raw.sqlite3_open_v2(workingFile.Path, out workingDb, SQLitePCL.raw.SQLITE_OPEN_READONLY, null);
        if (rcOpen != OK) { throw new SqliteException( $"Could not open working database '{workingFile.Name}' in working directory '{Path.GetDirectoryName(workingFile.Path)}'", rcOpen); }
        Debug.Assert(workingDb != null);

        rcOpen = SQLitePCL.raw.sqlite3_open_v2(temporaryFilepath, out temporaryDb, SQLitePCL.raw.SQLITE_OPEN_READWRITE|SQLitePCL.raw.SQLITE_OPEN_CREATE, null);
        if (rcOpen != OK) { throw new SqliteException($"Could not create temporary database '{temporaryFilename}' in working directory '{workingFolder.Path}'", rcOpen); }
        Debug.Assert(temporaryDb != null);

        sqlite3_backup? backupSession = SQLitePCL.raw.sqlite3_backup_init(temporaryDb, "main", workingDb, "main");
        if (backupSession == null) { throw new SqliteException( $"Could not intialize backup of database. " + SQLitePCL.raw.sqlite3_errmsg(temporaryDb).utf8_to_string(), SQLitePCL.raw.sqlite3_errcode(temporaryDb), SQLitePCL.raw.sqlite3_extended_errcode(temporaryDb)); } 
        while (true) {
            int stepResult = SQLitePCL.raw.sqlite3_backup_step(backupSession, 10);
            int toDo = SQLitePCL.raw.sqlite3_backup_pagecount(backupSession);
            int done = toDo - SQLitePCL.raw.sqlite3_backup_remaining(backupSession);
            SetProgress( toDo, done);
            if (stepResult != OK && stepResult != BUSY && stepResult != LOCKED) { break; }
            await Task.Delay(200);
        }
        int overallResult = SQLitePCL.raw.sqlite3_backup_finish(backupSession); // Not BUSY or LOCKED, since those are transitory codes.
        backupSession = null;

        int rcCloseW = SQLitePCL.raw.sqlite3_close(workingDb);
        workingDb = null;

        int rcCloseT = SQLitePCL.raw.sqlite3_close(temporaryDb);
        temporaryDb = null;

        if (overallResult != OK) { throw new SqliteException($"Backup of working database failed to complete. " + SQLitePCL.raw.sqlite3_errmsg(temporaryDb).utf8_to_string(), overallResult); }
        if (rcCloseW != OK) { throw new SqliteException($"Could not close working database '{workingFile.Name}' in working directory '{Path.GetDirectoryName(workingFile.Path)}'", rcOpen); }
        if (rcCloseT != OK) { throw new SqliteException( $"Could not close temporary database '{workingFile.Name}' in working directory '{Path.GetDirectoryName(workingFile.Path)}'", rcOpen); }

        temporaryFile = (await workingFolder.GetItemAsync(temporaryFilename)) as StorageFile; // throws if e.g. file not found
        if (temporaryFile == null) { throw new InvalidOperationException($"Temporary file was not a StorageFile!"); }
        await temporaryFile.CopyAndReplaceAsync(resultingFile); // no locking issues here since sourceFile is never opened as an Sqlite database.
        ClearProgress();
    }