Datenbankabfragen

Gefahr

Denken Sie daran, dass das Herumspielen in der Datenbank den Mond in Brand setzen könnte. Vermeiden Sie es nach Möglichkeit, die Datenbank direkt zu ändern, und erstellen Sie immer aktuelle Backups.

Tipp

Führen Sie docker exec -it immich_postgres psql --dbname=<DB_DATABASE_NAME> --username=<DB_USERNAME> aus, um direkt über den Container eine Verbindung zur Datenbank herzustellen.

(Ersetzen Sie <DB_DATABASE_NAME> und <DB_USERNAME> durch die Werte aus Ihrer .env-Datei).

Assets

Name

Notiz

Die Spalte "originalFileName" ist der Name der Datei zum Zeitpunkt des Hochladens, einschließlich der Erweiterung.

Nach Originaldateiname suchen

SELECT * FROM "asset" WHERE "originalFileName" = 'PXL_20230903_232542848.jpg';
SELECT * FROM "asset" WHERE "originalFileName" LIKE 'PXL_%'; -- alle Dateien, die mit PXL_ beginnen
SELECT * FROM "asset" WHERE "originalFileName" LIKE '%_2023_%'; -- alle Dateien mit _2023_ in der Mitte

Nach Pfad suchen

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

Nach ID suchen

SELECT * FROM "asset" WHERE "id" = '9f94e60f-65b6-47b7-ae44-a4df7b57f0e9';

Nach teilweise ID suchen

SELECT * FROM "asset" WHERE "id"::text LIKE '%ab431d3a%';

Prüfsumme

Notiz

Sie können die Prüfsumme für eine bestimmte Datei mit dem Befehl sha1sum <dateiname> berechnen.

Nach Prüfsumme (SHA-1) suchen

SELECT encode("checksum", 'hex') FROM "asset";
SELECT * FROM "asset" WHERE "checksum" = decode('69de19c87658c4c15d9cacb9967b8e033bf74dd1', 'hex');
SELECT * FROM "asset" WHERE "checksum" = '\x69de19c87658c4c15d9cacb9967b8e033bf74dd1'; -- alternative Schreibweise

Doppelte Assets mit identischer Prüfsumme (SHA-1) finden (ausgenommen gelöschte Dateien)

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";

Metadaten

Live-Fotos

SELECT * FROM "asset" WHERE "livePhotoVideoId" IS NOT NULL;

Nach Beschreibung

SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE TRIM("asset_exif"."description") <> ''; -- alle Dateien mit einer Beschreibung
SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."description" ILIKE '%string to match%'; -- nach String suchen

Ohne Metadaten

SELECT "asset".* FROM "asset_exif"
  LEFT JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."assetId" IS NULL;

Größe < 100.000 Bytes, kleinste bis größte

SELECT * FROM "asset"
  JOIN "asset_exif" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."fileSizeInByte" < 100000
  ORDER BY "asset_exif"."fileSizeInByte" ASC;

Typ

Nach Typ

SELECT * FROM "asset" WHERE "asset"."type" = 'VIDEO';
SELECT * FROM "asset" WHERE "asset"."type" = 'IMAGE';

Nach Typ zählen

SELECT "asset"."type", COUNT(*) FROM "asset" GROUP BY "asset"."type";

Nach Typ zählen (pro Benutzer)

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

Nach Tag zählen

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;

Nach Tag zählen (pro Benutzer)

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;

Benutzer

Alle Benutzer auflisten

SELECT * FROM "user";

Besitzerinformationen von Asset-ID abrufen

SELECT "user".* FROM "user" JOIN "asset" ON "user"."id" = "asset"."ownerId" WHERE "asset"."id" = 'fa310b01-2f26-4b7a-9042-d578226e021f';

Personen

Person löschen und sie von den zugehörigen Gesichtern entfernen

DELETE FROM "person" WHERE "name" = 'PersonNameHere';

System

Konfiguration

Benutzerspezifische Einstellungen

SELECT "key", "value" FROM "system_metadata" WHERE "key" = 'system-config';

(Wird nur verwendet, wenn nicht die Konfigurationsdatei verwendet wird)

Dateieigenschaften

Ohne Thumbnails

SELECT * FROM "asset" WHERE "asset"."previewPath" IS NULL OR "asset"."thumbnailPath" IS NULL;

Fehlgeschlagene Dateibewegungen

SELECT * FROM "move_history";

Internes Postgres

DB_PASSWORD ändern

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