← Back to branch summary

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

« back to all changes in this revision

Viewing changes to ivle/db.py

Added module ivle.config, which takes care of some work interfacing with
    configobj, including searching for the file and opening the object.
ivle.conf.conf now uses this instead of having its own search.

Show diffs side-by-side

added added

removed removed

Lines of Context:
ivle/db.py
1
 
# IVLE - Informatics Virtual Learning Environment
2
 
# Copyright (C) 2007-2008 The University of Melbourne
3
 
#
4
 
# This program is free software; you can redistribute it and/or modify
5
 
# it under the terms of the GNU General Public License as published by
6
 
# the Free Software Foundation; either version 2 of the License, or
7
 
# (at your option) any later version.
8
 
#
9
 
# This program is distributed in the hope that it will be useful,
10
 
# but WITHOUT ANY WARRANTY; without even the implied warranty of
11
 
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
12
 
# GNU General Public License for more details.
13
 
#
14
 
# You should have received a copy of the GNU General Public License
15
 
# along with this program; if not, write to the Free Software
16
 
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
17
 
 
18
 
# Module: Database
19
 
# Author: Matt Giuca
20
 
# Date:   15/2/2008
21
 
 
22
 
# Code to talk to the PostgreSQL database.
23
 
# (This is the Data Access Layer).
24
 
# All DB code should be in this module to ensure portability if we want to
25
 
# change the DB implementation.
26
 
# This means no SQL strings should be outside of this module. Add functions
27
 
# here to perform the activities needed, and place the SQL code for those
28
 
# activities within.
29
 
 
30
 
# CAUTION to editors of this module.
31
 
# All string inputs must be sanitized by calling _escape before being
32
 
# formatted into an SQL query string.
33
 
 
34
 
import pg
35
 
import md5
36
 
import copy
37
 
import time
38
 
 
39
 
import ivle.conf
40
 
from ivle import caps
41
 
 
42
 
TIMESTAMP_FORMAT = '%Y-%m-%d %H:%M:%S'
43
 
 
44
 
def _escape(val):
45
 
    """Wrapper around pg.escape_string. Prepares the Python value for use in
46
 
    SQL. Returns a string, which may be safely placed verbatim into an SQL
47
 
    query.
48
 
    Handles the following types:
49
 
    * str: Escapes the string, and also quotes it.
50
 
    * int/long/float: Just converts to an unquoted string.
51
 
    * bool: Returns as "TRUE" or "FALSE", unquoted.
52
 
    * NoneType: Returns "NULL", unquoted.
53
 
    * common.caps.Role: Returns the role as a quoted, lowercase string.
54
 
    * time.struct_time: Returns the time as a quoted string for insertion into
55
 
        a TIMESTAMP column.
56
 
    Raises a DBException if val has an unsupported type.
57
 
    """
58
 
    # "E'" is postgres's way of making "escape" strings.
59
 
    # Such strings allow backslashes to escape things. Since escape_string
60
 
    # converts a single backslash into two backslashes, it needs to be fed
61
 
    # into E mode.
62
 
    # Ref: http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
63
 
    # WARNING: PostgreSQL-specific code
64
 
    if val is None:
65
 
        return "NULL"
66
 
    elif isinstance(val, str) or isinstance(val, unicode):
67
 
        return "E'" + pg.escape_string(val) + "'"
68
 
    elif isinstance(val, bool):
69
 
        return "TRUE" if val else "FALSE"
70
 
    elif isinstance(val, int) or isinstance(val, long) \
71
 
        or isinstance(val, float):
72
 
        return str(val)
73
 
    elif isinstance(val, caps.Role):
74
 
        return _escape(str(val))
75
 
    elif isinstance(val, time.struct_time):
76
 
        return _escape(time.strftime(TIMESTAMP_FORMAT, val))
77
 
    else:
78
 
        raise DBException("Attempt to insert an unsupported type "
79
 
            "into the database (%s)" % repr(type(val)))
80
 
 
81
 
def _parse_boolean(val):
82
 
    """
83
 
    Accepts a boolean as output from the DB (either the string 't' or 'f').
84
 
    Returns a boolean value True or False.
85
 
    Also accepts other values which mean True or False in PostgreSQL.
86
 
    If none match, raises a DBException.
87
 
    """
88
 
    # On a personal note, what sort of a language allows 7 different values
89
 
    # to denote each of True and False?? (A: SQL)
90
 
    if isinstance(val, bool):
91
 
        return val
92
 
    elif val == 't':
93
 
        return True
94
 
    elif val == 'f':
95
 
        return False
96
 
    elif val == 'true' or val == 'y' or val == 'yes' or val == '1' \
97
 
        or val == 1:
98
 
        return True
99
 
    elif val == 'false' or val == 'n' or val == 'no' or val == '0' \
100
 
        or val == 0:
101
 
        return False
102
 
    else:
103
 
        raise DBException("Invalid boolean value returned from DB")
104
 
 
105
 
def _passhash(password):
106
 
    return md5.md5(password).hexdigest()
107
 
 
108
 
class DBException(Exception):
109
 
    """A DBException is for bad conditions in the database or bad input to
110
 
    these methods. If Postgres throws an exception it does not get rebadged.
111
 
    This is only for additional exceptions."""
112
 
    pass
113
 
 
114
 
class DB:
115
 
    """An IVLE database object. This object provides an interface to
116
 
    interacting with the IVLE database without using any external SQL.
117
 
 
118
 
    Most methods of this class have an optional dry argument. If true, they
119
 
    will return the SQL query string and NOT actually execute it. (For
120
 
    debugging purposes).
121
 
 
122
 
    Methods may throw db.DBException, or any of the pg exceptions as well.
123
 
    (In general, be prepared to catch exceptions!)
124
 
    """
125
 
    def __init__(self):
126
 
        """Connects to the database and creates a DB object.
127
 
        Takes no parameters - gets all the DB info from the configuration."""
128
 
        self.open = False
