#13322

pending completion

Build # #13322

Build Type

push

circleci

Committed by

web-flow

Commit Message

Merge pull request #3001 from mozilla/main

Merge `main` into `localization`

Run Details

282 of 1768 branches covered (15.95%)

Branch coverage included in aggregate %.

451 of 451 new or added lines in 56 files covered. (100.0%)

959 of 4670 relevant lines covered (20.54%)

1.72 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

0.0

/src/db/tables/subscribers.js

/* This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/. */

import { destroyOAuthToken } from '../../utils/fxa.js'
import Knex from 'knex'
import knexConfig from '../knexfile.js'
import AppConstants from '../../appConstants.js'
import mozlog from '../../utils/log.js'
const knex = Knex(knexConfig)
const { DELETE_UNVERIFIED_SUBSCRIBERS_TIMER } = AppConstants
const log = mozlog('DB.subscribers')

async function getSubscriberByToken (token) {
  const res = await knex('subscribers')
    .where('primary_verification_token', '=', token)

  return res[0]
}

async function getSubscriberByTokenAndHash (token, emailSha1) {
  const res = await knex.table('subscribers')
    .first()
    .where({
      primary_verification_token: token,
      primary_sha1: emailSha1
    })
  return res
}

async function getSubscribersByHashes (hashes) {
  return await knex('subscribers').whereIn('primary_sha1', hashes).andWhere('primary_verified', '=', true)
}

async function getSubscriberById (id) {
  const [subscriber] = await knex('subscribers').where({
    id
  })
  const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)
  return subscriberAndEmails
}

async function getSubscriberByFxaUid (uid) {
  const [subscriber] = await knex('subscribers').where({
    fxa_uid: uid
  })
  const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)
  return subscriberAndEmails
}

async function getSubscriberByEmail (email) {
  const [subscriber] = await knex('subscribers').where({
    primary_email: email,
    primary_verified: true
  })
  const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)
  return subscriberAndEmails
}
/**
 * Update fxa_refresh_token and fxa_profile_json for subscriber
 *
 * @param {object} subscriber knex object in DB
 * @param {string} fxaAccessToken from Firefox Account Oauth
 * @param {string} fxaRefreshToken from Firefox Account Oauth
 * @param {string} fxaProfileData from Firefox Account
 * @returns {object} updated subscriber knex object in DB
 */
async function updateFxAData (subscriber, fxaAccessToken, fxaRefreshToken, fxaProfileData) {
  const fxaUID = JSON.parse(fxaProfileData).uid
  const updated = await knex('subscribers')
    .where('id', '=', subscriber.id)
    .update({
      fxa_uid: fxaUID,
      fxa_access_token: fxaAccessToken,
      fxa_refresh_token: fxaRefreshToken,
      fxa_profile_json: fxaProfileData
    })
    .returning('*')
  const updatedSubscriber = Array.isArray(updated) ? updated[0] : null
  if (updatedSubscriber && subscriber.fxa_refresh_token) {
    destroyOAuthToken({ refresh_token: subscriber.fxa_refresh_token })
  }
  return updatedSubscriber
}

/**
 * Update fxa_profile_json for subscriber
 *
 * @param {object} subscriber knex object in DB
 * @param {string} fxaProfileData from Firefox Account
 * @returns {object} updated subscriber knex object in DB
 */
async function updateFxAProfileData (subscriber, fxaProfileData) {
  await knex('subscribers').where('id', subscriber.id)
    .update({
      fxa_profile_json: fxaProfileData
    })
  return getSubscriberById(subscriber.id)
}

/**
 * Remove fxa tokens and profile data for subscriber
 *
 * @param {object} subscriber knex object in DB
 * @returns {object} updated subscriber knex object in DB
 */
