feat(server): improve thumbnail relation and updating

[michelheusschen]

Added albumThumnailAssetId foreign key to AssetEntity. It should actually be a composite foreign key to the linking table of assets and albums, but I've not done that due to quite some added complexity, because the linking table has to be defined explicitly.

Reworking AlbumService._checkValidThumbnail means we no longer need to pull all assets from the database in AlbumService.getAllAlbums and then those queries can be optimized further, which has recently been causing some performance bottlenecks.

Still WIP and haven't tested it beyond the basics

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

1 Ignored Deployment

Name	Status	Preview	Comments	Updated
immich	⬜️ Ignored (Inspect)			Mar 4, 2023 at 7:43AM (UTC)

[michelheusschen]

Changed the query for updateThumbnails a bit, it now runs in less than 0.1ms for 5000 assets and 5 albums when no updates are needed. Then if a thumbnail needs to be updated it adds about 2ms for every updated album.

To the migrations I've added a slightly modified version of the updateThumbnails query to allow comparing between uuid and varchar. The query probably won't do anything in 99% of cases, but I've added it to prevent any issues with converting to uuid and adding the foreign key.

[alextran1502]

@michelheusschen Hi Michel, can you help fix the unit tests?

[michelheusschen]

@michelheusschen Hi Michel, can you help fix the unit tests?

Unit tests are fixed. Rebasing went a bit wrong but I've sorted it out