Skip to content

Instantly share code, notes, and snippets.

@icewind1991

icewind1991/queries.md Secret

Created January 19, 2024 15:45
Show Gist options
  • Save icewind1991/f9583eab9cf80455743812b22664ea0e to your computer and use it in GitHub Desktop.
Save icewind1991/f9583eab9cf80455743812b22664ea0e to your computer and use it in GitHub Desktop.
Download ZIP
Filecache queries that don't filter by a single storage id
Raw
queries.md

Filecache queries that don't filter by a single storage id

ignoring any queries from tests and migrations, somewhat organized by how they will be an issue for sharding

Queries that join on the filecache by id and filter indirectly on the fileid

These probably have no way to get efficient sharding as the storage id can't be predicted, would need significant rework in how these relations are handled if they can be "fixed" at all.

  • Deck\Sharing\DeckShareProvider::getShareById: join with shares, selects by share id

  • Spreed\RoomShareProvider::getSharesByIds: join with shares

  • Spreed\RoomShareProvider::getSharedWith: join with shares

  • Deck\Sharing\DeckShareProvider::getSharedWith: join with shares

  • Deck\Sharing\DeckShareProvider::getSharedWithByType: join with shares

  • VirtualFolder\FolderConfigManager::getAllByRootIds: join with folders

  • Photos\AlbumMapper::getForAlbumIdAndUserWithFiles: joins with photos

  • Photos\AlbumMapper::getSharedAlbumsForCollaboratorWithFiles: joins with photos

  • Photos\AlbumMapper::getForAlbumIdAndFileId: joins with photos

  • Files\Config\UserMountCache::getMountsForRootId: join fileid on mount root id

  • UserInfo\UserInfoManager::getUsageInfo: joins with root id from mounts and selects by mount user

  • UserInfo\UserInfoManager::getUsedQuota: joins with root id from mounts and selects by mount point

  • Files\Config\UserMountCache::getMountsForUser: join fileid on mount root id

  • Share\Share::getItemSharedWithUser: join on shares, select by share recipient

  • Share\Share::getItems: join on shares, select by share data

  • Share\Share::getSharedWith: join on shares, select by share recipient

Queries that join on the filecache by id and filter indirectly on the storage

Should be possible to rework into a

  • Files\Config\UserMountCache::getUsedSpaceForUsers: select the home folder for all users

  • FilesExternal\Notify::getStorageIds: joins with storages from oc_mounts and filters on mount id, can probably be replaced

Queries that select from the filecache without filtering by storage, fileid or parent

These are mostly performance "insensitive" maintenance/background queries, depending if/how they join other tables, these could be ran across multiple/all shares, combining the results.

  • Sharing\DeleteOrphanedSharesJob: select over full table to find shares without matching filecache item

  • FilesAntivirus\BackgroundScanner::getUnscannedFiles: select items without matching av item

  • FilesAntivirus\BackgroundScanner::getToRescanFiles: select items with outdated av item

  • FilesAntivirus\BackgroundScanner::getOutdatedFiles: select items with outdated av item

  • Files\ScanFiles::getUsersToScan: select up to 1 item with size -1 from across all storages

  • Files\RepairTree::findBrokenTreeBits: searches through entire filecache

  • Files\Type\Loader::updateFileCache: select by filename LIKE and mimetype across the entire cache

  • Repair\RepairMimeTypes::updateMimetypes: select by filename LIKE and mimetype across the entire cache

  • Preview\ResetRenderedTexts::getPreviewsToDelete: filters only by path LIKE and mimetype

  • RepairShareOwnerShip::getWrongShareOwnership select over full table, join shares and files and mounts and find shares with wrong owner

  • RepairShareOwnerShip::getWrongShareOwnershipForUser same as above but limits by mount users

  • Files\DeleteOrphanedItems::cleanUp: select over full table to find system tags without matching filecache item

  • Files\DeleteOrphanedFiles::execute: select all items that don't have a storage in the storage table, join with storage table but search for NULL storage

Query that filters by parent, in most cases the storage id should be available to add to the filter

  • DAV\CustomPropertiesBackend::cacheDirectory

  • Deck\Sharing\DeckShareProvider::getSharesInFolder

  • GroupFolders\ACL\RuleManager::getRulesForFilesByParent

  • Comments\Manager::getNumberOfUnreadCommentsForFolder not sure if available, seems unused? but exported through OCP

  • Preview\BackgroundCleanupJon::getOldPreviewLocations

Queries that filter directly on multiple file ids

  • VirtualFolder\VirtualFolderFactory::getSourceFilesFromFileIds: by array of file ids

Queries that filter by single fileid

Would need an efficient way to lookup storage id by fileid, some already have a storage id around that can be reused

  • Files\RepairTree::deleteById: might be easy fix, storage id should be available

  • Files\ObjectUtil::objectExistsInDB: storage id is not available

  • Files\Config\UserMountCache::getCacheInfoFromFileId

  • FilesSharing\OrphanHelper::fileExists: don't think storage id is available

  • FilesSharing\SharedMount::getNumericStorageId: not available

  • FilesSharing\ShareBackend\Folder::getParentId: not available

  • Preview\ResetRenderedTexts::getPreviewsToDelete: available

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment