Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

uninstall sql script not comprehensive #673

Closed
SpotMeFive opened this Issue May 16, 2018 · 10 comments

Comments

Projects
None yet
4 participants
@SpotMeFive
Copy link

SpotMeFive commented May 16, 2018

We are using TeamCity to install and uninstall utPLSQL each testing cycle in the application owner schema.

As part of our database build, we are checking for modified objects and I noticed we are incurring leftover objects from the framework after the uninstall script is executed.

Can you please advise if this is a bug or intended behavior? I have attached my modified copy of the uninstall.sql which seems to deal with it.

Additionally: We are on version 3.0.4 of the framework but I couldn't find any bug addressed in the versions since. There also seem to be types leftover when actually executing tests (not captured in the recreation steps below).

After uninstall:
SQL> SELECT OBJECT_TYPE,COUNT(*) FROM USER_OBJECTS
2 GROUP BY OBJECT_TYPE;
SEQUENCE 1
TABLE 3
INDEX 3

To recreate:

  1. Run user creation script in /source
  2. install using install.sql
  3. uninstall using uninstall.sql
  4. run statement:
    SQL> SELECT OBJECT_TYPE,COUNT(*) FROM USER_OBJECTS
    2 GROUP BY OBJECT_TYPE;

orig.uninstall.log
uninstall.log
install.log
uninstall.sql.txt

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented May 16, 2018

Can you check against 3.1.1 pls? There was a fix that related to some synonyms and type not dropped.

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented May 16, 2018

Sequence, tables and inexres are left for PLSQL profiler. They should do no harm if they stay and also the installation scrip handles their existence.

@SpotMeFive

This comment has been minimized.

Copy link
Author

SpotMeFive commented May 17, 2018

Thanks for the quick response:

@lwasylow, I'll give that a shot.

@jgebal, I agree there is no harm. But they are installed as part of the framework. If I uninstall the framework, should they remain? If so, to what end? Is this to avoid damaging analysis that potentially already exists in those tables unrelated to utPLSQL? Should we be directing this to a separate store somehow? I imagine this is one of the reasons the documentation suggests installing to a separate schema. How about mentioning that in the documentation if we prefer not modifying the uninstall?

Also, I wanted to thank you, @jgebal, and the community for the v3 re-write (we just upgraded from v2). Head and shoulders above the last version; no offense to @stevenfeuerstein.

@SpotMeFive

This comment has been minimized.

Copy link
Author

SpotMeFive commented May 17, 2018

@lwasylow:
Looks like the script is less inclusive in 3.1.1. Logs attached.

See below:

SQL> SELECT COUNT(*), OBJECT_TYPE FROM USER_OBJECTS GROUP BY OBJECT_TYPE;

COUNT(*) OBJECT_TYPE


     1 SEQUENCE
     3 PACKAGE BODY
     3 PACKAGE
     3 TYPE BODY
     6 TABLE
     3 INDEX
    10 TYPE

7 rows selected.

install.log
uninstall.log

@lwasylow

This comment has been minimized.

Copy link
Member

lwasylow commented May 17, 2018

@SpotMeFive one thing that jumps to my mind. There is a big diffrence in terms of 3.0.4 and 3.1.1 some types etc are removed. Did you do install on clean (empty schema ) or did you do upgrade from existing ?

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented May 17, 2018

Good point @lwasylow

If you do: uninstall, install, test in each run, it would probably be better to switch to: install, test, uninstall.

Also, it would be a good idea to use docker or some kind of DB snap, so you can just have a baseline (with utPLSQL installed) and run your tests without need to reinstall framework.

I would also encourage to install framework into it's own schema (as a shared library).
It seems more lightweight and more robust solution.

@SpotMeFive

This comment has been minimized.

Copy link
Author

SpotMeFive commented May 17, 2018

@lwasylow
Done on a clean system: installed and uninstalled using 3.1.1
SQL> SELECT COUNT(*), OBJECT_TYPE FROM USER_OBJECTS GROUP BY OBJECT_TYPE;

COUNT(*) OBJECT_TYPE


     1 SEQUENCE
     3 PACKAGE BODY
     3 PACKAGE
     3 TYPE BODY
     4 TABLE **--2 less than "upgraded system" report**
     3 INDEX
    10 TYPE

7 rows selected.

@jgebal: I may not have been clear in my first post. After installing the application schema, we install, test, uninstall as you suggest.

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented May 17, 2018

@SpotMeFive - thanks for the kudos on the work we did.
Also, thanks for spotting and reporting the issue.

ACTION NOTE

  • We need to assure that uninstall removes all objects that it should
  • We need to add this as a test to our CI/CD pipeline so we don't fall into regression.
  • We need to add option to remove all sources, including dbms_profiler tables.

@jgebal jgebal added this to the 3.1.2 milestone May 17, 2018

@jgebal jgebal added the bug label May 17, 2018

@pocelka

This comment has been minimized.

Copy link

pocelka commented May 24, 2018

@jgebal:
I think that the best would be to create create a procedure to remove objects in the correct order. This way we could ensure that when new object is added it would be removed even if somebody would it normally forget to add it to the uninstall script. At the end of the uninstall this procedure / package would be dropped as a last step. What are your thoughs?

Was there some historical reason why there is each object named in the uninstall.sql script?

@jgebal

This comment has been minimized.

Copy link
Member

jgebal commented Jun 4, 2018

I think the best way to protect against a missing element in the uninstall script is by adding the uninstall as part of deployment pipeline and validating that all items were removed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.