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.6 2006/10/02 16:04:22 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 'password': Parameter(str, "Account password in crypt() form", max = 254),
45 'last_updated': Parameter(str, "Date and time of last update", ro = True),
46 'date_created': Parameter(str, "Date and time when account was created", ro = True),
47 'role_ids': Parameter([int], "List of role identifiers", ro = True),
48 'roles': Parameter([str], "List of roles", ro = True),
49 'site_ids': Parameter([int], "List of site identifiers", ro = True),
50 'key_ids': Parameter([int], "List of key identifiers", ro = True),
51 'slice_ids': Parameter([int], "List of slice identifiers", ro = True),
54 def __init__(self, api, fields):
55 Row.__init__(self, fields)
58 def validate_email(self, email):
60 Validate email address. Stolen from Mailman.
63 invalid_email = PLCInvalidArgument("Invalid e-mail address")
64 email_badchars = r'[][()<>|;^,\200-\377]'
66 # Pretty minimal, cheesy check. We could do better...
67 if not email or email.count(' ') > 0:
69 if re.search(email_badchars, email) or email[0] == '-':
73 at_sign = email.find('@')
76 user = email[:at_sign]
77 rest = email[at_sign+1:]
78 domain = rest.split('.')
80 # This means local, unqualified addresses, are no allowed
86 conflicts = Persons(self.api, [email])
87 for person_id, person in conflicts.iteritems():
88 if 'person_id' not in self or self['person_id'] != person_id:
89 raise PLCInvalidArgument, "E-mail address already in use"
93 def validate_password(self, password):
95 Encrypt password if necessary before committing to the
101 if len(password) > len(magic) and \
102 password[0:len(magic)] == magic:
105 # Generate a somewhat unique 8 character salt string
106 salt = str(time.time()) + str(Random().random())
107 salt = md5.md5(salt).hexdigest()[:8]
108 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
110 def can_update(self, person):
112 Returns true if we can update the specified person. We can
115 1. We are the person.
117 3. We are a PI and the person is a user or tech or at
121 assert isinstance(person, Person)
123 if self['person_id'] == person['person_id']:
126 if 'admin' in self['roles']:
129 if 'pi' in self['roles']:
130 if set(self['site_ids']).intersection(person['site_ids']):
131 # Can update people with higher role IDs
132 return min(self['role_ids']) < min(person['role_ids'])
136 def can_view(self, person):
138 Returns true if we can view the specified person. We can
141 1. We are the person.
143 3. We are a PI and the person is at one of our sites.
146 assert isinstance(person, Person)
148 if self.can_update(person):
151 if 'pi' in self['roles']:
152 if set(self['site_ids']).intersection(person['site_ids']):
153 # Can view people with equal or higher role IDs
154 return min(self['role_ids']) <= min(person['role_ids'])
158 def add_role(self, role_id, commit = True):
160 Add role to existing account.
163 assert 'person_id' in self
165 person_id = self['person_id']
166 self.api.db.do("INSERT INTO person_role (person_id, role_id)" \
167 " VALUES(%(person_id)d, %(role_id)d)",
173 assert 'role_ids' in self
174 if role_id not in self['role_ids']:
175 self['role_ids'].append(role_id)
177 def remove_role(self, role_id, commit = True):
179 Remove role from existing account.
182 assert 'person_id' in self
184 person_id = self['person_id']
185 self.api.db.do("DELETE FROM person_role" \
186 " WHERE person_id = %(person_id)d" \
187 " AND role_id = %(role_id)d",
193 assert 'role_ids' in self
194 if role_id in self['role_ids']:
195 self['role_ids'].remove(role_id)
197 def set_primary_site(self, site, commit = True):
199 Set the primary site for an existing account.
202 assert 'person_id' in self
203 assert isinstance(site, PLC.Sites.Site)
204 assert 'site_id' in site
206 person_id = self['person_id']
207 site_id = site['site_id']
208 self.api.db.do("UPDATE person_site SET is_primary = False" \
209 " WHERE person_id = %(person_id)d",
211 self.api.db.do("UPDATE person_site SET is_primary = True" \
212 " WHERE person_id = %(person_id)d" \
213 " AND site_id = %(site_id)d",
219 assert 'site_ids' in self
220 assert site_id in self['site_ids']
222 # Make sure that the primary site is first in the list
223 self['site_ids'].remove(site_id)
224 self['site_ids'].insert(0, site_id)
226 def sync(self, commit = True):
228 Commit changes back to the database.
233 # Fetch a new person_id if necessary
234 if 'person_id' not in self:
235 rows = self.api.db.selectall("SELECT NEXTVAL('persons_person_id_seq') AS person_id")
237 raise PLCDBError, "Unable to fetch new person_id"
238 self['person_id'] = rows[0]['person_id']
243 # Filter out fields that cannot be set or updated directly
244 persons_fields = self.api.db.fields('persons')
245 fields = dict(filter(lambda (key, value): \
246 key in persons_fields and \
247 (key not in self.fields or not self.fields[key].ro),
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.
276 keys = Keys(self.api, self['key_ids'])
277 for key in keys.values():
278 key.delete(commit = False)
280 # Clean up miscellaneous join tables
281 for table in ['person_role', 'person_site', 'slice_person']:
282 self.api.db.do("DELETE FROM %s" \
283 " WHERE person_id = %d" % \
284 (table, self['person_id']))
287 self['deleted'] = True
290 class Persons(Table):
292 Representation of row(s) from the persons table in the
293 database. Specify deleted and/or enabled to force a match on
294 whether a person is deleted and/or enabled. Default is to match on
295 non-deleted accounts.
298 def __init__(self, api, person_id_or_email_list = None, fields = Person.fields, enabled = None):
301 sql = "SELECT %s FROM view_persons WHERE deleted IS False" % \
304 if enabled is not None:
305 sql += " AND enabled IS %(enabled)s"
307 if person_id_or_email_list:
308 # Separate the list into integers and strings
309 person_ids = filter(lambda person_id: isinstance(person_id, (int, long)),
310 person_id_or_email_list)
311 emails = filter(lambda email: isinstance(email, StringTypes),
312 person_id_or_email_list)
315 sql += " OR person_id IN (%s)" % ", ".join(map(str, person_ids))
317 # Case insensitive e-mail address comparison
318 sql += " OR email IN (%s)" % ", ".join(api.db.quote(emails)).lower()
321 rows = self.api.db.selectall(sql, locals())
324 self[row['person_id']] = person = Person(api, row)
325 for aggregate in 'role_ids', 'roles', 'site_ids', 'key_ids', 'slice_ids':
326 if not person.has_key(aggregate) or person[aggregate] is None:
327 person[aggregate] = []
329 elements = person[aggregate].split(',')
331 person[aggregate] = map(int, elements)
333 person[aggregate] = elements