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.4 2006/09/25 15:10:00 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 'key_ids': Parameter([int], "List of key identifiers"),
52 'slice_ids': Parameter([int], "List of slice identifiers"),
55 def __init__(self, api, fields):
56 Row.__init__(self, fields)
59 def validate_email(self, email):
61 Validate email address. Stolen from Mailman.
64 invalid_email = PLCInvalidArgument("Invalid e-mail address")
65 email_badchars = r'[][()<>|;^,\200-\377]'
67 # Pretty minimal, cheesy check. We could do better...
68 if not email or email.count(' ') > 0:
70 if re.search(email_badchars, email) or email[0] == '-':
74 at_sign = email.find('@')
77 user = email[:at_sign]
78 rest = email[at_sign+1:]
79 domain = rest.split('.')
81 # This means local, unqualified addresses, are no allowed
87 conflicts = Persons(self.api, [email])
88 for person_id, person in conflicts.iteritems():
89 if not person['deleted'] and ('person_id' not in self or self['person_id'] != person_id):
90 raise PLCInvalidArgument, "E-mail address already in use"
94 def validate_password(self, password):
96 Encrypt password if necessary before committing to the
102 if len(password) > len(magic) and \
103 password[0:len(magic)] == magic:
106 # Generate a somewhat unique 8 character salt string
107 salt = str(time.time()) + str(Random().random())
108 salt = md5.md5(salt).hexdigest()[:8]
109 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
111 def can_update(self, person):
113 Returns true if we can update the specified person. We can
116 1. We are the person.
118 3. We are a PI and the person is a user or tech or at
122 assert isinstance(person, Person)
124 if self['person_id'] == person['person_id']:
127 if 'admin' in self['roles']:
130 if 'pi' in self['roles']:
131 if set(self['site_ids']).intersection(person['site_ids']):
132 # Can update people with higher role IDs
133 return min(self['role_ids']) < min(person['role_ids'])
137 def can_view(self, person):
139 Returns true if we can view the specified person. We can
142 1. We are the person.
144 3. We are a PI and the person is at one of our sites.
147 assert isinstance(person, Person)
149 if self.can_update(person):
152 if 'pi' in self['roles']:
153 if set(self['site_ids']).intersection(person['site_ids']):
154 # Can view people with equal or higher role IDs
155 return min(self['role_ids']) <= min(person['role_ids'])
159 def add_role(self, role_id, commit = True):
161 Add role to existing account.
164 assert 'person_id' in self
166 person_id = self['person_id']
167 self.api.db.do("INSERT INTO person_role (person_id, role_id)" \
168 " VALUES(%(person_id)d, %(role_id)d)",
174 assert 'role_ids' in self
175 if role_id not in self['role_ids']:
176 self['role_ids'].append(role_id)
178 def remove_role(self, role_id, commit = True):
180 Remove role from existing account.
183 assert 'person_id' in self
185 person_id = self['person_id']
186 self.api.db.do("DELETE FROM person_role" \
187 " WHERE person_id = %(person_id)d" \
188 " AND role_id = %(role_id)d",
194 assert 'role_ids' in self
195 if role_id in self['role_ids']:
196 self['role_ids'].remove(role_id)
198 def set_primary_site(self, site, commit = True):
200 Set the primary site for an existing account.
203 assert 'person_id' in self
204 assert isinstance(site, PLC.Sites.Site)
205 assert 'site_id' in site
207 person_id = self['person_id']
208 site_id = site['site_id']
209 self.api.db.do("UPDATE person_site SET is_primary = False" \
210 " WHERE person_id = %(person_id)d",
212 self.api.db.do("UPDATE person_site SET is_primary = True" \
213 " WHERE person_id = %(person_id)d" \
214 " AND site_id = %(site_id)d",
220 assert 'site_ids' in self
221 assert site_id in self['site_ids']
223 # Make sure that the primary site is first in the list
224 self['site_ids'].remove(site_id)
225 self['site_ids'].insert(0, site_id)
227 def sync(self, commit = True):
229 Commit changes back to the database.
234 # Fetch a new person_id if necessary
235 if 'person_id' not in self:
236 rows = self.api.db.selectall("SELECT NEXTVAL('persons_person_id_seq') AS person_id")
238 raise PLCDBError, "Unable to fetch new person_id"
239 self['person_id'] = rows[0]['person_id']
244 # Filter out fields that cannot be set or updated directly
245 persons_fields = self.api.db.fields('persons')
246 fields = dict(filter(lambda (key, value): key in persons_fields,
248 for ro_field in 'date_created', 'last_updated':
249 if ro_field in fields:
252 # Parameterize for safety
254 values = [self.api.db.param(key, value) for (key, value) in fields.items()]
257 # Insert new row in persons table
258 sql = "INSERT INTO persons (%s) VALUES (%s)" % \
259 (", ".join(keys), ", ".join(values))
261 # Update existing row in persons table
262 columns = ["%s = %s" % (key, value) for (key, value) in zip(keys, values)]
263 sql = "UPDATE persons SET " + \
264 ", ".join(columns) + \
265 " WHERE person_id = %(person_id)d"
267 self.api.db.do(sql, fields)
272 def delete(self, commit = True):
274 Delete existing account.
278 keys = Keys(self.api, self['key_ids'])
279 for key in keys.values():
280 key.delete(commit = False)
282 # Clean up miscellaneous join tables
283 for table in ['person_role', 'person_site', 'slice_person']:
284 self.api.db.do("DELETE FROM %s" \
285 " WHERE person_id = %d" % \
286 (table, self['person_id']))
289 self['deleted'] = True
292 class Persons(Table):
294 Representation of row(s) from the persons table in the
295 database. Specify deleted and/or enabled to force a match on
296 whether a person is deleted and/or enabled. Default is to match on
297 non-deleted accounts.
300 def __init__(self, api, person_id_or_email_list = None, fields = Person.fields, deleted = False, enabled = None):
303 sql = "SELECT %s FROM view_persons WHERE TRUE" % \
306 if deleted is not None:
307 sql += " AND deleted IS %(deleted)s"
309 if enabled is not None:
310 sql += " AND enabled IS %(enabled)s"
312 if person_id_or_email_list:
313 # Separate the list into integers and strings
314 person_ids = filter(lambda person_id: isinstance(person_id, (int, long)),
315 person_id_or_email_list)
316 emails = filter(lambda email: isinstance(email, StringTypes),
317 person_id_or_email_list)
320 sql += " OR person_id IN (%s)" % ", ".join(map(str, person_ids))
322 # Case insensitive e-mail address comparison
323 sql += " OR email IN (%s)" % ", ".join(api.db.quote(emails)).lower()
326 rows = self.api.db.selectall(sql, locals())
329 self[row['person_id']] = person = Person(api, row)
330 for aggregate in 'role_ids', 'roles', 'site_ids', 'key_ids', 'slice_ids':
331 if not person.has_key(aggregate) or person[aggregate] is None:
332 person[aggregate] = []
334 elements = person[aggregate].split(',')
336 person[aggregate] = map(int, elements)
338 person[aggregate] = elements