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
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 'role_ids': Parameter([int], "List of role identifiers"),
53 'roles': Parameter([str], "List of roles"),
54 'site_ids': Parameter([int], "List of site identifiers"),
55 'key_ids': Parameter([int], "List of key identifiers"),
56 'slice_ids': Parameter([int], "List of slice identifiers"),
57 'peer_id': Parameter(int, "Peer to which this user belongs", nullok = True),
58 'peer_person_id': Parameter(int, "Foreign user identifier at peer", nullok = True),
61 'roles': [Mixed(Parameter(int, "Role identifier"),
62 Parameter(str, "Role name"))],
63 'sites': [Mixed(Parameter(int, "Site identifier"),
64 Parameter(str, "Site name"))],
65 'keys': [Mixed(Parameter(int, "Key identifier"),
67 'slices': [Mixed(Parameter(int, "Slice identifier"),
68 Parameter(str, "Slice name"))]
75 foreign_fields = ['first_name', 'last_name', 'title', 'email', 'phone', 'url',
76 'bio', 'enabled', 'password', ]
77 # forget about these ones, they are read-only anyway
78 # handling them causes Cache to re-sync all over again
79 # 'last_updated', 'date_created'
81 {'field' : 'key_ids', 'class': 'Key', 'table' : 'person_key' } ,
82 {'field' : 'site_ids', 'class': 'Site', 'table' : 'person_site'},
83 # xxx this is not handled by Cache yet
84 # 'role_ids': Parameter([int], "List of role identifiers"),
87 def validate_email(self, email):
89 Validate email address. Stolen from Mailman.
92 invalid_email = PLCInvalidArgument("Invalid e-mail address")
93 email_badchars = r'[][()<>|;^,\200-\377]'
95 # Pretty minimal, cheesy check. We could do better...
96 if not email or email.count(' ') > 0:
98 if re.search(email_badchars, email) or email[0] == '-':
101 email = email.lower()
102 at_sign = email.find('@')
105 user = email[:at_sign]
106 rest = email[at_sign+1:]
107 domain = rest.split('.')
109 # This means local, unqualified addresses, are not allowed
115 # check only against users on the same peer
116 if 'peer_id' in self:
117 namespace_peer_id = self['peer_id']
119 namespace_peer_id = None
121 conflicts = Persons(self.api, {'email':email,'peer_id':namespace_peer_id})
123 for person in conflicts:
124 if 'person_id' not in self or self['person_id'] != person['person_id']:
125 raise PLCInvalidArgument, "E-mail address already in use"
129 def validate_password(self, password):
131 Encrypt password if necessary before committing to the
137 if len(password) > len(magic) and \
138 password[0:len(magic)] == magic:
141 # Generate a somewhat unique 8 character salt string
142 salt = str(time.time()) + str(Random().random())
143 salt = md5.md5(salt).hexdigest()[:8]
144 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
146 validate_date_created = Row.validate_timestamp
147 validate_last_updated = Row.validate_timestamp
148 validate_verification_expires = Row.validate_timestamp
150 def can_update(self, person):
152 Returns true if we can update the specified person. We can
155 1. We are the person.
157 3. We are a PI and the person is a user or tech or at
161 assert isinstance(person, Person)
163 if self['person_id'] == person['person_id']:
166 if 'admin' in self['roles']:
169 if 'pi' in self['roles']:
170 if set(self['site_ids']).intersection(person['site_ids']):
171 # Can update people with higher role IDs
172 return min(self['role_ids']) < min(person['role_ids'])
176 def can_view(self, person):
178 Returns true if we can view the specified person. We can
181 1. We are the person.
183 3. We are a PI and the person is at one of our sites.
186 assert isinstance(person, Person)
188 if self.can_update(person):
191 if 'pi' in self['roles']:
192 if set(self['site_ids']).intersection(person['site_ids']):
193 # Can view people with equal or higher role IDs
194 return min(self['role_ids']) <= min(person['role_ids'])
198 add_role = Row.add_object(Role, 'person_role')
199 remove_role = Row.remove_object(Role, 'person_role')
201 add_key = Row.add_object(Key, 'person_key')
202 remove_key = Row.remove_object(Key, 'person_key')
204 def set_primary_site(self, site, commit = True):
206 Set the primary site for an existing user.
209 assert 'person_id' in self
210 assert 'site_id' in site
212 person_id = self['person_id']
213 site_id = site['site_id']
214 self.api.db.do("UPDATE person_site SET is_primary = False" \
215 " WHERE person_id = %(person_id)d",
217 self.api.db.do("UPDATE person_site SET is_primary = True" \
218 " WHERE person_id = %(person_id)d" \
219 " AND site_id = %(site_id)d",
225 assert 'site_ids' in self
226 assert site_id in self['site_ids']
228 # Make sure that the primary site is first in the list
229 self['site_ids'].remove(site_id)
230 self['site_ids'].insert(0, site_id)
232 def update_last_updated(self, commit = True):
234 Update last_updated field with current time
237 assert 'person_id' in self
238 assert self.table_name
240 self.api.db.do("UPDATE %s SET last_updated = CURRENT_TIMESTAMP " % (self.table_name) + \
241 " where person_id = %d" % (self['person_id']) )
244 def associate_roles(self, auth, field, value):
246 Adds roles found in value list to this person (using AddRoleToPerson).
247 Deletes roles not found in value list from this person (using DeleteRoleFromPerson).
250 assert 'role_ids' in self
251 assert 'person_id' in self
252 assert isinstance(value, list)
254 (role_ids, roles_names) = self.separate_types(value)[0:2]
256 # Translate roles into role_ids
258 roles = Roles(self.api, role_names, ['role_id']).dict('role_id')
259 role_ids += roles.keys()
261 # Add new ids, remove stale ids
262 if self['role_ids'] != role_ids:
263 from PLC.Methods.AddRoleToPerson import AddRoleToPerson
264 from PLC.Methods.DeleteRoleFromPerson import DeleteRoleFromPerson
265 new_roles = set(role_ids).difference(self['role_ids'])
266 stale_roles = set(self['role_ids']).difference(role_ids)
268 for new_role in new_roles:
269 AddRoleToPerson.__call__(AddRoleToPerson(self.api), auth, new_role, self['person_id'])
270 for stale_role in stale_roles:
271 DeleteRoleFromPerson.__call__(DeleteRoleFromPerson(self.api), auth, stale_role, self['person_id'])
274 def associate_sites(self, auth, field, value):
276 Adds person to sites found in value list (using AddPersonToSite).
277 Deletes person from site not found in value list (using DeletePersonFromSite).
280 from PLC.Sites import Sites
282 assert 'site_ids' in self
283 assert 'person_id' in self
284 assert isinstance(value, list)
286 (site_ids, site_names) = self.separate_types(value)[0:2]
288 # Translate roles into role_ids
290 sites = Sites(self.api, site_names, ['site_id']).dict('site_id')
291 site_ids += sites.keys()
293 # Add new ids, remove stale ids
294 if self['site_ids'] != site_ids:
295 from PLC.Methods.AddPersonToSite import AddPersonToSite
296 from PLC.Methods.DeletePersonFromSite import DeletePersonFromSite
297 new_sites = set(site_ids).difference(self['site_ids'])
298 stale_sites = set(self['site_ids']).difference(site_ids)
300 for new_site in new_sites:
301 AddPersonToSite.__call__(AddPersonToSite(self.api), auth, self['person_id'], new_site)
302 for stale_site in stale_sites:
303 DeletePersonFromSite.__call__(DeletePersonFromSite(self.api), auth, self['person_id'], stale_site)
306 def associate_keys(self, auth, field, value):
308 Deletes key_ids not found in value list (using DeleteKey).
309 Adds key if key_fields w/o key_id is found (using AddPersonKey).
310 Updates key if key_fields w/ key_id is found (using UpdateKey).
312 assert 'key_ids' in self
313 assert 'person_id' in self
314 assert isinstance(value, list)
316 (key_ids, blank, keys) = self.separate_types(value)
318 if self['key_ids'] != key_ids:
319 from PLC.Methods.DeleteKey import DeleteKey
320 stale_keys = set(self['key_ids']).difference(key_ids)
322 for stale_key in stale_keys:
323 DeleteKey.__call__(DeleteKey(self.api), auth, stale_key)
326 from PLC.Methods.AddPersonKey import AddPersonKey
327 from PLC.Methods.UpdateKey import UpdateKey
328 updated_keys = filter(lambda key: 'key_id' in key, keys)
329 added_keys = filter(lambda key: 'key_id' not in key, keys)
331 for key in added_keys:
332 AddPersonKey.__call__(AddPersonKey(self.api), auth, self['person_id'], key)
333 for key in updated_keys:
334 key_id = key.pop('key_id')
335 UpdateKey.__call__(UpdateKey(self.api), auth, key_id, key)
338 def associate_slices(self, auth, field, value):
340 Adds person to slices found in value list (using AddPersonToSlice).
341 Deletes person from slices found in value list (using DeletePersonFromSlice).
344 from PLC.Slices import Slices
346 assert 'slice_ids' in self
347 assert 'person_id' in self
348 assert isinstance(value, list)
350 (slice_ids, slice_names) = self.separate_types(value)[0:2]
352 # Translate roles into role_ids
354 slices = Slices(self.api, slice_names, ['slice_id']).dict('slice_id')
355 slice_ids += slices.keys()
357 # Add new ids, remove stale ids
358 if self['slice_ids'] != slice_ids:
359 from PLC.Methods.AddPersonToSlice import AddPersonToSlice
360 from PLC.Methods.DeletePersonFromSlice import DeletePersonFromSlice
361 new_slices = set(slice_ids).difference(self['slice_ids'])
362 stale_slices = set(self['slice_ids']).difference(slice_ids)
364 for new_slice in new_slices:
365 AddPersonToSlice.__call__(AddPersonToSlice(self.api), auth, self['person_id'], new_slice)
366 for stale_slice in stale_slices:
367 DeletePersonFromSlice.__call__(DeletePersonFromSlice(self.api), auth, self['person_id'], stale_slice)
370 def delete(self, commit = True):
372 Delete existing user.
376 keys = Keys(self.api, self['key_ids'])
378 key.delete(commit = False)
380 # Clean up miscellaneous join tables
381 for table in self.join_tables:
382 self.api.db.do("DELETE FROM %s WHERE person_id = %d" % \
383 (table, self['person_id']))
386 self['deleted'] = True
389 class Persons(Table):
391 Representation of row(s) from the persons table in the
395 def __init__(self, api, person_filter = None, columns = None):
396 Table.__init__(self, api, Person, columns)
397 #sql = "SELECT %s FROM view_persons WHERE deleted IS False" % \
398 # ", ".join(self.columns)
399 foreign_fields = {'role_ids': ('role_id', 'person_role'),
400 'roles': ('name', 'roles'),
401 'site_ids': ('site_id', 'person_site'),
402 'key_ids': ('key_id', 'person_key'),
403 'slice_ids': ('slice_id', 'slice_person')
406 db_fields = filter(lambda field: field not in foreign_fields.keys(), Person.fields.keys())
407 all_fields = db_fields + [value[0] for value in foreign_fields.values()]
410 _from = " FROM persons "
411 _join = " LEFT JOIN peer_person USING (person_id) "
412 _where = " WHERE deleted IS False "
415 # include all columns
417 tables = [value[1] for value in foreign_fields.values()]
419 for key in foreign_fields.keys():
420 foreign_keys[foreign_fields[key][0]] = key
422 if table in ['roles']:
423 _join += " LEFT JOIN roles USING(role_id) "
425 _join += " LEFT JOIN %s USING (person_id) " % (table)
428 columns = filter(lambda column: column in db_fields+foreign_fields.keys(), columns)
430 for column in columns:
431 if column in foreign_fields.keys():
432 (field, table) = foreign_fields[column]
433 foreign_keys[field] = column
436 if column in ['roles']:
437 _join += " LEFT JOIN roles USING(role_id) "
439 _join += " LEFT JOIN %s USING (person_id)" % \
440 (foreign_fields[column][1])
445 # postgres will return timestamps as datetime objects.
446 # XMLPRC cannot marshal datetime so convert to int
447 timestamps = ['date_created', 'last_updated', 'verification_expires']
449 if field in timestamps:
450 fields[fields.index(field)] = \
451 "CAST(date_part('epoch', %s) AS bigint) AS %s" % (field, field)
453 _select += ", ".join(fields)
454 sql = _select + _from + _join + _where
457 if person_filter is not None:
458 if isinstance(person_filter, (list, tuple, set)):
459 # Separate the list into integers and strings
460 ints = filter(lambda x: isinstance(x, (int, long)), person_filter)
461 strs = filter(lambda x: isinstance(x, StringTypes), person_filter)
462 person_filter = Filter(Person.fields, {'person_id': ints, 'email': strs})
463 sql += " AND (%s) %s" % person_filter.sql(api, "OR")
464 elif isinstance(person_filter, dict):
465 person_filter = Filter(Person.fields, person_filter)
466 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
467 elif isinstance (person_filter, StringTypes):
468 person_filter = Filter(Person.fields, {'email':[person_filter]})
469 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
470 elif isinstance (person_filter, int):
471 person_filter = Filter(Person.fields, {'person_id':[person_filter]})
472 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
474 raise PLCInvalidArgument, "Wrong person filter %r"%person_filter
478 for row in self.api.db.selectall(sql):
479 person_id = row['person_id']
481 if all_persons.has_key(person_id):
482 for (key, key_list) in foreign_keys.items():
484 row[key_list] = [data]
485 if data and data not in all_persons[person_id][key_list]:
486 all_persons[person_id][key_list].append(data)
488 for key in foreign_keys.keys():
491 row[foreign_keys[key]] = [value]
493 row[foreign_keys[key]] = []
495 all_persons[person_id] = row
498 for row in all_persons.values():
499 obj = self.classobj(self.api, row)