DbFace script is purpose-built query language based on SQL, powered by smarty template engine (with YAML front-matter supported).

---
event_token: 'iap_purchased'
date: '2020-10-06'
---
select count() from event where event_token='{$event_token}' and created_at_date = '{$date}';
sql-query-2;
sql-query-3

DbFace will simply split the queries by ;, and run theses queries one by one, and combine (join) all the result set into one result set.

If the big SQL query contains ;, and you want to force DbFace run the query by one batch, please add {config-one-query} tag, or use one_query front matter.

---
event_token: 'iap_purchased'
date: '2020-10-06'
one_query: true
---
{config-one-query}
select count() from event where event_token='{$event_token}' and created_at_date = '{$date}';
sql-query-2;
sql-query-3

Script Features#

• Accept GET, POST, SESSION parameters as variables
• Accept YAML front-matter
• Auto join multiple scripts query result, and combine them as one result

Using smarty template engine to extend SQL script achieves the function that user form variables can be dynamically inserted in SQL scripts and the scripts can be dynamically rewritten according to the input.

Before all the scripts submitted to the database, all the variables will be replaced with the value of the current form.

Form variable value ​​can be applied to the script.

For example, a tabular report query application script:

select OrderData, Status, ColumnName from Orders where Status = '{$status}'

Considering a form that has a form field named "status", if user input "Shipped", the final query should be

select OrderData, Status, ColumnName from Orders where Status = 'Shipped'

The final statement may vary depending on the the choice of different data sources.

For example:

About advanced usage of the script, please refer to Smarty template.

Form script#

Form variables used in the script#

Depending on the control, form controls may output two types of data: Single value and a value list.

• Single value

Single Line, Multi Line, Drop down, and Radio Group submit only one value.

If it's single value, please insert it into the scripts in the form {$Field Name} . Considering a Single Line named Status, you can insert it into the filter condition or the scripts in the form {$status}. Such as:

select orderDate, status where status = '{$status}'

Please note: when this variable is included in the script, you need to add quotes. When parsing script, Dbface will not automatically determine the type of the field.

• Multiple value

Checkbox and Multi Select will submit multiple value.

Multi-value control used in the filter condition is the same with Single value control, meaning that: a match to meet the conditons.

Multi-value control used in script mode is different from Single value control, such as the search condition.

... Where status = '{$status}'

Dbface will not retrieve the data, because {$status} is an array so that it will be forced to be converted to a string 'Array'. The correct usage should be:

... Where status in ({$status|join})

{$status | join} Syntax will automatically expand and split this value.

Data Source script#

Considering some form controls having limited value, such as DropDown, Multi Select, Checkbox, RadioGroup, you can use data source scripts to specify their limited value. When executing, DbFace will dynamically execute data source script to get the value of form controls.

Data source script can output one or two fields, the remaining fields will be ignored.

If there are two fields: the contents of the first field as submitted value ​​(data is applied in the script), the second field as the displayed value ​​(data is only for display).

As

select value from table where ...

Or

select key, value from table where ...

To use the data source scripts, click the "Edit" button in Forms controls having limited value. Click the "Script" button in the edit box of pop-up window field, the input textbox "Data source script" will be displayed, where you can enter the data source script and save it.

Do not forget to use the data source script generator, click on the generator icon behind the "script" button, all tables of current links and their fields will pop up. Check the required fields to generate queries.

Form field onchange script#

After editing the form field, you can set a query script. When the input is completed and the focus is lost, the script will automatically execute and fetch data form the final database. The data obtained will be mapped by name to the current form control one by one.

To change the script, click on the "Edit" button, the field property edit box pops up, click on the "Onchange Scripts" button, enter the script in the expanded textbox, then save it.

You can use the current form variable to change form field script.

Form loading script#

Users can specify a loading script (query) for user form to initialize the form data value.

After the application opened, DbFace will detect whether the current form has the loading script, if so, Dbface will automatically execute this loading script and map the query result to the current form one by one. If the query returns multiple data, Dbface will provide the "Browse" button, then you can select the required data for the form control value.

To create a form loading script, click the Form Editor "Properties" button, then the property editbox pops up, where you can enter the loading script and save it.

Tips#

Script Plugins#

DbFace provides many script plugins that help you to build complex application source.

config#

You can use config function to assign variables to current application script.

{config key1=value1 key2=value2}

Now, you can use {$key1}, {$key2} in the application script.

For loading multiple fixed variables, you can also create ini file

{config file="your-ini-file-which-located-in-dbface-system-folder"}

DbFace will try to load the ini file and assigned all variables in the ini file. To create ini file, please open Settings menu and click Cloud Code link, then click "Start Development Workspace", then click "New File" in system folder.

create_variable#

As you already known, you can create Global Variables in Parameters section. All available variables will be assigned in every appliction script.

You can also create local variable that only available in current application script.

{create_variables name="local-variable-name" value="value" sql=""  func="" url=""}

value, sql, func and url should be (and only one) assigned.

• value: assigned fixed value to {$local-variable-name}

• sql: DbFace wil try to use the sql to query application database, and assigne the result set to {$local-variable-name}

• func: DbFace will execute the function and assign the function result to {$local-variable-name}

