SQLite queries return stale table metadata after alter operation #529

JaniruTEC · 2024-04-10T17:11:17Z

Please agree to the following

I have searched existing issues for duplicates
I agree to follow this project's Code of Conduct

Summary

In rare cases queries to a recently changed table will return stale table metadata (e.g. column names). This might cause difficult to diagnose bugs during database migration. So far this issue hasn't been reported in production, but was encountered during development on #506.

System Setup

...

Cloud Type

No response

Steps to Reproduce

...

Expected Behavior

...

Actual Behavior

...

Reproducibility

Always

Relevant Log Output

No response

Anything else?

Related bug on the google issue tracker: 153521693

UpgradeDatabaseTest.kt:816 and UpgradeDatabaseTest.kt:895 caused the tests to fail because of issue #529 [1]. Consequently they have been commented out until the issue is resolved. [1] #529

Copied from the Android-Issue-153521693 project [2] *This commit is related to issue #529 [1]* [1] #529 [2] https://github.com/SailReal/Android-Issue-153521693/tree/cfb5033cf287572ac4b4972700f1656ce2b61d03/src/androidTest/java/de/skymatic/android_issue153521693

*This commit is related to issue #529 [1]* [1] #529

Room is designed to choose the journal mode itself if none is specified. greenDAO's journal mode ("TRUNCATE") will stay for now because the journal mode has influence on the behavior caused by issue #529 [1] and because changing the journal mode complicates the migration further. This can still be changed later. *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

Replaced particular implementation of "fix" with call to freshly introduced, user-supplied mapping function (and added "RandomUUIDSQLMappingFunction" as new mapping function with that particular implementation) Replaced "fixCompile" with direct instantiation of "CacheControlledSupportSQLiteStatement" Updated static elements to be inner members of "CacheControlledSupportSQLiteDatabase" (and removed now unnecessary properties) Changed computation logic of "sql" property in "CacheControlledSupportSQLiteQuery" *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

Renamed package "org.cryptomator.data.db.cachecontrol" to "org.cryptomator.data.db.sqlmapping" Renamed "CacheControlledSupportSQLiteDatabase.kt" to "MappingSupportSQLiteDatabase.kt" Moved contents to new location *This commit is related to issue #529 [1]* [1] #529

Renamed class "CacheControlledSupportSQLiteDatabase" to "MappingSupportSQLiteDatabase" Renamed class "CacheControlledSupportSQLiteOpenHelper" to "MappingSupportSQLiteOpenHelper" Renamed class "CacheControlledSupportSQLiteOpenHelperFactory" to "MappingSupportSQLiteOpenHelperFactory" Renamed inner class "CacheControlledSupportSQLiteStatement" to "MappingSupportSQLiteStatement" Renamed inner class "CacheControlledSupportSQLiteQuery" to "MappingSupportSQLiteQuery" Renamed method "fix" to "map" in "MappingSupportSQLiteDatabase" Renamed method "fixWhereClause" to "mapWhereClause" in "MappingSupportSQLiteDatabase" Renamed property "identifiers" to "mappedQueries" in "MappingSupportSQLiteQuery" Renamed extension method "SupportSQLiteOpenHelper.Factory.asCacheControlled" to "SupportSQLiteOpenHelper.Factory.asMapped" in "MappingSupportSQLiteDatabase.kt" *This commit is related to issue #529 [1]* [1] #529

Moved "RandomUUIDSQLMappingFunction" from "MappingSupportSQLiteDatabase.kt" to new file "SQLiteCacheControl.kt" Added extension function "SupportSQLiteOpenHelper.Factory.asCacheControlled" in "SQLiteCacheControl.kt" as wrapper for "asMapped" with "RandomUUIDSQLMappingFunction" as mapping function Removed "RandomUUIDSQLMappingFunction" as default mapping function from extension function "SupportSQLiteOpenHelper.Factory.asMapped" in "MappingSupportSQLiteDatabase.kt" Replaced call to "asMapped" with call to "asCacheControlled" in "DatabaseModule" *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

See: c13b12f *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

Calling "requery" on a cursor may cause the driver to re-use queries, which is unwanted behavior. *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

Also moved compat method for "ContentValues.isEmpty" *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

This also makes the class compatible with "MappingSupportSQLiteDatabase" *This commit is related to issue #529 [1]* [1] #529

This also makes the test method compatible with "MappingSupportSQLiteDatabase" Also added "assertIsUUID" *This commit is related to issue #529 [1]* [1] #529

See: b3449f0 Also See: c13b12f *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

Added "ValueExtractor<T>" as type alias for "(T) -> Any?" Changed argument declarations of "pseudo equality" methods and underlying implementations Added "ValueExtractor<T>" as type declaration for existing value extractor sets *This commit is related to issue #529 [1]* [1] #529

Also added workaround for tests *This commit is related to issue #529 [1]* [1] #529

Changed "insert" to directly compile the mapped sql instead of creating a "MappingSupportSQLiteStatement" Added check if the database is open to "compileStatement" *This commit is related to issue #529 [1]* [1] #529

…iteDatabase" *This commit is related to issue #529 [1]* [1] #529

Moved tests from android tests to unit tests Renamed containing class from "DatabaseOpenHelperFactoryTest" to "BomVerificationTest" Fixed failing tests due to differences in JDK distros *This commit is related to issue #529 [1]* [1] #529

Added sorting of BOMs to ease debugging Added check for classes that can't be verified by a unit test *This commit is related to issue #529 [1]* [1] #529

