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.36 2007/03/29 20:14:46 tmack Exp $
10 from types import StringTypes
11 from datetime import datetime
14 from random import Random
18 from PLC.Faults import *
19 from PLC.Debug import log
20 from PLC.Parameter import Parameter
21 from PLC.Filter import Filter
22 from PLC.Table import Row, Table
23 from PLC.Roles import Role, Roles
24 from PLC.Keys import Key, Keys
25 from PLC.Messages import Message, Messages
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().
34 table_name = 'persons'
35 primary_key = 'person_id'
36 join_tables = ['person_key', 'person_role', 'person_site', 'slice_person', 'person_session', 'peer_person']
38 'person_id': Parameter(int, "User identifier"),
39 'first_name': Parameter(str, "Given name", max = 128),
40 'last_name': Parameter(str, "Surname", max = 128),
41 'title': Parameter(str, "Title", max = 128, nullok = True),
42 'email': Parameter(str, "Primary e-mail address", max = 254),
43 'phone': Parameter(str, "Telephone number", max = 64, nullok = True),
44 'url': Parameter(str, "Home page", max = 254, nullok = True),
45 'bio': Parameter(str, "Biography", max = 254, nullok = True),
46 'enabled': Parameter(bool, "Has been enabled"),
47 'password': Parameter(str, "Account password in crypt() form", max = 254),
48 'verification_key': Parameter(str, "Reset password key", max = 254, nullok = True),
49 'verification_expires': Parameter(int, "Date and time when verification_key expires", nullok = True),
50 'last_updated': Parameter(int, "Date and time of last update", ro = True),
51 'date_created': Parameter(int, "Date and time when account was created", ro = True),
52 'role_ids': Parameter([int], "List of role identifiers"),
53 'roles': Parameter([str], "List of roles"),
54 'site_ids': Parameter([int], "List of site identifiers"),
55 'key_ids': Parameter([int], "List of key identifiers"),
56 'slice_ids': Parameter([int], "List of slice identifiers"),
57 'peer_id': Parameter(int, "Peer to which this user belongs", nullok = True),
58 'peer_person_id': Parameter(int, "Foreign user identifier at peer", nullok = True),
63 foreign_fields = ['first_name', 'last_name', 'title', 'email', 'phone', 'url',
64 'bio', 'enabled', 'password', ]
65 # forget about these ones, they are read-only anyway
66 # handling them causes Cache to re-sync all over again
67 # 'last_updated', 'date_created'
69 {'field' : 'key_ids', 'class': 'Key', 'table' : 'person_key' } ,
70 {'field' : 'site_ids', 'class': 'Site', 'table' : 'person_site'},
71 # xxx this is not handled by Cache yet
72 # 'role_ids': Parameter([int], "List of role identifiers"),
75 def validate_email(self, email):
77 Validate email address. Stolen from Mailman.
80 invalid_email = PLCInvalidArgument("Invalid e-mail address")
81 email_badchars = r'[][()<>|;^,\200-\377]'
83 # Pretty minimal, cheesy check. We could do better...
84 if not email or email.count(' ') > 0:
86 if re.search(email_badchars, email) or email[0] == '-':
90 at_sign = email.find('@')
93 user = email[:at_sign]
94 rest = email[at_sign+1:]
95 domain = rest.split('.')
97 # This means local, unqualified addresses, are not allowed
103 # check only against users on the same peer
104 if 'peer_id' in self:
105 namespace_peer_id = self['peer_id']
107 namespace_peer_id = None
109 conflicts = Persons(self.api, {'email':email,'peer_id':namespace_peer_id})
111 for person in conflicts:
112 if 'person_id' not in self or self['person_id'] != person['person_id']:
113 raise PLCInvalidArgument, "E-mail address already in use"
117 def validate_password(self, password):
119 Encrypt password if necessary before committing to the
125 if len(password) > len(magic) and \
126 password[0:len(magic)] == magic:
129 # Generate a somewhat unique 8 character salt string
130 salt = str(time.time()) + str(Random().random())
131 salt = md5.md5(salt).hexdigest()[:8]
132 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
134 validate_date_created = Row.validate_timestamp
135 validate_last_updated = Row.validate_timestamp
136 validate_verification_expires = Row.validate_timestamp
138 def can_update(self, person):
140 Returns true if we can update the specified person. We can
143 1. We are the person.
145 3. We are a PI and the person is a user or tech or at
149 assert isinstance(person, Person)
151 if self['person_id'] == person['person_id']:
154 if 'admin' in self['roles']:
157 if 'pi' in self['roles']:
158 if set(self['site_ids']).intersection(person['site_ids']):
159 # Can update people with higher role IDs
160 return min(self['role_ids']) < min(person['role_ids'])
164 def can_view(self, person):
166 Returns true if we can view the specified person. We can
169 1. We are the person.
171 3. We are a PI and the person is at one of our sites.
174 assert isinstance(person, Person)
176 if self.can_update(person):
179 if 'pi' in self['roles']:
180 if set(self['site_ids']).intersection(person['site_ids']):
181 # Can view people with equal or higher role IDs
182 return min(self['role_ids']) <= min(person['role_ids'])
186 add_role = Row.add_object(Role, 'person_role')
187 remove_role = Row.remove_object(Role, 'person_role')
189 add_key = Row.add_object(Key, 'person_key')
190 remove_key = Row.remove_object(Key, 'person_key')
192 def set_primary_site(self, site, commit = True):
194 Set the primary site for an existing user.
197 assert 'person_id' in self
198 assert 'site_id' in site
200 person_id = self['person_id']
201 site_id = site['site_id']
202 self.api.db.do("UPDATE person_site SET is_primary = False" \
203 " WHERE person_id = %(person_id)d",
205 self.api.db.do("UPDATE person_site SET is_primary = True" \
206 " WHERE person_id = %(person_id)d" \
207 " AND site_id = %(site_id)d",
213 assert 'site_ids' in self
214 assert site_id in self['site_ids']
216 # Make sure that the primary site is first in the list
217 self['site_ids'].remove(site_id)
218 self['site_ids'].insert(0, site_id)
220 def delete(self, commit = True):
222 Delete existing user.
226 keys = Keys(self.api, self['key_ids'])
228 key.delete(commit = False)
230 # Clean up miscellaneous join tables
231 for table in self.join_tables:
232 self.api.db.do("DELETE FROM %s WHERE person_id = %d" % \
233 (table, self['person_id']))
236 self['deleted'] = True
239 class Persons(Table):
241 Representation of row(s) from the persons table in the
245 def __init__(self, api, person_filter = None, columns = None):
246 Table.__init__(self, api, Person, columns)
247 #sql = "SELECT %s FROM view_persons WHERE deleted IS False" % \
248 # ", ".join(self.columns)
249 foreign_fields = {'role_ids': ('role_id', 'person_role'),
250 'roles': ('name', 'roles'),
251 'site_ids': ('site_id', 'person_site'),
252 'key_ids': ('key_id', 'person_key'),
253 'slice_ids': ('slice_id', 'slice_person')
256 db_fields = filter(lambda field: field not in foreign_fields.keys(), Person.fields.keys())
257 all_fields = db_fields + [value[0] for value in foreign_fields.values()]
260 _from = " FROM persons "
261 _join = " LEFT JOIN peer_person USING (person_id) "
262 _where = " WHERE deleted IS False "
265 # include all columns
267 tables = [value[1] for value in foreign_fields.values()]
269 for key in foreign_fields.keys():
270 foreign_keys[foreign_fields[key][0]] = key
272 if table in ['roles']:
273 _join += " LEFT JOIN roles USING(role_id) "
275 _join += " LEFT JOIN %s USING (person_id) " % (table)
278 columns = filter(lambda column: column in db_fields+foreign_fields.keys(), columns)
280 for column in columns:
281 if column in foreign_fields.keys():
282 (field, table) = foreign_fields[column]
283 foreign_keys[field] = column
286 if column in ['roles']:
287 _join += " LEFT JOIN roles USING(role_id) "
289 _join += " LEFT JOIN %s USING (person_id)" % \
290 (foreign_fields[column][1])
295 # postgres will return timestamps as datetime objects.
296 # XMLPRC cannot marshal datetime so convert to int
297 timestamps = ['date_created', 'last_updated', 'verification_expires']
299 if field in timestamps:
300 fields[fields.index(field)] = \
301 "CAST(date_part('epoch', %s) AS bigint) AS %s" % (field, field)
303 _select += ", ".join(fields)
304 sql = _select + _from + _join + _where
307 if person_filter is not None:
308 if isinstance(person_filter, (list, tuple, set)):
309 # Separate the list into integers and strings
310 ints = filter(lambda x: isinstance(x, (int, long)), person_filter)
311 strs = filter(lambda x: isinstance(x, StringTypes), person_filter)
312 person_filter = Filter(Person.fields, {'person_id': ints, 'email': strs})
313 sql += " AND (%s)" % person_filter.sql(api, "OR")
314 elif isinstance(person_filter, dict):
315 person_filter = Filter(Person.fields, person_filter)
316 sql += " AND (%s)" % person_filter.sql(api, "AND")
320 for row in self.api.db.selectall(sql):
321 person_id = row['person_id']
323 if all_persons.has_key(person_id):
324 for (key, key_list) in foreign_keys.items():
326 row[key_list] = [data]
327 if data and data not in all_persons[person_id][key_list]:
328 all_persons[person_id][key_list].append(data)
330 for key in foreign_keys.keys():
333 row[foreign_keys[key]] = [value]
335 row[foreign_keys[key]] = []
337 all_persons[person_id] = row
340 for row in all_persons.values():
341 obj = self.classobj(self.api, row)