topic Re: Query in MySQL database in Alfresco Forum

Query in MySQL database

june_cataquez1 — Wed, 18 Jan 2017 11:16:26 GMT

Hi fellow Alfrecians,I already have multiple documents uploaded to alfresco (documents are uploaded to different Sites I made), and the database I'm using in MySQL(XAMPP), now, I want to query a select statement so that I can know the files that the users are uploading, is it possible? is there a Da

Re: Query in MySQL database

kaynezhang — Thu, 19 Jan 2017 03:23:25 GMT

Using following sql to select all contents

SELECT n.id AS "Node ID",
n.store_id AS "Store ID",
u.content_size,
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')

About alfresco database diagram you can refer to http://bosdonnat.fr/data/alfresco/schema_bd_alfresco.jpg

Re: Query in MySQL database

june_cataquez1 — Thu, 19 Jan 2017 05:30:16 GMT

Hi Kayne, Thanks, just the data I needed.

Re: Query in MySQL database

june_cataquez1 — Thu, 19 Jan 2017 05:38:41 GMT

Hi Kayne,

I tried the query you gave and it works, I have a question, in the Field 'Document Name' it seems like it fetched a garbage name (e.g. a4443108-15cd-447a-ab3c-edf9dff3957b) is this normal? is there a way to fetch the Name the user assigned to the particular document?

Thanks in advance.

Best Regards,

June

Re: Query in MySQL database

kaynezhang — Thu, 19 Jan 2017 07:42:23 GMT

Try this:

Re: Query in MySQL database

june_cataquez1 — Wed, 25 Jan 2017 06:56:13 GMT

Hi Kayne,

Thanks, I have another question, can I query all the rows that I added in a Data List (e.g. Contact List or Custom List)?

Best Regards,

June