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

84.43

/src/backend/model/database/SQLConnection.ts

import 'reflect-metadata';
import {Connection, createConnection, DataSourceOptions, getConnection, LoggerOptions,} from 'typeorm';
import {UserEntity} from './enitites/UserEntity';
import {UserRoles} from '../../../common/entities/UserDTO';
import {PhotoEntity} from './enitites/PhotoEntity';
import {DirectoryEntity} from './enitites/DirectoryEntity';
import {Config} from '../../../common/config/private/Config';
import {SharingEntity} from './enitites/SharingEntity';
import {PasswordHelper} from '../PasswordHelper';
import {ProjectPath} from '../../ProjectPath';
import {VersionEntity} from './enitites/VersionEntity';
import {Logger} from '../../Logger';
import {MediaEntity} from './enitites/MediaEntity';
import {VideoEntity} from './enitites/VideoEntity';
import {DataStructureVersion} from '../../../common/DataStructureVersion';
import {FileEntity} from './enitites/FileEntity';
import {PersonEntry} from './enitites/person/PersonEntry';
import {Utils} from '../../../common/Utils';
import * as path from 'path';
import {DatabaseType, ServerDataBaseConfig, SQLLogLevel,} from '../../../common/config/private/PrivateConfig';
import {AlbumBaseEntity} from './enitites/album/AlbumBaseEntity';
import {SavedSearchEntity} from './enitites/album/SavedSearchEntity';
import {NotificationManager} from '../NotifocationManager';
import {PersonJunctionTable} from './enitites/person/PersonJunctionTable';
import {MDFileEntity} from './enitites/MDFileEntity';
import {ProjectedDirectoryCacheEntity} from './enitites/ProjectedDirectoryCacheEntity';
import {ProjectedPersonCacheEntity} from './enitites/person/ProjectedPersonCacheEntity';
import {ProjectedAlbumCacheEntity} from './enitites/album/ProjectedAlbumCacheEntity';

const LOG_TAG = '[SQLConnection]';

type Writeable<T> = { -readonly [P in keyof T]: T[P] };

export class SQLConnection {
  // eslint-disable-next-line @typescript-eslint/ban-types
  private static entries: Function[] = [
    UserEntity,
    FileEntity,
    MDFileEntity,
    PersonJunctionTable,
    PersonEntry,
    MediaEntity,
    PhotoEntity,
    VideoEntity,
    DirectoryEntity,
    SharingEntity,
    AlbumBaseEntity,
    SavedSearchEntity,
    VersionEntity,
    // projection-aware cache entries
    ProjectedDirectoryCacheEntity,
    ProjectedPersonCacheEntity,
    ProjectedAlbumCacheEntity
  ];
  private static connection: Connection = null;
  private static FIXED_SQL_TABLE = [
    'sqlite_sequence'
  ];

  // eslint-disable-next-line @typescript-eslint/ban-types
  public static getEntries(): Function[] {
    return this.entries;
  }

  // eslint-disable-next-line @typescript-eslint/ban-types
  public static async addEntries(tables: Function[]) {
    if (!tables?.length) {
      return;
    }
    await this.close();
    this.entries = Utils.getUnique(this.entries.concat(tables));
    await (await this.getConnection()).synchronize();
  }

  public static async getConnection(): Promise<Connection> {
    if (this.connection == null) {
      const options = this.getDriver(Config.Database);

      Logger.debug(
        LOG_TAG,
        'Creating connection: ' + DatabaseType[Config.Database.type],
        ', with driver:',
        options.type
      );
      this.connection = await this.createConnection(options);
      await SQLConnection.schemeSync(this.connection);
    }
    return this.connection;
  }

  public static async tryConnection(
    config: ServerDataBaseConfig
  ): Promise<boolean> {
    try {
      await getConnection('test').close();
      // eslint-disable-next-line no-empty
    } catch (err) {
    }
    const options = this.getDriver(config);
    options.name = 'test';
    const conn = await this.createConnection(options);
    await SQLConnection.schemeSync(conn);
    await conn.close();
    return true;
  }