129
 
        self.db = pg.connect(host=ivle.conf.db_host, port=ivle.conf.db_port,
130
 
                         dbname=ivle.conf.db_dbname,
131
 
                         user=ivle.conf.db_user, passwd=ivle.conf.db_password)
132
 
        self.open = True
133
 
 
134
 
    def __del__(self):
135
 
        if self.open:
136
 
            self.db.close()
137
 
 
138
 
    # GENERIC DB FUNCTIONS #
139
 
 
140
 
    @staticmethod
141
 
    def check_dict(dict, tablefields, disallowed=frozenset([]), must=False):
142
 
        """Checks that a dict does not contain keys that are not fields
143
 
        of the specified table.
144
 
        dict: A mapping from string keys to values; the keys are checked to
145
 
            see that they correspond to login table fields.
146
 
        tablefields: Collection of strings for field names in the table.
147
 
            Only these fields will be allowed.
148
 
        disallowed: Optional collection of strings for field names that are
149
 
            not allowed.
150
 
        must: If True, the dict MUST contain all fields in tablefields.
151
 
            If False, it may contain any subset of the fields.
152
 
        Returns True if the dict is valid, False otherwise.
153
 
        """
154
 
        allowed = frozenset(tablefields) - frozenset(disallowed)
155
 
        dictkeys = frozenset(dict.keys())
156
 
        if must:
157
 
            return allowed == dictkeys
158
 
        else:
159
 
            return allowed.issuperset(dictkeys)
160
 
 
161
 
    def insert(self, dict, tablename, tablefields, disallowed=frozenset([]),
162
 
        dry=False):
163
 
        """Inserts a new row in a table, using data from a supplied
164
 
        dictionary (which will be checked by check_dict).
165
 
        dict: Dictionary mapping column names to values. The values may be
166
 
            any of the following types:
167
 
            str, int, long, float, NoneType.
168
 
        tablename: String, name of the table to insert into. Will NOT be
169
 
            escaped - must be a valid identifier.
170
 
        tablefields, disallowed: see check_dict.
171
 
        dry: Returns the SQL query as a string, and does not execute it.
172
 
        Raises a DBException if the dictionary contains invalid fields.
173
 
        """
174
 
        if not DB.check_dict(dict, tablefields, disallowed):
175
 
            extras = set(dict.keys()) - tablefields
176
 
            raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
177
 
        # Build two lists concurrently: field names and values, as SQL strings
178
 
        fieldnames = []
179
 
        values = []
180
 
        for k,v in dict.items():
181
 
            fieldnames.append(k)
182
 
            values.append(_escape(v))
183
 
        if len(fieldnames) == 0: return
184
 
        fieldnames = ', '.join(fieldnames)
185
 
        values = ', '.join(values)
186
 
        query = ("INSERT INTO %s (%s) VALUES (%s);"
187
 
            % (tablename, fieldnames, values))
188
 
        if dry: return query
189
 
        self.db.query(query)
190
 
 
191
 
    def return_insert(self, dict, tablename, tablefields, returning,
192
 
        disallowed=frozenset([]), dry=False):
193
 
        """Inserts a new row in a table, using data from a supplied
194
 
        dictionary (which will be checked by check_dict) and returns certain 
195
 
        fields as a dict.
196
 
        dict: Dictionary mapping column names to values. The values may be
197
 
            any of the following types:
198
 
            str, int, long, float, NoneType.
199
 
        tablename: String, name of the table to insert into. Will NOT be
200
 
            escaped - must be a valid identifier.
201
 
        returning: List of fields to return, not escaped
202
 
        tablefields, disallowed: see check_dict.
203
 
        dry: Returns the SQL query as a string, and does not execute it.
204
 
        Raises a DBException if the dictionary contains invalid fields.
205
 
        """
206
 
        if not DB.check_dict(dict, tablefields, disallowed):
207
 
            extras = set(dict.keys()) - tablefields
208
 
            raise DBException("Supplied dictionary contains invalid fields. (%s)" % (repr(extras)))
209
 
        # Build two lists concurrently: field names and values, as SQL strings
210
 
        fieldnames = []
211
 
        values = []
212
 
        for k,v in dict.items():
213
 
            fieldnames.append(k)
214
 
            values.append(_escape(v))
215
 
        if len(fieldnames) == 0: return
216
 
        fieldnames = ', '.join(fieldnames)
217
 
        values = ', '.join(values)
218
 
        returns = ', '.join(returning)
219
 
        query = ("INSERT INTO %s (%s) VALUES (%s) RETURNING (%s);"
220
 
            % (tablename, fieldnames, values, returns))
221
 
        if dry: return query
222
 
        return self.db.query(query)
223
 
 
224
 
 
225
 
    def update(self, primarydict, updatedict, tablename, tablefields,
226
 
        primary_keys, disallowed_update=frozenset([]), dry=False):
227
 
        """Updates a row in a table, matching against primarydict to find the
228
 
        row, and using the data in updatedict (which will be checked by
229
 
        check_dict).
230
 
        primarydict: Dict mapping column names to values. The keys should be
231
 
            the table's primary key. Only rows which match this dict's values
232
 
            will be updated.
233
 
        updatedict: Dict mapping column names to values. The columns will be
234
 
            updated with the given values for the matched rows.
235
 
        tablename, tablefields, disallowed_update: See insert.
236
 
        primary_keys: Collection of strings which together form the primary
237
 
            key for this table. primarydict must contain all of these as keys,
238
 
            and only these keys.
239
 
        """
240
 
        if (not (DB.check_dict(primarydict, primary_keys, must=True)
241
 
            and DB.check_dict(updatedict, tablefields, disallowed_update))):
242
 
            raise DBException("Supplied dictionary contains invalid or missing fields (1).")
243
 
        # Make a list of SQL fragments of the form "field = 'new value'"
244
 
        # These fragments are ALREADY-ESCAPED
245
 
        setlist = []
246
 
        for k,v in updatedict.items():
247
 
            setlist.append("%s = %s" % (k, _escape(v)))
