get the structure of the whole breadcrumb within a select in database

[Michael Regelin]

Hello,

We use actually a command file (sh) to create a zip with all attachment from a space.

We create a folder structure for every page in only one hierarchy.

Here is the select to construct the creation of folder and file within a SED command.

select 'sourceFile="/data/confluence/attachments/ver003/'
|| MOD(TO_NUMBER(SUBSTR(sp.spaceid , LENGTH(sp.spaceid) - 2, 3)), 250)
|| '/'
|| MOD(TO_NUMBER(SUBSTR(sp.spaceid , LENGTH(sp.spaceid) - 5, 3)), 250)
|| '/'
|| sp.spaceid
|| '/'
|| MOD(TO_NUMBER(SUBSTR(c.pageid , LENGTH(c.pageid) - 2, 3)), 250)
|| '/'
|| MOD(TO_NUMBER(SUBSTR(c.pageid , LENGTH(c.pageid) - 5, 3)), 250)
|| '/'
|| c.pageid
|| '/'
|| c.contentid
|| '/'
|| c.version
|| '" '
|| CHR(38)
|| CHR(38)
|| ' targetPath="/tmp/wikiadm/'
|| sp.spacekey
|| '/'
|| REGEXP_REPLACE(REGEXP_REPLACE(c2.lowertitle,'[<>:"/\\\*|¦\?]',''), '[ ]+','_')
|| '/'
|| REGEXP_REPLACE(REGEXP_REPLACE(c.lowertitle,'[<>:"/\\\*|¦\?]',''), '[ ]+','_')
|| '" '
|| CHR(38)
|| CHR(38)
|| ' mkdir -p $(dirname "$targetPath") '
|| CHR(38)
|| CHR(38)
|| ' ln -s "$sourceFile" "$targetPath"'
as path
from content c
inner join content c2 on c2.contentid = c.pageid
inner join spaces sp on sp.spaceid = c.spaceid
where c.contenttype = 'ATTACHMENT'
and c.content_status ='current'
and c.prevver is null
and sp.spacekey ='XXXXXX';

 I get this result for every attachment:

sourceFile="/data/confluence/attachments/ver003/119/52/399802369/222/42/1155792972/1155792960/1" && targetPath="/tmp/wikiadm/XXXXX/insérer_un_film_depuis_youtube/image2020-4-16_10-52-16.png" && mkdir -p $(dirname "$targetPath") && ln -s "$sourceFile" "$targetPath"

Now I need to get the whole structure after the spacekey to get the whole bunch of the breadcrumb (ancestor, grand-parent, parent, etc.) to be able to navigate in folder inside the zip file.

Is it doable ?

Thanks for your help.

Michael