OpenAdmin Query Console - tsgrp/HPI GitHub Wiki
For monitoring migrations or ongoing, asynchronous jobs on large datasets, it is often more helpful to bypass the Alfresco API and instead run these queries directly against the repository's database. The actual database implementation does not matter so long as it is a SQL-compliant(and not db-specific-compliant) query, but this console is only intended - and only supports - read only SELECT queries. Note that attempting to use other queries will error.
The first point of setup is defining the following in your project bean config:
<bean id="ocSqlManager" class="com.tsgrp.opencontent.alfresco.query.OCSQLDbQueryManager">
<property name="databasePool" ref="sqlConnector"/>
</bean>
<bean id="sqlConnector" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${query.alfresco.sql.passthrough.jdbc.driver}"/>
<property name="url" value="${query.alfresco.sql.passthrough.db.url}"/>
<property name="username" value="${query.alfresco.sql.passthrough.username}"/>
<property name="password" value="${query.alfresco.sql.passthrough.password}"/>
</bean>Ensure that the URL and driver class name match the database you want the Query console to use, and that a read only user's credentials are provided for the username and password.
Within OC, this then enables the Query Impl to have a direct line against the database, vs. having to use a repository API.
Next, in your OCMS configuration, add the following configuration to your app object in your config-project.js:
"modules/hpiadmin/adminsearchtool/adminsearchtool" : {
passthroughQueryEnabled: true,
}This communicates to OCMS that it should render the front-end portion of the console for users with access to OpenAdmin.
At this point, the Query Console should be usable. Navigate to the OpenAdmin screen, select tools from the bottom left, and then select the Search Tool. The Query Console appears below the Update Properties tool.
Provided below are a few example queries for running in Alfresco.
Finding all documents with "TSG" in the name:
SELECT
n.id AS DB_ID,
n.uuid AS UUID,
props.string_value
FROM
alf_node n,
alf_node_properties props,
alf_qname q,
alf_namespace ns
WHERE
ns.uri = 'http://www.alfresco.org/model/content/1.0'
AND ns.id = q.ns_id
AND q.local_name = 'name'
AND props.qname_id = q.id
AND props.string_value LIKE '%TSG%'
AND props.node_id = n.idFinding all documents created on a given date:
SELECT
n.id AS DB_ID,
n.uuid AS UUID
FROM
alf_node n
WHERE
n.audit_created LIKE '2017-10-03%'
;Get a count of all documents in all stores in Alfresco.
SELECT COUNT(n.id)
FROM alf_node nGet a count of all nodes of type "insuranceDemo:claimsDocument".
SELECT COUNT(n.id)
FROM
alf_node n,
alf_namespace type_ns,
alf_qname type_name
WHERE
type_ns.uri = 'http://www.tsgrp.com/model/insuranceDemo/1.0'
AND type_name.local_name = 'claimsDocument'
AND type_name.id = n.type_qname_idGet the IDs ad names of all nodes of type "insuranceDemo:claimsDocument".
SELECT
n.id AS DB_ID,
n.uuid AS UUID,
props.string_value AS objectName
FROM
alf_node n,
alf_node_properties props,
alf_qname q,
alf_namespace ns,
alf_namespace type_ns,
alf_qname type_name
WHERE
ns.uri = 'http://www.alfresco.org/model/content/1.0'
AND ns.id = q.ns_id
AND type_ns.uri = 'http://www.tsgrp.com/model/insuranceDemo/1.0'
AND type_name.local_name = 'claimsDocument'
AND type_name.id = n.type_qname_id
AND q.local_name = 'name'
AND props.qname_id = q.id
AND props.node_id = n.idFurther examples are available on the following page: https://blog.dbi-services.com/alfresco-some-useful-database-queries/
Alfresco uses IDs to represent nodes, types, aspects, properties, etc. in the database. These numbers will be different on each instance of Alfresco, which can cause crafting SQL queries to be difficult. The above queries do extra table joins to get around this, but this can slow queries down significantly. To get around this OpenContent provides an endpoint to fill these numbers using Alfresco's internal caches.
For example, this query with placeholders:
SELECT node.store_id, node.uuid FROM alf_node node
JOIN alf_node_aspects aspects ON aspects.node_id = node.id
JOIN alf_node_properties props on props.node_id = node.id
JOIN alf_content_data cd on (cd.id <> 0 AND cd.id = props.long_value)
AND node.store_id = @store@workspace://SpacesStore@store@
AND aspects.qname_id = @qname@cm:titled@qname@
AND node.type_qname_id = @qname@cm:content@qname@
AND props.qname_id = @qname@cm:content@qname@
AND cd.content_mimetype_id IN (
@mimetype@text/xml@mimetype@,
@mimetype@video/quicktime@mimetype@,
@mimetype@image/tiff@mimetype@,
@mimetype@application/rtf@mimetype@,
@mimetype@text/html@mimetype@,
@mimetype@image/bmp@mimetype@,
@mimetype@image/gif@mimetype@,
@mimetype@application/vnd.ms-xpsdocument@mimetype@,
@mimetype@image/png@mimetype@,
@mimetype@application/pdf@mimetype@,
@mimetype@text/plain@mimetype@,
@mimetype@image/jpeg@mimetype@,
@mimetype@message/rfc822@mimetype@,
@mimetype@application/vnd.openxmlformats-officedocument.wordprocessingml.document@mimetype@,
@mimetype@application/msword@mimetype@
)Would be translated and returned as:
SELECT
node.store_id,
node.uuid
FROM
alf_node node
JOIN
alf_node_aspects aspects
ON aspects.node_id = node.id
JOIN
alf_node_properties props
on props.node_id = node.id
JOIN
alf_content_data cd
on (
cd.id <> 0
AND cd.id = props.long_value
)
AND node.store_id = 6
AND aspects.qname_id = 30
AND node.type_qname_id = 51
AND props.qname_id = 51
AND cd.content_mimetype_id IN (
1,
-1,
-1,
-1,
3,
-1,
-1,
-1,
8,
9,
2,
7,
-1,
-1,
13 )These numbers will change based on the instance you run it on. You can then take this query and use it in the Query Console or anywhere else.