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

'''
BSD Licence
Copyright (c) 2012, Science & Technology Facilities Council (STFC)
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, 
are permitted provided that the following conditions are met:

    * Redistributions of source code must retain the above copyright notice, 
        this list of conditions and the following disclaimer.
    * Redistributions in binary form must reproduce the above copyright notice,
        this list of conditions and the following disclaimer in the documentation
        and/or other materials provided with the distribution.
    * Neither the name of the Science & Technology Facilities Council (STFC) 
        nor the names of its contributors may be used to endorse or promote 
        products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, 
THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR 
PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS
BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, 
OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Created on 10 Jan 2012

@author: mnagni
'''
from libs.epb import EPB
from ea_model.moles3_4.observationcollection.mo_observationcollection import MO_ObservationCollection
from ea_model.moles3_4.observation.mo_observation import MO_Observation
from sqlalchemy import Table, Column, ForeignKey, Integer, String
from sqlalchemy.orm import mapper
from MolesManager.ceda_guid import CedaGUID
from sqlalchemy.orm.collections import InstrumentedList
from ea_model.iso_19115_2006_metadata_corrigendum.reference_system_information.md_identifier import MD_Identifier
from ea_model.iso_19115_2006_metadata_corrigendum.citation_and_responsible_party_information.ci_citation import CI_Citation
from ea_model.iso_19115_2006_metadata_corrigendum.extent_information.ex_geographicboundingbox import EX_GeographicBoundingBox
from libs.postgisutil import unifyGeometries, create_st_setSRID, getBox2D,\
    unifyGeometriesAsBBox
from ea_model.ceda_metadatamodel.ceda_observationcollection.ceda_observationcollection import CEDA_ObservationCollection
from ea_model.ceda_metadatamodel.ceda_observation.ceda_observation import CEDA_Observation
from ea_model.ceda_metadatamodel.ceda_project.ceda_project import CEDA_Project
from ea_model.moles3_4.utilities.mo_responsiblepartyinfo import MO_ResponsiblePartyInfo
from ea_model.moles3_4.utilities.ceda_rolevalue import CEDA_RoleValue
from MolesManager.codelist import MM_RoleValue, getCLValue
from libs.migration.processor.commons import fromPhenomenonTimeToString
from datetime import datetime
from ascore.utils import synchAttributes

class Moles3EPBFactory(EPB):
   
    def __init__(self, dbManager):
        self._dbManager = dbManager
        self._initCEDA_Customization()   
        

    def _initCEDA_Customization(self):
        self._associateCEDA_GUID()
        self._initSearchIndexes()        
        
    def _associateCEDA_GUID(self):
        guid_table = Table('ceda_guid', self._dbManager.metadata, \
                           Column('id', String, primary_key=True), \
                           Column('ceda_observationcollection', Integer, ForeignKey('ceda_observationcollection.id')), 
                           Column('ceda_observation', Integer, ForeignKey('ceda_observation.id')))
        mapper(CedaGUID, guid_table)
        self._dbManager.metadata.create_all()

    def _initSearchIndexes(self):
        #To Be Done - CHECK IF THE COLUMN ALREADY EXISTS!
        # We don't want sqlalchemy to know about this column so we add it externally.
        try:
            self._dbManager.engine.execute("alter table md_identifier add column code_search_vector tsvector")                 

            # This indexes the tsvector column

            self._dbManager.engine.execute("create index md_identifier_code_search_index on md_identifier using gin(code_search_vector)")

            # This sets up the trigger that keeps the tsvector column up to date.
            self._dbManager.engine.execute("create trigger md_identifier_code_search_update before update or insert on md_identifier \
                for each row execute procedure tsvector_update_trigger('code_search_vector', 'pg_catalog.english', code)")                        
        except Exception as e:
            pass

    def _getSession(self):
        if self._dbManager is not None:
            return self._dbManager.createDbSession()                
        return None
        
    def createEPB(self):
        return Moles3EPB(self._getSession())

