~azzar1/unity/add-show-desktop-key : revision 1080.1.90

1

# IVLE - Informatics Virtual Learning Environment

2

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, 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(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(self, login, dry=False):

405

"""Given a login, returns a User object containing details looked up

406

in the DB.

407

408

Raises a DBException if the login is not found in the DB.

409

"""

410

userdict = self.get_single({"login": login}, "login",

411

self.login_fields, self.login_primary,

412

error_notfound="get_user: No user with that login name", dry=dry)

413

if dry:

414

return userdict # Query string

415

# Package into a User object

416

return user.User(**userdict)

417

418

def get_users(self, dry=False):

419

"""Returns a list of all users in the DB, as User objects.

420

"""

421

userdicts = self.get_all("login", self.login_fields, dry=dry)

422

if dry:

423

return userdicts # Query string

424

# Package into User objects

425

return [user.User(**userdict) for userdict in userdicts]

426

427

def get_user_loginid(self, login, dry=False):

428

"""Given a login, returns the integer loginid for this user.

429

430

Raises a DBException if the login is not found in the DB.

431

"""

432

userdict = self.get_single({"login": login}, "login",

433

['loginid'], self.login_primary,

434

error_notfound="get_user_loginid: No user with that login name",

435

dry=dry)

436

if dry:

437

return userdict # Query string

438

return userdict['loginid']

439

440

def user_authenticate(self, login, password, dry=False):

441

"""Performs a password authentication on a user. Returns True if

442

"passhash" is the correct passhash for the given login, False

443

if the passhash does not match the password in the DB,

444

and None if the passhash in the DB is NULL.

445

Also returns False if the login does not exist (so if you want to

446

differentiate these cases, use get_user and catch an exception).

447

"""

448

query = ("SELECT passhash FROM login WHERE login = %s;"

449

% _escape(login))

450

if dry: return query

451

result = self.db.query(query)

452

if result.ntuples() == 1:

453

# Valid username. Check password.

454

passhash = result.getresult()[0][0]

455

if passhash is None:

456

return None

457

return _passhash(password) == passhash

458

else:

459

return False

460

461

# PROBLEM AND PROBLEM ATTEMPT FUNCTIONS #

462

463

def get_problem_problemid(self, exercisename, dry=False):

464

"""Given an exercise name, returns the associated problemID.

465

If the exercise name is NOT in the database, it inserts it and returns

466

the new problemID. Hence this may mutate the DB, but is idempotent.

467

"""

468

try:

469

d = self.get_single({"identifier": exercisename}, "problem",

470

['problemid'], frozenset(["identifier"]),

471

dry=dry)

472

if dry:

473

return d # Query string

474

except DBException:

475

if dry:

476

# Shouldn't try again, must have failed for some other reason

477

raise

478

# if we failed to get a problemid, it was probably because

479

# the exercise wasn't in the db. So lets insert it!

480

#

481

# The insert can fail if someone else simultaneously does

482

# the insert, so if the insert fails, we ignore the problem.

483

try:

484

self.insert({'identifier': exercisename}, "problem",

485

frozenset(['identifier']))

486

except Exception, e:

487

pass

488

489

# Assuming the insert succeeded, we should be able to get the

490

# problemid now.

491

d = self.get_single({"identifier": exercisename}, "problem",

492

['problemid'], frozenset(["identifier"]))

493

494

return d['problemid']

495

496

def insert_problem_attempt(self, login, exercisename, date, complete,

497

attempt, dry=False):

498

"""Inserts the details of a problem attempt into the database.

499

exercisename: Name of the exercise. (identifier field of problem

500

table). If this exercise does not exist, also creates a new row in

501

the problem table for this exercise name.

502

login: Name of the user submitting the attempt. (login field of the

503

login table).

504

date: struct_time, the date this attempt was made.

505

complete: bool. Whether the test passed or not.

506

attempt: Text of the attempt.

507

508

Note: Even if dry, will still physically call get_problem_problemid,

509

which may mutate the DB, and get_user_loginid, which may fail.

510

"""

511

problemid = self.get_problem_problemid(exercisename)

512

loginid = self.get_user_loginid(login) # May raise a DBException

513

514

return self.insert({

515

'problemid': problemid,

516

'loginid': loginid,

517

'date': date,

518

'complete': complete,

519

'attempt': attempt,

520

}, 'problem_attempt',

521

frozenset(['problemid','loginid','date','complete','attempt']),

522

dry=dry)

523

524

def write_problem_save(self, login, exercisename, date, text, dry=False):

525

"""Writes text to the problem_save table (for when the user saves an

526

exercise). Creates a new row, or overwrites an existing one if the

527

user has already saved that problem.

528

(Unlike problem_attempt, does not keep historical records).

529

"""

530

problemid = self.get_problem_problemid(exercisename)

531

loginid = self.get_user_loginid(login) # May raise a DBException

532

533

try:

534

return self.insert({

535

'problemid': problemid,

536

'loginid': loginid,

537

'date': date,

538

'text': text,

539

}, 'problem_save',

540

frozenset(['problemid','loginid','date','text']),

541

dry=dry)

542

except pg.ProgrammingError:

543

# May have failed because this problemid/loginid row already

544

# exists (they have a unique key constraint).

545

# Do an update instead.

546

if dry:

547

# Shouldn't try again, must have failed for some other reason

548

raise

549

self.update({

550

'problemid': problemid,

551

'loginid': loginid,

552

},

553

{

554

'date': date,

555

'text': text,

556

}, "problem_save",

557

frozenset(['date', 'text']),

558

frozenset(['problemid', 'loginid']))

559

560

def get_problem_stored_text(self, login, exercisename, dry=False):

561

"""Given a login name and exercise name, returns the text of the

562

last saved/submitted attempt for this question.

563

Returns None if the user has not saved or made an attempt on this

564

problem.

565

(If the user has both saved and submitted, it returns whichever was

566

made last).

567

568

Note: Even if dry, will still physically call get_problem_problemid,

569

which may mutate the DB, and get_user_loginid, which may fail.

570

"""

571

problemid = self.get_problem_problemid(exercisename)

572

loginid = self.get_user_loginid(login) # May raise a DBException

573

# This very complex query finds all submissions made by this user for

574

# this problem, as well as the save made by this user for this

575

# problem, and returns the text of the newest one.

576

# (Whichever is newer out of the save or the submit).

577

query = """SELECT text FROM

578

(

579

(SELECT * FROM problem_save WHERE loginid = %d AND problemid = %d)

580

UNION

581

(SELECT problemid, loginid, date, text FROM problem_attempt

582

AS problem_attempt (problemid, loginid, date, text)

583

WHERE loginid = %d AND problemid = %d AND active)

584

)

585

AS _

586

ORDER BY date DESC

587

LIMIT 1;""" % (loginid, problemid, loginid, problemid)

588

if dry: return query

589

result = self.db.query(query)

590

if result.ntuples() == 1:

591

# The user has made at least 1 attempt. Return the newest.

592

return result.getresult()[0][0]

593

else:

594

return None

595

596

def get_problem_attempts(self, login, exercisename, allow_inactive=True,

597

dry=False):

598

"""Given a login name and exercise name, returns a list of dicts, one

599

for each attempt made for that exercise.

600

Dicts are {'date': 'formatted_time', 'complete': bool}.

601

Ordered with the newest first.

602

603

Note: By default, returns de-activated problem attempts (unlike

604

get_problem_stored_text).

605

If allow_inactive is False, will not return disabled attempts.

606

607

Note: Even if dry, will still physically call get_problem_problemid,

608

which may mutate the DB, and get_user_loginid, which may fail.

609

"""

610

problemid = self.get_problem_problemid(exercisename)

611

loginid = self.get_user_loginid(login) # May raise a DBException

612

andactive = '' if allow_inactive else ' AND active'

613

query = """SELECT date, complete FROM problem_attempt

614

WHERE loginid = %d AND problemid = %d%s

615

ORDER BY date DESC;""" % (loginid, problemid, andactive)

616

if dry: return query

617

result = self.db.query(query).getresult()

618

# Make into dicts (could use dictresult, but want to convert values)

619

return [{'date': date, 'complete': _parse_boolean(complete)}

620

for date, complete in result]

621

622

def get_problem_attempt(self, login, exercisename, as_of,

623

allow_inactive=True, dry=False):

624

"""Given a login name, exercise name, and struct_time, returns the

625

text of the submitted attempt for this question as of that date.

626

Returns None if the user had not made an attempt on this problem at

627

that date.

628

629

Note: By default, returns de-activated problem attempts (unlike

630

get_problem_stored_text).

631

If allow_inactive is False, will not return disabled attempts.

632

633

Note: Even if dry, will still physically call get_problem_problemid,

634

which may mutate the DB, and get_user_loginid, which may fail.

635

"""

636

problemid = self.get_problem_problemid(exercisename)

637

loginid = self.get_user_loginid(login) # May raise a DBException

638

# Very similar to query in get_problem_stored_text, but without

639

# looking in problem_save, and restricting to a certain date.

640

andactive = '' if allow_inactive else ' AND active'

641

query = """SELECT attempt FROM problem_attempt

642

WHERE loginid = %d AND problemid = %d%s AND date <= %s

643

ORDER BY date DESC

644

LIMIT 1;""" % (loginid, problemid, andactive, _escape(as_of))

645

if dry: return query

646

result = self.db.query(query)

647

if result.ntuples() == 1:

648

# The user has made at least 1 attempt. Return the newest.

649

return result.getresult()[0][0]

650

else:

651

return None

652

653

def get_problem_status(self, login, exercisename, dry=False):

654

"""Given a login name and exercise name, returns information about the

655

user's performance on that problem.

656

Returns a tuple of:

657

- A boolean, whether they have successfully passed this exercise.

658

- An int, the number of attempts they have made up to and

659

including the first successful attempt (or the total number of

660

attempts, if not yet successful).

661

Note: exercisename may be an int, in which case it will be directly

662

used as the problemid.

663

"""

664

if isinstance(exercisename, int):

665

problemid = exercisename

666

else:

667

problemid = self.get_problem_problemid(exercisename)

668

loginid = self.get_user_loginid(login) # May raise a DBException

669

670

# ASSUME that it is completed, get the total number of attempts up to

671

# and including the first successful attempt.

672

# (Get the date of the first successful attempt. Then count the number

673

# of attempts made <= that date).

674

# Will return an empty table if the problem has never been

675

# successfully completed.

676

query = """SELECT COUNT(*) FROM problem_attempt

677

WHERE loginid = %d AND problemid = %d AND active AND date <=

678

(SELECT date FROM problem_attempt

679

WHERE loginid = %d AND problemid = %d AND complete AND active

680

ORDER BY date ASC

681

LIMIT 1);""" % (loginid, problemid, loginid, problemid)

682

if dry: return query

683

result = self.db.query(query)

684

count = int(result.getresult()[0][0])

685

if count > 0:

686

# The user has made at least 1 successful attempt.

687

# Return True for success, and the number of attempts up to and

688

# including the successful one.

689

return (True, count)

690

else:

691

# Returned 0 rows - this indicates that the problem has not been

692

# completed.

693

# Return the total number of attempts, and False for success.

694

query = """SELECT COUNT(*) FROM problem_attempt

695

WHERE loginid = %d AND problemid = %d AND active;""" % (loginid, problemid)

696

result = self.db.query(query)

697

count = int(result.getresult()[0][0])

698

return (False, count)

699

700

# WORKSHEET/PROBLEM ASSOCIATION AND MARKS CALCULATION

701

702

def get_worksheet_mtime(self, subject, worksheet, dry=False):

703

"""

704

For a given subject/worksheet name, gets the time the worksheet was

705

last updated in the DB, if any.

706

This can be used to check if there is a newer version on disk.

707

Returns the timestamp as a time.struct_time, or None if the worksheet

708

is not found or has no stored mtime.

709

"""

710

try:

711

r = self.get_single(

712

{"subject": subject, "identifier": worksheet},

713

"worksheet", ["mtime"], ["subject", "identifier"],

714

dry=dry)

715

except DBException:

716

# Assume the worksheet is not in the DB

717

return None

718

if dry:

719

return r

720

if r["mtime"] is None:

721

return None

722

return time.strptime(r["mtime"], TIMESTAMP_FORMAT)

723

724

def create_worksheet(self, subject, worksheet, problems=None,

725

assessable=None):

726

"""

727

Inserts or updates rows in the worksheet and worksheet_problems

728

tables, to create a worksheet in the database.

729

This atomically performs all operations. If the worksheet is already

730

in the DB, removes it and all its associated problems and rebuilds.

731

Sets the timestamp to the current time.

732

733

problems is a collection of pairs. The first element of the pair is

734

the problem identifier ("identifier" column of the problem table). The

735

second element is an optional boolean, "optional". This can be omitted

736

(so it's a 1-tuple), and then it will default to False.

737

738

Problems and assessable are optional, and if omitted, will not change

739

the existing data. If the worksheet does not yet exist, and assessable

740

is omitted, it defaults to False.

741

742

Note: As with get_problem_problemid, if a problem name is not in the

743

DB, it will be added to the problem table.

744

"""

745

self.start_transaction()

746

try:

747

# Use the current time as the "mtime" field

748

mtime = time.localtime()

749

try:

750

# Get the worksheetid

751

r = self.get_single(

752

{"subject": subject, "identifier": worksheet},

753

"worksheet", ["worksheetid"], ["subject", "identifier"])

754

worksheetid = r["worksheetid"]

755

756

# Delete any problems which might exist, if problems is

757

# supplied. If it isn't, keep the existing ones.

758

if problems is not None:

759

query = ("DELETE FROM worksheet_problem "

760

"WHERE worksheetid = %d;" % worksheetid)

761

self.db.query(query)

762

# Update the row with the new details

763

if assessable is None:

764

query = ("UPDATE worksheet "

765

"SET mtime = %s WHERE worksheetid = %d;"

766

% (_escape(mtime), worksheetid))

767

else:

768

query = ("UPDATE worksheet "

769

"SET assessable = %s, mtime = %s "

770

"WHERE worksheetid = %d;"

771

% (_escape(assessable), _escape(mtime), worksheetid))

772

self.db.query(query)

773

except DBException:

774

# Assume the worksheet is not in the DB

775

# If assessable is not supplied, default to False.

776

if assessable is None:

777

assessable = False

778

# Create the worksheet row

779

query = ("INSERT INTO worksheet "

780

"(subject, identifier, assessable, mtime) "

781

"VALUES (%s, %s, %s, %s);"""

782

% (_escape(subject), _escape(worksheet),

783

_escape(assessable), _escape(mtime)))

784

self.db.query(query)

785

# Now get the worksheetid again - should succeed

786

r = self.get_single(

787

{"subject": subject, "identifier": worksheet},

788

"worksheet", ["worksheetid"], ["subject", "identifier"])

789

worksheetid = r["worksheetid"]

790

791

# Now insert each problem into the worksheet_problem table

792

if problems is not None:

793

for problem in problems:

794

if isinstance(problem, tuple):

795

prob_identifier = problem[0]

796

try:

797

optional = problem[1]

798

except IndexError:

799

optional = False

800

else:

801

prob_identifier = problem

802

optional = False

803

problemid = self.get_problem_problemid(prob_identifier)

804

query = ("INSERT INTO worksheet_problem "

805

"(worksheetid, problemid, optional) "

806

"VALUES (%d, %d, %s);"

807

% (worksheetid, problemid, _escape(optional)))

808

self.db.query(query)

809

810

self.commit()

811

except:

812

self.rollback()

813

raise

814

815

def set_worksheet_assessable(self, subject, worksheet, assessable,

816

dry=False):

817

"""

818

Sets the "assessable" field of a worksheet without updating the mtime.

819

820

IMPORTANT: This will NOT update the mtime. This is designed to allow

821

updates which did not come from the worksheet XML file. It would be

822

bad to update the mtime without consulting the XML file because then

823

it would appear the database is up to date, when it isn't.

824

825

Therefore, call this method if you are getting "assessable"

826

information from outside the worksheet XML file (eg. from the subject

827

XML file).

828

829

Unlike create_worksheet, raises a DBException if the worksheet is not

830

in the database.

831

"""

832

return self.update({"subject": subject, "identifier": worksheet},

833

{"assessable": assessable}, "worksheet", ["assessable"],

834

["subject", "identifier"], dry=dry)

835

836

def worksheet_is_assessable(self, subject, worksheet, dry=False):

837

r = self.get_single(

838

{"subject": subject, "identifier": worksheet},

839

"worksheet", ["assessable"], ["subject", "identifier"], dry=dry)

840

return _parse_boolean(r["assessable"])

841

842

def calculate_score_worksheet(self, login, subject, worksheet):

843

"""

844

Calculates the score for a user on a given worksheet.

845

Returns a 4-tuple of ints, consisting of:

846

(No. mandatory exercises completed,

847

Total no. mandatory exercises,

848

No. optional exercises completed,

849

Total no. optional exercises)

850

"""

851

self.start_transaction()

852

try:

853

mand_done = 0

854

mand_total = 0

855

opt_done = 0

856

opt_total = 0

857

# Get a list of problems and optionality for all problems in the

858

# worksheet

859

query = ("""SELECT problemid, optional FROM worksheet_problem

860

WHERE worksheetid = (SELECT worksheetid FROM worksheet

861

WHERE subject = %s and identifier = %s);"""

862

% (_escape(subject), _escape(worksheet)))

863

result = self.db.query(query)

864

# Now get the student's pass/fail for each problem in this worksheet

865

for problemid, optional in result.getresult():

866

done, _ = self.get_problem_status(login, problemid)

867

# done is a bool, whether this student has completed that

868

# problem

869

if _parse_boolean(optional):

870

opt_total += 1

871

if done: opt_done += 1

872

else:

873

mand_total += 1

874

if done: mand_done += 1

875

self.commit()

876

except:

877

self.rollback()

878

raise

879

return mand_done, mand_total, opt_done, opt_total

880

881

# ENROLMENT INFORMATION

882

883

def add_enrolment(self, login, subj_code, semester, year=None, dry=False):

884

"""

885

Enrol a student in the given offering of a subject.

886

Returns True on success, False on failure (which usually means either

887

the student is already enrolled in the subject, the student was not

888

found, or no offering existed with the given details).

889

The return value can usually be ignored.

890

"""

891

subj_code = str(subj_code)

892

semester = str(semester)

893

if year is None:

894

year = str(time.gmtime().tm_year)

895

else:

896

year = str(year)

897

query = """\

898

INSERT INTO enrolment (loginid, offeringid)

899

VALUES (

900

(SELECT loginid FROM login WHERE login=%s),

901

(SELECT offeringid

902

FROM offering, subject, semester

903

WHERE subject.subjectid = offering.subject

904

AND semester.semesterid = offering.semesterid

905

AND subj_code=%s AND semester=%s AND year=%s)

906

);""" % (_escape(login), _escape(subj_code), _escape(semester),

907

_escape(year))

908

if dry:

909

return query

910

try:

911

result = self.db.query(query)

912

except pg.ProgrammingError:

913

return False

914

return True

915

916

# SUBJECTS AND ENROLEMENT

917

918

def get_subjects(self, dry=False):

919

"""

920

Get all subjects in IVLE.

921

Returns a list of dicts (all values strings), with the keys:

922

subj_code, subj_name, subj_short_name, url

923

"""

924

return self.get_all("subject",

925

("subjectid", "subj_code", "subj_name", "subj_short_name", "url"),

926

dry)

927

928

def get_offering_semesters(self, subjectid, dry=False):

929

"""

930

Get the semester information for a subject as well as providing

931

information about if the subject is active and which semester it is in.

932

"""

933

query = """\

934

SELECT offeringid, subj_name, year, semester, active

935

FROM semester, offering, subject

936

WHERE offering.semesterid = semester.semesterid AND

937

offering.subject = subject.subjectid AND

938

offering.subject = %d;"""%subjectid

939

if dry:

940

return query

941

results = self.db.query(query).dictresult()

942

# Parse boolean varibles

943

for result in results:

944

result['active'] = _parse_boolean(result['active'])

945

return results

946

947

def get_offering_members(self, offeringid, dry=False):

948

"""

949

Gets the logins of all the people enroled in an offering

950

"""

951

query = """\

952

SELECT login.login AS login, login.fullname AS fullname

953

FROM login, enrolment

954

WHERE login.loginid = enrolment.loginid AND

955

enrolment.offeringid = %d

956

ORDER BY login.login;"""%offeringid

957

if dry:

958

return query

959

return self.db.query(query).dictresult()

960

961

962

def get_enrolment(self, login, dry=False):

963

"""

964

Get all offerings (in IVLE) the student is enrolled in.

965

Returns a list of dicts (all values strings), with the keys:

966

offeringid, subj_code, subj_name, subj_short_name, year, semester, url

967

"""

968

query = """\

969

SELECT offering.offeringid, subj_code, subj_name, subj_short_name,

970

semester.year, semester.semester, subject.url

971

FROM login, enrolment, offering, subject, semester

972

WHERE enrolment.offeringid=offering.offeringid

973

AND login.loginid=enrolment.loginid

974

AND offering.subject=subject.subjectid

975

AND semester.semesterid=offering.semesterid

976

AND enrolment.active

977

AND login=%s;""" % _escape(login)

978

if dry:

979

return query

980

return self.db.query(query).dictresult()

981

982

def get_enrolment_groups(self, login, offeringid, dry=False):

983

"""

984

Get all groups the user is member of in the given offering.

985

Returns a list of dicts (all values strings), with the keys:

986

name, nick

987

"""

988

query = """\

989

SELECT project_group.groupnm as name, project_group.nick as nick

990

FROM project_set, project_group, group_member, login

991

WHERE login.login=%s

992

AND project_set.offeringid=%s

993

AND group_member.loginid=login.loginid

994

AND project_group.groupid=group_member.groupid

995

AND project_group.projectsetid=project_set.projectsetid

996

""" % (_escape(login), _escape(offeringid))

997

if dry:

998

return query

999

return self.db.query(query).dictresult()

1000

1001

def get_subjects_status(self, login, dry=False):

1002

"""

1003

Get all subjects in IVLE, split into lists of enrolled and unenrolled

1004

subjects.

1005

Returns a tuple of lists (enrolled, unenrolled) of dicts

1006

(all values strings) with the keys:

1007

subj_code, subj_name, subj_short_name, url

1008

"""

1009

enrolments = self.get_enrolment(login)

1010

all_subjects = self.get_subjects()

1011

1012

enrolled_set = set(x['subj_code'] for x in enrolments)

1013

1014

enrolled_subjects = [x for x in all_subjects

1015

if x['subj_code'] in enrolled_set]

1016

unenrolled_subjects = [x for x in all_subjects

1017

if x['subj_code'] not in enrolled_set]

1018

enrolled_subjects.sort(key=lambda x: x['subj_code'])

1019

unenrolled_subjects.sort(key=lambda x: x['subj_code'])

1020

return (enrolled_subjects, unenrolled_subjects)

1021

1022

1023

# PROJECT GROUPS

1024

def get_groups_by_user(self, login, offeringid=None, dry=False):

1025

"""

1026

Get all project groups the student is in, corresponding to a

1027

particular subject offering (or all offerings, if omitted).

1028

Returns a list of tuples:

1029

(int groupid, str groupnm, str group_nick, bool is_member).

1030

(Note: If is_member is false, it means they have just been invited to

1031

this group, not a member).

1032

"""

1033

if offeringid is None:

1034

and_offering = ""

1035

else:

1036

and_projectset_table = ", project_set"

1037

and_offering = """

1038

AND project_group.projectsetid = project_set.projectsetid

1039

AND project_set.offeringid = %s""" % _escape(offeringid)

1040

# Union both the groups this user is a member of, and the groups this

1041

# user is invited to.

1042

query = """\

1043

SELECT project_group.groupid, groupnm, project_group.nick, True

1044

FROM project_group, group_member, login %(and_projectset_table)s

1045

WHERE project_group.groupid = group_member.groupid

1046

AND group_member.loginid = login.loginid

1047

AND login = %(login)s

1048

%(and_offering)s

1049

UNION

1050

SELECT project_group.groupid, groupnm, project_group.nick, False

1051

FROM project_group, group_invitation, login %(and_projectset_table)s

1052

WHERE project_group.groupid = group_invitation.groupid

1053

AND group_invitation.loginid = login.loginid

1054

AND login = %(login)s

1055

%(and_offering)s

1056

;""" % {"login": _escape(login), "and_offering": and_offering,

1057

"and_projectset_table": and_projectset_table}

1058

if dry:

1059

return query

1060

# Convert 't' -> True, 'f' -> False

1061

return [(groupid, groupnm, nick, ismember == 't')

1062

for groupid, groupnm, nick, ismember

1063

in self.db.query(query).getresult()]

1064

1065

def get_offering_info(self, projectsetid, dry=False):

1066

"""Takes information from projectset and returns useful information

1067

about the subject and semester. Returns as a dictionary.

1068

"""

1069

query = """\

1070

SELECT subjectid, subj_code, subj_name, subj_short_name, url, year, semester,

1071

active

1072

FROM subject, offering, semester, project_set

1073

WHERE offering.subject = subject.subjectid AND

1074

offering.semesterid = semester.semesterid AND

1075

project_set.offeringid = offering.offeringid AND

1076

project_set.projectsetid = %d;"""%projectsetid

1077

if dry:

1078

return query

1079

return self.db.query(query).dictresult()[0]

1080

1081

def get_projectgroup_members(self, groupid, dry=False):

1082

"""Returns the logins of all students in a project group

1083

"""

1084

query = """\

1085

SELECT login.login as login, login.fullname as fullname

1086

FROM login, group_member

1087

WHERE login.loginid = group_member.loginid AND

1088

group_member.groupid = %d

1089

ORDER BY login.login;"""%groupid

1090

if dry:

1091

return query

1092

return self.db.query(query).dictresult()

1093

1094

def get_projectsets_by_offering(self, offeringid, dry=False):

1095

"""Returns all the projectsets in a particular offering"""

1096

query = """\

1097

SELECT projectsetid, max_students_per_group

1098

FROM project_set

1099

WHERE project_set.offeringid = %d;"""%offeringid

1100

if dry:

1101

return query

1102

return self.db.query(query).dictresult()

1103

1104

def get_groups_by_projectset(self, projectsetid, dry=False):

1105

"""Returns all the groups that are in a particular projectset"""

1106

query = """\

1107

SELECT groupid, groupnm, nick, createdby, epoch

1108

FROM project_group

1109

WHERE project_group.projectsetid = %d;"""%projectsetid

1110

if dry:

1111

return query

1112

return self.db.query(query).dictresult()

1113

1114

def close(self):

1115

"""Close the DB connection. Do not call any other functions after

1116

this. (The behaviour of doing so is undefined).

1117

"""

1118

self.db.close()

1119

self.open = False