CODEWITHSUNDEEP
alfresco
jln
jln

Reputation: 3

How to query the relationship between alfresco's metadata and content in the database

How to query the relationship between alfresco's metadata and content in the database

This URL address is seen on the alfresco webpage:

alfresco webpage

the content file is a binary format file. How to find the relationship between the two in the database.

screenshot

Upvotes: 0

Views: 1031

Answers (2)

Arjun
Arjun

Reputation: 634

You can get the type id of a relationship name from alf_qname table.

Also the below join query will give you exact details of what you are looking for,

SELECT n.id AS "Node ID",
  n.store_id AS "Store ID",
  round(u.content_size/1024/1024,2) AS "Size (MB)",
  n.uuid AS "Document ID (UUID)",
  n.audit_creator AS "Creator",
  n.audit_created AS "Creation Date",
  n.audit_modifier AS "Modifier",
  n.audit_modified AS "Modification Date",
  p1.string_value AS "Document Name",
  u.content_url AS "Location"
FROM alf_node AS n,
  alf_node_properties AS p,
  alf_node_properties AS p1,
  alf_namespace AS ns,
  alf_qname AS q,
  alf_content_data AS d,
  alf_content_url AS u
WHERE n.id=p.node_id
  AND ns.id=q.ns_id
  AND p.qname_id=q.id
  AND p.long_value=d.id
  AND d.content_url_id=u.id
  AND p1.node_id=n.id
  AND p1.qname_id IN (SELECT id FROM alf_qname WHERE local_name='name')
  AND n.uuid='782d55ba-4d96-4031-84f3-423fa3461db5';

enter image description here

Upvotes: 1

Lista
Lista

Reputation: 2246

This blog post will help you a lot, you're new with Alfresco, I assume. For instance, get various node information including the content URL, based on UUID.

SELECT n.id AS "Node ID",
  n.store_id AS "Store ID",
  round(u.content_size/1024/1024,2) AS "Size (MB)",
  n.uuid AS "Document ID (UUID)",
  n.audit_creator AS "Creator",
  n.audit_created AS "Creation Date",
  n.audit_modifier AS "Modifier",
  n.audit_modified AS "Modification Date",
  p1.string_value AS "Document Name",
  u.content_url AS "Location"
FROM alf_node AS n,
  alf_node_properties AS p,
  alf_node_properties AS p1,
  alf_namespace AS ns,
  alf_qname AS q,
  alf_content_data AS d,
  alf_content_url AS u
WHERE n.id=p.node_id
  AND ns.id=q.ns_id
  AND p.qname_id=q.id
  AND p.long_value=d.id
  AND d.content_url_id=u.id
  AND p1.node_id=n.id
  AND p1.qname_id IN (SELECT id FROM alf_qname WHERE local_name='name')
  AND n.uuid='eb267742-c018-4ba5-8ca4-75ca23c860f0';

https://blog.dbi-services.com/alfresco-some-useful-database-queries/

Upvotes: 0

Related Questions