postgresql7.4
SELECT *
FROM cc_uos u, cc_uos_revisions r, cr_items i
WHERE u.uos_id = :uos_id
AND i.item_id = u.uos_id
AND r.uos_revision_id = i.latest_revision
SELECT d.detail_id, dr.lecturer_id, dr.objectives,
dr.learning_outcomes, dr.syllabus, dr.relevance,
dr.online_course_content
FROM cc_uos u, cc_uos_revisions r, cr_items i,
cc_uos_detail_revisions dr, cc_uos_detail d
WHERE u.uos_id = :uos_id
AND i.item_id = u.uos_id
AND r.uos_revision_id = i.latest_revision
AND d.parent_uos_id = :uos_id
AND dr.detail_revision_id = d.latest_revision_id
SELECT t.tl_id, t.latest_revision_id
FROM cc_uos u, cc_uos_revisions r, cr_items i, cc_uos_tl t
WHERE u.uos_id = :uos_id
AND i.item_id = u.uos_id
AND r.uos_revision_id = i.latest_revision
AND t.parent_uos_id = :uos_id
SELECT t.textbook_set_id, t.latest_revision_id
FROM cc_uos u, cc_uos_revisions r, cr_items i, cc_uos_textbook_set t
WHERE u.uos_id = :uos_id
AND i.item_id = u.uos_id
AND r.uos_revision_id = i.latest_revision
AND t.parent_uos_id = :uos_id
SELECT g.gradattr_set_id, g.latest_revision_id
FROM cc_uos u, cc_uos_revisions r, cr_items i, cc_uos_gradattr_set g
WHERE u.uos_id = :uos_id
AND i.item_id = u.uos_id
AND r.uos_revision_id = i.latest_revision
AND g.parent_uos_id = :uos_id
SELECT w.workload_id, wr.formal_contact_hrs, wr.informal_study_hrs,
wr.student_commitment, wr.expected_feedback, wr.student_feedback,
wr.assumed_concepts
FROM cc_uos u, cc_uos_revisions r, cr_items i,
cc_uos_workload_revisions wr, cc_uos_workload w
WHERE u.uos_id = :uos_id
AND i.item_id = u.uos_id
AND r.uos_revision_id = i.latest_revision
AND w.parent_uos_id = :uos_id
AND wr.workload_revision_id = w.latest_revision_id
SELECT a.assess_id, a.latest_revision_id
FROM cc_uos u, cc_uos_revisions r, cr_items i, cc_uos_assess a
WHERE u.uos_id = :uos_id
AND i.item_id = u.uos_id
AND r.uos_revision_id = i.latest_revision
AND a.parent_uos_id = :uos_id
SELECT method_id FROM cc_uos_assess_method_map
WHERE assess_revision_id = :latest_revision_id
SELECT method_id FROM cc_uos_tl_method_map
WHERE tl_revision_id = :latest_revision_id
SELECT textbook_id FROM cc_uos_textbook_map
WHERE revision_id = :latest_revision_id
SELECT gradattr_id FROM cc_uos_gradattr_map
WHERE revision_id = :latest_revision_id
SELECT count(*)
FROM cc_uos u,
workflow_cases c,
workflow_case_fsm f,
workflow_fsm_states s
WHERE c.case_id = f.case_id
AND s.state_id = f.current_state
AND c.workflow_id = :workflow_id
AND u.uos_id = c.object_id
AND s.short_name != 'closed'
AND u.unit_coordinator_id = :user_id
SELECT count(*)
FROM cc_uos u,
workflow_cases c,
workflow_case_fsm f,
workflow_fsm_states s
WHERE c.case_id = f.case_id
AND s.state_id = f.current_state
AND c.workflow_id = :workflow_id
AND u.uos_id = c.object_id
AND s.short_name != 'closed'
SELECT staff_id FROM cc_staff
SELECT staff_id FROM cc_staff
SELECT DISTINCT coordinator_id FROM cc_stream
WHERE package_id = :package_id
SELECT cc_uos_revision__new (
null,
:uos_id,
:uos_code,
:uos_name,
:credit_value,
:semester,
:unit_coordinator_id,
:activity_log,
:activity_log_format,
now(),
:creation_user,
:creation_ip
);
UPDATE cc_uos SET unit_coordinator_id = :unit_coordinator_id
WHERE uos_id = :uos_id
SELECT cc_uos_detail_revision__new (
null,
:detail_id,
:lecturer_id,
:objectives,
:learning_outcomes,
:syllabus,
:relevance,
:online_course_content,
now(),
:user_id,
:creation_ip
);
SELECT cc_uos_tl_revision__new (
null,
:tl_id,
now(),
:user_id,
:creation_ip
);
SELECT cc_uos_assess_revision__new (
null,
:assess_id,
now(),
:user_id,
:creation_ip
);
SELECT cc_uos_textbook_set_rev__new (
null,
:textbook_set_id,
now(),
:user_id,
:creation_ip
);
SELECT cc_uos_gradattr_set_rev__new (
null,
:gradattr_set_id,
now(),
:user_id,
:creation_ip
);
SELECT cc_uos_workload_revision__new (
null,
:workload_id,
:formal_contact_hrs,
:informal_study_hrs,
:student_commitment,
:expected_feedback,
:student_feedback,
:assumed_concepts,
now(),
:user_id,
:creation_ip
);
SELECT cc_uos_tl_method__map (
:revision_id,
:tl_approach_id
);
SELECT cc_uos_textbook__map (
:revision_id,
:textbook_id
);
SELECT cc_uos_assess_method__map (
:revision_id,
:assess_method_id
);
SELECT cc_uos_gradattr__map (
:revision_id,
:gradattr_id
);
SELECT m.name || ' (' || m.identifier || ')' AS method_name,
m.method_id
FROM cc_uos_tl_method m, acs_objects o
WHERE o.object_id = m.method_id
AND o.package_id = :package_id
AND o.creation_user = :user_id
SELECT t.title || ' (' || t.author || ')' AS textbook_name,
t.textbook_id
FROM cc_uos_textbook t, acs_objects o
WHERE o.object_id = t.textbook_id
AND o.package_id = :package_id
AND o.creation_user = :user_id
SELECT g.name || ' (' || g.identifier || ')' AS ga_name,
g.gradattr_id
FROM cc_uos_gradattr g, acs_objects o
WHERE o.object_id = g.gradattr_id
AND o.package_id = :package_id
AND o.creation_user = :user_id
SELECT m.name || ' (' || m.identifier || '): ' || m.weighting || '%'
AS method_name, m.method_id
FROM cc_uos_assess_method m, acs_objects o
WHERE o.object_id = m.method_id
AND o.package_id = :package_id
AND o.creation_user = :user_id
SELECT latest_revision FROM cr_items WHERE item_id = :object_id
SELECT i.latest_revision AS latest_detail_revision
FROM cr_items i, cr_child_rels c
WHERE c.relation_tag = 'cc_uos_detail'
AND c.parent_id = :object_id
AND i.item_id = c.child_id
SELECT i.latest_revision AS latest_tl_revision
FROM cr_items i, cr_child_rels c
WHERE c.relation_tag = 'cc_uos_tl'
AND c.parent_id = :object_id
AND i.item_id = c.child_id
SELECT i.latest_revision AS latest_assess_revision
FROM cr_items i, cr_child_rels c
WHERE c.relation_tag = 'cc_uos_assess'
AND c.parent_id = :object_id
AND i.item_id = c.child_id
SELECT i.latest_revision AS latest_textbook_revision
FROM cr_items i, cr_child_rels c
WHERE c.relation_tag = 'cc_uos_textbook_set'
AND c.parent_id = :object_id
AND i.item_id = c.child_id
SELECT i.latest_revision AS latest_ga_revision
FROM cr_items i, cr_child_rels c
WHERE c.relation_tag = 'cc_uos_gradattr_set'
AND c.parent_id = :object_id
AND i.item_id = c.child_id
SELECT i.latest_revision AS latest_workload_revision
FROM cr_items i, cr_child_rels c
WHERE c.relation_tag = 'cc_uos_workload'
AND c.parent_id = :object_id
AND i.item_id = c.child_id
UPDATE cc_uos SET live_revision_id = :latest_revision
WHERE uos_id = :object_id
UPDATE cc_uos_detail SET live_revision_id = :latest_detail_revision
WHERE parent_uos_id = :object_id
UPDATE cc_uos_tl SET live_revision_id = :latest_tl_revision
WHERE parent_uos_id = :object_id
UPDATE cc_uos_assess SET live_revision_id = :latest_assess_revision
WHERE parent_uos_id = :object_id
UPDATE cc_uos_gradattr_set SET live_revision_id = :latest_ga_revision
WHERE parent_uos_id = :object_id
UPDATE cc_uos_textbook_set
SET live_revision_id = :latest_textbook_revision
WHERE parent_uos_id = :object_id
UPDATE cc_uos_workload SET live_revision_id = :latest_workload_revision
WHERE parent_uos_id = :object_id
(select * from cc_users u, cc_staff s where u.user_id = s.staff_id)
(select * from cc_users u, cc_staff s where u.user_id = s.staff_id)
SELECT sum(weighting)
FROM cc_uos_assess a,
cc_uos_assess_method_map map,
cc_uos_assess_method meth
WHERE a.assess_id = :assess_id
AND map.assess_revision_id = a.latest_revision_id
AND map.method_id = meth.method_id