diff --git a/server/src/scripts/add-replies-count-column.js b/server/src/scripts/add-replies-count-column.js deleted file mode 100644 index b1d24b25c..000000000 --- a/server/src/scripts/add-replies-count-column.js +++ /dev/null @@ -1,14 +0,0 @@ -// @flow - -import { dbQuery, SQL } from '../database/database'; -import { main } from './utils'; - -async function addColumn() { - const update = SQL` - ALTER TABLE threads - ADD replies_count INT UNSIGNED NOT NULL DEFAULT 0 - `; - await dbQuery(update); -} - -main([addColumn]); diff --git a/server/src/scripts/add-sender-column.js b/server/src/scripts/add-sender-column.js deleted file mode 100644 index cb1f123ab..000000000 --- a/server/src/scripts/add-sender-column.js +++ /dev/null @@ -1,14 +0,0 @@ -// @flow - -import { dbQuery, SQL } from '../database/database'; -import { main } from './utils'; - -async function addColumn() { - const update = SQL` - ALTER TABLE memberships - ADD sender TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 - `; - await dbQuery(update); -} - -main([addColumn]); diff --git a/server/src/scripts/compute-replies-count.js b/server/src/scripts/compute-replies-count.js deleted file mode 100644 index 7dca9f179..000000000 --- a/server/src/scripts/compute-replies-count.js +++ /dev/null @@ -1,50 +0,0 @@ -// @flow - -import { messageSpecs } from 'lib/shared/messages/message-specs'; -import { messageTypes } from 'lib/types/message-types'; -import { threadTypes } from 'lib/types/thread-types'; -import { updateTypes } from 'lib/types/update-types'; - -import { createUpdates } from '../creators/update-creator'; -import { dbQuery, SQL } from '../database/database'; -import { main } from './utils'; - -async function computeRepliesCount() { - const includedMessageTypes = Object.keys(messageTypes) - .map((key) => messageTypes[key]) - .filter((type) => messageSpecs[type].includedInRepliesCount); - - const sidebarMembersQuery = SQL` - SELECT t.id AS threadID, m.user AS userID - FROM threads t - INNER JOIN memberships m ON t.id = m.thread - WHERE t.type = ${threadTypes.SIDEBAR} - AND m.role >= 0 - `; - const readCountUpdate = SQL` - UPDATE threads t - INNER JOIN ( - SELECT thread AS threadID, COUNT(*) AS count - FROM messages - WHERE type IN (${includedMessageTypes}) - GROUP BY thread - ) c ON c.threadID = t.id - SET t.replies_count = c.count - WHERE t.type = ${threadTypes.SIDEBAR} - `; - const [[sidebarMembers]] = await Promise.all([ - dbQuery(sidebarMembersQuery), - dbQuery(readCountUpdate), - ]); - - const time = Date.now(); - const updates = sidebarMembers.map(({ threadID, userID }) => ({ - userID, - time, - threadID, - type: updateTypes.UPDATE_THREAD, - })); - await createUpdates(updates); -} - -main([computeRepliesCount]); diff --git a/server/src/scripts/determine-sender-status.js b/server/src/scripts/determine-sender-status.js deleted file mode 100644 index df12bca8c..000000000 --- a/server/src/scripts/determine-sender-status.js +++ /dev/null @@ -1,56 +0,0 @@ -// @flow - -import { messageSpecs } from 'lib/shared/messages/message-specs'; -import { messageTypes } from 'lib/types/message-types'; -import { updateTypes } from 'lib/types/update-types'; - -import { createUpdates } from '../creators/update-creator'; -import { dbQuery, mergeOrConditions, SQL } from '../database/database'; -import { main } from './utils'; - -async function determineSenderStatus() { - const includedMessageTypes = Object.keys(messageTypes) - .map((key) => messageTypes[key]) - .filter((type) => messageSpecs[type].includedInRepliesCount); - - const sendersQuery = SQL` - SELECT DISTINCT m.thread AS threadID, m.user AS userID - FROM messages m - WHERE m.type IN (${includedMessageTypes}) - `; - const [senders] = await dbQuery(sendersQuery); - - const conditions = senders.map( - ({ threadID, userID }) => SQL`thread = ${threadID} AND user = ${userID}`, - ); - const setSenders = SQL` - UPDATE memberships m - SET m.sender = 1 - WHERE - `; - setSenders.append(mergeOrConditions(conditions)); - - const updatedThreads = new Set(senders.map(({ threadID }) => threadID)); - const affectedMembersQuery = SQL` - SELECT thread AS threadID, user AS userID - FROM memberships - WHERE thread IN (${[...updatedThreads]}) - AND role >= 0 - `; - - const [[affectedMembers]] = await Promise.all([ - dbQuery(affectedMembersQuery), - dbQuery(setSenders), - ]); - - const time = Date.now(); - const updates = affectedMembers.map(({ threadID, userID }) => ({ - userID, - time, - threadID, - type: updateTypes.UPDATE_THREAD, - })); - await createUpdates(updates); -} - -main([determineSenderStatus]); diff --git a/server/src/scripts/setup-sidebars.js b/server/src/scripts/setup-sidebars.js new file mode 100644 index 000000000..5faf71ca5 --- /dev/null +++ b/server/src/scripts/setup-sidebars.js @@ -0,0 +1,126 @@ +// @flow + +import { messageSpecs } from 'lib/shared/messages/message-specs'; +import { messageTypes } from 'lib/types/message-types'; +import { threadTypes } from 'lib/types/thread-types'; +import { updateTypes } from 'lib/types/update-types'; + +import { createUpdates } from '../creators/update-creator'; +import { dbQuery, mergeOrConditions, SQL } from '../database/database'; +import { main } from './utils'; + +async function addRepliesCountColumn() { + const update = SQL` + ALTER TABLE threads + ADD replies_count INT UNSIGNED NOT NULL DEFAULT 0 + `; + + try { + await dbQuery(update); + } catch (e) { + console.log(e, 'replies-count column already exists'); + } +} + +async function addSenderColumn() { + const update = SQL` + ALTER TABLE memberships + ADD sender TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 + `; + + try { + await dbQuery(update); + } catch (e) { + console.log(e, 'sender column already exists'); + } +} + +async function computeRepliesCount() { + const includedMessageTypes = Object.keys(messageTypes) + .map((key) => messageTypes[key]) + .filter((type) => messageSpecs[type].includedInRepliesCount); + + const sidebarMembersQuery = SQL` + SELECT t.id AS threadID, m.user AS userID + FROM threads t + INNER JOIN memberships m ON t.id = m.thread + WHERE t.type = ${threadTypes.SIDEBAR} + AND m.role >= 0 + `; + const readCountUpdate = SQL` + UPDATE threads t + INNER JOIN ( + SELECT thread AS threadID, COUNT(*) AS count + FROM messages + WHERE type IN (${includedMessageTypes}) + GROUP BY thread + ) c ON c.threadID = t.id + SET t.replies_count = c.count + WHERE t.type = ${threadTypes.SIDEBAR} + `; + const [[sidebarMembers]] = await Promise.all([ + dbQuery(sidebarMembersQuery), + dbQuery(readCountUpdate), + ]); + + const time = Date.now(); + const updates = sidebarMembers.map(({ threadID, userID }) => ({ + userID, + time, + threadID, + type: updateTypes.UPDATE_THREAD, + })); + await createUpdates(updates); +} + +export async function determineSenderStatus() { + const includedMessageTypes = Object.keys(messageTypes) + .map((key) => messageTypes[key]) + .filter((type) => messageSpecs[type].includedInRepliesCount); + + const sendersQuery = SQL` + SELECT DISTINCT m.thread AS threadID, m.user AS userID + FROM messages m + WHERE m.type IN (${includedMessageTypes}) + `; + const [senders] = await dbQuery(sendersQuery); + + const conditions = senders.map( + ({ threadID, userID }) => SQL`thread = ${threadID} AND user = ${userID}`, + ); + const setSenders = SQL` + UPDATE memberships m + SET m.sender = 1 + WHERE + `; + setSenders.append(mergeOrConditions(conditions)); + + const updatedThreads = new Set(senders.map(({ threadID }) => threadID)); + const affectedMembersQuery = SQL` + SELECT thread AS threadID, user AS userID + FROM memberships + WHERE thread IN (${[...updatedThreads]}) + AND role >= 0 + `; + + const [[affectedMembers]] = await Promise.all([ + dbQuery(affectedMembersQuery), + dbQuery(setSenders), + ]); + + const time = Date.now(); + const updates = affectedMembers.map(({ threadID, userID }) => ({ + userID, + time, + threadID, + type: updateTypes.UPDATE_THREAD, + })); + await createUpdates(updates); +} + +main([ + addRepliesCountColumn, + addSenderColumn, + computeRepliesCount, + determineSenderStatus, +]);