19852291005

Committed 02 Dec 2025 08:37AM UTC coverage: 85.233% (-0.03%) from 85.262%

Build # 19852291005

Build Type

push

github

Committed by

vmarseguerra

Commit Message

feat(sql): uses the pg_cron extension to periodically refresh materialized views

Run Details

2519 of 3113 branches covered (80.92%)

Branch coverage included in aggregate %.

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

2 existing lines in 1 file now uncovered.

5342 of 6110 relevant lines covered (87.43%)

22.82 hits per line

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

93.68

/api/services/RecentChangeService.js

const CommonService = require('./CommonService');

const GROUP_MAX_TIME_DIFF_S = 60 * 60 * 6; // 6 Hours

async function removeOlderChanges() {
  const query = `DELETE FROM t_last_change WHERE date_change < current_timestamp - interval '1 month';`;
  await CommonService.query(query);
}

// When creating a cave, entrance, massif, document or grotto the name is created after the entity
// So the name is updated afterward
async function setNameCreate(entityType, entityId, authorId, name) {
  const query = `
  UPDATE t_last_change
  SET name = $1
  WHERE type_entity = $2 AND type_change = 'create' AND id_entity = $3 AND id_author = $4 AND date_change > current_timestamp - interval '1 minute';
  `;
  await CommonService.query(query, [name, entityType, entityId, authorId]);
}

// When deleting / restoring an entity, the author is not historized, so it is updated afterward
async function setDeleteRestoreAuthor(
  changeType,
  entityType,
  entityId,
  authorId
) {
  const query = `
  UPDATE t_last_change
  SET id_author = $4
  WHERE type_entity = $2 AND type_change = $1 AND id_entity = $3 AND date_change > current_timestamp - interval '1 minute';
  `;
  await CommonService.query(query, [
    changeType,
    entityType,
    entityId,
    authorId,
  ]);
}

// To make the change list more relevant we groups change event when they are from the same author and about the same entity
function groupChanges(changes) {
  const authorChanges = {};
  // Grouping order:
  // - author
  // - related entity
  // - time
  // Change type 'create' or 'restore' have priority over 'update'
  // Change type 'delete' is never grouped

  const createNewGroupFromChange = (c) => ({
    date: c.date_change,
    authorId: c.id_author,
    author: c.nickname,
    mainEntityType: c.type_related_entity ?? c.type_entity,
    mainEntityId: c.id_related_entity ?? c.id_entity,
    mainAction: c.id_related_entity ? null : c.type_change, // Null in case it is a change on a sub entity
    subEntityTypes: c.id_related_entity ? [c.type_entity] : [],
    subAction: c.id_related_entity ? c.type_change : null, // Null when no sub entities
    name: c.name, // Can be the real entity name or the related entity name
  });

  const addChangeToExistingGroup = (g, c) => {
    if (
      !c.id_related_entity &&
      g.mainAction === 'update' &&
      ['create', 'restore'].includes(c.type_change)
    )
      g.mainAction = c.type_change; // eslint-disable-line no-param-reassign
    if (c.id_related_entity && !g.subEntityTypes.includes(c.type_entity))
      g.subEntityTypes.push(c.type_entity);
    if (c.id_related_entity && g.subAction !== c.type_change)
      g.subAction = 'change'; // eslint-disable-line no-param-reassign
  };

  for (const change of changes) {
    if (!authorChanges[change.id_author]) {
      authorChanges[change.id_author] = [createNewGroupFromChange(change)];
      continue; // eslint-disable-line no-continue
    }

    const authorsChanges = authorChanges[change.id_author];
    const previousChangeForThisEntity = authorsChanges.find(
      (e) =>
        e.mainEntityId === (change.id_related_entity ?? change.id_entity) &&
        e.mainEntityType === (change.type_related_entity ?? change.type_entity)
    );

    if (
      !previousChangeForThisEntity ||
      Math.abs(previousChangeForThisEntity.date - change.date_change) >
        GROUP_MAX_TIME_DIFF_S * 1000 ||
      change.type_change === 'delete' ||
      previousChangeForThisEntity.mainAction === 'delete'
    ) {
      authorsChanges.unshift(createNewGroupFromChange(change));
      continue; // eslint-disable-line no-continue
    }
    addChangeToExistingGroup(previousChangeForThisEntity, change);
  }

  const allGroups = Object.values(authorChanges).flat();
  return allGroups.sort((a, b) => b.date - a.date);
}

