How can I export a Room database to a .db file so I can use it later? I would like to export it to Download folder in Android device storage. I already have setup export to .CSV but now i need to .db.

This is so I can reimport it if the user is switching devices and needs his data on the other device.

Is there a way just to use .CSV data or do i have to use .db?

CodePudding user response：

It would be far simpler to backup the database file(s) and probably also faster than trying to manipulate CSV data.

There is also the advantage that the database can then be used in SQLite Tools simply by copying the files.

P.S. The extension ( e.g. .csv .db) is only an indication of the file's use. In the example .whatever has been used.

• An issue is that after restoring the database the can be problems with accessing the restored data.

  • A way to circumvent such issue would be to include restarting the App automatically after the restore.

• Another issue is that Room uses Write-Ahead Logging (WAL) by default and it doesn't appear to be that easy to checkpoint the database.

  • as a get-around instead of backing up a single file, 3 files can be backed up and restored (the wal file and the shm file) see
    • Ideally the Restore button should not appear as there is, at this stage nothing to restore from
      • clicking the Restore button is fine as the restore function checks to see if there are any restore files and if not returns immediately

    If the backup button is clicked then the files will be backed up (even if there is no data (there will actually be some data in the file though, such as the file header and the system tables)).

    If after clicking the Backup button, the restore button is clicked (even if no data has been added) then the database is actually restored and the App is restarted.

    If the AddData button is clicked the 3 rows of data are added and displayed (another 3 added if clicked again and so on).

    

    As the Backup buttons was previously clicked. Clicking the restore button will restore to the backup that has no data and restart the App displaying no data.

    

    Adding 2 sets of data and also clicking the Backup button

    

    Adding another 2 sets of data

    

    Clicking restore then the data is restored to just the 6 rows of data

    

    CodePudding user response：

    Here's a CSV solution that will backup and restore ALL non-system/room/android tables BUT, it does not handle BLOBS (that is more complex) it may have space issues, it may have issues if Foreign Keys constraints are included (backing up the file is the suggested way as it is far less likely to encounter issues, other than the suggested restart of the App which is just an inconvenience).

    Furthermore, for brevity and convenience, it

    • a) runs on the main thread
    • b) places the backups in the databases folder rather than elsewhere

    It revolves around 4 functions, in this example within the @Database annotated class (TheDatabase). The functions are :-

    getTableColumnnames (see example/demo)

    • this returns the list of columns in the provided table name using the provided SupportSQliteDatabase (both passed from the createAutoCSV function)
      • the list is return to the calling function.

    createAutoCSV (see example/demo)

    • This function generates and returns the CSV file as a String from a StringBuffer that is progressively built.
      • not sure about size limitations (see

        Then after clicking ADD DATA button again to add some data after the backups.

        

        Then clicking the CSV RESTORE button

        

        Again ADD DATA is clicked one or more times and then RESTORE button is clicked (restore from the File backup)

        

        • so back to the original data, noting that the App is restarted.