UCD grade export comparisons - Fall of 2010

General documentation of code and process: Automated Grade Exports

DB and Application Hosts

Application Server	smartsite-test26
DB Server	mario
Schema	sakai

Identification of Sites

Each course site is created for one specific academic term If the grades are submitted using GB2 from each course site during the same term for which it is created (safe assumption) then we can associate a gradebook2 release version which was used to generate the final grades.

First we generate a list of sites for each term:

SQL used:

SELECT
    gb.GRADEBOOK_UID, ssp.value

FROM
    GB_GRADEBOOK_T gb,
    SAKAI_SITE ss,
    SAKAI_SITE_PROPERTY ssp,
    SAKAI_SITE_TOOL sst
WHERE
    gb.GRADEBOOK_UID = ss.SITE_ID AND
    sst.SITE_ID = ss.SITE_ID AND
    ssp.SITE_ID = ss.SITE_ID AND
    ssp.NAME = 'term_eid' AND
    to_char(ssp.VALUE) IN ( 'http://ucdavis.edu/course/academicsession/200909',
    'http://ucdavis.edu/course/academicsession/200910',
    'http://ucdavis.edu/course/academicsession/201001',
    'http://ucdavis.edu/course/academicsession/201002',
    'http://ucdavis.edu/course/academicsession/201003',
    'http://ucdavis.edu/course/academicsession/201004',
    'http://ucdavis.edu/course/academicsession/201005',
    'http://ucdavis.edu/course/academicsession/201006',
    'http://ucdavis.edu/course/academicsession/201007',
    'http://ucdavis.edu/course/academicsession/201008',
    'http://ucdavis.edu/course/academicsession/200909',
    'http://ucdavis.edu/course/academicsession/200910' ) AND
    sst.REGISTRATION = 'sakai.gradebook.gwt.rpc' AND
    ss.TYPE = 'course' ORDER BY to_char(VALUE) DESC;

The attachment gradebook2siteids.zip contains the result of the query after some processing to separate the results by term. The zip file contains one file for each term in the sql where-clause above, each named with the format 'site_[term].txt'; eg, 'site_200910.txt'

The Test In A Nutshell

First, we had create a data set that represents the best guess we have for the grades that were submitted. Our 'base dataset'. For each term, we have to identify the GB2 version in production during that term. Then narrow that list down to the most likely version(s) used when the grades were actually submitted and run the export process for that version against the list if sites for the term. Our base dataset represents our best (automated) guess of the actual submitted values.

Later we will compare the base data for each of the sites against different versions to generate hypothetical grades: grade which would have been submitted with the given code.

Base Data Results

The 'GB2 version...' column is the version or versions of GB2 in production during the period of each term and the 'Version choice(s)..' column, the versions in which final grade submissions were most likely made. Grades can be submitted early or late, so this is an imperfect selection. But the version(s) selected in each case represents the best choices and chosen to minimize the possibility of error.

