Help Center

Menu

DB data source

You can use a DB data source to query a database and display the results from that query on your site. For example, you can build several SQL queries to be run on your 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 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.

Some knowledge of how to build a SQL query is required to use this asset. You will also need to know the details of the database on which you want to run the query.

Once you have created your DB data source, you can configure the asset on its associated screens. These screens are similar to a standard page and are described in the Asset details screen documentation.

This documentation 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 set up the connection details for the database. Read the Asset details screen documentation for more information about the Status, Future status, Thumbnail, and Details sections of the Details screen.

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 to 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 to which you are connecting. 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/documentation/en/pdo.drivers.PHP for more information about installing the correct driver.

Database name

Enter the name of the database on which you wish to run the query.

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 connect to a Matrix database, the complete DSN to its database is located in the sq_conf_db_dsn field of 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 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 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

  1. Enter the query into the SQL query field.

  2. Select Save.

If the SQL query is correct, shadow assets will be shown under the DB data source in the asset tree.

Record set asset names

This section allows you to specify the shadow assets under the DB data source in the Record set asset names field.

Renamed assets

This name can either be a regular 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 and 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

The Record filter screen allows you to create a set of filters to refine the results of the DB data source.

Filter options

This section allows you to configure the record filtering settings on the DB data source. The following fields are available:

Filter status

Enable the filtering of records, determined by the conditions created on the Record filter screen. If this option is disabled, no filtering will occur on the results of the DB data source.

Logical grouping

Select the grouping logic used for matching the configured conditions. You can select to refine the results of the DB data source depending on whether any condition or all conditions are fulfilled.

Filter mode

Specify whether the records returned on the DB data source match the configured conditions (return the records matching the given condition) or those that do not (filter out the records matching the given condition).

Record filters

This section allows you to configure the conditions to check against when filtering records on the DB data source. In the conditions section, specify the conditions to match against the records of the DB data source. You can add multiple conditions by clicking the More…​ button. When these conditions are met, records are filtered according to the options configured.

Record set field name

The field to check against on the data source record set. For example, entering name would check this condition against the Name field of the record.

Value to match

The value to match on the specified field, as configuring in the Record set field name setting. You can use dynamic variables configured on the Dynamic inputs screen in this field, using the keyword format %%variablename%%.

Match type

The parameters used for matching the condition. The options available are:

String match - contains

A partial match of the condition on the record. For example, the value note would match against the field values notebook and denoted.

String match - exact

An exact match of the condition on the record. For example, the value note would only match against the field value note.

Regex match

A regular expression match of the condition on the record. For example, the value [0-9]+ would only match against field values containing a numeral.

Match case

Determines whether the condition value is case-sensitive when matching.

Allow empty

Specifies whether the condition should perform a wildcard search if the condition value is evaluated as an empty string. If this option is enabled, an empty value will return all records on the DB data source (no filtering). This option is useful when using dynamic variables within the Value to match field, which may not always return a value that can be matched.

Delete?

To delete a condition:

  1. Select this field.

  2. Select Save. The condition will be removed from the conditions table.

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.

  1. Enter the variable name into the Name field.

  2. Enter the default value into the Default value field.

  3. Select Save. 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:

  1. Click the Delete box

  2. Select Save.

To use the variable within the SQL query, add double-percentage signs around the variable name. For example, if the variable’s name 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 added in the section above.

Select which variable to edit from the Parameter list and select a Source from the source list.

Read the Asset listing documentation for more information on the options list.

Example of setting up a DB data source

The information returned on a DB data source can be used within a site, for example, through an asset listing. Consider the following example of showing the information of a 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 tree.

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 and link to the asset. This information can be seen when viewing the asset listing on the front end, 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.