async function removeFxAData (subscriber) {
  log.debug('removeFxAData', subscriber)
  const updated = await knex('subscribers')
    .where('id', '=', subscriber.id)
    .update({
      fxa_access_token: null,
      fxa_refresh_token: null,
      fxa_profile_json: null
    })
    .returning('*')
  const updatedSubscriber = Array.isArray(updated) ? updated[0] : null
  if (updatedSubscriber && subscriber.fxa_refresh_token) {
    await destroyOAuthToken({ refresh_token: subscriber.fxa_refresh_token })
  }
  if (updatedSubscriber && subscriber.fxa_access_token) {
    await destroyOAuthToken({ token: subscriber.fxa_access_token })
  }
  return updatedSubscriber
}

/**
 * @param {import('./subscribers_types').SubscriberRow} subscriber
 * @param {number} onerepProfileId
 */
async function setOnerepProfileId (subscriber, onerepProfileId) {
  await knex('subscribers')
    .where('id', subscriber.id)
    .update({
      onerep_profile_id: onerepProfileId
    })
}

async function setBreachesLastShownNow (subscriber) {
  // TODO: turn 2 db queries into a single query (also see #942)
  const nowDateTime = new Date()
  const nowTimeStamp = nowDateTime.toISOString()
  await knex('subscribers')
    .where('id', '=', subscriber.id)
    .update({
      breaches_last_shown: nowTimeStamp
    })
  return getSubscriberByEmail(subscriber.primary_email)
}

async function setAllEmailsToPrimary (subscriber, allEmailsToPrimary) {
  const updated = await knex('subscribers')
    .where('id', subscriber.id)
    .update({
      all_emails_to_primary: allEmailsToPrimary
    })
    .returning('*')
  const updatedSubscriber = Array.isArray(updated) ? updated[0] : null
  return updatedSubscriber
}

/**
 * OBSOLETE, preserved for backwards compatibility
 * TODO: Delete after monitor v2, only use setBreachResolution for v2
 *
 * @param {*} options {user, updatedResolvedBreaches}
 * @returns subscriber
 */
async function setBreachesResolved (options) {
  const { user, updatedResolvedBreaches } = options
  await knex('subscribers')
    .where('id', user.id)
    .update({
      breaches_resolved: updatedResolvedBreaches
    })
  return getSubscriberByEmail(user.primary_email)
}

/**
 * Set "breach_resolution" column with the latest breach resolution object
 * This column is meant to replace "breaches_resolved" column, which was used
 * for v1.
 *
 * @param {object} user user object that contains the id of a user
 * @param {object} updatedBreachesResolution {emailId: [{breachId: {isResolved: bool, resolutionsChecked: [BreachType]}}, {}...]}
 * @returns subscriber
 */
async function setBreachResolution (user, updatedBreachesResolution) {
  await knex('subscribers')
    .where('id', user.id)
    .update({
      breach_resolution: updatedBreachesResolution
    })
  return getSubscriberByEmail(user.primary_email)
}

async function setWaitlistsJoined (options) {
  const { user, updatedWaitlistsJoined } = options
  await knex('subscribers')
    .where('id', user.id)
    .update({
      waitlists_joined: updatedWaitlistsJoined
    })
  return getSubscriberByEmail(user.primary_email)
}

async function removeSubscriber (subscriber) {
  await knex('email_addresses').where({ subscriber_id: subscriber.id }).del()
  await knex('subscribers').where({ id: subscriber.id }).del()
}

async function removeSubscriberByToken (token, emailSha1) {
  const subscriber = await getSubscriberByTokenAndHash(token, emailSha1)
  if (!subscriber) {
    return false
  }
  await knex('subscribers')
    .where({
      primary_verification_token: subscriber.primary_verification_token,
      primary_sha1: subscriber.primary_sha1
    })
    .del()
  return subscriber
}

async function deleteUnverifiedSubscribers () {
  const expiredDateTime = new Date(Date.now() - DELETE_UNVERIFIED_SUBSCRIBERS_TIMER * 1000)
  const expiredTimeStamp = expiredDateTime.toISOString()
  const numDeleted = await knex('subscribers')
    .where('primary_verified', false)
    .andWhere('created_at', '<', expiredTimeStamp)
    .del()
  log.info('deleteUnverifiedSubscribers', { msg: `Deleted ${numDeleted} rows.` })
}

async function deleteSubscriberByFxAUID (fxaUID) {
  await knex('subscribers').where('fxa_uid', fxaUID).del()
}

