← Back to branch summary

~azzar1/unity/add-show-desktop-key

« back to all changes in this revision

Viewing changes to lib/common/db.py

  • Committer: wagrant
  • Date: 2008-07-23 02:42:54 UTC
  • Revision ID: svn-v3-trunk0:2b9c9e99-6f39-0410-b283-7f802c844ae2:trunk:935
userdb: Large changes:
 - Split out semesters into their own table.
 - Give semesters and enrolments an active flag.
   + Enrolments cannot be active if their semester is not.
 - Introduce the project set concept, to link groups to projects.
   + Groups and projects now reference a project set, not an offering.

Also update the DB access code to respect the group changes.

<FONT COLOR="red"><BLINK>

  ** WARNING: This migration will destroy all groups, enrolments and
              offerings. **

</BLINK></FONT>

Show diffs side-by-side

added added

removed removed

Lines of Context:
lib/common/db.py
832
832
        (SELECT loginid FROM login WHERE login=%s),
833
833
        (SELECT offeringid
834
834
            FROM (offering INNER JOIN subject
835
 
                ON subject.subjectid = offering.subject)
 
835
                ON subject.subjectid = offering.subject
 
836
                INNER JOIN semester
 
837
                ON semester.semesterid = offering.semesterid)
836
838
            WHERE subj_code=%s AND semester=%s AND year=%s)
837
839
        );""" % (_escape(login), _escape(subj_code), _escape(semester),
838
840
                 _escape(year))
854
856
        """
855
857
        query = """\
856
858
SELECT offering.offeringid, subj_code, subj_name, subj_short_name,
857
 
       year, semester
858
 
FROM login, enrolment, offering, subject
 
859
       semester.year, semester.semester
 
860
FROM login, enrolment, offering, subject, semester
859
861
WHERE enrolment.offeringid=offering.offeringid
860
862
  AND login.loginid=enrolment.loginid
861
863
  AND offering.subject=subject.subjectid
 
864
  AND semester.semesterid=offering.semesterid
 
865
  AND enrolment.active
862
866
  AND login=%s;""" % _escape(login)
863
867
        if dry:
864
868
            return query
878
882
        if offeringid is None:
879
883
            and_offering = ""
880
884
        else:
881
 
            and_offering = "AND project_group.offeringid = %s" % \
882
 
                            _escape(offeringid)
 
885
            and_projectset_table = ", project_set"
 
886
            and_offering = """
 
887
AND project_group.projectsetid = project_set.projectsetid
 
888
AND project_set.offeringid = %s""" % _escape(offeringid)
883
889
        # Union both the groups this user is a member of, and the groups this
884
890
        # user is invited to.
885
891
        query = """\
886
892
    SELECT project_group.groupid, groupnm, project_group.nick, True
887
 
    FROM project_group, group_member, login
 
893
    FROM project_group, group_member, login %(and_projectset_table)s
888
894
    WHERE project_group.groupid = group_member.groupid
889
895
      AND group_member.loginid = login.loginid
890
896
      AND login = %(login)s
891
897
      %(and_offering)s
892
898
UNION
893
899
    SELECT project_group.groupid, groupnm, project_group.nick, False
894
 
    FROM project_group, group_invitation, login
 
900
    FROM project_group, group_invitation, login %(and_projectset_table)s
895
901
    WHERE project_group.groupid = group_invitation.groupid
896
902
      AND group_invitation.loginid = login.loginid
897
903
      AND login = %(login)s
898
904
      %(and_offering)s
899
 
;""" % {"login": _escape(login), "and_offering": and_offering}
 
905
;""" % {"login": _escape(login), "and_offering": and_offering,
 
906
        "and_projectset_table": and_projectset_table}
900
907
        if dry:
901
908
            return query
902
909
        # Convert 't' -> True, 'f' -> False