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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
# IVLE - Informatics Virtual Learning Environment
# Copyright (C) 2007-2008 The University of Melbourne
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA

# Module: Database
# Author: Matt Giuca
# Date:   15/2/2008

# Code to talk to the PostgreSQL database.
# (This is the Data Access Layer).
# All DB code should be in this module to ensure portability if we want to
# change the DB implementation.
# This means no SQL strings should be outside of this module. Add functions
# here to perform the activities needed, and place the SQL code for those
# activities within.

# CAUTION to editors of this module.
# All string inputs must be sanitized by calling _escape before being
# formatted into an SQL query string.

import pg
import conf
import md5
import copy
import time

from common import (caps, user)

TIMESTAMP_FORMAT = '%Y-%m-%d %H:%M:%S'

def _escape(val):
    """Wrapper around pg.escape_string. Prepares the Python value for use in
    SQL. Returns a string, which may be safely placed verbatim into an SQL
    query.
    Handles the following types:
    * str: Escapes the string, and also quotes it.
    * int/long/float: Just converts to an unquoted string.
    * bool: Returns as "TRUE" or "FALSE", unquoted.
    * NoneType: Returns "NULL", unquoted.
    * common.caps.Role: Returns the role as a quoted, lowercase string.
    * time.struct_time: Returns the time as a quoted string for insertion into
        a TIMESTAMP column.
    Raises a DBException if val has an unsupported type.
    """
    # "E'" is postgres's way of making "escape" strings.
    # Such strings allow backslashes to escape things. Since escape_string
    # converts a single backslash into two backslashes, it needs to be fed
    # into E mode.
    # Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
    # WARNING: PostgreSQL-specific code
    if val is None:
        return "NULL"
    elif isinstance(val, str) or isinstance(val, unicode):
        return "E'" + pg.escape_string(val) + "'"
    elif isinstance(val, bool):
        return "TRUE" if val else "FALSE"
    elif isinstance(val, int) or isinstance(val, long) \
        or isinstance(val, float):
        return str(val)
    elif isinstance(val, caps.Role):
        return _escape(str(val))
    elif isinstance(val, time.struct_time):
        return _escape(time.strftime(TIMESTAMP_FORMAT, val))
    else:
        raise DBException("Attempt to insert an unsupported type "
            "into the database (%s)" % repr(type(val)))

def _parse_boolean(val):
    """
    Accepts a boolean as output from the DB (either the string 't' or 'f').
    Returns a boolean value True or False.
    Also accepts other values which mean True or False in PostgreSQL.
    If none match, raises a DBException.
    """
    # On a personal note, what sort of a language allows 7 different values
    # to denote each of True and False?? (A: SQL)
    if isinstance(val, bool):
        return val
    elif val == 't':
        return True
    elif val == 'f':
        return False
    elif val == 'true' or val == 'y' or val == 'yes' or val == '1' \
        or val == 1:
        return True
    elif val == 'false' or val == 'n' or val == 'no' or val == '0' \
        or val == 0:
        return False
    else:
        raise DBException("Invalid boolean value returned from DB")

def _passhash(password):
    return md5.md5(password).hexdigest()

class DBException(Exception):
    """A DBException is for bad conditions in the database or bad input to
    these methods. If Postgres throws an exception it does not get rebadged.
    This is only for additional exceptions."""
    pass