class Moles3EPB(object):

    def __init__(self, session):
        self._session = session
        
    def close(self):
        return self._session.close()        
        
    def searchEager(self, clazz, inst_id):
        return EPB.searchEager(clazz, inst_id, self._session)     
     
    def _controlledCommit(self):
        try:
            self._session.commit()
        except Exception as e:
            print e
     
    def persistInstance(self, instance):
        """
            Adds a new migration object.
            @param migrationObj: the migration object to add
            @param session: an SQLAlchemy Session object. If not None the session IS NOT closed at the exit, 
            If None (default) a new Session is created from the underlying EPB and closed at the exit.
            @return an updated, session independent, object instance reflecting the new persisted object 
        """
        self._session.add(instance)
        self._session.commit()     
        
      
    def updateCedaObject(self, ceda_object, cols_to_update):
        """
            Update, eventually add to the session, and commit a CEDA Object in MOLES3 db. 
            @param ceda_object: the CEDA object to update
            @param cols_to_update: a dictionary containing the columns to update for the given ceda_object and the desired value.
            If the attribute is a list of objects the new instances are appended only if do not exist in the actual list 
            @return: the given instance with the updated attributes.
        """
        coll = None
        try:
            if ceda_object in self._session:
                coll = self._session.merge(ceda_object)
            else:
                self._session.add(ceda_object)
                coll = ceda_object   
        except Exception as e:
            print e
        if coll != None:        
            for k,v in cols_to_update.items():
                if hasattr(coll, k):
                    val = None
                    try:
                        val = self._session.merge(v)
                    except Exception:
                        val = v
                    coll_k = getattr(coll, k)                        
                    if type(coll_k) == list or type(coll_k) == InstrumentedList:
                        tmp_coll = []
                        if type(val) == list or type(val) == InstrumentedList:
                            tmp_coll.extend(val)
                        else:
                            tmp_coll.append(val)
                        for item in tmp_coll:
                            if item not in coll_k:
                                coll_k.append(item)
                    else:
                        setattr(coll, k, val)
        synchAttributes(coll)                                             
        self._controlledCommit()
        return coll                                      

    def getUnifyObservationCollectionGEAsBBox(self, collection):
        """
            Returns the union of the collections.member'a  GeographicExtension(s)
            @param collection: an CEDA_ObservationColleciton instance  
        """
        bboxes = []
        for member in collection.member:
            for ge in member.geographicExtent:
                bboxes.append(getGeograpicExtentGeometry(ge))
        
        return unifyGeometriesAsBBox(bboxes, self)  
        
    def getUnifyObservationCollectionPhenomenonTime(self, collection):
        """
            Returns the time period of the collections.member'a  phenomenonTime(s)
            @param collection: an CEDA_ObservationColleciton instance 
            @return: a tuple (startDate, endDate) 
        """
        ptStart = []
        ptEnd = []      
        for member in collection.member:
            for pt in member.phenomenonTime:
                ptString = fromPhenomenonTimeToString(pt)
                if ptString[0] is not None:                    
                    ptStart.append(datetime.strptime(ptString[0], '%Y-%m-%d'))
                if ptString[1] is not None:                    
                    ptEnd.append(datetime.strptime(ptString[1], '%Y-%m-%d'))
        ptStart.sort()                    
        ptEnd.sort()
        start = None
        end = None
        #takes the earlier date
        if len(ptStart) > 0:
            start = ptStart[0]
        #takes the latest date
        if len(ptEnd) > 0:
            end = ptEnd[len(ptEnd) - 1]
        return start, end  
        
        
        
        #return unifyGeometriesAsBBox(bboxes, self)


    def retrieveGUIDFromInstance(self, instance):
        """
            Returns the CedaGUID object associated with the given instance.
            @param instance: an instance of CEDA_Observation os CEDA_ObservationCollection  
        """
        if instance is None or not hasattr(instance, 'id'):
            return None
        if type(instance) == CEDA_ObservationCollection:
            return self._session.query(CedaGUID).filter(CedaGUID.ceda_observationcollection==instance.id).first()
        elif type(instance) == CEDA_Observation:
            return self._session.query(CedaGUID).filter(CedaGUID.ceda_observation==instance.id).first()        
    
    def observationCollectionHasObservation(self, obs_coll_id, obs_id):
        """
            Checks if a CEDA_Collection contains a given CEDA_Observation.
            @param obs_coll_id: the CEDA_ObservationColleciton id
            @param obs_id: the CEDA_Observation id
            @return: True if the collection contains the given observation, False otherwise  
        """
        coll = self._session.query(CEDA_ObservationCollection).filter(CEDA_ObservationCollection.id==obs_coll_id).first()
        obs = self._session.query(CEDA_Observation).filter(CEDA_Observation.id==obs_id).first()
        return obs in coll.member

    def observationAuthor(self, observation):
        """
            Lists the CEDA_Observation author.
            @param observation: the CEDA_Observation inside which look for the author            
            @return: True if the collection contains the given observation, False otherwise  
        """
        
        # TO FIX!!!
        for partyInfo in observation.relatedParty:
            if partyInfo.role == getCLValue(MM_RoleValue.cl_author):
                return partyInfo.party       

        

    def extractObservationByTitleKeywords(self, keywords):
        """
            Loooks for CEDA_Observation containing a specific title (observation.identifier.code)
            @param keywords: a space separated terms string
            @return: a tuple containing a CEDA_Observation satisfying the queryllection.idenfitier element having the title  
        """                
        # search_vector is a ts_vector column. To search for terms, you use the 
        # @@ operator. plainto_tsquery turns a string into a query that can be 
        # used with @@. So this adds a where clause like "WHERE search_vector 
        # @@ plaint_tsquery(<search string>)"
        q = self._session.query(CEDA_Observation). \
            join(MO_Observation).join(MO_Observation.identifier). \
            filter('md_identifier.code_search_vector @@ to_tsquery(:terms)')
        # This binds the :terms placeholder to the searchterms string. User input 
        # should always be put into queries this way to prevent SQL injection.
        q = q.params(terms=keywords)
        return q.all()


    def extractCollectionIdentifierByTitle(self, i_title):
        """
            Searches for an MD_Identifier from a CEDA_ObservationCollection contains a specific title (observation.identifier.code)
            @param i_title: the CEDA_ObservationCollection.identifier.title value to search for
            @return: a tuple containing a CEDA_ObservationCollection and the CEDA_ObservationCollection.idenfitier element having the title  
        """
        return self._session.query(CEDA_ObservationCollection, MD_Identifier). \
            join(MO_ObservationCollection).join(MO_ObservationCollection.identifier). \
            join(MD_Identifier.authority).filter(CI_Citation.title.like('%' + i_title + '%'))

    def extractObservationsForProject(self, project):
        """
            Searches for the CEDA_Observation associated with a CEDA_Project
            @param project: a CEDA_Project instance
            @return: a tuple containing the associated CEDA_Observation  
        """
        return self._session.query(CEDA_Observation). \
            join(CEDA_Observation, MO_Observation.inSupportOf).filter(CEDA_Project.id == project.id)

    def extractProjectObservationCollections(self, project):
        """
            Searches for the Observation_Collections associated with a CEDA_Project
            @param project: a CEDA_Project instance
            @return: a tuple containing the associated CEDA_ObservationCollection  
        """
        mo_obs = self._session.query(MO_Observation).join(CEDA_Project).filter(CEDA_Project.id == project.id).subquery()      
        obsers = self._session.query(CEDA_Observation).join(mo_obs, CEDA_Observation.id == mo_obs.c.id).one()
        #print "obsers: " + str(intSession.query(CEDA_Observation).join(mo_obs, CEDA_Observation.id == mo_obs.c.id).count())
        
        cos = self._session.query(CEDA_ObservationCollection).all()
        co = self._session.query(MO_ObservationCollection).join(MO_ObservationCollection.member).filter(MO_ObservationCollection.member.contains(obsers))
        
        observations = self._session.query(MO_ObservationCollection).join(CEDA_Observation). \
            filter(obsers.any(CEDA_Observation.id==obsers.c.id))
        print "observation:" + str(observations.count())
        return observations

    def search(self, clazz, inst_id):
        ret = EPB.search(clazz, inst_id, self._session)
        return ret
      
    def searchSelectiveLoad(self, clazz, inst_id, attributes): 
        """
            Searches a required instance by id loading selectively \
            the specified fields. The parameter "attributes" is a single string or a list of attributes 
            owned by the instance of "clazz". Furthermore such list may contain 
            also the children of the main attributes. For example "attrs" may look 
            like
            ['resultAccumulation', 'identifier.authority', 'resultTime.position.dateTime8601.month', \
                      'relatedParty.party', 'result.source.function', 'permission', \
                      'geographicExtent', 'phenomenonTime', 'keywords', 'description', \
                      'inSupportOf.abstract', 'dataLineage']
            the first parameter refers to the main class so is equivalent to 
            clazz.resultAccumulation
            the second parameter is equivalent to invoke
            clazz.identifier.authority
            As single string "attributes" could be as well just 'identifier.authority'
            @param clazz: the class type to search for
            @param inst_id: the instance id for which the search is done
            @param attributes: a single string or a list of attributes to load 
            @param session: a session to use for the query. By default a new one is created automatically at start and closed at the end
            @return the required instance              
        """               
        ret = EPB.searchSelectiveLoad(clazz, inst_id, attributes, self._session)
        return ret    
    
    def loadAttributes(self, instance, attributes):
        """
            Returns the attribute of an instance. The parameter "attributes" is a single string or a list of attributes 
            owned by the instance of "clazz". Furthermore such list may contain 
            also the children of the main attributes. For example "attrs" may look 
            like
            ['resultAccumulation', 'identifier.authority', 'resultTime.position.dateTime8601.month', \
                      'relatedParty.party', 'result.source.function', 'permission', \
                      'geographicExtent', 'phenomenonTime', 'keywords', 'description', \
                      'inSupportOf.abstract', 'dataLineage']
            the first parameter refers to the main class so is equivalent to 
            clazz.resultAccumulation
            the second parameter is equivalent to invoke
            clazz.identifier.authority
            As single string "attributes" could be as well just 'identifier.authority'
            @param instance: an instance containing the appropriate id
            @param attributes: the attribute value required
            @param session: the session to use for the operation
            @return: the given instance filled with the required attributes.                     
        """
        instance = self._session.merge(instance)
        EPB.loadAttributes(instance, attributes, self._session)                  
        return instance 

    def executeNative(self, sqlNative):
        return EPB.executeNative(sqlNative, self._session)  