[Reference for GB2 deployment dates for versions: https://confluence.ucdavis.edu/confluence/x/IYjvAQ]

Term	Title	Dates	Number of Sites	GB2 version(s) in use	Versions choice(s) for export testing	ucd-gradebook2-ent version(s)	Data File(s)	ActionRecord Property Name
201001	Winter Quarter 2010	04-JAN-10 - 15-MAR-10	1131	(1.1.3, 1.1.4.1) 1.1.4.1 deployed about 3 weeks before finals week (3/16) on 2/26	1.1.4.1
201007	Summer Session 2 2010	02-AUG-10 - 10-SEP-10	227	(1.2.0, 1.3.0) 1.3.0 deployed about 3 weeks before end of SS	1.3.0		Grade130TERM201007.csv	Grade130TERM201007
201002	Spring Semester 2010	11-JAN-10 - 14-MAY-10	36	(1.1.3, 1.1.4.1, 1.2.0) 1.2.0 deployed 1 month before end of semester	1.2.0	1.1.0	Grade120TERM201002_11182010.csv	Grade120TERM201002
201005	Summer Session 1 2010	21-JUN-10 - 30-05-AUG-05	252	1.2.0	1.2.0	1.1.0	Grade120TERM201005_11182010.csv	Grade120TERM201005
201008	Summer Quarter 2010	14-JUN-10 - 19-SEP-10	30	(1.2.0, 1.3.0) 1.3.0 deployed more than 4 weeks before end of quarter	1.3.0		Grade130TERM201008.csv	Grade130TERM201008
200910	Fall Quarter 2009	24-SEP-09 - 04-DEC-09	974	(1.1.1, 1.1.2, 1.1.3) 1.1.3 deployed more than 4 weeks before end of quarter	1.1.3
201004	Extra Session 2010	21-JUN-10 - 30-JUL-10	1	1.2.0	1.2.0	1.1.0	no results (bogus ITX site)	Grade120TERM201004
201006	Summer Special Session 2010	16-JUN-10 - 10-SEP-10 0	25	(1.2.0, 1.3.0) 1.3.0 deployed about 3 weeks before end of SS	1.3.0		Grade130TERM201006.csv	Grade130TERM201006
200909	Fall Semester 2009	24-AUG-09 - 23-DEC-09	30	(1.0.0, 1.1.0, 1.1.1, 1.1.2, 1.1.3) 1.1.3 deployed more than 6 weeks before end of semester	1.1.3
201003	Spring Quarter 2010	29-MAR-10 - 03-JUN-10	1181	(1.1.4.1, 1.2.0) 1.2.0 deployed more than 6 weeks before end of Quarter	1.2.0	1.1.0	Grade120TERM201003_11182010.csv	Grade120TERM201003
201009	Fall Semester 2010	23-AUG-10 - 22-DEC-10	35	(1.3.0, 1.3.1) 1.3.1 deployed about 6 weeks before the end of the Quarter	1.3.1	https://svn.rsmart.com/svn/cle-custom/ucd/branches/cle262x/ucd-gradebook2-ent	Grade131TERM201009_11192010.csv Grade131TERM201009.csv	Grade131TERM201009
201010	Fall Quarter 2010	23-SEP-10 - 03-DEC-10	1605	(1.3.0, 1.3.1) 1.3.1 deployed about 6 weeks before the end of the semester	1.3.1	https://svn.rsmart.com/svn/cle-custom/ucd/branches/cle262x/ucd-gradebook2-ent	Grade131TERM201010_11192010.csv Grade131TERM201010.csv	Grade131TERM201010

Test Data results

We will be comparing the base data against various test datasets. The test datasets include exported grades for all the sites in the base dataset using the particular GB2 version to be tested. Usually this version is the latest and best version of fixes that exist, in order to identify any erroneously calculated historical grades that fix(es) would uncover. But it might also uncover erroneously calculated new grades that the fix(es) create ('bugs', actually).

GB2 version 1.4.0 Alpha 1 (tag 1.4.0-a1)

This version of GB2 incorporates GRBK-767 and GRBK-774

Term(s)	Property Name	Dataset
201009 201010	Grade140a1TERM201009201010	Grade140a1TERM201009201010_11192010.csv
201009	Grade140a1TERM201009	Grade140a1TERM201009.csv
201010	Grade140a1TERM201010	Grade140a1TERM201010.csv
201006	Grade140a1TERM201006	Grade140a1TERM201006.csv
201008	Grade140a1TERM201008	Grade140a1TERM201008.csv
201007	Grade140a1TERM201007	Grade140a1TERM201007.csv
201002	Grade140a1TERM201002	Grade140a1TERM201002.csv
201005	Grade140a1TERM201005	Grade140a1TERM201005.csv
201004	Grade140a1TERM201004	nil
201003	Grade140a1TERM201003	Grade140a1TERM201003.csv

Testing the test

At the time the data above was collected during Fall 2010, the two academic terms, Fall Semester and Fall Quarter, were in session and so the gradebooks used to export the grades may be incomplete. So, in order to test the process used (specifically exporting the same sites with 1.4.1-a1 and 1.3.1 and comparing the results), we exported Spring Quarter 2010 using both 1.3.1 and 1.4.1-a1 to compare the results since Spring Quarter should have a good set of completed gradebooks.

Note from the redundancy department is that Spring Quarter 2010 ran 1.2.0 so the data below is only to test the process and has no meaning for actually grades given.

Term(s)	Property Name	Dataset
201003	Grade140a1TERM201003	Grade140a1TERM201003.csv
201003	Grade131TERM201003	Grade131TERM201003.csv

GB2 version 1.4.0 Alpha 2 (tag 1.4.0-a2)

This version of GB2 incorporates GRBK-780

Term(s)	Property Name	Dataset
201009 201010	Grade140a1TERM201009201010

Comparisons

The comparisons are completed using SQL. You'll find the SQL attached to the Sakai Foundation Wiki page Automated Grade Exports

Test	Test Data GB2 version	Test Data Property Name	TERM(s) compared	Base Data GB2 Version	Base Data Property Name	SQL used for comparison	Datafile	# of grades effected by code change
1.4.0-a1 vs 1.3.1 Fall Terms 2010	tag 1.4.0-a1	Grade140a1TERM201009201010	201009,201010	1.3.1	Grade131TERM201009201010	LetterGrade Only	n/a	0
1.4.0-a1 vs 1.3.1 Fall Semester 2010	tag 1.4.0-a1	Grade140a1TERM201009	201009	1.3.1	Grade131TERM201009	(same)	n/a	0
1.4.0-a1 vs 1.3.1 Fall Quarter 2010	tag 1.4.0-a1	Grade140a1TERM201010	201010	1.3.1	Grade131TERM201010	(same)	tbd	10
Hypothetical: 1.4.0-a1 vs 1.3.1 Spring Quarter 2010	tag 1.4.0-a1	Grade140a1TERM201003	201003	1.3.1	(not really 'base' since v1.2.0 was run during this term) Grade131TERM201003	(same)	tbd	14 (note 1)
1.4.0-a1 vs 1.3.0 Summer Session II 2010	tag 1.4.0-a1	Grade140a1TERM201007	201007	1.3.0	Grade130TERM201007	same	tbd	14 (note 2)
1.4.0.a1 vs 1.3.0 Special Summer Session 2010	tag 1.4.0.-a1	Grade140a1TERM201006	201006	1.3.0	Grade130TERM201006	same	n/a	0
1.4.0-a1 vs 1.3.0 Summer Quarter 2010	tag 1.4.0-a1	Grade140a1TERM201008	201008	1.3.0	Grade130TERM201008	same	n/a	0
1.4.0-a1 vs 1.2.0 Spring Semester 2010	tag 1.4.0-a1	Grade140a1TERM201002	201002	1.2.0	Grade120TERM201002	same	n/a	0
1.4.0-a1 vs 1.2.0 Summer Session I 2010	tag 1.4.0-a1	Grade140a1TERM201005	201005	1.2.0	Grade120TERM201005	same	n/a	0
1.4.0-a1 vs 1.2.0 Extra Session 2010	----	---	----	----	----	---	n/a	0 (note 3)
1.4.0-a1 vs 1.2.0 Spring Quarter 2010	tag 1.4.0-a1	Grade140a1TERM201003	201003	1.2.0	Grade120TERM201003	same	tbd	146

Notes:

this number only reflects error potential for the the test of Fall Terms.... no Spring grades were *actually* effected. This is a hypothetical test case: a test of the process, if you will.
this is the same 14 students and the same grade changes that were found with early version of this test... no new results
there are not legitimate course sites created for this term

Notes for running tests:

 ssh (app host)
 sudo (app user)
 cd src/*b9
 cd (version of gb2)
 patch -p0<~/(REST_TESTING...) <-- apply correct patch
 find . -name Gradebook2ComponentServiceImpl.java -exec vi {} \;
 (for 140a1 and 140a2 use Gradebook2ComponentServiceNewImpl.java)
 (/submitF <return> and edit property_name, the key the map, for letterGrade and change to suit)
 ZZ
 ~/bin./stop.sh
../clean-gb2libs-staging.sh
mvnrsmart
#### if a new ucd-gb2-ent version is needed ####
cd ~/src/*b9/cle/custom/
(change version link to ucd-gradebook2-ent)
cd ucd-gradebook2-ent
~/src/*b9/clean-gb2-ent-staging.sh
mvnrsmart
#### end of ucd gb ent section #####
jps
jps
cd
~/config-staging/setup-sakai-node.sh
vi ~/sakai-home/local.properties
(change gb2.security.enabled setting to 'false'; change version)
ZZ
~/bin/start.sh

UC Davis Sakai Collaborative

GradeExport Comparisons