lrc: migrate database to new schema

Which new schema?

@sblin major changes:

• per account db
• avoid translated strings in the db

changed milestone to %Iteration 3

changed milestone to %Iteration 4

added 1 deleted label

changed milestone to %Iteration 5

changed milestone to %Iteration 6

changed milestone to %Iteration 8

changed milestone to %Iteration 9

removed due date

changed milestone to %Iteration 10

Did not you forget to add an autoanswer flag per contact in the new schema? :)

The autoanswer flag is currently kept in the config.yml for each account. Unless we want the autoanswer flag to be per contact ?

Yes, per contact. It was mentioned in the ring-daemon#72, and I think it is the right approach.

And what about a field "user-supplied name" for conversations (ring-client-gnome#952)?

@Vladimir Yes we are designing a way to support any number of per-contact preferences and easily add new ones.

And don't forget about group chats in the future. So I think should be different lists for conversations and contacts.

Sorry for many comments, but Jami really need all these features.

LRC local data storage

Abstract

A database will be created for each account and used by LRC to retrieve information pertinent to organizing conversations and their respective interactions. The vCards of peers are stored in files containing extra profile data that may synchronized via during calls and contact requests. Jami vCards will include avatars and display names and may include other meta-data in the future.

1. Database schema

Table 1: Conversations

Table conversations {
  id INT [pk]
  participant TEXT  // uri of the participant
  extra_data TEXT   // JSON reserved for metadata
}

extra_data {}

index on participant

Table 2: Interactions

Table interactions {
  id INT [pk]
  author TEXT       // uri of author (empty if outgoing)
  conversation INT  // conversation's id
  timestamp INT     // time of interaction
  body TEXT         // msg body
  type TEXT         // text representation of interaction::Type
  status TEXT       // text representation of interaction::Status
  is_read INT       // non-zero if the interaction has been read
  daemon_id BIGINT  // internal message id
  extra_data TEXT   // JSON reserved for metadata
}

extra_data {
    duration        // duration of call in seconds (interactions of type:CALL)
}

foreign key bind: interactions.conversation > conversations.id

index on (author, timestamp)

2. Enumerations

enum class Type {
    INVALID,
    TEXT,
    CALL,
    CONTACT,
    DATA_TRANSFER,
    COUNT__
}

enum class Status {
    INVALID,
    UNKNOWN,
    SENDING,
    FAILURE,
    SUCCESS,
    TRANSFER_CREATED,
    TRANSFER_ACCEPTED,
    TRANSFER_CANCELED,
    TRANSFER_ERROR,
    TRANSFER_UNJOINABLE_PEER,
    TRANSFER_ONGOING,
    TRANSFER_AWAITING_PEER,
    TRANSFER_AWAITING_HOST,
    TRANSFER_TIMEOUT_EXPIRED,
    TRANSFER_FINISHED,
    COUNT__
}

3. File structure

{local_storage}/jami
└── {account_id}
    ├── config.yml
    ├── contacts
    ├── export.gz
    ├── incomingTrustRequests
    ├── knownDevicesNames
    ├── history.db              <-- conversations and interactions database
    ├── profile.vcf             <-- account vcard
    ├── profiles                <-- account contact vcards
    │   │── {contact_uri}.vcf
    │   └── ...
    ├── ring_device.crt
    └── ring_device.key

4. Contact URIs

The format of contact URIs used for vcard filenames and stored in the database in conversations.participant and interactions.author will be:

• for Jami account contacts
  • a partial uri with no scheme prefix
  • the hostname is omitted
  • examples:
    • file: 3d1112ab2bb089370c0744a44bbbb0786418d40b.vcf
    • db: 3d1112ab2bb089370c0744a44bbbb0786418d40b
• for SIP account contacts
  • if the hostname is not provided, the account's hostname will be added
  • no port will be used
  • examples:
    • file: username@hostname.vcf
    • db: username@hostname

5. Inferred interaction states

Direction of an interaction:

• an interaction is outgoing if
  • has an empty author column

Missed call:

• an interaction of type CALL is missed if
  • the extra_data column does not contain the key duration

Contact added:

• a Contact Added interaction
  • has a type column of value CONTACT
  • has an empty author column

Invitation received:

• an Invitation received interaction
  • has a type column of value CONTACT
  • has non-empty author column
  • has a status column of value UNKNOWN

Invitation accepted:

• an Invitation received interaction
  • has a type column of value CONTACT
  • has non-empty author column
  • has a status column of value SUCCESS

Should contact events be stored in the interactions table or should it be handled client side and by the daemon?

Yes, contact events should be stored in the interactions table.

For the Interactions table:

• index on author might be index on (author, date) to quickly order by date, paginate, or get the last interaction fast.

Done

Does the schema have a favourite/favorite boolean for conversation/contact?

This will allow favourite/favorite conversations/contacts to show at the top of the list https://git.jami.net/savoirfairelinux/ring-lrc/issues/407#note_14022

What do you think?

Thank you

Yes, this will be in extra_data

• interactions.extra_data: How would call duration be stored for incoming calls ?

Since we know who was in the call (using the Conversations' participants) we could know if the call was incoming by using the author field (author is us or the peer)

Also, what for intercom? When calling yourself

IMO in extra_data, we could store a packed or a JSON, like

{
    "duration":"82"
}

For a call with a 1 min 22 duration.

interactions.extra_data: How would call duration be stored for incoming calls ?

The same as for outgoing calls?

Since we know who was in the call (using the Conversations' participants) we could know if the call was incoming by using the author field (author is us or the peer)

I had accidentally inverted the titles. Done

│ │── {contact_uri}.vcf

If sensitive information is contained in the filesystem metadata, how are you going to implement a password protection for Jami (ring-client-gnome#737)? I think should be a single db with all account's data (except profile.vcf) which you can optionally encrypt.

Not related to this patch, but you can protect an archive with the account's data inside this archive for example.

changed milestone to %Iteration 12

participant TEXT // uri of the participant

Why not "participants" here? It can be a conference or a group chat.

enum class type {
    INVALID,
<...>
}

enum class status {
    INVALID,
    UNKNOWN,
<...>
}

What is it for? I think a normal program should not keep any data in the invalid state. If this is an indicator whether some structure is initialized or not, I think this indicator should be stored in the structure itself. UNKNOWN should be more concrete (what does an error leave data in the UNKNOWN state?).

Why not "participants" here? It can be a conference or a group chat.

The intended logic for group chats would have been searching for each participant with a common conversation ID but I think that complicates querying (especially since to get the conversation ID, you need to query the participant first a lot of the time (since convID is generated)).

We could alternatively store all participants in one row as a JSON Object or something similar?

@Vladimir

What is it for? I think a normal program should not keep any data in the invalid state. If this is an indicator whether some structure is initialized or not, I think this indicator should be stored in the structure itself.

The INVALID type and status values are indeed intended to indicate an uninitialized structure, and should ideally never be stored in the database. If for some catastrophic reason an INVALID status does get stored, a recovery mechanism should be in place, when loading interactions, to migrate such a state towards a more stable and logical value.

UNKNOWN should be more concrete (what does an error leave data in the UNKNOWN state?).

The UNKNOWN status if the first part of a text message's life cycle. Updates to message status are made in response to callbacks from the message engine. This status value should not require a recovery mechanism as the message engine SHOULD retry and update a message's status until it reaches a stable state.

@rosseiran

We could alternatively store all participants in one row as a JSON Object or something similar?

I'm not absolutely sure, but I think that JSON parsing may be worse the query complexity.

changed milestone to %Iteration 13

changed milestone to %Iteration 14

added SprintTo review label

removed 1 deleted label

mentioned in commit 8dfc664d

changed milestone to %Iteration 15

closed

removed SprintTo review label