  public static async init(): Promise<void> {
    const connection = await this.getConnection();

    if (Config.Users.authenticationRequired !== true) {
      return;
    }
    // Adding enforced users to the db
    const userRepository = connection.getRepository(UserEntity);
    if (
      Array.isArray(Config.Users.enforcedUsers) &&
      Config.Users.enforcedUsers.length > 0
    ) {
      for (let i = 0; i < Config.Users.enforcedUsers.length; ++i) {
        const uc = Config.Users.enforcedUsers[i];
        const user = await userRepository.findOneBy({name: uc.name});
        if (!user) {
          Logger.info(LOG_TAG, 'Saving enforced user: ' + uc.name);
          const a = new UserEntity();
          a.name = uc.name;
          a.password = uc.encryptedPassword;
          a.role = uc.role;
          await userRepository.save(a);
        }
      }
    }

    // Add dummy Admin to the db
    const admins = await userRepository.findBy({role: UserRoles.Admin});
    const devs = await userRepository.findBy({role: UserRoles.Developer});
    if (admins.length === 0 && devs.length === 0) {
      const a = new UserEntity();
      a.name = 'admin';
      a.password = PasswordHelper.cryptPassword('admin');
      a.role = UserRoles.Admin;
      await userRepository.save(a);
    }

    const defAdmin = await userRepository.findOneBy({
      name: 'admin',
      role: UserRoles.Admin,
    });
    if (
      defAdmin &&
      PasswordHelper.comparePassword('admin', defAdmin.password)
    ) {
      NotificationManager.error(
        'Using default admin user!',
        'You are using the default admin/admin user/password, please change or remove it.'
      );
    }
  }

  public static async close(): Promise<void> {
    try {
      if (this.connection != null) {
        await this.connection.close();
        this.connection = null;
      }
    } catch (err) {
      console.error('Error during closing sql db:');
      console.error(err);
    }
  }

  /**
   * Clears up the DB from unused tables. use it when the entities list are up-to-date (extensions won't add any new)
   */
  public static async removeUnusedTables() {
    const conn = await this.getConnection();
    const validTableNames = this.entries.map(e => conn.getRepository(e).metadata.tableName).concat(this.FIXED_SQL_TABLE);
    let currentTables: string[];

    if (Config.Database.type === DatabaseType.sqlite) {
      currentTables = (await conn.query('SELECT name FROM sqlite_master  WHERE type=\'table\''))
        .map((r: { name: string }) => r.name);
    } else {
      currentTables = (await conn.query(`SELECT table_name
                                         FROM information_schema.tables ` +
        `WHERE table_schema = '${Config.Database.mysql.database}'`))
        .map((r: { table_name: string }) => r.table_name);
    }

    const tableToDrop = currentTables.filter(ct => !validTableNames.includes(ct));
    for (let i = 0; i < tableToDrop.length; ++i) {
      await conn.query('DROP TABLE ' + tableToDrop[i]);
    }
  }

  public static getSQLiteDB(config: ServerDataBaseConfig): string {
    return path.join(ProjectPath.getAbsolutePath(config.dbFolder), 'sqlite.db');
  }

  private static async createConnection(
    options: DataSourceOptions
  ): Promise<Connection> {
    if (options.type === 'sqlite' || options.type === 'better-sqlite3') {
      return await createConnection(options);
    }
    try {
      return await createConnection(options);
    } catch (e) {
      if (e.sqlMessage === 'Unknown database \'' + options.database + '\'') {
        Logger.debug(LOG_TAG, 'creating database: ' + options.database);
        const tmpOption = Utils.clone(options);
        // eslint-disable-next-line @typescript-eslint/ban-ts-comment
        // @ts-ignore
        delete tmpOption.database;
        const tmpConn = await createConnection(tmpOption);
        await tmpConn.query(
          'CREATE DATABASE IF NOT EXISTS ' + options.database
        );
        await tmpConn.close();
        return await createConnection(options);
      }
      throw e;
    }
  }

  private static async schemeSync(connection: Connection): Promise<void> {
    let version = null;
    try {
      version = (await connection.getRepository(VersionEntity).find())[0];
      // eslint-disable-next-line no-empty
    } catch (ex) {
    }
    if (version && version.version === DataStructureVersion) {
      return;
    }
    Logger.info(LOG_TAG, 'Updating database scheme');
    if (!version) {
      version = new VersionEntity();
    }
    version.version = DataStructureVersion;

    let users: UserEntity[] = [];
    try {
      users = await connection
        .getRepository(UserEntity)
        .createQueryBuilder('user')
        .getMany();
      // eslint-disable-next-line no-empty
    } catch (ex) {
    }
    await connection.dropDatabase();
    await connection.synchronize();
    await connection.getRepository(VersionEntity).save(version);
    try {
      await connection.getRepository(UserEntity).save(users);
    } catch (e) {
      await connection.dropDatabase();
      await connection.synchronize();
      await connection.getRepository(VersionEntity).save(version);
      Logger.warn(
        LOG_TAG,
        'Could not move users to the new db scheme, deleting them. Details:' +
        e.toString()
      );
    }
  }


