Expertiza_Wiki - User contributions [en]

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-17T06:38:16Z

Agupta38: /* Results */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.

[[File:ParseComments_def.jpg]]

Segregation of parsing user comments functionality into above method of one of the most important and critical tasks of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored (Trimmed) by above new method

[[File:DiffParseComments.jpg|1100px]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.

[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

Segregation of this parsing answer query into <code>getanswerQuery</code> is of one of the most important task of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored(Trimmed) by above new method

[[File:DiffGetAnswerquery.jpg|1100px]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

We have made video demonstration of our project but we have made a small gif to give brief idea of how parsing and evaluation is being done.
So once you run the application, it start parsing the student answer files. Once parsed, then it moves to evaluation state. After evaluation is done, User/ Instructor can see the grade summary in grade_summary file generated.

[[File:Gif.gif]]

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - https://www.youtube.com/watch?v=cNIVvMuNcOc

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-17T06:35:39Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.

[[File:ParseComments_def.jpg]]

Segregation of parsing user comments functionality into above method of one of the most important and critical tasks of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored (Trimmed) by above new method

[[File:DiffParseComments.jpg|1100px]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.

[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

Segregation of this parsing answer query into <code>getanswerQuery</code> is of one of the most important task of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored(Trimmed) by above new method

[[File:DiffGetAnswerquery.jpg|1100px]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

We have made video demonstration of our project but we have made a small gif to give brief idea of how parsing and evaluation is being done.

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - https://www.youtube.com/watch?v=cNIVvMuNcOc

File:Gif.gif

2020-11-17T06:32:34Z

Agupta38: Agupta38 uploaded a new version of File:Gif.gif

File:Gif.gif

2020-11-17T06:30:42Z

Agupta38:

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-17T06:29:48Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.

[[File:ParseComments_def.jpg]]

Segregation of parsing user comments functionality into above method of one of the most important and critical tasks of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored (Trimmed) by above new method

[[File:DiffParseComments.jpg|1100px]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.

[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

Segregation of this parsing answer query into <code>getanswerQuery</code> is of one of the most important task of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored(Trimmed) by above new method

[[File:DiffGetAnswerquery.jpg|1100px]]

[[File:Gif.gif]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

We have made video demonstration of our project but we have made a small gif to give brief idea of how parsing and evaluation is being done.

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - https://www.youtube.com/watch?v=cNIVvMuNcOc

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-17T06:28:37Z

Agupta38: /* Results */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.

[[File:ParseComments_def.jpg]]

Segregation of parsing user comments functionality into above method of one of the most important and critical tasks of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored (Trimmed) by above new method

[[File:DiffParseComments.jpg|1100px]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.

[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

Segregation of this parsing answer query into <code>getanswerQuery</code> is of one of the most important task of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored(Trimmed) by above new method

[[File:DiffGetAnswerquery.jpg|1100px]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

We have made video demonstration of our project but we have made a small gif to give brief idea of how parsing and evaluation is being done.

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - https://www.youtube.com/watch?v=cNIVvMuNcOc

File:DiffGetAnswerquery.jpg

2020-11-16T07:28:16Z

Agupta38: Agupta38 uploaded a new version of File:DiffGetAnswerquery.jpg

File:DiffGetAnswerquery.jpg

2020-11-16T07:27:52Z

Agupta38: Agupta38 uploaded a new version of File:DiffGetAnswerquery.jpg

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T07:25:16Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.

[[File:ParseComments_def.jpg]]

Segregation of parsing user comments functionality into above method of one of the most important and critical tasks of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored (Trimmed) by above new method

[[File:DiffParseComments.jpg|1100px]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.

[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

Segregation of this parsing answer query into <code>getanswerQuery</code> is of one of the most important task of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored(Trimmed) by above new method

[[File:DiffGetAnswerquery.jpg|1100px]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T07:19:46Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.
[[File:ParseComments_def.jpg]]

[[File:DiffParseComments.jpg|1100px]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.
[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

Segregation of this functionality into above method of one of the most important and critical tasks of refactoring. Diff Comparison in figure shown below highlights how some of the code from the original single monolith function/method <code>readSubmission</code> was refactored (Trimmed) by above new method

[[File:DiffGetAnswerquery.jpg|1100px]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T07:15:44Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.
[[File:ParseComments_def.jpg]]

[[File:DiffParseComments.jpg|1100px]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.
[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]
[[File:DiffGetAnswerquery.jpg|1100px]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T07:14:57Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.
[[File:ParseComments_def.jpg]]

[[File:DiffParseComments.jpg]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.
[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]
[[File:DiffGetAnswerquery.jpg|1100px]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

File:DiffParseComments.jpg

2020-11-16T07:14:31Z

Agupta38:

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T07:11:18Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

[[File:DiffParseComments_def.jpg]|1100px]]

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.
[[File:ParseComments_def.jpg]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.
[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]
[[File:DiffGetAnswerquery.jpg|1100px]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T06:10:23Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.
[[File:ParseComments_def.jpg]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.
[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]
[[File:DiffGetAnswerquery.jpg|1100px]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

File:DiffGetAnswerquery.jpg

2020-11-16T06:00:03Z

Agupta38: Agupta38 uploaded a new version of File:DiffGetAnswerquery.jpg

File:DiffGetAnswerquery.jpg

2020-11-16T06:00:00Z

Agupta38: Agupta38 uploaded a new version of File:DiffGetAnswerquery.jpg

File:DiffGetAnswerquery.jpg

2020-11-16T05:59:26Z

Agupta38:

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T05:59:06Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.
[[File:ParseComments_def.jpg]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.
[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

[[File:DiffGetAnswerquery.jpg]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T05:58:10Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.
[[File:ParseComments_def.jpg]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.
[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

[[File:Diff.jpg]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T05:57:12Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.
[[File:ParseComments_def.jpg]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.
[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

[[File:GetAnswerquery_3.jpg]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

File:GetAnswerquery 3.jpg

2020-11-16T05:48:32Z

Agupta38: Agupta38 uploaded a new version of File:GetAnswerquery 3.jpg

File:GetAnswerquery 3.jpg

2020-11-16T05:46:50Z

Agupta38:

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-11-16T05:45:47Z

Agupta38: /* Code updates */

=='''Introduction'''==
The SQLFE (SQL File Evaluation) open source software system automatically grades a set of files containing SQL queries. This project, written in Java, focuses on redesigning and rewriting one large method that parses one student submission file and builds a data structure for later evaluation.

===About SQLFE===
: SQLFE is an open source software tool to assist computer science instructors by automatically grading assignments or tests involving multiple submitted files consisting of SQL queries added to a template submission file. Developed by Paul Wagner and others at the University of Wisconsin – Eau Claire, SQLFE allows instructors to specify a weighted set of tests for each assignment question, which are then used to evaluate a submitted query and/or compare that submitted query against an instructor-supplied solution query.
:SQLFE also generates a detailed output file for each submission, which can be returned as feedback. SQLFE reduces instructor grading time for SQL queries and allows quicker, more detailed feedback as compared to hand grading. SQLFE source code, sample submission and instructor-generated files, and documentation can be found at https://github.com/wagnerpj42/SQL-File-Evaluation .
: SQLFE is written entirely in Java. Since SQLFE executes SQL queries as part of its evaluation, it must be connected to an SQL database management system (DBMS). Currently Oracle and MySQL DBMSs are supported.

:Here is what the home page of SQLFE looks like:
:[[File:sqlfe1.png]]
:On the left side are the settings to connect to a DB server of your choice, either Oracle or MySQL. The right side menu is used to select which student submissions to read.

===Team Members===
: Mentor: Dr. Paul Wagner
# Jack Maccdonald (jmmacdo4)
# Nick Garner (nrgarner)
# Sumitosh Pal (spal3)
# Abhishek Gupta (agupta38)

===Problem Statement===
Submission.java is the main file in SQLFE responsible for parsing student submissions. As the capabilities of SQLFE have increased, so has the complexity and responsibilities of the readSubmission method. At over 200 LoC, this method has become bloated and cumbersome. The challenge for this team is to refactor readSubmission and Submission.java to maintain proper functionality with proper code structure that will lend itself to future development. 

# Restructure and rewrite the readSubmission() method in the Submission.java class, adding any new sub-methods desired, to accurately and efficiently parse any student submission file that follows the assignment instructions.
#* Each question number and submitted SQL query for that question should be placed in a new QuestionAnswer object in the class-level answers arraylist variable.
#* Any user SQL comments (either -- single line SQL comments or /* */ multi-line SQL comments) should be written out to the AAA_student_comments.out file through the commWriter object. Each user SQL comment line may be written out separately, though if possible it would be fine to write out any user SQL comment as a whole, especially for multi-line /* */ comments.
#* Any parsing problems that cannot be handled should be identified and written out to the AAA_parse_problems.out file through the parseWriter object.
# Optionally use utility methods in the Utilities.java class without modifing or removing any existing methods in Utilities.java as they are used elsewhere. Any new or modified methods should be given new method names if they remain in the Utilities.java class file.
# Place sub-methods created as part of the new readSubmission() method functionality in either Submission.java or Utilities.java as design logic dictates.
# Good functionality and good code commenting is expected.
#* Optionally provide suggestions for improving the Submission class overall.
#* Report any issues or possible improvements in working on this project by communicating them to Paul Wagner


===General Approach===
* '''Make use of the BufferedReader class to parse input files on a per-character basis.''' This will allow us to detect things like comment syntax, improper formatting of solutions, and many other criteria to control the parsing exactly as desired by the project mentor.
* '''Use a Finite State Machine to build submissions.''' One of the challenges with any parsing method is the variety of input available that can create tricky edge cases. We intend to help control this with a well defined finite state machine to keep track of what sort of text we are currently parsing. For example, if we parse the opening to a comment, we will treat all subsequent text as a comment until we parse a comment close. This will also allow us to define discrete transition methods and states to drastically reduce the current readSubmission method's code complexity.
* '''Abstract commonly used code to helper methods in Utility.java.''' We will be looking closely for opportunities to abstract out commonly used to code to helper functions. These can be used in conjunction with the aforementioned boolean flags to help parse large chunks of input depending on surrounding syntax.
* '''Create unit and user tests for Submission.java.''' Currently, only the classes dealing with grading have attached unit tests. We will be creating unit tests as well as user tests through the UI to help ensure that our refactor of readSubmission is working properly. More details below in Test Plan.

=='''Rationale'''==
Most of the requirements from Dr. Wagner are related to making the readSubmission() method in Submission.java modular i.e refactor/split the function accordingly to meet standard function requirements. These can involve using helper functions from Utilities.java as well. Secondary requirements are handling several scenarios in the submission document that might appear due to students’ mistakes or other factors.

===Implementation Strategy===
Initially, we would fork the repository and create another branch where we will commit our changes. Dr Wagner has agreed to add us team members as collaborators for open source SQLFE, so we can create a branch and add changes to it.
We also intend to add unit tests using JUnit4. After all the changes have been reviewed we would merge the changes back to main/master.

===Changes introduced===
# The readSumissions function contains 6 new functions defined within Submission.java and 3 new functions defined within Utilities.java. The function now follows the SOLID and DRY principles which are achieved by doing the following.
## Extracting lines of code into the methods getFileMetadata and reachFirstQuestion to make them adhere to single responsibility.
## Extracting the regex code that makes comparison for a new question in method Utilities.isQuestionFound and achieving interface segregation and DRY.
# The method skipExtraQueries and getQuestionNumber are added in Utilities.java to parsing anomalies in file such as extra statements before first question and add pattern support for special characters('.' and ')') before a question.
# The methods getAnswerQuery and ParseComments replace the lines of code which optimize file reading using buffered reader, improving performance and also improving code readability.
# The file SubmissionTests.java is added in JUnit folder to test the new readsubmissions method and provides a 100 percent coverage.
# Travis CI is created for the original open source repo for providing feature to run build and provide coverage whenever a commit is pushed into the repository.

=='''Implementation'''==
===Flowchart===
The diagram below shows the overall flow of the SQLFE java application from starting the application to closing the window. The part highlighted in red contains the section that our project will focus upon primarily.
[[File:FlowchartSQLFE.jpg]]

===Finite State Machine===
The diagram below shows our plan for controlling the flow of parsing in readSubmission. We will be using states with controlled transitions to track where we are in the submission and to build valid QuestionAnswer objects to interact with the grading utility.
[[File:SQLFEFSM.png]]

===Code updates===
The main focus of our refactoring is <code>Submission.java</code>. The file changes are given below and the entire diff is available in the pull request added in references.

'''readSubmission method Changes'''

1. The general changes include removing usage of several boolean flags and declaring all called variables at the start of the function.
[[File:GeneralChanges.jpg]]

2. These changes are added to encapsulate the part of readSubmissions that gets the file headers present at the beginning of the line and also parses everything before reaching the first question.
[[File:Metadata.jpg]]

3. The method isQuestionFound is used to replace redeclaration of pattern and matcher. The definition is added in <code>Utilities.java</code> and details are added below in this document.
[[File:find_replace_questionFound.jpg]]

4. The method getQuestionNumber replaces the code that was used to store the question number in variable <code>qNumStr</code>.
[[File:QuestionNum_used.jpg]]

5. The methods below replace the lines of code that parse the comments to get the question and answer in the file and store it in <code>QuestionAnswer</code> arrayList. Also the method reachNewQuestion replaces the code that finds the next question.

[[File:ParseComments.jpg]]


'''Method Definitions Implemented'''

<code>parseComments</code> - The method definition to parse all comments using the BufferedReader instance before reaching the AnswerQuery.
[[File:ParseComments_def.jpg]]

<code>getanswerQuery</code> - The method definition to get AnswerQuery from the line and return the answer as a string.
[[File:GetAnswerquery_1.jpg]]
[[File:GetAnswerquery_2.jpg]]

[[File:GetAnswerquery_3.jpg]]

<code>getFileMetadata</code> The method definition that abstracts out the code to get the file name and the student's name.
[[File:metadata_def.jpg]]

<code>reachFirstQuestion</code> The method definition that abstracts out the code that parses file to reach the first question.
[[File:reachFirstqn.jpg]]

<code>reachNextQuestion</code> The method definition that is abstracting out the code that parses file for comments before reaching the next question.
[[File:reachNextQn.jpg]]

<code>writeToQuestionAnswerList</code> The method definition that is abstracting out the code that writes the Question and Answer pair in the QuestionAnswer arraylist.

[[File:questionAnswer.jpg]]

The secondary file that is refactored is <code>Utilities.java</code>. The file changes are below:

<code>getQuestionNumber</code> The method is implemented to read the line and extract the string that marks the question number using regex pattern matching.
[[File:questionnumber.jpg]]

<code>isQuestionFound</code> The method that is used to extract declaration of pattern match statements. This helps avoid the use of regex in the file and provides encapsulation like other helper functions.

[[File:isquestionFound_def.jpg]]

<code>skipExtraQueries</code> The method is implemented to remove any extra queries or anomalies in the file such as incompatible format in instructor comment. This works by parsing the line and matching the regex for instructor comment and not saving any information that does not match the regex.

[[File:skipqueries_def.jpg]]

=='''Test Plan'''==
Currently, our main focus with testing is the creation of unit and user tests for the Submission.java class. This will involve creating test submission files with edge case text in them to try and break the parser.

Examples include:
* Student answers inline with instructor comments
* Student comments written with instructor comment syntax
* Student answers with no terminating semicolon
* Student answers with inline comments
* Student comments with no termination
* Non-ASCII characters
* Submissions with question numbering different from the assignment

Here are some simple unit test examples. The first tests a simple multi line join command. The second one makes sure we pick up a more complicated command with different indentation and a nested cross join.

QuestionAnswer a = answers.get(0);
assertEquals("1", a.getQNumStr());

String qString = "SELECT CustID, FName, LName, AccClosedDate\n" +
"FROM Customer C\n" +
"JOIN Account A ON (C.CustID = A.Customer)\n" +
"WHERE A.AccOpenLocation = 'Central' AND A.AccClosedDate >= '01-MAR-2017'";
assertEquals(qString, a.getActualQuery().toString());
//Test cross join

a = answers.get(5);
qString = "SELECT CustID, FName, LName\n" +
"FROM Customer\n" +
"WHERE CustID IN(\n" +
" SELECT CustID\n" +
" FROM Customer C1\n" +
" CROSS JOIN Account A1\n" +
" MINUS\n" +
" SELECT CustID\n" +
" FROM Customer C2\n" +
" JOIN Account A2 ON (C2.CustID = A2.Customer)\n" +
" WHERE AccStatus = 'Active')";
assertEquals(qString, a.getActualQuery().toString());

In addition, we will be devising a series of blackbox user tests to ensure that our updates to readSubmission do not break any functionality in the UI. This will involve users attempting use cases such as creating an assignment, grading a single submission, and grading a batch of submissions.

Beyond testing, we will be implementing some repository features to help improve the project’s accessibility for Open Source work. This includes setting up continuous integration features like TravisCI and Coveralls, to ensure that proposed changes do not break the build or significantly lower code coverage.

=='''Results'''==

===Impact===
#Refactored readSubmission from 154 LoC to 43 LoC.
#Abstracted commonly used behavior to maintain DRY principle by creating 7 methods which improves code maintainability.
#Created 2 methods to solve issue in parsing files with unusual occourences and increasing robustness of application.
#Improved coverage of method readSubmission to 100%.

===Added Features===
Continuous integration: TravisCI and Coveralls run unit tests on every commit to maintain code quality.

===Proposed Future Scope===
# Improve support for Travis CI and integrate with the main repository.
# Replace hard coded character matching with general regex matching for all methods in Utilites.java like skipInstructorComments, processUserComments, etc.
# Include option to perform JUnit testing with MySQL DB connnector which is currently exclusive for Oracle DB.
# Migrate the code to support the latest eclipse IDE and drivers and make JavaScript replacement for Nashorn Engine.

=='''References'''==
# Repository of the main project - https://github.com/wagnerpj42/SQL-File-Evaluation
# Project Board - https://github.com/nickrgarner/SQL-File-Evaluation/projects/1
# Youtube link of the screencast - TBD

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T02:12:30Z

Agupta38: /* Refractory of File */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T02:11:51Z

Agupta38: /* Refractory of File */

File:Submission.jpg

2020-10-29T02:11:28Z

Agupta38: Agupta38 uploaded a new version of File:Submission.jpg

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T02:09:07Z

Agupta38: /* Refractory of File */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T02:08:14Z

Agupta38: /* Refractory of File */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T02:05:38Z

Agupta38: /* Reference */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T02:05:26Z

Agupta38: /* Reference */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T02:04:54Z

Agupta38: /* Refractory of File */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T02:04:10Z

Agupta38: /* Proposed Future Scope */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T02:03:05Z

Agupta38: /* Refractory of File */

File:Diff.jpg

2020-10-29T01:45:53Z

Agupta38:

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T01:45:22Z

Agupta38: /* Refractory of File */

File:ReadSubmission.jpg

2020-10-29T01:43:30Z

Agupta38: Agupta38 uploaded a new version of File:ReadSubmission.jpg

File:ReadSubmission.jpg

2020-10-29T01:42:03Z

Agupta38:

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T01:02:18Z

Agupta38: /* Refractory of File */

File:Submission.jpg

2020-10-29T00:59:17Z

Agupta38: Agupta38 uploaded a new version of File:Submission.jpg

File:Submission.jpg

2020-10-29T00:58:32Z

Agupta38: Agupta38 uploaded a new version of File:Submission.jpg

File:Submission.jpg

2020-10-29T00:57:39Z

Agupta38:

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T00:57:18Z

Agupta38: /* Refractory of File */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T00:56:33Z

Agupta38: /* Refractory of File */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T00:53:38Z

Agupta38: /* Refractory of File */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T00:53:09Z

Agupta38: /* Refractory of File */

CSC/ECE 517 Fall 2020 - SQLFE. Refactor Submission.java

2020-10-29T00:38:31Z

Agupta38: /* Finite State Machine */

CSC/ECE 517 Fall 2020 - E2064. Refactor reputation web service controller.rb

2020-10-20T03:08:12Z

Agupta38: /* Refactor send_post_request */

=='''Introduction'''==
==='''Introduction to Expertiza'''===
[http://expertiza.ncsu.edu/ Expertiza] is a peer review based system which provides incremental learning from the class. This project has been developed together by faculty and students using [http://rubyonrails.org/ Ruby on Rails] framework. Expertiza allows the instructor to create, edit and delete assignments, create new assignment topics, assign them to a particular class or selected students, have students work on teams and then review each other's assignments at the end. For the students, they can signup for topics, form teams, and submit their projects and assignments.
Students then review the work done by other students and give suggestions to improve. Teams after reviews are allotted scores and they can refer to the peer comments to further improve their work. It also supports submission of different file types for assignments, including the URLs and wiki pages.
==='''About Reputation Web Service Controller'''===
Expertiza allows student work to be peer-reviewed, since peers can provide more feedback than the instructor can. However, if we want to assure that all students receive competent feedback, or even use peer-assigned grades, we need a way to judge which peer reviewers are most credible. The solution is the reputation system. Reputation systems have been deployed as web services, peer-review researchers will be able to use them to calculate scores on assignments, both past and present (past data can be used to tune the algorithms).
For this project, our team's job is to refactor the file: reputation_web_service_controller.rb. This file is the controller to calculate the reputation scores. A “reputation” measures how close a reviewer’s scores are to other reviewers’ scores. This controller implements the calculation of reputation scores.
----

=='''Issues to be fixed'''==

reputation_web_service_controller.rb is a fairly complex file. It contains a lot of methods that are long and hard to understand (for e.g. send_post_request). These methods need to be broken down into simpler and more specific methods that are easier to read/understand. Also, the few instances of code duplication that exist should be removed.

# There is a lot of unused/commented code, which should be removed.
# Figure out what the code is doing and write appropriate comments for it.
# Rename bad method names such as (db_query, json_generator). Method names should be verbs, and should say what the method does. Method names should not be so general that they could apply to many different methods..
# In the case of db_query, the name should say what it queries for. Also, this method not only queries, but calculates sums. Since each method should do only one thing, the code for calculating sums should be in another method. And there should be comments in the code!
#json_generator should be generate_json. There needs to be a method comment saying what the parameters are.
# In send_post_request, there are references to specific assignments, such as 724, 735, and 756. They were put in to gather data for a paper published in 2015. They are no longer relevant and should be removed. send_post_request is 91 lines long, far too long.
#There is a password for a private key in the code (and the code is open-sourced!) It should be in the db instead.
#Fix spelling of “dimention”
#client is a bad method name; why is stuff being copied from class variables to instance variables?

----

=='''Steps taken to resolve the issues'''==

----

=== Refactor db_query===

Renaming of bad method names such as (<code>db_query</code>). Method names should be verbs, and should say what the method does.
Method names should not be so general that they could apply to many different methods.
The other problem with the method was that it did not follow the good practice of having each method perform only one task.
This method got the review responses with a db query.
Also, it iterated over the review responses to perform a calculation of peer review grades.

We have split the method into two to have the review responses do only the get responses part.

[[File:snip7.png]]

 

=== The code for calculating sums should be in another method. ===
The result of this method is stored and we have created a new method <code>calculate_peer_review_grades</code> and moved the calculation part to it. 
We also added a comment to indicate the calculate weighted sum part.

 
[[File:snip8.png]]

 
And then, we have changed the call to the <code> get review responses</code> and the new method calculate peer review grades accordingly
 
[[File:snip3.png]]
 

Similarly, we have changed <code>db_query_with_quiz_scores</code> to <code>calculate_quiz_scores</code>

 
[[File:snip5.png]]
 

===''' Change name of json_generator'''===

json_generator method should be <code>generate_json</code>
Also,there needs to be a method comment saying what the parameters are.
We have added the method comments with parameter information as seen below.

[[File:snip4.png]]

===''' Refactor send_post_request'''===

“In (<code>send_post_request</code>), there are references to specific assignments, such as 724, 735, and 756.
They were put in to gather data for a paper published in 2015. They are no longer relevant and should be removed.
send_post_request is 91 lines long, far too long”

We observed that encryption of request being sent to evaluate Algorithm and decryption of response is written in (<code>send_post_request</code>) only that is why send post is 91 lines long.
We have created two separate methods
(<code>encrypt_request</code>)
(<code>decrypt_response</code>)

In the image below we can see that we have separated Encryption functionality from (<code>send_post_request</code>) to (<code>encrypt_request</code>)

[[File:encryption-separated.jpg]]

On the similar lines we have separated Decryption functionality from (<code>send_post_request</code>) to (<code>decrypt_response</code>)

[[File:decryption-separated.jpg]]

(<code>encrypt_request</code>) has been refactored as follows

[[File:encrypt_request.png]]

(<code>decrypt_response</code>) has been refactored as follows

[[File:decrypt_request.png]]

===''' Fixed spelling of dimention'''===

[[File:capture2.png]]

==='''Updating client method'''===

We changed the class variables in the code to instance variables because we realized that the send_post_request method and client method were the only methods using the class variables.
A client.html.erb file was used to call our controller to fill out a form for send_post_request which set values of class variables then immediately redirected control to the client method which set the instance variables to those class variables. The instance variables were then used in the rest of the client.html.erb file.

The process of setting class variables to instance variables with a function was redundant, so we removed simply set the instance variables within the send_post_request method and took them out of the client method.
The client method did have a few actions that it completed besides setting class variables which we have left in the method until we can find a solution to replacing them.

[[File:old-client-method.png|frame|none|alt=Alt text|Old client method]]

[[File:new-client-method-and-helpers-new.png|frame|none|alt=Alt text|Newest client method]]

The reason why we kept the client method's name is because the routes.rb file specifies the client method in the code and we haven't fully tracked down the impact of that yet.

== Testing ==
=== Test Plan ===
There was no existing tests available for the Reputation web service controller.
Since we are refactoring it, Our testing plan is to have an automated testing to ensure that the refactoring such as splitting a method into separate methods does not affect the existing implementation.
We also added steps for the manual testing to make sure the page loads as expected after the refactoring.
 
For the automated testing, We are using Rspec tests.
 
===RSpec===
We have tested the two new methods created during refactoring and also a renamed method.
The test coverage has increased a bit by 0.6% to 44%.
 

The tests we created can also be manually run with command 
<code>rspec spec/controllers/reputation_web_controller_spec.rb. </code>
 

[[File:snip13.png]]

===Manual UI Testing===
 
To test our program you must login to the site here: http://152.7.98.78:8080/

Username: instructor6
Password: password

Then if this page "Manage content" doesn't show up, go to Manage... -> Assignments

Then on the CSC 517, Fall 2017 Row, click on "Add Participants" in the actions column. This is also indicated by a blue shirted person.

Once in there, copy on of the student names (such as student7487) and then go to Manage... -> Impersonate User and enter their name (student7487) into the text field.

After impersonating them, you should be able to see their assignments. Click on any of their assignments such as "Program 1", then click on "Alternate View" to the right of "Your Scores". This will take you to a site that shows a Reputation Score like in the screenshot below. This proves that our controller has given the information to this view for the

 
----

=='''Modified Files'''==
<code>reputation_web_service_controller.rb</code>

=='''New Files'''==
<code>reputation_web_service_controller_spec.rb</code>

==Team Information==
'''Project Mentor:''' 
Saurabh Shingte

'''Project Members:''' 
Abhishek Gupta 
Skieler Capezza 
Ummu Kolusum Yasmin Ahamed Adam

=='''References'''==

*[https://github.com/srcapezza/expertiza/tree/beta Project Repo] 
*[https://github.com/expertiza/expertiza/pull/1790 Github Pull Request]
*[https://github.com/expertiza/expertiza Expertiza on GitHub]
*[http://expertiza.ncsu.edu/ The live Expertiza website]
*[http://research.csc.ncsu.edu/efg/expertiza Expertiza project Details]
*[https://www.youtube.com/channel/UCdKXzox7hrWjfOMML6FzTWg Expertiza YouTube Channel]

File:Decrypt request.png

2020-10-20T03:04:47Z

Agupta38: Agupta38 uploaded a new version of File:Decrypt request.png

File:Decrypt request.png

2020-10-20T03:04:08Z

Agupta38: