b56453e7-a80d-44cc-af0b-79b3c35d69d4

Build # b56453e7-a80d-44cc-af0b-79b3c35d69d4

Build Type

push

circleci

Committed by GitHub

Commit Message

Merge pull request #2957 from mozilla/MNTOR-1491

Run Details

282 of 1603 branches covered (17.59%)

Branch coverage included in aggregate %.

4 of 4 new or added lines in 1 file covered. (100.0%)

959 of 4325 relevant lines covered (22.17%)

3.69 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 '../../app-constants.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
}

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,
  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 '../../app-constants.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	async function setBreachesLastShownNow (subscriber) {
128	// TODO: turn 2 db queries into a single query (also see #942)
129	const nowDateTime = new Date()	×
130	const nowTimeStamp = nowDateTime.toISOString()	×
131	await knex('subscribers')	×
132	.where('id', '=', subscriber.id)
133	.update({
134	breaches_last_shown: nowTimeStamp
135	})
136	return getSubscriberByEmail(subscriber.primary_email)	×
137	}
138
139	async function setAllEmailsToPrimary (subscriber, allEmailsToPrimary) {
140	const updated = await knex('subscribers')	×
141	.where('id', subscriber.id)
142	.update({
143	all_emails_to_primary: allEmailsToPrimary
144	})
145	.returning('*')
146	const updatedSubscriber = Array.isArray(updated) ? updated[0] : null	×
147	return updatedSubscriber	×
148	}
149
150	/**
151	* OBSOLETE, preserved for backwards compatibility
152	* TODO: Delete after monitor v2, only use setBreachResolution for v2
153	*
154	* @param {*} options {user, updatedResolvedBreaches}
155	* @returns subscriber
156	*/
157	async function setBreachesResolved (options) {
158	const { user, updatedResolvedBreaches } = options	×
159	await knex('subscribers')	×
160	.where('id', user.id)
161	.update({
162	breaches_resolved: updatedResolvedBreaches
163	})
164	return getSubscriberByEmail(user.primary_email)	×
165	}
166
167	/**
168	* Set "breach_resolution" column with the latest breach resolution object
169	* This column is meant to replace "breaches_resolved" column, which was used
170	* for v1.
171	*
172	* @param {object} user user object that contains the id of a user
173	* @param {object} updatedBreachesResolution {emailId: [{breachId: {isResolved: bool, resolutionsChecked: [BreachType]}}, {}...]}
174	* @returns subscriber
175	*/
176	async function setBreachResolution (user, updatedBreachesResolution) {
177	await knex('subscribers')	×
178	.where('id', user.id)
179	.update({
180	breach_resolution: updatedBreachesResolution
181	})
182	return getSubscriberByEmail(user.primary_email)	×
183	}
184
185	async function setWaitlistsJoined (options) {
186	const { user, updatedWaitlistsJoined } = options	×
187	await knex('subscribers')	×
188	.where('id', user.id)
189	.update({
190	waitlists_joined: updatedWaitlistsJoined
191	})
192	return getSubscriberByEmail(user.primary_email)	×
193	}
194
195	async function removeSubscriber (subscriber) {
196	await knex('email_addresses').where({ subscriber_id: subscriber.id }).del()	×
197	await knex('subscribers').where({ id: subscriber.id }).del()	×
198	}
199
200	async function removeSubscriberByToken (token, emailSha1) {
201	const subscriber = await getSubscriberByTokenAndHash(token, emailSha1)	×
202	if (!subscriber) {	×
203	return false	×
204	}
205	await knex('subscribers')	×
206	.where({
207	primary_verification_token: subscriber.primary_verification_token,
208	primary_sha1: subscriber.primary_sha1
209	})
210	.del()
211	return subscriber	×
212	}
213
214	async function deleteUnverifiedSubscribers () {
215	const expiredDateTime = new Date(Date.now() - DELETE_UNVERIFIED_SUBSCRIBERS_TIMER * 1000)	×
216	const expiredTimeStamp = expiredDateTime.toISOString()	×
217	const numDeleted = await knex('subscribers')	×
218	.where('primary_verified', false)
219	.andWhere('created_at', '<', expiredTimeStamp)
220	.del()
221	log.info('deleteUnverifiedSubscribers', { msg: `Deleted ${numDeleted} rows.` })	×
222	}
223
224	async function deleteSubscriberByFxAUID (fxaUID) {
225	await knex('subscribers').where('fxa_uid', fxaUID).del()	×
226	}
227
228	async function deleteResolutionsWithEmail (id, email) {
229	const [subscriber] = await knex('subscribers').where({	×
230	id
231	})
232	const { breach_resolution: breachResolution } = subscriber	×
233	// if email exists in breach resolution, remove it
234	if (breachResolution && breachResolution[email]) {	×
235	delete breachResolution[email]	×
236	console.info(`Deleting resolution with email: ${email}`)	×
237	return await setBreachResolution(subscriber, breachResolution)	×
238	}
239	console.info(`No resolution with ${email} found, skip`)	×
240	}
241
242	async function updateBreachStats (id, stats) {
243	await knex('subscribers')	×
244	.where('id', id)
245	.update({
246	breach_stats: stats
247	})
248	}
249
250	async function updateMonthlyEmailTimestamp (email) {
251	const res = await knex('subscribers').update({ monthly_email_at: 'now' })	×
252	.where('primary_email', email)
253	.returning('monthly_email_at')
254
255	return res	×
256	}
257
258	/**
259	* Unsubscribe user from monthly unresolved breach emails
260	*
261	* @param {string} token User verification token
262	*/
263	async function updateMonthlyEmailOptout (token) {
264	await knex('subscribers')	×
265	.update('monthly_email_optout', true)
266	.where('primary_verification_token', token)
267	}
268
269	function getSubscribersWithUnresolvedBreachesQuery () {
270	return knex('subscribers')	×
271	.whereRaw('monthly_email_optout IS NOT TRUE')
272	.whereRaw("greatest(created_at, monthly_email_at) < (now() - interval '30 days')")
273	.whereRaw("(breach_stats #>> '{numBreaches, numUnresolved}')::int > 0")
274	}
275
276	async function getSubscribersWithUnresolvedBreaches (limit = 0) {	×
277	let query = getSubscribersWithUnresolvedBreachesQuery()	×
278	.select('primary_email', 'primary_verification_token', 'breach_stats', 'signup_language')
279	if (limit) {	×
280	query = query.limit(limit).orderBy('created_at')	×
281	}
282	return await query	×
283	}
284
285	async function getSubscribersWithUnresolvedBreachesCount () {
286	const query = getSubscribersWithUnresolvedBreachesQuery()	×
287	const count = parseInt((await query.count({ count: '*' }))[0].count)	×
288	return count	×
289	}
290
291	/** Private */
292
293	async function joinEmailAddressesToSubscriber (subscriber) {
294	if (subscriber) {	×
295	const emailAddressRecords = await knex('email_addresses').where({	×
296	subscriber_id: subscriber.id
297	})
298	subscriber.email_addresses = emailAddressRecords.map(	×
299	emailAddress => ({ id: emailAddress.id, email: emailAddress.email })	×
300	)
301	}
302	return subscriber	×
303	}
304	export {
305	getSubscriberByToken,
306	getSubscribersByHashes,
307	getSubscriberByTokenAndHash,
308	getSubscriberById,
309	getSubscriberByFxaUid,
310	getSubscriberByEmail,
311	getSubscribersWithUnresolvedBreachesQuery,
312	getSubscribersWithUnresolvedBreaches,
313	getSubscribersWithUnresolvedBreachesCount,
314	updateFxAData,
315	removeFxAData,
316	updateFxAProfileData,
317	setBreachesLastShownNow,
318	setAllEmailsToPrimary,
319	setBreachesResolved,
320	setBreachResolution,
321	setWaitlistsJoined,
322	updateBreachStats,
323	updateMonthlyEmailTimestamp,
324	updateMonthlyEmailOptout,
325	removeSubscriber,
326	removeSubscriberByToken,
327	deleteUnverifiedSubscribers,
328	deleteSubscriberByFxAUID,
329	deleteResolutionsWithEmail
330	}

mozilla / blurts-server / b56453e7-a80d-44cc-af0b-79b3c35d69d4

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