248
 
        wherelist = []
249
 
        for k,v in primarydict.items():
250
 
            wherelist.append("%s = %s" % (k, _escape(v)))
251
 
        if len(setlist) == 0 or len(wherelist) == 0:
252
 
            return
253
 
        # Join the fragments into a comma-separated string
254
 
        setstring = ', '.join(setlist)
255
 
        wherestring = ' AND '.join(wherelist)
256
 
        # Build the whole query as an UPDATE statement
257
 
        query = ("UPDATE %s SET %s WHERE %s;"
258
 
            % (tablename, setstring, wherestring))
259
 
        if dry: return query
260
 
        self.db.query(query)
261
 
 
262
 
    def delete(self, primarydict, tablename, primary_keys, dry=False):
263
 
        """Deletes a row in the table, matching against primarydict to find
264
 
        the row.
265
 
        primarydict, tablename, primary_keys: See update.
266
 
        """
267
 
        if not DB.check_dict(primarydict, primary_keys, must=True):
268
 
            raise DBException("Supplied dictionary contains invalid or missing fields (2).")
269
 
        wherelist = []
270
 
        for k,v in primarydict.items():
271
 
            wherelist.append("%s = %s" % (k, _escape(v)))
272
 
        if len(wherelist) == 0:
273
 
            return
274
 
        wherestring = ' AND '.join(wherelist)
275
 
        query = ("DELETE FROM %s WHERE %s;" % (tablename, wherestring))
276
 
        if dry: return query
277
 
        self.db.query(query)
278
 
 
279
 
    def get_single(self, primarydict, tablename, getfields, primary_keys,
280
 
        error_notfound="No rows found", dry=False):
281
 
        """Retrieves a single row from a table, returning it as a dictionary
282
 
        mapping field names to values. Matches against primarydict to find the
283
 
        row.
284
 
        primarydict, tablename, primary_keys: See update/delete.
285
 
        getfields: Collection of strings; the field names which will be
286
 
            returned as keys in the dictionary.
287
 
        error_notfound: Error message if 0 rows match.
288
 
        Raises a DBException if 0 rows match, with error_notfound as the msg.
289
 
        Raises an AssertError if >1 rows match (this should not happen if
290
 
            primary_keys is indeed the primary key).
291
 
        """
292
 
        if not DB.check_dict(primarydict, primary_keys, must=True):
293
 
            raise DBException("Supplied dictionary contains invalid or missing fields (3).")
294
 
        wherelist = []
295
 
        for k,v in primarydict.items():
296
 
            wherelist.append("%s = %s" % (k, _escape(v)))
297
 
        if len(getfields) == 0 or len(wherelist) == 0:
298
 
            return
299
 
        # Join the fragments into a comma-separated string
300
 
        getstring = ', '.join(getfields)
301
 
        wherestring = ' AND '.join(wherelist)
302
 
        # Build the whole query as an SELECT statement
303
 
        query = ("SELECT %s FROM %s WHERE %s;"
304
 
            % (getstring, tablename, wherestring))
305
 
        if dry: return query
306
 
        result = self.db.query(query)
307
 
        # Expecting exactly one
308
 
        if result.ntuples() != 1:
309
 
            # It should not be possible for ntuples to be greater than 1
310
 
            assert (result.ntuples() < 1)
311
 
            raise DBException(error_notfound)
312
 
        # Return as a dictionary
313
 
        return result.dictresult()[0]
314
 
 
315
 
    def get_all(self, tablename, getfields, dry=False):
316
 
        """Retrieves all rows from a table, returning it as a list of
317
 
        dictionaries mapping field names to values.
318
 
        tablename, getfields: See get_single.
319
 
        """
320
 
        if len(getfields) == 0:
321
 
            return
322
 
        getstring = ', '.join(getfields)
323
 
        query = ("SELECT %s FROM %s;" % (getstring, tablename))
324
 
        if dry: return query
325
 
        return self.db.query(query).dictresult()
326
 
 
327
 
    def start_transaction(self, dry=False):
328
 
        """Starts a DB transaction.
329
 
        Will not commit any changes until self.commit() is called.
330
 
        """
331
 
        query = "START TRANSACTION;"
332
 
        if dry: return query
333
 
        self.db.query(query)
334
 
 
335
 
    def commit(self, dry=False):
336
 
        """Commits (ends) a DB transaction.
337
 
        Commits all changes since the call to start_transaction.
338
 
        """
339
 
        query = "COMMIT;"
340
 
        if dry: return query
341
 
        self.db.query(query)
342
 
 
343
 
    def rollback(self, dry=False):
344
 
        """Rolls back (ends) a DB transaction, undoing all changes since the
345
 
        call to start_transaction.
346
 
        """
347
 
        query = "ROLLBACK;"
348
 
        if dry: return query
349
 
        self.db.query(query)
350
 
 
351
 
    # USER MANAGEMENT FUNCTIONS #
352
 
 
353
 
    login_primary = frozenset(["login"])
354
 
    login_fields_list = [
355
 
        "login", "passhash", "state", "unixid", "email", "nick", "fullname",
356
 
        "rolenm", "studentid", "acct_exp", "pass_exp", "last_login", "svn_pass"
357
 
    ]
358
 
    login_fields = frozenset(login_fields_list)
359
 
 
360
 
    def create_user(self, user_obj=None, dry=False, **kwargs):
361
 
        """Creates a user login entry in the database.
362
 
        Two ways to call this - passing a user object, or passing
363
 
        all fields as separate arguments.
364
 
 
365
 
        Either pass a "user_obj" as the first argument (in which case other
366
 
        fields will be ignored), or pass all fields as arguments.
367
 
 
368
 
        All user fields are to be passed as args. The argument names
369
 
        are the field names of the "login" table of the DB schema.
370
 
        However, instead of supplying a "passhash", you must supply a
371
 
        "password" argument, which will be hashed internally.
372
 
        Also "state" must not given explicitly; it is implicitly set to
373
 
        "no_agreement".
374
 
        Raises an exception if the user already exists, or the dict contains
375
 
        invalid keys or is missing required keys.
376
 
        """
