Index: openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql,v diff -u -r1.9 -r1.10 --- openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql 25 Jul 2004 11:12:24 -0000 1.9 +++ openacs-4/packages/evaluation/sql/postgresql/evaluation-create.sql 30 Jul 2004 22:50:07 -0000 1.10 @@ -6,6 +6,9 @@ primary key constraint evaluation_grades_id_fk references cr_revisions(revision_id), + grade_item_id integer + constraint evaluation_grades_gid_fk + references cr_items(item_id), grade_name varchar(100), grade_plural_name varchar(100), comments text, @@ -31,6 +34,9 @@ primary key constraint evaluation_tasks_fk references cr_revisions(revision_id), + task_item_id integer + constraint evaluation_tasks_tid_fk + references cr_items(item_id), task_name varchar constraint evaluation_tasks_tn_nn not null, @@ -41,11 +47,11 @@ constraint evaluation_tasks_nom_df default 1, due_date timestamp, - grade_id integer + grade_item_id integer constraint evaluation_tasks_gid_fk - references evaluation_grades, + references cr_items(item_id), -- percentage of the grade of the course - weight integer, + weight numeric, -- the task will be submitted on line online_p char(1) constraint evaluation_tasks_onp_ck @@ -74,9 +80,12 @@ create table evaluation_tasks_sols ( solution_id integer primary key, - task_id integer + solution_item_id integer + constraint evaluation_tsols_siid_fk + references cr_items(item_id), + task_item_id integer constraint evaluation_tsols_tid_fk - references evaluation_tasks + references cr_items(item_id) ); -- create indexes @@ -96,15 +105,18 @@ answer_id integer primary key references cr_revisions, + answer_item_id integer + constraint evaluation_sans_aiid_fk + references cr_items(item_id), -- person/group to wich the answer belongs party_id integer constraint evaluation_sans_pid_nn not null constraint evaluation_sans_pid_fk references parties(party_id), - task_id integer + task_item_id integer constraint evaluation_sans_tid_fk - references evaluation_tasks + references cr_items(tem_id) ); create index evaluation_answers_tid_index on evaluation_answers(party_id,task_id); @@ -126,11 +138,13 @@ primary key constraint evaluation_stu_evals_fk references acs_objects(object_id), - task_id integer + evaluation_item_id integer constraint evaluation_stu_evals_eiid + references cr_items(item_id), + task_item_id integer constraint evaluation_stu_evals_tid_nn not null constraint evaluation_stu_evals_tid_fk - references evaluation_tasks, + references evaluation_tasks(task_item_id), -- must have student_id or team_id party_id integer constraint evaluation_stu_evals_pid_nn @@ -162,9 +176,12 @@ create table evaluation_grades_sheets ( grades_sheet_id integer primary key, - task_id integer + grades_sheet_item_id integer + constraint evaluation_gsheets_giid_fk + references cr_items(item_id), + task_item_id integer constraint evaluation_gsheets_t_id_fk - references evaluation_tasks + references evaluation_tasks(task_item_id) ); -- create indexes @@ -202,11 +219,11 @@ primary key constraint evaluation_task_groups_fk references groups(group_id), - task_id integer + task_item_id integer constraint evaluation_task_groups_tid_nn not null constraint evaluation_task_groups_tid_fk - references evaluation_tasks(task_id) + references evaluation_tasks(task_item_id) ); create index evaluation_task_groups_tid_index on evaluation_task_groups(task_id); @@ -336,13 +353,15 @@ ); insert into evaluation_grades - (grade_id, + (grade_id, + grade_item_id, grade_name, grade_plural_name, comments, weight) values - (v_revision_id, + (v_revision_id, + p_item_id, p_grade_name, p_grade_plural_name, p_description, @@ -355,26 +374,30 @@ create function evaluation__delete_grade (integer) returns integer as ' declare - p_grade_id alias for $1; + p_grade_item_id alias for $1; del_rec record; + begin FOR del_rec IN - select task_id - from evaluation_tasks - where grade_id = p_grade_id + select task_item_id + from evaluation_tasks + where grade_item_id = p_grade_item_id + LOOP - delete from evaluation_student_evals where task_id = del_rec.task_id; - delete from evaluation_answers where task_id = del_rec.task_id; - delete from evaluation_tasks_sols where task_id = del_rec.task_id; - delete from evaluation_grades_sheets where task_id = del_rec.task_id; + + PERFORM evaluation__delete_student_eval(evaluation_id) from evaluation_student_evals where task_item_id = del_rec.task_item_id; + PERFORM evaluation__delete_answer(answer_id) from evaluation_answers where task_item_id = del_rec.task_item_id; + PERFORM evaluation__delete_task_sol(solution_id) from evaluation_tasks_sols where task_item_id = del_rec.task_item_id; + PERFORM evaluation__delete_grades_sheet(grades_sheet_id) from evaluation_grades_sheets where task_item_id = del_rec.task_item_id; + END LOOP; - delete from evaluation_tasks where grade_id = p_grade_id; - delete from evaluation_grades where grade_id = p_grade_id; - - PERFORM content_revision__delete(p_grade_id); + PERFORM evaluation__delete_task(task_id) from evaluation_tasks where grade_item_id = p_grade_item_id; + delete from evaluation_grades where grade_item_id = p_grade_item_id; + PERFORM content_revision__delete(grade_id) from evaluation_grades where grade_item_id = p_grade_item_id; + return 0; end;' language 'plpgsql'; @@ -404,7 +427,7 @@ p_revision_id alias for $2; p_task_name alias for $3; p_number_of_members alias for $4; - p_grade_id alias for $5; + p_grade_item_id alias for $5; p_description alias for $6; p_weight alias for $7; p_due_date alias for $8; @@ -441,20 +464,22 @@ insert into evaluation_tasks (task_id, + task_item_id, task_name, number_of_members, due_date, - grade_id, + grade_item_id, weight, online_p, late_submit_p, requires_grade_p) values - (v_revision_id, + (v_revision_id, + p_item_id, p_task_name, p_number_of_members, p_due_date, - p_grade_id, + p_grade_item_id, p_weight, p_online_p, p_late_submit_p, @@ -467,17 +492,19 @@ create function evaluation__delete_task (integer) returns integer as ' declare - p_task_id alias for $1; + p_task_item_id alias for $1; del_rec record; begin - delete from evaluation_student_evals where task_id = p_task_id; - delete from evaluation_answers where task_id = p_task_id; - delete from evaluation_tasks_sols where task_id = p_task_id; - delete from evaluation_grades_sheets where task_id = p_task_id; - delete from evaluation_tasks where task_id = p_task_id; + + PERFORM evaluation__delete_student_eval(evaluation_id) from evaluation_student_evals where task_item_id = p_task_item_id; + PERFORM evaluation__delete_answer(answer_id) from evaluation_answers where task_item_id = p_task_item_id; + PERFORM evaluation__delete_task_sol(solution_id) from evaluation_tasks_sols where task_item_id = p_task_item_id; + PERFORM evaluation__delete_grades_sheet(grades_sheet_id) from evaluation_grades_sheets where task_item_id = p_task_item_id; + + delete from evaluation_tasks where task_id = p_task_item_id; - PERFORM content_revision__delete(p_task_id); + PERFORM content_revision__delete(task_id) from evaluation_tasks where task_item_id = p_task_item_id; return 0; @@ -506,7 +533,7 @@ declare p_item_id alias for $1; p_revision_id alias for $2; - p_task_id alias for $3; + p_task_item_id alias for $3; p_object_type alias for $4; p_creation_date alias for $5; p_creation_user alias for $6; @@ -537,10 +564,12 @@ insert into evaluation_tasks_sols (solution_id, - task_id) + solution_item_id, + task_item_id) values (v_revision_id, - p_task_id); + p_item_id, + p_task_item_id); return v_revision_id; end; @@ -571,7 +600,7 @@ declare p_item_id alias for $1; p_revision_id alias for $2; - p_task_id alias for $3; + p_task_item_id alias for $3; p_party_id alias for $4; p_object_type alias for $5; p_creation_date alias for $6; @@ -603,11 +632,13 @@ insert into evaluation_answers (answer_id, - task_id, + answer_item_id, + task_item_id, party_id) values - (v_revision_id, - p_task_id, + (v_revision_id, + p_item_id, + p_task_item_id, p_party_id); return v_revision_id; @@ -638,7 +669,7 @@ declare p_item_id alias for $1; p_revision_id alias for $2; - p_task_id alias for $3; + p_task_item_id alias for $3; p_object_type alias for $4; p_creation_date alias for $5; p_creation_user alias for $6; @@ -669,10 +700,12 @@ insert into evaluation_grades_sheets (grades_sheet_id, - task_id) + grades_sheet_item_id, + task_item_id) values (v_revision_id, - p_task_id); + p_item_id, + p_task_item_id); return v_revision_id; end; @@ -701,7 +734,7 @@ declare p_item_id alias for $1; p_revision_id alias for $2; - p_task_id alias for $3; + p_task_item_id alias for $3; p_party_id alias for $4; p_grade alias for $5; p_show_student_p alias for $6; @@ -736,13 +769,15 @@ insert into evaluation_student_evals (evaluation_id, - task_id, + evaluation_item_id, + task_item_id, party_id, grade, show_student_p) values (v_revision_id, - p_task_id, + p_item_id, + p_task_item_id, p_party_id, p_grade, p_show_student_p); @@ -778,7 +813,7 @@ p_creation_user alias for $5; p_creation_ip alias for $6; p_context_id alias for $7; - p_task_id alias for $8; + p_task_item_id alias for $8; v_group_id integer; @@ -799,10 +834,10 @@ insert into evaluation_task_groups (group_id, - task_id) + task_item_id) values (v_group_id, - p_task_id); + p_task_item_id); return v_group_id; end; @@ -942,11 +977,11 @@ FOR v_grades_cursor IN select (ese.grade*et.weight*eg.weight)/10000 as grade from evaluation_gradesx eg, evaluation_tasks et, evaluation_student_evalsi ese, acs_objects ao - where et.task_id = ese.task_id - and et.grade_id = eg.grade_id + where et.task_item_id = ese.task_item_id + and et.grade_item_id = eg.grade_item_id and eg.item_id = ao.object_id and ao.context_id = p_package_id - and ese.party_id = evaluation__party_id(p_user_id,ese.task_id) + and ese.party_id = evaluation__party_id(p_user_id,et.task_id) and content_revision__is_live(ese.evaluation_id) = true and content_revision__is_live(eg.grade_id) = true and content_revision__is_live(et.task_id) = true @@ -998,10 +1033,13 @@ if v_number_of_members = 1 then return p_user_id; else - return coalesce((select etg.group_id from evaluation_task_groups etg, acs_rels map + return coalesce((select etg.group_id from evaluation_task_groups etg, + evaluation_tasks et, + acs_rels map where map.object_id_one = etg.group_id and map.object_id_two = p_user_id - and etg.task_id = p_task_id),0); + and etg.task_item_id = et.task_item_id + and et.task_id = p_task_id),0); end if; end;' language 'plpgsql';