none
UWP and statefull critical data in SQLite or DB RRS feed

  • Question

  • There is a critical issue in UWP and SQLite that is not mentioned and I was not able to solve: what if the data in the SQLite is critical and should not be bound to the sandbox but must survive the App?

    From all the test I did I was NOT able to create the SQLite database file in the "Documents Folder" of the user.

    Reading from MS documentation UWP application should NOT address directly the files/folders but use StorageFolder and Storage FIle

    (references: https://docs.microsoft.com/en-us/uwp/api/windows.storage.storagefolder

    https://blogs.msdn.microsoft.com/wsdevsol/2012/12/04/skip-the-path-stick-to-the-storagefile/)

    Even if I've configured correctly all the File access properties and bindings

    (https://docs.microsoft.com/en-us/windows/uwp/files/file-access-permissions

    https://docs.microsoft.com/en-us/uwp/schemas/appxpackage/uapmanifestschema/element-uap-capability)

    In fact I'm able to enumerate and read the file in the Library I'm not able to perform the SQLiteConnection

    Using db As SqliteConnection = New SqliteConnection("Filename=" ...))

    No matter what I try to do there.

    I've found a document "Using SQLite Databases in Any Folder in WinRT" (https://www.codeproject.com/Articles/879846/Using-SQLite-Databases-in-Any-Folder-in-WinRT) that explains it is not possible from SQLite3 but has to do with custom/not supported code.

    If this si true is a critical issue as my application handles very critical data that must not be deleted in the case of an app removal.

    So I must be able to have the db file in a statefull storage outside of the sandbox and I would even put it in an OneDrive folder.

    A workaround that is not feasible in terms of performance would be perform a file copy from the sandbox app storage location to the documents folder.

    On the other side I tried to use SQL Server LocaDB (https://blogs.msdn.microsoft.com/jerrynixon/2012/02/26/sql-express-v-localdb-v-sql-compact-edition/) but I received an error that is NOT supported for UWP. In fact this section only mentions to connect with a SQL Server.

    So how I can I solve the critical issue I face?

    Am I doing something wrong? is it possible to have a code sample of an UWP app opening the data from a file in the User/Documents folder?

    Also note that several articles mention that LocalDB is for single use only and not production and that for real app SQLExpress should be used... but this raises the issue on how to distribute SQLExpress within an UWP app or automatically install it as prerequisite in the distribution via marketplace.

    So again is not a feasible solution. The best is to have SQLite with the DB file in a user folder.


    M.Savazzi

    Monday, October 7, 2019 8:22 PM

All replies

  • Hi,

    For this scenario, @Rob had explained this behavior before: UWP App SQLite Access database in Documents Library.

    "Because SQLite opens the database file directly rather than going through the file broker it can only see databases in the app install and application data (the directories that the app has direct file permissions to read and read/write respectively).

    Changing this would require an update to SQLite to use streams from the file broker objects (StorageFile and StorageFolder) to access locations that have permissions granted via capabilities, pickers, etc."

    A possible way it to copy the DB file from the user folder before you use it and update the DB file in user folder after you used it. 

    Best regards,

    Roy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 8, 2019 3:16 AM
    Moderator
  • Hi,

    For this scenario, @Rob had explained this behavior before: UWP App SQLite Access database in Documents Library.

    "Because SQLite opens the database file directly rather than going through the file broker it can only see databases in the app install and application data (the directories that the app has direct file permissions to read and read/write respectively).

    Changing this would require an update to SQLite to use streams from the file broker objects (StorageFile and StorageFolder) to access locations that have permissions granted via capabilities, pickers, etc."

    A possible way it to copy the DB file from the user folder before you use it and update the DB file in user folder after you used it. 

    Best regards,

    Roy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Roy, as I wrote in my messge I already thought to that solution and is UNACCEPTABLE!

    It creates a massive overhead of copy operations and if done async it create critical paths where an Update of the database is to be done while the file is still to be copied.

    IF MICROSOFT defines as THE ONLY supported database for UWP application SQLite it MUST create a version that works correctly within UWP and with it's sandboxing.

    As I wrote, as is today, UWP is USELESS and CANNOT be used for any real application with relevant data (data that must not risk to be destroyed if the app is removed for any reason).

    Is TOTALLY UNACCEPTABLE!

    Is the contrary of the definition of Data Storage... is "temporary" storage!

    As MICROSOFT is endorsing SQLite and there are several NUGET packages maintained by Microsoft for SQLite MICROSOFT should deliver an UWP compliant package! (and maintain it)

    Otherwise let's be clear and declare that UWP is "bullshit" (in terms of real relevant applications) and the way forward is WPF... but everything I've read is not in this direction!


    M.Savazzi


    • Edited by m.savazzi Tuesday, October 8, 2019 9:58 PM
    Tuesday, October 8, 2019 9:53 PM
  • Hi,

    For this scenario, @Rob had explained this behavior before: UWP App SQLite Access database in Documents Library.

    "Because SQLite opens the database file directly rather than going through the file broker it can only see databases in the app install and application data (the directories that the app has direct file permissions to read and read/write respectively).

    Changing this would require an update to SQLite to use streams from the file broker objects (StorageFile and StorageFolder) to access locations that have permissions granted via capabilities, pickers, etc."

    A possible way it to copy the DB file from the user folder before you use it and update the DB file in user folder after you used it. 

    Best regards,

    Roy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Let me be more clear and as I posted in Rob's anwer yhat is not a solution, is a workaround and opens more issues:

    1) overhead of read/writes. For relevant data EVERY database modification (insert/update/delete) must result in a file copy. This doubles the I/O (read file / write file)
    Also it requires to create some "new" close method to perform every time a close is after an update of the db to copy it.
    2) the copy function should be async in UWP this opens up to critical paths where while the file is still being copied the app can update the data creating a clash.
    How you solve that?
    3) it create a failure point because if the app crashes/machine crashes between file update and end of file copy you end up with 2 inconsistent files.
    4) how you manage the "restore" of the file? For any reason you have to uninstall/reinstall the app so the file in local storage is destroyed, how you can recover the existing copy? (copy back?) how you ensure is working and the real latest?
    5) what do you do if the copy fails? a DB must be transactional (SQLite IS transactional) so in the scenario where you do the db update -> copy -> copy fails how you manage the db rollback? is impossible as the transaction is already ended! otherwise you cannot close the db connection and copy the file.

    Overall, as is, is not an acceptable solution for a production grade application that must support resilient data.

    As said your is not an answer but a workaround that (as is) opens more issues than it solves. How you can address all the other issues?


    M.Savazzi

    Tuesday, October 8, 2019 10:10 PM