1 from types import StringTypes
2 from datetime import datetime
4 from sqlalchemy import Column, Integer, String, DateTime
5 from sqlalchemy import Table, Column, MetaData, join, ForeignKey
6 from sqlalchemy.orm import relationship, backref
7 from sqlalchemy.orm import column_property
8 from sqlalchemy.orm import object_mapper
9 from sqlalchemy.orm import validates
10 from sqlalchemy.ext.declarative import declarative_base
12 from sfa.storage.record import Record
13 from sfa.util.sfalogging import logger
14 from sfa.util.sfatime import utcparse, datetime_to_string
15 from sfa.util.xml import XML
17 from sfa.trust.gid import GID
19 ##############################
20 Base=declarative_base()
25 # historically the front end to the db dealt with dicts, so the code was only dealing with dicts
26 # sqlalchemy however offers an object interface, meaning that you write obj.id instead of obj['id']
27 # which is admittedly much nicer
28 # however we still need to deal with dictionaries if only for the xmlrpc layer
30 # here are a few utilities for this
32 # (*) first off, when an old pieve of code needs to be used as-is, if only temporarily, the simplest trick
33 # is to use obj.__dict__
34 # this behaves exactly like required, i.e. obj.__dict__['field']='new value' does change obj.field
35 # however this depends on sqlalchemy's implementation so it should be avoided
37 # (*) second, when an object needs to be exposed to the xmlrpc layer, we need to convert it into a dict
38 # remember though that writing the resulting dictionary won't change the object
39 # essentially obj.__dict__ would be fine too, except that we want to discard alchemy private keys starting with '_'
40 # 2 ways are provided for that:
43 # the former dict(obj) relies on __iter__() and next() below, and does not rely on the fields names
44 # although it seems to work fine, I've found cases where it issues a weird python error that I could not get right
45 # so the latter obj.todict() seems more reliable but more hacky as is relies on the form of fields, so this can probably be improved
47 # (*) finally for converting a dictionary into an sqlalchemy object, we provide
48 # obj.load_from_dict(dict)
50 class AlchemyObj(Record):
52 self._i = iter(object_mapper(self).columns)
55 n = self._i.next().name
56 return n, getattr(self, n)
58 # # only intended for debugging
59 # def inspect (self, logger, message=""):
60 # logger.info("%s -- Inspecting AlchemyObj -- attrs"%message)
62 # if not k.startswith('_'):
63 # logger.info (" %s: %s"%(k,getattr(self,k)))
64 # logger.info("%s -- Inspecting AlchemyObj -- __dict__"%message)
66 # for (k,v) in d.iteritems():
67 # logger.info("[%s]=%s"%(k,v))
70 ##############################
71 # various kinds of records are implemented as an inheritance hierarchy
72 # RegRecord is the base class for all actual variants
73 # a first draft was using 'type' as the discriminator for the inheritance
74 # but we had to define another more internal column (classtype) so we
75 # accomodate variants in types like authority+am and the like
77 class RegRecord (Base,AlchemyObj):
78 __tablename__ = 'records'
79 record_id = Column (Integer, primary_key=True)
80 # this is the discriminator that tells which class to use
81 classtype = Column (String)
82 # in a first version type was the discriminator
83 # but that could not accomodate for 'authority+sa' and the like
84 type = Column (String)
87 authority = Column (String)
88 peer_authority = Column (String)
89 pointer = Column (Integer, default=-1)
90 date_created = Column (DateTime)
91 last_updated = Column (DateTime)
92 # use the 'type' column to decide which subclass the object is of
93 __mapper_args__ = { 'polymorphic_on' : classtype }
95 fields = [ 'type', 'hrn', 'gid', 'authority', 'peer_authority' ]
96 def __init__ (self, type=None, hrn=None, gid=None, authority=None, peer_authority=None,
97 pointer=None, dict=None):
98 if type: self.type=type
101 if isinstance(gid, StringTypes): self.gid=gid
102 else: self.gid=gid.save_to_string(save_parents=True)
103 if authority: self.authority=authority
104 if peer_authority: self.peer_authority=peer_authority
105 if pointer: self.pointer=pointer
106 if dict: self.load_from_dict (dict)
109 result="<Record id=%s, type=%s, hrn=%s, authority=%s, pointer=%s" % \
110 (self.record_id, self.type, self.hrn, self.authority, self.pointer)
111 # skip the uniform '--- BEGIN CERTIFICATE --' stuff
112 if self.gid: result+=" gid=%s..."%self.gid[28:36]
113 else: result+=" nogid"
117 # shortcut - former implem. was record-based
118 def get (self, field, default):
119 return getattr(self,field,default)
122 def validate_gid (self, key, gid):
123 if gid is None: return
124 elif isinstance(gid, StringTypes): return gid
125 else: return gid.save_to_string(save_parents=True)
127 def validate_datetime (self, key, incoming):
128 if isinstance (incoming, datetime): return incoming
129 elif isinstance (incoming, (int,float)):return datetime.fromtimestamp (incoming)
130 else: logger.info("Cannot validate datetime for key %s with input %s"%\
133 @validates ('date_created')
134 def validate_date_created (self, key, incoming): return self.validate_datetime (key, incoming)
136 @validates ('last_updated')
137 def validate_last_updated (self, key, incoming): return self.validate_datetime (key, incoming)
139 # xxx - there might be smarter ways to handle get/set'ing gid using validation hooks
140 def get_gid_object (self):
141 if not self.gid: return None
142 else: return GID(string=self.gid)
144 def just_created (self):
146 self.date_created=now
147 self.last_updated=now
149 def just_updated (self):
151 self.last_updated=now
153 #################### cross-relations tables
154 # authority x user (pis) association
155 authority_pi_table = \
156 Table ( 'authority_pi', Base.metadata,
157 Column ('authority_id', Integer, ForeignKey ('records.record_id'), primary_key=True),
158 Column ('pi_id', Integer, ForeignKey ('records.record_id'), primary_key=True),
160 # slice x user (researchers) association
161 slice_researcher_table = \
162 Table ( 'slice_researcher', Base.metadata,
163 Column ('slice_id', Integer, ForeignKey ('records.record_id'), primary_key=True),
164 Column ('researcher_id', Integer, ForeignKey ('records.record_id'), primary_key=True),
167 ##############################
168 # all subclasses define a convenience constructor with a default value for type,
169 # and when applicable a way to define local fields in a kwd=value argument
171 class RegAuthority (RegRecord):
172 __tablename__ = 'authorities'
173 __mapper_args__ = { 'polymorphic_identity' : 'authority' }
174 record_id = Column (Integer, ForeignKey ("records.record_id"), primary_key=True)
175 #### extensions come here
176 reg_pis = relationship \
178 secondary=authority_pi_table,
179 primaryjoin=RegRecord.record_id==authority_pi_table.c.authority_id,
180 secondaryjoin=RegRecord.record_id==authority_pi_table.c.pi_id,
181 backref='reg_authorities_as_pi')
183 def __init__ (self, **kwds):
184 # fill in type if not previously set
185 if 'type' not in kwds: kwds['type']='authority'
186 # base class constructor
187 RegRecord.__init__(self, **kwds)
189 # no proper data yet, just hack the typename
191 return RegRecord.__repr__(self).replace("Record","Authority")
193 def update_pis (self, pi_hrns):
194 # don't ruin the import of that file in a client world
195 from sfa.storage.alchemy import dbsession
196 # strip that in case we have <researcher> words </researcher>
197 pi_hrns = [ x.strip() for x in pi_hrns ]
198 request = dbsession.query (RegUser).filter(RegUser.hrn.in_(pi_hrns))
199 logger.info ("RegAuthority.update_pis: %d incoming pis, %d matches found"%(len(pi_hrns),request.count()))
200 pis = dbsession.query (RegUser).filter(RegUser.hrn.in_(pi_hrns)).all()
204 class RegSlice (RegRecord):
205 __tablename__ = 'slices'
206 __mapper_args__ = { 'polymorphic_identity' : 'slice' }
207 record_id = Column (Integer, ForeignKey ("records.record_id"), primary_key=True)
208 #### extensions come here
209 reg_researchers = relationship \
211 secondary=slice_researcher_table,
212 primaryjoin=RegRecord.record_id==slice_researcher_table.c.slice_id,
213 secondaryjoin=RegRecord.record_id==slice_researcher_table.c.researcher_id,
214 backref='reg_slices_as_researcher')
216 def __init__ (self, **kwds):
217 if 'type' not in kwds: kwds['type']='slice'
218 RegRecord.__init__(self, **kwds)
221 return RegRecord.__repr__(self).replace("Record","Slice")
223 def update_researchers (self, researcher_hrns):
224 # don't ruin the import of that file in a client world
225 from sfa.storage.alchemy import dbsession
226 # strip that in case we have <researcher> words </researcher>
227 researcher_hrns = [ x.strip() for x in researcher_hrns ]
228 request = dbsession.query (RegUser).filter(RegUser.hrn.in_(researcher_hrns))
229 logger.info ("RegSlice.update_researchers: %d incoming researchers, %d matches found"%(len(researcher_hrns),request.count()))
230 researchers = dbsession.query (RegUser).filter(RegUser.hrn.in_(researcher_hrns)).all()
231 self.reg_researchers = researchers
233 # when dealing with credentials, we need to retrieve the PIs attached to a slice
235 # don't ruin the import of that file in a client world
236 from sfa.storage.alchemy import dbsession
237 from sfa.util.xrn import get_authority
238 authority_hrn = get_authority(self.hrn)
239 auth_record = dbsession.query(RegAuthority).filter_by(hrn=authority_hrn).first()
240 return auth_record.reg_pis
242 @validates ('expires')
243 def validate_expires (self, key, incoming): return self.validate_datetime (key, incoming)
246 class RegNode (RegRecord):
247 __tablename__ = 'nodes'
248 __mapper_args__ = { 'polymorphic_identity' : 'node' }
249 record_id = Column (Integer, ForeignKey ("records.record_id"), primary_key=True)
251 def __init__ (self, **kwds):
252 if 'type' not in kwds: kwds['type']='node'
253 RegRecord.__init__(self, **kwds)
256 return RegRecord.__repr__(self).replace("Record","Node")
259 class RegUser (RegRecord):
260 __tablename__ = 'users'
261 # these objects will have type='user' in the records table
262 __mapper_args__ = { 'polymorphic_identity' : 'user' }
263 record_id = Column (Integer, ForeignKey ("records.record_id"), primary_key=True)
264 #### extensions come here
265 email = Column ('email', String)
266 # can't use name 'keys' here because when loading from xml we're getting
267 # a 'keys' tag, and assigning a list of strings in a reference column like this crashes
268 reg_keys = relationship \
269 ('RegKey', backref='reg_user',
270 cascade="all, delete, delete-orphan")
272 # so we can use RegUser (email=.., hrn=..) and the like
273 def __init__ (self, **kwds):
274 # handle local settings
275 if 'email' in kwds: self.email=kwds.pop('email')
276 if 'type' not in kwds: kwds['type']='user'
277 RegRecord.__init__(self, **kwds)
279 # append stuff at the end of the record __repr__
281 result = RegRecord.__repr__(self).replace("Record","User")
282 result.replace (">"," email=%s"%self.email)
287 def validate_email(self, key, address):
288 assert '@' in address
292 # xxx tocheck : not sure about eager loading of this one
293 # meaning, when querying the whole records, we expect there should
294 # be a single query to fetch all the keys
295 # or, is it enough that we issue a single query to retrieve all the keys
297 __tablename__ = 'keys'
298 key_id = Column (Integer, primary_key=True)
299 record_id = Column (Integer, ForeignKey ("records.record_id"))
300 key = Column (String)
301 pointer = Column (Integer, default = -1)
303 def __init__ (self, key, pointer=None):
305 if pointer: self.pointer=pointer
308 result="<key id=%s key=%s..."%(self.key_id,self.key[8:16],)
309 try: result += " user=%s"%self.reg_user.record_id
310 except: result += " no-user"
314 ##############################
315 # although the db needs of course to be reachable for the following functions
316 # the schema management functions are here and not in alchemy
317 # because the actual details of the classes need to be known
318 # migrations: this code has no notion of the previous versions
319 # of the data model nor of migrations
320 # sfa.storage.migrations.db_init uses this when starting from
322 def init_tables(engine):
323 logger.info("Initializing db schema from current/latest model")
324 Base.metadata.create_all(engine)
326 def drop_tables(engine):
327 logger.info("Dropping tables from current/latest model")
328 Base.metadata.drop_all(engine)
330 ##############################
331 # create a record of the right type from either a dict or an xml string
332 def make_record (dict={}, xml=""):
333 if dict: return make_record_dict (dict)
334 elif xml: return make_record_xml (xml)
335 else: raise Exception("make_record has no input")
337 # convert an incoming record - typically from xmlrpc - into an object
338 def make_record_dict (record_dict):
339 assert ('type' in record_dict)
340 type=record_dict['type'].split('+')[0]
341 if type=='authority':
342 result=RegAuthority (dict=record_dict)
344 result=RegUser (dict=record_dict)
346 result=RegSlice (dict=record_dict)
348 result=RegNode (dict=record_dict)
350 logger.debug("Untyped RegRecord instance")
351 result=RegRecord (dict=record_dict)
352 logger.info ("converting dict into Reg* with type=%s"%type)
353 logger.info ("returning=%s"%result)
355 # register non-db attributes in an extensions field
358 def make_record_xml (xml):
359 xml_record = XML(xml)
360 xml_dict = xml_record.todict()
361 logger.info("load from xml, keys=%s"%xml_dict.keys())
362 return make_record_dict (xml_dict)