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.2 2006/09/07 23:45:31 mlhuang Exp $
10 from types import StringTypes
11 from datetime import datetime
14 from random import Random
18 from PLC.Faults import *
19 from PLC.Parameter import Parameter
20 from PLC.Debug import profile
21 from PLC.Table import Row, Table
22 from PLC.Roles import Roles
23 from PLC.Addresses import Address, Addresses
24 from PLC.Keys import Key, Keys
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", max = 128),
37 'last_name': Parameter(str, "Surname", max = 128),
38 'title': Parameter(str, "Title", max = 128),
39 'email': Parameter(str, "Primary e-mail address", max = 254),
40 'phone': Parameter(str, "Telephone number", max = 64),
41 'url': Parameter(str, "Home page", max = 254),
42 'bio': Parameter(str, "Biography", max = 254),
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", max = 254),
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
117 if len(password) > len(magic) and \
118 password[0:len(magic)] == magic:
121 # Generate a somewhat unique 8 character salt string
122 salt = str(time.time()) + str(Random().random())
123 salt = md5.md5(salt).hexdigest()[:8]
124 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
126 def validate_role_ids(self, role_ids):
128 Ensure that the specified role_ids are all valid.
131 roles = Roles(self.api)
132 for role_id in role_ids:
133 if role_id not in roles:
134 raise PLCInvalidArgument, "No such role"
138 def validate_site_ids(self, site_ids):
140 Ensure that the specified site_ids are all valid.
143 sites = PLC.Sites.Sites(self.api, site_ids)
144 for site_id in site_ids:
145 if site_id not in sites:
146 raise PLCInvalidArgument, "No such site"
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 def add_role(self, role_id, commit = True):
200 Add role to existing account.
203 assert 'person_id' in self
205 person_id = self['person_id']
206 self.api.db.do("INSERT INTO person_roles (person_id, role_id)" \
207 " VALUES(%(person_id)d, %(role_id)d)",
213 assert 'role_ids' in self
214 if role_id not in self['role_ids']:
215 self['role_ids'].append(role_id)
217 def remove_role(self, role_id, commit = True):
219 Remove role from existing account.
222 assert 'person_id' in self
224 person_id = self['person_id']
225 self.api.db.do("DELETE FROM person_roles" \
226 " WHERE person_id = %(person_id)d" \
227 " AND role_id = %(role_id)d",
233 assert 'role_ids' in self
234 if role_id in self['role_ids']:
235 self['role_ids'].remove(role_id)
237 def set_primary_site(self, site, commit = True):
239 Set the primary site for an existing account.
242 assert 'person_id' in self
243 assert isinstance(site, PLC.Sites.Site)
244 assert 'site_id' in site
246 person_id = self['person_id']
247 site_id = site['site_id']
248 self.api.db.do("UPDATE person_site SET is_primary = False" \
249 " WHERE person_id = %(person_id)d",
251 self.api.db.do("UPDATE person_site SET is_primary = True" \
252 " WHERE person_id = %(person_id)d" \
253 " AND site_id = %(site_id)d",
259 assert 'site_ids' in self
260 assert site_id in self['site_ids']
262 # Make sure that the primary site is first in the list
263 self['site_ids'].remove(site_id)
264 self['site_ids'].insert(0, site_id)
266 def flush(self, commit = True):
268 Commit changes back to the database.
273 # Fetch a new person_id if necessary
274 if 'person_id' not in self:
275 rows = self.api.db.selectall("SELECT NEXTVAL('persons_person_id_seq') AS person_id")
277 raise PLCDBError, "Unable to fetch new person_id"
278 self['person_id'] = rows[0]['person_id']
283 # Filter out fields that cannot be set or updated directly
284 fields = dict(filter(lambda (key, value): key in self.fields,
287 # Parameterize for safety
289 values = [self.api.db.param(key, value) for (key, value) in fields.items()]
292 # Insert new row in persons table
293 sql = "INSERT INTO persons (%s) VALUES (%s)" % \
294 (", ".join(keys), ", ".join(values))
296 # Update existing row in persons table
297 columns = ["%s = %s" % (key, value) for (key, value) in zip(keys, values)]
298 sql = "UPDATE persons SET " + \
299 ", ".join(columns) + \
300 " WHERE person_id = %(person_id)d"
302 self.api.db.do(sql, fields)
307 def delete(self, commit = True):
309 Delete existing account.
312 assert 'person_id' in self
314 # Make sure extra fields are present
315 persons = Persons(self.api, [self['person_id']],
316 ['address_ids', 'key_ids'])
318 self.update(persons.values()[0])
320 # Delete all addresses
321 addresses = Addresses(self.api, self['address_ids'])
322 for address in addresses.values():
323 address.delete(commit = False)
326 keys = Keys(self.api, self['key_ids'])
327 for key in keys.values():
328 key.delete(commit = False)
330 # Clean up miscellaneous join tables
331 for table in ['person_roles', 'person_capabilities', 'person_site',
332 'node_root_access', 'dslice03_sliceuser']:
333 self.api.db.do("DELETE FROM %s" \
334 " WHERE person_id = %d" % \
335 (table, self['person_id']))
338 self['deleted'] = True
341 class Persons(Table):
343 Representation of row(s) from the persons table in the
344 database. Specify deleted and/or enabled to force a match on
345 whether a person is deleted and/or enabled. Default is to match on
346 non-deleted accounts.
349 def __init__(self, api, person_id_or_email_list = None, extra_fields = [], deleted = False, enabled = None):
352 role_max = Roles.role_max
354 # N.B.: Site IDs returned may be deleted. Persons returned are
355 # never deleted, but may not be enabled.
356 sql = "SELECT persons.*" \
357 ", roles.role_id, roles.name AS role" \
358 ", person_site.site_id" \
360 # N.B.: Joined IDs may be marked as deleted in their primary tables
362 # extra_field: (extra_table, extra_column, join_using)
363 'address_ids': ('person_address', 'address_id', 'person_id'),
364 'key_ids': ('person_keys', 'key_id', 'person_id'),
365 'slice_ids': ('dslice03_sliceuser', 'slice_id', 'person_id'),
368 extra_fields = filter(join_tables.has_key, extra_fields)
369 extra_tables = ["%s USING (%s)" % \
370 (join_tables[field][0], join_tables[field][2]) \
371 for field in extra_fields]
372 extra_columns = ["%s.%s" % \
373 (join_tables[field][0], join_tables[field][1]) \
374 for field in extra_fields]
377 sql += ", " + ", ".join(extra_columns)
379 sql += " FROM persons" \
380 " LEFT JOIN person_roles USING (person_id)" \
381 " LEFT JOIN roles USING (role_id)" \
382 " LEFT JOIN person_site USING (person_id)"
385 sql += " LEFT JOIN " + " LEFT JOIN ".join(extra_tables)
387 # So that people with no roles have empty role_ids and roles values
388 sql += " WHERE (role_id IS NULL or role_id <= %(role_max)d)"
390 if deleted is not None:
391 sql += " AND persons.deleted IS %(deleted)s"
393 if enabled is not None:
394 sql += " AND persons.enabled IS %(enabled)s"
396 if person_id_or_email_list:
397 # Separate the list into integers and strings
398 person_ids = filter(lambda person_id: isinstance(person_id, (int, long)),
399 person_id_or_email_list)
400 emails = filter(lambda email: isinstance(email, StringTypes),
401 person_id_or_email_list)
404 sql += " OR person_id IN (%s)" % ", ".join(map(str, person_ids))
406 # Case insensitive e-mail address comparison
407 sql += " OR lower(email) IN (%s)" % ", ".join(api.db.quote(emails)).lower()
410 # The first site_id in the site_ids list is the primary site
411 # of the user. See AdmGetPersonSites().
412 sql += " ORDER BY person_site.is_primary DESC"
414 rows = self.api.db.selectall(sql, locals())
416 if self.has_key(row['person_id']):
417 person = self[row['person_id']]
420 self[row['person_id']] = Person(api, row)