diff options
Diffstat (limited to 'filamento/migrations')
| -rw-r--r-- | filamento/migrations/1.sql | 147 | ||||
| -rw-r--r-- | filamento/migrations/20240113011930_luz.sql | 141 |
2 files changed, 147 insertions, 141 deletions
diff --git a/filamento/migrations/1.sql b/filamento/migrations/1.sql new file mode 100644 index 0000000..1b40d89 --- /dev/null +++ b/filamento/migrations/1.sql @@ -0,0 +1,147 @@ +-- SPDX-FileCopyrightText: 2025 cel <cel@bunny.garden> +-- +-- SPDX-License-Identifier: AGPL-3.0-or-later + +PRAGMA foreign_keys = on; + +-- a user jid will never change, only a chat user will change +create table if not exists users( + -- bare jid + jid text primary key not null, + nick text, + avatar text, + -- can receive presence status from non-contacts + cached_status_message text + -- TODO: last_seen +); + +-- 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 +create table if not exists subscription( + state text primary key not null +); + +insert into subscription ( state ) values ('none'), ('pending-out'), ('pending-in'), ('pending-in-pending-out'), ('only-out'), ('only-in'), ('out-pending-in'), ('in-pending-out'), ('buddy') on conflict do nothing; + +-- a roster contains users, with client-set nickname +CREATE TABLE if not exists roster( + user_jid text primary key not null, + name TEXT, + subscription text not null, + foreign key(subscription) references subscription(state), + foreign key(user_jid) references users(jid) +); + +create table if not exists groups( + group_name text primary key not null +); + +create table if not exists groups_roster( + group_name text not null, + contact_jid text not null, + foreign key(group_name) references groups(group_name), + foreign key(contact_jid) references roster(user_jid) on delete cascade, + primary key(group_name, contact_jid) +); + +-- chat includes reference to user jid chat is with +-- specifically for dms, groups should be different +-- can send chat message to user (creating a new chat if not already exists) +create table if not exists chats ( + id text primary key not null, + have_chatted bool not null, + correspondent text not null unique, + -- 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 +create table if not exists delivery( + state text primary key not null +); + +insert into delivery ( state ) values ('sending'), ('written'), ('sent'), ('delivered'), ('read'), ('failed'), ('queued') on conflict do nothing; + +-- messages include reference to chat they are in, and who sent them. +create table if not exists messages ( + id text primary key not null, + body text, + -- delivery is nullable as only messages sent by the user are markable + delivery text, + chat_id text not null, + -- TODO: channel stuff + -- channel_id uuid, + -- check ((chat_id == null) <> (channel_id == null)), + -- check ((chat_id == null) or (channel_id == null)), + -- TODO: queued messages offline + timestamp text not null, + + -- 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), + foreign key(chat_id) references chats(id) on delete cascade, + foreign key(from_identity) references identities(id) +); + +-- enum for subscription state +create table if not exists show ( + state text primary key not null +); + +insert into show ( state ) values ('away'), ('chat'), ('do-not-disturb'), ('extended-away') on conflict do nothing; + +create table if not exists cached_status ( + id integer primary key not null, + show text, + message text, + foreign key(show) references show(state) +); + +insert into cached_status (id) values (0) on conflict do nothing; + +create table if not exists capability_hash_nodes ( + node text primary key not null, + timestamp text, + -- TODO: normalization + capabilities text not null +); + +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) +) diff --git a/filamento/migrations/20240113011930_luz.sql b/filamento/migrations/20240113011930_luz.sql deleted file mode 100644 index c2f35dd..0000000 --- a/filamento/migrations/20240113011930_luz.sql +++ /dev/null @@ -1,141 +0,0 @@ -PRAGMA foreign_keys = on; - --- a user jid will never change, only a chat user will change --- TODO: avatar, nick, etc. -create table users( - -- TODO: enforce bare jid - jid text primary key not null, - nick text, - avatar text, - -- can receive presence status from non-contacts - cached_status_message text - -- 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 resources( - bare_jid text not null, - resource text not null, - foreign key(bare_jid) references users(jid), - primary key(bare_jid, resource) -); - --- enum for subscription state -create table subscription( - state text primary key not null -); - -insert into subscription ( state ) values ('none'), ('pending-out'), ('pending-in'), ('pending-in-pending-out'), ('only-out'), ('only-in'), ('out-pending-in'), ('in-pending-out'), ('buddy'); - --- a roster contains users, with client-set nickname -CREATE TABLE roster( - user_jid text primary key not null, - name TEXT, - subscription text not null, - foreign key(subscription) references subscription(state), - foreign key(user_jid) references users(jid) -); - -create table groups( - group_name text primary key not null -); - -create table groups_roster( - group_name text not null, - contact_jid text not null, - foreign key(group_name) references groups(group_name), - foreign key(contact_jid) references roster(user_jid) on delete cascade, - primary key(group_name, contact_jid) -); - --- chat includes reference to user jid chat is with --- specifically for dms, groups should be different --- can send chat message to user (creating a new chat if not already exists) -create table chats ( - id text primary key not null, - have_chatted bool not null, - correspondent text not null unique, - foreign key(correspondent) references users(jid) -); - --- enum for subscription state -create table delivery( - state text primary key not null -); - -insert into delivery ( state ) values ('sending'), ('written'), ('sent'), ('delivered'), ('read'), ('failed'), ('queued'); - --- messages include reference to chat they are in, and who sent them. -create table messages ( - id text primary key not null, - body text, - -- delivery is nullable as only messages sent by the user are markable - delivery text, - chat_id text not null, - -- TODO: channel stuff - -- 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), - - 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) -); - --- enum for subscription state -create table show ( - state text primary key not null -); - -insert into show ( state ) values ('away'), ('chat'), ('do-not-disturb'), ('extended-away'); - -create table cached_status ( - id integer primary key not null, - show text, - message text, - foreign key(show) references show(state) -); - -insert into cached_status (id) values (0); - -create table capability_hash_nodes ( - node text primary key not null, - timestamp text, - -- TODO: normalization - capabilities text not null -); - -insert into capability_hash_nodes ( node, capabilities ) values ('https://bunny.garden/filamento#mSavc/SLnHm8zazs5RlcbD/iXoc=', 'aHR0cDovL2phYmJlci5vcmcvcHJvdG9jb2wvY2Fwcx9odHRwOi8vamFiYmVyLm9yZy9wcm90b2NvbC9kaXNjbyNpbmZvH2h0dHA6Ly9qYWJiZXIub3JnL3Byb3RvY29sL2Rpc2NvI2l0ZW1zH2h0dHA6Ly9qYWJiZXIub3JnL3Byb3RvY29sL25pY2sfaHR0cDovL2phYmJlci5vcmcvcHJvdG9jb2wvbmljaytub3RpZnkfHGNsaWVudB9wYx8fZmlsYW1lbnRvIDAuMS4wHx4cHA=='); |