[Building Sakai] GB: pulling grades, en masse, from gradebook via SQL -- solved?

[will at serensoft.com]

Fragile, maybe :) ...

If we were inside the Java code we would have all kinds of avenues to
explore, but the context we're working with here is that a process outside
of Sakai must get the grade info.

If the getSpreadsheetData method (thanks, Branden) were worked into a
webservice, that'd be spiffy. Zut alors, without that, SQL looks to be about
the most viable option.


On Tue, Dec 1, 2009 at 2:24 PM, Ray Davis <ray at media.berkeley.edu> wrote:

> It's unsolicited advice, but I have to second Lance's comment: this sounds
> extraordinarily dangerous. Duplicate implementations are never a great idea,
> and for some reason students and instructors tend to get especially irked by
> discrepancies in grade displays. Even if you manage to exactly replicate
> today's logic, who's going to ensure that your SQL stays up to date with
> every future change and bug-fix?
>
> For whatever my opinion's worth, if I needed Gradebook data that wasn't
> available through existing Gradebook service methods, I'd formally request a
> new service method that exposed the existing internal functionality; if I
> needed it in a hurry, I'd branch the code in msub and submit a patch along
> with my JIRA request.
>
> Best,
> Ray
>
>
> On 12/1/09 11:39 AM, will at serensoft.com wrote:
>
>> Hold the phone, we're closing in on it! So far this appears to work for
>> the per-gradable-object level (that is, course-grade-overrides are ignored
>> at this point, that'll come in a later phase)...
>>
>> If you have gradebook chops, see if the following SQL is missing any
>> tricks, our spot-checking indicates it's working! (Seems to work for
>> no-category-grades, category-only-grades, and weighted-category-grades...)
>>
>> Note that the temporary tables all start with "x_" to avoid colliding with
>> any existing Sakai tables, and they "go away" once you close your MySQL
>> connection.
>>
>> drop table if exists x_courses;
>>
>> create temporary table x_courses
>>    select
>>        site.site_id,
>>        site.title worksite,
>>        p.value term
>>    from
>>        SAKAI_SITE site
>>            join
>>        SAKAI_SITE_PROPERTY p
>>            on site.SITE_ID = p.SITE_ID
>>    where
>>        p.value = 'FA09' and -- ***** SEMESTER FILTER HERE *****
>>        p.name <http://p.name> = 'term_eid' and
>>
>>        site.published = 1
>>        and site.title like 'SOME-FILTERING-HERE%'
>> ;
>>
>> drop table if exists x_categories;
>>
>> create temporary table x_categories
>> select
>>    s.title worksite,
>>    s.site_id,
>>    g.id <http://g.id> gradebook,
>>
>>    u.eid username,
>>    u.user_id,
>>    c.weight,
>>    c.name <http://c.name> category,
>>
>>    sum( o.points_possible ) max,
>>    sum( r.points_earned ) pts
>> from
>>    (
>>    GB_GRADABLE_OBJECT_T o
>>        left join
>>    GB_CATEGORY_T c
>>        on o.CATEGORY_ID = c.ID
>>        join
>>    (
>>    GB_GRADE_RECORD_T r
>>        join
>>    SAKAI_USER_ID_MAP u
>>        on r.student_id=u.user_id
>>    )          on o.id <http://o.id> = r.gradable_object_id
>>
>>    )          join
>>    GB_GRADEBOOK_T g
>>        on g.id <http://g.id>=o.gradebook_id
>>
>>        join
>>    SAKAI_SITE s
>>        on g.gradebook_uid=s.site_id
>> where
>>    s.site_id in (
>>        select
>>            site_id
>>        from
>>            x_courses
>>    ) and
>>    o.object_type_id = 1 and -- ***** NOT 'COURSE GRADE', gradebook-items
>> only *****
>>    o.not_counted != 1 -- ***** OMIT IF NOT INCLUDED IN COURSE GRADE *****
>> group by
>>    s.title,
>>    s.site_id,
>>    g.id <http://g.id>,
>>
>>    u.eid,
>>    u.user_id,
>>    c.weight,
>>    c.name <http://c.name>
>>
>> ;
>>
>> -- intermediate quick-check, already rolled up into categories when
>> available:
>> select worksite,username,category,weight,max,pts,
>>    (pts/max) score,
>>    (pts/max*weight) weighted
>> from x_categories;
>>
>> -- now for the real grades (again these are gradable-object level grades,
>> -- if any course-override grades have been entered they would be ignored
>> select
>>    worksite,
>>    -- site_id,
>>    username,
>>    -- user_id,
>>    sum( weight ) scale,
>>    case
>>        when sum(weight) = 0 or sum(weight) is null
>>            then sum( pts ) / sum( max )
>>        else sum( pts/max * weight ) / sum( weight )
>>        end score
>> from
>>    x_categories
>> group by
>>    worksite,
>>    username;
>>
>>
>>
>>    On Mon, Nov 30, 2009 at 10:32 PM, will at serensoft.com
>>    <mailto:will at serensoft.com> <will at serensoft.com
>>
>>    <mailto:will at serensoft.com>> wrote:
>>
>>        Use case: the registrar wants to see how the students are doing,
>>        especially those who might need remedial support.
>>
>>        We are experimenting with how best to pull grades from the
>>        gradebook in 2.6.x on MySQL v5.0. We have some SELECT statements
>>        that do some grade-pulling... and some spot-checks indicate they
>>        match with what's shown in the browser for simple cases, but
>>        with partial activity in weighted categories, it gets muddy and
>>        muddier.
>>
>>
>>
>> --
>> will trillich
>> "Tis the set of the sails / And not the gales / That tells the way we go."
>> -- Ella Wheeler Wilcox
>>
>>
>>
>


-- 
will trillich
"Tis the set of the sails / And not the gales / That tells the way we go."
-- Ella Wheeler Wilcox
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20091201/5e10368b/attachment.html