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.34 2007/03/28 19:15:34 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 conflicts = Persons(self.api, [email])
104 for person in conflicts:
105 if 'person_id' not in self or self['person_id'] != person['person_id']:
106 raise PLCInvalidArgument, "E-mail address already in use"
110 def validate_password(self, password):
112 Encrypt password if necessary before committing to the
118 if len(password) > len(magic) and \
119 password[0:len(magic)] == magic:
122 # Generate a somewhat unique 8 character salt string
123 salt = str(time.time()) + str(Random().random())
124 salt = md5.md5(salt).hexdigest()[:8]
125 return crypt.crypt(password.encode(self.api.encoding), magic + salt + "$")
127 validate_date_created = Row.validate_timestamp
128 validate_last_updated = Row.validate_timestamp
129 validate_verification_expires = Row.validate_timestamp
131 def can_update(self, person):
133 Returns true if we can update the specified person. We can
136 1. We are the person.
138 3. We are a PI and the person is a user or tech or at
142 assert isinstance(person, Person)
144 if self['person_id'] == person['person_id']:
147 if 'admin' in self['roles']:
150 if 'pi' in self['roles']:
151 if set(self['site_ids']).intersection(person['site_ids']):
152 # Can update people with higher role IDs
153 return min(self['role_ids']) < min(person['role_ids'])
157 def can_view(self, person):
159 Returns true if we can view the specified person. We can
162 1. We are the person.
164 3. We are a PI and the person is at one of our sites.
167 assert isinstance(person, Person)
169 if self.can_update(person):
172 if 'pi' in self['roles']:
173 if set(self['site_ids']).intersection(person['site_ids']):
174 # Can view people with equal or higher role IDs
175 return min(self['role_ids']) <= min(person['role_ids'])
179 add_role = Row.add_object(Role, 'person_role')
180 remove_role = Row.remove_object(Role, 'person_role')
182 add_key = Row.add_object(Key, 'person_key')
183 remove_key = Row.remove_object(Key, 'person_key')
185 def set_primary_site(self, site, commit = True):
187 Set the primary site for an existing user.
190 assert 'person_id' in self
191 assert 'site_id' in site
193 person_id = self['person_id']
194 site_id = site['site_id']
195 self.api.db.do("UPDATE person_site SET is_primary = False" \
196 " WHERE person_id = %(person_id)d",
198 self.api.db.do("UPDATE person_site SET is_primary = True" \
199 " WHERE person_id = %(person_id)d" \
200 " AND site_id = %(site_id)d",
206 assert 'site_ids' in self
207 assert site_id in self['site_ids']
209 # Make sure that the primary site is first in the list
210 self['site_ids'].remove(site_id)
211 self['site_ids'].insert(0, site_id)
213 def delete(self, commit = True):
215 Delete existing user.
219 keys = Keys(self.api, self['key_ids'])
221 key.delete(commit = False)
223 # Clean up miscellaneous join tables
224 for table in self.join_tables:
225 self.api.db.do("DELETE FROM %s WHERE person_id = %d" % \
226 (table, self['person_id']))
229 self['deleted'] = True
232 class Persons(Table):
234 Representation of row(s) from the persons table in the
238 def __init__(self, api, person_filter = None, columns = None):
239 Table.__init__(self, api, Person, columns)
240 #sql = "SELECT %s FROM view_persons WHERE deleted IS False" % \
241 # ", ".join(self.columns)
242 foreign_fields = {'role_ids': ('role_id', 'person_role'),
243 'roles': ('name', 'roles'),
244 'site_ids': ('site_id', 'person_site'),
245 'key_ids': ('key_id', 'person_key'),
246 'slice_ids': ('slice_id', 'slice_person')
249 db_fields = filter(lambda field: field not in foreign_fields.keys(), Person.fields.keys())
250 all_fields = db_fields + [value[0] for value in foreign_fields.values()]
253 _from = " FROM persons "
254 _join = " LEFT JOIN peer_person USING (person_id) "
255 _where = " WHERE deleted IS False "
258 # include all columns
260 tables = [value[1] for value in foreign_fields.values()]
262 for key in foreign_fields.keys():
263 foreign_keys[foreign_fields[key][0]] = key
265 if table in ['roles']:
266 _join += " LEFT JOIN roles USING(role_id) "
268 _join += " LEFT JOIN %s USING (person_id) " % (table)
271 columns = filter(lambda column: column in db_fields+foreign_fields.keys(), columns)
273 for column in columns:
274 if column in foreign_fields.keys():
275 (field, table) = foreign_fields[column]
276 foreign_keys[field] = column
279 if column in ['roles']:
280 _join += " LEFT JOIN roles USING(role_id) "
282 _join += " LEFT JOIN %s USING (person_id)" % \
283 (foreign_fields[column][1])
288 # postgres will return timestamps as datetime objects.
289 # XMLPRC cannot marshal datetime so convert to int
290 timestamps = ['date_created', 'last_updated']
292 if field in timestamps:
293 fields[fields.index(field)] = \
294 "CAST(date_part('epoch', %s) AS bigint) AS %s" % (field, field)
296 _select += ", ".join(fields)
297 sql = _select + _from + _join + _where
300 if person_filter is not None:
301 if isinstance(person_filter, (list, tuple, set)):
302 # Separate the list into integers and strings
303 ints = filter(lambda x: isinstance(x, (int, long)), person_filter)
304 strs = filter(lambda x: isinstance(x, StringTypes), person_filter)
305 person_filter = Filter(Person.fields, {'person_id': ints, 'email': strs})
306 sql += " AND (%s)" % person_filter.sql(api, "OR")
307 elif isinstance(person_filter, dict):
308 person_filter = Filter(Person.fields, person_filter)
309 sql += " AND (%s)" % person_filter.sql(api, "AND")
313 for row in self.api.db.selectall(sql):
314 person_id = row['person_id']
316 if all_persons.has_key(person_id):
317 for (key, key_list) in foreign_keys.items():
319 row[key_list] = [data]
320 if data and data not in all_persons[person_id][key_list]:
321 all_persons[person_id][key_list].append(data)
323 for key in foreign_keys.keys():
326 row[foreign_keys[key]] = [value]
328 row[foreign_keys[key]] = []
330 all_persons[person_id] = row
333 for row in all_persons.values():
334 obj = self.classobj(self.api, row)