A DB Data Source can be used to query a database, so that you can display the results from that query on your site. For example, you can build a number of SQL queries to be run on your Squiz Matrix database and display these results on your site. Once you have the SQL queries and results in your system, you can use an Asset Listing to display the records or a Data Source Graph to show a graph of the results.

The results obtained from the SQL query will update as the records in the database are updated. For example, if you created a SQL query to count the number of assets in your system, the count will change as you add assets into your system.

To be able to use this asset you need to have some knowledge of how to build a SQL query. You will also need to know the details of the database you want to run the query on.

Once you have created your DB Data Source , you can configure the asset on its associated screens. Many of these screens are the same or similar to those for a Standard Page and are described in the Asset Screens manual. In this chapter we will only describe the Details, Query Builder, Record Filter and Dynamic Inputs screens, which are different for a DB Data Source.

Details Screen

The Details screen for a DB Data Source allows you to setup the connection details for the database. For more information about the Status, Future Status, Thumbnail and Details sections of the Details screen see the Details Screen chapter in the Asset Screens manual.

Connection Setup

This section allows you to enter the database connection details. The fields available are as follows:

• Username: enter the username to use to connect to the database.
• Password: enter the password to use tot connect to the database. If there is no password, leave these fields blank.
• Database Details: this section allows you to enter the details for the database.
  • Database Type: select the type of database that you are connecting to. The available selections include PostgreSQL, Oracle and MySQL.
    
    Make sure the PHP PDO Driver is installed for the selected database type.
    Read https://www.php.net/manual/en/pdo.drivers.php for more information about installing the correct driver.
  • Database Name: enter the name of the database you wish to run the query on.
  • Host Name: enter the location of the database, for example the IP address.
• OR Complete DSN: specify the connection string for the database. For example, if you are connecting to a Squiz Matrix database, the complete DSN to its database is located in SQ_CONF_DB_DSN field the main.inc file.

Use a Connector Asset

This section allows you to select a DB Connector asset to use for the DB Data Source.

If you select an asset in this field, the database connection details that have been entered on the DB Connector will be used for the DB Data Source. Any information that you have entered into the Connection Setup fields will be ignored.

Query Builder Screen

The Query Builder screen is used to enter the SQL query that will be run on the database you specified on the Details screen.

SQL To Execute

This section allows you to enter the SQL query to execute on the database.

Shadow assets

Enter the query into the SQL Query field and click Commit. If the SQL Query is correct, shadow assets will be shown under the DB Data Source in the Asset Map, as shown in the figure to the right. For more information on shadow assets, refer to the Shadow Assets chapter in this manual.

Record Set Asset Names

This section allows you to specify the name of the Shadow Assets that appear under the DB Data Source in the Record Set Asset Names field.

Renamed assets

This name can either be a normal string or a combination of strings and keyword replacements. For example you can enter %data_source_record_set_name% to have Asset Name as the name of your shadow assets, as shown in the figure to the right.

Available Keywords

This section provides a list of available keyword replacements for the shadow assets. You can use any combination of these keyword replacements as well as strings as the name of the shadow assets. Each keyword can  be written as %data_source_record_set_<record attribute name> % or %ds__<record attribute name>%.

For example, if an attribute's name is "title", you can use the following either of the following keyword formats to print the value on the front end:

%ds__title%
%data_source_record_set_title%

Record Filter Screen

Dynamic Inputs Screen

The Dynamic Inputs screen allows you to use dynamic parameters within the SQL query.

Dynamic Variables

The Dynamic Variables section allows you to add variable names for the parameters that you want to add.

Enter the variable name into the Name field, enter the default value into the Default Value field and click Commit. The variable will be added to the list and you can continue setting it up in the Data Mappings section below. To delete a variable, click the Delete box and click Commit.

To use the variable within the SQL query, add double-percentage signs around the variable name. For example, if the name of the variable is Variable, add %%Variable%% within the SQL Query. Variable names are case sensitive, so for example, Variable is not the same as variable.

Data Mappings

The Data Mappings section allows you to set up the dynamic variables that have been added in the section above.

Select which variable to edit from the Parameter list and select a source from the Source list. For more information on the options in the list, refer to the Asset Listing manual.

Example of Setting Up a DB Data Source

The information returned on a DB Data Source can be used within a Site, for example, via an Asset Listing. To see how this works consider the following example of showing the information of a Squiz Matrix database.

DB Data Source

Create and configure the DB Data Source asset under your Site. On the Query Builder screen, input the SQL query, as follows:

select l.assetid, l.url, a.type_code, a.short_name from sq_ast_lookup l, sq_ast a
where l.assetid=a.assetid
order by l.assetid ASC
limit 15

The SQL query used in this example retrieves the asset ID, short name, type code and URL information of the assets within the system, with a limit of fifteen assets in ascending order by asset ID. Modify this SQL query to display alternative information within the Data Source Record Assets returned on the DB Data Source asset.

Once you have configured the SQL query, the returned records will be displayed as shadow assets under the DB Data Source in the Asset Map.

Asset Listing

Create an Asset Listing under your Site, configuring it to list the Data Source Record Set assets under the DB Data Source.

You can alter the information displayed for each listed record using the keyword replacements displayed in the Available Keyword field on the Query Builder screen of the DB Data Source. On the Default Format Bodycopy of the Asset Listing, configure the format of the listed items by using these keyword replacements, for example:

%ds__assetid%: <a href="%ds__url%">%ds__short_name%</a> [%ds__type_code%]

This format will display the asset ID, short name and type code of each record under the DB Data Source, as well as linking to the asset. This information can be seen when viewing the Asset Listing on the frontend, as shown in the figure below.

Example DB Data Source records listed on an Asset Listing

Now that this Asset Listing has been configured to list the records of the DB Data Source, it can be used elsewhere within your Site, for example, nested within its Design.