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
- Query builder screen
- Record filter screen
- Dynamic inputs screen
- Example of setting up a DB data source
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.
This section allows you to enter the database connection details. The fields available are as follows:
Enter the username to use to connect to the database.
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_dsnfield of the
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.
The query builder screen is used to enter the SQL query run on the database you specified on the Details screen.
This section allows you to enter the SQL query to execute on the database.
Enter the query into the SQL query field.
If the SQL query is correct, shadow assets will be shown under the DB data source in the asset tree.
This section allows you to specify the shadow assets under the DB data source in the Record set asset names field.
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.
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:
Record filter screen allows you to create a set of filters to refine the results of the DB data source.
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 filterscreen. 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).
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
namewould 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
- 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
notewould match against the field values
- String match - exact
An exact match of the condition on the record. For example, the value
notewould only match against the field value
- 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.
To delete a condition:
Select this field.
Click Save. The condition will be removed from the conditions table.
The Dynamic inputs screen allows you to use dynamic parameters within the SQL query.
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.
Click 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:
Click the Delete box
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
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.
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.
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.
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.
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.