Room: Provide Database Path Guarantee, Or Provide Pre-Packaged Database Solution [62185732]

Fixed

Feature Request

Status Update

No update yet.

Description

mm...@commonsware.com

created issue #1

May 30, 2017 02:07PM

Component used: Room
Version used: 1.0.0-alpha1
Devices/Android versions reproduced on: n/a

A common scenario is where an app needs to ship a database as part of the APK, or downloaded on first run of the app. That might serve as starter data, or it might serve as a read-only database of reference information. For the packaged-in-the-APK scenario, Jeff Gilfelt's SQLiteAssetHelper is a common solution, which replaces SQLiteOpenHelper and transparently copies the database from assets into the proper spot in the filesystem before proceeding.

With Room, there is no obvious way to handle the pre-populated database scenario.

One possibility is that Room doesn't do anything, and we have to arrange to get the database in position prior to creating the RoomDatabase. However, for that to work, we need to know where to put the database. While we supply the database name, technically Room could be storing that database anywhere (e.g., in a room/ subdirectory off of the normal database directory on internal storage). If we are going to go this route, we need some sort of documented statement about where Room stores the database, or a method to retrieve that location (prior to creating the RoomDatabase), so we know where to copy the database file into.

Other possibilities include:

- Having the option of subclassing the Framework implementation of SupportSQLiteOpenHelper, so we can craft a SQLiteAssetHelper-style implementation. Right now, that class is not part of the public API. While we could fork all of the Framework stuff to try to get this, that seems like overkill.

- Having a createFromAsset() and/or createFromFile() method on RoomDatabase.Builder, for us to provide the location of the starter database, for Room to copy into position for us if the database does not already exist.

Thanks!

Comments

mm...@commonsware.com <mm...@commonsware.com> #2May 30, 2017 02:09PM

For Kotlin 2.0 and KSP 2.0 the Cannot change attributes of configuration ':composeApp:debugFrameworkIosX64' after it has been locked for mutation really seems like a KSP issue. You should file a bug in their repository with a sample app if possible.

If you downgrade to Kotlin 1.9 then things 'should' work, there are example apps out there with such configuration, like the following one: https://github.com/realityexpander/KMP_Room/tree/main

je...@gmail.com <je...@gmail.com> #3May 30, 2017 05:22PM

Yes this does seem like a KSP issue however it only starts happening when room is added to the project. As the template project compiles just fine.

Will try to use the example provided by you to check if it fixes the issue.

yb...@google.com <yb...@google.com> #4May 30, 2017 07:29PM

Assigned to yb...@google.com.

Note that this issue happens when applying the Compose, KSP and Room Plugin together in Kotlin 2.0.x, the workaround for now is to not use the Room Gradle Plugin and instead specify the schema location vis KSP arguments:

// In the build.gradle
ksp {
    arg("room.schemaLocation", "${projectDir}/schemas")
}

mm...@commonsware.com <mm...@commonsware.com> #5May 30, 2017 07:34PM

Hi, I encountered a similar problem and was able to resolve it by updating the dependencies

room = "2.7.0-alpha08"
ksp = "2.0.20-1.0.25"

compose-plugin = "1.6.11"
kotlin = "2.0.20"

mm...@commonsware.com <mm...@commonsware.com> #6May 30, 2017 10:23PM

FWIW, attached is a proof-of-concept implementation of a SupportSQLiteOpenHelper and SupportSQLiteOpenHelper.Factory that uses Jeff Gilfelt's SQLiteAssetHelper for supporting a pre-packaged database in the APK.

It uses forked copies of FrameworkSQLiteOpenHelper and FrameworkSQLiteOpenHelper.Factory (named AssetSQLiteOpenHelper and AssetSQLiteOpenHelper.Factory) that leverage SQLiteAssetHelper. It also uses a forked copy of Jeff's classes, as SQLiteAssetHelper didn't support the right SQLiteOpenHelper constructor and had three methods marked final that SupportSQLiteOpenHelper.Callback needed.

To use it, you basically put the database in the spot documented for SQLiteAssetHelper (see

https://github.com/jgilfelt/android-sqlite-asset-helper), use openHelperFactory(new AssetSQLiteOpenHelperFactory()) in your RoomDatabase.Builder, and have your database name match the one in assets/. The sample app does all of that, with a bunch of constants copied from SensorManager.

(which, BTW, needs a GRAVITY_STARKILLER_BASE, now that I think of it...)

This most likely needs work for production use, but it demonstrates the basic capability. Of particular note, SQLiteAssetHelper's upgrade-the-database-from-assets probably doesn't work, as I would guess that Room's Migration-based system will take precedence, but that's all untested. YMMV.

Also note that by doing it this way, I didn't need the do-nothing migration discussed earlier in the issue. Room seemed fairly happy with a pre-populated database with no version and no checksum. ¯\_(ツ)_/¯

Everything is licensed Apache Software License 2.0, with copyright headers showing where the code came from.

AssetRoom-20170530.zip

46 KB

Download

de...@gmail.com <de...@gmail.com> #7Nov 29, 2017 01:07PM

It seems that class "FrameworkSQLiteDatabase" has been removed from Room library stable release.
So using the above given implementation with new library it throws compile time error.
Any workaround?

mm...@commonsware.com <mm...@commonsware.com> #8Nov 29, 2017 01:17PM

@7: I have no problems building and running the instrumentation tests for my AssetRoom book sample (

https://github.com/commonsguy/cw-androidarch/tree/v0.6/General/AssetRoom) with Room 1.0.0, and it references FrameworkSQLiteDatabase.

de...@gmail.com <de...@gmail.com> #9Nov 30, 2017 07:52AM

I am sorry. i didn't know "FrameworkSQLiteDatabase" is a private class in package "android.arch.persistence.db.framework" and interestingly i also didn't know that putting java classes inside a package named same as the library package would actually put the files in the library. Hence this way, you can access that private class. Hmm, very interesting.

st...@gmail.com <st...@gmail.com> #10May 28, 2018 03:11PM

I was able to copy over a pre-existing SQLite database into context.getDatabaseFile() and use it with Room (v1.1.0) without the extra SQLiteAssetHelper code and without the empty migration. However, it seems I have to make sure Room runs through the table creation first before copying the database file. If I didn't let the initial creation run, I got errors when createAllTables() ran, attempting to create indexes where they already exist. No CREATE IF NOT EXISTS clause for the indexes like there is for table creation.

Anyway, it would be nice to have an API that handled the pre-existing db condition :-)

st...@gmail.com <st...@gmail.com> #11May 28, 2018 05:10PM

Just did some more testing, looks like whenever the database connection is closed and I try to reopen it, Room thinks it needs to run through table creation again and it crashes attempting to create "duplicate" indexes. Looks like I'll have to do something similar to SQLiteAssetHelper :/

yb...@google.com <yb...@google.com> #12May 28, 2018 05:19PM

That is likely to happen because your custom version does not have the signature that room inserts.

I've not tried, but you can probably do this:

copy the db file on app creation, before creating room. Give it version 1
Open room db with version 2
provide an empty migration from 1 to 2.

lmk if tha tworks.

st...@gmail.com <st...@gmail.com> #13May 28, 2018 05:54PM

Looks like it worked! Tried it briefly after reading the above conversation but it looks like the db version was set to '0' by the desktop application that generated the db file, causing the migration from 1 to 2 to fail the first time I tried...

I'll still have to do some manual table editing on import/export to keep things in sync between the two apps I'm working with, but I think this will work for now.

Thanks!

sh...@gmail.com <sh...@gmail.com> #14Mar 5, 2019 10:09AM

Take your time google. Wait a couple of years more for this feature.

da...@google.com <da...@google.com> Mar 5, 2019 06:14PM

Reassigned to da...@google.com.

ge...@gmail.com <ge...@gmail.com> #15May 5, 2019 10:04PM

Any update on this? Should be a priority.

ma...@marcardar.com <ma...@marcardar.com> #16May 6, 2019 02:17AM

Out of interest, is there any technical barrier to having a read-only Room database from being accessed directly in assets (note: those are not compressed) without having to copy it to storage?

vi...@google.com <vi...@google.com> #17May 8, 2019 05:55AM

Your valuable inputs are awaited in this bug.

mo...@gmail.com <mo...@gmail.com> #18May 21, 2019 03:26AM

I kept tracking on this issue since years, but no update from Google side :( How much time needed for this feature to integrate with ROOM Library.?

da...@google.com <da...@google.com> #19May 21, 2019 03:39PM

This is on the road map along with other migration improvements. Sorry that it hadn't been a prioritization since there are workarounds.

sh...@gmail.com <sh...@gmail.com> #20May 22, 2019 03:04AM

Kindly mention those workarounds for the benefit of all affected.

ap...@google.com <ap...@google.com> #21May 28, 2019 07:05PM

Project: platform/frameworks/support
Branch: androidx-master-dev

commit 54fa08a7bb20a88aa5266736d0183acc4dc66e89
Author: Daniel Santiago Rivera <danysantiago@google.com>
Date: Sat May 25 16:14:28 2019

Use IF NOT EXISTS for Index create statement.

Bug: 62185732
Test: room:room-compiler:test
Change-Id: Ia58a4f4188425c2861cb14792b3e00ea83495bfb

M room/compiler/src/main/kotlin/androidx/room/vo/Index.kt
M room/compiler/src/test/kotlin/androidx/room/vo/IndexTest.kt
M room/integration-tests/kotlintestapp/schemas/androidx.room.integration.kotlintestapp.migration.MigrationDbKotlin/7.json

https://android-review.googlesource.com/971064

https://goto.google.com/android-sha1/54fa08a7bb20a88aa5266736d0183acc4dc66e89

ap...@google.com <ap...@google.com> #22Jun 7, 2019 11:18PM

Project: platform/frameworks/support
Branch: androidx-master-dev

commit e70c2aa60c0c0324cf2ce9ed90d6847ac04cb789
Author: Daniel Santiago Rivera <danysantiago@google.com>
Date: Sat May 25 16:37:56 2019

Create API for using Room with a pre-populated database.

The builder methods createFromAsset() and createFromFile() allows the
user to configure Room to use a pre-packaged database if the DB is being
opened for the first time ever. With fromAsset the DB file has to be
placed in the 'assets/' folder of the APK. With fromFile Room will try
to locate the file in external storage. As long as the right permissions
are available Room will be able to utilize the external storage path.

If the pre-packaged database file is an older version, then Room will
go through the upgrade flow as usual.

The pre-packaged database does not need to contain Room metadata table,
upon being opened Room will validate the schema and create it.

Bug: 62185732
Test: room:integration-tests:room-testapp:cC
Change-Id: I5d84a48f8511bcf3adaaaf6b8bf4073843f01ac7

M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/IdentityDetectionTest.java
A room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/PrepackageTest.java
A room/integration-tests/testapp/src/main/assets/databases/products_badFile.db
A room/integration-tests/testapp/src/main/assets/databases/products_badSchema.db
A room/integration-tests/testapp/src/main/assets/databases/products_v0.db
A room/integration-tests/testapp/src/main/assets/databases/products_v0_badSchema.db
A room/integration-tests/testapp/src/main/assets/databases/products_v1.db
M room/runtime/api/2.2.0-alpha01.txt
M room/runtime/api/current.txt
M room/runtime/api/restricted_2.2.0-alpha01.txt
M room/runtime/api/restricted_current.txt
M room/runtime/src/main/java/androidx/room/DatabaseConfiguration.java
M room/runtime/src/main/java/androidx/room/RoomDatabase.java
M room/runtime/src/main/java/androidx/room/RoomOpenHelper.java
A room/runtime/src/main/java/androidx/room/SQLiteCopyOpenHelper.java
A room/runtime/src/main/java/androidx/room/SQLiteCopyOpenHelperFactory.java
M room/testing/src/main/java/androidx/room/testing/MigrationTestHelper.java

https://android-review.googlesource.com/971065

https://goto.google.com/android-sha1/e70c2aa60c0c0324cf2ce9ed90d6847ac04cb789

ma...@marcardar.com <ma...@marcardar.com> #23Jun 8, 2019 06:28AM

Is there any technical barrier to having a read-only Room database from being accessed directly in assets (note: those are not compressed) without having to copy it to storage?

da...@google.com <da...@google.com> #24Jun 10, 2019 04:14PM

There are some barriers, with both the Assets API and SQLite, I've created a separate issue for you

http://issuetracker.google.com/134928363 with your specific request since even though it is similar to this issue it is asking for something different.

ap...@google.com <ap...@google.com> #25Jun 14, 2019 09:35PM

Project: platform/frameworks/support
Branch: androidx-master-dev

commit 19fcda5e8d0af108b47c0f740bc7a82e02406131
Author: Daniel Santiago Rivera <danysantiago@google.com>
Date: Fri May 31 15:04:57 2019

Use pre-populated DB on destructive migration.

When fallback to destructive migration is enabled and the database was
created using the createFromAsset or createFromFile APIs then try to
re-copy the pre-populated database when the database is being upgraded
and has no migration path.

Bug: 62185732
Test: room:integration-tests:room-testapp:cC
Change-Id: I2c6e56176c4feb3301790877c610ea7c5bf58b5e

M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/PrepackageTest.java
A room/integration-tests/testapp/src/main/assets/databases/products_v2.db
M room/runtime/api/restricted_2.2.0-alpha01.txt
M room/runtime/api/restricted_current.txt
M room/runtime/src/main/java/androidx/room/RoomDatabase.java
M room/runtime/src/main/java/androidx/room/SQLiteCopyOpenHelper.java
M room/runtime/src/main/java/androidx/room/SQLiteCopyOpenHelperFactory.java
M room/runtime/src/main/java/androidx/room/util/DBUtil.java

https://android-review.googlesource.com/974763

https://goto.google.com/android-sha1/19fcda5e8d0af108b47c0f740bc7a82e02406131

ap...@google.com <ap...@google.com> #26Jun 21, 2019 05:09PM

Project: platform/frameworks/support
Branch: androidx-master-dev

commit f0505b73efc88f23344e70fab11f347e8e00dc02
Author: Daniel Santiago Rivera <danysantiago@google.com>
Date: Thu Jun 20 13:01:39 2019

Better error message for pre-package DB invalid schema.

Deprecate validateMigration in favor of onValidateSchema to be able to
perform schema validation in other cases other than migrations. The
new method returns a ValidationResult that indicates if the schema is
valid along with an expected-vs-found message to help fix the schema.
Caller then throws with appropriate message pointing to either migration
or pre-populate database validation.

Compatibility with already generate code that uses the the newer
RoomOpenHelper API is taken into consideration. Specifically, already
generate code don't need to implement new method since it has default
implementation. Meanwhile newly generated code doesn't have to override
old method since it has become non-abstract, an API binary compatible
change.

Test: Updated Tests
Bug: 62185732
Change-Id: If53a2a8a71826de4a1d9c97bd8f20796ea8eb594

M room/compiler/src/main/kotlin/androidx/room/ext/javapoet_ext.kt
M room/compiler/src/main/kotlin/androidx/room/writer/FtsTableInfoValidationWriter.kt
M room/compiler/src/main/kotlin/androidx/room/writer/SQLiteOpenHelperWriter.kt
M room/compiler/src/main/kotlin/androidx/room/writer/TableInfoValidationWriter.kt
M room/compiler/src/main/kotlin/androidx/room/writer/ViewInfoValidationWriter.kt
M room/compiler/src/test/data/databasewriter/output/ComplexDatabase.java
M room/integration-tests/kotlintestapp/src/androidTest/java/androidx/room/integration/kotlintestapp/migration/MigrationKotlinTest.kt
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/migration/MigrationTest.java
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/PrepackageTest.java
M room/runtime/api/restricted_2.2.0-alpha01.txt
M room/runtime/api/restricted_current.txt
M room/runtime/src/main/java/androidx/room/RoomOpenHelper.java
M room/testing/src/main/java/androidx/room/testing/MigrationTestHelper.java

https://android-review.googlesource.com/987224

https://goto.google.com/android-sha1/f0505b73efc88f23344e70fab11f347e8e00dc02

ap...@google.com <ap...@google.com> #27Jun 26, 2019 05:20PM

Project: platform/frameworks/support
Branch: androidx-master-dev

commit 72b111275fd5df454834c026e45db9ec087c6b32
Author: Daniel Santiago Rivera <danysantiago@google.com>
Date: Fri Jun 14 11:43:51 2019

Implement a copy lock mechanism for pre-populated databases.

Adds a two layer locking mechanism to protect the copy operation from
occurring concurrently within the same process or another process. The
idea is that the copy operation is lock protected and if there is
contention only one copy operation should occur.

Bug: 62185732
Bug: 135743327
Test: SQLiteCopyOpenHelperTest & PrepackageTest
Change-Id: I8013de92e83e016f0d3dec111e459ddac4a65513

M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/PrepackageTest.java
A room/integration-tests/testapp/src/main/assets/databases/products_big.db
M room/runtime/api/restricted_2.2.0-alpha01.txt
M room/runtime/api/restricted_current.txt
M room/runtime/src/main/java/androidx/room/RoomDatabase.java
M room/runtime/src/main/java/androidx/room/SQLiteCopyOpenHelper.java
A room/runtime/src/main/java/androidx/room/util/CopyLock.java
M room/runtime/src/main/java/androidx/room/util/DBUtil.java
A room/runtime/src/main/java/androidx/room/util/FileUtil.java
A room/runtime/src/test/java/androidx/room/SQLiteCopyOpenHelperTest.kt

https://android-review.googlesource.com/984409

https://goto.google.com/android-sha1/72b111275fd5df454834c026e45db9ec087c6b32

da...@google.com <da...@google.com> Jun 26, 2019 05:36PM

Marked as fixed.