View source for CSC/ECE 517 Spring 2022 - S2200: Refactoring evaluation of SQL queries (Java/JUnit)

== About SQLFE==

SQL File Evaluation (SQLFE) is an open-source tool that helps in the flexible scoring of multiple SQL queries in multiple submitted files from assignments, lab tests, or other work. It was developed by [https://www.uwec.edu/profiles/wagnerpj/ Prof. Paul Wagner] at [https://www.uwec.edu/ University Of Wisconsin Eau Claire]. This tool has features to support the automated grading of the SQL assignments. It allows partial grading of the question to fairly grade the students and also allows students to provide comments, and multiple solutions to the same problem.

== Description about the project ==
This page is a description of SQLFE OSS project S2200 which is Refactoring evaluation of SQL queries (Java/JUnit) and writing test cases for the changes made. SQLFE is an SQL evaluation and grading solution. The core of the functionality deals with going through all the submissions and getting the output for a student's solution via SQL and comparing the output with the desired one. This involves calling the evaluate function which has high complexity and is almost non-readable. Our project primarily focuses on refactoring the complex evaluate function to reduce the complexity by breaking it up into several functions, each dealing with a specific part of the functionality. The goal of this project is to attempt to make this part of the application easier to read and maintain. We have also optimized the code by removing the redundant calls to the same operation and storing the output.

== Files modified in current project ==
BackEnd.java;
QueryEvaluationLists.java;
AbstractTest.java;
BackEndTest.java

=== BackEnd.java ===
This file receives the SQL connection details and assignment details from the frontend and performs the grading for all the students.

====List Of Changes====
We worked on the following changes:<br/> 
=====Change 1=====
The calculation of the questions to the possible answer was happening redundantly which was impacting the performance. We moved this code to a separate function and are now using a map to store the question to answer values.

<pre>
while (qIndex < questions.size() && !foundAll) {
	// first match
	if (!foundOne && questions.get(qIndex).getQNumStr().indexOf(qa.getQNumStr()) == 0) {
		foundOne = true;
		currQuestions.add(questions.get(qIndex));		// use this question
		qIndex++;
	}
	// subsequent match
	else if (foundOne && questions.get(qIndex).getQNumStr().indexOf(qa.getQNumStr()) == 0) {
		currQuestions.add(questions.get(qIndex));		// add this question too
		qIndex++;
	}
	// first non-match after subsequent match
	else if (foundOne && questions.get(qIndex).getQNumStr().indexOf(qa.getQNumStr()) != 0) {
		foundAll = true;
		qIndex++;
	}
	// not a match
	else {
		qIndex++;
	}
}	// end - while looking for question(s) to match student answer
</pre>

The above operation was happening redundantly for each student. We have moved this code to a separate function which is called in the preprocessing phase. 

<pre>
/** Method to create a map of questions
 *
 * @param questions Array list containing a list of questions.
 * @return Arraylist from Integer(question number) to list of questions.
 */
Map<Integer, ArrayList<Question>> createQuestionToAnswer(ArrayList<Question> questions) {


	Map<Integer, ArrayList<Question>>  questionToAnswer = new HashMap<>();

	// iterate through the list of questions.
	for( Question question: questions){
		// get the question number
		Integer questionNo = Integer.parseInt(String.valueOf(question.getQNumStr().charAt(0)));

		//Add the question to the map.
		questionToAnswer.putIfAbsent(questionNo, new ArrayList<>());
		questionToAnswer.get(questionNo).add(question);
	}
	return questionToAnswer;
}
</pre>

This function creates a map and we can directly use this map to get currQuestions.
<pre>
currQuestions = questionToAnswer.getOrDefault(questionNo, null);
</pre>

=====Change 2=====
The calculation of the evaluation array lists was happening redundantly for each student. We have separated out this part and have created a separate class. This has improved the readability and we are now avoiding doing redundant operations.

<pre>
ArrayList<ISQLTest> questionTests = new ArrayList<ISQLTest>();
ArrayList<Integer> questionPcts  = new ArrayList<Integer>();
ArrayList<String> questionConditions = new ArrayList<String>();

// evaluate all tests for this question
for (int tiqIndex = 0; tiqIndex < questionEvalComps.size(); tiqIndex++) {
	// get test names
	String currTestName = questionEvalComps.get(tiqIndex).getEvalComponentName();
	currTestName = "sqlfe.sqltests." + currTestName;

	// make test object out of test name
	try {
		Class<?> aClass = Class.forName(currTestName);
		Object oTest = aClass.newInstance();
		ISQLTest test = (ISQLTest)oTest;
		questionTests.add(test);
	}
	catch (Exception e) {
		System.out.println("exception in generating class object from name");
	}

	// get percents
	int currTestPct = questionEvalComps.get(tiqIndex).getPercent();
	questionPcts.add(currTestPct);
	
	// get condition
	String currTestCondition = questionEvalComps.get(tiqIndex).getCondition();
	questionConditions.add(currTestCondition);
}	// end - for each test in question
</pre>

We have created a <b>new class </b> for keeping the query evaluation arraylists. 

<pre>
/*
 * QueryEvaluationLists - class that holds query evaluation params.
 *
 * Created - 1-Mar-2022
 */
package sqlfe.general;

import sqlfe.sqltests.ISQLTest;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class QueryEvaluationLists {

    // List that holds query evaluations params.
    final private ArrayList<ISQLTest> questionTests ;
    final private ArrayList<Integer> questionPcts  ;
    final private ArrayList<String> questionConditions ;

    // methods
    // constructors
    // all-args constructor
    public QueryEvaluationLists(ArrayList<ISQLTest> questionTests, ArrayList<Integer> questionPcts, ArrayList<String> questionConditions) {
        this.questionTests = questionTests;
        this.questionPcts = questionPcts;
        this.questionConditions = questionConditions;
    }

    // methods
    //default constructor
    public QueryEvaluationLists() {
        this.questionTests = new ArrayList<>();
        this.questionPcts = new ArrayList<>();
        this.questionConditions = new ArrayList<>();
    }

    public ArrayList<ISQLTest> getQuestionTests(){
        return this.questionTests;
    }

    public ArrayList<Integer> getQuestionPcts(){
        return this.questionPcts;
    }

    public ArrayList<String> getQuestionConditions(){
        return this.questionConditions;
    }

    /**
     * Create a map for the holding question number to metrics . This functions does preprocessing.
     * @param questions. A list that holds objects containing data.
     * @return the map holding data
     */
    public Map<String,QueryEvaluationLists> createQuestionNoToEvaluationMetricsMap(ArrayList<Question> questions) {


        Map<String,QueryEvaluationLists> questionNoToEvaluationMetrics= new HashMap<>();

        // iterate through the list of questions.
        for (Question question:questions) {
            // get the question number
            String questionName=question.getQNumStr();
            // create object to hold the data.
            QueryEvaluationLists queryEvaluationLists= new QueryEvaluationLists();

            // Get all the tests corresponding to the question.
            ArrayList<EvalComponentInQuestion> questionEvalComps = question.getTests();

            for (EvalComponentInQuestion questionEvalComp : questionEvalComps) {
                // get test names
                String currTestName = questionEvalComp.getEvalComponentName();
                currTestName = "sqlfe.sqltests." + currTestName;

                // make test object out of test name
                try {
                    Class<?> aClass = Class.forName(currTestName);
                    Object oTest = aClass.newInstance();
                    ISQLTest test = (ISQLTest) oTest;
                    queryEvaluationLists.questionTests.add(test);
                } catch (Exception e) {
                    System.out.println("exception in generating class object from name");
                }

                // get percents
                int currTestPct = questionEvalComp.getPercent();
                queryEvaluationLists.questionPcts.add(currTestPct);

                // get condition
                String currTestCondition = questionEvalComp.getCondition();
                queryEvaluationLists.questionConditions.add(currTestCondition);
            }    // end - for each test in question

            questionNoToEvaluationMetrics.put(questionName,queryEvaluationLists);

        }
        return questionNoToEvaluationMetrics;
    }


}

</pre>

After this change, we can simply get the map of the current questions by using the question number.

<pre>
Map<String, QueryEvaluationLists> questionNoToEvaluationMetrics = queryEvaluationLists.createQuestionNoToEvaluationMetricsMap(questions);
queryEvaluationLists = questionNoToEvaluationMetrics.get(currQuestion.getQNumStr());
</pre>

=====Change 3 =====
The evaluate function was performing all the activities involving the grading of students. The tasks involved were:<br>
1. Initialize the variables like gradesWriter which writes to the UI, load the submission corpus.
2. Go through all the submissions. 
3. For each individual submission go through all the questions.
4. Grade each question.
5. Calculate the overall score for the assignment and write to the output file.

As we can see the evaluate method is responsible for multiple functionalities. Owing to this the complexity is high with low readability. We have split the evaluate functions into several smaller functions.

1. evaluate <br>
2. goThroughAllSubmissions <br>
3. gradeSubmission

== Test Plan ==
We wrote test cases for Backend.java class, particulary for the evaluate method. The tests are discussed below.
=== Setup ===
Here we do initial setup of the unit test and create a test object for backend class.
<pre>
@Before
public void setup() {
    Utilities.forTesting = true;
    backEnd.createTestObject(testDAO, mainFolderPath);
    System.setErr(new PrintStream(errContent));
}
</pre>

=== Output files check ===
This test checks if the number of output lines generated is the same as the number of input files. The additional 2 is because of the header of output file.
<pre>
int numberOfFiles = Objects.requireNonNull(new File(backEndSubmissionPath).list()).length;
long lineCount;
try (Stream<String> stream = Files.lines(Paths.get(gradesFileName), StandardCharsets.UTF_8)) {
    lineCount = stream.count();
}
assertEquals(lineCount,numberOfFiles+2);
</pre>

=== Submission output has grade === 
This test asserts that every question has been graded for any given submission. It compares the number of delimiter(,) in a line against the total number of questions.
<pre>
// test for each submission the output should contain grade for each question
Assignment a = backEnd.createAssignment(mainFolderPath + "/assignmentProperties-MySQL");

Map<Integer, ArrayList<Question>>  questionToAnswer = new HashMap<>();

// iterate through the list of questions.
for( Question question: a.getQuestions()){
    // get the question number
    Integer questionNo = Integer.parseInt(String.valueOf(question.getQNumStr().charAt(0)));

    //Add the question to the map.
    questionToAnswer.putIfAbsent(questionNo, new ArrayList<>());
    questionToAnswer.get(questionNo).add(question);
}

int questions= questionToAnswer.size();
Scanner scanner = new Scanner(new File(gradesFileName));
int lineNo=0;
while (scanner.hasNextLine()) {
    String line = scanner.nextLine();
    if(lineNo>=2){
        String[] arrOfStr = line.split(":");
        assertEquals(arrOfStr.length,3);
        int freqComma =0;
        for(int i=0;i<arrOfStr[2].length();i++){
            if(arrOfStr[2].charAt(i)==',')
                freqComma++;
        }
        assertEquals(freqComma,questions);
    }
    lineNo++;
}
</pre>

=== Invalid paths ===
<pre>
// invalid folder paths
backEnd.setSubmissionFolderPath("invalidPath");
backEnd.setEvaluationFolderPath("invalidPath");
backEnd.evaluate();
assertEquals("Error in reading submission collection\n".trim(), errContent.toString().trim());

errContent.reset();
</pre>

=== Invalid DAO ===
<pre>
// invalid DAO object
IDAO invalidDAO = new MySQL80DataAccessObject("localhost", "3306", "sqlf", "roo", "", true);
backEnd.createTestObject(invalidDAO, mainFolderPath);
backEnd.evaluate();
assertEquals("Invalid database properties\n".trim(), errContent.toString().trim());
</pre>

=== Grade summary output file ===
<pre>
//Test for grade submission, the output file should match are predefined one to pass
backEnd.evaluate();
assertEquals(Files.readAllLines(Paths.get(gradesTestFile)), Files.readAllLines(Paths.get(gradesFileName)));
</pre>

== GitHub Links ==
Link to SQLFE repository: [https://github.com/wagnerpj42/sql-file-evaluation here]

Link to the forked repository: [https://github.com/Sakshamrzt/SQL-File-Evaluation here]

== Project Mentor ==
Prof. Paul Wagner (wagnerpj@uwec.edu)

== Team Members ==
Rachit Sharma(rsharm26@ncsu.edu ) <br>
Saksham Thakur (sthakur5@ncsu.edu) <br>
Shubhender Singh (ssingh54@ncsu.edu)