Page MenuHomePhabricator
Differential D7982

[web-db] add migration logic to SQLite on web
ClosedPublic
Actions

Authored by kamil on May 25 2023, 10:25 AM.
Tags
None
Referenced Files
Unknown Object (File)
Tue, Apr 1, 3:09 PM
Unknown Object (File)
Wed, Mar 26, 11:15 AM
Unknown Object (File)
Tue, Mar 18, 2:33 AM
Unknown Object (File)
Tue, Mar 18, 2:33 AM
Unknown Object (File)
Tue, Mar 18, 2:32 AM
Unknown Object (File)
Tue, Mar 18, 2:23 AM
Unknown Object (File)
Thu, Mar 13, 1:40 AM
Unknown Object (File)
Sat, Mar 8, 7:32 AM
View All Files
Subscribers
ashoat
atul

Details

Reviewers
tomek
michal
Commits
rCOMM35313865ac8f: [web-db] add migration logic to SQLite on web
Summary

Similar to keyserver logic. Currently, nothing is happening when migration will fail but there is a followup task to improve error handling on web.

Depends on D7939

Test Plan
  1. Check if migrate is called when using old db
  2. Check in migrate is not called when creating fresh database
  3. Tests

Diff Detail

Repository
rCOMM Comm
Lint
Lint Not Applicable
Unit
Tests Not Applicable

Event Timeline

kamil created this revision.May 25 2023, 10:25 AM
kamil held this revision as a draft.
Herald added subscribers: atul, ashoat. · View Herald TranscriptMay 25 2023, 10:25 AM
kamil added a child revision: D7983: [web-db] implement SQL queries for report store.May 25 2023, 10:28 AM
Harbormaster completed remote builds in B19775: Diff 27067.May 25 2023, 10:43 AM
kamil published this revision for review.May 25 2023, 1:45 PM
michal accepted this revision.May 30 2023, 8:26 AM

I think you could test the migration code by mocking the migrations variable with jest, but not sure if that's worth it.

This revision is now accepted and ready to land.May 30 2023, 8:26 AM
Closed by commit rCOMM35313865ac8f: [web-db] add migration logic to SQLite on web (authored by kamil). · Explain WhyMay 31 2023, 10:11 AM
This revision was automatically updated to reflect the committed changes.
kamil added a commit: rCOMM35313865ac8f: [web-db] add migration logic to SQLite on web.

Revision Contents
Changeset List

PathSize
web/
database/
queries/
33 lines
12 lines
utils/
8 lines
worker/
6 lines

Diff 27310

View Options

web/database/queries/db-queries.js