377
 
        if 'passhash' in kwargs:
378
 
            raise DBException("Supplied arguments include passhash (invalid) (1).")
379
 
        # Make a copy of the dict. Change password to passhash (hashing it),
380
 
        # and set 'state' to "no_agreement".
381
 
        if user_obj is None:
382
 
            # Use the kwargs
383
 
            fields = copy.copy(kwargs)
384
 
        else:
385
 
            # Use the user object
386
 
            fields = dict(user_obj)
387
 
        if 'password' in fields:
388
 
            fields['passhash'] = _passhash(fields['password'])
389
 
            del fields['password']
390
 
        if 'role' in fields:
391
 
            # Convert role to rolenm
392
 
            fields['rolenm'] = str(user_obj.role)
393
 
            del fields['role']
394
 
        if user_obj is None:
395
 
            fields['state'] = "no_agreement"
396
 
            # else, we'll trust the user, but it SHOULD be "no_agreement"
397
 
            # (We can't change it because then the user object would not
398
 
            # reflect the DB).
399
 
        if 'local_password' in fields:
400
 
            del fields['local_password']
401
 
        # Execute the query.
402
 
        return self.insert(fields, "login", self.login_fields, dry=dry)
403
 
 
404
 
    def get_user_loginid(self, login, dry=False):
405
 
        """Given a login, returns the integer loginid for this user.
406
 
 
407
 
        Raises a DBException if the login is not found in the DB.
408
 
        """
409
 
        userdict = self.get_single({"login": login}, "login",
410
 
            ['loginid'], self.login_primary,
411
 
            error_notfound="get_user_loginid: No user with that login name",
412
 
            dry=dry)
413
 
        if dry:
414
 
            return userdict     # Query string
415
 
        return userdict['loginid']
416
 
 
417
 
    # PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #
418
 
 
419
 
    def get_problem_problemid(self, exercisename, dry=False):
420
 
        """Given an exercise name, returns the associated problemID.
421
 
        If the exercise name is NOT in the database, it inserts it and returns
422
 
        the new problemID. Hence this may mutate the DB, but is idempotent.
423
 
        """
424
 
        try:
425
 
            d = self.get_single({"identifier": exercisename}, "problem",
426
 
                ['problemid'], frozenset(["identifier"]),
427
 
                dry=dry)
428
 
            if dry:
429
 
                return d        # Query string
430
 
        except DBException:
431
 
            if dry:
432
 
                # Shouldn't try again, must have failed for some other reason
433
 
                raise
434
 
            # if we failed to get a problemid, it was probably because
435
 
            # the exercise wasn't in the db. So lets insert it!
436
 
            #
437
 
            # The insert can fail if someone else simultaneously does
438
 
            # the insert, so if the insert fails, we ignore the problem. 
439
 
            try:
440
 
                self.insert({'identifier': exercisename}, "problem",
441
 
                        frozenset(['identifier']))
442
 
            except Exception, e:
443
 
                pass
444
 
 
445
 
            # Assuming the insert succeeded, we should be able to get the
446
 
            # problemid now.
447
 
            d = self.get_single({"identifier": exercisename}, "problem",
448
 
                ['problemid'], frozenset(["identifier"]))
449
 
 
450
 
        return d['problemid']
451
 
 
452
 
    def insert_problem_attempt(self, login, exercisename, date, complete,
453
 
        attempt, dry=False):
454
 
        """Inserts the details of a problem attempt into the database.
455
 
        exercisename: Name of the exercise. (identifier field of problem
456
 
            table). If this exercise does not exist, also creates a new row in
457
 
            the problem table for this exercise name.
458
 
        login: Name of the user submitting the attempt. (login field of the
459
 
            login table).
460
 
        date: struct_time, the date this attempt was made.
461
 
        complete: bool. Whether the test passed or not.
462
 
        attempt: Text of the attempt.
463
 
 
464
 
        Note: Even if dry, will still physically call get_problem_problemid,
465
 
        which may mutate the DB, and get_user_loginid, which may fail.
466
 
        """
467
 
        problemid = self.get_problem_problemid(exercisename)
468
 
        loginid = self.get_user_loginid(login)  # May raise a DBException
469
 
 
470
 
        return self.insert({
471
 
                'problemid': problemid,
472
 
                'loginid': loginid,
473
 
                'date': date,
474
 
                'complete': complete,
475
 
                'attempt': attempt,
476
 
            }, 'problem_attempt',
477
 
            frozenset(['problemid','loginid','date','complete','attempt']),
478
 
            dry=dry)
479
 
 
480
 
    def write_problem_save(self, login, exercisename, date, text, dry=False):
481
 
        """Writes text to the problem_save table (for when the user saves an
482
 
        exercise). Creates a new row, or overwrites an existing one if the
483
 
        user has already saved that problem.
484
 
        (Unlike problem_attempt, does not keep historical records).
485
 
        """
486
 
        problemid = self.get_problem_problemid(exercisename)
487
 
        loginid = self.get_user_loginid(login)  # May raise a DBException
488
 
 
489
 
        try:
490
 
            return self.insert({
491
 
                    'problemid': problemid,
492
 
                    'loginid': loginid,
493
 
                    'date': date,
494
 
                    'text': text,
495
 
                }, 'problem_save',
496
 
                frozenset(['problemid','loginid','date','text']),
497
 
                dry=dry)
498
 
        except pg.ProgrammingError:
499
 
            # May have failed because this problemid/loginid row already
500
 
            # exists (they have a unique key constraint).
501
 
            # Do an update instead.
502
 
            if dry:
503
 
                # Shouldn't try again, must have failed for some other reason
504
 
                raise
505
 
            self.update({
506
 
                    'problemid': problemid,
507
 
                    'loginid': loginid,
508
 
                },
509
 
                {
510
 
                    'date': date,
511
 
                    'text': text,
512
 
                }, "problem_save",
513
 
                frozenset(['date', 'text']),
514
 
                frozenset(['problemid', 'loginid']))
