When browsing through the application, users may want to show field details to capture the timeline changes of the selected attribute.

Need

Although this functionality may resemble the concept of historization, it is computed differently. 


Indeed, those entries are the ones from the source table (SA if basic, SD otherwise), they are the different values that are stored for the different loads for this given record and attribute that the user is checking.


If activating the history tables is not possible, then source tables are the way to find back some kind of historic trail. Although it might depend on the retention policy defined. Because, if one has been set for source records, then only the historical depth defined there can be retrieved in the source tables.


And so, this article will focus on detailing how to better understand how those displayed values in the timeline view are computed.


Summarized Solution

To do so, we will activate loggers to extract the query sent against the database when displaying the timeline view.


Detailed Solution

Catching the specific query behind the timeline view is achieved by activating below loggers in the logging configuration:

<Root level="TRACE">
<Logger level="DEBUG" name="org.springframework.jdbc.core.JdbcTemplate"/>
<Logger level="TRACE" name="org.springframework.jdbc.core.StatementCreatorUtils"/>

Those being quite verbose, to simplify the process of catching the query, it is needed to: 
  • In the app: get ready by going on the form where the timeline will be displayed.
  • In the application builder: clear the pde.
  • In the configuration module: update the loggers and save.
  • Go back to the app:
    • Click on the little "i" that will open the right side panel/
    • Click on the timeline tab
  • Go back in the app builder:
    • Go to the error logs
    • Click in the middle of the table to refresh the logs
    • Export the pde

As an example, for an entity called TEST, it is the entry generated in the pde:
!ENTRY org.apache.servicemix.bundles.spring-jdbc 1 0 2025-03-11 09:51:27.983
!MESSAGE Executing prepared SQL statement [select TEST.BATCH AS BATCH, TEST.CLASS AS CLASS, TEST.CREAT AS CREAT, TEST.LOADI AS LOADI, TEST.PUBID AS PUBID, TEST.SIREN AS SIREN, TEST.SOURC AS SOURC, TEST.SUBMI AS SUBMI, TEST.UPDAT AS UPDAT
from
    (  select S.B_PUBID AS PUBID, S.B_SOURCEID AS SOURC, S.B_LOADID AS LOADI, S.B_CLASSNAME AS CLASS, B.B_SUBMITDATE AS SUBMI, B.B_BATCHID AS BATCH, S.B_CREATOR AS CREAT, S.SIREN AS SIREN, S.B_UPDATOR AS UPDAT, S.B_ERROR_STATUS AS ERRST, B.B_STATUS AS STATU
      from SD_TEST S inner join DL_BATCH B on (S.B_LOADID = B.B_LOADID and B.B_BATCHID is not null and B.B_BATCHID >= 0)
    ) TEST
    left join
    (  select S.B_PUBID AS PUBID, S.B_SOURCEID AS SOURC, S.TEST_ID AS TESID
      from MD_TEST S
    ) TEST1 on (TEST.PUBID = TEST1.PUBID and TEST.SOURC = TEST1.SOURC)
    left join
    (  select S.TEST_ID AS TESID
      from GD_TEST S
    ) TEST2 on (TEST1.TESID = TEST2.TESID)
where (TEST2.TESID = ?) and (TEST.ERRST is null or TEST.ERRST in ('RECYCLED', 'VALID')) and ((TEST.BATCH <= ?) and (TEST.STATU in ('WARNING', 'DONE')))
order by TEST.BATCH DESC, TEST.PUBID ASC, TEST.SOURC ASC

As it can be seen, some conditions rely on parameters that can be found right below this query:
!ENTRY org.apache.servicemix.bundles.spring-jdbc 1 0 2025-03-11 09:51:27.983
!MESSAGE Setting SQL statement parameter value: column index 1, parameter value [4], value class [java.math.BigDecimal], SQL type unknown (Message reported by logger org.springframework.jdbc.core.StatementCreatorUtils, current authenticated user: 'semadmin')

!ENTRY org.apache.servicemix.bundles.spring-jdbc 1 0 2025-03-11 09:51:27.983
!MESSAGE Setting SQL statement parameter value: column index 2, parameter value [38], value class [java.lang.Long], SQL type 2 (Message reported by logger org.springframework.jdbc.core.StatementCreatorUtils, current authenticated user: 'semadmin')

!ENTRY org.apache.servicemix.bundles.spring-jdbc 1 0 2025-03-11 09:51:27.983
!MESSAGE Setting SQL statement parameter value: column index 3, parameter value [50000], value class [java.lang.Long], SQL type 2 (Message reported by logger org.springframework.jdbc.core.StatementCreatorUtils, current authenticated user: 'semadmin')

!ENTRY org.apache.servicemix.bundles.spring-jdbc 1 0 2025-03-11 09:51:27.983
!MESSAGE Setting SQL statement parameter value: column index 4, parameter value [1], value class [java.lang.Long], SQL type 2 (Message reported by logger org.springframework.jdbc.core.StatementCreatorUtils, current authenticated user: 'semadmin')

!ENTRY org.apache.servicemix.bundles.spring-jdbc 1 0 2025-03-11 09:51:27.996
!MESSAGE Setting SQL statement parameter value: column index 5, parameter value [1], value class [java.lang.Long], SQL type 2 (Message reported by logger org.springframework.jdbc.core.StatementCreatorUtils, current authenticated user: 'semadmin')

The parameters are in the order of the query, so they'll need to be replaced accordingly for the query to run properly on the database.


Don't forget to put back the logging configuration as before, as those are quite verbose and are taking resources to run.
A little icon is available in the top-right corner if it is needed to go back to the default configuration.