How to determine the file paths for the attachments of a specific Page
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Fisheye および Crucible は除く
要約
Ocassionally, an administrator may need to know the disk volume file path for a given attachment on a page, wherein they know the pageID for the page in question.
ソリューション
For this purpose, you can make use of the following SQL statements.
There were recent changes in the Attachments Hierarchy used in Confluence. Please, refer to the DiskLocV3 column result for Confluence 5.7.0 to Confluence 8.0.x; while for Confluence 8.1.0+, refer to the DiskLocV4 column result.
select
concat('<CONFLUENCE_SHARED_HOME>/attachments/ver003/', spaceid % 250, '/', spaceid / 1000 % 250, '/', spaceid,
'/', pageid % 250, '/', pageid / 1000 % 250, '/', pageid,
'/', case when prevver is null then contentid else prevver end,
'/', version
) as DiskLocV3,
concat('<CONFLUENCE_SHARED_HOME>/attachments/v4/',
case when prevver is null then contentid else prevver end % 65535 % 256,
'/', case when prevver is null then contentid else prevver end % 65535 / 256,
'/', case when prevver is null then contentid else prevver end,
'/', case when prevver is null then contentid else prevver end, '.', version) as DiskLocV4,
spaceid, pageid, contentid, version, *
from content where contenttype = 'ATTACHMENT' and
pageid in (
select c.contentid FROM content c JOIN SPACES s ON s.spaceid = c.spaceid
where c.contenttype = 'PAGE'
AND c.contentid = ######
and s.spaceid is not null
AND c.prevver IS NULL);select concat('<CONFLUENCE_SHARED_HOME>/attachments/ver003/', spaceid MOD 250, '/', (spaceid DIV 1000) MOD 250, '/', spaceid,
'/', pageid MOD 250, '/', pageid DIV 1000 MOD 250, '/', pageid,
'/', case when prevver is null then contentid else prevver end,
'/', version
) as DiskLocV3,
concat('<CONFLUENCE_SHARED_HOME>/attachments/v4/',
case when prevver is null then contentid else prevver end MOD 65535 MOD 256,
'/', case when prevver is null then contentid else prevver end MOD 65535 DIV 256,
'/', case when prevver is null then contentid else prevver end,
'/', case when prevver is null then contentid else prevver end, '.', version) as DiskLocV4,
spaceid, pageid, contentid, version, CONTENT.* from CONTENT where contenttype = 'ATTACHMENT' and
pageid in (
select c.contentid FROM CONTENT c JOIN SPACES s ON s.spaceid = c.spaceid
where c.contenttype = 'PAGE'
AND c.contentid = ######
and s.spaceid is not null
AND c.prevver IS NULL);select concat('<CONFLUENCE_SHARED_HOME>/attachments/ver003/', SPACEID % 250, '/', FLOOR(SPACEID / 1000) % 250, '/', SPACEID,
'/', PAGEID % 250, '/', FLOOR(PAGEID / 1000) % 250, '/', PAGEID,
'/', case WHEN PREVVER is NULL THEN CONTENTID else PREVVER end,
'/', VERSION
) as DiskLocV3,
concat('<CONFLUENCE_SHARED_HOME>/attachments/v4/',
case when PREVVER is null then CONTENTID else PREVVER end % 65535 % 256,
'/', case when PREVVER is null then CONTENTID else PREVVER end % 65535 / 256,
'/', case when PREVVER is null then CONTENTID else PREVVER end,
'/', case when PREVVER is null then CONTENTID else PREVVER end, '.', VERSION) as DiskLocV4,
SPACEID, PAGEID, CONTENTID, VERSION, CONTENT.* from CONTENT where CONTENTTYPE = 'ATTACHMENT' and
PAGEID in (
select c.CONTENTID FROM CONTENT c JOIN SPACES s ON s.SPACEID = c.SPACEID
where c.CONTENTTYPE = 'PAGE'
AND c.CONTENTID = ######
and s.SPACEID is not null
AND c.PREVVER IS NULL);select '<CONFLUENCE_SHARED_HOME>/attachments/ver003/' || MOD(spaceid, 250) || '/' || MOD(FLOOR(spaceid / 1000), 250) || '/' || spaceid ||
'/' || MOD(pageid, 250) || '/' || MOD(FLOOR(pageid / 1000), 250) || '/' || pageid ||
'/' || case when prevver is null then contentid else prevver end || '/' || version as DiskLocV3,
'<CONFLUENCE_SHARED_HOME>/attachments/v4/'
|| MOD(MOD(case when prevver is null then contentid else prevver end, 65535), 256)
|| '/'
|| TRUNC(MOD(case when prevver is null then contentid else prevver end, 65535) / 256) ||
'/' || case when prevver is null then contentid else prevver end ||
'/' || case when prevver is null then contentid else prevver end || '.' || version as DiskLocV4,
spaceid, pageid, contentid, version, content.* from content where contenttype = 'ATTACHMENT' and
pageid in (
select c.contentid FROM content c JOIN SPACES s ON s.spaceid = c.spaceid
where c.contenttype = 'PAGE'
AND c.contentid = ######
and s.spaceid is not null
AND c.prevver IS NULL);
Replace the <PAGEID> in c.contentid=<PAGEID> at the end of each SQL statement with the page's actual pageID
最終更新日 2024 年 5 月 24 日
Powered by Confluence and Scroll Viewport.