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 5652 2007-11-06 03:42:57Z tmack $
10 from types import StringTypes
11 from datetime import datetime
14 from random import Random
18 from PLC.Faults import *
19 from PLC.Debug import log
20 from PLC.Parameter import Parameter, Mixed
21 from PLC.Filter import Filter
22 from PLC.Table import Row, Table
23 from PLC.Roles import Role, Roles
24 from PLC.Keys import Key, Keys
25 from PLC.Messages import Message, Messages
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 sync().
34 table_name = 'persons'
35 primary_key = 'person_id'
36 join_tables = ['person_key', 'person_role', 'person_site', 'slice_person', 'person_session', 'peer_person']
38 'person_id': Parameter(int, "User identifier"),
39 'first_name': Parameter(str, "Given name", max = 128),
40 'last_name': Parameter(str, "Surname", max = 128),
41 'title': Parameter(str, "Title", max = 128, nullok = True),
42 'email': Parameter(str, "Primary e-mail address", max = 254),
43 'phone': Parameter(str, "Telephone number", max = 64, nullok = True),
44 'url': Parameter(str, "Home page", max = 254, nullok = True),
45 'bio': Parameter(str, "Biography", max = 254, nullok = True),
46 'enabled': Parameter(bool, "Has been enabled"),
47 'password': Parameter(str, "Account password in crypt() form", max = 254),
48 'verification_key': Parameter(str, "Reset password key", max = 254, nullok = True),
49 'verification_expires': Parameter(int, "Date and time when verification_key expires", nullok = True),
50 'last_updated': Parameter(int, "Date and time of last update", ro = True),
51 'date_created': Parameter(int, "Date and time when account was created", ro = True),
52 'uuid': Parameter(str, "Universal Unique Identifier"),
53 'role_ids': Parameter([int], "List of role identifiers"),
54 'roles': Parameter([str], "List of roles"),
55 'site_ids': Parameter([int], "List of site identifiers"),
56 'key_ids': Parameter([int], "List of key identifiers"),
57 'slice_ids': Parameter([int], "List of slice identifiers"),
58 'peer_id': Parameter(int, "Peer to which this user belongs", nullok = True),
59 'peer_person_id': Parameter(int, "Foreign user identifier at peer", nullok = True),
62 'roles': [Mixed(Parameter(int, "Role identifier"),
63 Parameter(str, "Role name"))],
64 'sites': [Mixed(Parameter(int, "Site identifier"),
65 Parameter(str, "Site name"))],
66 'keys': [Mixed(Parameter(int, "Key identifier"),
68 'slices': [Mixed(Parameter(int, "Slice identifier"),
69 Parameter(str, "Slice name"))]
76 foreign_fields = ['first_name', 'last_name', 'title', 'email', 'phone', 'url',
77 'bio', 'enabled', 'password', 'uuid', ]
78 # forget about these ones, they are read-only anyway
79 # handling them causes Cache to re-sync all over again
80 # 'last_updated', 'date_created'
82 {'field' : 'key_ids', 'class': 'Key', 'table' : 'person_key' } ,
83 {'field' : 'site_ids', 'class': 'Site', 'table' : 'person_site'},
84 # xxx this is not handled by Cache yet
85 # 'role_ids': Parameter([int], "List of role identifiers"),
88 def validate_email(self, email):
90 Validate email address. Stolen from Mailman.
93 invalid_email = PLCInvalidArgument("Invalid e-mail address")
94 email_badchars = r'[][()<>|;^,\200-\377]'
96 # Pretty minimal, cheesy check. We could do better...
97 if not email or email.count(' ') > 0:
99 if re.search(email_badchars, email) or email[0] == '-':
102 email = email.lower()
103 at_sign = email.find('@')
106 user = email[:at_sign]
107 rest = email[at_sign+1:]
108 domain = rest.split('.')
110 # This means local, unqualified addresses, are not allowed
116 # check only against users on the same peer
117 if 'peer_id' in self:
118 namespace_peer_id = self['peer_id']
120 namespace_peer_id = None
122 conflicts = Persons(self.api, {'email':email,'peer_id':namespace_peer_id})
124 for person in conflicts:
125 if 'person_id' not in self or self['person_id'] != person['person_id']:
126 raise PLCInvalidArgument, "E-mail address already in use"
130 def validate_password(self, password):
132 Encrypt password if necessary before committing to the
138 if len(password) > len(magic) and \
139 password[0:len(magic)] == magic:
142 # Generate a somewhat unique 8 character salt string
143 salt = str(time.time()) + str(Random().random())
144 salt = md5.md5(salt).hexdigest()[:8]
145 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
147 validate_date_created = Row.validate_timestamp
148 validate_last_updated = Row.validate_timestamp
149 validate_verification_expires = Row.validate_timestamp
151 def can_update(self, person):
153 Returns true if we can update the specified person. We can
156 1. We are the person.
158 3. We are a PI and the person is a user or tech or at
162 assert isinstance(person, Person)
164 if self['person_id'] == person['person_id']:
167 if 'admin' in self['roles']:
170 if 'pi' in self['roles']:
171 if set(self['site_ids']).intersection(person['site_ids']):
172 # Can update people with higher role IDs
173 return min(self['role_ids']) < min(person['role_ids'])
177 def can_view(self, person):
179 Returns true if we can view the specified person. We can
182 1. We are the person.
184 3. We are a PI and the person is at one of our sites.
187 assert isinstance(person, Person)
189 if self.can_update(person):
192 if 'pi' in self['roles']:
193 if set(self['site_ids']).intersection(person['site_ids']):
194 # Can view people with equal or higher role IDs
195 return min(self['role_ids']) <= min(person['role_ids'])
199 add_role = Row.add_object(Role, 'person_role')
200 remove_role = Row.remove_object(Role, 'person_role')
202 add_key = Row.add_object(Key, 'person_key')
203 remove_key = Row.remove_object(Key, 'person_key')
205 def set_primary_site(self, site, commit = True):
207 Set the primary site for an existing user.
210 assert 'person_id' in self
211 assert 'site_id' in site
213 person_id = self['person_id']
214 site_id = site['site_id']
215 self.api.db.do("UPDATE person_site SET is_primary = False" \
216 " WHERE person_id = %(person_id)d",
218 self.api.db.do("UPDATE person_site SET is_primary = True" \
219 " WHERE person_id = %(person_id)d" \
220 " AND site_id = %(site_id)d",
226 assert 'site_ids' in self
227 assert site_id in self['site_ids']
229 # Make sure that the primary site is first in the list
230 self['site_ids'].remove(site_id)
231 self['site_ids'].insert(0, site_id)
233 def update_last_updated(self, commit = True):
235 Update last_updated field with current time
238 assert 'person_id' in self
239 assert self.table_name
241 self.api.db.do("UPDATE %s SET last_updated = CURRENT_TIMESTAMP " % (self.table_name) + \
242 " where person_id = %d" % (self['person_id']) )
245 def associate_roles(self, auth, field, value):
247 Adds roles found in value list to this person (using AddRoleToPerson).
248 Deletes roles not found in value list from this person (using DeleteRoleFromPerson).
251 assert 'role_ids' in self
252 assert 'person_id' in self
253 assert isinstance(value, list)
255 (role_ids, role_names) = self.separate_types(value)[0:2]
257 # Translate roles into role_ids
259 roles = Roles(self.api, role_names, ['role_id']).dict('role_id')
260 role_ids += roles.keys()
262 # Add new ids, remove stale ids
263 if self['role_ids'] != role_ids:
264 from PLC.Methods.AddRoleToPerson import AddRoleToPerson
265 from PLC.Methods.DeleteRoleFromPerson import DeleteRoleFromPerson
266 new_roles = set(role_ids).difference(self['role_ids'])
267 stale_roles = set(self['role_ids']).difference(role_ids)
269 for new_role in new_roles:
270 AddRoleToPerson.__call__(AddRoleToPerson(self.api), auth, new_role, self['person_id'])
271 for stale_role in stale_roles:
272 DeleteRoleFromPerson.__call__(DeleteRoleFromPerson(self.api), auth, stale_role, self['person_id'])
275 def associate_sites(self, auth, field, value):
277 Adds person to sites found in value list (using AddPersonToSite).
278 Deletes person from site not found in value list (using DeletePersonFromSite).
281 from PLC.Sites import Sites
283 assert 'site_ids' in self
284 assert 'person_id' in self
285 assert isinstance(value, list)
287 (site_ids, site_names) = self.separate_types(value)[0:2]
289 # Translate roles into role_ids
291 sites = Sites(self.api, site_names, ['site_id']).dict('site_id')
292 site_ids += sites.keys()
294 # Add new ids, remove stale ids
295 if self['site_ids'] != site_ids:
296 from PLC.Methods.AddPersonToSite import AddPersonToSite
297 from PLC.Methods.DeletePersonFromSite import DeletePersonFromSite
298 new_sites = set(site_ids).difference(self['site_ids'])
299 stale_sites = set(self['site_ids']).difference(site_ids)
301 for new_site in new_sites:
302 AddPersonToSite.__call__(AddPersonToSite(self.api), auth, self['person_id'], new_site)
303 for stale_site in stale_sites:
304 DeletePersonFromSite.__call__(DeletePersonFromSite(self.api), auth, self['person_id'], stale_site)
307 def associate_keys(self, auth, field, value):
309 Deletes key_ids not found in value list (using DeleteKey).
310 Adds key if key_fields w/o key_id is found (using AddPersonKey).
311 Updates key if key_fields w/ key_id is found (using UpdateKey).
313 assert 'key_ids' in self
314 assert 'person_id' in self
315 assert isinstance(value, list)
317 (key_ids, blank, keys) = self.separate_types(value)
319 if self['key_ids'] != key_ids:
320 from PLC.Methods.DeleteKey import DeleteKey
321 stale_keys = set(self['key_ids']).difference(key_ids)
323 for stale_key in stale_keys:
324 DeleteKey.__call__(DeleteKey(self.api), auth, stale_key)
327 from PLC.Methods.AddPersonKey import AddPersonKey
328 from PLC.Methods.UpdateKey import UpdateKey
329 updated_keys = filter(lambda key: 'key_id' in key, keys)
330 added_keys = filter(lambda key: 'key_id' not in key, keys)
332 for key in added_keys:
333 AddPersonKey.__call__(AddPersonKey(self.api), auth, self['person_id'], key)
334 for key in updated_keys:
335 key_id = key.pop('key_id')
336 UpdateKey.__call__(UpdateKey(self.api), auth, key_id, key)
339 def associate_slices(self, auth, field, value):
341 Adds person to slices found in value list (using AddPersonToSlice).
342 Deletes person from slices found in value list (using DeletePersonFromSlice).
345 from PLC.Slices import Slices
347 assert 'slice_ids' in self
348 assert 'person_id' in self
349 assert isinstance(value, list)
351 (slice_ids, slice_names) = self.separate_types(value)[0:2]
353 # Translate roles into role_ids
355 slices = Slices(self.api, slice_names, ['slice_id']).dict('slice_id')
356 slice_ids += slices.keys()
358 # Add new ids, remove stale ids
359 if self['slice_ids'] != slice_ids:
360 from PLC.Methods.AddPersonToSlice import AddPersonToSlice
361 from PLC.Methods.DeletePersonFromSlice import DeletePersonFromSlice
362 new_slices = set(slice_ids).difference(self['slice_ids'])
363 stale_slices = set(self['slice_ids']).difference(slice_ids)
365 for new_slice in new_slices:
366 AddPersonToSlice.__call__(AddPersonToSlice(self.api), auth, self['person_id'], new_slice)
367 for stale_slice in stale_slices:
368 DeletePersonFromSlice.__call__(DeletePersonFromSlice(self.api), auth, self['person_id'], stale_slice)
371 def delete(self, commit = True):
373 Delete existing user.
377 keys = Keys(self.api, self['key_ids'])
379 key.delete(commit = False)
381 # Clean up miscellaneous join tables
382 for table in self.join_tables:
383 self.api.db.do("DELETE FROM %s WHERE person_id = %d" % \
384 (table, self['person_id']))
387 self['deleted'] = True
390 class Persons(Table):
392 Representation of row(s) from the persons table in the
396 def __init__(self, api, person_filter = None, columns = None):
397 Table.__init__(self, api, Person, columns)
398 #sql = "SELECT %s FROM view_persons WHERE deleted IS False" % \
399 # ", ".join(self.columns)
400 foreign_fields = {'role_ids': ('role_id', 'person_role'),
401 'roles': ('name', 'roles'),
402 'site_ids': ('site_id', 'person_site'),
403 'key_ids': ('key_id', 'person_key'),
404 'slice_ids': ('slice_id', 'slice_person')
407 db_fields = filter(lambda field: field not in foreign_fields.keys(), Person.fields.keys())
408 all_fields = db_fields + [value[0] for value in foreign_fields.values()]
411 _from = " FROM persons "
412 _join = " LEFT JOIN peer_person USING (person_id) "
413 _where = " WHERE deleted IS False "
416 # include all columns
418 tables = [value[1] for value in foreign_fields.values()]
420 for key in foreign_fields.keys():
421 foreign_keys[foreign_fields[key][0]] = key
423 if table in ['roles']:
424 _join += " LEFT JOIN roles USING(role_id) "
426 _join += " LEFT JOIN %s USING (person_id) " % (table)
429 columns = filter(lambda column: column in db_fields+foreign_fields.keys(), columns)
431 for column in columns:
432 if column in foreign_fields.keys():
433 (field, table) = foreign_fields[column]
434 foreign_keys[field] = column
437 if column in ['roles']:
438 _join += " LEFT JOIN roles USING(role_id) "
440 _join += " LEFT JOIN %s USING (person_id)" % \
441 (foreign_fields[column][1])
446 # postgres will return timestamps as datetime objects.
447 # XMLPRC cannot marshal datetime so convert to int
448 timestamps = ['date_created', 'last_updated', 'verification_expires']
450 if field in timestamps:
451 fields[fields.index(field)] = \
452 "CAST(date_part('epoch', %s) AS bigint) AS %s" % (field, field)
454 _select += ", ".join(fields)
455 sql = _select + _from + _join + _where
458 if person_filter is not None:
459 if isinstance(person_filter, (list, tuple, set)):
460 # Separate the list into integers and strings
461 ints = filter(lambda x: isinstance(x, (int, long)), person_filter)
462 strs = filter(lambda x: isinstance(x, StringTypes), person_filter)
463 person_filter = Filter(Person.fields, {'person_id': ints, 'email': strs})
464 sql += " AND (%s) %s" % person_filter.sql(api, "OR")
465 elif isinstance(person_filter, dict):
466 person_filter = Filter(Person.fields, person_filter)
467 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
468 elif isinstance (person_filter, StringTypes):
469 person_filter = Filter(Person.fields, {'email':[person_filter]})
470 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
471 elif isinstance (person_filter, int):
472 person_filter = Filter(Person.fields, {'person_id':[person_filter]})
473 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
475 raise PLCInvalidArgument, "Wrong person filter %r"%person_filter
479 for row in self.api.db.selectall(sql):
480 person_id = row['person_id']
482 if all_persons.has_key(person_id):
483 for (key, key_list) in foreign_keys.items():
485 row[key_list] = [data]
486 if data and data not in all_persons[person_id][key_list]:
487 all_persons[person_id][key_list].append(data)
489 for key in foreign_keys.keys():
492 row[foreign_keys[key]] = [value]
494 row[foreign_keys[key]] = []
496 all_persons[person_id] = row
499 for row in all_persons.values():
500 obj = self.classobj(self.api, row)