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.3 2006/09/08 19:45:46 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 sync().
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 'enabled': Parameter(bool, "Has been enabled"),
44 'deleted': Parameter(bool, "Has been deleted"),
45 'password': Parameter(str, "Account password in crypt() form", max = 254),
46 'last_updated': Parameter(str, "Date and time of last update"),
47 'date_created': Parameter(str, "Date and time when account was created"),
48 'role_ids': Parameter([int], "List of role identifiers"),
49 'roles': Parameter([str], "List of roles"),
50 'site_ids': Parameter([int], "List of site identifiers"),
51 'address_ids': Parameter([int], "List of address identifiers"),
52 'key_ids': Parameter([int], "List of key identifiers"),
53 # 'slice_ids': Parameter([int], "List of slice identifiers"),
56 def __init__(self, api, fields):
57 Row.__init__(self, fields)
60 def validate_email(self, email):
62 Validate email address. Stolen from Mailman.
65 invalid_email = PLCInvalidArgument("Invalid e-mail address")
66 email_badchars = r'[][()<>|;^,\200-\377]'
68 # Pretty minimal, cheesy check. We could do better...
69 if not email or email.count(' ') > 0:
71 if re.search(email_badchars, email) or email[0] == '-':
75 at_sign = email.find('@')
78 user = email[:at_sign]
79 rest = email[at_sign+1:]
80 domain = rest.split('.')
82 # This means local, unqualified addresses, are no allowed
88 conflicts = Persons(self.api, [email])
89 for person_id, person in conflicts.iteritems():
90 if not person['deleted'] and ('person_id' not in self or self['person_id'] != person_id):
91 raise PLCInvalidArgument, "E-mail address already in use"
95 def validate_password(self, password):
97 Encrypt password if necessary before committing to the
103 if len(password) > len(magic) and \
104 password[0:len(magic)] == magic:
107 # Generate a somewhat unique 8 character salt string
108 salt = str(time.time()) + str(Random().random())
109 salt = md5.md5(salt).hexdigest()[:8]
110 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
112 def can_update(self, person):
114 Returns true if we can update the specified person. We can
117 1. We are the person.
119 3. We are a PI and the person is a user or tech or at
123 assert isinstance(person, Person)
125 if self['person_id'] == person['person_id']:
128 if 'admin' in self['roles']:
131 if 'pi' in self['roles']:
132 if set(self['site_ids']).intersection(person['site_ids']):
133 # Can update people with higher role IDs
134 return min(self['role_ids']) < min(person['role_ids'])
138 def can_view(self, person):
140 Returns true if we can view the specified person. We can
143 1. We are the person.
145 3. We are a PI and the person is at one of our sites.
148 assert isinstance(person, Person)
150 if self.can_update(person):
153 if 'pi' in self['roles']:
154 if set(self['site_ids']).intersection(person['site_ids']):
155 # Can view people with equal or higher role IDs
156 return min(self['role_ids']) <= min(person['role_ids'])
160 def add_role(self, role_id, commit = True):
162 Add role to existing account.
165 assert 'person_id' in self
167 person_id = self['person_id']
168 self.api.db.do("INSERT INTO person_role (person_id, role_id)" \
169 " VALUES(%(person_id)d, %(role_id)d)",
175 assert 'role_ids' in self
176 if role_id not in self['role_ids']:
177 self['role_ids'].append(role_id)
179 def remove_role(self, role_id, commit = True):
181 Remove role from existing account.
184 assert 'person_id' in self
186 person_id = self['person_id']
187 self.api.db.do("DELETE FROM person_role" \
188 " WHERE person_id = %(person_id)d" \
189 " AND role_id = %(role_id)d",
195 assert 'role_ids' in self
196 if role_id in self['role_ids']:
197 self['role_ids'].remove(role_id)
199 def set_primary_site(self, site, commit = True):
201 Set the primary site for an existing account.
204 assert 'person_id' in self
205 assert isinstance(site, PLC.Sites.Site)
206 assert 'site_id' in site
208 person_id = self['person_id']
209 site_id = site['site_id']
210 self.api.db.do("UPDATE person_site SET is_primary = False" \
211 " WHERE person_id = %(person_id)d",
213 self.api.db.do("UPDATE person_site SET is_primary = True" \
214 " WHERE person_id = %(person_id)d" \
215 " AND site_id = %(site_id)d",
221 assert 'site_ids' in self
222 assert site_id in self['site_ids']
224 # Make sure that the primary site is first in the list
225 self['site_ids'].remove(site_id)
226 self['site_ids'].insert(0, site_id)
228 def sync(self, commit = True):
230 Commit changes back to the database.
235 # Fetch a new person_id if necessary
236 if 'person_id' not in self:
237 rows = self.api.db.selectall("SELECT NEXTVAL('persons_person_id_seq') AS person_id")
239 raise PLCDBError, "Unable to fetch new person_id"
240 self['person_id'] = rows[0]['person_id']
245 # Filter out fields that cannot be set or updated directly
246 persons_fields = self.api.db.fields('persons')
247 fields = dict(filter(lambda (key, value): key in persons_fields,
250 # Parameterize for safety
252 values = [self.api.db.param(key, value) for (key, value) in fields.items()]
255 # Insert new row in persons table
256 sql = "INSERT INTO persons (%s) VALUES (%s)" % \
257 (", ".join(keys), ", ".join(values))
259 # Update existing row in persons table
260 columns = ["%s = %s" % (key, value) for (key, value) in zip(keys, values)]
261 sql = "UPDATE persons SET " + \
262 ", ".join(columns) + \
263 " WHERE person_id = %(person_id)d"
265 self.api.db.do(sql, fields)
270 def delete(self, commit = True):
272 Delete existing account.
275 # Delete all addresses
276 addresses = Addresses(self.api, self['address_ids'])
277 for address in addresses.values():
278 address.delete(commit = False)
281 keys = Keys(self.api, self['key_ids'])
282 for key in keys.values():
283 key.delete(commit = False)
285 # Clean up miscellaneous join tables
286 for table in ['person_role', 'person_site']:
287 self.api.db.do("DELETE FROM %s" \
288 " WHERE person_id = %d" % \
289 (table, self['person_id']))
292 self['deleted'] = True
295 class Persons(Table):
297 Representation of row(s) from the persons table in the
298 database. Specify deleted and/or enabled to force a match on
299 whether a person is deleted and/or enabled. Default is to match on
300 non-deleted accounts.
303 def __init__(self, api, person_id_or_email_list = None, fields = Person.fields, deleted = False, enabled = None):
306 sql = "SELECT %s FROM view_persons WHERE TRUE" % \
309 if deleted is not None:
310 sql += " AND deleted IS %(deleted)s"
312 if enabled is not None:
313 sql += " AND enabled IS %(enabled)s"
315 if person_id_or_email_list:
316 # Separate the list into integers and strings
317 person_ids = filter(lambda person_id: isinstance(person_id, (int, long)),
318 person_id_or_email_list)
319 emails = filter(lambda email: isinstance(email, StringTypes),
320 person_id_or_email_list)
323 sql += " OR person_id IN (%s)" % ", ".join(map(str, person_ids))
325 # Case insensitive e-mail address comparison
326 sql += " OR lower(email) IN (%s)" % ", ".join(api.db.quote(emails)).lower()
329 rows = self.api.db.selectall(sql, locals())
332 self[row['person_id']] = person = Person(api, row)
333 for aggregate in 'role_ids', 'roles', 'site_ids', 'address_ids', 'key_ids':
334 if not person.has_key(aggregate) or person[aggregate] is None:
335 person[aggregate] = []
337 elements = person[aggregate].split(',')
339 person[aggregate] = map(int, elements)
341 person[aggregate] = elements