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
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 update_last_updated(self, commit = True):
222 Update last_updated field with current time
225 assert 'person_id' in self
226 assert self.table_name
228 self.api.db.do("UPDATE %s SET last_updated = CURRENT_TIMESTAMP " % (self.table_name) + \
229 " where person_id = %d" % (self['person_id']) )
232 def delete(self, commit = True):
234 Delete existing user.
238 keys = Keys(self.api, self['key_ids'])
240 key.delete(commit = False)
242 # Clean up miscellaneous join tables
243 for table in self.join_tables:
244 self.api.db.do("DELETE FROM %s WHERE person_id = %d" % \
245 (table, self['person_id']))
248 self['deleted'] = True
251 class Persons(Table):
253 Representation of row(s) from the persons table in the
257 def __init__(self, api, person_filter = None, columns = None):
258 Table.__init__(self, api, Person, columns)
259 #sql = "SELECT %s FROM view_persons WHERE deleted IS False" % \
260 # ", ".join(self.columns)
261 foreign_fields = {'role_ids': ('role_id', 'person_role'),
262 'roles': ('name', 'roles'),
263 'site_ids': ('site_id', 'person_site'),
264 'key_ids': ('key_id', 'person_key'),
265 'slice_ids': ('slice_id', 'slice_person')
268 db_fields = filter(lambda field: field not in foreign_fields.keys(), Person.fields.keys())
269 all_fields = db_fields + [value[0] for value in foreign_fields.values()]
272 _from = " FROM persons "
273 _join = " LEFT JOIN peer_person USING (person_id) "
274 _where = " WHERE deleted IS False "
277 # include all columns
279 tables = [value[1] for value in foreign_fields.values()]
281 for key in foreign_fields.keys():
282 foreign_keys[foreign_fields[key][0]] = key
284 if table in ['roles']:
285 _join += " LEFT JOIN roles USING(role_id) "
287 _join += " LEFT JOIN %s USING (person_id) " % (table)
290 columns = filter(lambda column: column in db_fields+foreign_fields.keys(), columns)
292 for column in columns:
293 if column in foreign_fields.keys():
294 (field, table) = foreign_fields[column]
295 foreign_keys[field] = column
298 if column in ['roles']:
299 _join += " LEFT JOIN roles USING(role_id) "
301 _join += " LEFT JOIN %s USING (person_id)" % \
302 (foreign_fields[column][1])
307 # postgres will return timestamps as datetime objects.
308 # XMLPRC cannot marshal datetime so convert to int
309 timestamps = ['date_created', 'last_updated', 'verification_expires']
311 if field in timestamps:
312 fields[fields.index(field)] = \
313 "CAST(date_part('epoch', %s) AS bigint) AS %s" % (field, field)
315 _select += ", ".join(fields)
316 sql = _select + _from + _join + _where
319 if person_filter is not None:
320 if isinstance(person_filter, (list, tuple, set)):
321 # Separate the list into integers and strings
322 ints = filter(lambda x: isinstance(x, (int, long)), person_filter)
323 strs = filter(lambda x: isinstance(x, StringTypes), person_filter)
324 person_filter = Filter(Person.fields, {'person_id': ints, 'email': strs})
325 sql += " AND (%s) %s" % person_filter.sql(api, "OR")
326 elif isinstance(person_filter, dict):
327 person_filter = Filter(Person.fields, person_filter)
328 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
329 elif isinstance (person_filter, StringTypes):
330 person_filter = Filter(Person.fields, {'email':[person_filter]})
331 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
332 elif isinstance (person_filter, int):
333 person_filter = Filter(Person.fields, {'person_id':[person_filter]})
334 sql += " AND (%s) %s" % person_filter.sql(api, "AND")
336 raise PLCInvalidArgument, "Wrong person filter %r"%person_filter
340 for row in self.api.db.selectall(sql):
341 person_id = row['person_id']
343 if all_persons.has_key(person_id):
344 for (key, key_list) in foreign_keys.items():
346 row[key_list] = [data]
347 if data and data not in all_persons[person_id][key_list]:
348 all_persons[person_id][key_list].append(data)
350 for key in foreign_keys.keys():
353 row[foreign_keys[key]] = [value]
355 row[foreign_keys[key]] = []
357 all_persons[person_id] = row
360 for row in all_persons.values():
361 obj = self.classobj(self.api, row)