  private static getDriver(config: ServerDataBaseConfig): Writeable<DataSourceOptions> {
    let driver: Writeable<DataSourceOptions>;
    if (config.type === DatabaseType.mysql) {
      driver = {
        type: 'mysql',
        host: config.mysql.host,
        port: config.mysql.port,
        username: config.mysql.username,
        password: config.mysql.password,
        database: config.mysql.database,
        charset: 'utf8mb4',
      };
    } else if (config.type === DatabaseType.sqlite) {
      driver = {
        type: 'better-sqlite3',
        database: path.join(
          ProjectPath.getAbsolutePath(config.dbFolder),
          config.sqlite.DBFileName
        ),
      };
    }
    driver.entities = this.entries;
    driver.synchronize = false;
    if (Config.Server.Log.sqlLevel !== SQLLogLevel.none) {
      driver.logging = SQLLogLevel[Config.Server.Log.sqlLevel] as LoggerOptions;
    }
    return driver;
  }
}

1	import 'reflect-metadata';	1✔
2	import {Connection, createConnection, DataSourceOptions, getConnection, LoggerOptions,} from 'typeorm';	1✔
3	import {UserEntity} from './enitites/UserEntity';	1✔
4	import {UserRoles} from '../../../common/entities/UserDTO';	1✔
5	import {PhotoEntity} from './enitites/PhotoEntity';	1✔
6	import {DirectoryEntity} from './enitites/DirectoryEntity';	1✔
7	import {Config} from '../../../common/config/private/Config';	1✔
8	import {SharingEntity} from './enitites/SharingEntity';	1✔
9	import {PasswordHelper} from '../PasswordHelper';	1✔
10	import {ProjectPath} from '../../ProjectPath';	1✔
11	import {VersionEntity} from './enitites/VersionEntity';	1✔
12	import {Logger} from '../../Logger';	1✔
13	import {MediaEntity} from './enitites/MediaEntity';	1✔
14	import {VideoEntity} from './enitites/VideoEntity';	1✔
15	import {DataStructureVersion} from '../../../common/DataStructureVersion';	1✔
16	import {FileEntity} from './enitites/FileEntity';	1✔
17	import {PersonEntry} from './enitites/person/PersonEntry';	1✔
18	import {Utils} from '../../../common/Utils';	1✔
19	import * as path from 'path';	1✔
20	import {DatabaseType, ServerDataBaseConfig, SQLLogLevel,} from '../../../common/config/private/PrivateConfig';	1✔
21	import {AlbumBaseEntity} from './enitites/album/AlbumBaseEntity';	1✔
22	import {SavedSearchEntity} from './enitites/album/SavedSearchEntity';	1✔
23	import {NotificationManager} from '../NotifocationManager';	1✔
24	import {PersonJunctionTable} from './enitites/person/PersonJunctionTable';	1✔
25	import {MDFileEntity} from './enitites/MDFileEntity';	1✔
26	import {ProjectedDirectoryCacheEntity} from './enitites/ProjectedDirectoryCacheEntity';	1✔
27	import {ProjectedPersonCacheEntity} from './enitites/person/ProjectedPersonCacheEntity';	1✔
28	import {ProjectedAlbumCacheEntity} from './enitites/album/ProjectedAlbumCacheEntity';	1✔
29
30	const LOG_TAG = '[SQLConnection]';	1✔
31
32	type Writeable<T> = { -readonly [P in keyof T]: T[P] };
33
34	export class SQLConnection {	1✔
35	// eslint-disable-next-line @typescript-eslint/ban-types
36	private static entries: Function[] = [	1✔
37	UserEntity,
38	FileEntity,
39	MDFileEntity,
40	PersonJunctionTable,
41	PersonEntry,
42	MediaEntity,
43	PhotoEntity,
44	VideoEntity,
45	DirectoryEntity,
46	SharingEntity,
47	AlbumBaseEntity,
48	SavedSearchEntity,
49	VersionEntity,
50	// projection-aware cache entries
51	ProjectedDirectoryCacheEntity,
52	ProjectedPersonCacheEntity,
53	ProjectedAlbumCacheEntity
54	];
55	private static connection: Connection = null;	1✔
56	private static FIXED_SQL_TABLE = [	1✔
57	'sqlite_sequence'
58	];
59
60	// eslint-disable-next-line @typescript-eslint/ban-types
61	public static getEntries(): Function[] {
NEW 62	return this.entries;	×
63	}
64
65	// eslint-disable-next-line @typescript-eslint/ban-types
66	public static async addEntries(tables: Function[]) {
NEW 67	if (!tables?.length) {	×
NEW 68	return;	×
69	}
NEW 70	await this.close();	×
NEW 71	this.entries = Utils.getUnique(this.entries.concat(tables));	×
NEW 72	await (await this.getConnection()).synchronize();	×
73	}
74
75	public static async getConnection(): Promise<Connection> {
76	if (this.connection == null) {	2,701✔
77	const options = this.getDriver(Config.Database);	454✔
78
79	Logger.debug(	454✔
80	LOG_TAG,
81	'Creating connection: ' + DatabaseType[Config.Database.type],
82	', with driver:',
83	options.type
84	);
85	this.connection = await this.createConnection(options);	454✔
86	await SQLConnection.schemeSync(this.connection);	454✔
87	}
88	return this.connection;	2,701✔
89	}
90
91	public static async tryConnection(
92	config: ServerDataBaseConfig
93	): Promise<boolean> {
94	try {	31✔
95	await getConnection('test').close();	31✔
96	// eslint-disable-next-line no-empty
97	} catch (err) {
98	}
99	const options = this.getDriver(config);	31✔
100	options.name = 'test';	31✔
101	const conn = await this.createConnection(options);	31✔
102	await SQLConnection.schemeSync(conn);	31✔
103	await conn.close();	31✔
104	return true;	31✔
105	}
106
107	public static async init(): Promise<void> {
108	const connection = await this.getConnection();	219✔
109
110	if (Config.Users.authenticationRequired !== true) {	219✔
111	return;	12✔
112	}
113	// Adding enforced users to the db
114	const userRepository = connection.getRepository(UserEntity);	207✔
115	if (	207✔
116	Array.isArray(Config.Users.enforcedUsers) &&	414✔
117	Config.Users.enforcedUsers.length > 0
118	) {
119	for (let i = 0; i < Config.Users.enforcedUsers.length; ++i) {	62✔
120	const uc = Config.Users.enforcedUsers[i];	62✔
121	const user = await userRepository.findOneBy({name: uc.name});	62✔
122	if (!user) {	62✔
123	Logger.info(LOG_TAG, 'Saving enforced user: ' + uc.name);	62✔
124	const a = new UserEntity();	62✔
125	a.name = uc.name;	62✔
126	a.password = uc.encryptedPassword;	62✔
127	a.role = uc.role;	62✔
128	await userRepository.save(a);	62✔
129	}
130	}
131	}
132
133	// Add dummy Admin to the db
134	const admins = await userRepository.findBy({role: UserRoles.Admin});	207✔
135	const devs = await userRepository.findBy({role: UserRoles.Developer});	207✔
136	if (admins.length === 0 && devs.length === 0) {	207✔
137	const a = new UserEntity();	175✔
138	a.name = 'admin';	175✔
139	a.password = PasswordHelper.cryptPassword('admin');	175✔
140	a.role = UserRoles.Admin;	175✔
141	await userRepository.save(a);	175✔
142	}
143
144	const defAdmin = await userRepository.findOneBy({	207✔
145	name: 'admin',
146	role: UserRoles.Admin,
147	});
148	if (	207✔
149	defAdmin &&	414✔
150	PasswordHelper.comparePassword('admin', defAdmin.password)
151	) {
152	NotificationManager.error(	207✔
153	'Using default admin user!',
154	'You are using the default admin/admin user/password, please change or remove it.'
155	);
156	}
157	}
158
159	public static async close(): Promise<void> {
160	try {	531✔
161	if (this.connection != null) {	531✔
162	await this.connection.close();	454✔
163	this.connection = null;	454✔
164	}
165	} catch (err) {
166	console.error('Error during closing sql db:');	×
167	console.error(err);	×
168	}
169	}
170
171	/**
172	* Clears up the DB from unused tables. use it when the entities list are up-to-date (extensions won't add any new)
173	*/
174	public static async removeUnusedTables() {
175	const conn = await this.getConnection();	×
176	const validTableNames = this.entries.map(e => conn.getRepository(e).metadata.tableName).concat(this.FIXED_SQL_TABLE);	×
177	let currentTables: string[];
178
179	if (Config.Database.type === DatabaseType.sqlite) {	×
180	currentTables = (await conn.query('SELECT name FROM sqlite_master WHERE type=\'table\''))	×
181	.map((r: { name: string }) => r.name);	×
182	} else {
NEW 183	currentTables = (await conn.query(`SELECT table_name	×
184	FROM information_schema.tables ` +
185	`WHERE table_schema = '${Config.Database.mysql.database}'`))
186	.map((r: { table_name: string }) => r.table_name);	×
187	}
188
189	const tableToDrop = currentTables.filter(ct => !validTableNames.includes(ct));	×
190	for (let i = 0; i < tableToDrop.length; ++i) {	×
191	await conn.query('DROP TABLE ' + tableToDrop[i]);	×
192	}
193	}
194
195	public static getSQLiteDB(config: ServerDataBaseConfig): string {
196	return path.join(ProjectPath.getAbsolutePath(config.dbFolder), 'sqlite.db');	26✔
197	}
198
199	private static async createConnection(
200	options: DataSourceOptions
201	): Promise<Connection> {
202	if (options.type === 'sqlite' \|\| options.type === 'better-sqlite3') {	485✔
203	return await createConnection(options);	174✔
204	}
205	try {	311✔
206	return await createConnection(options);	311✔
207	} catch (e) {
208	if (e.sqlMessage === 'Unknown database \'' + options.database + '\'') {	115✔
209	Logger.debug(LOG_TAG, 'creating database: ' + options.database);	115✔
210	const tmpOption = Utils.clone(options);	115✔
211	// eslint-disable-next-line @typescript-eslint/ban-ts-comment
212	// @ts-ignore
213	delete tmpOption.database;	115✔
214	const tmpConn = await createConnection(tmpOption);	115✔
215	await tmpConn.query(	115✔
216	'CREATE DATABASE IF NOT EXISTS ' + options.database
217	);
218	await tmpConn.close();	115✔
219	return await createConnection(options);	115✔
220	}
221	throw e;	×
222	}
223	}
224
225	private static async schemeSync(connection: Connection): Promise<void> {
226	let version = null;	485✔
227	try {	485✔
228	version = (await connection.getRepository(VersionEntity).find())[0];	485✔
229	// eslint-disable-next-line no-empty
230	} catch (ex) {
231	}
232	if (version && version.version === DataStructureVersion) {	485✔
233	return;	257✔
234	}
235	Logger.info(LOG_TAG, 'Updating database scheme');	228✔
236	if (!version) {	228✔
237	version = new VersionEntity();	227✔
238	}
239	version.version = DataStructureVersion;	228✔
240
241	let users: UserEntity[] = [];	228✔
242	try {	228✔
243	users = await connection	228✔
244	.getRepository(UserEntity)
245	.createQueryBuilder('user')
246	.getMany();
247	// eslint-disable-next-line no-empty
248	} catch (ex) {
249	}
250	await connection.dropDatabase();	228✔
251	await connection.synchronize();	228✔
252	await connection.getRepository(VersionEntity).save(version);	228✔
253	try {	228✔
254	await connection.getRepository(UserEntity).save(users);	228✔
255	} catch (e) {
256	await connection.dropDatabase();	×
257	await connection.synchronize();	×
258	await connection.getRepository(VersionEntity).save(version);	×
259	Logger.warn(	×
260	LOG_TAG,
261	'Could not move users to the new db scheme, deleting them. Details:' +
262	e.toString()
263	);
264	}
265	}
266
267
268	private static getDriver(config: ServerDataBaseConfig): Writeable<DataSourceOptions> {
269	let driver: Writeable<DataSourceOptions>;
270	if (config.type === DatabaseType.mysql) {	485✔
271	driver = {	311✔
272	type: 'mysql',
273	host: config.mysql.host,
274	port: config.mysql.port,
275	username: config.mysql.username,
276	password: config.mysql.password,
277	database: config.mysql.database,
278	charset: 'utf8mb4',
279	};
280	} else if (config.type === DatabaseType.sqlite) {	174✔
281	driver = {	174✔
282	type: 'better-sqlite3',
283	database: path.join(
284	ProjectPath.getAbsolutePath(config.dbFolder),
285	config.sqlite.DBFileName
286	),
287	};
288	}
289	driver.entities = this.entries;	485✔
290	driver.synchronize = false;	485✔
291	if (Config.Server.Log.sqlLevel !== SQLLogLevel.none) {	485✔
292	driver.logging = SQLLogLevel[Config.Server.Log.sqlLevel] as LoggerOptions;	485✔
293	}
294	return driver;	485✔
295	}
296	}

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