Description

pesse

opened on Nov 5, 2019

Issue body actions

Is your feature request related to a problem? Please describe.
When using the global DDL trigger approach, there might be situations where the annotation cache is not up to date or otherwise broken.

Describe the solution you'd like
In these cases, a simple-to-call procedure to completely refresh/recreate the annotation cache at will would be very handy.

Activity

1. pesse

   added

   enhancement

   on Nov 5, 2019
2. jgebal commented on Nov 5, 2019

   

   jgebal

   on Nov 5, 2019

   Member

   More actions

   It's already mentioned in install guide.
   Maybe should be rewritten so it can be directly referenced.
   See Note here:
   https://github.com/utPLSQL/utPLSQL/blob/develop/docs/userguide/install.md#installation-with-ddl-trigger
3. pesse commented on Nov 6, 2019

   

   pesse

   on Nov 6, 2019

   MemberAuthor

   More actions

   Ah, thank you!
   Yes, probably a dedicated section for the annotation cache would be good. Will come up with a suggestion.
4. jgebal

   added

   question

   documentation

   enhancement

   and removed

   enhancement

   on Nov 28, 2019
5. hkorsvoll commented on Mar 10, 2020

   

   hkorsvoll

   on Mar 10, 2020

   More actions

   Hi, I am struggling with a related issue here.

   We are starting to use utPLSQL in favour of a homemade test framework that we have been using. Now I am trying to set up TeamCity to trigger nightly runs of the complete test suite with coverage report. I have installed utplsql-cli on the build server, and I am able to initiate test runs from TeamCity.
   We installed utplsql (v 3.1.10) with the DDL trigger approach since we expect a lot of tests to be generated.
   Our database is built up with several schemas that have a lot of dependencies. The tests will be located in several schemas, and we will like to run all tests in all schemas. I am testing with creating one test package in one of the schemas and set up the build server to initiate the test run using another user with appropriate permissions.

   This works sometimes, and sometimes not. Sometimes it runs all tests, and sometimes it doesn't find any tests to run. I have narrowed it down to have something to do with the annotation cache. If I run
   begin utp3.ut_runner.rebuild_annotation_cache('FELLES'); end;
   and runs all the tests from that same session, I am able to trigger all tests from utplsql-cli on the build server.

   I have investigated a bit further and found that initiating tests with the UT3 user is what clears the cache. So it seems like not a good idea to use that user for running tests. (I haven't found this information in the documentation though.)

   Do you have any recommendation on how to set up a system like this? How to ensure that the annotation cache is not cleared? Does the DDL trigger do some magic with the cache? (I have tried to disable it, with no success).

   I have raised an issue in utplsql-cli for adding a switch that forces rebuild of the annotation cache. That would work around the issue, but I would like to trust the cache as it is, by using it in a correct manner.
6. jgebal commented on Mar 10, 2020

   

   jgebal

   on Mar 10, 2020

   Member

   More actions

   @hkorsvoll
   First, thanks for raising this issue.
   If I would be to reproduce this issue, what steps should I take?

   Also, did you give any extra grants to UT3 (UTP3) user to allow for test execution?

   By default, the framework owner doesn't get execute privilege on any other objects than the ones it owns.

   In order to allow for DDL trigger to be disabled in UT3 schema, the UT3 user needs to have administer database trigger privilege.

   This privilege is only granted for the duration of install process and is then revoked.
   The reason for doing this is to prevent other DB users from gaining elevated privilege and creating other DDL triggers in UT3 schema.
   DDL triggers can be used for very malicious purposes and this step is an attempt to prevent any of that from happening.
7. jgebal commented on Mar 10, 2020

   

   jgebal

   on Mar 10, 2020

   Member

   More actions

   To answer your question on how to set-up a system like this, here is what I would recommend:

   • use framework owner only to have framework installed - nothing more
   • have a separate power-user with execute any procedure grant
   • use the power-user to run tests across schemas in a single test-run

   The reason for having execute any procedure privilege is to allow for coverage reporting - it is required by DBMS_PROFILER.

   If you don't care about coverage and will not need to have coverage gathered for your tested code, then the power-user can simply have direct or indirect grants to test-packages.

   Hopefully this helps.
8. jgebal commented on Mar 10, 2020

   

   jgebal

   on Mar 10, 2020

   Member

   More actions

   @hkorsvoll
   Regardless of my above answer, I would still like to address the issue mentioned by you as it is a buggy behavior.
9. jgebal commented on Mar 13, 2020

   

   jgebal

   on Mar 13, 2020

   Member

   More actions

   The issue was solved in 3.1.10 already - closing.
10. jgebal

    closed this as completedon Mar 13, 2020

Issue actions