Move Student Ops to DB

[cinnaco]

Introduction

This pull request implements functions which allow students and teachers to enroll in classes using LearnSQL.joinClass() and return a list of currently enrolled classes using LearnSQL.getClasses(). A series of testing routines have also been included to measure the success statuses of each function upon execution.

Function Usage

• LearnSQL.joinClass()

  1. When this function is called by a student or teacher, the user is able to enroll in a class using a class password, which was determined upon creation of the class using LearnSQL.createClass().
  2. When this function is called by an administrator providing their credentials as additional parameters, they are able to forcibly enroll users into a specified class. Non-administrator accounts should not be able to add other users to classes.

• LearnSQL.getClasses()

  1. This function returns a table of the currently enrolled classes of a user, provided a username. Administrators, teachers and students are permitted to call this function. If the username supplied is not found or is not enrolled in any class, an exception is raised and no table is returned.

Test Script

• This PR attempts to evolve the method in which the success statuses of test scripts are reported. This partially came out of necessity due to dblink() functions not working properly inside of functions, within a transaction, under certain circumstances. Previously, the process was to simply report error codes upon a failure and refer to documentation to map the code with the explanation of the error. The new process involves a table created in the pg_temp schema which stores information about the event time, function name, success boolean, and an error description. As functions are called, certain conditions are checked and the result of these checks are inserted into the table. Right before the script finishes execution, the entire contents of the table is displayed to inform the user of the results of the test. Every time, testStudentMgmt.sql is run, the pg_temp.ErrorLog table is truncated so only the results of the most recent test are stored. Overall, this approach increases the readability of the test results and decreases the time involved to correct errors.

Other Notes

• This PR is intended to operate with the current dev branch of the Modified_ClassDB repository.
• Drop and recreate your LearnSQL database, then reinitialize it using the addAllToDBAndServer.psql script.
• Run the script, testStudentMgmt.sql while connected to the LearnSQL database
• This PR fixes Issue: #81

[KevinKelly25]

Thank you for the PR, everything works for me. However, I see a few small issues that need to be fixed before approval.