async function getRecent() {
  // The t_last_change table is populated by trigger on the other main tables
  const query = `
  SELECT tbl.*, author.nickname FROM t_last_change tbl
  LEFT JOIN t_caver author ON tbl.id_author = author.id
  ORDER BY date_change DESC
  `;
  const rep = await CommonService.query(query);

  // When creating/updating a entrance the associated cave is also created/updated (when not part of a network)
  // As cave changes are duplicate we filter out them
  const changes = rep.rows.filter((e, i, a) => {
    if (e.type_entity !== 'cave') return true;

    // Is the cave change is after the entrance change ?
    if (
      i < a.length - 2 &&
      a[i + 1].date_change - e.date_change < 5000 &&
      a[i + 1].id_author === e.id_author &&
      a[i + 1].type_entity === 'entrance'
    )
      return false;

    // Is the cave change is before the entrance change ?
    if (
      i > 0 &&
      e.date_change - a[i - 1].date_change < 5000 &&
      a[i - 1].id_author === e.id_author &&
      a[i - 1].type_entity === 'entrance'
    )
      return false;

    return true;
  });

  // 5% chance to also remove older changes
  if (Math.random() < 0.05) removeOlderChanges();

  return groupChanges(changes);
}

module.exports = {
  getRecent,
  setNameCreate,
  setDeleteRestoreAuthor,
};