515
 
 
516
 
    def get_problem_stored_text(self, login, exercisename, dry=False):
517
 
        """Given a login name and exercise name, returns the text of the
518
 
        last saved/submitted attempt for this question.
519
 
        Returns None if the user has not saved or made an attempt on this
520
 
        problem.
521
 
        (If the user has both saved and submitted, it returns whichever was
522
 
        made last).
523
 
 
524
 
        Note: Even if dry, will still physically call get_problem_problemid,
525
 
        which may mutate the DB, and get_user_loginid, which may fail.
526
 
        """
527
 
        problemid = self.get_problem_problemid(exercisename)
528
 
        loginid = self.get_user_loginid(login)  # May raise a DBException
529
 
        # This very complex query finds all submissions made by this user for
530
 
        # this problem, as well as the save made by this user for this
531
 
        # problem, and returns the text of the newest one.
532
 
        # (Whichever is newer out of the save or the submit).
533
 
        query = """SELECT text FROM
534
 
    (
535
 
        (SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)
536
 
    UNION
537
 
        (SELECT problemid, loginid, date, text FROM problem_attempt
538
 
         AS problem_attempt (problemid, loginid, date, text)
539
 
         WHERE loginid = %d AND problemid = %d AND active)
540
 
    )
541
 
    AS _
542
 
    ORDER BY date DESC
543
 
    LIMIT 1;""" % (loginid, problemid, loginid, problemid)
544
 
        if dry: return query
545
 
        result = self.db.query(query)
546
 
        if result.ntuples() == 1:
547
 
            # The user has made at least 1 attempt. Return the newest.
548
 
            return result.getresult()[0][0]
549
 
        else:
550
 
            return None
551
 
 
552
 
    def get_problem_attempts(self, login, exercisename, allow_inactive=True,
553
 
                             dry=False):
554
 
        """Given a login name and exercise name, returns a list of dicts, one
555
 
        for each attempt made for that exercise.
556
 
        Dicts are {'date': 'formatted_time', 'complete': bool}.
557
 
        Ordered with the newest first.
558
 
        
559
 
        Note: By default, returns de-activated problem attempts (unlike
560
 
        get_problem_stored_text).
561
 
        If allow_inactive is False, will not return disabled attempts.
562
 
 
563
 
        Note: Even if dry, will still physically call get_problem_problemid,
564
 
        which may mutate the DB, and get_user_loginid, which may fail.
565
 
        """
566
 
        problemid = self.get_problem_problemid(exercisename)
567
 
        loginid = self.get_user_loginid(login)  # May raise a DBException
568
 
        andactive = '' if allow_inactive else ' AND active'
569
 
        query = """SELECT date, complete FROM problem_attempt
570
 
    WHERE loginid = %d AND problemid = %d%s
571
 
    ORDER BY date DESC;""" % (loginid, problemid, andactive)
572
 
        if dry: return query
573
 
        result = self.db.query(query).getresult()
574
 
        # Make into dicts (could use dictresult, but want to convert values)
575
 
        return [{'date': date, 'complete': _parse_boolean(complete)}
576
 
                for date, complete in result]
577
 
 
578
 
    def get_problem_attempt(self, login, exercisename, as_of,
579
 
        allow_inactive=True, dry=False):
580
 
        """Given a login name, exercise name, and struct_time, returns the
581
 
        text of the submitted attempt for this question as of that date.
582
 
        Returns None if the user had not made an attempt on this problem at
583
 
        that date.
584
 
        
585
 
        Note: By default, returns de-activated problem attempts (unlike
586
 
        get_problem_stored_text).
587
 
        If allow_inactive is False, will not return disabled attempts.
588
 
 
589
 
        Note: Even if dry, will still physically call get_problem_problemid,
590
 
        which may mutate the DB, and get_user_loginid, which may fail.
591
 
        """
592
 
        problemid = self.get_problem_problemid(exercisename)
593
 
        loginid = self.get_user_loginid(login)  # May raise a DBException
594
 
        # Very similar to query in get_problem_stored_text, but without
595
 
        # looking in problem_save, and restricting to a certain date.
596
 
        andactive = '' if allow_inactive else ' AND active'
597
 
        query = """SELECT attempt FROM problem_attempt
598
 
    WHERE loginid = %d AND problemid = %d%s AND date <= %s
599
 
    ORDER BY date DESC
600
 
    LIMIT 1;""" % (loginid, problemid, andactive, _escape(as_of))
601
 
        if dry: return query
602
 
        result = self.db.query(query)
603
 
        if result.ntuples() == 1:
604
 
            # The user has made at least 1 attempt. Return the newest.
605
 
            return result.getresult()[0][0]
606
 
        else:
607
 
            return None
608
 
 
609
 
    def get_problem_status(self, login, exercisename, dry=False):
610
 
        """Given a login name and exercise name, returns information about the
611
 
        user's performance on that problem.
612
 
        Returns a tuple of:
613
 
            - A boolean, whether they have successfully passed this exercise.
614
 
            - An int, the number of attempts they have made up to and
615
 
              including the first successful attempt (or the total number of
616
 
              attempts, if not yet successful).
617
 
        Note: exercisename may be an int, in which case it will be directly
618
 
        used as the problemid.
619
 
        """
620
 
        if isinstance(exercisename, int):
621
 
            problemid = exercisename
622
 
        else:
623
 
            problemid = self.get_problem_problemid(exercisename)
624
 
        loginid = self.get_user_loginid(login)  # May raise a DBException
625
 
 
626
 
        # ASSUME that it is completed, get the total number of attempts up to
627
 
        # and including the first successful attempt.
628
 
        # (Get the date of the first successful attempt. Then count the number
629
 
        # of attempts made <= that date).
630
 
        # Will return an empty table if the problem has never been
631
 
        # successfully completed.
