FWD » Core Development » Database

Support for this VST was mostly implemented in #3814, however a few things remain to be addressed:

• add update counters for field updates made by the BUFFER-COPY statement/method;
• test/confirm temp-table support works and fix it if not;
• test/confirm the retrieve counter is working correctly for AdaptiveQuery when in dynamic mode after #5306 is addressed.

For the #5306-related item, we know the retrieve counter is incorrectly incremented twice when AdaptiveQuery is in dynamic mode. This will be fixed in #5306, not here, but once that is done, we need to test the VST is working correctly.

Regarding the temp-table support, there are cases where conversion optimizes 4GL code which deletes temp-table records in a loop to use a bulk delete operation in the converted result instead. This is an optimization worth preserving, but it means that the VST delete counter will be bypassed. Some re-engineering of this optimization to use the result of a SQL DELETE operation to update the VST delete counter (or some other approach) would be needed to deal with this. If not, this would be a permanent limitation/incompatibility with the 4GL's behavior.

Eric Faulhaber wrote:

Support for this VST was mostly implemented in #3814, however a few things remain to be addressed:

• add update counters for field updates made by the BUFFER-COPY statement/method;
• test/confirm temp-table support works and fix it if not;
• test/confirm the retrieve counter is working correctly for AdaptiveQuery when in dynamic mode after #5306 is addressed.

For the #5306-related item, we know the retrieve counter is incorrectly incremented twice when AdaptiveQuery is in dynamic mode. This will be fixed in #5306, not here, but once that is done, we need to test the VST is working correctly.

Please note that we also have to make sure the counters are updated correctly in the presence of triggers (there are some tricky corner cases such as #3814-400)). This can be done after the issues described in #5289 will be resolved.

We have a project that reads _usertablestat to log the table activity of a session, so one more item needs to be part of this task.

The in-memory statistics are never flushed when the table is queried. In persist/orm/Query.java (around line 481), a query over _usertablestat calls:

UserTableStatUpdater.persist(db);

Here db is the session's database, which for a metadata query is the META database. UserTableStatUpdater instances are cached by the Database they were created for, and Database equality includes its type. The counters are recorded against the PRIMARY database: RecordBuffer calls update/create/retrieve/delete(getDatabase(), ...). So the flush finds a separate, empty instance and writes nothing. The only other flush happens at disconnect (ConnectTableUpdater). As a result, a query over _usertablestat during a session returns no records, and the table looks unimplemented.

Ovidiu found this and gave the fix:

= p2j/src/com/goldencode/p2j/persist/orm/Query.java
-            UserTableStatUpdater.persist(db);
+            UserTableStatUpdater.persist(db.toType(Database.Type.PRIMARY));

Please include a testcase which reads _usertablestat for the current connection before and after creating, reading, updating and deleting records in a table, and checks that the differences are reported within the same session.

I added the unit tests in xfer testcases/1957.

• tests/persistence/schema_features/Test_UserTableStat.cls
• tests/persistence/schema_features/Test_UserTableStatTriggers.cls
• tests/persistence/db_triggers/TestFindTriggersInvalidatedQuery.cls

The current status of the tests is:

└─ FWD Test ✔
   └─ tests.persistence.schema_features.Test_UserTableStatTriggers ✔
      ├─ testTriggersAccepting ✘ Expected: 2 but was: 1
      ├─ testFindRejected ✘ invoke() of program tests.persistence.schema_features.Test_UserTableStatTriggers failed
      ├─ testCreateRejected ✘ invoke() of program tests.persistence.schema_features.Test_UserTableStatTriggers failed
      ├─ testWriteRejected ✘ invoke() of program tests.persistence.schema_features.Test_UserTableStatTriggers failed
      ├─ testDeleteRejected ✘ invoke() of program tests.persistence.schema_features.Test_UserTableStatTriggers failed
      ├─ testGetCurrentUpdateRejected ✘ invoke() of program tests.persistence.schema_features.Test_UserTableStatTriggers failed
      └─ testReadInsideTrigger ✔
└─ FWD Test ✔
   └─ tests.persistence.db_triggers.TestFindTriggersInvalidatedQuery ✔
      ├─ testForEachExclusiveUpdateIndexField ✘ Expected: 3 but was: 5
      ├─ testForEachShareUpdateIndexField ✘ Expected: 3 but was: 5
      ├─ testForEachNoLockUpdateIndexFieldOtherBuffer ✔
      └─ testQueryExclusiveUpdateIndexField ✘ Expected: 3 but was: 5
└─ FWD Test ✔
   └─ tests.persistence.schema_features.Test_UserTableStat ✔
      ├─ testStatsVisibleInSameSession ✔
      ├─ testCreate ✔
      ├─ testRead ✔
      ├─ testUpdate ✔
      ├─ testDelete ✔
      ├─ testForEachRead ✔
      ├─ testQueryRead ✔
      ├─ testForEachUpdate ✔
      ├─ testForEachUpdateIndexField ✘ Expected: 3 but was: 5
      ├─ testBufferCopyStatement ✔
      ├─ testBufferCopyMethod ✔
      ├─ testForEachNoLockUpdateIndexField ✔
      ├─ testUpdateSameValue ✔
      ├─ testGetCurrentDelete ✘ Expected: 2 but was: 1
      ├─ testGetCurrentUpdate ✘ Expected: 2 but was: 1
      ├─ testTempTableNotCounted ✔
      └─ testDeltasAccumulate ✔

testForEachUpdateIndexField test shows a problem where an AdaptiveQuery changes to dynamic mode after the first record and every record is put into the buffer twice (RecordBuffer.setRecord() is called twice and counted into the _usertablestat). TestFindTriggersInvalidatedQuery is an extension of it.

I've let /fwd-testcases:canonicalize-ticket create a set of unit tests for this task (it took close to 2h), is it fine if I commit those to testcases too?

Number 2 issues would require too many changes just to support this type of behavior from OE. The main idea is that FWD always returns the current counters, but OE shows the new counters only when the holding buffer releases it. Simple explanation: OE reports the counter as 0 while a row is held and 1 after release, FWD reports 1 in both cases. I'd like another opinion here.

As for number 1 and 3, those should be separate tasks.