17685697493

Committed 12 Sep 2025 08:48PM UTC coverage: 65.828% (+1.7%) from 64.102%

Build # 17685697493

Build Type

push

github

Committed by

web-flow

Commit Message

Merge pull request #1030 from bpatrik/db-projection

Implement projected (scoped/filtered) gallery. Fixes #1015

Run Details

1305 of 2243 branches covered (58.18%)

Branch coverage included in aggregate %.

706 of 873 new or added lines in 54 files covered. (80.87%)

16 existing lines in 10 files now uncovered.

4794 of 7022 relevant lines covered (68.27%)

4355.01 hits per line

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

96.24

/src/backend/model/database/PersonManager.ts

import {SQLConnection} from './SQLConnection';
import {PersonEntry} from './enitites/person/PersonEntry';
import {PersonDTO} from '../../../common/entities/PersonDTO';
import {Logger} from '../../Logger';
import {SQL_COLLATE} from './enitites/EntityUtils';
import {PersonJunctionTable} from './enitites/person/PersonJunctionTable';
import {ParentDirectoryDTO} from '../../../common/entities/DirectoryDTO';
import {ProjectedPersonCacheEntity} from './enitites/person/ProjectedPersonCacheEntity';
import {SessionContext} from '../SessionContext';
import {ProjectionAwareManager} from './ProjectionAwareManager';

const LOG_TAG = '[PersonManager]';

export class PersonManager extends ProjectionAwareManager<PersonEntry> {

  async updatePerson(
    name: string,
    partialPerson: PersonDTO
  ): Promise<PersonEntry> {
    const connection = await SQLConnection.getConnection();
    const repository = connection.getRepository(PersonEntry);
    const person = await repository
      .createQueryBuilder('person')
      .limit(1)
      .where('person.name LIKE :name COLLATE ' + SQL_COLLATE, {name})
      .getOne();

    if (typeof partialPerson.name !== 'undefined') {
      person.name = partialPerson.name;
    }
    if (typeof partialPerson.isFavourite !== 'undefined') {
      person.isFavourite = partialPerson.isFavourite;
    }
    await repository.save(person);

    // reset memory cache after person update. DB cache entry did not change, no need to reset that
    this.resetMemoryCache();

    return person;
  }

  /**
   * Used for statistic
   */
  public async countFaces(): Promise<number> {
    const connection = await SQLConnection.getConnection();
    return await connection
      .getRepository(PersonJunctionTable)
      .createQueryBuilder('personJunction')
      .getCount();
  }

  public async get(session: SessionContext, name: string): Promise<PersonEntry> {
    const persons = await this.getAll(session);
    return persons.find((p): boolean => p.name === name);
  }

  public async saveAll(
    persons: { name: string; mediaId: number }[]
  ): Promise<void> {
    const toSave: { name: string; mediaId: number }[] = [];
    const connection = await SQLConnection.getConnection();
    const personRepository = connection.getRepository(PersonEntry);
    const personJunction = connection.getRepository(PersonJunctionTable);

    const savedPersons = await personRepository.find();
    // filter already existing persons
    for (const personToSave of persons) {
      const person = savedPersons.find(
        (p): boolean => p.name === personToSave.name
      );
      if (!person) {
        toSave.push(personToSave);
      }
    }

    if (toSave.length > 0) {
      for (let i = 0; i < toSave.length / 200; i++) {
        const saving = toSave.slice(i * 200, (i + 1) * 200);
        // saving person
        const inserted = await personRepository.insert(
          saving.map((p) => ({name: p.name}))
        );
        // saving junction table
        const junctionTable = inserted.identifiers.map((idObj, j) => ({person: idObj, media: {id: saving[j].mediaId}}));
        await personJunction.insert(junctionTable);
      }
    }
  }

  protected async invalidateDBCache(changedDir?: ParentDirectoryDTO): Promise<void> {
    if (!changedDir || !changedDir.id) {
      await this.invalidateAllDBCache();
      return;
    }
    try {
      const connection = await SQLConnection.getConnection();

      // Collect affected person ids from this directory (non-recursive)
      const rows = await connection.getRepository(PersonJunctionTable)
        .createQueryBuilder('pjt')
        .innerJoin('pjt.media', 'm')
        .innerJoin('m.directory', 'd')
        .innerJoin('pjt.person', 'person')
        .where('d.id = :dirId', {dirId: changedDir.id})
        .select('DISTINCT person.id', 'pid')
        .getRawMany();

      const pids = rows.map((r: any) => parseInt(r.pid, 10)).filter((n: number) => !isNaN(n));
      if (pids.length === 0) {
        return;
      }

      // Mark projection-aware person cache entries invalid for these persons
      await connection.getRepository(ProjectedPersonCacheEntity)
        .createQueryBuilder()
        .update()
        .set({valid: false})
        .where('personId IN (:...pids)', {pids})
        .execute();
    } catch (err) {
      Logger.warn(LOG_TAG, 'Failed to invalidate projected person cache on data change', err);
    }
  }

