Optimize availability check for "Run utPLSQL test" menu option

[PhilippSalvisberg]

In the preferences you can configure if you want to check for the availability of the menu option "Run utPLSQL test". When set, the extension enables the menu option only when at least one test has been found. This may be slow for two reasons:

1. because the annotation cache needs to be updated
   this happens only, if you do not use a DDL trigger to track changes, in this case changes are detected delayed, when running a test or when checking for availability of tests

2. large schemas
   there is more data in the annotation cache and in the data dictionary, affecting the runtime performance of the queries, especially when no DDL trigger is in place, due to delayed annotation detection

Since version 3.1.8 the following functions are available in ut_runner:

• has_suites
• is_suite
• is_test

These functions are optimized for performance and are faster than the generic table function get_suites_info. Hence these functions should be used whenever possible by the SQL Developer extension.

In fact these functions are available since utPLSQL version 3.1.3, but since version 3.1.8 they are considered reliable (see also utPLSQL/utPLSQL#976). Hence the use of this functions should be limited to utPLSQL version 3.1.8 and higher.

[jgebal]

@phsalvisberg
I would need to double check but I think it was reliable before.
Only develop (3.1.8-develop) had a bug as I went too far with optimizetions.
Let me check change history to see.

[PhilippSalvisberg]

@jgebal I checked. My JUnit test case fails with 3.1.7. All functions return false instead of true, because they do not update the annotation cache (without using the DDL trigger). I have not tested that with 3.1.8 yet. Maybe this is an issue there as well.

[jgebal]

The functionality was introduced in this change

Since then it wasn't changed until this change and then this change in 3.1.8-develop

I'm no sure why the functions don't work for you in 3.1.7. They should as they should update cache.
Perhaps it's not worth digging in old versions.

If this didn't work for you before 3.1.8, lets leave it active since 3.1.8 only.

[PhilippSalvisberg]

The problem is that has_suite, is_suite and is_test do not check if the annotation cache is up-to-date. This is a problem when you install utPLSQL with install_headless.sql instead of install_headless_with_trigger.sql. Hence this works in the develop environment and in our CI environment where install_headless_with_trigger.sql is used behind the scenes. These functions do not deliver a reliable result. Even in 3.1.8 :-(

[PhilippSalvisberg]

IMHO the functions should check if the DDL trigger is active. If not they have to update the annotation cache first. Similar logic as in get_suites_info.

[PhilippSalvisberg]

If this will not be fixed in 3.1.8 I'll have to undo this change.

[jgebal]

They do, and they always did.
line

    refresh_cache(l_owner_name);

Is responsible for doing the check and cache refresh.

[PhilippSalvisberg]

It's not working.

[jgebal]

I can have a look at this in the evening.

[PhilippSalvisberg]

@jgebal Why have you reopened this issue?

[jgebal]

Because we're discussing it and As you said, you might need to revert the change.
Feel free to close if you want. I can open a different issue on utPLSQL

[PhilippSalvisberg]

Ok, I checked it again.
It works as expected (without trigger) in v3.1.8.
It does not work (without trigger) in v3.1.7. JUnit test fails.
Hence I close this issue.