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
11 from types import StringTypes
12 from datetime import datetime
15 from random import Random
19 from PLC.Faults import *
20 from PLC.Debug import log
21 from PLC.Parameter import Parameter, Mixed
22 from PLC.Filter import Filter
23 from PLC.Table import Row, Table
24 from PLC.Roles import Role, Roles
25 from PLC.Keys import Key, Keys
26 from PLC.Messages import Message, Messages
30 Representation of a row in the persons table. To use, optionally
31 instantiate with a dict of values. Update as you would a
32 dict. Commit to the database with sync().
35 table_name = 'persons'
36 primary_key = 'person_id'
37 join_tables = ['person_key', 'person_role', 'person_site', 'slice_person', 'person_session', 'peer_person']
39 'person_id': Parameter(int, "User identifier"),
40 'first_name': Parameter(str, "Given name", max = 128),
41 'last_name': Parameter(str, "Surname", max = 128),
42 'title': Parameter(str, "Title", max = 128, nullok = True),
43 'email': Parameter(str, "Primary e-mail address", max = 254),
44 'phone': Parameter(str, "Telephone number", max = 64, nullok = True),
45 'url': Parameter(str, "Home page", max = 254, nullok = True),
46 'bio': Parameter(str, "Biography", max = 254, nullok = True),
47 'enabled': Parameter(bool, "Has been enabled"),
48 'password': Parameter(str, "Account password in crypt() form", max = 254),
49 'verification_key': Parameter(str, "Reset password key", max = 254, nullok = True),
50 'verification_expires': Parameter(int, "Date and time when verification_key expires", nullok = True),
51 'last_updated': Parameter(int, "Date and time of last update", ro = True),
52 'date_created': Parameter(int, "Date and time when account was created", ro = True),
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"))]
72 def validate_email(self, email):
74 Validate email address. Stolen from Mailman.
77 invalid_email = PLCInvalidArgument("Invalid e-mail address")
78 email_badchars = r'[][()<>|;^,\200-\377]'
80 # Pretty minimal, cheesy check. We could do better...
81 if not email or email.count(' ') > 0:
83 if re.search(email_badchars, email) or email[0] == '-':
87 at_sign = email.find('@')
90 user = email[:at_sign]
91 rest = email[at_sign+1:]
92 domain = rest.split('.')
94 # This means local, unqualified addresses, are not allowed
100 # check only against users on the same peer
101 if 'peer_id' in self:
102 namespace_peer_id = self['peer_id']
104 namespace_peer_id = None
106 conflicts = Persons(self.api, {'email':email,'peer_id':namespace_peer_id})
108 for person in conflicts:
109 if 'person_id' not in self or self['person_id'] != person['person_id']:
110 raise PLCInvalidArgument, "E-mail address already in use"
114 def validate_password(self, password):
116 Encrypt password if necessary before committing to the
122 if len(password) > len(magic) and \
123 password[0:len(magic)] == magic:
126 # Generate a somewhat unique 8 character salt string
127 salt = str(time.time()) + str(Random().random())
128 salt = md5.md5(salt).hexdigest()[:8]
129 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
131 validate_date_created = Row.validate_timestamp
132 validate_last_updated = Row.validate_timestamp
133 validate_verification_expires = Row.validate_timestamp
135 def can_update(self, person):
137 Returns true if we can update the specified person. We can
140 1. We are the person.
142 3. We are a PI and the person is a user or tech or at
146 assert isinstance(person, Person)
148 if self['person_id'] == person['person_id']:
151 if 'admin' in self['roles']:
154 if 'pi' in self['roles']:
155 if set(self['site_ids']).intersection(person['site_ids']):
156 # Can update person is neither a PI or ADMIN
157 return (not (('pi' in person['roles']) or ('admin' in person['roles'])))
161 def can_view(self, person):
163 Returns true if we can view the specified person. We can
166 1. We are the person.
168 3. We are a PI and the person is at one of our sites.
171 assert isinstance(person, Person)
173 if self.can_update(person):
176 if 'pi' in self['roles']:
177 if set(self['site_ids']).intersection(person['site_ids']):
178 # Can view people with equal or higher role IDs
179 return 'admin' not in person['roles']
183 add_role = Row.add_object(Role, 'person_role')
184 remove_role = Row.remove_object(Role, 'person_role')
186 add_key = Row.add_object(Key, 'person_key')
187 remove_key = Row.remove_object(Key, 'person_key')
189 def set_primary_site(self, site, commit = True):
191 Set the primary site for an existing user.
194 assert 'person_id' in self
195 assert 'site_id' in site
197 person_id = self['person_id']
198 site_id = site['site_id']
199 self.api.db.do("UPDATE person_site SET is_primary = False" \
200 " WHERE person_id = %(person_id)d",
202 self.api.db.do("UPDATE person_site SET is_primary = True" \
203 " WHERE person_id = %(person_id)d" \
204 " AND site_id = %(site_id)d",
210 assert 'site_ids' in self
211 assert site_id in self['site_ids']
213 # Make sure that the primary site is first in the list
214 self['site_ids'].remove(site_id)
215 self['site_ids'].insert(0, site_id)
217 def update_last_updated(self, commit = True):
219 Update last_updated field with current time
222 assert 'person_id' in self
223 assert self.table_name
225 self.api.db.do("UPDATE %s SET last_updated = CURRENT_TIMESTAMP " % (self.table_name) + \
226 " where person_id = %d" % (self['person_id']) )
229 def associate_roles(self, auth, field, value):
231 Adds roles found in value list to this person (using AddRoleToPerson).
232 Deletes roles not found in value list from this person (using DeleteRoleFromPerson).
235 assert 'role_ids' in self
236 assert 'person_id' in self
237 assert isinstance(value, list)
239 (role_ids, role_names) = self.separate_types(value)[0:2]
241 # Translate roles into role_ids
243 roles = Roles(self.api, role_names).dict('role_id')
244 role_ids += roles.keys()
246 # Add new ids, remove stale ids
247 if self['role_ids'] != role_ids:
248 from PLC.Methods.AddRoleToPerson import AddRoleToPerson
249 from PLC.Methods.DeleteRoleFromPerson import DeleteRoleFromPerson
250 new_roles = set(role_ids).difference(self['role_ids'])
251 stale_roles = set(self['role_ids']).difference(role_ids)
253 for new_role in new_roles:
254 AddRoleToPerson.__call__(AddRoleToPerson(self.api), auth, new_role, self['person_id'])
255 for stale_role in stale_roles:
256 DeleteRoleFromPerson.__call__(DeleteRoleFromPerson(self.api), auth, stale_role, self['person_id'])
259 def associate_sites(self, auth, field, value):
261 Adds person to sites found in value list (using AddPersonToSite).
262 Deletes person from site not found in value list (using DeletePersonFromSite).
265 from PLC.Sites import Sites
267 assert 'site_ids' in self
268 assert 'person_id' in self
269 assert isinstance(value, list)
271 (site_ids, site_names) = self.separate_types(value)[0:2]
273 # Translate roles into role_ids
275 sites = Sites(self.api, site_names, ['site_id']).dict('site_id')
276 site_ids += sites.keys()
278 # Add new ids, remove stale ids
279 if self['site_ids'] != site_ids:
280 from PLC.Methods.AddPersonToSite import AddPersonToSite
281 from PLC.Methods.DeletePersonFromSite import DeletePersonFromSite
282 new_sites = set(site_ids).difference(self['site_ids'])
283 stale_sites = set(self['site_ids']).difference(site_ids)
285 for new_site in new_sites:
286 AddPersonToSite.__call__(AddPersonToSite(self.api), auth, self['person_id'], new_site)
287 for stale_site in stale_sites:
288 DeletePersonFromSite.__call__(DeletePersonFromSite(self.api), auth, self['person_id'], stale_site)
291 def associate_keys(self, auth, field, value):
293 Deletes key_ids not found in value list (using DeleteKey).
294 Adds key if key_fields w/o key_id is found (using AddPersonKey).
295 Updates key if key_fields w/ key_id is found (using UpdateKey).
297 assert 'key_ids' in self
298 assert 'person_id' in self
299 assert isinstance(value, list)
301 (key_ids, blank, keys) = self.separate_types(value)
303 if self['key_ids'] != key_ids:
304 from PLC.Methods.DeleteKey import DeleteKey
305 stale_keys = set(self['key_ids']).difference(key_ids)
307 for stale_key in stale_keys:
308 DeleteKey.__call__(DeleteKey(self.api), auth, stale_key)
311 from PLC.Methods.AddPersonKey import AddPersonKey
312 from PLC.Methods.UpdateKey import UpdateKey
313 updated_keys = filter(lambda key: 'key_id' in key, keys)
314 added_keys = filter(lambda key: 'key_id' not in key, keys)
316 for key in added_keys:
317 AddPersonKey.__call__(AddPersonKey(self.api), auth, self['person_id'], key)
318 for key in updated_keys:
319 key_id = key.pop('key_id')
320 UpdateKey.__call__(UpdateKey(self.api), auth, key_id, key)
323 def associate_slices(self, auth, field, value):
325 Adds person to slices found in value list (using AddPersonToSlice).
326 Deletes person from slices found in value list (using DeletePersonFromSlice).
329 from PLC.Slices import Slices
331 assert 'slice_ids' in self
332 assert 'person_id' in self
333 assert isinstance(value, list)
335 (slice_ids, slice_names) = self.separate_types(value)[0:2]
337 # Translate roles into role_ids
339 slices = Slices(self.api, slice_names, ['slice_id']).dict('slice_id')
340 slice_ids += slices.keys()
342 # Add new ids, remove stale ids
343 if self['slice_ids'] != slice_ids:
344 from PLC.Methods.AddPersonToSlice import AddPersonToSlice
345 from PLC.Methods.DeletePersonFromSlice import DeletePersonFromSlice
346 new_slices = set(slice_ids).difference(self['slice_ids'])
347 stale_slices = set(self['slice_ids']).difference(slice_ids)
349 for new_slice in new_slices:
350 AddPersonToSlice.__call__(AddPersonToSlice(self.api), auth, self['person_id'], new_slice)
351 for stale_slice in stale_slices:
352 DeletePersonFromSlice.__call__(DeletePersonFromSlice(self.api), auth, self['person_id'], stale_slice)
355 def delete(self, commit = True):
357 Delete existing user.
361 keys = Keys(self.api, self['key_ids'])
363 key.delete(commit = False)
365 # Clean up miscellaneous join tables
366 for table in self.join_tables:
367 self.api.db.do("DELETE FROM %s WHERE person_id = %d" % \
368 (table, self['person_id']))
371 self['deleted'] = True
374 class Persons(Table):
376 Representation of row(s) from the persons table in the
380 def __init__(self, api, person_filter = None, columns = None):
381 Table.__init__(self, api, Person, columns)
383 foreign_fields = {'role_ids': ('role_id', 'person_role'),
384 'roles': ('name', 'roles'),
385 'site_ids': ('site_id', 'person_site'),
386 'key_ids': ('key_id', 'person_key'),
387 'slice_ids': ('slice_id', 'slice_person')
390 db_fields = filter(lambda field: field not in foreign_fields.keys(), Person.fields.keys())
391 all_fields = db_fields + [value[0] for value in foreign_fields.values()]
394 _from = " FROM persons "
395 _join = " LEFT JOIN peer_person USING (person_id) "
396 _where = " WHERE deleted IS False "
399 # include all columns
401 tables = [value[1] for value in foreign_fields.values()]
403 for key in foreign_fields.keys():
404 foreign_keys[foreign_fields[key][0]] = key
406 if table in ['roles']:
407 _join += " LEFT JOIN roles USING(role_id) "
409 _join += " LEFT JOIN %s USING (person_id) " % (table)
412 columns = filter(lambda column: column in db_fields+foreign_fields.keys(), columns)
414 for column in columns:
415 if column in foreign_fields.keys():
416 (field, table) = foreign_fields[column]
417 foreign_keys[field] = column
420 if column in ['roles']:
421 _join += " LEFT JOIN roles USING(role_id) "
423 _join += " LEFT JOIN %s USING (person_id)" % \
424 (foreign_fields[column][1])
429 # postgres will return timestamps as datetime objects.
430 # XMLPRC cannot marshal datetime so convert to int
431 timestamps = ['date_created', 'last_updated', 'verification_expires']
433 if field in timestamps:
434 fields[fields.index(field)] = \
435 "CAST(date_part('epoch', %s) AS bigint) AS %s" % (field, field)
437 _select += ", ".join(fields)
438 sql = _select + _from + _join + _where
441 if person_filter is not None:
442 if isinstance(person_filter, (list, tuple, set)):
443 # Separate the list into integers and strings
444 ints = filter(lambda x: isinstance(x, (int, long)), person_filter)
445 strs = filter(lambda x: isinstance(x, StringTypes), person_filter)
446 person_filter = Filter(Person.fields, {'person_id': ints, 'email': strs})
447 sql += " AND (%s) %s" % person_filter.sql(api, "OR")
448 elif isinstance(person_filter, dict):
449 person_filter = Filter(Person.fields, person_filter)
450 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
451 elif isinstance (person_filter, StringTypes):
452 person_filter = Filter(Person.fields, {'email':[person_filter]})
453 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
454 elif isinstance (person_filter, int):
455 person_filter = Filter(Person.fields, {'person_id':[person_filter]})
456 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
458 raise PLCInvalidArgument, "Wrong person filter %r"%person_filter
462 for row in self.api.db.selectall(sql):
463 person_id = row['person_id']
465 if all_persons.has_key(person_id):
466 for (key, key_list) in foreign_keys.items():
468 row[key_list] = [data]
469 if data and data not in all_persons[person_id][key_list]:
470 all_persons[person_id][key_list].append(data)
472 for key in foreign_keys.keys():
475 row[foreign_keys[key]] = [value]
477 row[foreign_keys[key]] = []
479 all_persons[person_id] = row
482 for row in all_persons.values():
483 obj = self.classobj(self.api, row)