  protected async loadEntities(session: SessionContext): Promise<PersonEntry[]> {
    await this.updateCacheForAll(session);
    const connection = await SQLConnection.getConnection();
    const personRepository = connection.getRepository(PersonEntry);
    return await personRepository
      .createQueryBuilder('person')
      .leftJoin('person.cache', 'cache', 'cache.projectionKey = :pk', {pk: session.user.projectionKey})
      .leftJoin('cache.sampleRegion', 'sampleRegion')
      .leftJoin('sampleRegion.media', 'media')
      .leftJoin('media.directory', 'directory')
      .select([
        'person.id',
        'person.name',
        'person.isFavourite',
        'cache.count',
        'sampleRegion',
        'media',
        'directory.path',
        'directory.name'
      ])
      .where('cache.valid = 1 AND cache.count > 0')
      .getMany();
  }

  private async invalidateAllDBCache(): Promise<void> {
    const connection = await SQLConnection.getConnection();
    await connection.getRepository(ProjectedPersonCacheEntity)
      .createQueryBuilder()
      .update()
      .set({valid: false})
      .execute();
  }

  private async updateCacheForAll(session: SessionContext): Promise<void> {
    const connection = await SQLConnection.getConnection();
    const projectionKey = session.user.projectionKey;

    // Get all persons that need cache updates (either missing or invalid)
    const personsNeedingUpdate = await connection
      .getRepository(PersonEntry)
      .createQueryBuilder('person')
      .leftJoin('person.cache', 'cache', 'cache.projectionKey = :projectionKey', {projectionKey})
      .where('cache.id IS NULL OR cache.valid = false')
      .select(['person.id'])
      .getMany();

    if (personsNeedingUpdate.length === 0) {
      return;
    }

    // Process persons in batches to avoid memory issues
    const batchSize = 200;
    for (let i = 0; i < personsNeedingUpdate.length; i += batchSize) {
      const batch = personsNeedingUpdate.slice(i, i + batchSize);
      const personIds = batch.map(p => p.id);

      // Build base query for person junction table with projection constraints
      const baseQb = connection
        .getRepository(PersonJunctionTable)
        .createQueryBuilder('pjt')
        .innerJoin('pjt.media', 'media')
        .where('pjt.person IN (:...personIds)', {personIds});

      // Apply projection query if it exists
      if (session.projectionQuery) {
        if (session.hasDirectoryProjection) {
          baseQb.leftJoin('media.directory', 'directory');
        }
        baseQb.andWhere(session.projectionQuery);
      }

      // Compute counts per person
      const countResults = await baseQb
        .clone()
        .select(['pjt.person as personId', 'COUNT(*) as count'])
        .groupBy('pjt.person')
        .getRawMany();
      // Compute sample regions per person (best rated/newest photo)
      // Use individual queries per person to ensure compatibility with older SQLite versions
      const topSamples: Record<number, number> = {};
      for (const personId of personIds) {
        const sampleQb = connection
          .getRepository(PersonJunctionTable)
          .createQueryBuilder('pjt')
          .innerJoin('pjt.media', 'media')
          .where('pjt.person = :personId', {personId});

        // Apply projection query if it exists
        if (session.projectionQuery) {
          if (session.hasDirectoryProjection) {
            sampleQb.leftJoin('media.directory', 'directory');
          }
          sampleQb.andWhere(session.projectionQuery);
        }

        const sampleResult = await sampleQb
          .select('pjt.id')
          .orderBy('media.metadataRating', 'DESC')
          .addOrderBy('media.metadataCreationdate', 'DESC')
          .limit(1)
          .getOne();

        if (sampleResult) {
          topSamples[personId] = sampleResult.id;
        }
      }

      // Build count lookup
      const counts = countResults.reduce((acc: Record<number, number>, r: any) => {
        acc[parseInt(r.personId, 10)] = parseInt(r.count, 10);
        return acc;
      }, {});

      // Batch upsert cache entries to minimize DB transactions
      const cacheRepo = connection.getRepository(ProjectedPersonCacheEntity);
      const cacheEntriesToSave: ProjectedPersonCacheEntity[] = [];

      // Get existing cache entries for this batch
      const existingEntries = await cacheRepo
        .createQueryBuilder('cache')
        .leftJoinAndSelect('cache.person', 'person')
        .where('cache.projectionKey = :projectionKey', {projectionKey})
        .andWhere('cache.person IN (:...personIds)', {personIds})
        .getMany();

      const existingByPersonId = existingEntries.reduce((acc, entry) => {
        acc[entry.person.id] = entry;
        return acc;
      }, {} as Record<number, ProjectedPersonCacheEntity>);

      for (const person of batch) {
        const count = counts[person.id] || 0;
        const sampleRegionId = topSamples[person.id] || null;

        let cacheEntry = existingByPersonId[person.id];
        if (cacheEntry) {
          // Update existing entry
          cacheEntry.count = count;
          cacheEntry.sampleRegion = sampleRegionId ? {id: sampleRegionId} as any : null;
          cacheEntry.valid = true;
        } else {
          // Create new entry
          cacheEntry = new ProjectedPersonCacheEntity();
          cacheEntry.projectionKey = projectionKey;
          cacheEntry.person = {id: person.id} as any;
          cacheEntry.count = count;
          cacheEntry.sampleRegion = sampleRegionId ? {id: sampleRegionId} as any : null;
          cacheEntry.valid = true;
        }

        cacheEntriesToSave.push(cacheEntry);
      }

      // Batch save all cache entries for this batch
      if (cacheEntriesToSave.length > 0) {
        await cacheRepo.save(cacheEntriesToSave);
      }
    }
  }

}