632
 
        query = """SELECT COUNT(*) FROM problem_attempt
633
 
    WHERE loginid = %d AND problemid = %d AND active AND date <=
634
 
        (SELECT date FROM problem_attempt
635
 
            WHERE loginid = %d AND problemid = %d AND complete AND active
636
 
            ORDER BY date ASC
637
 
            LIMIT 1);""" % (loginid, problemid, loginid, problemid)
638
 
        if dry: return query
639
 
        result = self.db.query(query)
640
 
        count = int(result.getresult()[0][0])
641
 
        if count > 0:
642
 
            # The user has made at least 1 successful attempt.
643
 
            # Return True for success, and the number of attempts up to and
644
 
            # including the successful one.
645
 
            return (True, count)
646
 
        else:
647
 
            # Returned 0 rows - this indicates that the problem has not been
648
 
            # completed.
649
 
            # Return the total number of attempts, and False for success.
650
 
            query = """SELECT COUNT(*) FROM problem_attempt
651
 
    WHERE loginid = %d AND problemid = %d AND active;""" % (loginid, problemid)
652
 
            result = self.db.query(query)
653
 
            count = int(result.getresult()[0][0])
654
 
            return (False, count)
655
 
 
656
 
    # WORKSHEET/PROBLEM ASSOCIATION AND MARKS CALCULATION
657
 
 
658
 
    def get_worksheet_mtime(self, subject, worksheet, dry=False):
659
 
        """
660
 
        For a given subject/worksheet name, gets the time the worksheet was
661
 
        last updated in the DB, if any.
662
 
        This can be used to check if there is a newer version on disk.
663
 
        Returns the timestamp as a time.struct_time, or None if the worksheet
664
 
        is not found or has no stored mtime.
665
 
        """
666
 
        try:
667
 
            r = self.get_single(
668
 
                {"subject": subject, "identifier": worksheet},
669
 
                "worksheet", ["mtime"], ["subject", "identifier"],
670
 
                dry=dry)
671
 
        except DBException:
672
 
            # Assume the worksheet is not in the DB
673
 
            return None
674
 
        if dry:
675
 
            return r
676
 
        if r["mtime"] is None:
677
 
            return None
678
 
        return time.strptime(r["mtime"], TIMESTAMP_FORMAT)
679
 
 
680
 
    def create_worksheet(self, subject, worksheet, problems=None,
681
 
        assessable=None):
682
 
        """
683
 
        Inserts or updates rows in the worksheet and worksheet_problems
684
 
        tables, to create a worksheet in the database.
685
 
        This atomically performs all operations. If the worksheet is already
686
 
        in the DB, removes it and all its associated problems and rebuilds.
687
 
        Sets the timestamp to the current time.
688
 
 
689
 
        problems is a collection of pairs. The first element of the pair is
690
 
        the problem identifier ("identifier" column of the problem table). The
691
 
        second element is an optional boolean, "optional". This can be omitted
692
 
        (so it's a 1-tuple), and then it will default to False.
693
 
 
694
 
        Problems and assessable are optional, and if omitted, will not change
695
 
        the existing data. If the worksheet does not yet exist, and assessable
696
 
        is omitted, it defaults to False.
697
 
 
698
 
        Note: As with get_problem_problemid, if a problem name is not in the
699
 
        DB, it will be added to the problem table.
700
 
        """
701
 
        self.start_transaction()
702
 
        try:
703
 
            # Use the current time as the "mtime" field
704
 
            mtime = time.localtime()
705
 
            try:
706
 
                # Get the worksheetid
707
 
                r = self.get_single(
708
 
                    {"subject": subject, "identifier": worksheet},
709
 
                    "worksheet", ["worksheetid"], ["subject", "identifier"])
710
 
                worksheetid = r["worksheetid"]
711
 
 
712
 
                # Delete any problems which might exist, if problems is
713
 
                # supplied. If it isn't, keep the existing ones.
714
 
                if problems is not None:
715
 
                    query = ("DELETE FROM worksheet_problem "
716
 
                        "WHERE worksheetid = %d;" % worksheetid)
717
 
                    self.db.query(query)
718
 
                # Update the row with the new details
719
 
                if assessable is None:
720
 
                    query = ("UPDATE worksheet "
721
 
                        "SET mtime = %s WHERE worksheetid = %d;"
722
 
                        % (_escape(mtime), worksheetid))
723
 
                else:
724
 
                    query = ("UPDATE worksheet "
725
 
                        "SET assessable = %s, mtime = %s "
726
 
                        "WHERE worksheetid = %d;"
727
 
                        % (_escape(assessable), _escape(mtime), worksheetid))
728
 
                self.db.query(query)
729
 
            except DBException:
730
 
                # Assume the worksheet is not in the DB
731
 
                # If assessable is not supplied, default to False.
732
 
                if assessable is None:
733
 
                    assessable = False
734
 
                # Create the worksheet row
735
 
                query = ("INSERT INTO worksheet "
736
 
                    "(subject, identifier, assessable, mtime) "
737
 
                    "VALUES (%s, %s, %s, %s);"""
738
 
                    % (_escape(subject), _escape(worksheet),
739
 
                    _escape(assessable), _escape(mtime)))
740
 
                self.db.query(query)
741
 
                # Now get the worksheetid again - should succeed
742
 
                r = self.get_single(
743
 
                    {"subject": subject, "identifier": worksheet},
744
 
                    "worksheet", ["worksheetid"], ["subject", "identifier"])
745
 
                worksheetid = r["worksheetid"]
746
 
 
747
 
            # Now insert each problem into the worksheet_problem table
748
 
            if problems is not None:
749
 
                for problem in problems:
750
 
                    if isinstance(problem, tuple):
751
 
                        prob_identifier = problem[0]
752
 
                        try:
753
 
                            optional = problem[1]
754
 
                        except IndexError:
755
 
                            optional = False
756
 
                    else:
757
 
                        prob_identifier = problem
758
 
                        optional = False
759
 
                    problemid = self.get_problem_problemid(prob_identifier)
