Projects:Enable Read-Only Pool per report/Specs

Contents 1 Functional Requirements 1.1 Overview 1.2 Affected reports 1.3 Specifications 2 Technical Requirements 2.1 Default report pool Preference 2.2 Session Info 2.3 Limitations

Functional Requirements

Overview

Starting from 3.0PR17Q2, a Read-Only Pool (from now RO pool) can be configured to enable a secondary read-only database pool where most reports can fetch their data reducing the main's database overhead.

One downside of using the RO database pool is that they may not be synchronized and the data retrieved is not up-to-date. This behavior is not acceptable in reports where their data is constantly being updated (e.g. Sales reports). It would be better to be able to choose which database pool should be used in a per-report basis.

The goal of this project is to let the admin determine, at a system level (no client/organization individual settings), which reports can fetch their data from the main database pool instead of using the RO pool.

Affected reports

Once the RO pool is enabled, it will be used to generate all standard reports of the application. In addition to that, some manual reports can take advantage of the RO pool. A full list of supported manual reports can be found here.

However, starting from 3.0PR18Q2, documents printed with the standard print toolbar button, are never using the RO pool. Here we assume that Reports referenced in a Tab are used in the Print toolbar button, hence they won't appear in the report selector.

Specifications

Once the RO pool is enabled, all supported reports (see section above) should be using this pool by default. To override this behavior there is a Preference called DefaultDBPoolPerReport which defines the pool that should be used when requesting a RO pool via OBDal.

In addition, this Preference can be overridden in a per report basis using the new Window (General Setup|Application|Data pool selection) where the admin can create new entries for each report which associate a report with a single data pool.

Entries in Data pool selection can be deactivated using the Is active flag. Once deactivated, the pool defined in DefaultDBPoolPerReport will be used.

Administrators can add entries to this Window even if RO pool is not configured yet. In that case, a message should appear to indicate that RO pool is not configured yet and that the referred report is already using the main pool.

Technical Requirements

Default report pool Preference

A new Preference DefaultDBPoolForReports have been created to determine the default behavior when requesting a read-only connection for reports. If not defined, the default value will be to use the read-only pool (RO). The new behavior should be summarized with the following table:

getInstance() parameter	DefaultDBPoolForReports	Instance retrieved
RO	RO	RO
RO	DEFAULT	DEFAULT
DEFAULT	RO	DEFAULT
DEFAULT	DEFAULT	DEFAULT

Session Info

In order to choose the appropriate DB pool for each request, it could be useful to know the current request being executed. We can achieve that using the SessionInfo object. Specifically, we can use the ProcessType and the ProcessId to identify the reports that should not use the RO pool.

Report type	Process ID	Process Type
Report and Process	AD_Process_ID	R
Process Definition	OBUIAPP_Process_ID	PD
Toolbar print	AD_Process_ID	P

Limitations

In order to retrieve a OBDal instance, a query to Preferences and another to DataPoolSelection is required to determine the appropriate instance for the process in execution. In addition, Preferences class is not available when compiling OBDal, so importing this class in OBDal or a class that depends on it leads to a compile error.

For this reason, and to improve performance, both the preference and the values in DataPoolSelection are cached when Tomcat starts. Preference cached value cannot be updated until Tomcat is restarted, but any changes in DataPoolSelection will invalidate their cached values. However, in a cluster environment, a container restart is required in order to synchronize the changes. To summarize the actions required when changing the preferences:

Change made	Single instance	Cluster
Change DefaultDBPoolPerReport	Restart Tomcat	Restart Tomcat
Add/Remove/Modify DataPoolSelection	-	Restart Tomcat

Another limitation found in this project is that CachedPreference cannot be used in this scenario because the Initializers are beyond Weld's session scope, so they cannot instantiate CachedPreference to retrieve and cache the value we need.