Example - SQL Data

XX Sources

The Site Database and Any Database Content Sources are provided in the separate XX Sources extension addon package for Universal Content Puller. The sources in this extension are XX because they are the kind of source you may not want to let just anyone loose with, hence a separate package so they don't have to be installed with less sensitive sources.

Site Database

A content source that makes SQL queries on the site database could break a page, be used to pull data you do not want exposed, or even to subvert the site in more malign ways. The Site Database source does contain protection against such queries, but even so, you should not rely on that protection being foolproof and certainly not hacker proof.

To illustrate the use of this Site Database source, below is a list of addon pages pulled from the site database. In this example, its not anything you couldn't do with an autonav or page list block or with the Page List content source -  The example is designed to be obvious as a learning aid that you can use as a starting point when you have a requirement to display data from other tables.

The query used is shown below. The tokens {{limit}} and {{offset}} are inserted to facilitate pagination in the Content Source, rather than the default pagination in the Content Display

[code]
SELECT 
PagePaths.cID AS 'cID', 
PagePaths.cPath AS 'Path', 
CollectionVersions.cvName AS 'Name',
CollectionVersions.cvDescription AS 'Description' 
FROM PagePaths JOIN CollectionVersions ON PagePaths.cID = CollectionVersions.cID 
WHERE CollectionVersions.cvIsApproved = 1
AND PagePaths.ppIsCanonical = 1
AND PagePaths.cPath LIKE '%/addons/universal%' 
ORDER BY PagePaths.cPath 
ASC LIMIT {{limit}} OFFSET {{offset}}
[/code]

An optional count() query to predict the total allows source pagination to be fully anticipated. If not provided, source pagination is built dynamically by checking if there would be more to show.

[code]
SELECT 
count(PagePaths.cID) AS 'total_cID'
FROM PagePaths JOIN CollectionVersions ON PagePaths.cID = CollectionVersions.cID 
WHERE CollectionVersions.cvIsApproved = 1
AND PagePaths.ppIsCanonical = 1
AND PagePaths.cPath LIKE '%/addons/universal%'
[/code]

The Content Transform used is a simple Pass Through and the Content Display is Table, with column keys as headings and pagination set to 5 pages with an orphan limit of 1. Whilst the query also returns the cID, that is explicitly filtered out in the Table display. I like to keep it there for convenience when testing any new variations.

Path Name Description
/addons/universal-content-puller/example-responsive-tables Example - Responsive tables Pulling CSV data and displaying it as a table responsively
/addons/universal-content-puller/example-rotated-charts Example - Rotated Charts Displaying tabular data as a chart
/addons/universal-content-puller/example-rss Example - RSS Feed An example on pulling and displaying and RSS feed.
/addons/universal-content-puller/example-site-database Example - SQL Data Enter a simple SQL select statement to retrieve data from the concrete5 site database.
View settings

Any Database

If you need to retrieve data from a database other than the site database, use the Any Database content source.

With the Any Database source you can choose from your concrete5 installation's pre-configured databases and enter a simple SQL select statement to retrieve data from the selected database. The available databases need to be configured in /application/config/database.php as described in the documentation page Connecting to Multiple Databases.

Behind the scenes, Site Database above is a special case of Any Database, just constrained internally to be the site database.

return array(
    'default-connection' => 'concrete',
    'connections' => array(
        'concrete' => array(
            'driver' => 'c5_pdo_mysql',
            'server' => 'localhost',
            'database' => 'database',
            'username' => 'username',
            'password' => 'password',
            'charset' => 'utf8',
        ),
        'pricing' => array(
            'driver' => 'c5_pdo_mysql',
            'server' => 'secure-pricing.wherever.com',
            'database' => 'pricing_db',
            'username' => 'username',
            'password' => 'password',
            'charset' => 'utf8',
        ),
    ),
);

More complex SQL queries

Any Database and Site Database also serve as starting points for developing further custom database Content Source Plugins with more complex database queries hard coded into them, specific to your application.

Copy and rename the Site Database source plugin for your custom source plugin and then hard-code your more complex query into it by overriding the get_query_string($data) method of Any Database to return your built in query string and override enter_sql($data) with a stub or with a simple message about your query for the edit dialog.

Site Database already inherits from Any Database to stub out choose_connection() in the edit dialog and get_connection() to only return the site database. You may want to remove those or modify them if your custom query is on another database. 

The other methods you will likely want to override are the various checks on the SQL. If you leave them as-is, your custom SQL is likely to be rejected. With a had-coded query string these could be short-circuited.

If you want to take advantage of the built in pagination, you can continue to use the {{limit}} and {{offset}} placeholders.

More about custom plugins can be found at Plugin System..

 

Additional Pages

About this Sidebar

Creating a sidebar for a group of pages without messing about with stacks is an easy use-case for Universal Content Puller.

This sidebar is edited once, within the main addon page for Universal Content Puller.

It is then pulled into all UCP sub-pages using a UCP block.

The Content Source is Parent Page, set to pull the Sidebar area from 2 pages from the top. The Content Transform is Selector, set to remove container and row classes that, when unnecessarily nested, could mess up the Bootstrap grid. The Content Display is Plain, which just outputs the transformed text.

In the advanced settings, sanitization is disabled as we trust the source page and don't want to strip out any formatting or functionality from the pulled sidebar.