Page MenuHomePhabricator
Differential D13083

[SQLite] deprecate `removeOutboundP2PMessagesOlderThan` and update to remove only one specific message
ClosedPublic
Actions

Authored by kamil on Aug 14 2024, 7:01 AM.
Tags
None
Referenced Files
Unknown Object (File)
Fri, Apr 4, 7:31 AM
Unknown Object (File)
Thu, Apr 3, 11:26 PM
Unknown Object (File)
Thu, Apr 3, 4:22 AM
Unknown Object (File)
Thu, Mar 20, 11:12 AM
Unknown Object (File)
Mar 11 2025, 9:14 PM
Unknown Object (File)
Feb 28 2025, 11:55 AM
Unknown Object (File)
Feb 28 2025, 11:54 AM
Unknown Object (File)
Feb 21 2025, 1:28 AM
View All 49 Files
Subscribers
ashoat

Details

Reviewers
tomek
marcin
Commits
rCOMM74b74fdfee46: [SQLite] deprecate `removeOutboundP2PMessagesOlderThan` and update to remove…
Summary

ENG-8994.

This difference includes two things:

  1. Update to the query
  2. Rename
Test Plan

Tests

Diff Detail

Repository
rCOMM Comm
Lint
Lint Not Applicable
Unit
Tests Not Applicable

Revision Contents
Changeset List

PathSize
lib/
tunnelbroker/
5 lines
types/
2 lines
utils/
__mocks__/
2 lines
native/
cpp/
CommonCpp/
DatabaseManagers/
4 lines
4 lines
13 lines
NativeModules/
2 lines
6 lines
_generated/
database/
3 lines
schema/
2 lines
web/
cpp/
4 lines
database/
4 lines
shared-worker/
_generated/
queries/
19 lines
types/
5 lines
worker/
4 lines
types/
8 lines

Diff 43547

View Options

lib/tunnelbroker/use-peer-to-peer-message-handler.js

