Data filters

Data filters define SQL conditions on SQL tables, to filter the data rows send to the DB Sync client apps. Data filters can be parameterized, to get user-specific filtered data.

Purpose of data filters

Data filters are defined on SQL tables for a given app by the administrator, in the DB Sync client app console.

An SQL condition can be defined for each SQL table, with parameter placeholders. The values of the filter parameters are the specified for each end user.

When collecting data in the central database for the DB Sync client app, the DB Sync server only selects the data rows matching the data filters for the current user doing the synchronization.

Data filters are defined for SQL tables associated to DB Sync client apps. — Figure: DB Sync data filters

With the foreign key constraints defined in the DB Sync database schema, the DB Sync framework builds a complete SQL query to filter all tables associated to a user/app. For example, with a customer table referencing a city table for the address, which in turn references a country table, if you define a filter on the country table, France for example, the SQL query filter propagates to the city and customer tables for only the cities and customers with an address in France.

Parameter placeholders

Data filters are defined as SQL conditions, and can include placeholders that will be replaced at runtime with parameter values defined for each end user. The data filter parameters must be enclosed between two % characters and must start with the DBSYNC_ prefix.

Placeholders for character string parameters should be specified between single quotes. Placeholders for numeric and date/time values should be specified directly with no delimiters, and the values must follow the format expected by the central database SQL engine.

For example, to filter the data for a given country, you would define the following SQL condition:

(country.cou_name = '%DBSYNC_COUNTRY%')

At runtime, if the DBSYNC_COUNTRY parameter is defined with the value France, the resulting SQL condition will be:

(country.cou_name = 'France')

To filter on a set of countries, you would define the following SQL condition:

(country.cou_name IN (%DBSYNC_COUNTRY_LIST%))

And define DBSYNC_COUNTRY_LIST as 'France','Spain','Italy', to get at runtime:

(country.cou_name IN ('France','Spain','Italy'))

When using DB Sync in direct mode (without GAS/GIP), data filter parameters can be defined for each end user in the DB Sync user console.

When using DB Sync with GAS/GIP, the data filter parameters must be defined as property fields in the GIP user definition form.

Predefined parameter placeholders are replaced with data values known by the DB Sync server. Predefined parameter placeholders have the DBSYNC_PREDEF_ predefix.

List of predefined parameter placeholders:


Predefined parameter placeholder	Data type	Replacement value
`DBSYNC_PREDEF_USER_ID`	`VARCHAR(40)`	The user id issuing the sync request. With GAS/SSO, this is the OIDC SUB.

Testing data filters

Start a DB Sync client app and synchronize the database.
In the command line, navigate to demo/books/version_1 directory and run the following command:
```
fglrun main dbsync_source_gas.json
```

Open the dbsync.html starter page in a browser:

http[s]://host:port/ua/web/dbsync_setup_server_config/dbsync.html

For example:

http://toro:6394/ua/web/dbsync_setup_server_config/dbsync.html

Open the DB Sync Apps Configuration program.
In the bottom-right of the form, define a data filter for one of the SQL tables associated to the app. For example, with the app_library application, the following SQL condition can be specified for the author table to filter on author names:
```
a_name like '%DBSYNC_AUTHOR%'
```
Save the data filter.

You get warned that changing a data filter resets modification timestamps, and force complete data fetch for all DB Sync client apps, when they do a new synchronization.

If the data filter contains parameter placeholders, you must provide sample data, to let the system check the SQL syntax of the data filter.
Use the Verify button to check what data rows are selected by the filter, and see the SQL conditions that will be generated for all related tables.

Open the demos.html starter page in a browser:

http[s]://host:port/demos.html

For example:

http://toro:6394/demos.html

Open the Console application program.
Select Users > Manage users and modify a user row.
Select Properties > Append .
Enter DBSYNC_AUTHOR in the name.
Enter Dan Brown in the Value field.
Click OK and Save the changes.
Return to the DB Sync client app and synchronize the database. Data is now filtered on the author Dan Brown.