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".
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]
[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">
</form>
[/code]
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 |
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.