Page Content in Database

[Ecem Kanıyılmaz]

Hi,

We try to access page content from database. The content of the page is too long. We wrote a query using BODYCONTENT and CONTENT tables. However, we were able to access only a part of the page content with this query. We could not find where in the database the rest of the page content is stored.

Why can we only see part of the page content in database?

Where can we find the rest of the page content in the database?

Thanks.

[Nic Brough -Adaptavist-]

Reading the database is the worst possible way of trying to get information from an Atlassian system.  

It's impossible to tell you where the rest of the content is in the database, because we don't know what it is.  Macros?  Attachment?  Gadgets?  None of those are stored in the database, so if you want to "get" them, you need to look elsewhere anyway.

Could you tell us why you are trying to do this?  What are you trying to achieve?  Why are you reading the database for this?

[Ecem Kanıyılmaz]

We want to get the information in the table on a page. We will prepare report in PowerBI using the information in this table. Therefore, we need to pass table content to PowerBI. (This report should be made periodically.)

We want to do this with existing Confluence features, without purchasing plugins.

What are your suggestions?

[Nic Brough -Adaptavist-]

Ok, so you need to extract a table from a page.

The best way to do that would be to use the REST API to grab the content of a page and then go through it to read the table information out.  The data you'll get from REST is far more easy to parse than the raw data you'd get from a SQL query.

[Radek Dostál]

However, we were able to access only a part of the page content with this query. We could not find where in the database the rest of the page content is stored.

Then you queried wrong pages, or old page histories, but not the current version. As we don't know the SQL you used, hard to tell.

To get up to date content I used this:

SELECT c.contentid, c.contenttype, c.title, s.spacekey 
FROM CONTENT c 
JOIN BODYCONTENT bc 
    ON c.contentid = bc.contentid 
JOIN SPACES s 
    ON c.spaceid = s.spaceid 
WHERE c.prevver IS NULL
    AND c.contenttype IN ('PAGE', 'BLOGPOST')
    AND bc.body LIKE '%STRING YOU SEARCH FOR%';

That said, I don't quite see the need to query the db, or the expectation of the content being too long - you're querying the content, you get what you query for.

Whatever it is you're doing, there probably is a better way than sql.