source: mauRepo/MolesManager/trunk/src/MolesManager/moles3epb.py @ 8358

Subversion URL: http://proj.badc.rl.ac.uk/svn/ndg/mauRepo/MolesManager/trunk/src/MolesManager/moles3epb.py@8358
Revision 8358, 15.3 KB checked in by mnagni, 7 years ago (diff)

Major refactoring about migration (now handles better create/update, even if the single updates have to be quite fully implemented)
Added the connection pool from SQLAlchemy

Line 
1'''
2BSD Licence
3Copyright (c) 2012, Science & Technology Facilities Council (STFC)
4All rights reserved.
5
6Redistribution and use in source and binary forms, with or without modification,
7are permitted provided that the following conditions are met:
8
9    * Redistributions of source code must retain the above copyright notice,
10        this list of conditions and the following disclaimer.
11    * Redistributions in binary form must reproduce the above copyright notice,
12        this list of conditions and the following disclaimer in the documentation
13        and/or other materials provided with the distribution.
14    * Neither the name of the Science & Technology Facilities Council (STFC)
15        nor the names of its contributors may be used to endorse or promote
16        products derived from this software without specific prior written permission.
17
18THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
19AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
20THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
21PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS
22BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,
23OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
24SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
25HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
26OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
27OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
28
29Created on 10 Jan 2012
30
31@author: mnagni
32'''
33from libs.epb import EPB
34from ea_model.ceda_metadatamodel.ceda_observationcollection.ceda_observationcollection import CEDA_ObservationCollection
35from ea_model.ceda_metadatamodel.ceda_observation.ceda_observation import CEDA_Observation
36from ea_model.iso_19115_2006_metadata_corrigendum.reference_system_information.md_identifier import MD_Identifier
37from ea_model.iso_19115_2006_metadata_corrigendum.citation_and_responsible_party_information.ci_citation import CI_Citation
38from ea_model.moles3_4.observationcollection.mo_observationcollection import MO_ObservationCollection
39from ea_model.moles3_4.observation.mo_observation import MO_Observation
40from ea_model.ceda_metadatamodel.ceda_project.ceda_project import CEDA_Project
41from sqlalchemy import Table, Column, ForeignKey, Integer, String
42from sqlalchemy.orm import mapper
43from MolesManager.ceda_guid import CedaGUID
44from ea_model.moles3_4.utilities.mo_responsiblepartyinfo import MO_ResponsiblePartyInfo
45from ea_model.moles3_4.utilities.mo_rolevalue import MO_RoleValue
46from sqlalchemy.orm.collections import InstrumentedList
47
48
49class Moles3EPBFactory(EPB):
50   
51    def __init__(self, dbManager):
52        self._dbManager = dbManager
53        self._initCEDA_Customization()   
54       
55
56    def _initCEDA_Customization(self):
57        self._associateCEDA_GUID()
58        self._initSearchIndexes()       
59       
60    def _associateCEDA_GUID(self):
61        guid_table = Table('ceda_guid', self._dbManager.metadata, \
62                           Column('id', String, primary_key=True), \
63                           Column('ceda_observationcollection', Integer, ForeignKey('ceda_observationcollection.id')), 
64                           Column('ceda_observation', Integer, ForeignKey('ceda_observation.id')))
65        mapper(CedaGUID, guid_table)
66        self._dbManager.metadata.create_all()
67
68    def _initSearchIndexes(self):
69        #To Be Done - CHECK IF THE COLUMN ALREADY EXISTS!
70        # We don't want sqlalchemy to know about this column so we add it externally.
71        try:
72            self._dbManager.engine.execute("alter table md_identifier add column code_search_vector tsvector")                 
73
74            # This indexes the tsvector column
75
76            self._dbManager.engine.execute("create index md_identifier_code_search_index on md_identifier using gin(code_search_vector)")
77
78            # This sets up the trigger that keeps the tsvector column up to date.
79            self._dbManager.engine.execute("create trigger md_identifier_code_search_update before update or insert on md_identifier \
80                for each row execute procedure tsvector_update_trigger('code_search_vector', 'pg_catalog.english', code)")                       
81        except Exception as e:
82            pass
83
84    def _getSession(self):
85        if self._dbManager is not None:
86            return self._dbManager.createDbSession()               
87        return None
88       
89    def createEPB(self):
90        return Moles3EPB(self._getSession())
91
92class Moles3EPB(object):
93
94    def __init__(self, session):
95        self._session = session
96       
97    def close(self):
98        return self._session.close()       
99       
100    def searchEager(self, clazz, inst_id):
101        return EPB.searchEager(clazz, inst_id)     
102     
103    def persistInstance(self, instance):
104        """
105            Adds a new migration object.
106            @param migrationObj: the migration object to add
107            @param session: an SQLAlchemy Session object. If not None the session IS NOT closed at the exit,
108            If None (default) a new Session is created from the underlying EPB and closed at the exit.
109            @return an updated, session independent, object instance reflecting the new persisted object
110        """     
111        EPB.persistInstance(instance, self._session)
112       
113     
114    def updateCedaObject(self, ceda_object, cols_to_update):
115        """
116            Update and eventually commit a CEDA Object in MOLES3 db.
117            @param ceda_object: the CEDA object to update
118            @param cols_to_update: a dictionary containing the columns to update for the given ceda_object and the desired value.
119            If the attribute is a list of objects the new instances are appended only if do not exist in the actual list
120            @return: the given instance with the updated attributes.
121        """
122        coll = None
123        try:
124            coll = self._session.merge(ceda_object)
125        except Exception as e:
126            print e
127        if coll != None:       
128            for k,v in cols_to_update.items():
129                if hasattr(coll, k):
130                    val = None
131                    try:
132                        val = self._session.merge(v)
133                    except Exception:
134                        val = v
135                    coll_k = getattr(coll, k)                       
136                    if type(coll_k) == list or type(coll_k) == InstrumentedList:
137                        tmp_coll = []
138                        if type(val) == list or type(val) == InstrumentedList:
139                            tmp_coll.extend(val)
140                        else:
141                            tmp_coll.append(val)
142                        for item in tmp_coll:
143                            if item not in coll_k:
144                                coll_k.append(item)
145                    else:
146                        setattr(coll, k, val)
147        self._session.commit()
148        return coll                                     
149
150    def retrieveGUIDFromInstance(self, instance):
151        """
152            Returns the CedaGUID object associated with the given instance.
153            @param instance: an instance of CEDA_Observation os CEDA_ObservationCollection 
154        """
155        if instance is None or not hasattr(instance, 'id'):
156            return None
157        if type(instance) == CEDA_ObservationCollection:
158            return self._session.query(CedaGUID).filter(CedaGUID.ceda_observationcollection==instance.id).first()
159        elif type(instance) == CEDA_Observation:
160            return self._session.query(CedaGUID).filter(CedaGUID.ceda_observation==instance.id).first()       
161   
162    def observationCollectionHasObservation(self, obs_coll_id, obs_id):
163        """
164            Checks if a CEDA_Collection contains a given CEDA_Observation.
165            @param obs_coll_id: the CEDA_ObservationColleciton id
166            @param obs_id: the CEDA_Observation id
167            @return: True if the collection contains the given observation, False otherwise 
168        """
169        ret = self._session.query(CEDA_ObservationCollection, CEDA_Observation).filter(CEDA_ObservationCollection.id==obs_coll_id).filter(CEDA_Observation.id==obs_id).count() > 0
170        return ret
171
172    def observationAuthor(self, obs_id):
173        """
174            Lists the CEDA_Observation author.
175            @param obs_id: the CEDA_Observation id           
176            @return: True if the collection contains the given observation, False otherwise 
177        """
178        ret = self._session.query(MO_ResponsiblePartyInfo).join(MO_Observation). \
179            filter(MO_ResponsiblePartyInfo.role == MO_RoleValue.cl_author). \
180            filter(MO_Observation.id == obs_id)       
181        return ret
182
183    def extractObservationByTitleKeywords(self, keywords):
184        """
185            Loooks for CEDA_Observation containing a specific title (observation.identifier.code)
186            @param keywords: a space separated terms string
187            @return: a tuple containing a CEDA_Observation satisfying the queryllection.idenfitier element having the title 
188        """               
189        # search_vector is a ts_vector column. To search for terms, you use the
190        # @@ operator. plainto_tsquery turns a string into a query that can be
191        # used with @@. So this adds a where clause like "WHERE search_vector
192        # @@ plaint_tsquery(<search string>)"
193        q = self._session.query(CEDA_Observation). \
194            join(MO_Observation).join(MO_ObservationCollection.identifier). \
195            filter('md_identifier.code_search_vector @@ to_tsquery(:terms)')
196        # This binds the :terms placeholder to the searchterms string. User input
197        # should always be put into queries this way to prevent SQL injection.
198        q = q.params(terms=keywords)
199        return q.all()
200
201
202    def extractCollectionIdentifierByTitle(self, i_title):
203        """
204            Searches for an MD_Identifier from a CEDA_ObservationCollection contains a specific title (observation.identifier.code)
205            @param i_title: the CEDA_ObservationCollection.identifier.title value to search for
206            @return: a tuple containing a CEDA_ObservationCollection and the CEDA_ObservationCollection.idenfitier element having the title 
207        """
208        return self._session.query(CEDA_ObservationCollection, MD_Identifier). \
209            join(MO_ObservationCollection).join(MO_ObservationCollection.identifier). \
210            join(MD_Identifier.authority).filter(CI_Citation.title.like('%' + i_title + '%'))
211
212    def extractObservationsForProject(self, project):
213        """
214            Searches for the CEDA_Observation associated with a CEDA_Project
215            @param project: a CEDA_Project instance
216            @return: a tuple containing the associated CEDA_Observation 
217        """
218        return self._session.query(CEDA_Observation). \
219            join(CEDA_Observation, MO_Observation.inSupportOf).filter(CEDA_Project.id == project.id)
220
221    def extractProjectObservationCollections(self, project):
222        """
223            Searches for the Observation_Collections associated with a CEDA_Project
224            @param project: a CEDA_Project instance
225            @return: a tuple containing the associated CEDA_ObservationCollection 
226        """
227        mo_obs = self._session.query(MO_Observation).join(CEDA_Project).filter(CEDA_Project.id == project.id).subquery()     
228        obsers = self._session.query(CEDA_Observation).join(mo_obs, CEDA_Observation.id == mo_obs.c.id).one()
229        #print "obsers: " + str(intSession.query(CEDA_Observation).join(mo_obs, CEDA_Observation.id == mo_obs.c.id).count())
230       
231        cos = self._session.query(CEDA_ObservationCollection).all()
232        co = self._session.query(MO_ObservationCollection).join(MO_ObservationCollection.member).filter(MO_ObservationCollection.member.contains(obsers))
233       
234        observations = self._session.query(MO_ObservationCollection).join(CEDA_Observation). \
235            filter(obsers.any(CEDA_Observation.id==obsers.c.id))
236        print "observation:" + str(observations.count())
237        return observations
238
239    def search(self, clazz, inst_id):
240        ret = EPB.search(clazz, inst_id, self._session)
241        return ret
242     
243    def searchSelectiveLoad(self, clazz, inst_id, attributes): 
244        """
245            Searches a required instance by id loading selectively \
246            the specified fields. The parameter "attributes" is a single string or a list of attributes
247            owned by the instance of "clazz". Furthermore such list may contain
248            also the children of the main attributes. For example "attrs" may look
249            like
250            ['resultAccumulation', 'identifier.authority', 'resultTime.position.dateTime8601.month', \
251                      'relatedParty.party', 'result.source.function', 'permission', \
252                      'geographicExtent', 'phenomenonTime', 'keywords', 'description', \
253                      'inSupportOf.abstract', 'dataLineage']
254            the first parameter refers to the main class so is equivalent to
255            clazz.resultAccumulation
256            the second parameter is equivalent to invoke
257            clazz.identifier.authority
258            As single string "attributes" could be as well just 'identifier.authority'
259            @param clazz: the class type to search for
260            @param inst_id: the instance id for which the search is done
261            @param attributes: a single string or a list of attributes to load
262            @param session: a session to use for the query. By default a new one is created automatically at start and closed at the end
263            @return the required instance             
264        """               
265        ret = EPB.searchSelectiveLoad(clazz, inst_id, attributes, self._session)
266        return ret   
267   
268    def loadAttributes(self, instance, attributes):
269        """
270            Returns the attribute of an instance. The parameter "attributes" is a single string or a list of attributes
271            owned by the instance of "clazz". Furthermore such list may contain
272            also the children of the main attributes. For example "attrs" may look
273            like
274            ['resultAccumulation', 'identifier.authority', 'resultTime.position.dateTime8601.month', \
275                      'relatedParty.party', 'result.source.function', 'permission', \
276                      'geographicExtent', 'phenomenonTime', 'keywords', 'description', \
277                      'inSupportOf.abstract', 'dataLineage']
278            the first parameter refers to the main class so is equivalent to
279            clazz.resultAccumulation
280            the second parameter is equivalent to invoke
281            clazz.identifier.authority
282            As single string "attributes" could be as well just 'identifier.authority'
283            @param instance: an instance containing the appropriate id
284            @param attributes: the attribute value required
285            @param session: the session to use for the operation
286            @return: the given instance filled with the required attributes.                     
287        """
288        instance = self._session.merge(instance)
289        EPB.loadAttributes(instance, attributes, self._session)                 
290        return instance
291
292    def executeNative(self, sqlNative):
293        return EPB.executeNative(sqlNative, self._session) 
Note: See TracBrowser for help on using the repository browser.