Query in MySQL database

[june_cataquez1]

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 Data Model about the tables in Alfresco? because It's hard to know where the uploaded files are stored in the database.

Best Regards,

June Rodrigo Cataquez

[kaynezhang]

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

[june_cataquez1]

Hi Kayne, Thanks, just the data I needed.

[june_cataquez1]

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

[kaynezhang]

Try this:

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='title')

[june_cataquez1]

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