1	import {SQLConnection} from './SQLConnection';	1✔
2	import {PersonEntry} from './enitites/person/PersonEntry';	1✔
3	import {PersonDTO} from '../../../common/entities/PersonDTO';
4	import {Logger} from '../../Logger';	1✔
5	import {SQL_COLLATE} from './enitites/EntityUtils';	1✔
6	import {PersonJunctionTable} from './enitites/person/PersonJunctionTable';	1✔
7	import {ParentDirectoryDTO} from '../../../common/entities/DirectoryDTO';
8	import {ProjectedPersonCacheEntity} from './enitites/person/ProjectedPersonCacheEntity';	1✔
9	import {SessionContext} from '../SessionContext';
10	import {ProjectionAwareManager} from './ProjectionAwareManager';	1✔
11
12	const LOG_TAG = '[PersonManager]';	1✔
13
14	export class PersonManager extends ProjectionAwareManager<PersonEntry> {	1✔
15
16	async updatePerson(
17	name: string,
18	partialPerson: PersonDTO
19	): Promise<PersonEntry> {
20	const connection = await SQLConnection.getConnection();	2✔
21	const repository = connection.getRepository(PersonEntry);	2✔
22	const person = await repository	2✔
23	.createQueryBuilder('person')
24	.limit(1)
25	.where('person.name LIKE :name COLLATE ' + SQL_COLLATE, {name})
26	.getOne();
27
28	if (typeof partialPerson.name !== 'undefined') {	2✔
29	person.name = partialPerson.name;	2✔
30	}
31	if (typeof partialPerson.isFavourite !== 'undefined') {	2✔
32	person.isFavourite = partialPerson.isFavourite;	2✔
33	}
34	await repository.save(person);	2✔
35
36	// reset memory cache after person update. DB cache entry did not change, no need to reset that
37	this.resetMemoryCache();	2✔
38
39	return person;	2✔
40	}
41
42	/**
43	* Used for statistic
44	*/
45	public async countFaces(): Promise<number> {
46	const connection = await SQLConnection.getConnection();	4✔
47	return await connection	4✔
48	.getRepository(PersonJunctionTable)
49	.createQueryBuilder('personJunction')
50	.getCount();
51	}
52
53	public async get(session: SessionContext, name: string): Promise<PersonEntry> {
54	const persons = await this.getAll(session);	18✔
55	return persons.find((p): boolean => p.name === name);	70✔
56	}
57
58	public async saveAll(
59	persons: { name: string; mediaId: number }[]
60	): Promise<void> {
61	const toSave: { name: string; mediaId: number }[] = [];	244✔
62	const connection = await SQLConnection.getConnection();	244✔
63	const personRepository = connection.getRepository(PersonEntry);	244✔
64	const personJunction = connection.getRepository(PersonJunctionTable);	244✔
65
66	const savedPersons = await personRepository.find();	244✔
67	// filter already existing persons
68	for (const personToSave of persons) {	244✔
69	const person = savedPersons.find(	7,022✔
70	(p): boolean => p.name === personToSave.name	6,176✔
71	);
72	if (!person) {	7,022✔
73	toSave.push(personToSave);	6,956✔
74	}
75	}
76
77	if (toSave.length > 0) {	244✔
78	for (let i = 0; i < toSave.length / 200; i++) {	146✔
79	const saving = toSave.slice(i * 200, (i + 1) * 200);	176✔
80	// saving person
81	const inserted = await personRepository.insert(	176✔
82	saving.map((p) => ({name: p.name}))	6,956✔
83	);
84	// saving junction table
85	const junctionTable = inserted.identifiers.map((idObj, j) => ({person: idObj, media: {id: saving[j].mediaId}}));	6,956✔
86	await personJunction.insert(junctionTable);	176✔
87	}
88	}
89	}
90
91	protected async invalidateDBCache(changedDir?: ParentDirectoryDTO): Promise<void> {
92	if (!changedDir \|\| !changedDir.id) {	124✔
93	await this.invalidateAllDBCache();	118✔
94	return;	118✔
95	}
96	try {	6✔
97	const connection = await SQLConnection.getConnection();	6✔
98
99	// Collect affected person ids from this directory (non-recursive)
100	const rows = await connection.getRepository(PersonJunctionTable)	6✔
101	.createQueryBuilder('pjt')
102	.innerJoin('pjt.media', 'm')
103	.innerJoin('m.directory', 'd')
104	.innerJoin('pjt.person', 'person')
105	.where('d.id = :dirId', {dirId: changedDir.id})
106	.select('DISTINCT person.id', 'pid')
107	.getRawMany();
108
109	const pids = rows.map((r: any) => parseInt(r.pid, 10)).filter((n: number) => !isNaN(n));	28✔
110	if (pids.length === 0) {	6✔
111	return;	2✔
112	}
113
114	// Mark projection-aware person cache entries invalid for these persons
115	await connection.getRepository(ProjectedPersonCacheEntity)	4✔
116	.createQueryBuilder()
117	.update()
118	.set({valid: false})
119	.where('personId IN (:...pids)', {pids})
120	.execute();
121	} catch (err) {
NEW 122	Logger.warn(LOG_TAG, 'Failed to invalidate projected person cache on data change', err);	×
123	}
124	}
125
126	protected async loadEntities(session: SessionContext): Promise<PersonEntry[]> {
127	await this.updateCacheForAll(session);	40✔
128	const connection = await SQLConnection.getConnection();	40✔
129	const personRepository = connection.getRepository(PersonEntry);	40✔
130	return await personRepository	40✔
131	.createQueryBuilder('person')
132	.leftJoin('person.cache', 'cache', 'cache.projectionKey = :pk', {pk: session.user.projectionKey})
133	.leftJoin('cache.sampleRegion', 'sampleRegion')
134	.leftJoin('sampleRegion.media', 'media')
135	.leftJoin('media.directory', 'directory')
136	.select([
137	'person.id',
138	'person.name',
139	'person.isFavourite',
140	'cache.count',
141	'sampleRegion',
142	'media',
143	'directory.path',
144	'directory.name'
145	])
146	.where('cache.valid = 1 AND cache.count > 0')
147	.getMany();
148	}
149
150	private async invalidateAllDBCache(): Promise<void> {
151	const connection = await SQLConnection.getConnection();	118✔
152	await connection.getRepository(ProjectedPersonCacheEntity)	118✔
153	.createQueryBuilder()
154	.update()
155	.set({valid: false})
156	.execute();
157	}
158
159	private async updateCacheForAll(session: SessionContext): Promise<void> {
160	const connection = await SQLConnection.getConnection();	40✔
161	const projectionKey = session.user.projectionKey;	40✔
162
163	// Get all persons that need cache updates (either missing or invalid)
164	const personsNeedingUpdate = await connection	40✔
165	.getRepository(PersonEntry)
166	.createQueryBuilder('person')
167	.leftJoin('person.cache', 'cache', 'cache.projectionKey = :projectionKey', {projectionKey})
168	.where('cache.id IS NULL OR cache.valid = false')
169	.select(['person.id'])
170	.getMany();
171
172	if (personsNeedingUpdate.length === 0) {	40✔
173	return;	12✔
174	}
175
176	// Process persons in batches to avoid memory issues
177	const batchSize = 200;	28✔
178	for (let i = 0; i < personsNeedingUpdate.length; i += batchSize) {	28✔
179	const batch = personsNeedingUpdate.slice(i, i + batchSize);	28✔
180	const personIds = batch.map(p => p.id);	274✔
181
182	// Build base query for person junction table with projection constraints
183	const baseQb = connection	28✔
184	.getRepository(PersonJunctionTable)
185	.createQueryBuilder('pjt')
186	.innerJoin('pjt.media', 'media')
187	.where('pjt.person IN (:...personIds)', {personIds});
188
189	// Apply projection query if it exists
190	if (session.projectionQuery) {	28✔
191	if (session.hasDirectoryProjection) {	10!
NEW 192	baseQb.leftJoin('media.directory', 'directory');	×
193	}
194	baseQb.andWhere(session.projectionQuery);	10✔
195	}
196
197	// Compute counts per person
198	const countResults = await baseQb	28✔
199	.clone()
200	.select(['pjt.person as personId', 'COUNT(*) as count'])
201	.groupBy('pjt.person')
202	.getRawMany();
203	// Compute sample regions per person (best rated/newest photo)
204	// Use individual queries per person to ensure compatibility with older SQLite versions
205	const topSamples: Record<number, number> = {};	28✔
206	for (const personId of personIds) {	28✔
207	const sampleQb = connection	274✔
208	.getRepository(PersonJunctionTable)
209	.createQueryBuilder('pjt')
210	.innerJoin('pjt.media', 'media')
211	.where('pjt.person = :personId', {personId});
212
213	// Apply projection query if it exists
214	if (session.projectionQuery) {	274✔
215	if (session.hasDirectoryProjection) {	96!
NEW 216	sampleQb.leftJoin('media.directory', 'directory');	×
217	}
218	sampleQb.andWhere(session.projectionQuery);	96✔
219	}
220
221	const sampleResult = await sampleQb	274✔
222	.select('pjt.id')
223	.orderBy('media.metadataRating', 'DESC')
224	.addOrderBy('media.metadataCreationdate', 'DESC')
225	.limit(1)
226	.getOne();
227
228	if (sampleResult) {	274✔
229	topSamples[personId] = sampleResult.id;	196✔
230	}
231	}
232
233	// Build count lookup
234	const counts = countResults.reduce((acc: Record<number, number>, r: any) => {	28✔
235	acc[parseInt(r.personId, 10)] = parseInt(r.count, 10);	196✔
236	return acc;	196✔
237	}, {});
238
239	// Batch upsert cache entries to minimize DB transactions
240	const cacheRepo = connection.getRepository(ProjectedPersonCacheEntity);	28✔
241	const cacheEntriesToSave: ProjectedPersonCacheEntity[] = [];	28✔
242
243	// Get existing cache entries for this batch
244	const existingEntries = await cacheRepo	28✔
245	.createQueryBuilder('cache')
246	.leftJoinAndSelect('cache.person', 'person')
247	.where('cache.projectionKey = :projectionKey', {projectionKey})
248	.andWhere('cache.person IN (:...personIds)', {personIds})
249	.getMany();
250
251	const existingByPersonId = existingEntries.reduce((acc, entry) => {	28✔
252	acc[entry.person.id] = entry;	158✔
253	return acc;	158✔
254	}, {} as Record<number, ProjectedPersonCacheEntity>);
255
256	for (const person of batch) {	28✔
257	const count = counts[person.id] \|\| 0;	274✔
258	const sampleRegionId = topSamples[person.id] \|\| null;	274✔
259
260	let cacheEntry = existingByPersonId[person.id];	274✔
261	if (cacheEntry) {	274✔
262	// Update existing entry
263	cacheEntry.count = count;	158✔
264	cacheEntry.sampleRegion = sampleRegionId ? {id: sampleRegionId} as any : null;	158✔
265	cacheEntry.valid = true;	158✔
266	} else {
267	// Create new entry
268	cacheEntry = new ProjectedPersonCacheEntity();	116✔
269	cacheEntry.projectionKey = projectionKey;	116✔
270	cacheEntry.person = {id: person.id} as any;	116✔
271	cacheEntry.count = count;	116✔
272	cacheEntry.sampleRegion = sampleRegionId ? {id: sampleRegionId} as any : null;	116✔
273	cacheEntry.valid = true;	116✔
274	}
275
276	cacheEntriesToSave.push(cacheEntry);	274✔
277	}
278
279	// Batch save all cache entries for this batch
280	if (cacheEntriesToSave.length > 0) {	28✔
281	await cacheRepo.save(cacheEntriesToSave);	28✔
282	}
283	}
284	}
285
286	}

bpatrik / pigallery2 / 17685697493

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