AnsweredAssumed Answered

Where to find binary files in companyhome

Question asked by wnovak on May 30, 2012
Here's my view for the oracle database system to show the connections between binary file in alf_data and filestructure in company home.

CREATE OR REPLACE FORCE VIEW "ALFRESCO"."FILE_PATH" ("ALFFILE", "ALFPATH", "FILESYSTEM", "FILESIZE", "ORPHANTIME")
AS
  SELECT uu.alfFile,
    uu.alfPath,
    REPLACE(xx.content_url,'store://') fileSystem,
    xx.content_size fileSize,
    xx.orphan_time orphantime
  FROM
    (SELECT aa.child_node_id node_id,
      bb.string_value alfFile,
      REPLACE(aa.path,aa.root) alfPath
    FROM
      (SELECT child_node_id,
        qname_localname Root,
        level,
        SYS_CONNECT_BY_PATH(qname_localname,'/') Path
      FROM alf_child_assoc
        START WITH qname_localname LIKE 'company_home'
        CONNECT BY prior child_node_id=parent_node_id
      ) aa,
      alf_node_properties bb
    WHERE aa.child_node_id = bb.node_id
    AND bb.string_value   IS NOT NULL
    AND bb.qname_id        =
      (SELECT id
      FROM alf_qname
      WHERE local_name='name'
      AND ns_id       =
        (SELECT id
        FROM alf_namespace
        WHERE uri='http://www.alfresco.org/model/content/1.0'
        )
      )
    ) uu,
    alf_node_properties vv,
    alf_content_data ww,
    alf_content_url xx
  WHERE (uu.node_id = vv.node_id
  AND vv.qname_id   =
    (SELECT id
    FROM alf_qname
    WHERE local_name='content'
    AND ns_id       =
      (SELECT id
      FROM alf_namespace
      WHERE uri='http://www.alfresco.org/model/content/1.0'
      )
    ))
  AND vv.long_value     = ww.id
  AND ww.content_url_id = xx.id;

Maybe someone can translate it to the other database systems and maybe Alfresco can include it into core product.

Outcomes