async function deleteResolutionsWithEmail (id, email) {
  const [subscriber] = await knex('subscribers').where({
    id
  })
  const { breach_resolution: breachResolution } = subscriber
  // if email exists in breach resolution, remove it
  if (breachResolution && breachResolution[email]) {
    delete breachResolution[email]
    console.info(`Deleting resolution with email: ${email}`)
    return await setBreachResolution(subscriber, breachResolution)
  }
  console.info(`No resolution with ${email} found, skip`)
}

async function updateBreachStats (id, stats) {
  await knex('subscribers')
    .where('id', id)
    .update({
      breach_stats: stats
    })
}

async function updateMonthlyEmailTimestamp (email) {
  const res = await knex('subscribers').update({ monthly_email_at: 'now' })
    .where('primary_email', email)
    .returning('monthly_email_at')

  return res
}

/**
 * Unsubscribe user from monthly unresolved breach emails
 *
 * @param {string} token User verification token
 */
async function updateMonthlyEmailOptout (token) {
  await knex('subscribers')
    .update('monthly_email_optout', true)
    .where('primary_verification_token', token)
}

function getSubscribersWithUnresolvedBreachesQuery () {
  return knex('subscribers')
    .whereRaw('monthly_email_optout IS NOT TRUE')
    .whereRaw("greatest(created_at, monthly_email_at) < (now() - interval '30 days')")
    .whereRaw("(breach_stats #>> '{numBreaches, numUnresolved}')::int > 0")
}

async function getSubscribersWithUnresolvedBreaches (limit = 0) {
  let query = getSubscribersWithUnresolvedBreachesQuery()
    .select('primary_email', 'primary_verification_token', 'breach_stats', 'signup_language')
  if (limit) {
    query = query.limit(limit).orderBy('created_at')
  }
  return await query
}

async function getSubscribersWithUnresolvedBreachesCount () {
  const query = getSubscribersWithUnresolvedBreachesQuery()
  const count = parseInt((await query.count({ count: '*' }))[0].count)
  return count
}

/** Private */

async function joinEmailAddressesToSubscriber (subscriber) {
  if (subscriber) {
    const emailAddressRecords = await knex('email_addresses').where({
      subscriber_id: subscriber.id
    })
    subscriber.email_addresses = emailAddressRecords.map(
      emailAddress => ({ id: emailAddress.id, email: emailAddress.email })
    )
  }
  return subscriber
}
export {
  getSubscriberByToken,
  getSubscribersByHashes,
  getSubscriberByTokenAndHash,
  getSubscriberById,
  getSubscriberByFxaUid,
  getSubscriberByEmail,
  getSubscribersWithUnresolvedBreachesQuery,
  getSubscribersWithUnresolvedBreaches,
  getSubscribersWithUnresolvedBreachesCount,
  updateFxAData,
  removeFxAData,
  updateFxAProfileData,
  setOnerepProfileId,
  setBreachesLastShownNow,
  setAllEmailsToPrimary,
  setBreachesResolved,
  setBreachResolution,
  setWaitlistsJoined,
  updateBreachStats,
  updateMonthlyEmailTimestamp,
  updateMonthlyEmailOptout,
  removeSubscriber,
  removeSubscriberByToken,
  deleteUnverifiedSubscribers,
  deleteSubscriberByFxAUID,
  deleteResolutionsWithEmail
}

