DB Data Source

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.

Tip: 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 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 Connection Setup section of the Details screen 

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.

The Use a Connection Asset section of the Details screen

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.

The SQL to Execute section of the Query Builder screen

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.

The Record Set Asset Names section of the Query Builder screen 

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.

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.

The Dynamic Variables section of the Dynamic Inputs screen 

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.

The Data Mappings section of the Dynamic Inputs screen

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. 

Using the DB Data Source

Once you have created the DB Data Source and shadow assets in your system, you can use them on your site via the Asset Listing page. To see how this works, consider the example of showing the results from the following SQL query:

Select sq_ast.type_code, sq_ast_typ.name, 
Count(*) as num_assets from sq_ast 
left join sq_ast_typ on sq_ast.type_code = sq_ast_typ.type_code 
group by sq_ast_typ.name,sq_ast.type_code 
order by num_assets desc limit 15

DB Data Source

Create a DB Data Source asset under the site. Enter the SQL query on the Query Builder screen and click Commit. The shadow assets are created under the DB Data Source in the Asset Map.

Asset Listing

Create an Asset Listing under the Site. On the Details screen enter the following values:

• Asset Types to List: select Data Source Record Set. This will only show the shadow assets that appear under the DB Data Source.
• Root Nodes: select the DB Data Source as the Root Node for the Asset Listing page.
• Direct Links Only: change this value to Yes so that it will only show the shadow assets that are under the DB Data Source.

On the Page Contents Bodycopy for the Asset Listing, specify a title for the page and enter the keyword replacement %asset_listing% on the Edit Contents screen.

In this example for each shadow asset we want to show the name of the standard name of the asset type and how many there are in the system. As this information comes from the SQL query we can use the keyword replacements that are listed on the Query Builder screen of the DBData Source asset. Edit the contents of the Default Format Bodycopy and put these keywords into the WYSIWYG Editor. An example of this is shown in the figure below.

WYSIWYG Editors of the Default Format Bodycopy

When you preview the Asset Listing, for each shadow asset under the DB Data Source it will show the type of asset and how many there are in the system. 

PreviousNext