Example - Chart from SQLite

Here we have a more complex example. An SQLite database is uploaded to the Concrete File Manager and pulled using the SQLite File Manager with Form content source.

The database is the well used chinook.db example database for music sales data. Whilst there is plenty of data to play with, we have kept it relatively simple by querying to summarize sales data by country.

The form input Filter is connected to a partial match of the country name. When clicked, the page will reload with a reduces subset of countries. For example, try entering "land".

View settings

More details about how it is done

Universal Content Puller XX Sources includes sources for pulling from SQLite databases. To provide some data to work on, the publicly available chinook.db used in many datbase training and education exercises has been uploaded to the file manager so it is available as a ConcreteCMS file. As a prerequisite, the file extension ".db" was added to the allowed extensions at Dashboard > System & Settings > Files > Allowed File Types.

The SQLite File Manager With Form content source was then used to select the chinook.db file and from that pull the sales data with some SQL. This is in the SQLite dialect of SQL, which if you are familiar with MySQL has some small differences.

In the query below, we have one placeholder for a partial match on the Country provided by the form. Concatenation is managed by the || operator. In MySQL the equivalent would have been CONCAT('%',?,'%'). We need concatenation because the ? placeholder cannot be inside a string.

The form is simple HTML in an HTML Block.

[code language="SQL"]

SELECT `BillingCountry` AS 'Country',
      SUM(`Total`) AS 'Total'
  FROM `invoices`
  WHERE `BillingCountry` LIKE '%'||?||'%'
  GROUP BY `BillingCountry`
  ORDER BY `BillingCountry`


[code language="HTML"]

<form class="ucp-attach-form form-inline pull-right">
    <input type="text" name="Country" class="form-control">
    <input type="submit" value="Filter" class="btn btn-primary">


Transform and display

Having pulled data from the SQLite database and named the columns nicely in the SQL, the transform is a simple transpose with the Array Hacker transform.

Display is a Bar Chart from Universal Content Puller Charts.

Below we have the same data shown as a table. The SQLite File Manager With Form content source is identically configured so it responds to the Country filter above. No transform is required and the display is a Table.

Country Total Sales $
Argentina 37.62
Australia 37.62
Austria 42.62
Belgium 37.62
Brazil 190.1
Canada 303.96
Chile 46.62
Czech Republic 90.24
Denmark 37.62
Finland 41.62
France 195.1
Germany 156.48
Hungary 45.62
India 75.26
Ireland 45.62
Italy 37.62
Netherlands 40.62
Norway 39.62
Poland 37.62
Portugal 77.24
Spain 37.62
Sweden 38.62
USA 523.06
United Kingdom 112.86
View settings

SQL Extract Transforms

If you need to run a query on data, the SQL sources from Universal Cointent Puller XX Sources are the best solution.

However, data is not always available in a database source. For such situations,  Universal Cointent Puller XX Sources also provides the SQL Extract and SQL Extract with Form transforms.

The general process is to pull the content source and, if necessary, transform it into a table. This will typically involve a Pipeline transform so an  SQL Extract or SQL Extract with Form transform can then follow.

These SQL Extract or SQL Extract with Form transforms work in a similar way to the equivalent SQL sources. Internally, they translate the tabular data into a temporary SQLite database, then run the configured query to extract from the data and pass it onwards to further transforms or to the content display.

Query expressions consequently use the SQLite syntax which has minor differences from MySQL.


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.