• ln 30 of studentMgmt.sql is unneeded as dblink was already added in userMgmt.sql
• Please stick to the already pre-defined comment structure of the rest of the code. So

  
  /*

• Function returns a table listing the user's currently
• enrolled classes. Returns an error if the user is not
• a member of any class. */

  should be
  ```sql
  --  Function returns a table listing the user's currently
  --   enrolled classes. Returns an error if the user is not
  --   a member of any class.

While this style may or may not be the better style we should leave changes like this to a separate PR where we change all of our comments to this style at once. Of course this should be after we discuss this change. If you think this is something we should do please bring it up in our next meeting.

• There is many cases where there is unneeded line spaces such as ln 43 of studentMgmt.sql
• For get classes it should also return ClassID. Even though most, if not all, current cases we will not need that info there may be a need for it in the future. I actually believe there is going to be a need for it in the upcoming workshop section.
• ln 123 of studentMgmt.sql the query has an unneeded space at the beginning
• lns 132 to 136 is unneeded as the password is already being checked with the dblink in the lines above it. If the password was wrong the dblink would fail. We are also planning to get rid of the password attribute in the userdata_t table so we should avoid adding dependencies to it.
• ln 138 of studentMgmt.sql I believe the extra parentheses is unneeded
• ln 179 of studentMgmt.sql can we use dblink_exec instead since we are not using any of the returned details?
• ln92 of testStudentMgmt.sql may cause issues if there is an actaul user that names there class cs305.
• on ln621 of testStudentMgmt.sql is there a reason you commit there instead of doing a rollback after all the drops? I know there might be because of the dblink issues just unsure. Should make a comment on file if so stating why.

[michaeltorres1]

Thanks for the PR @cinnaco. I tested out the PR and everything works as expected with the exception that @kevinKelly25 mentioned that dblink was already added in userMgmt.sql but is not needed.

I also encountered the same issues that @kevinkelly25 mentioned in his review and in addition found some other errors which are listed below:

• L 44 you should be left aligning RETURN as to be consistent with the rest of the project. This issue appears in several places in both studentMgmt.sql and testStudentMgmt.sql.
• L 42 there seems to be an indentation with too many spaces. Instead of indenting with 4 spaces please use two spaces as to keep consistent with the rest of the project. This issue appears in both and in several places in studentMgmt.sql and testStudentMgmt.sql.
• L 59 please indent the IF statement with two spaces. It currently is completely left aligned.
• Througout both studentMgmt.sql and testStudentMgmt.sql the IF statement does not line up with the THEN. In order to keep consistent within our project please line these up as it is done in the other files.
• L 88 and 89 there are comments that go over the max length of characters 80 according to ESLint documentation. This is just the traditional max amount of characters we should have per line. Please do your best to keep to this standard.
• L 123 the code goes over the amount of characters per line please see the comment just before this one for details.
• In some comments you include periods as the end of your sentences and in others you do not.
• I do have the same concern as @kevinkelly25 has as to why you are using COMMIT in your testStudentMgmt.sql however, I did test it out myself and noticed that without it, it does not work. I would clarify as to why the COMMIT is not there.
• the LearnSQL schema qualifier name does not need to be added in many places in your code. An example of this is in Lines 79 and 80 in studentMgmt.sql. Please check for all occurrences like this.

We should speak as a team about what type of format we should be using for many of the things I mentioned as well as what @KevinKelly25 mentioned as well. And as @KevinKelly25 mentioned please stick to the way we have it formatted now until we have an agreed format to stick to.

With these issues fixed I do believe your PR will be ready for approval.

[cinnaco]

Thanks for the suggestions @KevinKelly25 and @michaeltorres1. Here are the following changes I have implemented based on your feedback:

• Fixed code style including comments, alignment, whitespace and line length

• The pg_temp.dropTempDBUser() function now checks for specific names instead of using wildcards.

• LearnSQL.getClasses() now returns a classID for each class enrolled

The following items I have not changed based on the following reasoning:

• The password parameter used in the dblink() within the LearnSQL.joinClass() function is supplied from the database user, not the administrator. Therefore dblink() failing does not imply that the administrator's username and password were incorrect. Maybe you confused the variable names because they both begin with checkAdmin...?

• The addStudentQuery cross-DB query in LearnSQL.joinClass() is unable to use dblink_exec(). Even though ClassDB.createStudent() returns VOID, the function fails to work correctly when using dblink_exec() and gives an error stating that the return value has nowhere to be stored.

• The COMMIT in the testStudentMgmt.sql script is present so the function calls following it perform as expected. If a ROLLBACK was issued instead, the SELECT statements that follow would be calling functions which would not exist anymore.

[KevinKelly25]

Thank you for the update @cinnaco thank you for exploring that functionality of dblink_exec(). I should have realized that it would fail since you are using select.

I am still seeing many extra lines that are unneeded such as lines 107 and 113 in studentMgmt.sql

There is a user password in the parameter in joinClass() for no reason as it is unused in the function

Concerning the password issue mentioned in the last comment I believe I explained it wrong/misunderstood it at first. I believe you are counting on the password supplied to be already hashed with the bf cipher. I believe this is the wrong approach since for our other functions we do the hash within the function rather then counting on the webapp to do it for us.

However, I do not believe we need to send the admin password in order to get the DB function to work. From the webapps perspective it is only going to be supplied the class and student information. If coming from an admin route it should attach the current users name as the optional parameter. Since the username is being supplied by the webapp I believe it would be safe to just check if the supplied username is an admin without checking password

Using ClassDB.isMember is unnecessary because the user can be checked to be in role classdb_admin in the current database since the roles are the same for the whole server. This would be preferable then to do a cross database query. You should be able to use the same methodology of ClassDB.isMember to get this to work.

As mentioned above for the user password the class join password should be encrypted within the function rather then expecting it to be supplied encrypted.

I do not believe the if statement in line 161 is needed as if the password is null it would fail the in line 168.

The schema qualifiers for the query that starts on line 67 in studentMgmt.sql are unneeded from everywhere except in FROM LearnSQL.Attends INNER JOIN LearnSQL.Class

Also, for the getClasses function I believe you should add an extra parameter to the function, isTeacher which is by default false. Both the teacher and student needs the get Classes function so it makes sense to combine them into a single function since the only difference is the single boolean isTeacher.

[cinnaco]

Thanks again for the feedback. I have made the necessary corrections and additions you mentioned.

[michaeltorres1]

Thank you @cinnaco for the changes you have made to this PR.

• The comment on line 34 should be updated to explain how it can return either the student classes or teacher classes.
• The adminPassword is no longer included so you need to update you comment on line 125 to not include adminPassword.
• On Line 145 and line 195 in studentMgmt.sql the colon in := is not needed.

[KevinKelly25]

@michaeltorres1 's comments seemed to cover most of the issues I saw. I only saw one more which is that user's full name for join class should not be a required parameter for the join class function. Instead it should be retrieved from the userdata_t table.

You are also declaring checkAdminQuery but never using it. Also is setting the isAdmin to false necessary since you are inserting into it before using it?

[cinnaco]

I have made the changes you guys have recommended. I see most of the suggestions made were fragments of code/comments which were missed based on revisions from previous commits since the PR was posted. Since my PR has challenged many stylistic concerns, at some point we should review and agree on a set standard so that such issues will not occur in the future.