DB Data Source
Last Updated: 17 Apr 2017
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.
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.
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.
- 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.
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.
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.
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>
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
The 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 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 Date Source are those that 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, the records will be filtered according to the Filter 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 especially useful when using dynamic variables within the Value to Match field, which may not always return a value that can be matched against.
- Delete?: to delete a condition, select this field and click Commit. 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.
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.
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.
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.
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.