1	const CommonService = require('./CommonService');	7✔
2
3	const GROUP_MAX_TIME_DIFF_S = 60 * 60 * 6; // 6 Hours	7✔
4
5	async function removeOlderChanges() {
UNCOV 6	const query = `DELETE FROM t_last_change WHERE date_change < current_timestamp - interval '1 month';`;	×
UNCOV 7	await CommonService.query(query);	×
8	}
9
10	// When creating a cave, entrance, massif, document or grotto the name is created after the entity
11	// So the name is updated afterward
12	async function setNameCreate(entityType, entityId, authorId, name) {
13	const query = `	23✔
14	UPDATE t_last_change
15	SET name = $1
16	WHERE type_entity = $2 AND type_change = 'create' AND id_entity = $3 AND id_author = $4 AND date_change > current_timestamp - interval '1 minute';
17	`;
18	await CommonService.query(query, [name, entityType, entityId, authorId]);	23✔
19	}
20
21	// When deleting / restoring an entity, the author is not historized, so it is updated afterward
22	async function setDeleteRestoreAuthor(
23	changeType,
24	entityType,
25	entityId,
26	authorId
27	) {
28	const query = `	29✔
29	UPDATE t_last_change
30	SET id_author = $4
31	WHERE type_entity = $2 AND type_change = $1 AND id_entity = $3 AND date_change > current_timestamp - interval '1 minute';
32	`;
33	await CommonService.query(query, [	29✔
34	changeType,
35	entityType,
36	entityId,
37	authorId,
38	]);
39	}
40
41	// To make the change list more relevant we groups change event when they are from the same author and about the same entity
42	function groupChanges(changes) {
43	const authorChanges = {};	19✔
44	// Grouping order:
45	// - author
46	// - related entity
47	// - time
48	// Change type 'create' or 'restore' have priority over 'update'
49	// Change type 'delete' is never grouped
50
51	const createNewGroupFromChange = (c) => ({	19✔
52	date: c.date_change,
53	authorId: c.id_author,
54	author: c.nickname,
55	mainEntityType: c.type_related_entity ?? c.type_entity,	23✔
56	mainEntityId: c.id_related_entity ?? c.id_entity,	23✔
57	mainAction: c.id_related_entity ? null : c.type_change, // Null in case it is a change on a sub entity	13✔
58	subEntityTypes: c.id_related_entity ? [c.type_entity] : [],	13✔
59	subAction: c.id_related_entity ? c.type_change : null, // Null when no sub entities	13✔
60	name: c.name, // Can be the real entity name or the related entity name
61	});
62
63	const addChangeToExistingGroup = (g, c) => {	19✔
64	if (	4!
65	!c.id_related_entity &&	6!
66	g.mainAction === 'update' &&
67	['create', 'restore'].includes(c.type_change)
68	)
69	g.mainAction = c.type_change; // eslint-disable-line no-param-reassign	×
70	if (c.id_related_entity && !g.subEntityTypes.includes(c.type_entity))	4✔
71	g.subEntityTypes.push(c.type_entity);	1✔
72	if (c.id_related_entity && g.subAction !== c.type_change)	4✔
73	g.subAction = 'change'; // eslint-disable-line no-param-reassign	2✔
74	};
75
76	for (const change of changes) {	19✔
77	if (!authorChanges[change.id_author]) {	17✔
78	authorChanges[change.id_author] = [createNewGroupFromChange(change)];	11✔
79	continue; // eslint-disable-line no-continue	11✔
80	}
81
82	const authorsChanges = authorChanges[change.id_author];	6✔
83	const previousChangeForThisEntity = authorsChanges.find(	6✔
84	(e) =>
85	e.mainEntityId === (change.id_related_entity ?? change.id_entity) &&	6✔
86	e.mainEntityType === (change.type_related_entity ?? change.type_entity)	10✔
87	);
88
89	if (	6✔
90	!previousChangeForThisEntity \|\|	22✔
91	Math.abs(previousChangeForThisEntity.date - change.date_change) >
92	GROUP_MAX_TIME_DIFF_S * 1000 \|\|
93	change.type_change === 'delete' \|\|
94	previousChangeForThisEntity.mainAction === 'delete'
95	) {
96	authorsChanges.unshift(createNewGroupFromChange(change));	2✔
97	continue; // eslint-disable-line no-continue	2✔
98	}
99	addChangeToExistingGroup(previousChangeForThisEntity, change);	4✔
100	}
101
102	const allGroups = Object.values(authorChanges).flat();	19✔
103	return allGroups.sort((a, b) => b.date - a.date);	19✔
104	}
105
106	async function getRecent() {
107	// The t_last_change table is populated by trigger on the other main tables
108	const query = `	19✔
109	SELECT tbl.*, author.nickname FROM t_last_change tbl
110	LEFT JOIN t_caver author ON tbl.id_author = author.id
111	ORDER BY date_change DESC
112	`;
113	const rep = await CommonService.query(query);	19✔
114
115	// When creating/updating a entrance the associated cave is also created/updated (when not part of a network)
116	// As cave changes are duplicate we filter out them
117	const changes = rep.rows.filter((e, i, a) => {	19✔
118	if (e.type_entity !== 'cave') return true;	19✔
119
120	// Is the cave change is after the entrance change ?
121	if (	3✔
122	i < a.length - 2 &&	6✔
123	a[i + 1].date_change - e.date_change < 5000 &&
124	a[i + 1].id_author === e.id_author &&
125	a[i + 1].type_entity === 'entrance'
126	)
127	return false;	1✔
128
129	// Is the cave change is before the entrance change ?
130	if (	2✔
131	i > 0 &&	5✔
132	e.date_change - a[i - 1].date_change < 5000 &&
133	a[i - 1].id_author === e.id_author &&
134	a[i - 1].type_entity === 'entrance'
135	)
136	return false;	1✔
137
138	return true;	1✔
139	});
140
141	// 5% chance to also remove older changes
142	if (Math.random() < 0.05) removeOlderChanges();	19!
143
144	return groupChanges(changes);	19✔
145	}
146
147	module.exports = {	7✔
148	getRecent,
149	setNameCreate,
150	setDeleteRestoreAuthor,
151	};

GrottoCenter / grottocenter-api / 19852291005

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