This page is used to manage (add, edit or delete) data sources which select data from databases. Data are selected based on a SQL query (usually it is SELECT query).
On this page administrator can
- Create a new data source using "New" button.
- Edit an existing data source by clicking it's name or by selecting checkbox at left of appropriate data source and clicking "Edit" button.
- Remove data sources by selecting checkbox at left of appropriate data sources and clicking "Delete" button.
Create/edit data source
The following parameters can be defined for data source:
- ID is record number in the database. It is used by "ARI Smart Content" applications to select data source(s) which will be used.
- Name is a name of the data source.
- Description is a description of the data source.
- DB Connection is a DB connection which will be used by the data source to connect to the database. If the parameter is set to "- Default -" then default DB connection, which is defined on DB connections page, will be used.
- Query is an SQL query which is used to retrieve data from the database. For example if you want to show id and title of Joomla! articles, select a connection to Joomla! database in DB Connection parameter and use the following SQL query:
SELECT id, title FROM #__content
More information about syntax of SELECT SQL query is available here. Different databases are also used different dialects of SQL language. More information can be found in documentation for the database which is used on your server.
It is possible to use the following predefined variables in WHERE clause:
- {$UserId} is ID of currently logged user. For guest users it will be 0.
- {$UserName} is name of currently logged user.
- {$UserLogin} is login of currently logged user.
- {$UserEmail} is email of currently logged user.
For example if want to select id and title of articles which are created by currently logged user, use the following query:
SELECT id, title FROM #__content WHERE created_by = {$UserId}
It also possible to use request variables in WHERE clause. They are used in the following way:
{$REQUEST:VAR_NAME}
Where VAR_NAME is a name of request variable. For example if want to show id and title of articles where title contains value of search request variable, use the following SQL query:
SELECT id, title FROM #__content WHERE ({$REQUEST:search|empty:''} = '' OR title LIKE CONCAT('%', {$REQUEST:search|empty:''}, '%'))
The following parameters are used to defined some properties and formatters for fields in the selected data:
- All columns sortable is used to mark all selected fields as sortable. This property is not used by the data source in anyway, but it can be used by an application which is used the data source (for example by Datatable application to recognize what fields should be sortable). This property can be overridden by "Columns settings → Sortable" parameter for specific field(s).
- All columns searchable is used to mark all selected fields as searchable. This property is not used by the data source in anyway, but it can be used by an application which is used the data source (for example by Datatable application to recognize what fields should be sortable). This property can be overridden by "Columns settings → Sortable" parameter for specific field(s).
- Columns settings parameters section is used to define properties and formatters for specific fields. The following parameters are available:
- ID is a field name or zero-based index (it means the first field has index 0, the second has index 1 and etc.). This parameter is required and used to specify the field for what some properties will be defined.
- Type is a field type. It is used by some applications to sort fields on client side properly (for example by Datatable application). If data is sorted on server side then this parameter will be ignored and data will be sorted using database clause like ORDER BY and the type which is defined for the field in DB table. The following types are supported:
- String values in the column will be sorted as strings.
- HTML values will be sorted as strings, but before sorting HTML code will be removed from values.
- Date is used when the column contains dates in format. For example: .
- Date (dd/mm/YYYY) is used when the column contains dates in dd/mm/YYYY format. For example: 10/02/2014, 01/12/2012.
- Numeric is used when the column contains numbers. For example: 1, -20, 1.5 and etc.
- Numeric (string) is used when the column contains numbers with some text (a currency symbol, measure unit and etc.). In this case all text will be removed before sorting. For example: $1, -2.3 m and etc.
- Numeric (HTML) is used when the column contains numbers with HTML code. In this case HTML code will be removed before sorting. For example: <span style="color: red">10</span>, <b>-2.3</b> and etc.
- Numeric (x,xxx.xxx) is used when the column contains numbers in x,xxx.xxx format. For example: 5,234,100.234, -1,200.100 and etc.
- Sortable is used to define should be the field mark as sortable or not. If the parameter is set to "Inherit" then value which is defined in All columns sortable parameter will be used.
- Searchable is used to define should be the field mark as searchable or not. If the parameter is set to "Inherit" then value which is defined in All columns searchable parameter will be used.
- Hidden is used if want to hide the column on frontend, but use it in Format parameter for example.
- Alias is used to define field alias. It will be shown on frontend instead of field name. For example you use SELECT id, title FROM #__content query to select data and want to show "Article Id" caption for id field on frontend in a table then set Columns setiti the parameter to Article Id value. If the parameter is empty then field name will be shown.
- Align this property is used by table applications (Datatable, HTML table and etc.) to align values in the column on frontend.
- Width this property is used by table applications (Datatable, HTML table and etc.) to define column width. This value can be ignored by applications depends on their configuration (for example auto size is enabled). Possible values: 100px, 15% and etc.
- CSS Class this property is used by table applications (Datatable, HTML table and etc.) to add the entered CSS class for the column in a table. Can be used to defined specific styles for the column.
- Format is used for formatting values of the column. It is useful if column contains links to pages or images and it is required to convert them to HTML hyperlinks or images. It is possible to use {$value} predefined variable and it will be replaced with value of the column. For example the column contains to images (http://www.domain.com/photos/photo1.jpg, http://www.domain.com/photos/photo2.jpg and etc.) and you want to convert them to HTML images then use the following value for Format parameter:
<img src="{$value}" />
If the column contains links to pages and want to convert them to HTML hyperlinks, use the following value:
<a href="{$value}" target="_blank">Open page</a>
It is also possible to use values from other columns from the same record. For this user {$columns:COLUMNNAME} variable where COLUMNNAME is name of the column. For example you use SELECT ProductId,ProductName,ProductDetailsLink FROM products query to select data and want to convert values of ProductDetailsLink column to hyperlinks and use value of ProductName column as link text. For this set "Columns settings → ID" parameter to ProductDetailsLink value and "Columns settings → Format" parameter to the following value:
<a href="{$value}">{$columns:ProductName}</a>
"Data transformation" section is used to transform data. If "Revert data" parameter is set to "Yes", the selected data will be reverted. For example the following data:
#
|
col 1
|
col 2
|
col 3
|
val 1
|
val 1-1
|
val 1-2
|
val 1-3
|
val 2
|
val 2-1
|
val 2-2
|
val 2-3
|
will be transformed in following way:
#
|
val 1
|
val 2
|
col 1
|
val 1-1
|
val 2-1
|
col 2
|
val 1-2
|
val 2-2
|
col 3
|
val 1-3
|
val 2-3
|
This transformation can be useful if necessary to prepare data for a graph or a chart.
"Graph transformation" section is also used to transform data. It accepts the following parameters:
- Enable is used to enable graph transformation.
- X axis column is used to define a column which values will be used as labels on X axis.
- Y axis column is used to define a column which values will be used as labels on Y axis.
- Value column is used to define a column which values will be used point values.
- Default value is used to define a default value which will be used if value from Value column is empty.
To understand how it works, see how the following table will be transformed:
SubmissionId
|
Name
|
Value
|
1
|
FirstName
|
Tom
|
1
|
LastName
|
Smith
|
1
|
Age
|
33
|
2
|
FirstName
|
John
|
2
|
LastName
|
Doe
|
2
|
Age
|
21
|
If X axis column parameter is set to Name value, Y axis column is set to SubmissionId value and Value column is set to Value then the data will be transformed in the following way:
|
FirstName
|
LastName
|
Age
|
1
|
Tom
|
Smith
|
33
|
2
|
John
|
Doe
|
21
|
If "Revert data" and "Graph transformation" are enabled together then "Revert data" will be applied first and after this "Graph transformation" will be applied. If any transformation is used, applications can't retrieve data dynamically (via AJAX for example).