Swapped bindings storage from lambdas to actual values Added additional copy-operation to ByteArray-bindings to prevent downstream changes Added utility methods *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

Moved call to "asCacheControlled" for singleton instance of "DatabaseOpenHelperFactory" from "DatabaseModule.provideInternalCryptomatorDatabase" to new binding "DatabaseModule.provideOpenHelperFactory" to prevent unnecessary duplicate invocations *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

Moved WAL initialization from the "onConfigure" methods (via "applyDefaultConfiguration") of the database configurations to the corresponding instances of "SupportSQLiteOpenHelper" Updated extension method "applyDefaultConfiguration" for "SupportSQLiteDatabase" defined in "CryptomatorDatabase.kt" to no longer configure WAL but verify that it has been set properly in the corresponding "SupportSQLiteOpenHelper" *This commit is related to issue #529 [1]* [1] #529

Added comments and documentation Simplified implementation for "RandomUUIDMapping.mapWhereClause" Replaced import alias "org.mockito.Mockito.`when` as whenCalled" with built-in "org.mockito.kotlin.whenever" in "MappingSupportSQLiteDatabaseTest.kt" *This commit is related to issue #529 [1]* [1] #529

*This commit is related to issue #529 [1]* [1] #529

@VisibleForTesting

Added "@VisibleForTesting" to "MappingSupportSQLiteQuery" and changed its visibility to internal *This commit is related to issue #529 [1]* [1] #529

Moved logic for configuring a test database, a "MigrationContainer" and creating a v0 database to "DatabaseTests.kt" *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added the type:bug Something isn't working label Apr 10, 2024

JaniruTEC changed the title ~~SQLite queries return stale table metadata after update~~ SQLite queries return stale table metadata after alter operation Apr 10, 2024

JaniruTEC added a commit that referenced this issue Apr 10, 2024

Applied code style to BugTest.kt and SmallBugTest.kt

eb02fc0

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 10, 2024

Finalized work on BugTest.kt and SmallBugTest.kt

7b78b92

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Added first implementation of "CacheControlledSupportSQLiteDatabase"

ff67959

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Narrowed visibility for members of "AOP_SQLiteDatabase" class

7d5de82

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Renamed "RandomUUIDSQLMappingFunction" to "RandomUUIDMapping"

eae6c03

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Refactored "SQLMappingFunction"

35b4e00

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Redefined logic of "MappingSupportSQLiteQuery"

95eede5

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Re-enabled tests

b3449f0

See: c13b12f *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Added first tests for "MappingSupportSQLiteDatabase"

6b1a9b5

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Added support for mapping cursors

af17db5

Calling "requery" on a cursor may cause the driver to re-use queries, which is unwanted behavior. *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Renamed "DataForTestQueryCancelable"

34f75af

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Extracted methods "cartesianProduct" and "toArgumentsStream"

ed0fe82

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Added test for "update"

c89f4b1

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Specified contract for "SupportSQLiteDatabase.insert"

d98ce8b

Also moved compat method for "ContentValues.isEmpty" *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 13, 2024

Added tests for "insert"

bfcca43

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 21, 2024

Refactored "Sql" to follow the API contract of "SupportSQLiteDatabase"

6af9811

This also makes the class compatible with "MappingSupportSQLiteDatabase" *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 21, 2024

Fixed "migrate12To14IndexSideEffects"

abf19e2

This also makes the test method compatible with "MappingSupportSQLiteDatabase" Also added "assertIsUUID" *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 21, 2024

Applied fix for #529 [1] to re-enabled tests

0e63cad

See: b3449f0 Also See: c13b12f *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 22, 2024

Moved logic for handling extra calls to dedicated class "OneOffDelegate"

9f70454

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 22, 2024

Implemented "MappingSupportSQLiteQuery.bindTo" with "OneOffDelegate"

b691766

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Apr 22, 2024

Enforced single call convention for parts of "MappingSupportSQLiteQuery"

cfde324

Also added workaround for tests *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Jun 4, 2024

Added tests for verifying BOMs for dependencies of "MappingSupportSQL…

93417c3

…iteDatabase" *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Jun 4, 2024

Improved tests for verifying BOMs

a198130

Added sorting of BOMs to ease debugging Added check for classes that can't be verified by a unit test *This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Sep 25, 2024

Made "MappingSupportSQLiteStatement" threadsafe

5588563

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Sep 25, 2024

Added missing WAL configuration/verification

26bad8d

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Sep 25, 2024

Moved "pseudo equality" methods to dedicated class

2c17283

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Sep 25, 2024

Moved helper methods for "Iterables" to dedicated class

c581e77

*This commit is related to issue #529 [1]* [1] #529

JaniruTEC added a commit that referenced this issue Sep 25, 2024

Added type-checks to tests for "MappingSupportSQLiteQuery"

3f35615

Added "@VisibleForTesting" to "MappingSupportSQLiteQuery" and changed its visibility to internal *This commit is related to issue #529 [1]* [1] #529

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQLite queries return stale table metadata after alter operation #529

SQLite queries return stale table metadata after alter operation #529

JaniruTEC commented Apr 10, 2024 •

edited

Loading

SQLite queries return stale table metadata after alter operation #529

SQLite queries return stale table metadata after alter operation #529

Comments

JaniruTEC commented Apr 10, 2024 • edited Loading

Please agree to the following

Summary

System Setup

Cloud Type

Steps to Reproduce

Expected Behavior

Actual Behavior

Reproducibility

Relevant Log Output

Anything else?

JaniruTEC commented Apr 10, 2024 •

edited

Loading