2 # Functions for interacting with the persons table in the database
4 # Mark Huang <mlhuang@cs.princeton.edu>
5 # Copyright (C) 2006 The Trustees of Princeton University
7 # $Id: Persons.py,v 1.1 2006/09/06 15:36:07 mlhuang Exp $
10 from types import StringTypes
11 from datetime import datetime
14 from random import Random
17 from PLC.Faults import *
18 from PLC.Parameter import Parameter
19 from PLC.Debug import profile
20 from PLC.Table import Row, Table
21 from PLC.Roles import Roles
22 from PLC.Addresses import Address, Addresses
23 from PLC.Keys import Key, Keys
24 from PLC import md5crypt
29 Representation of a row in the persons table. To use, optionally
30 instantiate with a dict of values. Update as you would a
31 dict. Commit to the database with flush().
35 'person_id': Parameter(int, "Account identifier"),
36 'first_name': Parameter(str, "Given name"),
37 'last_name': Parameter(str, "Surname"),
38 'title': Parameter(str, "Title"),
39 'email': Parameter(str, "Primary e-mail address"),
40 'phone': Parameter(str, "Telephone number"),
41 'url': Parameter(str, "Home page"),
42 'bio': Parameter(str, "Biography"),
43 'accepted_aup': Parameter(bool, "Has accepted the AUP"),
44 'enabled': Parameter(bool, "Has been enabled"),
45 'deleted': Parameter(bool, "Has been deleted"),
46 'password': Parameter(str, "Account password in crypt() form"),
47 'last_updated': Parameter(str, "Date and time of last update"),
48 'date_created': Parameter(str, "Date and time when account was created"),
51 # These fields are derived from join tables and are not actually
52 # in the persons table.
54 'role_ids': Parameter([int], "List of role identifiers"),
55 'roles': Parameter([str], "List of roles"),
56 'site_ids': Parameter([int], "List of site identifiers"),
59 # These fields are derived from join tables and are not returned
60 # by default unless specified.
62 'address_ids': Parameter([int], "List of address identifiers"),
63 'key_ids': Parameter([int], "List of key identifiers"),
64 'slice_ids': Parameter([int], "List of slice identifiers"),
67 default_fields = dict(fields.items() + join_fields.items())
68 all_fields = dict(default_fields.items() + extra_fields.items())
70 def __init__(self, api, fields):
71 Row.__init__(self, fields)
74 def validate_email(self, email):
76 Validate email address. Stolen from Mailman.
79 invalid_email = PLCInvalidArgument("Invalid e-mail address")
80 email_badchars = r'[][()<>|;^,\200-\377]'
82 # Pretty minimal, cheesy check. We could do better...
83 if not email or email.count(' ') > 0:
85 if re.search(email_badchars, email) or email[0] == '-':
89 at_sign = email.find('@')
92 user = email[:at_sign]
93 rest = email[at_sign+1:]
94 domain = rest.split('.')
96 # This means local, unqualified addresses, are no allowed
102 conflicts = Persons(self.api, [email])
103 for person_id, person in conflicts.iteritems():
104 if not person['deleted'] and ('person_id' not in self or self['person_id'] != person_id):
105 raise PLCInvalidArgument, "E-mail address already in use"
109 def validate_password(self, password):
111 Encrypt password if necessary before committing to the
115 if len(password) > len(md5crypt.MAGIC) and \
116 password[0:len(md5crypt.MAGIC)] == md5crypt.MAGIC:
119 # Generate a somewhat unique 2 character salt string
120 salt = str(time.time()) + str(Random().random())
121 salt = md5.md5(salt).hexdigest()[:8]
122 return md5crypt.md5crypt(password, salt)
124 def validate_role_ids(self, role_ids):
126 Ensure that the specified role_ids are all valid.
129 roles = Roles(self.api)
130 for role_id in role_ids:
131 if role_id not in roles:
132 raise PLCInvalidArgument, "No such role"
136 def validate_site_ids(self, site_ids):
138 Ensure that the specified site_ids are all valid.
141 sites = PLC.Sites.Sites(self.api, site_ids)
142 for site_id in site_ids:
143 if site_id not in sites:
144 raise PLCInvalidArgument, "No such site"
148 def can_update(self, person):
150 Returns true if we can update the specified person. We can
153 1. We are the person.
155 3. We are a PI and the person is a user or tech or at
159 assert isinstance(person, Person)
161 if self['person_id'] == person['person_id']:
164 if 'admin' in self['roles']:
167 if 'pi' in self['roles']:
168 if set(self['site_ids']).intersection(person['site_ids']):
169 # Can update people with higher role IDs
170 return min(self['role_ids']) < min(person['role_ids'])
174 def can_view(self, person):
176 Returns true if we can view the specified person. We can
179 1. We are the person.
181 3. We are a PI and the person is at one of our sites.
184 assert isinstance(person, Person)
186 if self.can_update(person):
189 if 'pi' in self['roles']:
190 if set(self['site_ids']).intersection(person['site_ids']):
191 # Can view people with equal or higher role IDs
192 return min(self['role_ids']) <= min(person['role_ids'])
196 def add_role(self, role_id, commit = True):
198 Add role to existing account.
201 assert 'person_id' in self
203 person_id = self['person_id']
204 self.api.db.do("INSERT INTO person_roles (person_id, role_id)" \
205 " VALUES(%(person_id)d, %(role_id)d)",
211 assert 'role_ids' in self
212 if role_id not in self['role_ids']:
213 self['role_ids'].append(role_id)
215 def remove_role(self, role_id, commit = True):
217 Remove role from existing account.
220 assert 'person_id' in self
222 person_id = self['person_id']
223 self.api.db.do("DELETE FROM person_roles" \
224 " WHERE person_id = %(person_id)d" \
225 " AND role_id = %(role_id)d",
231 assert 'role_ids' in self
232 if role_id in self['role_ids']:
233 self['role_ids'].remove(role_id)
235 def set_primary_site(self, site, commit = True):
237 Set the primary site for an existing account.
240 assert 'person_id' in self
241 assert isinstance(site, PLC.Sites.Site)
242 assert 'site_id' in site
244 person_id = self['person_id']
245 site_id = site['site_id']
246 self.api.db.do("UPDATE person_site SET is_primary = False" \
247 " WHERE person_id = %(person_id)d",
249 self.api.db.do("UPDATE person_site SET is_primary = True" \
250 " WHERE person_id = %(person_id)d" \
251 " AND site_id = %(site_id)d",
257 assert 'site_ids' in self
258 assert site_id in self['site_ids']
260 # Make sure that the primary site is first in the list
261 self['site_ids'].remove(site_id)
262 self['site_ids'].insert(0, site_id)
264 def flush(self, commit = True):
266 Commit changes back to the database.
271 # Fetch a new person_id if necessary
272 if 'person_id' not in self:
273 rows = self.api.db.selectall("SELECT NEXTVAL('persons_person_id_seq') AS person_id")
275 raise PLCDBError, "Unable to fetch new person_id"
276 self['person_id'] = rows[0]['person_id']
281 # Filter out fields that cannot be set or updated directly
282 fields = dict(filter(lambda (key, value): key in self.fields,
285 # Parameterize for safety
287 values = [self.api.db.param(key, value) for (key, value) in fields.items()]
290 # Insert new row in persons table
291 sql = "INSERT INTO persons (%s) VALUES (%s)" % \
292 (", ".join(keys), ", ".join(values))
294 # Update existing row in persons table
295 columns = ["%s = %s" % (key, value) for (key, value) in zip(keys, values)]
296 sql = "UPDATE persons SET " + \
297 ", ".join(columns) + \
298 " WHERE person_id = %(person_id)d"
300 self.api.db.do(sql, fields)
305 def delete(self, commit = True):
307 Delete existing account.
310 assert 'person_id' in self
312 # Make sure extra fields are present
313 persons = Persons(self.api, [self['person_id']],
314 ['address_ids', 'key_ids'])
316 self.update(persons.values()[0])
318 # Delete all addresses
319 addresses = Addresses(self.api, self['address_ids'])
320 for address in addresses.values():
321 address.delete(commit = False)
324 keys = Keys(self.api, self['key_ids'])
325 for key in keys.values():
326 key.delete(commit = False)
328 # Clean up miscellaneous join tables
329 for table in ['person_roles', 'person_capabilities', 'person_site',
330 'node_root_access', 'dslice03_sliceuser']:
331 self.api.db.do("DELETE FROM %s" \
332 " WHERE person_id = %d" % \
333 (table, self['person_id']))
336 self['deleted'] = True
339 class Persons(Table):
341 Representation of row(s) from the persons table in the
342 database. Specify deleted and/or enabled to force a match on
343 whether a person is deleted and/or enabled. Default is to match on
344 non-deleted accounts.
347 def __init__(self, api, person_id_or_email_list = None, extra_fields = [], deleted = False, enabled = None):
350 role_max = Roles.role_max
352 # N.B.: Site IDs returned may be deleted. Persons returned are
353 # never deleted, but may not be enabled.
354 sql = "SELECT persons.*" \
355 ", roles.role_id, roles.name AS role" \
356 ", person_site.site_id" \
358 # N.B.: Joined IDs may be marked as deleted in their primary tables
360 # extra_field: (extra_table, extra_column, join_using)
361 'address_ids': ('person_address', 'address_id', 'person_id'),
362 'key_ids': ('person_keys', 'key_id', 'person_id'),
363 'slice_ids': ('dslice03_sliceuser', 'slice_id', 'person_id'),
366 extra_fields = filter(join_tables.has_key, extra_fields)
367 extra_tables = ["%s USING (%s)" % \
368 (join_tables[field][0], join_tables[field][2]) \
369 for field in extra_fields]
370 extra_columns = ["%s.%s" % \
371 (join_tables[field][0], join_tables[field][1]) \
372 for field in extra_fields]
375 sql += ", " + ", ".join(extra_columns)
377 sql += " FROM persons" \
378 " LEFT JOIN person_roles USING (person_id)" \
379 " LEFT JOIN roles USING (role_id)" \
380 " LEFT JOIN person_site USING (person_id)"
383 sql += " LEFT JOIN " + " LEFT JOIN ".join(extra_tables)
385 # So that people with no roles have empty role_ids and roles values
386 sql += " WHERE (role_id IS NULL or role_id <= %(role_max)d)"
388 if deleted is not None:
389 sql += " AND persons.deleted IS %(deleted)s"
391 if enabled is not None:
392 sql += " AND persons.enabled IS %(enabled)s"
394 if person_id_or_email_list:
395 # Separate the list into integers and strings
396 person_ids = filter(lambda person_id: isinstance(person_id, (int, long)),
397 person_id_or_email_list)
398 emails = filter(lambda email: isinstance(email, StringTypes),
399 person_id_or_email_list)
402 sql += " OR person_id IN (%s)" % ", ".join(map(str, person_ids))
404 # Case insensitive e-mail address comparison
405 sql += " OR lower(email) IN (%s)" % ", ".join(api.db.quote(emails)).lower()
408 # The first site_id in the site_ids list is the primary site
409 # of the user. See AdmGetPersonSites().
410 sql += " ORDER BY person_site.is_primary DESC"
412 rows = self.api.db.selectall(sql, locals())
414 if self.has_key(row['person_id']):
415 person = self[row['person_id']]
418 self[row['person_id']] = Person(api, row)