Developing (v7+)

Is it possible to get a mobile app to read the contents of a concrete5 site?

Permalink January 17, 2013 at 10:54 AM

Hi

I'm working a mobile application that would need to read the contents of a Concrete5 site for updates. Is it possible to do this? Thanks.

TechnatomyClient replied on Jan 17, 2013 at 3:38 pm Permalink Reply

Found some useful leads.

The content block data is stored in the database table `btContentLocal'. It turns out this table doesn't indicate which page it comes from.

In addition, it turns out these content blocks get a new ID, called bID, each time they are updated. I discovered this from the link below:

http://stackoverflow.com/questions/4228995/what-is-the-series-of-my...

Assuming I'm using this method, the next question is, how do I find out which page it comes from?

I've created a new question to explore this topic.

http://www.concrete5.org/community/forums/customizing_c5/how-to-tel...

If there are any other methods, please post them. I'm assuming I'm on the right track.

JohntheFish replied on Jan 17, 2013 at 6:39 pm Permalink Reply

It is much easier to do this the other way round. The relationships between c5's database tables is organised so that a page has an associated list of block ids (via the intermediary of versions).

The Global Areas addon is a good example of following this through the c5 core.

In concept, you could dig down and take the core sql query that does this and turn it inside-out.

There is also a recent addon called 'Where is my block' that finds which pages a block type is used on. That may also give some clues about determining where a specific block is used.

TechnatomyClient replied on Jan 18, 2013 at 5:35 pm Permalink Reply

Thanks. Those leads helped a lot.

I found a copy of the code for 'Where Is My Block' on Github.
https://github.com/beebs93/c5-where_is_my_block...

I learned that the installed add-ons are located in <source>\packages and that the php code gave clues on how the database is strung together.

Here is a key piece of code I found from
https://github.com/beebs93/c5-where_is_my_block/blob/master/where_is...

/**
* Fetches all of the blocks IDs of a specific block type ID on a specific page
*
* @see Concrete5_Model_Collection->getBlocks
* @param Page $objPage - Any page
* @param int $intBtId - A block type ID
* @return array
*
* @author Brad Beebe
* @since v0.9.1.2
*/
public function getPageBlockIds(Page $objPage, $intBtId){
$arrBlockIds = array();
$db = Loader::db();
$arrValues = array($objPage->getCollectionID(), $objPage->getVersionID(), (int) $intBtId);

Viewing 15 lines of 45 lines. View entire code block.

 
/**
* Fetches all of the blocks IDs of a specific block type ID on a specific page
*
* @see Concrete5_Model_Collection->getBlocks
* @param Page $objPage - Any page
* @param int $intBtId - A block type ID
* @return array
*
* @author Brad Beebe
* @since v0.9.1.2
*/
public function getPageBlockIds(Page $objPage, $intBtId){
$arrBlockIds = array();
 
$db = Loader::db();
 
$arrValues = array($objPage->getCollectionID(), $objPage->getVersionID(), (int) $intBtId);
 
// While there exists a native method to retrieve all block objects of a specific page,
// it has a large performance cost so we need to bypass it. We only need an array of
// block IDs so we can afford giving up the block objects.
$sqlBlocks = '
SELECT
Blocks.bID
FROM
CollectionVersionBlocks
INNER JOIN
Blocks ON (CollectionVersionBlocks.bID = Blocks.bID)
INNER JOIN
BlockTypes ON (Blocks.btID = BlockTypes.btID)
WHERE
CollectionVersionBlocks.cID = ?
AND
(CollectionVersionBlocks.cvID = ?
OR
CollectionVersionBlocks.cbIncludeAll = 1)
AND
Blocks.btID = ?
ORDER BY
CollectionVersionBlocks.cID ASC';
 
$arrResults = $db->GetAll($sqlBlocks, $arrValues);
 
if(is_array($arrResults)){
foreach($arrResults as $arrBlock){
$arrBlockIds[] = (int) $arrBlock['bID'];
}
}
 
return $arrBlockIds;
}

TechnatomyClient replied on Jan 18, 2013 at 5:36 pm Permalink Reply

The SQL query indicates that the bID column in table `btContentLocal' is somehow related to CollectionVersionBlocks.

After some more research and testing I found that the entire concrete site does not sit as a webpage at all. In fact, nothing in the conrete5 files change when a block is updated. Instead, only the MYSQL database is changed.

That means each time a webpage is called, it has to search through the database to find the corresponding webpage name, string together a list of HTML code (these are where the blocks are), and display them.

Armed with this knowledge I did a mysqldump in phpmyadmin by selecting the database and going to Export to do a quick dump of the SQL data to an SQL file. This file acts as import data for the database to any exported SQL file. As such, when I opened the file with Notepad++ (used to show PHP spacing), it shows the table IDs and table data. I then traced bID '21', which was the content block being updated, with the search feature in Notepad++. I found these tables linked to each other.

INSERT INTO `PagePaths` (`ppID`, `cID`, `cPath`, `ppIsCanonical`) VALUES
INSERT INTO `CollectionVersionBlocks` (`cID`, `cvID`, `bID`, `arHandle`, `cbDisplayOrder`, `isOriginal`, `cbOverrideAreaPermissions`, `cbIncludeAll`) VALUES
INSERT INTO `btContentLocal` (`bID`, `content`) VALUES

