Passer au contenu principal

Requêtes de base de données

danger

Gardez à l'esprit que jouer avec la base de données pourrait mettre la Lune en feu. Évitez de modifier directement la base de données lorsque c'est possible, et assurez-vous d'avoir des sauvegardes à jour.

astuce

Exécutez docker exec -it immich_postgres psql --dbname=<DB_DATABASE_NAME> --username=<DB_USERNAME> pour vous connecter directement à la base de données via le conteneur.

(Remplacez <DB_DATABASE_NAME> et <DB_USERNAME> par les valeurs de votre .env file).

Ressources​

Nom​

note

La colonne "originalFileName" correspond au nom du fichier au moment du téléchargement, incluant l'extension.

Rechercher par nom de fichier original
SELECT * FROM "asset" WHERE "originalFileName" = 'PXL_20230903_232542848.jpg';
SELECT * FROM "asset" WHERE "originalFileName" LIKE 'PXL_%'; -- tous les fichiers commençant par PXL_
SELECT * FROM "asset" WHERE "originalFileName" LIKE '%_2023_%'; -- tous les fichiers contenant _2023_ au milieu
Rechercher par chemin
SELECT * FROM "asset" WHERE "originalPath" = 'upload/library/admin/2023/2023-09-03/PXL_2023.jpg';
SELECT * FROM "asset" WHERE "originalPath" LIKE 'upload/library/admin/2023/%';

ID​

Rechercher par ID
SELECT * FROM "asset" WHERE "id" = '9f94e60f-65b6-47b7-ae44-a4df7b57f0e9';
Rechercher par ID partiel
SELECT * FROM "asset" WHERE "id"::text LIKE '%ab431d3a%';

Somme de contrôle​

note

Vous pouvez calculer la somme de contrôle d'un fichier particulier en utilisant la commande sha1sum <filename>.

Rechercher par somme de contrôle (SHA-1)
SELECT encode("checksum", 'hex') FROM "asset";
SELECT * FROM "asset" WHERE "checksum" = decode('69de19c87658c4c15d9cacb9967b8e033bf74dd1', 'hex');
SELECT * FROM "asset" WHERE "checksum" = '\x69de19c87658c4c15d9cacb9967b8e033bf74dd1'; -- notation alternative
Rechercher des ressources en double avec une somme de contrôle identique (SHA-1) (en excluant les fichiers supprimés)
SELECT T1."checksum", array_agg(T2."id") ids FROM "asset" T1
  INNER JOIN "asset" T2 ON T1."checksum" = T2."checksum" AND T1."id" != T2."id" AND T2."deletedAt" IS NULL
  WHERE T1."deletedAt" IS NULL GROUP BY T1."checksum";

Métadonnées​

Photos en direct
SELECT * FROM "asset" WHERE "livePhotoVideoId" IS NOT NULL;
Par description
SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE TRIM("asset_exif"."description") <> ''; -- tous les fichiers avec une description
SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."description" ILIKE '%string to match%'; -- recherche par chaîne
Sans métadonnées
SELECT "asset".* FROM "asset_exif"
  LEFT JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."assetId" IS NULL;
taille < 100 000 octets, de la plus petite à la plus grande
SELECT * FROM "asset"
  JOIN "asset_exif" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."fileSizeInByte" < 100000
  ORDER BY "asset_exif"."fileSizeInByte" ASC;

Type​

Par type
SELECT * FROM "asset" WHERE "asset"."type" = 'VIDEO';
SELECT * FROM "asset" WHERE "asset"."type" = 'IMAGE';
Nombre par type
SELECT "asset"."type", COUNT(*) FROM "asset" GROUP BY "asset"."type";
Nombre par type (par utilisateur)
SELECT "user"."email", "asset"."type", COUNT(*) FROM "asset"
  JOIN "user" ON "asset"."ownerId" = "user"."id"
  GROUP BY "asset"."type", "user"."email" ORDER BY "user"."email";

Tags​

Nombre par tag
SELECT "t"."value" AS "tag_name", COUNT(*) AS "number_assets" FROM "tag" "t"
  JOIN "tag_asset" "ta" ON "t"."id" = "ta"."tagsId" JOIN "asset" "a" ON "ta"."assetsId" = "a"."id"
  WHERE "a"."visibility" != 'hidden'
  GROUP BY "t"."value" ORDER BY "number_assets" DESC;
Nombre par tag (par utilisateur)
SELECT "t"."value" AS "tag_name", "u"."email" as "user_email", COUNT(*) AS "number_assets" FROM "tag" "t"
  JOIN "tag_asset" "ta" ON "t"."id" = "ta"."tagsId" JOIN "asset" "a" ON "ta"."assetsId" = "a"."id" JOIN "user" "u" ON "a"."ownerId" = "u"."id"
  WHERE "a"."visibility" != 'hidden'
  GROUP BY "t"."value", "u"."email" ORDER BY "number_assets" DESC;

Utilisateurs​

Lister tous les utilisateurs
SELECT * FROM "user";
Obtenir les informations du propriétaire à partir de l'ID de la ressource
SELECT "user".* FROM "user" JOIN "asset" ON "user"."id" = "asset"."ownerId" WHERE "asset"."id" = 'fa310b01-2f26-4b7a-9042-d578226e021f';

Personnes​

Supprimer une personne et désassocier les visages liés
DELETE FROM "person" WHERE "name" = 'PersonNameHere';

Système​

Configuration​

Paramètres personnalisés
SELECT "key", "value" FROM "system_metadata" WHERE "key" = 'system-config';

(Utilisé uniquement si le fichier de configuration n'est pas utilisé)

Propriétés du fichier​

Sans vignettes
SELECT * FROM "asset" WHERE "asset"."previewPath" IS NULL OR "asset"."thumbnailPath" IS NULL;
Déplacements de fichiers échoués
SELECT * FROM "move_history";

Interne Postgres​

Changer DB_PASSWORD
ALTER USER <DB_USERNAME> WITH ENCRYPTED PASSWORD 'newpasswordhere';