Installer NOTICE Suppression and Uninstaller Improvements

[wildtayne]

This branch contains fixes in two areas:

NOTICE Suppression:

• SET LOCAL client_min_messages TO WARNING; was added to the primary transactions in perpareClassServer, prepareClassDB, prepareUserLogging, and metaFunctions
• This should suppress any NOTICE messages during the run of these scripts only. Any functions they create are unaffected
• I removed the notice suppression from addHelperFunctions for now, since it only uses CREATE OR REPLACE. We can add it if necessary.

Uninstaller Improvements:

• All DB level operations have been moved into removeClassDBFromDB
• The only operations performed in removeClassDBFromServer are the DROP ROLES for the ClassDB roles (Fixes #88)
• The schemas for reassignment are now found by getting a list of every Instructor, DBManager, and Student from pg_roles and pg_auth_members. Additionally, a subquery to INFO_SCHEMA.SCHEMATA checks if that schema exists and is in the current database. This should also theoretically work correctly if a user has schemas in multiple dbs. Only the one in the current database should be reassigned. (Fixes #89)
• See discussion here for some additional points about this change

Misc:

• I also changed the CREATE FUNCTION statement in prepareClassServer to CREATE OR REPLACE. I observed that if you run prepareClassServer multiple times in the same session without this, you will get an error because pg_temp is not cleared in-between.

[afig]

We will likely want to rename "Schemas for CS205" in the first line of any Shelter files. Perhaps something like "Example Schemas for ClassDB"

The NOTICE suppression looks good, however, it might also suppress other NOTICEs. Not ones that we explicitly raise, but rather other NOTICEs that PostgreSQL might raise under certain conditions. This does not seem like it would be a major issue, but just something to keep in mind.

Otherwise the changes look good.

[smurthys]

I agree with @afig on 'Schemas for CS205' and take his cue to recommend caution on suppressing notices.

Could a SET LOCAL client_min_messages TO WARNING; be helpful in removeClassDBFromServer.sql?

There is a 'TBD' comment in removeClassDBFromDB.sql leftover from the initial outline of the script.

All see this comment I just made on the change to removeClassDBFromDB.sql.

[wildtayne]

Thanks for the feedback. I do think removeClassDBFromServer.sql could use NOTICE suppression, however, I am starting to think about some of the drawbacks. For example, if somehow ClassDB got into an inconsistent state where, say, two of the four core roles were missing, it would be helpful to get that feedback from the script. I still think it is an OK solution, but past M1 we may want to come up with a nicer one. One idea off the top of my head would be to create our own DROP IF EXISTS function that has nicer output, or keep notices suppressed, but output a summary of how many objects were created/dropped/already existed at the end of the script.

I've pushed some new commits that:

• Add NOTICE suppression to removeClassDBFromServer.sql
• Remove that 'TDB' comment
• Fix the CS205 comment in the shelter files
• Update removeClassDBFromDB.sql as outlined in #89

[smurthys]

I just noticed that removeClassDBFromDB.sql uses the same essential query twice in reassigning schemas. It is better to use just the SELECT query presently inside the EXECUTE. For readability, put the result of that SELECT query into a VARCHAR or TEXT variable and then EXECUTE the variable after testing it for NULL (or unconditionally execute in combination with a COALESCE).

Also, I think the RAISE NOTICE in that area of the code could be RAISE INFO which has a lower priority level and that level is sufficient here.

[wildtayne]

Good catch, I've made the change. I also added the notice suppression to removeClassDBFromDB.sql, as it also has several IF EXISTS statements. Since we have some INFO and NOTICE messages we do want to display, I've slightly reorganized the file:

• First, we do the ALTER SCHEMA statements with client_min_messages at default
• Then we set client_min_messages to WARNING
• Then we do all the DROP IF EXISTS statements
• Then we reset client_min_messages (using RESET client_min_messages)
• Then we display our closing NOTICE messages

I've also ensured that removeClassDBFromServer.sql has the RESET statement so our closing NOTICE is shown.