def getGeograpicExtentGeometry(ge):
    '''
        Creates the appropriate postgis geometry from a EX_GeographicExtent
        @param ge: an EX_GeographicExtent instance
        @return: a postgix text geometry
    '''
    if isinstance(ge, EX_GeographicBoundingBox):
        return create_st_setSRID(ge.westBoundLongitude, ge.southBoundLatitude, \
                       ge.eastBoundLongitude, ge.northBoundLatitude)
    return None

def getUnifyObservationCollectionPhenomenonTime(collection):
    """
        Returns the time period of the collections.member'a  phenomenonTime(s)
        @param collection: an CEDA_ObservationColleciton instance 
        @return: a tuple (startDate, endDate) strings
    """
    dateFormat = '%Y-%m-%d'
    ptStart = []
    ptEnd = []      
    for member in collection.member:
        if member.phenomenonTime is None:
            continue
            
        pt =  member.phenomenonTime
        ptString = fromPhenomenonTimeToString(pt)
        if ptString[0] is not None:                    
            ptStart.append(datetime.strptime(ptString[0], dateFormat))
        if ptString[1] is not None:                    
            ptEnd.append(datetime.strptime(ptString[1], dateFormat))
    ptStart.sort()                    
    ptEnd.sort()
    start = None
    end = None
    #takes the earlier date
    if len(ptStart) > 0:
        start = ptStart[0].strftime(dateFormat) 
    #takes the latest date
    if len(ptEnd) > 0:
        end = ptEnd[len(ptEnd) - 1].strftime(dateFormat) 
    return start, end