Show First 20 Lines • Show All 226 Lines • ▼ Show 20 Lines const row = [
description, description,
viewer.userID, viewer.userID,
time, time,
color, color,
parentThreadID, parentThreadID,
threadAncestry.containingThreadID, threadAncestry.containingThreadID,
threadAncestry.community, threadAncestry.community,
threadAncestry.depth, threadAncestry.depth,
newRoles.default.id,
sourceMessageID, sourceMessageID,
]; ];
let existingThreadQuery = null; let existingThreadQuery = null;
if (threadType === threadTypes.PERSONAL) { if (threadType === threadTypes.PERSONAL) {
const otherMemberID = initialMemberIDs?.[0]; const otherMemberID = initialMemberIDs?.[0];
invariant( invariant(
otherMemberID, otherMemberID,
'Other member id should be set for a PERSONAL thread', 'Other member id should be set for a PERSONAL thread',
); );
existingThreadQuery = personalThreadQuery(viewer.userID, otherMemberID); existingThreadQuery = personalThreadQuery(viewer.userID, otherMemberID);
} else if (sourceMessageID) { } else if (sourceMessageID) {
existingThreadQuery = SQL` existingThreadQuery = SQL`
SELECT t.id SELECT t.id
FROM threads t FROM threads t
WHERE t.source_message = ${sourceMessageID} WHERE t.source_message = ${sourceMessageID}
`; `;
} }
if (existingThreadQuery) { if (existingThreadQuery) {
const query = SQL` const query = SQL`
INSERT INTO threads(id, type, name, description, creator, creation_time, INSERT INTO threads(id, type, name, description, creator, creation_time,
color, parent_thread_id, containing_thread_id, community, depth, color, parent_thread_id, containing_thread_id, community, depth,
default_role, source_message) source_message)
SELECT ${row} SELECT ${row}
WHERE NOT EXISTS (`; WHERE NOT EXISTS (`;
query.append(existingThreadQuery).append(SQL`)`); query.append(existingThreadQuery).append(SQL`)`);
const [result] = await dbQuery(query); const [result] = await dbQuery(query);
if (result.affectedRows === 0) { if (result.affectedRows === 0) {
const deleteRoles = SQL` const deleteRoles = SQL`
DELETE FROM roles DELETE FROM roles
▲ Show 20 Lines • Show All 50 Lines • ▼ Show 20 Lines if (result.affectedRows === 0) {
userInfos, userInfos,
newMessageInfos, newMessageInfos,
}; };
} }
} else { } else {
const query = SQL` const query = SQL`
INSERT INTO threads(id, type, name, description, creator, creation_time, INSERT INTO threads(id, type, name, description, creator, creation_time,
color, parent_thread_id, containing_thread_id, community, depth, color, parent_thread_id, containing_thread_id, community, depth,
default_role, source_message) source_message)
VALUES ${[row]} VALUES ${[row]}
`; `;
await dbQuery(query); await dbQuery(query);
} }
let initialMemberPromise; let initialMemberPromise;
if (initialMemberIDs) { if (initialMemberIDs) {
initialMemberPromise = changeRole(id, initialMemberIDs, null, { initialMemberPromise = changeRole(id, initialMemberIDs, null, {
▲ Show 20 Lines • Show All 178 Lines • Show Last 20 Lines
View Options

web/database/sql-js.test.js

Show First 20 Lines • Show All 226 Lines • ▼ Show 20 Lines const row = [
description, description,
viewer.userID, viewer.userID,
time, time,
color, color,
parentThreadID, parentThreadID,
threadAncestry.containingThreadID, threadAncestry.containingThreadID,
threadAncestry.community, threadAncestry.community,
threadAncestry.depth, threadAncestry.depth,
newRoles.default.id,
sourceMessageID, sourceMessageID,
]; ];
let existingThreadQuery = null; let existingThreadQuery = null;
if (threadType === threadTypes.PERSONAL) { if (threadType === threadTypes.PERSONAL) {
const otherMemberID = initialMemberIDs?.[0]; const otherMemberID = initialMemberIDs?.[0];
invariant( invariant(
otherMemberID, otherMemberID,
'Other member id should be set for a PERSONAL thread', 'Other member id should be set for a PERSONAL thread',
); );
existingThreadQuery = personalThreadQuery(viewer.userID, otherMemberID); existingThreadQuery = personalThreadQuery(viewer.userID, otherMemberID);
} else if (sourceMessageID) { } else if (sourceMessageID) {
existingThreadQuery = SQL` existingThreadQuery = SQL`
SELECT t.id SELECT t.id
FROM threads t FROM threads t
WHERE t.source_message = ${sourceMessageID} WHERE t.source_message = ${sourceMessageID}
`; `;
} }
if (existingThreadQuery) { if (existingThreadQuery) {
const query = SQL` const query = SQL`
INSERT INTO threads(id, type, name, description, creator, creation_time, INSERT INTO threads(id, type, name, description, creator, creation_time,
color, parent_thread_id, containing_thread_id, community, depth, color, parent_thread_id, containing_thread_id, community, depth,
default_role, source_message) source_message)
SELECT ${row} SELECT ${row}
WHERE NOT EXISTS (`; WHERE NOT EXISTS (`;
query.append(existingThreadQuery).append(SQL`)`); query.append(existingThreadQuery).append(SQL`)`);
const [result] = await dbQuery(query); const [result] = await dbQuery(query);
if (result.affectedRows === 0) { if (result.affectedRows === 0) {
const deleteRoles = SQL` const deleteRoles = SQL`
DELETE FROM roles DELETE FROM roles
▲ Show 20 Lines • Show All 50 Lines • ▼ Show 20 Lines if (result.affectedRows === 0) {
userInfos, userInfos,
newMessageInfos, newMessageInfos,
}; };
} }
} else { } else {
const query = SQL` const query = SQL`
INSERT INTO threads(id, type, name, description, creator, creation_time, INSERT INTO threads(id, type, name, description, creator, creation_time,
color, parent_thread_id, containing_thread_id, community, depth, color, parent_thread_id, containing_thread_id, community, depth,
default_role, source_message) source_message)
VALUES ${[row]} VALUES ${[row]}
`; `;
await dbQuery(query); await dbQuery(query);
} }
let initialMemberPromise; let initialMemberPromise;
if (initialMemberIDs) { if (initialMemberIDs) {
initialMemberPromise = changeRole(id, initialMemberIDs, null, { initialMemberPromise = changeRole(id, initialMemberIDs, null, {
▲ Show 20 Lines • Show All 178 Lines • Show Last 20 Lines
View Options

web/database/utils/migrations.js

Show First 20 Lines • Show All 226 Lines • ▼ Show 20 Lines const row = [
description, description,
viewer.userID, viewer.userID,
time, time,
color, color,
parentThreadID, parentThreadID,
threadAncestry.containingThreadID, threadAncestry.containingThreadID,
threadAncestry.community, threadAncestry.community,
threadAncestry.depth, threadAncestry.depth,
newRoles.default.id,
sourceMessageID, sourceMessageID,
]; ];
let existingThreadQuery = null; let existingThreadQuery = null;
if (threadType === threadTypes.PERSONAL) { if (threadType === threadTypes.PERSONAL) {
const otherMemberID = initialMemberIDs?.[0]; const otherMemberID = initialMemberIDs?.[0];
invariant( invariant(
otherMemberID, otherMemberID,
'Other member id should be set for a PERSONAL thread', 'Other member id should be set for a PERSONAL thread',
); );
existingThreadQuery = personalThreadQuery(viewer.userID, otherMemberID); existingThreadQuery = personalThreadQuery(viewer.userID, otherMemberID);
} else if (sourceMessageID) { } else if (sourceMessageID) {
existingThreadQuery = SQL` existingThreadQuery = SQL`
SELECT t.id SELECT t.id
FROM threads t FROM threads t
WHERE t.source_message = ${sourceMessageID} WHERE t.source_message = ${sourceMessageID}
`; `;
} }
if (existingThreadQuery) { if (existingThreadQuery) {
const query = SQL` const query = SQL`
INSERT INTO threads(id, type, name, description, creator, creation_time, INSERT INTO threads(id, type, name, description, creator, creation_time,
color, parent_thread_id, containing_thread_id, community, depth, color, parent_thread_id, containing_thread_id, community, depth,
default_role, source_message) source_message)
SELECT ${row} SELECT ${row}
WHERE NOT EXISTS (`; WHERE NOT EXISTS (`;
query.append(existingThreadQuery).append(SQL`)`); query.append(existingThreadQuery).append(SQL`)`);
const [result] = await dbQuery(query); const [result] = await dbQuery(query);
if (result.affectedRows === 0) { if (result.affectedRows === 0) {
const deleteRoles = SQL` const deleteRoles = SQL`
DELETE FROM roles DELETE FROM roles
▲ Show 20 Lines • Show All 50 Lines • ▼ Show 20 Lines if (result.affectedRows === 0) {
userInfos, userInfos,
newMessageInfos, newMessageInfos,
}; };
} }
} else { } else {
const query = SQL` const query = SQL`
INSERT INTO threads(id, type, name, description, creator, creation_time, INSERT INTO threads(id, type, name, description, creator, creation_time,
color, parent_thread_id, containing_thread_id, community, depth, color, parent_thread_id, containing_thread_id, community, depth,
default_role, source_message) source_message)
VALUES ${[row]} VALUES ${[row]}
`; `;
await dbQuery(query); await dbQuery(query);
} }
let initialMemberPromise; let initialMemberPromise;
if (initialMemberIDs) { if (initialMemberIDs) {
initialMemberPromise = changeRole(id, initialMemberIDs, null, { initialMemberPromise = changeRole(id, initialMemberIDs, null, {
▲ Show 20 Lines • Show All 178 Lines • Show Last 20 Lines
View Options

web/database/worker/db-worker.js

Show First 20 Lines • Show All 226 Lines • ▼ Show 20 Lines const row = [
description, description,
viewer.userID, viewer.userID,
time, time,
color, color,
parentThreadID, parentThreadID,
threadAncestry.containingThreadID, threadAncestry.containingThreadID,
threadAncestry.community, threadAncestry.community,
threadAncestry.depth, threadAncestry.depth,
newRoles.default.id,
sourceMessageID, sourceMessageID,
]; ];
let existingThreadQuery = null; let existingThreadQuery = null;
if (threadType === threadTypes.PERSONAL) { if (threadType === threadTypes.PERSONAL) {
const otherMemberID = initialMemberIDs?.[0]; const otherMemberID = initialMemberIDs?.[0];
invariant( invariant(
otherMemberID, otherMemberID,
'Other member id should be set for a PERSONAL thread', 'Other member id should be set for a PERSONAL thread',
); );
existingThreadQuery = personalThreadQuery(viewer.userID, otherMemberID); existingThreadQuery = personalThreadQuery(viewer.userID, otherMemberID);
} else if (sourceMessageID) { } else if (sourceMessageID) {
existingThreadQuery = SQL` existingThreadQuery = SQL`
SELECT t.id SELECT t.id
FROM threads t FROM threads t
WHERE t.source_message = ${sourceMessageID} WHERE t.source_message = ${sourceMessageID}
`; `;
} }
if (existingThreadQuery) { if (existingThreadQuery) {
const query = SQL` const query = SQL`
INSERT INTO threads(id, type, name, description, creator, creation_time, INSERT INTO threads(id, type, name, description, creator, creation_time,
color, parent_thread_id, containing_thread_id, community, depth, color, parent_thread_id, containing_thread_id, community, depth,
default_role, source_message) source_message)
SELECT ${row} SELECT ${row}
WHERE NOT EXISTS (`; WHERE NOT EXISTS (`;
query.append(existingThreadQuery).append(SQL`)`); query.append(existingThreadQuery).append(SQL`)`);
const [result] = await dbQuery(query); const [result] = await dbQuery(query);
if (result.affectedRows === 0) { if (result.affectedRows === 0) {
const deleteRoles = SQL` const deleteRoles = SQL`
DELETE FROM roles DELETE FROM roles
▲ Show 20 Lines • Show All 50 Lines • ▼ Show 20 Lines if (result.affectedRows === 0) {
userInfos, userInfos,
newMessageInfos, newMessageInfos,
}; };
} }
} else { } else {
const query = SQL` const query = SQL`
INSERT INTO threads(id, type, name, description, creator, creation_time, INSERT INTO threads(id, type, name, description, creator, creation_time,
color, parent_thread_id, containing_thread_id, community, depth, color, parent_thread_id, containing_thread_id, community, depth,
default_role, source_message) source_message)
VALUES ${[row]} VALUES ${[row]}
`; `;
await dbQuery(query); await dbQuery(query);
} }
let initialMemberPromise; let initialMemberPromise;
if (initialMemberIDs) { if (initialMemberIDs) {
initialMemberPromise = changeRole(id, initialMemberIDs, null, { initialMemberPromise = changeRole(id, initialMemberIDs, null, {
▲ Show 20 Lines • Show All 178 Lines • Show Last 20 Lines