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

« back to all changes in this revision

Viewing changes to lib/common/db.py

  • Committer: mattgiuca
  • Date: 2008-01-11 00:49:06 UTC
  • Revision ID: svn-v3-trunk0:2b9c9e99-6f39-0410-b283-7f802c844ae2:trunk:172
util: Added buildurl function.

Show diffs side-by-side

added added

removed removed

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