• url: DbFace will try to call the URL and assign the http response body to {$local-variable-name}

snippet#

snippet plugin helps you refer tagged application script into current application script.

{snippet name='your-tagged-sql-query'}

purge_cache#

Purge DbFace cache data.

{purge_cache}

Front-matter#

Front matter allows you to keep metadata attached to an application. i.e., embedded inside a variable.

DbFace application script can contains a YAML front matter block.

---
event_token: 'iap_purchased'
---
select count() from event where event_token='{$event_token}'

This script will be compiled to

select count() from event where event_token='iap_purchased'

You can also define yaml files in user directory, and import them into current application script.

import: config/my-yaml-file.yaml

or

import:
  - config/my-yaml-file1.yaml
  - config/my-yaml-file2.yaml

DbFace will try to load these yaml files and merge variables into current application script.

DbFace supports four formats for front matter, each with their own identifying tokens.

TOML identified by opening and closing +++.

YAML identified by opening and closing ---.

Quick Filter Options#

Quick Filter Options is an inline form attached to the DbFace application.

You can define quick filter options in Front-matter, DbFace support select, date and daterange as quick filter options.

quick_filters: 
  country:
    type: select (date or daterange)
    multiple: true (only valid for select)
    selected: usa 
    list: (select control value list)
      - USA (or usa:USA)
      - UK
      - CN
      - RU
  search_date:
    type: date
    value: today

We can use quick filter options in current application script by smarty manner.

We can also use !script tag to make DbFace query the select value

quick_filters: 
  country:
    type: select (date or daterange)
    multiple: true (only valid for select)
    selected: usa 
    list: !script select distinct country_name from country
  search_date:
    type: date
    value: today

DbFace will query the target database

select distinct country_name from country

and use the result to render the country drop list.

---
quick_filters: 
  search_date:
    type: date
    value: today
---
WITH (
  select sum(revenue) from event where package_name='com.test.package' and event_token='iap_purchased' and 
  created_at_date='{$search_date}'
) as total_payment

select roleId,count() as "Order Numbers",round(sum(revenue), 2) as "Payment", round(total_payment,2) as "Revenue", concat(toString(round(sum(revenue) * 100/total_payment, 2)), '%') as "Percent" from event 
where package_name='com.test.package' and event_token='iap_purchased' and created_at_date='{$search_date}'
group by roleId

---
quick_filters: 
  search_date:
    type: daterange
    value: last7days
---
WITH (
  select sum(revenue) from event where package_name='com.test.package' and event_token='iap_purchased' and 
  created_at_date>='{$search_date|from}' and created_at_date<='{$search_date|to}'
) as total_payment

select roleId,count() as "Order Numbers",round(sum(revenue), 2) as "Payment", round(total_payment,2) as "Revenue", concat(toString(round(sum(revenue) * 100/total_payment, 2)), '%') as "Percent" from event 
where package_name='com.test.package' and event_token='iap_purchased' and created_at_date='{$search_date}'
group by roleId

Chart Total Widget Script#

We can attach a total widget at DbFace chart reports. If we do not specify Total Widget Script, DbFace will collect all series data and caculate the summary value for this widget. If the total widget script specified, DbFace will use the script to query series summary value.

Chart Detail Table#

At Chart Options settings, check "Display Detail Table", DbFace will display the original chart data source using tabular.

Table Calculated Fields#

We can append new fields for table report using Caculated Fields in application script.

---
calculated_fields:
  ARPPU: row['Payment'] / row['Payers']
---

WITH (
  select sum(revenue) from event where package_name='com.flamingogames.gok' and event_token='iap_purchased' and 
  created_at_date='{$date_range|from}'
) as total_payment

select visitParamExtractString(params, 'sid') as sid,count() as "Order Numbers", count(distinct roleId) as "Payers",round(sum(revenue), 2) as "Payment", round(total_payment,2) as "Total Revenue", concat(toString(round(sum(revenue) * 100/total_payment, 2)), '%') as "Percent" from event 
where package_name='com.test' and event_token='iap_purchased' and created_at_date='{$date_range|from}'
group by sid

The table will append a caculated ARPPU field, that value

row['Payment'] / row['Payers']

We can use row, data, and row_index in the expression

• row: current row data
• data: the whole result set
• row_index: current row index (start with 0)

Table Sort Fields#

We can use sort_fields in script front matter to re-sort the result table columns.

---
calculated_fields:
  ARPPU: row['Payment'] / row['Payers']

sort_fields: ['sid', 'Payers', 'Order Numbers', 'Payment', 'Total Revenue', 'Percent', 'ARPPU']
---

WITH (
  select sum(revenue) from event where package_name='com.flamingogames.gok' and event_token='iap_purchased' and 
  created_at_date='{$date_range|from}'
) as total_payment

select visitParamExtractString(params, 'sid') as sid,count() as "Order Numbers", count(distinct roleId) as "Payers",round(sum(revenue), 2) as "Payment", round(total_payment,2) as "Total Revenue", concat(toString(round(sum(revenue) * 100/total_payment, 2)), '%') as "Percent" from event 
where package_name='com.test' and event_token='iap_purchased' and created_at_date='{$date_range|from}'
group by sid

The result table will display fields using the sort_fields in front matter.