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
10 from types import StringTypes
11 from datetime import datetime
14 from random import Random
17 from PLC.Faults import *
18 from PLC.Parameter import Parameter
19 from PLC.Debug import profile
20 from PLC.Table import Row, Table
21 from PLC.Roles import Roles
22 from PLC.Addresses import Address, Addresses
23 from PLC.Keys import Key, Keys
24 from PLC import md5crypt
28 Representation of a row in the persons table. To use, optionally
29 instantiate with a dict of values. Update as you would a
30 dict. Commit to the database with flush().
34 'person_id': Parameter(int, "Account identifier"),
35 'first_name': Parameter(str, "Given name"),
36 'last_name': Parameter(str, "Surname"),
37 'title': Parameter(str, "Title"),
38 'email': Parameter(str, "Primary e-mail address"),
39 'phone': Parameter(str, "Telephone number"),
40 'url': Parameter(str, "Home page"),
41 'bio': Parameter(str, "Biography"),
42 'accepted_aup': Parameter(bool, "Has accepted the AUP"),
43 'enabled': Parameter(bool, "Has been enabled"),
44 'deleted': Parameter(bool, "Has been deleted"),
45 'password': Parameter(str, "Account password in crypt() form"),
46 'last_updated': Parameter(str, "Date and time of last update"),
47 'date_created': Parameter(str, "Date and time when account was created"),
50 # These fields are derived from join tables and are not actually
51 # in the persons table.
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"),
58 # These fields are derived from join tables and are not returned
59 # by default unless specified.
61 'address_ids': Parameter([int], "List of address identifiers"),
62 'key_ids': Parameter([int], "List of key identifiers"),
63 'slice_ids': Parameter([int], "List of slice identifiers"),
66 default_fields = dict(fields.items() + join_fields.items())
67 all_fields = dict(default_fields.items() + extra_fields.items())
69 def __init__(self, api, fields):
70 Row.__init__(self, fields)
73 def validate_email(self, email):
75 Validate email address. Stolen from Mailman.
78 invalid_email = PLCInvalidArgument("Invalid e-mail address")
79 email_badchars = r'[][()<>|;^,\200-\377]'
81 # Pretty minimal, cheesy check. We could do better...
82 if not email or email.count(' ') > 0:
84 if re.search(email_badchars, email) or email[0] == '-':
88 at_sign = email.find('@')
91 user = email[:at_sign]
92 rest = email[at_sign+1:]
93 domain = rest.split('.')
95 # This means local, unqualified addresses, are no allowed
101 conflicts = Persons(self.api, [email])
102 for person_id, person in conflicts.iteritems():
103 if not person['deleted'] and ('person_id' not in self or self['person_id'] != person_id):
104 raise PLCInvalidArgument, "E-mail address already in use"
108 def validate_password(self, password):
110 Encrypt password if necessary before committing to the
114 if len(password) > len(md5crypt.MAGIC) and \
115 password[0:len(md5crypt.MAGIC)] == md5crypt.MAGIC:
118 # Generate a somewhat unique 2 character salt string
119 salt = str(time.time()) + str(Random().random())
120 salt = md5.md5(salt).hexdigest()[:8]
121 return md5crypt.md5crypt(password, salt)
123 def validate_role_ids(self, role_ids):
125 Ensure that the specified role_ids are all valid.
128 roles = Roles(self.api)
129 for role_id in role_ids:
130 if role_id not in roles:
131 raise PLCInvalidArgument, "No such role"
135 def validate_site_ids(self, site_ids):
137 Ensure that the specified site_ids are all valid.
140 sites = Sites(self.api, site_ids)
141 for site_id in site_ids:
142 if site_id not in sites:
143 raise PLCInvalidArgument, "No such site"
147 def can_update(self, person):
149 Returns true if we can update the specified person. We can
152 1. We are the person.
154 3. We are a PI and the person is a user or tech or at
158 if self['person_id'] == person['person_id']:
161 if 'admin' in self['roles']:
164 if 'pi' in self['roles']:
165 if set(self['site_ids']).intersection(person['site_ids']):
166 # Can update people with higher role IDs
167 return min(self['role_ids']) < min(person['role_ids'])
171 def can_view(self, person):
173 Returns true if we can view the specified person. We can
176 1. We are the person.
178 3. We are a PI and the person is at one of our sites.
181 if self.can_update(person):
184 if 'pi' in self['roles']:
185 if set(self['site_ids']).intersection(person['site_ids']):
186 # Can view people with equal or higher role IDs
187 return min(self['role_ids']) <= min(person['role_ids'])
191 def flush(self, commit = True):
193 Commit changes back to the database.
198 # Fetch a new person_id if necessary
199 if 'person_id' not in self:
200 rows = self.api.db.selectall("SELECT NEXTVAL('persons_person_id_seq') AS person_id")
202 raise PLCDBError, "Unable to fetch new person_id"
203 self['person_id'] = rows[0]['person_id']
208 # Filter out fields that cannot be set or updated directly
209 fields = dict(filter(lambda (key, value): key in self.fields,
212 # Parameterize for safety
214 values = [self.api.db.param(key, value) for (key, value) in fields.items()]
217 # Insert new row in persons table
218 sql = "INSERT INTO persons (%s) VALUES (%s)" % \
219 (", ".join(keys), ", ".join(values))
221 # Update existing row in persons table
222 columns = ["%s = %s" % (key, value) for (key, value) in zip(keys, values)]
223 sql = "UPDATE persons SET " + \
224 ", ".join(columns) + \
225 " WHERE person_id = %(person_id)d"
227 self.api.db.do(sql, fields)
232 def delete(self, commit = True):
234 Delete existing account.
237 assert 'person_id' in self
239 # Make sure extra fields are present
240 persons = Persons(self.api, [self['person_id']],
241 ['address_ids', 'key_ids'])
243 self.update(persons.values()[0])
245 # Delete all addresses
246 addresses = Addresses(self.api, self['address_ids'])
247 for address in addresses.values():
248 address.delete(commit = False)
251 keys = Keys(self.api, self['key_ids'])
252 for key in keys.values():
253 key.delete(commit = False)
255 # Clean up miscellaneous join tables
256 for table in ['person_roles', 'person_capabilities', 'person_site',
257 'node_root_access', 'dslice03_sliceuser']:
258 self.api.db.do("DELETE FROM %s" \
259 " WHERE person_id = %d" % \
260 (table, self['person_id']))
263 self['deleted'] = True
266 class Persons(Table):
268 Representation of row(s) from the persons table in the
269 database. Specify deleted and/or enabled to force a match on
270 whether a person is deleted and/or enabled. Default is to match on
271 non-deleted accounts.
274 def __init__(self, api, person_id_or_email_list = None, extra_fields = [], deleted = False, enabled = None):
277 role_max = Roles.role_max
279 # N.B.: Site IDs returned may be deleted. Persons returned are
280 # never deleted, but may not be enabled.
281 sql = "SELECT persons.*" \
282 ", roles.role_id, roles.name AS role" \
283 ", person_site.site_id" \
285 # N.B.: Joined IDs may be marked as deleted in their primary tables
287 # extra_field: (extra_table, extra_column, join_using)
288 'address_ids': ('person_address', 'address_id', 'person_id'),
289 'key_ids': ('person_keys', 'key_id', 'person_id'),
290 'slice_ids': ('dslice03_sliceuser', 'slice_id', 'person_id'),
293 extra_fields = filter(join_tables.has_key, extra_fields)
294 extra_tables = ["%s USING (%s)" % \
295 (join_tables[field][0], join_tables[field][2]) \
296 for field in extra_fields]
297 extra_columns = ["%s.%s" % \
298 (join_tables[field][0], join_tables[field][1]) \
299 for field in extra_fields]
302 sql += ", " + ", ".join(extra_columns)
304 sql += " FROM persons" \
305 " LEFT JOIN person_roles USING (person_id)" \
306 " LEFT JOIN roles USING (role_id)" \
307 " LEFT JOIN person_site USING (person_id)"
310 sql += " LEFT JOIN " + " LEFT JOIN ".join(extra_tables)
312 # So that people with no roles have empty role_ids and roles values
313 sql += " WHERE (role_id IS NULL or role_id <= %(role_max)d)"
315 if deleted is not None:
316 sql += " AND deleted IS %(deleted)s"
318 if enabled is not None:
319 sql += " AND enabled IS %(enabled)s"
321 if person_id_or_email_list:
322 # Separate the list into integers and strings
323 person_ids = filter(lambda person_id: isinstance(person_id, (int, long)),
324 person_id_or_email_list)
325 emails = filter(lambda email: isinstance(email, StringTypes),
326 person_id_or_email_list)
329 sql += " OR person_id IN (%s)" % ", ".join(map(str, person_ids))
331 # Case insensitive e-mail address comparison
332 sql += " OR lower(email) IN (%s)" % ", ".join(api.db.quote(emails)).lower()
335 # The first site_id in the site_ids list is the primary site
336 # of the user. See AdmGetPersonSites().
337 sql += " ORDER BY person_site.is_primary DESC"
339 rows = self.api.db.selectall(sql, locals())
341 if self.has_key(row['person_id']):
342 person = self[row['person_id']]
345 self[row['person_id']] = Person(api, row)