1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
|
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,
-- 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 not null,
-- TODO: normalization
capabilities text not null
);
|
