Alfresco queries

Count number of document with a given aspect

SELECT count(alf_node.id)
  FROM alf_node INNER JOIN alf_node_aspects ON alf_node.id = alf_node_aspects.node_id
  WHERE alf_node.store_id = 6 
  AND alf_node_aspects.qname_id = 2929;

Count number of documents for custom type zk:mydoc

SELECT count(*) FROM alf_node
  INNER JOIN alf_qname ON alf_node.type_qname_id = alf_qname.id AND alf_qname.local_name ='mydoc'
  INNER JOIN alf_namespace ON alf_qname.ns_id = alf_namespace.id AND alf_namespace.uri='http://www.zylk.net/model/zk/1.0'
  WHERE alf_node.store_id = 6;

From uuid to content_url and from content_url to 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='79a03a3e-a027-4b91-9f14-02b62723591e';

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 u.content_url='store://2017/5/10/12/34/70c432bc-5392-49ac-8982-8725e64cfe18.bin';

// Esta es 1 a 1 si el store no es de versiones
select uuid, content_url
from alf_node_properties np1
  left join alf_node n on n.id = np1.node_id    
  left join alf_content_data cd on cd.id = np1.long_value    
  left join alf_content_url cu on cu.id = cd.content_url_id
where content_url="store://2017/5/10/12/34/9a0eea8e-5c50-4a9b-8ea4-f99bc08d9464.bin" and n.store_id = '6';


// Esta suelta algunos NULL 
select uuid, content_url
from alf_node_properties np1
  left join alf_node n on n.id = np1.node_id    
  left join alf_content_data cd on cd.id = np1.long_value    
  left join alf_content_url cu on cu.id = cd.content_url_id
where uuid="1a0b110f-1e09-4ca2-b367-fe25e4964a4e" and n.store_id = '6';

//Asi los filtramos...
select uuid, content_url, content_size from alf_node_properties np1   
  left join alf_node n on n.id = np1.node_id       
  left join alf_content_data cd on cd.id = np1.long_value       
  left join alf_content_url cu on cu.id = cd.content_url_id 
where uuid="910eab96-ac17-4565-820d-68c34135d96d" and n.store_id = '6' and content_url like "store%";

zylk@alf5:/opt/data/contentstore$ du -b 2017/5/8/11/8/125c97df-bfc9-4255-a543-bcf48e23fd5e.bin
215165        2017/5/8/11/8/125c97df-bfc9-4255-a543-bcf48e23fd5e.bin

JMX Config

SELECT APSVk.string_value AS property, APSVv.string_value AS value
  FROM alf_prop_link APL
    JOIN alf_prop_value APVv ON APL.value_prop_id=APVv.id
    JOIN alf_prop_value APVk ON APL.key_prop_id=APVk.id
    JOIN alf_prop_string_value APSVk ON APVk.long_value=APSVk.id
    JOIN alf_prop_string_value APSVv ON APVv.long_value=APSVv.id
  WHERE APL.key_prop_id <> APL.value_prop_id
  AND APL.root_prop_id IN (SELECT prop1_id FROM alf_prop_unique_ctx);

Orphans

SELECT * FROM alf_content_url where orphan_time is not null;