Details

Reviewers

kamil
tomek
marcin

Commits

rCOMM2ea66c63800b: [SQLite] Add target_message generated column and index

Summary

Generated colums in SQLite are described this way in the docs:

Generated columns (also sometimes called "computed columns") are columns of a table whose values are a function of other columns in the same row. Generated columns can be read, but their values can not be directly written. The only way to change the value of a generated column is to modify the values of the other columns used to calculate the generated column.

I found them from this blog post, which explains how to use a generated column and index to efficiently query on the contents of a JSON column in SQLite. I found that blog post from this Reddit comment.

Test Plan

I confirmed I could compile and run this on both iOS and web
I checked the EXPLAIN QUERY PLAN for the query I use this index for (in a later diff) and it showed the index being used:

Screenshot 2024-07-15 at 5.15.00 PM.png (654×1 px, 314 KB)

Diff Detail

Repository

rCOMM Comm

Lint

Lint Not Applicable

Unit

Tests Not Applicable

Event Timeline

ashoat created this revision.Jul 15 2024, 2:15 PM

ashoat edited the test plan for this revision. (Show Details)Jul 15 2024, 2:16 PM

ashoat added a child revision: D12760: [SQLite] Factor out SQLiteQueryExecutor::processMessagesResults.Jul 15 2024, 2:16 PM

ashoat added inline comments.Jul 15 2024, 2:25 PM

native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp
1401 ↗	(On Diff #42302)	Since we added a new column, all the `LEFT JOIN`'d media got shifted to the right We could avoid having to do this if we replaced `SELECT *` with an explicit list on line 1384. Open to doing that if people think it's a good idea

Harbormaster returned this revision to the author for changes because remote builds failed.Jul 15 2024, 2:34 PM

Harbormaster failed remote builds in B30340: Diff 42302!

ashoat edited parent revisions, added: D12735: [native] Introduce MessageEntity type alias in more places; removed: D12740: [lib] Introduce getRelatedMessages to SQLiteAPI.Jul 15 2024, 7:07 PM

ashoat edited the summary of this revision. (Show Details)

Rebase, move it earlier in the stack, fix missing comma in create_schema for messages columns

Fix all the tabs and 3-spaces issues in SQLiteQueryExecutor.cpp

ashoat added inline comments.Jul 15 2024, 7:23 PM

native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp
1401 ↗	(On Diff #42313)	Since we added a new column, all the `LEFT JOIN`'d media got shifted to the right We could avoid having to do this if we replaced `SELECT *` with an explicit list on line 1384. Open to doing that if people think it's a good idea

Harbormaster completed remote builds in B30345: Diff 42307.Jul 15 2024, 7:29 PM

WASM update

ashoat added inline comments.Jul 15 2024, 8:03 PM

native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp
1401 ↗	(On Diff #42321)	Since we added a new column, all the `LEFT JOIN`'d media got shifted to the right We could avoid having to do this if we replaced `SELECT *` with an explicit list on line 1384. Open to doing that if people think it's a good idea