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.

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 Concrete CMS 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.

SQLite Databases

SQLite databases can be pulled from a URL or file path and from a database file uploaded to the Concrete CMS file manager. To use an SQLite database with the file manager, you will need to add the 'db' extension at Dashboard > System & Settings > Files > Allowed File Types.

For an more information, see Example - Chart from SQLite. SQLite is also used for SQL transforms. Beware that SQLite has some minor differences in language compared to MySQL.

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..