class DB:
    """An IVLE database object. This object provides an interface to
    interacting with the IVLE database without using any external SQL.

    Most methods of this class have an optional dry argument. If true, they
    will return the SQL query string and NOT actually execute it. (For
    debugging purposes).

    Methods may throw db.DBException, or any of the pg exceptions as well.
    (In general, be prepared to catch exceptions!)
    """
    def __init__(self):
        """Connects to the database and creates a DB object.
        Takes no parameters - gets all the DB info from the configuration."""
        self.open = False
        self.db = pg.connect(dbname=conf.db_dbname, host=conf.db_host,
                port=conf.db_port, user=conf.db_user, passwd=conf.db_password)
        self.open = True

    def __del__(self):
        if self.open:
            self.db.close()

    # GENERIC DB FUNCTIONS #

    @staticmethod
    def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
        """Checks that a dict does not contain keys that are not fields
        of the specified table.
        dict: A mapping from string keys to values; the keys are checked to
            see that they correspond to login table fields.
        tablefields: Collection of strings for field names in the table.
            Only these fields will be allowed.
        disallowed: Optional collection of strings for field names that are
            not allowed.
        must: If True, the dict MUST contain all fields in tablefields.
            If False, it may contain any subset of the fields.
        Returns True if the dict is valid, False otherwise.
        """
        allowed = frozenset(tablefields) - frozenset(disallowed)
        dictkeys = frozenset(dict.keys())
        if must:
            return allowed == dictkeys
        else:
            return allowed.issuperset(dictkeys)

    def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
        dry=False):
        """Inserts a new row in a table, using data from a supplied
        dictionary (which will be checked by check_dict).
        dict: Dictionary mapping column names to values. The values may be
            any of the following types:
            str, int, long, float, NoneType.
        tablename: String, name of the table to insert into. Will NOT be
            escaped - must be a valid identifier.
        tablefields, disallowed: see check_dict.
        dry: Returns the SQL query as a string, and does not execute it.
        Raises a DBException if the dictionary contains invalid fields.
        """
        if not DB.check_dict(dict, tablefields, disallowed):
            extras = set(dict.keys()) - tablefields
            raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
        # Build two lists concurrently: field names and values, as SQL strings
        fieldnames = []
        values = []
        for k,v in dict.items():
            fieldnames.append(k)
            values.append(_escape(v))
        if len(fieldnames) == 0: return
        fieldnames = ', '.join(fieldnames)
        values = ', '.join(values)
        query = ("INSERT INTO %s (%s) VALUES (%s);"
            % (tablename, fieldnames, values))
        if dry: return query
        self.db.query(query)

    def return_insert(self, dict, tablename, tablefields, returning,
        disallowed=frozenset([]), dry=False):
        """Inserts a new row in a table, using data from a supplied
        dictionary (which will be checked by check_dict) and returns certain 
        fields as a dict.
        dict: Dictionary mapping column names to values. The values may be
            any of the following types:
            str, int, long, float, NoneType.
        tablename: String, name of the table to insert into. Will NOT be
            escaped - must be a valid identifier.
        returning: List of fields to return, not escaped
        tablefields, disallowed: see check_dict.
        dry: Returns the SQL query as a string, and does not execute it.
        Raises a DBException if the dictionary contains invalid fields.
        """
        if not DB.check_dict(dict, tablefields, disallowed):
            extras = set(dict.keys()) - tablefields
            raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
        # Build two lists concurrently: field names and values, as SQL strings
        fieldnames = []
        values = []
        for k,v in dict.items():
            fieldnames.append(k)
            values.append(_escape(v))
        if len(fieldnames) == 0: return
        fieldnames = ', '.join(fieldnames)
        values = ', '.join(values)
        returns = ', '.join(returning)
        query = ("INSERT INTO %s (%s) VALUES (%s) RETURNING (%s);"
            % (tablename, fieldnames, values, returns))
        if dry: return query
        return self.db.query(query)


    def update(self, primarydict, updatedict, tablename, tablefields,
        primary_keys, disallowed_update=frozenset([]), dry=False):
        """Updates a row in a table, matching against primarydict to find the
        row, and using the data in updatedict (which will be checked by
        check_dict).
        primarydict: Dict mapping column names to values. The keys should be
            the table's primary key. Only rows which match this dict's values
            will be updated.
        updatedict: Dict mapping column names to values. The columns will be
            updated with the given values for the matched rows.
        tablename, tablefields, disallowed_update: See insert.
        primary_keys: Collection of strings which together form the primary
            key for this table. primarydict must contain all of these as keys,
            and only these keys.
        """
        if (not (DB.check_dict(primarydict, primary_keys, must=True)
            and DB.check_dict(updatedict, tablefields, disallowed_update))):
            raise DBException("Supplied dictionary contains invalid or missing fields (1).")
        # Make a list of SQL fragments of the form "field = 'new value'"
        # These fragments are ALREADY-ESCAPED
        setlist = []
        for k,v in updatedict.items():
            setlist.append("%s = %s" % (k, _escape(v)))
        wherelist = []
        for k,v in primarydict.items():
            wherelist.append("%s = %s" % (k, _escape(v)))
        if len(setlist) == 0 or len(wherelist) == 0:
            return
        # Join the fragments into a comma-separated string
        setstring = ', '.join(setlist)
        wherestring = ' AND '.join(wherelist)
        # Build the whole query as an UPDATE statement
        query = ("UPDATE %s SET %s WHERE %s;"
            % (tablename, setstring, wherestring))
        if dry: return query
        self.db.query(query)

    def delete(self, primarydict, tablename, primary_keys, dry=False):
        """Deletes a row in the table, matching against primarydict to find
        the row.
        primarydict, tablename, primary_keys: See update.
        """
        if not DB.check_dict(primarydict, primary_keys, must=True):
            raise DBException("Supplied dictionary contains invalid or missing fields (2).")
        wherelist = []
        for k,v in primarydict.items():
            wherelist.append("%s = %s" % (k, _escape(v)))
        if len(wherelist) == 0:
            return
        wherestring = ' AND '.join(wherelist)
        query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
        if dry: return query
        self.db.query(query)

    def get_single(self, primarydict, tablename, getfields, primary_keys,
        error_notfound="No rows found", dry=False):
        """Retrieves a single row from a table, returning it as a dictionary
        mapping field names to values. Matches against primarydict to find the
        row.
        primarydict, tablename, primary_keys: See update/delete.
        getfields: Collection of strings; the field names which will be
            returned as keys in the dictionary.
        error_notfound: Error message if 0 rows match.
        Raises a DBException if 0 rows match, with error_notfound as the msg.
        Raises an AssertError if >1 rows match (this should not happen if
            primary_keys is indeed the primary key).
        """
        if not DB.check_dict(primarydict, primary_keys, must=True):
            raise DBException("Supplied dictionary contains invalid or missing fields (3).")
        wherelist = []
        for k,v in primarydict.items():
            wherelist.append("%s = %s" % (k, _escape(v)))
        if len(getfields) == 0 or len(wherelist) == 0:
            return
        # Join the fragments into a comma-separated string
        getstring = ', '.join(getfields)
        wherestring = ' AND '.join(wherelist)
        # Build the whole query as an SELECT statement
        query = ("SELECT %s FROM %s WHERE %s;"
            % (getstring, tablename, wherestring))
        if dry: return query
        result = self.db.query(query)
        # Expecting exactly one
        if result.ntuples() != 1:
            # It should not be possible for ntuples to be greater than 1
            assert (result.ntuples() < 1)
            raise DBException(error_notfound)
        # Return as a dictionary
        return result.dictresult()[0]

    def get_all(self, tablename, getfields, dry=False):
        """Retrieves all rows from a table, returning it as a list of
        dictionaries mapping field names to values.
        tablename, getfields: See get_single.
        """
        if len(getfields) == 0:
            return
        getstring = ', '.join(getfields)
        query = ("SELECT %s FROM %s;" % (getstring, tablename))
        if dry: return query
        return self.db.query(query).dictresult()

    def start_transaction(self, dry=False):
        """Starts a DB transaction.
        Will not commit any changes until self.commit() is called.
        """
        query = "START TRANSACTION;"
        if dry: return query
        self.db.query(query)

    def commit(self, dry=False):
        """Commits (ends) a DB transaction.
        Commits all changes since the call to start_transaction.
        """
        query = "COMMIT;"
        if dry: return query
        self.db.query(query)

    def rollback(self, dry=False):
        """Rolls back (ends) a DB transaction, undoing all changes since the
        call to start_transaction.
        """
        query = "ROLLBACK;"
        if dry: return query
        self.db.query(query)

    # USER MANAGEMENT FUNCTIONS #

    login_primary = frozenset(["login"])
    login_fields_list = [
        "login", "passhash", "state", "unixid", "email", "nick", "fullname",
        "rolenm", "studentid", "acct_exp", "pass_exp", "last_login", "svn_pass"
    ]
    login_fields = frozenset(login_fields_list)

    def create_user(self, user_obj=None, dry=False, **kwargs):
        """Creates a user login entry in the database.
        Two ways to call this - passing a user object, or passing
        all fields as separate arguments.

        Either pass a "user_obj" as the first argument (in which case other
        fields will be ignored), or pass all fields as arguments.

        All user fields are to be passed as args. The argument names
        are the field names of the "login" table of the DB schema.
        However, instead of supplying a "passhash", you must supply a
        "password" argument, which will be hashed internally.
        Also "state" must not given explicitly; it is implicitly set to
        "no_agreement".
        Raises an exception if the user already exists, or the dict contains
        invalid keys or is missing required keys.
        """
        if 'passhash' in kwargs:
            raise DBException("Supplied arguments include passhash (invalid) (1).")
        # Make a copy of the dict. Change password to passhash (hashing it),
        # and set 'state' to "no_agreement".
        if user_obj is None:
            # Use the kwargs
            fields = copy.copy(kwargs)
        else:
            # Use the user object
            fields = dict(user_obj)
        if 'password' in fields:
            fields['passhash'] = _passhash(fields['password'])
            del fields['password']
        if 'role' in fields:
            # Convert role to rolenm
            fields['rolenm'] = str(user_obj.role)
            del fields['role']
        if user_obj is None:
            fields['state'] = "no_agreement"
            # else, we'll trust the user, but it SHOULD be "no_agreement"
            # (We can't change it because then the user object would not
            # reflect the DB).
        if 'local_password' in fields:
            del fields['local_password']
        # Execute the query.
        return self.insert(fields, "login", self.login_fields, dry=dry)

    def update_user(self, login, dry=False, **kwargs):
        """Updates fields of a particular user. login is the name of the user
        to update. The dict contains the fields which will be modified, and
        their new values. If any value is omitted from the dict, it does not
        get modified. login and studentid may not be modified.
        Passhash may be modified by supplying a "password" field, in
        cleartext, not a hashed password.

        Note that no checking is done. It is expected this function is called
        by a trusted source. In particular, it allows the password to be
        changed without knowing the old password. The caller should check
        that the user knows the existing password before calling this function
        with a new one.
        """
        if 'passhash' in kwargs:
            raise DBException("Supplied arguments include passhash (invalid) (2).")
        if "password" in kwargs:
            kwargs = copy.copy(kwargs)
            kwargs['passhash'] = _passhash(kwargs['password'])
            del kwargs['password']
        return self.update({"login": login}, kwargs, "login",
            self.login_fields, self.login_primary, ["login", "studentid"],
            dry=dry)

    def get_user(self, login, dry=False):
        """Given a login, returns a User object containing details looked up
        in the DB.

        Raises a DBException if the login is not found in the DB.
        """
        userdict = self.get_single({"login": login}, "login",
            self.login_fields, self.login_primary,
            error_notfound="get_user: No user with that login name", dry=dry)
        if dry:
            return userdict     # Query string
        # Package into a User object
        return user.User(**userdict)

    def get_users(self, dry=False):
        """Returns a list of all users in the DB, as User objects.
        """
        userdicts = self.get_all("login", self.login_fields, dry=dry)
        if dry:
            return userdicts    # Query string
        # Package into User objects
        return [user.User(**userdict) for userdict in userdicts]

    def get_user_loginid(self, login, dry=False):
        """Given a login, returns the integer loginid for this user.

        Raises a DBException if the login is not found in the DB.
        """
        userdict = self.get_single({"login": login}, "login",
            ['loginid'], self.login_primary,
            error_notfound="get_user_loginid: No user with that login name",
            dry=dry)
        if dry:
            return userdict     # Query string
        return userdict['loginid']

    def user_authenticate(self, login, password, dry=False):
        """Performs a password authentication on a user. Returns True if
        "passhash" is the correct passhash for the given login, False
        if the passhash does not match the password in the DB,
        and None if the passhash in the DB is NULL.
        Also returns False if the login does not exist (so if you want to
        differentiate these cases, use get_user and catch an exception).
        """
        query = ("SELECT passhash FROM login WHERE login = %s;"
            % _escape(login))
        if dry: return query
        result = self.db.query(query)
        if result.ntuples() == 1:
            # Valid username. Check password.
            passhash = result.getresult()[0][0]
            if passhash is None:
                return None
            return _passhash(password) == passhash
        else:
            return False

    # PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #

    def get_problem_problemid(self, exercisename, dry=False):
        """Given an exercise name, returns the associated problemID.
        If the exercise name is NOT in the database, it inserts it and returns
        the new problemID. Hence this may mutate the DB, but is idempotent.
        """
        try:
            d = self.get_single({"identifier": exercisename}, "problem",
                ['problemid'], frozenset(["identifier"]),
                dry=dry)
            if dry:
                return d        # Query string
        except DBException:
            if dry:
                # Shouldn't try again, must have failed for some other reason
                raise
            # if we failed to get a problemid, it was probably because
            # the exercise wasn't in the db. So lets insert it!
            #
            # The insert can fail if someone else simultaneously does
            # the insert, so if the insert fails, we ignore the problem. 
            try:
                self.insert({'identifier': exercisename}, "problem",
                        frozenset(['identifier']))
            except Exception, e:
                pass

            # Assuming the insert succeeded, we should be able to get the
            # problemid now.
            d = self.get_single({"identifier": exercisename}, "problem",
                ['problemid'], frozenset(["identifier"]))

        return d['problemid']

    def insert_problem_attempt(self, login, exercisename, date, complete,
        attempt, dry=False):
        """Inserts the details of a problem attempt into the database.
        exercisename: Name of the exercise. (identifier field of problem
            table). If this exercise does not exist, also creates a new row in
            the problem table for this exercise name.
        login: Name of the user submitting the attempt. (login field of the
            login table).
        date: struct_time, the date this attempt was made.
        complete: bool. Whether the test passed or not.
        attempt: Text of the attempt.

        Note: Even if dry, will still physically call get_problem_problemid,
        which may mutate the DB, and get_user_loginid, which may fail.
        """
        problemid = self.get_problem_problemid(exercisename)
        loginid = self.get_user_loginid(login)  # May raise a DBException

        return self.insert({
                'problemid': problemid,
                'loginid': loginid,
                'date': date,
                'complete': complete,
                'attempt': attempt,
            }, 'problem_attempt',
            frozenset(['problemid','loginid','date','complete','attempt']),
            dry=dry)

    def write_problem_save(self, login, exercisename, date, text, dry=False):
        """Writes text to the problem_save table (for when the user saves an
        exercise). Creates a new row, or overwrites an existing one if the
        user has already saved that problem.
        (Unlike problem_attempt, does not keep historical records).
        """
        problemid = self.get_problem_problemid(exercisename)
        loginid = self.get_user_loginid(login)  # May raise a DBException

        try:
            return self.insert({
                    'problemid': problemid,
                    'loginid': loginid,
                    'date': date,
                    'text': text,
                }, 'problem_save',
                frozenset(['problemid','loginid','date','text']),
                dry=dry)
        except pg.ProgrammingError:
            # May have failed because this problemid/loginid row already
            # exists (they have a unique key constraint).
            # Do an update instead.
            if dry:
                # Shouldn't try again, must have failed for some other reason
                raise
            self.update({
                    'problemid': problemid,
                    'loginid': loginid,
                },
                {
                    'date': date,
                    'text': text,
                }, "problem_save",
                frozenset(['date', 'text']),
                frozenset(['problemid', 'loginid']))

    def get_problem_stored_text(self, login, exercisename, dry=False):
        """Given a login name and exercise name, returns the text of the
        last saved/submitted attempt for this question.
        Returns None if the user has not saved or made an attempt on this
        problem.
        (If the user has both saved and submitted, it returns whichever was
        made last).

        Note: Even if dry, will still physically call get_problem_problemid,
        which may mutate the DB, and get_user_loginid, which may fail.
        """
        problemid = self.get_problem_problemid(exercisename)
        loginid = self.get_user_loginid(login)  # May raise a DBException
        # This very complex query finds all submissions made by this user for
        # this problem, as well as the save made by this user for this
        # problem, and returns the text of the newest one.
        # (Whichever is newer out of the save or the submit).
        query = """SELECT text FROM
    (
        (SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)
    UNION
        (SELECT problemid, loginid, date, text FROM problem_attempt
         AS problem_attempt (problemid, loginid, date, text)
         WHERE loginid = %d AND problemid = %d AND active)
    )
    AS _
    ORDER BY date DESC
    LIMIT 1;""" % (loginid, problemid, loginid, problemid)
        if dry: return query
        result = self.db.query(query)
        if result.ntuples() == 1:
            # The user has made at least 1 attempt. Return the newest.
            return result.getresult()[0][0]
        else:
            return None

    def get_problem_attempts(self, login, exercisename, allow_inactive=True,
                             dry=False):
        """Given a login name and exercise name, returns a list of dicts, one
        for each attempt made for that exercise.
        Dicts are {'date': 'formatted_time', 'complete': bool}.
        Ordered with the newest first.
        
        Note: By default, returns de-activated problem attempts (unlike
        get_problem_stored_text).
        If allow_inactive is False, will not return disabled attempts.

        Note: Even if dry, will still physically call get_problem_problemid,
        which may mutate the DB, and get_user_loginid, which may fail.
        """
        problemid = self.get_problem_problemid(exercisename)
        loginid = self.get_user_loginid(login)  # May raise a DBException
        andactive = '' if allow_inactive else ' AND active'
        query = """SELECT date, complete FROM problem_attempt
    WHERE loginid = %d AND problemid = %d%s
    ORDER BY date DESC;""" % (loginid, problemid, andactive)
        if dry: return query
        result = self.db.query(query).getresult()
        # Make into dicts (could use dictresult, but want to convert values)
        return [{'date': date, 'complete': _parse_boolean(complete)}
                for date, complete in result]

    def get_problem_attempt(self, login, exercisename, as_of,
        allow_inactive=True, dry=False):
        """Given a login name, exercise name, and struct_time, returns the
        text of the submitted attempt for this question as of that date.
        Returns None if the user had not made an attempt on this problem at
        that date.
        
        Note: By default, returns de-activated problem attempts (unlike
        get_problem_stored_text).
        If allow_inactive is False, will not return disabled attempts.

        Note: Even if dry, will still physically call get_problem_problemid,
        which may mutate the DB, and get_user_loginid, which may fail.
        """
        problemid = self.get_problem_problemid(exercisename)
        loginid = self.get_user_loginid(login)  # May raise a DBException
        # Very similar to query in get_problem_stored_text, but without
        # looking in problem_save, and restricting to a certain date.
        andactive = '' if allow_inactive else ' AND active'
        query = """SELECT attempt FROM problem_attempt
    WHERE loginid = %d AND problemid = %d%s AND date <= %s
    ORDER BY date DESC
    LIMIT 1;""" % (loginid, problemid, andactive, _escape(as_of))
        if dry: return query
        result = self.db.query(query)
        if result.ntuples() == 1:
            # The user has made at least 1 attempt. Return the newest.
            return result.getresult()[0][0]
        else:
            return None

    def get_problem_status(self, login, exercisename, dry=False):
        """Given a login name and exercise name, returns information about the
        user's performance on that problem.
        Returns a tuple of:
            - A boolean, whether they have successfully passed this exercise.
            - An int, the number of attempts they have made up to and
              including the first successful attempt (or the total number of
              attempts, if not yet successful).
        Note: exercisename may be an int, in which case it will be directly
        used as the problemid.
        """
        if isinstance(exercisename, int):
            problemid = exercisename
        else:
            problemid = self.get_problem_problemid(exercisename)
        loginid = self.get_user_loginid(login)  # May raise a DBException

        # ASSUME that it is completed, get the total number of attempts up to
        # and including the first successful attempt.
        # (Get the date of the first successful attempt. Then count the number
        # of attempts made <= that date).
        # Will return an empty table if the problem has never been
        # successfully completed.
        query = """SELECT COUNT(*) FROM problem_attempt
    WHERE loginid = %d AND problemid = %d AND active AND date <=
        (SELECT date FROM problem_attempt
            WHERE loginid = %d AND problemid = %d AND complete AND active
            ORDER BY date ASC
            LIMIT 1);""" % (loginid, problemid, loginid, problemid)
        if dry: return query
        result = self.db.query(query)
        count = int(result.getresult()[0][0])
        if count > 0:
            # The user has made at least 1 successful attempt.
            # Return True for success, and the number of attempts up to and
            # including the successful one.
            return (True, count)
        else:
            # Returned 0 rows - this indicates that the problem has not been
            # completed.
            # Return the total number of attempts, and False for success.
            query = """SELECT COUNT(*) FROM problem_attempt
    WHERE loginid = %d AND problemid = %d AND active;""" % (loginid, problemid)
            result = self.db.query(query)
            count = int(result.getresult()[0][0])
            return (False, count)

    # WORKSHEET/PROBLEM ASSOCIATION AND MARKS CALCULATION

    def get_worksheet_mtime(self, subject, worksheet, dry=False):
        """
        For a given subject/worksheet name, gets the time the worksheet was
        last updated in the DB, if any.
        This can be used to check if there is a newer version on disk.
        Returns the timestamp as a time.struct_time, or None if the worksheet
        is not found or has no stored mtime.
        """
        try:
            r = self.get_single(
                {"subject": subject, "identifier": worksheet},
                "worksheet", ["mtime"], ["subject", "identifier"],
                dry=dry)
        except DBException:
            # Assume the worksheet is not in the DB
            return None
        if dry:
            return r
        if r["mtime"] is None:
            return None
        return time.strptime(r["mtime"], TIMESTAMP_FORMAT)

    def create_worksheet(self, subject, worksheet, problems=None,
        assessable=None):
        """
        Inserts or updates rows in the worksheet and worksheet_problems
        tables, to create a worksheet in the database.
        This atomically performs all operations. If the worksheet is already
        in the DB, removes it and all its associated problems and rebuilds.
        Sets the timestamp to the current time.

        problems is a collection of pairs. The first element of the pair is
        the problem identifier ("identifier" column of the problem table). The
        second element is an optional boolean, "optional". This can be omitted
        (so it's a 1-tuple), and then it will default to False.

        Problems and assessable are optional, and if omitted, will not change
        the existing data. If the worksheet does not yet exist, and assessable
        is omitted, it defaults to False.

        Note: As with get_problem_problemid, if a problem name is not in the
        DB, it will be added to the problem table.
        """
        self.start_transaction()
        try:
            # Use the current time as the "mtime" field
            mtime = time.localtime()
            try:
                # Get the worksheetid
                r = self.get_single(
                    {"subject": subject, "identifier": worksheet},
                    "worksheet", ["worksheetid"], ["subject", "identifier"])
                worksheetid = r["worksheetid"]

                # Delete any problems which might exist, if problems is
                # supplied. If it isn't, keep the existing ones.
                if problems is not None:
                    query = ("DELETE FROM worksheet_problem "
                        "WHERE worksheetid = %d;" % worksheetid)
                    self.db.query(query)
                # Update the row with the new details
                if assessable is None:
                    query = ("UPDATE worksheet "
                        "SET mtime = %s WHERE worksheetid = %d;"
                        % (_escape(mtime), worksheetid))
                else:
                    query = ("UPDATE worksheet "
                        "SET assessable = %s, mtime = %s "
                        "WHERE worksheetid = %d;"
                        % (_escape(assessable), _escape(mtime), worksheetid))
                self.db.query(query)
            except DBException:
                # Assume the worksheet is not in the DB
                # If assessable is not supplied, default to False.
                if assessable is None:
                    assessable = False
                # Create the worksheet row
                query = ("INSERT INTO worksheet "
                    "(subject, identifier, assessable, mtime) "
                    "VALUES (%s, %s, %s, %s);"""
                    % (_escape(subject), _escape(worksheet),
                    _escape(assessable), _escape(mtime)))
                self.db.query(query)
                # Now get the worksheetid again - should succeed
                r = self.get_single(
                    {"subject": subject, "identifier": worksheet},
                    "worksheet", ["worksheetid"], ["subject", "identifier"])
                worksheetid = r["worksheetid"]

            # Now insert each problem into the worksheet_problem table
            if problems is not None:
                for problem in problems:
                    if isinstance(problem, tuple):
                        prob_identifier = problem[0]
                        try:
                            optional = problem[1]
                        except IndexError:
                            optional = False
                    else:
                        prob_identifier = problem
                        optional = False
                    problemid = self.get_problem_problemid(prob_identifier)
                    query = ("INSERT INTO worksheet_problem "
                        "(worksheetid, problemid, optional) "
                        "VALUES (%d, %d, %s);"
                        % (worksheetid, problemid, _escape(optional)))
                    self.db.query(query)

            self.commit()
        except:
            self.rollback()
            raise

    def set_worksheet_assessable(self, subject, worksheet, assessable,
        dry=False):
        """
        Sets the "assessable" field of a worksheet without updating the mtime.

        IMPORTANT: This will NOT update the mtime. This is designed to allow
        updates which did not come from the worksheet XML file. It would be
        bad to update the mtime without consulting the XML file because then
        it would appear the database is up to date, when it isn't.

        Therefore, call this method if you are getting "assessable"
        information from outside the worksheet XML file (eg. from the subject
        XML file).

        Unlike create_worksheet, raises a DBException if the worksheet is not
        in the database.
        """
        return self.update({"subject": subject, "identifier": worksheet},
            {"assessable": assessable}, "worksheet", ["assessable"],
            ["subject", "identifier"], dry=dry)

    def worksheet_is_assessable(self, subject, worksheet, dry=False):
        r = self.get_single(
            {"subject": subject, "identifier": worksheet},
            "worksheet", ["assessable"], ["subject", "identifier"], dry=dry)
        return _parse_boolean(r["assessable"])

    def calculate_score_worksheet(self, login, subject, worksheet):
        """
        Calculates the score for a user on a given worksheet.
        Returns a 4-tuple of ints, consisting of:
        (No. mandatory exercises completed,
         Total no. mandatory exercises,
         No. optional exercises completed,
         Total no. optional exercises)
        """
        self.start_transaction()
        try:
            mand_done = 0
            mand_total = 0
            opt_done = 0
            opt_total = 0
            # Get a list of problems and optionality for all problems in the
            # worksheet
            query = ("""SELECT problemid, optional FROM worksheet_problem
    WHERE worksheetid = (SELECT worksheetid FROM worksheet
                         WHERE subject = %s and identifier = %s);"""
                    % (_escape(subject), _escape(worksheet)))
            result = self.db.query(query)
            # Now get the student's pass/fail for each problem in this worksheet
            for problemid, optional in result.getresult():
                done, _ = self.get_problem_status(login, problemid)
                # done is a bool, whether this student has completed that
                # problem
                if _parse_boolean(optional):
                    opt_total += 1
                    if done: opt_done += 1
                else:
                    mand_total += 1
                    if done: mand_done += 1
            self.commit()
        except:
            self.rollback()
            raise
        return mand_done, mand_total, opt_done, opt_total

    # ENROLMENT INFORMATION

    def add_enrolment(self, login, subj_code, semester, year=None, dry=False):
        """
        Enrol a student in the given offering of a subject.
        Returns True on success, False on failure (which usually means either
        the student is already enrolled in the subject, the student was not
        found, or no offering existed with the given details).
        The return value can usually be ignored.
        """
        subj_code = str(subj_code)
        semester = str(semester)
        if year is None:
            year = str(time.gmtime().tm_year)
        else:
            year = str(year)
        query = """\
INSERT INTO enrolment (loginid, offeringid)
    VALUES (
        (SELECT loginid FROM login WHERE login=%s),
        (SELECT offeringid
            FROM offering, subject, semester
                WHERE subject.subjectid = offering.subject
                AND semester.semesterid = offering.semesterid
                AND subj_code=%s AND semester=%s AND year=%s)
        );""" % (_escape(login), _escape(subj_code), _escape(semester),
                 _escape(year))
        if dry:
            return query
        try:
            result = self.db.query(query)
        except pg.ProgrammingError:
            return False
        return True

    # SUBJECTS AND ENROLEMENT

    def get_subjects(self, dry=False):
        """
        Get all subjects in IVLE.
        Returns a list of dicts (all values strings), with the keys:
        subj_code, subj_name, subj_short_name, url
        """
        return self.get_all("subject",
            ("subjectid", "subj_code", "subj_name", "subj_short_name", "url"),
            dry)

    def get_offering_semesters(self, subjectid, dry=False):
        """
        Get the semester information for a subject as well as providing 
        information about if the subject is active and which semester it is in.
        """
        query = """\
SELECT offeringid, subj_name, year, semester, active
FROM semester, offering, subject
WHERE offering.semesterid = semester.semesterid AND
    offering.subject = subject.subjectid AND
    offering.subject = %d;"""%subjectid
        if dry:
            return query
        results = self.db.query(query).dictresult()
        # Parse boolean varibles
        for result in results:
            result['active'] = _parse_boolean(result['active'])
        return results

    def get_offering_members(self, offeringid, dry=False):
        """
        Gets the logins of all the people enroled in an offering
        """
        query = """\
SELECT login.login AS login, login.fullname AS fullname
FROM login, enrolment
WHERE login.loginid = enrolment.loginid AND
    enrolment.offeringid = %d;"""%offeringid
        if dry:
            return query
        return self.db.query(query).dictresult()


    def get_enrolment(self, login, dry=False):
        """
        Get all offerings (in IVLE) the student is enrolled in.
        Returns a list of dicts (all values strings), with the keys:
        offeringid, subj_code, subj_name, subj_short_name, year, semester, url
        """
        query = """\
SELECT offering.offeringid, subj_code, subj_name, subj_short_name,
       semester.year, semester.semester, subject.url
FROM login, enrolment, offering, subject, semester
WHERE enrolment.offeringid=offering.offeringid
  AND login.loginid=enrolment.loginid
  AND offering.subject=subject.subjectid
  AND semester.semesterid=offering.semesterid
  AND enrolment.active
  AND login=%s;""" % _escape(login)
        if dry:
            return query
        return self.db.query(query).dictresult()

    def get_enrolment_groups(self, login, offeringid, dry=False):
        """
        Get all groups the user is member of in the given offering.
        Returns a list of dicts (all values strings), with the keys:
        name, nick
        """
        query = """\
SELECT project_group.groupnm as name, project_group.nick as nick
FROM project_set, project_group, group_member, login
WHERE login.login=%s
  AND project_set.offeringid=%s
  AND group_member.loginid=login.loginid
  AND project_group.groupid=group_member.groupid
  AND project_group.projectsetid=project_set.projectsetid
""" % (_escape(login), _escape(offeringid))
        if dry:
            return query
        return self.db.query(query).dictresult()

    def get_subjects_status(self, login, dry=False):
        """
        Get all subjects in IVLE, split into lists of enrolled and unenrolled
        subjects.
        Returns a tuple of lists (enrolled, unenrolled) of dicts
        (all values strings) with the keys:
        subj_code, subj_name, subj_short_name, url
        """
        enrolments = self.get_enrolment(login)
        all_subjects = self.get_subjects()

        enrolled_set = set(x['subj_code'] for x in enrolments)
 
        enrolled_subjects = [x for x in all_subjects
                             if x['subj_code'] in enrolled_set]
        unenrolled_subjects = [x for x in all_subjects
                               if x['subj_code'] not in enrolled_set]
        enrolled_subjects.sort(key=lambda x: x['subj_code'])
        unenrolled_subjects.sort(key=lambda x: x['subj_code'])
        return (enrolled_subjects, unenrolled_subjects)


    # PROJECT GROUPS
    def get_groups_by_user(self, login, offeringid=None, dry=False):
        """
        Get all project groups the student is in, corresponding to a
        particular subject offering (or all offerings, if omitted).
        Returns a list of tuples:
        (int groupid, str groupnm, str group_nick, bool is_member).
        (Note: If is_member is false, it means they have just been invited to
        this group, not a member).
        """
        if offeringid is None:
            and_offering = ""
        else:
            and_projectset_table = ", project_set"
            and_offering = """
AND project_group.projectsetid = project_set.projectsetid
AND project_set.offeringid = %s""" % _escape(offeringid)
        # Union both the groups this user is a member of, and the groups this
        # user is invited to.
        query = """\
    SELECT project_group.groupid, groupnm, project_group.nick, True
    FROM project_group, group_member, login %(and_projectset_table)s
    WHERE project_group.groupid = group_member.groupid
      AND group_member.loginid = login.loginid
      AND login = %(login)s
      %(and_offering)s
UNION
    SELECT project_group.groupid, groupnm, project_group.nick, False
    FROM project_group, group_invitation, login %(and_projectset_table)s
    WHERE project_group.groupid = group_invitation.groupid
      AND group_invitation.loginid = login.loginid
      AND login = %(login)s
      %(and_offering)s
;""" % {"login": _escape(login), "and_offering": and_offering,
        "and_projectset_table": and_projectset_table}
        if dry:
            return query
        # Convert 't' -> True, 'f' -> False
        return [(groupid, groupnm, nick, ismember == 't')
                for groupid, groupnm, nick, ismember
                in self.db.query(query).getresult()]

    def get_offering_info(self, projectsetid, dry=False):
        """Takes information from projectset and returns useful information 
        about the subject and semester. Returns as a dictionary.
        """
        query = """\
SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester, 
active
FROM subject, offering, semester, project_set
WHERE offering.subject = subject.subjectid AND
    offering.semesterid = semester.semesterid AND
    project_set.offeringid = offering.offeringid AND
    project_set.projectsetid = %d;"""%projectsetid
        if dry:
            return query
        return self.db.query(query).dictresult()[0]

    def get_projectgroup_members(self, groupid, dry=False):
        """Returns the logins of all students in a project group
        """
        query = """\
SELECT login.login as login, login.fullname as fullname
FROM login, group_member
WHERE login.loginid = group_member.loginid AND
    group_member.groupid = %d
ORDER BY login.login;"""%groupid
        if dry:
            return query
        return self.db.query(query).dictresult()

    def get_projectsets_by_offering(self, offeringid, dry=False):
        """Returns all the projectsets in a particular offering"""
        query = """\
SELECT projectsetid, max_students_per_group
FROM project_set
WHERE project_set.offeringid = %d;"""%offeringid
        if dry:
            return query
        return self.db.query(query).dictresult()

    def get_groups_by_projectset(self, projectsetid, dry=False):
        """Returns all the groups that are in a particular projectset"""
        query = """\
SELECT groupid, groupnm, nick, createdby, epoch
FROM project_group
WHERE project_group.projectsetid = %d;"""%projectsetid
        if dry:
            return query
        return self.db.query(query).dictresult()

    def close(self):
        """Close the DB connection. Do not call any other functions after
        this. (The behaviour of doing so is undefined).
        """
        self.db.close()
        self.open = False