Skip to content

DB redesign

Ruben De Smet requested to merge db-redesign into master

Intends to fix #211 (closed), #39 (closed) .

This is a rebase of a series of commits of the UUID merge request, specifically the ones that introduce database migration testing, and the more standard-adhering SQLite code: eb760c05..07751ce2

First phase

  • Goal: get the diesel print-scheme as close to our real scheme.rs as possible, by strengthening the used Sqlite types.
    • Split the Diesel schema into our own and the contact scheme (schema.rs and contacts_schema.rs).
    • Introduces a more typed database (eb760c05..07751ce2), dropping a lot of NULLability, using TIMESTAMP (in ISO 8601) instead of milli's-since-Unix-epoch-as-a-bigint.
    • Introduce a few FOREIGN KEYs, where already possible.
  • Tests:
    • Timestamp conversion stress test

Second phase

Goal: introduce new features into the database scheme. Notably: UUID and GroupV2. Normal form™. This needs to be one big migration, because we want it to happen inside a single transaction.

  • Rebase on !123 (merged)
  • Decide what to do with attachments
  • Decide what to do with reactions. Signal Android puts them in the "SmsDatabase" as a blob.
  • Decide what to do with quotes.
  • Split of group members into a real table
  • Make UUID and phone number strong types for Diesel. -> #276 (closed)
  • Draft database (towards #178 (closed))
  • Sticker pack table #14
  • ~~Figure out how to store "special" messages (megaphone messages) -> #277
  • Pass over all BOOLEAN fields and prepend is_.
  • Per-recipient read status for group messages for #238
  • Tests:
    • We introduce three timestamps per message, check that the right ones are populated and the others are empty.
    • Migration test executable to dry-run migrations on the device itself.
  • Cross check with Android: rg "CREATE INDEX"
  • Check that all _ids have a FOREIGN KEY
  • CHECK constraint to assert that inbound group messages have a sender_recipient_id. -> this is a join-check, which isn't really possible with CHECK, make it a trigger instead.
    • Write a failure-test for this trigger
  • Migrate receipts for group messages. We can just "assume" that everyone has sent a receipt. It's the closest we'll get.

Third phase

Tests:

  • Groups should end up in the V1 table. Test these:
    • One single group with a few members.
    • Disjunct groups
    • Overlapping groups (assert that e164 is still unique in recipients)
  • Attachments should end up correctly in the attachments table. Messages without attachments should not end up there, so assert this.

Misc

  • CI/CD: consider a print-schema test, that runs diesel print-schema; cargo fmt -- src/schema.rs; git diff --status-code. This enforces up-to-date schema.{patch,rs}.
  • Make a Rust-migration that turns @-reactions into real reactions. #99
  • Git history: there's been a touching of schema.rs before the app was actually reworked. Rewrite the history until we're actually touching the app. (Ie., don't touch schema after 95a2d3e7 "CI: Diesel print-schema test")
  • Can we somehow assert that all messages have been migrated? -> dry run
  • How do we migrate sentq?
  • Change the dry-run such that it shows which constraints fail.
  • Fix @deryo's constraint failure. (not sure what fixed it, but the last few dry runs work)
  • Go over the introduced XXX's and make issues/threads/todo's for them.
  • Sync messages don't get a sent timestamp for some reason.
  • Sending group messages fails on 
    [2021-03-20T19:27:53Z TRACE harbour_whisperfish::worker::client] Sending for session: Session { id: 39, is_archived: false, is_pinned: false, is_silent: false, is_muted: false, expiring_message_timeout: None, draft: None, type: DirectMessage(Recipient { id: 44, e164: Some(""), uuid: None, username: None, email: None, blocked: false, profile_key: None, profile_key_credential: None, profile_given_name: None, profile_family_name: None, profile_joined_name: None, signal_profile_avatar: None, profile_sharing: false, last_profile_fetch: None, unidentified_access_mode: false, storage_service_id: None, storage_proto: None, capabilities: 0, last_gv1_migrate_reminder: None, last_session_reset: None }) }
[2021-03-20T19:27:53Z TRACE harbour_whisperfish::worker::client] Sending message: Message { id: 17668, session_id: 39, text: Some("Toet"), sender_recipient_id: Some(44), received_timestamp: Some(2021-03-20T19:27:53.764865311), sent_timestamp: None, server_timestamp: 2021-03-20T19:27:53.701148376, is_read: true, is_outbound: true, flags: 0, expires_in: None, expiry_started: None, schedule_send_time: None, is_bookmarked: false, use_unidentified: false, is_remote_deleted: false }
[2021-03-20T19:27:53Z TRACE harbour_whisperfish::worker::client] Transmitting DataMessage { body: Some("Toet"), attachments: [], group: None, group_v2: None, flags: None, expire_timer: None, profile_key: None, timestamp: Some(1616268473701), quote: None, contact: [], preview: [], sticker: None, required_protocol_version: Some(0), is_view_once: None, reaction: None, delete: None, body_ranges: [] }
thread 'main' panicked at 'only valid phone number in db: NoNumber', src/store/orm.rs:77:60

QML binding

  • Session list should be sorted again against the latest message.
  • Sending attachments doesn't seem to work
  • Receiving attachments should work, but isn't yet displayed.
  • Re-expose group members

Milestone %v0.6.0-beta.1: group management, Group V2 & UUID because this is a precondition for UUID sessions.

Edited by Ruben De Smet

Merge request reports