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"))]
71 def validate_email(self, email):
73 Validate email address. Stolen from Mailman.
76 invalid_email = PLCInvalidArgument("Invalid e-mail address")
77 email_badchars = r'[][()<>|;^,\200-\377]'
79 # Pretty minimal, cheesy check. We could do better...
80 if not email or email.count(' ') > 0:
82 if re.search(email_badchars, email) or email[0] == '-':
86 at_sign = email.find('@')
89 user = email[:at_sign]
90 rest = email[at_sign+1:]
91 domain = rest.split('.')
93 # This means local, unqualified addresses, are not allowed
99 # check only against users on the same peer
100 if 'peer_id' in self:
101 namespace_peer_id = self['peer_id']
103 namespace_peer_id = None
105 conflicts = Persons(self.api, {'email':email,'peer_id':namespace_peer_id})
107 for person in conflicts:
108 if 'person_id' not in self or self['person_id'] != person['person_id']:
109 raise PLCInvalidArgument, "E-mail address already in use"
113 def validate_password(self, password):
115 Encrypt password if necessary before committing to the
121 if len(password) > len(magic) and \
122 password[0:len(magic)] == magic:
125 # Generate a somewhat unique 8 character salt string
126 salt = str(time.time()) + str(Random().random())
127 salt = md5.md5(salt).hexdigest()[:8]
128 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
130 validate_date_created = Row.validate_timestamp
131 validate_last_updated = Row.validate_timestamp
132 validate_verification_expires = Row.validate_timestamp
134 def can_update(self, person):
136 Returns true if we can update the specified person. We can
139 1. We are the person.
141 3. We are a PI and the person is a user or tech or at
145 assert isinstance(person, Person)
147 if self['person_id'] == person['person_id']:
150 if 'admin' in self['roles']:
153 if 'pi' in self['roles']:
154 if set(self['site_ids']).intersection(person['site_ids']):
155 # Can update people with higher role IDs
156 return min(self['role_ids']) < min(person['role_ids'])
160 def can_view(self, person):
162 Returns true if we can view the specified person. We can
165 1. We are the person.
167 3. We are a PI and the person is at one of our sites.
170 assert isinstance(person, Person)
172 if self.can_update(person):
175 if 'pi' in self['roles']:
176 if set(self['site_ids']).intersection(person['site_ids']):
177 # Can view people with equal or higher role IDs
178 return min(self['role_ids']) <= min(person['role_ids'])
182 add_role = Row.add_object(Role, 'person_role')
183 remove_role = Row.remove_object(Role, 'person_role')
185 add_key = Row.add_object(Key, 'person_key')
186 remove_key = Row.remove_object(Key, 'person_key')
188 def set_primary_site(self, site, commit = True):
190 Set the primary site for an existing user.
193 assert 'person_id' in self
194 assert 'site_id' in site
196 person_id = self['person_id']
197 site_id = site['site_id']
198 self.api.db.do("UPDATE person_site SET is_primary = False" \
199 " WHERE person_id = %(person_id)d",
201 self.api.db.do("UPDATE person_site SET is_primary = True" \
202 " WHERE person_id = %(person_id)d" \
203 " AND site_id = %(site_id)d",
209 assert 'site_ids' in self
210 assert site_id in self['site_ids']
212 # Make sure that the primary site is first in the list
213 self['site_ids'].remove(site_id)
214 self['site_ids'].insert(0, site_id)
216 def update_last_updated(self, commit = True):
218 Update last_updated field with current time
221 assert 'person_id' in self
222 assert self.table_name
224 self.api.db.do("UPDATE %s SET last_updated = CURRENT_TIMESTAMP " % (self.table_name) + \
225 " where person_id = %d" % (self['person_id']) )
228 def associate_roles(self, auth, field, value):
230 Adds roles found in value list to this person (using AddRoleToPerson).
231 Deletes roles not found in value list from this person (using DeleteRoleFromPerson).
234 assert 'role_ids' in self
235 assert 'person_id' in self
236 assert isinstance(value, list)
238 (role_ids, role_names) = self.separate_types(value)[0:2]
240 # Translate roles into role_ids
242 roles = Roles(self.api, role_names, ['role_id']).dict('role_id')
243 role_ids += roles.keys()
245 # Add new ids, remove stale ids
246 if self['role_ids'] != role_ids:
247 from PLC.Methods.AddRoleToPerson import AddRoleToPerson
248 from PLC.Methods.DeleteRoleFromPerson import DeleteRoleFromPerson
249 new_roles = set(role_ids).difference(self['role_ids'])
250 stale_roles = set(self['role_ids']).difference(role_ids)
252 for new_role in new_roles:
253 AddRoleToPerson.__call__(AddRoleToPerson(self.api), auth, new_role, self['person_id'])
254 for stale_role in stale_roles:
255 DeleteRoleFromPerson.__call__(DeleteRoleFromPerson(self.api), auth, stale_role, self['person_id'])
258 def associate_sites(self, auth, field, value):
260 Adds person to sites found in value list (using AddPersonToSite).
261 Deletes person from site not found in value list (using DeletePersonFromSite).
264 from PLC.Sites import Sites
266 assert 'site_ids' in self
267 assert 'person_id' in self
268 assert isinstance(value, list)
270 (site_ids, site_names) = self.separate_types(value)[0:2]
272 # Translate roles into role_ids
274 sites = Sites(self.api, site_names, ['site_id']).dict('site_id')
275 site_ids += sites.keys()
277 # Add new ids, remove stale ids
278 if self['site_ids'] != site_ids:
279 from PLC.Methods.AddPersonToSite import AddPersonToSite
280 from PLC.Methods.DeletePersonFromSite import DeletePersonFromSite
281 new_sites = set(site_ids).difference(self['site_ids'])
282 stale_sites = set(self['site_ids']).difference(site_ids)
284 for new_site in new_sites:
285 AddPersonToSite.__call__(AddPersonToSite(self.api), auth, self['person_id'], new_site)
286 for stale_site in stale_sites:
287 DeletePersonFromSite.__call__(DeletePersonFromSite(self.api), auth, self['person_id'], stale_site)
290 def associate_keys(self, auth, field, value):
292 Deletes key_ids not found in value list (using DeleteKey).
293 Adds key if key_fields w/o key_id is found (using AddPersonKey).
294 Updates key if key_fields w/ key_id is found (using UpdateKey).
296 assert 'key_ids' in self
297 assert 'person_id' in self
298 assert isinstance(value, list)
300 (key_ids, blank, keys) = self.separate_types(value)
302 if self['key_ids'] != key_ids:
303 from PLC.Methods.DeleteKey import DeleteKey
304 stale_keys = set(self['key_ids']).difference(key_ids)
306 for stale_key in stale_keys:
307 DeleteKey.__call__(DeleteKey(self.api), auth, stale_key)
310 from PLC.Methods.AddPersonKey import AddPersonKey
311 from PLC.Methods.UpdateKey import UpdateKey
312 updated_keys = filter(lambda key: 'key_id' in key, keys)
313 added_keys = filter(lambda key: 'key_id' not in key, keys)
315 for key in added_keys:
316 AddPersonKey.__call__(AddPersonKey(self.api), auth, self['person_id'], key)
317 for key in updated_keys:
318 key_id = key.pop('key_id')
319 UpdateKey.__call__(UpdateKey(self.api), auth, key_id, key)
322 def associate_slices(self, auth, field, value):
324 Adds person to slices found in value list (using AddPersonToSlice).
325 Deletes person from slices found in value list (using DeletePersonFromSlice).
328 from PLC.Slices import Slices
330 assert 'slice_ids' in self
331 assert 'person_id' in self
332 assert isinstance(value, list)
334 (slice_ids, slice_names) = self.separate_types(value)[0:2]
336 # Translate roles into role_ids
338 slices = Slices(self.api, slice_names, ['slice_id']).dict('slice_id')
339 slice_ids += slices.keys()
341 # Add new ids, remove stale ids
342 if self['slice_ids'] != slice_ids:
343 from PLC.Methods.AddPersonToSlice import AddPersonToSlice
344 from PLC.Methods.DeletePersonFromSlice import DeletePersonFromSlice
345 new_slices = set(slice_ids).difference(self['slice_ids'])
346 stale_slices = set(self['slice_ids']).difference(slice_ids)
348 for new_slice in new_slices:
349 AddPersonToSlice.__call__(AddPersonToSlice(self.api), auth, self['person_id'], new_slice)
350 for stale_slice in stale_slices:
351 DeletePersonFromSlice.__call__(DeletePersonFromSlice(self.api), auth, self['person_id'], stale_slice)
354 def delete(self, commit = True):
356 Delete existing user.
360 keys = Keys(self.api, self['key_ids'])
362 key.delete(commit = False)
364 # Clean up miscellaneous join tables
365 for table in self.join_tables:
366 self.api.db.do("DELETE FROM %s WHERE person_id = %d" % \
367 (table, self['person_id']))
370 self['deleted'] = True
373 class Persons(Table):
375 Representation of row(s) from the persons table in the
379 def __init__(self, api, person_filter = None, columns = None):
380 Table.__init__(self, api, Person, columns)
382 foreign_fields = {'role_ids': ('role_id', 'person_role'),
383 'roles': ('name', 'roles'),
384 'site_ids': ('site_id', 'person_site'),
385 'key_ids': ('key_id', 'person_key'),
386 'slice_ids': ('slice_id', 'slice_person')
389 db_fields = filter(lambda field: field not in foreign_fields.keys(), Person.fields.keys())
390 all_fields = db_fields + [value[0] for value in foreign_fields.values()]
393 _from = " FROM persons "
394 _join = " LEFT JOIN peer_person USING (person_id) "
395 _where = " WHERE deleted IS False "
398 # include all columns
400 tables = [value[1] for value in foreign_fields.values()]
402 for key in foreign_fields.keys():
403 foreign_keys[foreign_fields[key][0]] = key
405 if table in ['roles']:
406 _join += " LEFT JOIN roles USING(role_id) "
408 _join += " LEFT JOIN %s USING (person_id) " % (table)
411 columns = filter(lambda column: column in db_fields+foreign_fields.keys(), columns)
413 for column in columns:
414 if column in foreign_fields.keys():
415 (field, table) = foreign_fields[column]
416 foreign_keys[field] = column
419 if column in ['roles']:
420 _join += " LEFT JOIN roles USING(role_id) "
422 _join += " LEFT JOIN %s USING (person_id)" % \
423 (foreign_fields[column][1])
428 # postgres will return timestamps as datetime objects.
429 # XMLPRC cannot marshal datetime so convert to int
430 timestamps = ['date_created', 'last_updated', 'verification_expires']
432 if field in timestamps:
433 fields[fields.index(field)] = \
434 "CAST(date_part('epoch', %s) AS bigint) AS %s" % (field, field)
436 _select += ", ".join(fields)
437 sql = _select + _from + _join + _where
440 if person_filter is not None:
441 if isinstance(person_filter, (list, tuple, set)):
442 # Separate the list into integers and strings
443 ints = filter(lambda x: isinstance(x, (int, long)), person_filter)
444 strs = filter(lambda x: isinstance(x, StringTypes), person_filter)
445 person_filter = Filter(Person.fields, {'person_id': ints, 'email': strs})
446 sql += " AND (%s) %s" % person_filter.sql(api, "OR")
447 elif isinstance(person_filter, dict):
448 person_filter = Filter(Person.fields, person_filter)
449 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
450 elif isinstance (person_filter, StringTypes):
451 person_filter = Filter(Person.fields, {'email':[person_filter]})
452 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
453 elif isinstance (person_filter, int):
454 person_filter = Filter(Person.fields, {'person_id':[person_filter]})
455 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
457 raise PLCInvalidArgument, "Wrong person filter %r"%person_filter
461 for row in self.api.db.selectall(sql):
462 person_id = row['person_id']
464 if all_persons.has_key(person_id):
465 for (key, key_list) in foreign_keys.items():
467 row[key_list] = [data]
468 if data and data not in all_persons[person_id][key_list]:
469 all_persons[person_id][key_list].append(data)
471 for key in foreign_keys.keys():
474 row[foreign_keys[key]] = [value]
476 row[foreign_keys[key]] = []
478 all_persons[person_id] = row
481 for row in all_persons.values():
482 obj = self.classobj(self.api, row)