760
 
                    query = ("INSERT INTO worksheet_problem "
761
 
                        "(worksheetid, problemid, optional) "
762
 
                        "VALUES (%d, %d, %s);"
763
 
                        % (worksheetid, problemid, _escape(optional)))
764
 
                    self.db.query(query)
765
 
 
766
 
            self.commit()
767
 
        except:
768
 
            self.rollback()
769
 
            raise
770
 
 
771
 
    def set_worksheet_assessable(self, subject, worksheet, assessable,
772
 
        dry=False):
773
 
        """
774
 
        Sets the "assessable" field of a worksheet without updating the mtime.
775
 
 
776
 
        IMPORTANT: This will NOT update the mtime. This is designed to allow
777
 
        updates which did not come from the worksheet XML file. It would be
778
 
        bad to update the mtime without consulting the XML file because then
779
 
        it would appear the database is up to date, when it isn't.
780
 
 
781
 
        Therefore, call this method if you are getting "assessable"
782
 
        information from outside the worksheet XML file (eg. from the subject
783
 
        XML file).
784
 
 
785
 
        Unlike create_worksheet, raises a DBException if the worksheet is not
786
 
        in the database.
787
 
        """
788
 
        return self.update({"subject": subject, "identifier": worksheet},
789
 
            {"assessable": assessable}, "worksheet", ["assessable"],
790
 
            ["subject", "identifier"], dry=dry)
791
 
 
792
 
    def worksheet_is_assessable(self, subject, worksheet, dry=False):
793
 
        r = self.get_single(
794
 
            {"subject": subject, "identifier": worksheet},
795
 
            "worksheet", ["assessable"], ["subject", "identifier"], dry=dry)
796
 
        return _parse_boolean(r["assessable"])
797
 
 
798
 
    def calculate_score_worksheet(self, login, subject, worksheet):
799
 
        """
800
 
        Calculates the score for a user on a given worksheet.
801
 
        Returns a 4-tuple of ints, consisting of:
802
 
        (No. mandatory exercises completed,
803
 
         Total no. mandatory exercises,
804
 
         No. optional exercises completed,
805
 
         Total no. optional exercises)
806
 
        """
807
 
        self.start_transaction()
808
 
        try:
809
 
            mand_done = 0
810
 
            mand_total = 0
811
 
            opt_done = 0
812
 
            opt_total = 0
813
 
            # Get a list of problems and optionality for all problems in the
814
 
            # worksheet
815
 
            query = ("""SELECT problemid, optional FROM worksheet_problem
816
 
    WHERE worksheetid = (SELECT worksheetid FROM worksheet
817
 
                         WHERE subject = %s and identifier = %s);"""
818
 
                    % (_escape(subject), _escape(worksheet)))
819
 
            result = self.db.query(query)
820
 
            # Now get the student's pass/fail for each problem in this worksheet
821
 
            for problemid, optional in result.getresult():
822
 
                done, _ = self.get_problem_status(login, problemid)
823
 
                # done is a bool, whether this student has completed that
824
 
                # problem
825
 
                if _parse_boolean(optional):
826
 
                    opt_total += 1
827
 
                    if done: opt_done += 1
828
 
                else:
829
 
                    mand_total += 1
830
 
                    if done: mand_done += 1
831
 
            self.commit()
832
 
        except:
833
 
            self.rollback()
834
 
            raise
835
 
        return mand_done, mand_total, opt_done, opt_total
836
 
 
837
 
    # ENROLMENT INFORMATION
838
 
 
839
 
    def add_enrolment(self, login, subj_code, semester, year=None, dry=False):
840
 
        """
841
 
        Enrol a student in the given offering of a subject.
842
 
        Returns True on success, False on failure (which usually means either
843
 
        the student is already enrolled in the subject, the student was not
844
 
        found, or no offering existed with the given details).
845
 
        The return value can usually be ignored.
846
 
        """
847
 
        subj_code = str(subj_code)
848
 
        semester = str(semester)
849
 
        if year is None:
850
 
            year = str(time.gmtime().tm_year)
851
 
        else:
852
 
            year = str(year)
853
 
        query = """\
854
 
INSERT INTO enrolment (loginid, offeringid)
855
 
    VALUES (
856
 
        (SELECT loginid FROM login WHERE login=%s),
857
 
        (SELECT offeringid
858
 
            FROM offering, subject, semester
859
 
                WHERE subject.subjectid = offering.subject
860
 
                AND semester.semesterid = offering.semesterid
861
 
                AND subj_code=%s AND semester=%s AND year=%s)
862
 
        );""" % (_escape(login), _escape(subj_code), _escape(semester),
863
 
                 _escape(year))
864
 
        if dry:
865
 
            return query
866
 
        try:
867
 
            result = self.db.query(query)
868
 
        except pg.ProgrammingError:
869
 
            return False
870
 
        return True
871
 
 
872
 
    # SUBJECTS AND ENROLEMENT
873
 
 
874
 
    def get_subjects(self, dry=False):
875
 
        """
876
 
        Get all subjects in IVLE.
877
 
        Returns a list of dicts (all values strings), with the keys:
878
 
        subj_code, subj_name, subj_short_name, url
879
 
        """
880
 
        return self.get_all("subject",
881
 
            ("subjectid", "subj_code", "subj_name", "subj_short_name", "url"),
882
 
            dry)
883
 
 
884
 
    def get_offering_semesters(self, subjectid, dry=False):
885
 
        """
886
 
        Get the semester information for a subject as well as providing 
887
 
        information about if the subject is active and which semester it is in.
888
 
        """
889
 
        query = """\
890
 
SELECT offeringid, subj_name, year, semester, active
891
 
FROM semester, offering, subject
892
 
WHERE offering.semesterid = semester.semesterid AND
893
 
    offering.subject = subject.subjectid AND
894
 
    offering.subject = %d;"""%subjectid
895
 
        if dry:
896
 
            return query
897
 
        results = self.db.query(query).dictresult()
898
 
        # Parse boolean varibles