Show All 11 Lines
import { createUpdates } from '../creators/update-creator.js'; import { createUpdates } from '../creators/update-creator.js';
import { dbQuery, SQL } from '../database/database.js'; import { dbQuery, SQL } from '../database/database.js';
import { import {
fetchKnownUserInfos, fetchKnownUserInfos,
fetchUsername, fetchUsername,
} from '../fetchers/user-fetchers.js'; } from '../fetchers/user-fetchers.js';
import { rescindPushNotifs } from '../push/rescind.js'; import { rescindPushNotifs } from '../push/rescind.js';
import { removeBlobHolders } from '../services/blob.js';
import { createNewAnonymousCookie } from '../session/cookies.js'; import { createNewAnonymousCookie } from '../session/cookies.js';
import type { Viewer, AnonymousViewerData } from '../session/viewer.js'; import type { Viewer, AnonymousViewerData } from '../session/viewer.js';
import { fetchOlmAccount } from '../updaters/olm-account-updater.js'; import { fetchOlmAccount } from '../updaters/olm-account-updater.js';
import { blobHoldersFromUploadRows } from '../uploads/media-utils.js';
async function deleteUploadsForUser(deletedUserID: string): Promise<void> {
try {
const [holderRows] = await dbQuery(SQL`
SELECT extra
FROM uploads
WHERE user_container = ${deletedUserID}
`);
const blobHolders = blobHoldersFromUploadRows(holderRows);
await removeBlobHolders(blobHolders);
await dbQuery(SQL`
DELETE u, i
FROM uploads u
LEFT JOIN ids i on i.id = u.id
WHERE u.user_container = ${deletedUserID};
`);
} catch (err) {
// unassign uploads so the deletion will be retried
// by the `deleteUnassignedUploads()`
await dbQuery(SQL`
UPDATE uploads
SET user_container = NULL
WHERE user_container = ${deletedUserID};
`);
throw err;
}
}
async function deleteAccount(viewer: Viewer): Promise<?LogOutResponse> { async function deleteAccount(viewer: Viewer): Promise<?LogOutResponse> {
if (!viewer.loggedIn) { if (!viewer.loggedIn) {
throw new ServerError('not_logged_in'); throw new ServerError('not_logged_in');
} }
const deletedUserID = viewer.userID; const deletedUserID = viewer.userID;
await rescindPushNotifs(SQL`n.user = ${deletedUserID}`, SQL`NULL`); await rescindPushNotifs(SQL`n.user = ${deletedUserID}`, SQL`NULL`);
const knownUserInfos = await fetchKnownUserInfos(viewer); const knownUserInfos = await fetchKnownUserInfos(viewer);
const usersToUpdate: $ReadOnlyArray<UserInfo> = values(knownUserInfos).filter( const usersToUpdate: $ReadOnlyArray<UserInfo> = values(knownUserInfos).filter(
(user: UserInfo): boolean => user.id !== deletedUserID, (user: UserInfo): boolean => user.id !== deletedUserID,
); );
ignorePromiseRejections(deleteUploadsForUser(deletedUserID));
// TODO: if this results in any orphaned orgs, convert them to chats // TODO: if this results in any orphaned orgs, convert them to chats
const deletionQuery = SQL` const deletionQuery = SQL`
START TRANSACTION; START TRANSACTION;
DELETE FROM users WHERE id = ${deletedUserID}; DELETE FROM users WHERE id = ${deletedUserID};
DELETE FROM ids WHERE id = ${deletedUserID}; DELETE FROM ids WHERE id = ${deletedUserID};
DELETE c, i DELETE c, i
FROM cookies c FROM cookies c
LEFT JOIN ids i ON i.id = c.id LEFT JOIN ids i ON i.id = c.id
Show All 11 Lines const deletionQuery = SQL`
DELETE s, i DELETE s, i
FROM sessions s FROM sessions s
LEFT JOIN ids i ON i.id = s.id LEFT JOIN ids i ON i.id = s.id
WHERE s.user = ${deletedUserID}; WHERE s.user = ${deletedUserID};
DELETE r, i DELETE r, i
FROM reports r FROM reports r
LEFT JOIN ids i ON i.id = r.id LEFT JOIN ids i ON i.id = r.id
WHERE r.user = ${deletedUserID}; WHERE r.user = ${deletedUserID};
DELETE u, i
FROM uploads u
LEFT JOIN ids i on i.id = u.id
WHERE u.user_container = ${deletedUserID};
DELETE FROM relationships_undirected WHERE user1 = ${deletedUserID}; DELETE FROM relationships_undirected WHERE user1 = ${deletedUserID};
DELETE FROM relationships_undirected WHERE user2 = ${deletedUserID}; DELETE FROM relationships_undirected WHERE user2 = ${deletedUserID};
DELETE FROM relationships_directed WHERE user1 = ${deletedUserID}; DELETE FROM relationships_directed WHERE user1 = ${deletedUserID};
DELETE FROM relationships_directed WHERE user2 = ${deletedUserID}; DELETE FROM relationships_directed WHERE user2 = ${deletedUserID};
COMMIT; COMMIT;
`; `;
const deletionPromise = dbQuery(deletionQuery, { multipleStatements: true }); const deletionPromise = dbQuery(deletionQuery, { multipleStatements: true });
▲ Show 20 Lines • Show All 78 Lines • Show Last 20 Lines
View Options

lib/types/sqlite-types.js

Show All 11 Lines
import { createUpdates } from '../creators/update-creator.js'; import { createUpdates } from '../creators/update-creator.js';
import { dbQuery, SQL } from '../database/database.js'; import { dbQuery, SQL } from '../database/database.js';
import { import {
fetchKnownUserInfos, fetchKnownUserInfos,
fetchUsername, fetchUsername,
} from '../fetchers/user-fetchers.js'; } from '../fetchers/user-fetchers.js';
import { rescindPushNotifs } from '../push/rescind.js'; import { rescindPushNotifs } from '../push/rescind.js';
import { removeBlobHolders } from '../services/blob.js';
import { createNewAnonymousCookie } from '../session/cookies.js'; import { createNewAnonymousCookie } from '../session/cookies.js';
import type { Viewer, AnonymousViewerData } from '../session/viewer.js'; import type { Viewer, AnonymousViewerData } from '../session/viewer.js';
import { fetchOlmAccount } from '../updaters/olm-account-updater.js'; import { fetchOlmAccount } from '../updaters/olm-account-updater.js';
import { blobHoldersFromUploadRows } from '../uploads/media-utils.js';
async function deleteUploadsForUser(deletedUserID: string): Promise<void> {
try {
const [holderRows] = await dbQuery(SQL`
SELECT extra
FROM uploads
WHERE user_container = ${deletedUserID}
`);
const blobHolders = blobHoldersFromUploadRows(holderRows);
await removeBlobHolders(blobHolders);
await dbQuery(SQL`
DELETE u, i
FROM uploads u
LEFT JOIN ids i on i.id = u.id
WHERE u.user_container = ${deletedUserID};
`);
} catch (err) {
// unassign uploads so the deletion will be retried
// by the `deleteUnassignedUploads()`
await dbQuery(SQL`
UPDATE uploads
SET user_container = NULL
WHERE user_container = ${deletedUserID};
`);
throw err;
}
}
async function deleteAccount(viewer: Viewer): Promise<?LogOutResponse> { async function deleteAccount(viewer: Viewer): Promise<?LogOutResponse> {
if (!viewer.loggedIn) { if (!viewer.loggedIn) {
throw new ServerError('not_logged_in'); throw new ServerError('not_logged_in');
} }
const deletedUserID = viewer.userID; const deletedUserID = viewer.userID;
await rescindPushNotifs(SQL`n.user = ${deletedUserID}`, SQL`NULL`); await rescindPushNotifs(SQL`n.user = ${deletedUserID}`, SQL`NULL`);
const knownUserInfos = await fetchKnownUserInfos(viewer); const knownUserInfos = await fetchKnownUserInfos(viewer);
const usersToUpdate: $ReadOnlyArray<UserInfo> = values(knownUserInfos).filter( const usersToUpdate: $ReadOnlyArray<UserInfo> = values(knownUserInfos).filter(
(user: UserInfo): boolean => user.id !== deletedUserID, (user: UserInfo): boolean => user.id !== deletedUserID,
); );
ignorePromiseRejections(deleteUploadsForUser(deletedUserID));
// TODO: if this results in any orphaned orgs, convert them to chats // TODO: if this results in any orphaned orgs, convert them to chats
const deletionQuery = SQL` const deletionQuery = SQL`
START TRANSACTION; START TRANSACTION;
DELETE FROM users WHERE id = ${deletedUserID}; DELETE FROM users WHERE id = ${deletedUserID};
DELETE FROM ids WHERE id = ${deletedUserID}; DELETE FROM ids WHERE id = ${deletedUserID};
DELETE c, i DELETE c, i
FROM cookies c FROM cookies c
LEFT JOIN ids i ON i.id = c.id LEFT JOIN ids i ON i.id = c.id
Show All 11 Lines const deletionQuery = SQL`
DELETE s, i DELETE s, i
FROM sessions s FROM sessions s
LEFT JOIN ids i ON i.id = s.id LEFT JOIN ids i ON i.id = s.id
WHERE s.user = ${deletedUserID}; WHERE s.user = ${deletedUserID};
DELETE r, i DELETE r, i
FROM reports r FROM reports r
LEFT JOIN ids i ON i.id = r.id LEFT JOIN ids i ON i.id = r.id
WHERE r.user = ${deletedUserID}; WHERE r.user = ${deletedUserID};
DELETE u, i
FROM uploads u
LEFT JOIN ids i on i.id = u.id
WHERE u.user_container = ${deletedUserID};
DELETE FROM relationships_undirected WHERE user1 = ${deletedUserID}; DELETE FROM relationships_undirected WHERE user1 = ${deletedUserID};
DELETE FROM relationships_undirected WHERE user2 = ${deletedUserID}; DELETE FROM relationships_undirected WHERE user2 = ${deletedUserID};
DELETE FROM relationships_directed WHERE user1 = ${deletedUserID}; DELETE FROM relationships_directed WHERE user1 = ${deletedUserID};
DELETE FROM relationships_directed WHERE user2 = ${deletedUserID}; DELETE FROM relationships_directed WHERE user2 = ${deletedUserID};
COMMIT; COMMIT;
`; `;
const deletionPromise = dbQuery(deletionQuery, { multipleStatements: true }); const deletionPromise = dbQuery(deletionQuery, { multipleStatements: true });
▲ Show 20 Lines • Show All 78 Lines • Show Last 20 Lines
View Options

lib/utils/__mocks__/config.js

Show All 11 Lines
import { createUpdates } from '../creators/update-creator.js'; import { createUpdates } from '../creators/update-creator.js';
import { dbQuery, SQL } from '../database/database.js'; import { dbQuery, SQL } from '../database/database.js';
import { import {
fetchKnownUserInfos, fetchKnownUserInfos,
fetchUsername, fetchUsername,
} from '../fetchers/user-fetchers.js'; } from '../fetchers/user-fetchers.js';
import { rescindPushNotifs } from '../push/rescind.js'; import { rescindPushNotifs } from '../push/rescind.js';
import { removeBlobHolders } from '../services/blob.js';
import { createNewAnonymousCookie } from '../session/cookies.js'; import { createNewAnonymousCookie } from '../session/cookies.js';
import type { Viewer, AnonymousViewerData } from '../session/viewer.js'; import type { Viewer, AnonymousViewerData } from '../session/viewer.js';
import { fetchOlmAccount } from '../updaters/olm-account-updater.js'; import { fetchOlmAccount } from '../updaters/olm-account-updater.js';
import { blobHoldersFromUploadRows } from '../uploads/media-utils.js';
async function deleteUploadsForUser(deletedUserID: string): Promise<void> {
try {
const [holderRows] = await dbQuery(SQL`
SELECT extra
FROM uploads
WHERE user_container = ${deletedUserID}
`);
const blobHolders = blobHoldersFromUploadRows(holderRows);
await removeBlobHolders(blobHolders);
await dbQuery(SQL`
DELETE u, i
FROM uploads u
LEFT JOIN ids i on i.id = u.id
WHERE u.user_container = ${deletedUserID};
`);
} catch (err) {
// unassign uploads so the deletion will be retried
// by the `deleteUnassignedUploads()`
await dbQuery(SQL`
UPDATE uploads
SET user_container = NULL
WHERE user_container = ${deletedUserID};
`);
throw err;
}
}
async function deleteAccount(viewer: Viewer): Promise<?LogOutResponse> { async function deleteAccount(viewer: Viewer): Promise<?LogOutResponse> {
if (!viewer.loggedIn) { if (!viewer.loggedIn) {
throw new ServerError('not_logged_in'); throw new ServerError('not_logged_in');
} }
const deletedUserID = viewer.userID; const deletedUserID = viewer.userID;
await rescindPushNotifs(SQL`n.user = ${deletedUserID}`, SQL`NULL`); await rescindPushNotifs(SQL`n.user = ${deletedUserID}`, SQL`NULL`);
const knownUserInfos = await fetchKnownUserInfos(viewer); const knownUserInfos = await fetchKnownUserInfos(viewer);
const usersToUpdate: $ReadOnlyArray<UserInfo> = values(knownUserInfos).filter( const usersToUpdate: $ReadOnlyArray<UserInfo> = values(knownUserInfos).filter(
(user: UserInfo): boolean => user.id !== deletedUserID, (user: UserInfo): boolean => user.id !== deletedUserID,
); );
ignorePromiseRejections(deleteUploadsForUser(deletedUserID));
// TODO: if this results in any orphaned orgs, convert them to chats // TODO: if this results in any orphaned orgs, convert them to chats
const deletionQuery = SQL` const deletionQuery = SQL`
START TRANSACTION; START TRANSACTION;
DELETE FROM users WHERE id = ${deletedUserID}; DELETE FROM users WHERE id = ${deletedUserID};
DELETE FROM ids WHERE id = ${deletedUserID}; DELETE FROM ids WHERE id = ${deletedUserID};
DELETE c, i DELETE c, i
FROM cookies c FROM cookies c
LEFT JOIN ids i ON i.id = c.id LEFT JOIN ids i ON i.id = c.id
Show All 11 Lines const deletionQuery = SQL`
DELETE s, i DELETE s, i
FROM sessions s FROM sessions s
LEFT JOIN ids i ON i.id = s.id LEFT JOIN ids i ON i.id = s.id
WHERE s.user = ${deletedUserID}; WHERE s.user = ${deletedUserID};
DELETE r, i DELETE r, i
FROM reports r FROM reports r
LEFT JOIN ids i ON i.id = r.id LEFT JOIN ids i ON i.id = r.id
WHERE r.user = ${deletedUserID}; WHERE r.user = ${deletedUserID};
DELETE u, i
FROM uploads u
LEFT JOIN ids i on i.id = u.id
WHERE u.user_container = ${deletedUserID};
DELETE FROM relationships_undirected WHERE user1 = ${deletedUserID}; DELETE FROM relationships_undirected WHERE user1 = ${deletedUserID};
DELETE FROM relationships_undirected WHERE user2 = ${deletedUserID}; DELETE FROM relationships_undirected WHERE user2 = ${deletedUserID};
DELETE FROM relationships_directed WHERE user1 = ${deletedUserID}; DELETE FROM relationships_directed WHERE user1 = ${deletedUserID};
DELETE FROM relationships_directed WHERE user2 = ${deletedUserID}; DELETE FROM relationships_directed WHERE user2 = ${deletedUserID};
COMMIT; COMMIT;
`; `;
const deletionPromise = dbQuery(deletionQuery, { multipleStatements: true }); const deletionPromise = dbQuery(deletionQuery, { multipleStatements: true });
▲ Show 20 Lines • Show All 78 Lines • Show Last 20 Lines
View Options

native/cpp/CommonCpp/DatabaseManagers/DatabaseQueryExecutor.h

Show All 11 Lines
import { createUpdates } from '../creators/update-creator.js'; import { createUpdates } from '../creators/update-creator.js';
import { dbQuery, SQL } from '../database/database.js'; import { dbQuery, SQL } from '../database/database.js';
import { import {
fetchKnownUserInfos, fetchKnownUserInfos,
fetchUsername, fetchUsername,
} from '../fetchers/user-fetchers.js'; } from '../fetchers/user-fetchers.js';
import { rescindPushNotifs } from '../push/rescind.js'; import { rescindPushNotifs } from '../push/rescind.js';
import { removeBlobHolders } from '../services/blob.js';
import { createNewAnonymousCookie } from '../session/cookies.js'; import { createNewAnonymousCookie } from '../session/cookies.js';
import type { Viewer, AnonymousViewerData } from '../session/viewer.js'; import type { Viewer, AnonymousViewerData } from '../session/viewer.js';
import { fetchOlmAccount } from '../updaters/olm-account-updater.js'; import { fetchOlmAccount } from '../updaters/olm-account-updater.js';
import { blobHoldersFromUploadRows } from '../uploads/media-utils.js';
async function deleteUploadsForUser(deletedUserID: string): Promise<void> {
try {
const [holderRows] = await dbQuery(SQL`
SELECT extra
FROM uploads
WHERE user_container = ${deletedUserID}
`);
const blobHolders = blobHoldersFromUploadRows(holderRows);
await removeBlobHolders(blobHolders);
await dbQuery(SQL`
DELETE u, i
FROM uploads u
LEFT JOIN ids i on i.id = u.id
WHERE u.user_container = ${deletedUserID};
`);
} catch (err) {
// unassign uploads so the deletion will be retried
// by the `deleteUnassignedUploads()`
await dbQuery(SQL`
UPDATE uploads
SET user_container = NULL
WHERE user_container = ${deletedUserID};
`);
throw err;
}
}
async function deleteAccount(viewer: Viewer): Promise<?LogOutResponse> { async function deleteAccount(viewer: Viewer): Promise<?LogOutResponse> {
if (!viewer.loggedIn) { if (!viewer.loggedIn) {
throw new ServerError('not_logged_in'); throw new ServerError('not_logged_in');
} }
const deletedUserID = viewer.userID; const deletedUserID = viewer.userID;
await rescindPushNotifs(SQL`n.user = ${deletedUserID}`, SQL`NULL`); await rescindPushNotifs(SQL`n.user = ${deletedUserID}`, SQL`NULL`);
const knownUserInfos = await fetchKnownUserInfos(viewer); const knownUserInfos = await fetchKnownUserInfos(viewer);
const usersToUpdate: $ReadOnlyArray<UserInfo> = values(knownUserInfos).filter( const usersToUpdate: $ReadOnlyArray<UserInfo> = values(knownUserInfos).filter(
(user: UserInfo): boolean => user.id !== deletedUserID, (user: UserInfo): boolean => user.id !== deletedUserID,
); );
ignorePromiseRejections(deleteUploadsForUser(deletedUserID));
// TODO: if this results in any orphaned orgs, convert them to chats // TODO: if this results in any orphaned orgs, convert them to chats
const deletionQuery = SQL` const deletionQuery = SQL`
START TRANSACTION; START TRANSACTION;
DELETE FROM users WHERE id = ${deletedUserID}; DELETE FROM users WHERE id = ${deletedUserID};
DELETE FROM ids WHERE id = ${deletedUserID}; DELETE FROM ids WHERE id = ${deletedUserID};
DELETE c, i DELETE c, i
FROM cookies c FROM cookies c
LEFT JOIN ids i ON i.id = c.id LEFT JOIN ids i ON i.id = c.id
Show All 11 Lines const deletionQuery = SQL`
DELETE s, i DELETE s, i
FROM sessions s FROM sessions s
LEFT JOIN ids i ON i.id = s.id LEFT JOIN ids i ON i.id = s.id
WHERE s.user = ${deletedUserID}; WHERE s.user = ${deletedUserID};
DELETE r, i DELETE r, i
FROM reports r FROM reports r
LEFT JOIN ids i ON i.id = r.id LEFT JOIN ids i ON i.id = r.id
WHERE r.user = ${deletedUserID}; WHERE r.user = ${deletedUserID};
DELETE u, i
FROM uploads u
LEFT JOIN ids i on i.id = u.id
WHERE u.user_container = ${deletedUserID};
DELETE FROM relationships_undirected WHERE user1 = ${deletedUserID}; DELETE FROM relationships_undirected WHERE user1 = ${deletedUserID};
DELETE FROM relationships_undirected WHERE user2 = ${deletedUserID}; DELETE FROM relationships_undirected WHERE user2 = ${deletedUserID};
DELETE FROM relationships_directed WHERE user1 = ${deletedUserID}; DELETE FROM relationships_directed WHERE user1 = ${deletedUserID};
DELETE FROM relationships_directed WHERE user2 = ${deletedUserID}; DELETE FROM relationships_directed WHERE user2 = ${deletedUserID};
COMMIT; COMMIT;
`; `;
const deletionPromise = dbQuery(deletionQuery, { multipleStatements: true }); const deletionPromise = dbQuery(deletionQuery, { multipleStatements: true });
▲ Show 20 Lines • Show All 78 Lines • Show Last 20 Lines
View Options

native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.h

Loading...
View Options

native/cpp/CommonCpp/DatabaseManagers/SQLiteQueryExecutor.cpp

Loading...
View Options

native/cpp/CommonCpp/NativeModules/CommCoreModule.h

Loading...
View Options

native/cpp/CommonCpp/NativeModules/CommCoreModule.cpp

Loading...
View Options

native/cpp/CommonCpp/_generated/commJSI-generated.cpp

Loading...
View Options

native/cpp/CommonCpp/_generated/commJSI.h

Loading...
View Options

native/database/sqlite-api.js

Loading...
View Options

native/schema/CommCoreModuleSchema.js

Loading...
View Options

web/cpp/SQLiteQueryExecutorBindings.cpp

Loading...
View Options

web/database/sqlite-api.js

Loading...
View Options

web/shared-worker/_generated/comm_query_executor.wasm

Loading...
View Options

web/shared-worker/queries/outbound-p2p-message-queries.test.js

Loading...
View Options

web/shared-worker/types/sqlite-query-executor.js

Loading...
View Options

web/shared-worker/worker/shared-worker.js

Loading...
View Options

web/types/worker-types.js

Loading...