Useful snippets

Reset the password

It happens to the best of us. The day comes, when you are no longer able to login. Execute the following SQL script to reset the password back to confinity

PostgreSQL

UPDATE "ConfinityUser"
SET "PasswordSalt" = E'\\x6ECFA824BB58AB7FB4353969E0AD616B1714C9F6723E5523EA01ACF441437D78',
    "PasswordHash" = E'\\x9B89CDB895E1406A75617BD2382A25D46C817D73926812172D47BA2FD9C36C50',
    "LoginEnabled" =  true
WHERE "Username"  = 'confinity'

MS SQL

UPDATE ConfinityUser
SET PasswordSalt = 0x6ECFA824BB58AB7FB4353969E0AD616B1714C9F6723E5523EA01ACF441437D78,
    PasswordHash = 0x9B89CDB895E1406A75617BD2382A25D46C817D73926812172D47BA2FD9C36C50,
    LoginEnabled =  1
WHERE Username  = 'confinity';

Find relocations with deleted assets

Assets can be deleted even if they are still used by a relocation. The following SQL script finds those relocations.

PostgreSQL

SELECT "Id"
FROM "ConfinityRelocation"
WHERE "Target" ::json ->> '$type' = 'Confinity.Assets.AssetLinkModel'
AND ("Target" ::json -> 'file' ->> 'id')::uuid  NOT IN (SELECT "Id" FROM "ConfinityAsset")

Export Pages with authors and last publishing date

PostgreSQL

WITH RECURSIVE page_hierarchy AS (
-- Base case: pages without parents (root pages)
SELECT "Id",
    "Name",
    "Slug",
    "ParentId",
    "KeepSlugInUrl",
    "PageType",
    "Slug"::TEXT AS "Parent Slugs"
FROM "ConfinityPage"
WHERE "ParentId" IS NULL

    UNION ALL

    -- Recursive case: pages with parents
    SELECT c."Id",
           c."Name",
           c."Slug",
           c."ParentId",
           c."KeepSlugInUrl",
           c."PageType",
           CASE
               WHEN p."KeepSlugInUrl" = true THEN p."Parent Slugs" || '/' || p."Slug"
               ELSE p."Parent Slugs"
               END ::TEXT AS "Parent Slugs"
    FROM "ConfinityPage" c
             INNER JOIN page_hierarchy p ON c."ParentId" = p."Id")

SELECT p."Id",
    p."Name",
    p."Slug",
    CASE WHEN p."PageType" = 2 THEN p."Slug" ELSE p."Parent Slugs" || '/' || p."Slug" END AS "Full Slug",
    u."FirstName" || ' ' || u."LastName" AS "Last Author",
    CASE WHEN
        (SELECT MAX("UnpublishedAt") FROM "__ConfinityHistory_ConfinityPage" WHERE "Entity_Id" = p."Id")
        >
        (SELECT MAX("PublishedAt") FROM "__ConfinityHistory_ConfinityPage" WHERE "Entity_Id" = p."Id")
    THEN NULL ELSE (SELECT MAX("PublishedAt")
FROM "__ConfinityHistory_ConfinityPage"
    WHERE "Entity_Id" = p."Id") END AS "Last Published"
FROM page_hierarchy p
    INNER JOIN "__ConfinityHistory_ConfinityPage" h
        ON h."Entity_Id" = p."Id" AND h."To" = 'infinity'
        LEFT OUTER JOIN "ConfinityUser" u
        ON u."Id" = h."Entity_ConfinityMetadata_ModifiedById"
ORDER BY p."Name";