1	/* This Source Code Form is subject to the terms of the Mozilla Public
2	* License, v. 2.0. If a copy of the MPL was not distributed with this
3	* file, You can obtain one at http://mozilla.org/MPL/2.0/. */
4
5	import { destroyOAuthToken } from '../../utils/fxa.js'
6	import Knex from 'knex'
7	import knexConfig from '../knexfile.js'
8	import AppConstants from '../../appConstants.js'
9	import mozlog from '../../utils/log.js'
10	const knex = Knex(knexConfig)	×
11	const { DELETE_UNVERIFIED_SUBSCRIBERS_TIMER } = AppConstants	×
12	const log = mozlog('DB.subscribers')	×
13
14	async function getSubscriberByToken (token) {
15	const res = await knex('subscribers')	×
16	.where('primary_verification_token', '=', token)
17
18	return res[0]	×
19	}
20
21	async function getSubscriberByTokenAndHash (token, emailSha1) {
22	const res = await knex.table('subscribers')	×
23	.first()
24	.where({
25	primary_verification_token: token,
26	primary_sha1: emailSha1
27	})
28	return res	×
29	}
30
31	async function getSubscribersByHashes (hashes) {
32	return await knex('subscribers').whereIn('primary_sha1', hashes).andWhere('primary_verified', '=', true)	×
33	}
34
35	async function getSubscriberById (id) {
36	const [subscriber] = await knex('subscribers').where({	×
37	id
38	})
39	const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)	×
40	return subscriberAndEmails	×
41	}
42
43	async function getSubscriberByFxaUid (uid) {
44	const [subscriber] = await knex('subscribers').where({	×
45	fxa_uid: uid
46	})
47	const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)	×
48	return subscriberAndEmails	×
49	}
50
51	async function getSubscriberByEmail (email) {
52	const [subscriber] = await knex('subscribers').where({	×
53	primary_email: email,
54	primary_verified: true
55	})
56	const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)	×
57	return subscriberAndEmails	×
58	}
59	/**
60	* Update fxa_refresh_token and fxa_profile_json for subscriber
61	*
62	* @param {object} subscriber knex object in DB
63	* @param {string} fxaAccessToken from Firefox Account Oauth
64	* @param {string} fxaRefreshToken from Firefox Account Oauth
65	* @param {string} fxaProfileData from Firefox Account
66	* @returns {object} updated subscriber knex object in DB
67	*/
68	async function updateFxAData (subscriber, fxaAccessToken, fxaRefreshToken, fxaProfileData) {
69	const fxaUID = JSON.parse(fxaProfileData).uid	×
70	const updated = await knex('subscribers')	×
71	.where('id', '=', subscriber.id)
72	.update({
73	fxa_uid: fxaUID,
74	fxa_access_token: fxaAccessToken,
75	fxa_refresh_token: fxaRefreshToken,
76	fxa_profile_json: fxaProfileData
77	})
78	.returning('*')
79	const updatedSubscriber = Array.isArray(updated) ? updated[0] : null	×
80	if (updatedSubscriber && subscriber.fxa_refresh_token) {	×
81	destroyOAuthToken({ refresh_token: subscriber.fxa_refresh_token })	×
82	}
83	return updatedSubscriber	×
84	}
85
86	/**
87	* Update fxa_profile_json for subscriber
88	*
89	* @param {object} subscriber knex object in DB
90	* @param {string} fxaProfileData from Firefox Account
91	* @returns {object} updated subscriber knex object in DB
92	*/
93	async function updateFxAProfileData (subscriber, fxaProfileData) {
94	await knex('subscribers').where('id', subscriber.id)	×
95	.update({
96	fxa_profile_json: fxaProfileData
97	})
98	return getSubscriberById(subscriber.id)	×
99	}
100
101	/**
102	* Remove fxa tokens and profile data for subscriber
103	*
104	* @param {object} subscriber knex object in DB
105	* @returns {object} updated subscriber knex object in DB
106	*/
107	async function removeFxAData (subscriber) {
108	log.debug('removeFxAData', subscriber)	×
109	const updated = await knex('subscribers')	×
110	.where('id', '=', subscriber.id)
111	.update({
112	fxa_access_token: null,
113	fxa_refresh_token: null,
114	fxa_profile_json: null
115	})
116	.returning('*')
117	const updatedSubscriber = Array.isArray(updated) ? updated[0] : null	×
118	if (updatedSubscriber && subscriber.fxa_refresh_token) {	×
119	await destroyOAuthToken({ refresh_token: subscriber.fxa_refresh_token })	×
120	}
121	if (updatedSubscriber && subscriber.fxa_access_token) {	×
122	await destroyOAuthToken({ token: subscriber.fxa_access_token })	×
123	}
124	return updatedSubscriber	×
125	}
126
127	/**
128	* @param {import('./subscribers_types').SubscriberRow} subscriber
129	* @param {number} onerepProfileId
130	*/
131	async function setOnerepProfileId (subscriber, onerepProfileId) {
132	await knex('subscribers')	×
133	.where('id', subscriber.id)
134	.update({
135	onerep_profile_id: onerepProfileId
136	})
137	}
138
139	async function setBreachesLastShownNow (subscriber) {
140	// TODO: turn 2 db queries into a single query (also see #942)
141	const nowDateTime = new Date()	×
142	const nowTimeStamp = nowDateTime.toISOString()	×
143	await knex('subscribers')	×
144	.where('id', '=', subscriber.id)
145	.update({
146	breaches_last_shown: nowTimeStamp
147	})
148	return getSubscriberByEmail(subscriber.primary_email)	×
149	}
150
151	async function setAllEmailsToPrimary (subscriber, allEmailsToPrimary) {
152	const updated = await knex('subscribers')	×
153	.where('id', subscriber.id)
154	.update({
155	all_emails_to_primary: allEmailsToPrimary
156	})
157	.returning('*')
158	const updatedSubscriber = Array.isArray(updated) ? updated[0] : null	×
159	return updatedSubscriber	×
160	}
161
162	/**
163	* OBSOLETE, preserved for backwards compatibility
164	* TODO: Delete after monitor v2, only use setBreachResolution for v2
165	*
166	* @param {*} options {user, updatedResolvedBreaches}
167	* @returns subscriber
168	*/
169	async function setBreachesResolved (options) {
170	const { user, updatedResolvedBreaches } = options	×
171	await knex('subscribers')	×
172	.where('id', user.id)
173	.update({
174	breaches_resolved: updatedResolvedBreaches
175	})
176	return getSubscriberByEmail(user.primary_email)	×
177	}
178
179	/**
180	* Set "breach_resolution" column with the latest breach resolution object
181	* This column is meant to replace "breaches_resolved" column, which was used
182	* for v1.
183	*
184	* @param {object} user user object that contains the id of a user
185	* @param {object} updatedBreachesResolution {emailId: [{breachId: {isResolved: bool, resolutionsChecked: [BreachType]}}, {}...]}
186	* @returns subscriber
187	*/
188	async function setBreachResolution (user, updatedBreachesResolution) {
189	await knex('subscribers')	×
190	.where('id', user.id)
191	.update({
192	breach_resolution: updatedBreachesResolution
193	})
194	return getSubscriberByEmail(user.primary_email)	×
195	}
196
197	async function setWaitlistsJoined (options) {
198	const { user, updatedWaitlistsJoined } = options	×
199	await knex('subscribers')	×
200	.where('id', user.id)
201	.update({
202	waitlists_joined: updatedWaitlistsJoined
203	})
204	return getSubscriberByEmail(user.primary_email)	×
205	}
206
207	async function removeSubscriber (subscriber) {
208	await knex('email_addresses').where({ subscriber_id: subscriber.id }).del()	×
209	await knex('subscribers').where({ id: subscriber.id }).del()	×
210	}
211
212	async function removeSubscriberByToken (token, emailSha1) {
213	const subscriber = await getSubscriberByTokenAndHash(token, emailSha1)	×
214	if (!subscriber) {	×
215	return false	×
216	}
217	await knex('subscribers')	×
218	.where({
219	primary_verification_token: subscriber.primary_verification_token,
220	primary_sha1: subscriber.primary_sha1
221	})
222	.del()
223	return subscriber	×
224	}
225
226	async function deleteUnverifiedSubscribers () {
227	const expiredDateTime = new Date(Date.now() - DELETE_UNVERIFIED_SUBSCRIBERS_TIMER * 1000)	×
228	const expiredTimeStamp = expiredDateTime.toISOString()	×
229	const numDeleted = await knex('subscribers')	×
230	.where('primary_verified', false)
231	.andWhere('created_at', '<', expiredTimeStamp)
232	.del()
233	log.info('deleteUnverifiedSubscribers', { msg: `Deleted ${numDeleted} rows.` })	×
234	}
235
236	async function deleteSubscriberByFxAUID (fxaUID) {
237	await knex('subscribers').where('fxa_uid', fxaUID).del()	×
238	}
239
240	async function deleteResolutionsWithEmail (id, email) {
241	const [subscriber] = await knex('subscribers').where({	×
242	id
243	})
244	const { breach_resolution: breachResolution } = subscriber	×
245	// if email exists in breach resolution, remove it
246	if (breachResolution && breachResolution[email]) {	×
247	delete breachResolution[email]	×
248	console.info(`Deleting resolution with email: ${email}`)	×
249	return await setBreachResolution(subscriber, breachResolution)	×
250	}
251	console.info(`No resolution with ${email} found, skip`)	×
252	}
253
254	async function updateBreachStats (id, stats) {
255	await knex('subscribers')	×
256	.where('id', id)
257	.update({
258	breach_stats: stats
259	})
260	}
261
262	async function updateMonthlyEmailTimestamp (email) {
263	const res = await knex('subscribers').update({ monthly_email_at: 'now' })	×
264	.where('primary_email', email)
265	.returning('monthly_email_at')
266
267	return res	×
268	}
269
270	/**
271	* Unsubscribe user from monthly unresolved breach emails
272	*
273	* @param {string} token User verification token
274	*/
275	async function updateMonthlyEmailOptout (token) {
276	await knex('subscribers')	×
277	.update('monthly_email_optout', true)
278	.where('primary_verification_token', token)
279	}
280
281	function getSubscribersWithUnresolvedBreachesQuery () {
282	return knex('subscribers')	×
283	.whereRaw('monthly_email_optout IS NOT TRUE')
284	.whereRaw("greatest(created_at, monthly_email_at) < (now() - interval '30 days')")
285	.whereRaw("(breach_stats #>> '{numBreaches, numUnresolved}')::int > 0")
286	}
287
288	async function getSubscribersWithUnresolvedBreaches (limit = 0) {	×
289	let query = getSubscribersWithUnresolvedBreachesQuery()	×
290	.select('primary_email', 'primary_verification_token', 'breach_stats', 'signup_language')
291	if (limit) {	×
292	query = query.limit(limit).orderBy('created_at')	×
293	}
294	return await query	×
295	}
296
297	async function getSubscribersWithUnresolvedBreachesCount () {
298	const query = getSubscribersWithUnresolvedBreachesQuery()	×
299	const count = parseInt((await query.count({ count: '*' }))[0].count)	×
300	return count	×
301	}
302
303	/** Private */
304
305	async function joinEmailAddressesToSubscriber (subscriber) {
306	if (subscriber) {	×
307	const emailAddressRecords = await knex('email_addresses').where({	×
308	subscriber_id: subscriber.id
309	})
310	subscriber.email_addresses = emailAddressRecords.map(	×
311	emailAddress => ({ id: emailAddress.id, email: emailAddress.email })	×
312	)
313	}
314	return subscriber	×
315	}
316	export {
317	getSubscriberByToken,
318	getSubscribersByHashes,
319	getSubscriberByTokenAndHash,
320	getSubscriberById,
321	getSubscriberByFxaUid,
322	getSubscriberByEmail,
323	getSubscribersWithUnresolvedBreachesQuery,
324	getSubscribersWithUnresolvedBreaches,
325	getSubscribersWithUnresolvedBreachesCount,
326	updateFxAData,
327	removeFxAData,
328	updateFxAProfileData,
329	setOnerepProfileId,
330	setBreachesLastShownNow,
331	setAllEmailsToPrimary,
332	setBreachesResolved,
333	setBreachResolution,
334	setWaitlistsJoined,
335	updateBreachStats,
336	updateMonthlyEmailTimestamp,
337	updateMonthlyEmailOptout,
338	removeSubscriber,
339	removeSubscriberByToken,
340	deleteUnverifiedSubscribers,
341	deleteSubscriberByFxAUID,
342	deleteResolutionsWithEmail
343	}

mozilla / blurts-server / #13322

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous