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

Actually that was an experimental API we thought about removing a couple of times but decided to keep for now.
About 1-1, it is actually better to write them as join queries. Is there a case where join is not feasible?

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

The issue with a JOIN is not feasibility. I suspect that can be done, albeit at the cost of yet another non-entity POJO.

Let's flip it around: given your characterization of the origins of @Relation ("an experimental API") and its possible lame-duck status ("decided to keep for now"), should we be using it?

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

Assigned to yb...@google.com.

How would I load all the relation objects immediately without a relation? I see in your documentation that you have a query like "@Query("SELECT

user.name AS userName,

pet.name AS petName "
+ "FROM user, pet "
+ "WHERE

user.id = pet.user_id")
public LiveData<List<UserPet>> loadUserAndPetNames();", but how would you load complete objects with it, not just certain fields? I tried using something like "SELECT * , * as comments FROM users, comments", but it was throwing an error?

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

I'm interested in this feature along with the ability to use a projection like "[expression] AS [column-name]" and map to a single value. Additionally, since the @Relation annotation is never mentioned on the Room page, will it be maintained?

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

It will be maintained, and added to the Room page.

AssetRoom-20170530.zip

46 KB

Download

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

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

commit c5dad6522e6c20bbd5ca8e53b8406a94cb84cfe8
Author: Daniel Santiago Rivera <danysantiago@google.com>
Date: Wed Jun 12 16:43:53 2019

Allow single-value @Relation field.

This change lifts the restriction of @Relation annotated fields needing
to be List or Set allowing for a more seamless representation of a
one-to-one relationship.

Changes in the generated code is so that it is not always assumed that
the relation field is a collection, thus not needing a temporary
collection when collecting the relating objects.

Bug: 62905145
Test: ./gradlew room:room-compiler:test \
room:integration-tests:room-testapp:cC

Change-Id: I462d59e89b55c43842df43cb84c1aeca0b2b743e

M room/common/src/main/java/androidx/room/Relation.java
M room/compiler/src/main/kotlin/androidx/room/processor/PojoProcessor.kt
M room/compiler/src/main/kotlin/androidx/room/processor/ProcessorErrors.kt
M room/compiler/src/main/kotlin/androidx/room/vo/RelationCollector.kt
M room/compiler/src/main/kotlin/androidx/room/writer/RelationCollectorMethodWriter.kt
M room/compiler/src/test/kotlin/androidx/room/processor/PojoProcessorTest.kt
M room/compiler/src/test/kotlin/androidx/room/processor/QueryMethodProcessorTest.kt
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/dao/PetDao.java
M room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/test/PojoWithRelationTest.java
A room/integration-tests/testapp/src/androidTest/java/androidx/room/integration/testapp/vo/PetAndOwner.java

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

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

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.