diff options
Diffstat (limited to 'filamento/migrations/1.sql')
| -rw-r--r-- | filamento/migrations/1.sql | 82 |
1 files changed, 42 insertions, 40 deletions
diff --git a/filamento/migrations/1.sql b/filamento/migrations/1.sql index 502c5a9..ec574fc 100644 --- a/filamento/migrations/1.sql +++ b/filamento/migrations/1.sql @@ -1,9 +1,8 @@ PRAGMA foreign_keys = on; -- a user jid will never change, only a chat user will change --- TODO: avatar, nick, etc. create table if not exists users( - -- TODO: enforce bare jid + -- bare jid jid text primary key not null, nick text, avatar text, @@ -12,28 +11,26 @@ create table if not exists users( -- TODO: last_seen ); --- -- links to messages, jabber users, stores jid history, etc. --- create table identities( --- id text primary key not null --- ); - --- create table identities_users( --- id text not null, --- jid text not null, --- -- whichever has the newest timestamp is the active one. --- -- what to do when somebody moves, but then the old jid is used again without having explicitly moved back? create new identity to assign ownership to? --- -- merging of identities? --- activated_timestamp not null, --- foreign key(id) references identities(id), --- foreign key(jid) references users(jid), --- primary key(activated timestamp, id, jid) --- ); - -create table if not exists resources( - bare_jid text not null, - resource text not null, - foreign key(bare_jid) references users(jid), - primary key(bare_jid, resource) +-- links to messages, jabber users, stores jid history, etc. +-- every identity requires a user, so imported messages must be assigned to an existing jabber account. +create table if not exists identities( + id text primary key not null, + -- each time a user moves, their identity's primary jid is updated, and the new jid is added to the identities log + primary_jid text not null unique, + foreign key(primary_jid) references users(jid) +); +-- this also allows e.g. mucs with imported histories to assign messages temporarily to muc-generated temporary users, then send user move events to assign message ownership once that person joins the new group chat with a jabber account. + +-- identities log basically. +create table if not exists identities_users( + id text not null, + jid text not null, + -- what to do when somebody moves, but then the old jid is used again without having explicitly moved back? create new identity to assign ownership to? + -- merging of identities? + activated_timestamp not null, + foreign key(id) references identities(id), + foreign key(jid) references users(jid), + primary key(id, jid) ); -- enum for subscription state @@ -71,7 +68,8 @@ create table if not exists chats ( id text primary key not null, have_chatted bool not null, correspondent text not null unique, - foreign key(correspondent) references users(jid) + -- TODO: how many messages to store locally/rely on mam, whether to auto-download/store media too. + foreign key(correspondent) references identities(id) ); -- enum for subscription state @@ -92,27 +90,17 @@ create table if not exists messages ( -- channel_id uuid, -- check ((chat_id == null) <> (channel_id == null)), -- check ((chat_id == null) or (channel_id == null)), - -- user is the current "owner" of the message -- TODO: queued messages offline - -- TODO: timestamp timestamp text not null, - -- TODO: icky - -- the user to show it coming from (not necessarily the original sender) - -- from_identity text not null, - -- original sender details (only from jabber supported for now) - from_jid text not null, - -- resource can be null - from_resource text, - -- check (from_jid != original_sender), + -- the identity to show it coming from (not necessarily the original sender) + from_identity text not null, + + -- TODO: unread message count, read bool not null, foreign key(delivery) references delivery(state), - -- TODO: from can be either a jid, a moved jid (for when a contact moves, save original sender jid/user but link to new user), or imported (from another service (save details), linked to new user) - -- TODO: read bool not null, foreign key(chat_id) references chats(id) on delete cascade, - -- foreign key(from_identity) references identities(id), - foreign key(from_jid) references users(jid), - foreign key(from_jid, from_resource) references resources(bare_jid, resource) + foreign key(from_identity) references identities(id) ); -- enum for subscription state @@ -139,3 +127,17 @@ create table if not exists capability_hash_nodes ( ); insert into capability_hash_nodes ( node, capabilities ) values ('https://bunny.garden/filamento#mSavc/SLnHm8zazs5RlcbD/iXoc=', 'aHR0cDovL2phYmJlci5vcmcvcHJvdG9jb2wvY2Fwcx9odHRwOi8vamFiYmVyLm9yZy9wcm90b2NvbC9kaXNjbyNpbmZvH2h0dHA6Ly9qYWJiZXIub3JnL3Byb3RvY29sL2Rpc2NvI2l0ZW1zH2h0dHA6Ly9qYWJiZXIub3JnL3Byb3RvY29sL25pY2sfaHR0cDovL2phYmJlci5vcmcvcHJvdG9jb2wvbmljaytub3RpZnkfHGNsaWVudB9wYx8fZmlsYW1lbnRvIDAuMS4wHx4cHA==') on conflict do nothing; + +-- TODO: later maybe just a full stanza log period? +create table if not exists message_stanzas ( + id integer primary key not null, + raw text not null, + timestamp text not null, + -- should be 1 to many as the same original stanza could technically have multiple envelopes...(e.g. message sync, mam), also makes making dangling stanzas not possible easier when modeling the relationship this way. + envelope_of integer, + linked_message text, + -- check that stanza is either linked to another stanza, or a message (no dangling stanzas) + check ((envelope_of == null) <> (linked_message == null)), + foreign key(linked_message) references messages(id), + foreign key(envelope_of) references message_stanzas(id) +) |