899
 
        for result in results:
900
 
            result['active'] = _parse_boolean(result['active'])
901
 
        return results
902
 
 
903
 
    def get_offering_members(self, offeringid, dry=False):
904
 
        """
905
 
        Gets the logins of all the people enroled in an offering
906
 
        """
907
 
        query = """\
908
 
SELECT login.login AS login, login.fullname AS fullname
909
 
FROM login, enrolment
910
 
WHERE login.loginid = enrolment.loginid AND
911
 
    enrolment.offeringid = %d
912
 
    ORDER BY login.login;"""%offeringid
913
 
        if dry:
914
 
            return query
915
 
        return self.db.query(query).dictresult()
916
 
 
917
 
 
918
 
    def get_enrolment_groups(self, login, offeringid, dry=False):
919
 
        """
920
 
        Get all groups the user is member of in the given offering.
921
 
        Returns a list of dicts (all values strings), with the keys:
922
 
        name, nick
923
 
        """
924
 
        query = """\
925
 
SELECT project_group.groupnm as name, project_group.nick as nick
926
 
FROM project_set, project_group, group_member, login
927
 
WHERE login.login=%s
928
 
  AND project_set.offeringid=%s
929
 
  AND group_member.loginid=login.loginid
930
 
  AND project_group.groupid=group_member.groupid
931
 
  AND project_group.projectsetid=project_set.projectsetid
932
 
""" % (_escape(login), _escape(offeringid))
933
 
        if dry:
934
 
            return query
935
 
        return self.db.query(query).dictresult()
936
 
 
937
 
 
938
 
    # PROJECT GROUPS
939
 
    def get_groups_by_user(self, login, offeringid=None, dry=False):
940
 
        """
941
 
        Get all project groups the student is in, corresponding to a
942
 
        particular subject offering (or all offerings, if omitted).
943
 
        Returns a list of tuples:
944
 
        (int groupid, str groupnm, str group_nick, bool is_member).
945
 
        (Note: If is_member is false, it means they have just been invited to
946
 
        this group, not a member).
947
 
        """
948
 
        if offeringid is None:
949
 
            and_offering = ""
950
 
        else:
951
 
            and_projectset_table = ", project_set"
952
 
            and_offering = """
953
 
AND project_group.projectsetid = project_set.projectsetid
954
 
AND project_set.offeringid = %s""" % _escape(offeringid)
955
 
        # Union both the groups this user is a member of, and the groups this
956
 
        # user is invited to.
957
 
        query = """\
958
 
    SELECT project_group.groupid, groupnm, project_group.nick, True
959
 
    FROM project_group, group_member, login %(and_projectset_table)s
960
 
    WHERE project_group.groupid = group_member.groupid
961
 
      AND group_member.loginid = login.loginid
962
 
      AND login = %(login)s
963
 
      %(and_offering)s
964
 
UNION
965
 
    SELECT project_group.groupid, groupnm, project_group.nick, False
966
 
    FROM project_group, group_invitation, login %(and_projectset_table)s
967
 
    WHERE project_group.groupid = group_invitation.groupid
968
 
      AND group_invitation.loginid = login.loginid
969
 
      AND login = %(login)s
970
 
      %(and_offering)s
971
 
;""" % {"login": _escape(login), "and_offering": and_offering,
972
 
        "and_projectset_table": and_projectset_table}
973
 
        if dry:
974
 
            return query
975
 
        # Convert 't' -> True, 'f' -> False
976
 
        return [(groupid, groupnm, nick, ismember == 't')
977
 
                for groupid, groupnm, nick, ismember
978
 
                in self.db.query(query).getresult()]
979
 
 
980
 
    def get_offering_info(self, projectsetid, dry=False):
981
 
        """Takes information from projectset and returns useful information 
982
 
        about the subject and semester. Returns as a dictionary.
983
 
        """
984
 
        query = """\
985
 
SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester, 
986
 
active
987
 
FROM subject, offering, semester, project_set
988
 
WHERE offering.subject = subject.subjectid AND
989
 
    offering.semesterid = semester.semesterid AND
990
 
    project_set.offeringid = offering.offeringid AND
991
 
    project_set.projectsetid = %d;"""%projectsetid
992
 
        if dry:
993
 
            return query
994
 
        return self.db.query(query).dictresult()[0]
995
 
 
996
 
    def get_projectgroup_members(self, groupid, dry=False):
997
 
        """Returns the logins of all students in a project group
998
 
        """
999
 
        query = """\
1000
 
SELECT login.login as login, login.fullname as fullname
1001
 
FROM login, group_member
1002
 
WHERE login.loginid = group_member.loginid AND
1003
 
    group_member.groupid = %d
1004
 
ORDER BY login.login;"""%groupid
1005
 
        if dry:
1006
 
            return query
1007
 
        return self.db.query(query).dictresult()
1008
 
 
1009
 
    def get_projectsets_by_offering(self, offeringid, dry=False):
1010
 
        """Returns all the projectsets in a particular offering"""
1011
 
        query = """\
1012
 
SELECT projectsetid, max_students_per_group
1013
 
FROM project_set
1014
 
WHERE project_set.offeringid = %d;"""%offeringid
1015
 
        if dry:
1016
 
            return query
1017
 
        return self.db.query(query).dictresult()
1018
 
 
1019
 
    def get_groups_by_projectset(self, projectsetid, dry=False):
1020
 
        """Returns all the groups that are in a particular projectset"""
1021
 
        query = """\
1022
 
SELECT groupid, groupnm, nick, createdby, epoch
1023
 
FROM project_group
1024
 
WHERE project_group.projectsetid = %d;"""%projectsetid
1025
 
        if dry:
1026
 
            return query
1027
 
        return self.db.query(query).dictresult()
1028
 
 
1029
 
    def close(self):
1030
 
        """Close the DB connection. Do not call any other functions after
1031
 
        this. (The behaviour of doing so is undefined).
1032
 
        """
1033
 
        self.db.close()
1034
 
        self.open = False