Can only track the last connection and DDL activity of students (M)

[smurthys]

We need to be able to track all connection and DDL activities of all users: not just the last of these activities; not just for students.

Adding this feature requires:

• [ ] Detail table(s) to capture activity
• [ ] DDL trigger to insert activity to a detail table
• [ ] Log import to insert activity to a detail table
• [ ] Views/functions to return activities
• [ ] Function(s) to purge the detail table(s)

[wildtayne]

Some thoughts about this:

• A robust solution to this issue will probably intersect with many issues, or at least provide a logical opportunity to integrate their solutions.
• I image two tables could be used, one storing DDL activity, and one storing connections. I made a quick sketch of them here (please let me know if this link doesn't work).
• Any columns related to logging should then be removed from ClassDB.student.
• This would provide an opportunity to merge ClassDB.student and ClassDB.instructor (#49), since the two tables would be virtually identical at that point. The above link also contains a possible User table as a replacement.

• 137 would be indirectly solved by the removal of mentioned columns from ClassDB.student, as @smurthys mentions.

• DDLCount and connectionCount would no longer be calculated by the log import and DDL trigger. Instead, the relevant frequent view(s) would derive them from the Detail tables.

[smurthys]

Thanks for the analysis @srrollo. Yes, in general, it may be good to stop working on issues whose resolutions may be voided by the new tables. Except, Issue #135 should be addressed so as not to interfere with new contributors' activities.

Things to think about in relation to the new tables:

• The User table needs a fullName attribute and userName should probably be renamed loginName or some such
• We need to think carefully about the PK for the detail tables: what do the time stamp fields represent?
• Names of the time stamp fields may need revision
• What are the pros and cons of maintaining two separate detail tables? Would users have to execute two separate queries to get all user activities?

I think @KevinKelly25 and @baconbm will likely have some thoughts on the sketch of the new tables and their connection to views. Specifically, they might be able to draw the ER diagram and generate table schemas.

[KevinKelly25]

I made a quick draft ER table of the above tables with some of the revisions @smurthys mentioned. I also made table schemas for the three tables shown.

CREATE TABLE IF NOT EXISTS DDLActivityDetail
(
  loginName VARCHAR(63) NOT NULL REFERENCES User, --Login name of the user
  activityTimestampAtUTC TIMESTAMP NOT NULL, --UTC date and time of the last DDL operation
  DDLOperation VARCHAR(64), --DDL operation the user performed
  DDLObject VARCHAR(256), --name of the object of the DDL operation
  PRIMARY KEY (loginName, activityTimestampAtUTC)
);

CREATE TABLE IF NOT EXISTS User
(
  loginName VARCHAR(63) NOT NULL PRIMARY KEY, --Login name of the user
  fullName VARCHAR(100) NOT NULL, --user's given name
  userType VARCHAR(20), --user type
  orgissuedID VARCHAR(20) --a school-issued ID
);

CREATE TABLE IF NOT EXISTS ConnectionDetail
(
  loginName VARCHAR(63) NOT NULL REFERENCES User, --Login name of the user
  connectionTimestampAtUTC TIMESTAMP NOT NULL, --UTC date and time of the connection
  PRIMARY KEY (loginName, connectionTimestampAtUTC)
);

I changed the name of the timestamp slightly to differentiate between the two times, as I think they were meant to be different. I left AtUTC at the end of both of them, however are they necessary as they are mentioned directly in the comments after creation?

With theses new tables as @srrollo indicated you can derrive the DDLCount and connectionCount. It might be possible to instead have one table instead of two that is just plainly named Activity details. Then add another attribute with AcitvityType. However, this would make DDLCount and connectionCount queries much more complicated then the simplicity of having two tables.

[smurthys]

@KevinKelly25 has a valid point about maintaining two separate activity tables. We can stay with this approach, but I encourage all to think about the pros and cons of each approach, and especially take into account how the information in those tables may be used.

It is necessary for field names to be self-explanatory regardless of their associated comment: comments are not visible everywhere the attribute is used. They are also necessary to be self-explanatory in query results.

I propose drawing the ERD using draw.io. I also propose adopting the attribution box used in Gradebook's ERD.

A few considerations for the ERD:

• Mark ID attributes clearly. Specifically, do not use parenthesis notation used in the sketches (referenced in earlier comments) unless those attributes are composite attributes
• As much as possible, use the same name that would be used in the relational schema for attributes and entities. For example, do not add spaces to names
• Drop the word "timestamp" from time stamp fields if the name otherwise can indicate a time stamp. For example, use ddlActivityAtUTC and connectionAtUTC (or connectedAtUTC)
• I propose using the names DDLActivity and connectionActivity instead of DDLActivityDetail and connectionDetail.
• Add ddlCount, connectionCount, etc. from the current Student table as derived attributes to the new User entity

The relational schema needs to be generated from ERD using the conversion procedures discussed in Fall 2017 CS205.

I am assigning this issue to myself, @KevinKelly25, and @baconbm because we have the most immediate familiarity with these topics.

@KevinKelly25 and @baconbm: Would one of you please prepare the ERD in draw.io?

@srrollo and @afig: Would one of you please verify if an non-MD file can be added to the docs folder (which we use to create the ClassDB web site)? Ideally, we should add the HTML file with the ERD to the docs folder and reference it from a documentation page. This should be possible, but like to verify first.

[KevinKelly25]

I updated the ERD on Draw.io Here. I left it as a link to the draw.io because I am unsure how to make it into a HTML file and add it to the docs.

I also updated the table schemas to reflect the changes to the ERD, put into a gist here

Edit: I found the html conversion for Draw.IO but I will leave link until the question in last comment to @srrollo and @afig is answered.

[afig]

I have verified that an HTML file can be added to the docs folder and then linked to from one of the other documentation pages. Linking to a .html file works the same way as linking to a .md file. As expected, it does not have any additional styling (such as the chosen GitHub Pages theme) added to it.

If there are both HTML and Markdown versions of a file in the docs folder, then the HTML file takes priority. For example, if test.html and test.md files exist in the docs directory, and another documentation page links to the file like so:

[Link to test page](test)

Then the test.html file is served, rather than the styled test.md file. This behavior cannot be overridden by specifying a file extension. If I had to guess, this is due to a styled test.md page not being created internally by GitHub pages due to a name conflict. For clarity, it might be best to specify a .html extension when linking anyway, so that it is clear that this is an HTML file we created, even if it does not change the above behavior.

However, it is possible to link to other file formats (such a PDF files) by adding a file extension, so

[Diagram - PDF version](diagram.pdf)

would serve a PDF file to the browser, provided that diagram.pdf exists in the docs folder.

[smurthys]

Thanks @afig for the thorough report.

@KevinKelly25 Please check in the HTML version of the ERD to folder docs. I recommend the file name ERSchema.html. This being your first check in, if you have questions, please feel free to ask by e-mail. Just in case you need it:

• Create a branch with a name such as add-ERD
• Commit: be sure to include an appropriate commit message. reference the Issue#; be careful not to say "Fixes" or "Resolves" immediately before the issue# (see here)
• Push the changes
• Create a PR if you wish. If you don't create a PR, add a comment here with a link to the commit (as @srrollo did here)

[KevinKelly25]

I made a pull request for commit Add-ERD branch add a rought draft of ERD to be pulled into master branch. This adds a ERD for this issue as discussed above.

[smurthys]

Thanks @KevinKelly25 for the commit. Comments on it will appear at the PR #139.

[wildtayne]

I propose that this be closed after #150 is merged, since it completes the solution to this issue.

[wildtayne]

Fixed in #150