The table `PagePaths` shows the 'cPath', which holds the Cononical value I created in the sitemap page properties. Here, it gets traced to `CollectionVersionBlocks` using 'cID'. This is also where a 'bID' is linked. From `CollectionVersionBlocks', it gets traced to `btContentLocal`. From `btContentLocal`, the same 'bID' has a corresponding 'content', which is where our content block data lies.

However, not all 'bIDs' have a content in 'btContentLocal'. Only 'bIDs' with a 'btID' of 9 actually have them. This is found in the table 'Blocks'.

INSERT INTO `Blocks` (`bID`, `bName`, `bDateAdded`, `bDateModified`, `bFilename`, `bIsActive`, `btID`, `uID`) VALUES

TechnatomyClient replied on Jan 18, 2013 at 5:42 pm Permalink Reply

Here are the steps to get a mobile app to read Content Blocks based off a just having a webpage name:

1. Go to the Concrete5 database
2. Check cPath at table PagePaths to verify the correct webpage is being used
3. Find the cID from cPath using table PagePaths.
4. Gather a list of bIds for the cID using table CollectionVersionBlocks
5. Narrow the list down to bIDs that are Content Blocks (btID of 9) for said cID using table 'Blocks'
6. Find the content associated with bID using table 'btContentLocal'

I think I've got it. Some additional checks can be used to see where on the page the Content is located at well, but I'm going to try to run some test code to verify if this works first.

RadiantWeb replied on Jan 19, 2013 at 9:52 am Permalink Reply

There is an easy to use API that was created for mobile html5/phonegap apps here:

https://github.com/goradiantweb/radiantweb_api...

ChadStrat

TechnatomyClient replied on Jan 21, 2013 at 10:55 am Permalink Reply

I'm not sure where to begin. How does the mobile app interface with this single page?

TechnatomyClient replied on Jan 21, 2013 at 11:21 am Permalink Reply

Here is a database query I created that picks up all the content blocks of text and their associated page names. I created series of INNER JOINS to do the sorting in the database query.

SELECT pagepaths.cPath, btcontentlocal.content
FROM collectionversionblocks
INNER JOIN collections
  ON collections.cID = collectionversionblocks.cID
INNER JOIN pages
  ON pages.cID = collectionversionblocks.cID
INNER JOIN blocks
  ON blocks.bID = collectionversionblocks.bID
INNER JOIN btcontentlocal
  ON btcontentlocal.bID = collectionversionblocks.bID
INNER JOIN collectionversions
  ON collectionversions.cvID = collectionversionblocks.cvID
INNER JOIN pagepaths
  ON pagepaths.cID = collectionversionblocks.cID
WHERE pages.cIsSystemPage=0

Viewing 15 lines of 20 lines. View entire code block.

 
SELECT pagepaths.cPath, btcontentlocal.content
FROM collectionversionblocks
INNER JOIN collections
  ON collections.cID = collectionversionblocks.cID
INNER JOIN pages
  ON pages.cID = collectionversionblocks.cID
INNER JOIN blocks
  ON blocks.bID = collectionversionblocks.bID
INNER JOIN btcontentlocal
  ON btcontentlocal.bID = collectionversionblocks.bID
INNER JOIN collectionversions
  ON collectionversions.cvID = collectionversionblocks.cvID
INNER JOIN pagepaths
  ON pagepaths.cID = collectionversionblocks.cID
WHERE pages.cIsSystemPage=0
  AND collections.cHandle IS NOT NULL
  AND blocks.btID = "9"
  AND collectionversions.cvIsApproved = "1"
  AND pagepaths.ppIsCanonical = "1"
ORDER BY collectionversionblocks.cbDisplayOrder

Notes:
cIsSystemPage = "0" to ensure the page is a non system page
cHandle has several NULL valued pages so I excluded them. Im not sure what they do
btID = "9" for Content blocks
cvIsApproved = "1" to ensure the latest version approved version is used
ppIsCanonical = "1" to ensure the primary Canonical webpage name is used

TechnatomyClient replied on Jan 27, 2013 at 2:31 pm Permalink Reply

The answer is 'yes'! Turns out the URL can be read directly as xhtml. The content block can then be searched for using a parser.

In java, there is this fanastic add on called jSoup which reads in html as a url, searches it for tags and attributes, and displays the html back as a string. Here is the sample code I ran:

// Alternate Contents Getter using jSoup, an html to DOM converter.
//http://jsoup.org/download
org.jsoup.nodes.Document docSoup = Jsoup.connect("http://localhost/index.php?cID=121").get();
org.jsoup.nodes.Element e = docSoup.select("div[id=body]").first();
System.out.println("--jSoup Excerpted HTML Begins Here----------------");
System.out.println(e.html());
System.out.println("--------------------------------------------------");

To find the content block in the main area, just look for the <div id="body"> tag. If you want to find other areas, right click on your webpage and select view source. Then look for the corresponding area name in the div id attribute.

This is much easiesr than searching through the database, which is what I was doing in the previous posts.

Forums

Developing (v7+)

Is it possible to get a mobile app to read the contents of a concrete5 site?

Code

Post Reply

Delete Post

Mark Post as Spam

Destroy Spammer

Sign In?