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

Is there any hope that this will be looked at any time soon?
I find it pretty disappointing that there is no way to debug queries. I am using Room in an application with foreign key relationships, and these are failing silently and for some mysterious reason, and I have no way to debug my queries other than manually adding a bunch of LogCat logs.
Thank you for considering this.

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

Hello

Room database looks very promising and interesting, But I am still on initial stage of learning it and I am facing the problem of not being able to see log outputs of the database.
While implementing / learning debugging should be enabled.

It would be extremely helpful if we can have debugging logs for Room database implementation.

Thanks and regards
Abhiroop Nandi Ray

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

Assigned to yb...@google.com.

sorry, we are not agianst this feature but it is just not a high priority right now.
You can technically implement this outside room by providing your logging support sqlite open helper.

you can also consider sending a CL ? :)

https://android.googlesource.com/platform/frameworks/support/+/androidx-master-dev

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

I have been able to achieve it via a very hackie way
Please refer to

https://stackoverflow.com/questions/46172417/android-room-database-dao-debug-log/53693849#53693849
or

https://gist.github.com/aasaandinesh/6e4cf206bd1bb56966954cabc42128dd

Will try to do it in a proper way

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

You can log Room's Sqlite queries on emulator, see:

https://stackoverflow.com/a/54963655/1233652

AssetRoom-20170530.zip

46 KB

Download

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

This is needed for asserting query execution and increasing the speed of adoption of the framework. Please prioritize higher as simple as it is.

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

Please, it would be very helpful for developers to be able to debug room operations in an easy way.

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

Please, it would be very helpful for a more complicated erd, prioritize higher as soon as possible.

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

this looks like a perfect candidate for external contribution.

It is also probably better to implement this in the androidx.sqlite package instead to ensure all queries are covered.

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

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

commit ab6d205c0217a0ed5c170bf6ceadb20422947699
Author: Elif Bilgin <elifbilgin@google.com>
Date: Tue Nov 17 13:50:26 2020

Implementing functionality for a general callback function for SQLite queries. If possible, bind arguments are provided to the callback in addition to the SQLite query statement. This callback may be used for logging executed queries, in which case it is recommended to use an immediate executor.

This CL will be followed up by a CL resolving

b/174478034

.

Test: Tests have been added QueryInterceptorTest.kt

Relnote: Add query callback feature to Room.

Bug: 74877608
Change-Id: Iaa513e39115f0c9c68359774fa70e1d3dd022c39

A room/integration-tests/kotlintestapp/src/androidTest/java/androidx/room/integration/kotlintestapp/test/QueryInterceptorTest.kt
M room/runtime/api/current.txt
M room/runtime/api/public_plus_experimental_current.txt
M room/runtime/api/restricted_current.txt
A room/runtime/src/main/java/androidx/room/QueryInterceptorDatabase.java
A room/runtime/src/main/java/androidx/room/QueryInterceptorOpenHelper.java
A room/runtime/src/main/java/androidx/room/QueryInterceptorOpenHelperFactory.java
A room/runtime/src/main/java/androidx/room/QueryInterceptorStatement.java
M room/runtime/src/main/java/androidx/room/RoomDatabase.java

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

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.