-
Notifications
You must be signed in to change notification settings - Fork 24
/
storage.tsx
450 lines (396 loc) · 12.9 KB
/
storage.tsx
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
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
import { NDKUserProfile } from '@nostr-dev-kit/ndk';
import Database from 'tauri-plugin-sql-api';
import { getParentID } from '@utils/transform';
let db: null | Database = null;
// connect database (sqlite)
// path: tauri::api::path::BaseDirectory::App
export async function connect(): Promise<Database> {
if (db) {
return db;
}
db = await Database.load('sqlite:lume.db');
return db;
}
// get active account
export async function getActiveAccount() {
const db = await connect();
const result: any = await db.select('SELECT * FROM accounts WHERE is_active = 1;');
if (result.length > 0) {
return result[0];
} else {
return null;
}
}
// get all accounts
export async function getAccounts() {
const db = await connect();
return await db.select(
'SELECT * FROM accounts WHERE is_active = 0 ORDER BY created_at DESC;'
);
}
// create account
export async function createAccount(
npub: string,
pubkey: string,
privkey: string,
follows?: string[][],
is_active?: number
) {
const db = await connect();
const res = await db.execute(
'INSERT OR IGNORE INTO accounts (npub, pubkey, privkey, follows, is_active) VALUES (?, ?, ?, ?, ?);',
[npub, pubkey, privkey, follows || '', is_active || 0]
);
if (res) {
await createBlock(
0,
'Preserve your freedom',
'https://void.cat/d/949GNg7ZjSLHm2eTR3jZqv'
);
}
const getAccount = await getActiveAccount();
return getAccount;
}
// update account
export async function updateAccount(
column: string,
value: string | string[],
pubkey: string
) {
const db = await connect();
return await db.execute(`UPDATE accounts SET ${column} = ? WHERE pubkey = ?;`, [
value,
pubkey,
]);
}
// count total notes
export async function countTotalChannels() {
const db = await connect();
const result = await db.select('SELECT COUNT(*) AS "total" FROM channels;');
return result[0];
}
// count total notes
export async function countTotalNotes() {
const db = await connect();
const result = await db.select(
'SELECT COUNT(*) AS "total" FROM notes WHERE kind IN (1, 6);'
);
return parseInt(result[0].total);
}
// get all notes
export async function getNotes(limit: number, offset: number) {
const db = await connect();
const totalNotes = await countTotalNotes();
const nextCursor = offset + limit;
const notes: any = { data: null, nextCursor: 0 };
const query: any = await db.select(
`SELECT * FROM notes WHERE kind IN (1, 6, 1063) GROUP BY parent_id ORDER BY created_at DESC LIMIT "${limit}" OFFSET "${offset}";`
);
notes['data'] = query;
notes['nextCursor'] = Math.round(totalNotes / nextCursor) > 1 ? nextCursor : undefined;
return notes;
}
// get all notes by pubkey
export async function getNotesByPubkey(pubkey: string) {
const db = await connect();
const res: any = await db.select(
`SELECT * FROM notes WHERE pubkey == "${pubkey}" AND kind IN (1, 6, 1063) GROUP BY parent_id ORDER BY created_at DESC;`
);
return res;
}
// get all notes by authors
export async function getNotesByAuthors(authors: string, limit: number, offset: number) {
const db = await connect();
const totalNotes = await countTotalNotes();
const nextCursor = offset + limit;
const array = JSON.parse(authors);
const finalArray = `'${array.join("','")}'`;
const notes: any = { data: null, nextCursor: 0 };
const query: any = await db.select(
`SELECT * FROM notes WHERE pubkey IN (${finalArray}) AND kind IN (1, 6, 1063) GROUP BY parent_id ORDER BY created_at DESC LIMIT "${limit}" OFFSET "${offset}";`
);
notes['data'] = query;
notes['nextCursor'] = Math.round(totalNotes / nextCursor) > 1 ? nextCursor : undefined;
return notes;
}
// get note by id
export async function getNoteByID(event_id: string) {
const db = await connect();
const result = await db.select(`SELECT * FROM notes WHERE event_id = "${event_id}";`);
return result[0];
}
// create note
export async function createNote(
event_id: string,
pubkey: string,
kind: number,
tags: any,
content: string,
created_at: number
) {
const db = await connect();
const account = await getActiveAccount();
const parentID = getParentID(tags, event_id);
return await db.execute(
'INSERT OR IGNORE INTO notes (event_id, account_id, pubkey, kind, tags, content, created_at, parent_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?);',
[event_id, account.id, pubkey, kind, tags, content, created_at, parentID]
);
}
// get note replies
export async function getReplies(parent_id: string) {
const db = await connect();
const result: any = await db.select(
`SELECT * FROM replies WHERE parent_id = "${parent_id}" ORDER BY created_at DESC;`
);
return result;
}
// create reply note
export async function createReplyNote(
parent_id: string,
event_id: string,
pubkey: string,
kind: number,
tags: any,
content: string,
created_at: number
) {
const db = await connect();
return await db.execute(
'INSERT OR IGNORE INTO replies (event_id, parent_id, pubkey, kind, tags, content, created_at) VALUES (?, ?, ?, ?, ?, ?, ?);',
[event_id, parent_id, pubkey, kind, tags, content, created_at]
);
}
// get all pubkeys in db
export async function getAllPubkeys() {
const db = await connect();
const notes: any = await db.select('SELECT DISTINCT pubkey FROM notes');
const replies: any = await db.select('SELECT DISTINCT pubkey FROM replies');
const chats: any = await db.select('SELECT DISTINCT sender_pubkey FROM chats');
return [...notes, ...replies, ...chats];
}
// get all channels
export async function getChannels() {
const db = await connect();
const result: any = await db.select('SELECT * FROM channels ORDER BY created_at DESC;');
return result;
}
// get channel by id
export async function getChannel(id: string) {
const db = await connect();
const result = await db.select(`SELECT * FROM channels WHERE event_id = "${id}";`);
return result[0];
}
// create channel
export async function createChannel(
event_id: string,
pubkey: string,
name: string,
picture: string,
about: string,
created_at: number
) {
const db = await connect();
return await db.execute(
'INSERT OR IGNORE INTO channels (event_id, pubkey, name, picture, about, created_at) VALUES (?, ?, ?, ?, ?, ?);',
[event_id, pubkey, name, picture, about, created_at]
);
}
// update channel metadata
export async function updateChannelMetadata(event_id: string, value: string) {
const db = await connect();
const data = JSON.parse(value);
return await db.execute(
'UPDATE channels SET name = ?, picture = ?, about = ? WHERE event_id = ?;',
[data.name, data.picture, data.about, event_id]
);
}
// create channel messages
export async function createChannelMessage(
channel_id: string,
event_id: string,
pubkey: string,
kind: number,
content: string,
tags: string[][],
created_at: number
) {
const db = await connect();
return await db.execute(
'INSERT OR IGNORE INTO channel_messages (channel_id, event_id, pubkey, kind, content, tags, created_at) VALUES (?, ?, ?, ?, ?, ?, ?);',
[channel_id, event_id, pubkey, kind, content, tags, created_at]
);
}
// get channel messages by channel id
export async function getChannelMessages(channel_id: string) {
const db = await connect();
return await db.select(
`SELECT * FROM channel_messages WHERE channel_id = "${channel_id}" ORDER BY created_at ASC;`
);
}
// get channel users
export async function getChannelUsers(channel_id: string) {
const db = await connect();
const result: any = await db.select(
`SELECT DISTINCT pubkey FROM channel_messages WHERE channel_id = "${channel_id}";`
);
return result;
}
// get all chats by pubkey
export async function getChats() {
const db = await connect();
const account = await getActiveAccount();
const result: any = await db.select(
`SELECT DISTINCT sender_pubkey FROM chats WHERE receiver_pubkey = "${account.pubkey}" ORDER BY created_at DESC;`
);
const newArr: any = result.map((v) => ({ ...v, new_messages: 0 }));
return newArr;
}
// get chat messages
export async function getChatMessages(receiver_pubkey: string, sender_pubkey: string) {
const db = await connect();
let receiver = [];
const sender: any = await db.select(
`SELECT * FROM chats WHERE sender_pubkey = "${sender_pubkey}" AND receiver_pubkey = "${receiver_pubkey}";`
);
if (receiver_pubkey !== sender_pubkey) {
receiver = await db.select(
`SELECT * FROM chats WHERE sender_pubkey = "${receiver_pubkey}" AND receiver_pubkey = "${sender_pubkey}";`
);
}
const result = [...sender, ...receiver].sort(
(x: { created_at: number }, y: { created_at: number }) => x.created_at - y.created_at
);
return result;
}
// create chat
export async function createChat(
event_id: string,
receiver_pubkey: string,
sender_pubkey: string,
content: string,
tags: string[][],
created_at: number
) {
const db = await connect();
await db.execute(
'INSERT OR IGNORE INTO chats (event_id, receiver_pubkey, sender_pubkey, content, tags, created_at) VALUES (?, ?, ?, ?, ?, ?);',
[event_id, receiver_pubkey, sender_pubkey, content, tags, created_at]
);
return sender_pubkey;
}
// get setting
export async function getSetting(key: string) {
const db = await connect();
const result = await db.select(`SELECT value FROM settings WHERE key = "${key}";`);
return result[0]?.value;
}
// update setting
export async function updateSetting(key: string, value: string | number) {
const db = await connect();
return await db.execute(`UPDATE settings SET value = "${value}" WHERE key = "${key}";`);
}
// get last login
export async function getLastLogin() {
const db = await connect();
const result = await db.select(`SELECT value FROM settings WHERE key = "last_login";`);
if (result[0]) {
return parseInt(result[0].value);
} else {
return 0;
}
}
// update last login
export async function updateLastLogin(value: number) {
const db = await connect();
return await db.execute(
`UPDATE settings SET value = ${value} WHERE key = "last_login";`
);
}
// get blacklist by kind and account id
export async function getBlacklist(account_id: number, kind: number) {
const db = await connect();
return await db.select(
`SELECT * FROM blacklist WHERE account_id = "${account_id}" AND kind = "${kind}";`
);
}
// get active blacklist by kind and account id
export async function getActiveBlacklist(account_id: number, kind: number) {
const db = await connect();
return await db.select(
`SELECT content FROM blacklist WHERE account_id = "${account_id}" AND kind = "${kind}" AND status = 1;`
);
}
// add to blacklist
export async function addToBlacklist(
account_id: number,
content: string,
kind: number,
status?: number
) {
const db = await connect();
return await db.execute(
'INSERT OR IGNORE INTO blacklist (account_id, content, kind, status) VALUES (?, ?, ?, ?);',
[account_id, content, kind, status || 1]
);
}
// update item in blacklist
export async function updateItemInBlacklist(content: string, status: number) {
const db = await connect();
return await db.execute(
`UPDATE blacklist SET status = "${status}" WHERE content = "${content}";`
);
}
// get all blocks
export async function getBlocks() {
const db = await connect();
const activeAccount = await getActiveAccount();
const result: any = await db.select(
`SELECT * FROM blocks WHERE account_id = "${activeAccount.id}" ORDER BY created_at DESC;`
);
return result;
}
// create block
export async function createBlock(kind: number, title: string, content: any) {
const db = await connect();
const activeAccount = await getActiveAccount();
return await db.execute(
'INSERT OR IGNORE INTO blocks (account_id, kind, title, content) VALUES (?, ?, ?, ?);',
[activeAccount.id, kind, title, content]
);
}
// remove block
export async function removeBlock(id: string) {
const db = await connect();
return await db.execute(`DELETE FROM blocks WHERE id = "${id}";`);
}
// logout
export async function removeAll() {
const db = await connect();
await db.execute(`UPDATE settings SET value = "0" WHERE key = "last_login";`);
await db.execute('DELETE FROM replies;');
await db.execute('DELETE FROM notes;');
await db.execute('DELETE FROM blacklist;');
await db.execute('DELETE FROM blocks;');
await db.execute('DELETE FROM chats;');
await db.execute('DELETE FROM accounts;');
return true;
}
// create metadata
export async function createMetadata(id: string, pubkey: string, content: string) {
const db = await connect();
const now = Math.floor(Date.now() / 1000);
return await db.execute(
'INSERT OR REPLACE INTO metadata (id, pubkey, content, created_at) VALUES (?, ?, ?, ?);',
[id, pubkey, content, now]
);
}
// get metadata
export async function getUserMetadata(pubkey: string) {
const db = await connect();
const result = await db.select(`SELECT content FROM metadata WHERE id = "${pubkey}";`);
if (result[0]) {
return JSON.parse(result[0].content);
} else {
return null;
}
}