Changeset 8205 for mauRepo

Timestamp:

01/04/12 18:07:50 (8 years ago)

Author:

mnagni

Message:

Complete - # 22416: CEDA MOLES3 - search function - by title keyword
 http://team.ceda.ac.uk/trac/ceda/ticket/22416

Location:

mauRepo/MolesManager/trunk/src

Files:

• MolesManager/moles3epb.py (modified) (2 diffs)
• MolesManager/molesSessionMiddleware.py (modified) (3 diffs)
• libs/migration/tests/moles3epbmothods.py (modified) (4 diffs)
• libs/migration/tests/test_utils.py (modified) (2 diffs)

mauRepo/MolesManager/trunk/src/MolesManager/moles3epb.py

@@ -13 +13 @@
 from ea_model.iso_19115_2006_metadata_corrigendum.citation_and_responsible_party_information.ci_citation import CI_Citation
 from ea_model.moles3_4.observationcollection.mo_observationcollection import MO_ObservationCollection
+from ea_model.moles3_4.observation.mo_observation import MO_Observation
 
 #molesDB = DbManager(MOLES3_DB_CONNECTION, MOLES3_DB_SCRIPT)
@@ -63 +64 @@
         intSession = Moles3EPB._getSession(session)
         return intSession.query(CEDA_ObservationCollection, CEDA_Observation).filter(CEDA_ObservationCollection.id==obs_coll_id).filter(CEDA_Observation.id==obs_id).count() > 0
+
+    @classmethod
+    def extractObservationByTitleKeywords(self, keywords, session = None):
+        """
+            Loooks for CEDA_Observation containing a specific title (observation.identifier.code)
+            @param i_title: a sspace separated terms string
+            @return: a tuple containing a CEDA_Observation satisfying the queryllection.idenfitier element having the title  
+        """                
+        intSession = Moles3EPB._getSession(session)
+        # 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 = intSession.query(CEDA_Observation). \
+            join(MO_Observation).join(MO_ObservationCollection.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
+
 
     @classmethod

mauRepo/MolesManager/trunk/src/MolesManager/molesSessionMiddleware.py

@@ -11 +11 @@
 from libs.commons_db import DbManager
 from libs.migration.MigrationEPB import MigrationEPB
+from ea_model.iso_19115_2006_metadata_corrigendum.reference_system_information.md_identifier import MD_Identifier
 
 
@@ -21 +22 @@
     
 
+    def _initSearchIndexes(self, db_manager):
+        #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:
+            db_manager._engine.execute("alter table md_identifier add column code_search_vector tsvector")                 
+
+            # This indexes the tsvector column
+            db_manager._engine.execute("create index md_identifier_code_search_index on md_identifier using gin(code_search_vector)")            
+        except Exception as e:
+            pass
+
+        # This sets up the trigger that keeps the tsvector column up to date.
+        db_manager._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')")
+
     
     def _getMolesSession(self):
@@ -30 +46 @@
             Moles3EPB.overrrideDBManager(molesDB)
             MolesSessionMiddleware._epbInitialized = True
+            
+            self._initSearchIndexes(molesDB)
+            
             return Moles3EPB.getNewMolesSession()
         else:

mauRepo/MolesManager/trunk/src/libs/migration/tests/moles3epbmothods.py

@@ -11 +11 @@
 import logging
 from logging import StreamHandler
-from ea_model.iso_19115_2006_metadata_corrigendum.reference_system_information.md_identifier import MD_Identifier
-from test_utils import createObservationCollection
-from ea_model.ceda_metadatamodel.ceda_observationcollection.ceda_observationcollection import CEDA_ObservationCollection
-from ea_model.moles3_4.observationcollection.mo_observationcollection import MO_ObservationCollection
-from ea_model.iso_19115_2006_metadata_corrigendum.citation_and_responsible_party_information.ci_citation import CI_Citation
+from test_utils import createObservationCollection, createObservation
+from sqlalchemy.dialects import postgres
 
 
@@ -23 +20 @@
         Moles3EPBTest.molesDB = DbManager(MOLES3_DB_CONNECTION, MOLES3_DB_SCRIPT)
         Moles3EPB.overrrideDBManager(Moles3EPBTest.molesDB)
+        self._initSearchIndexes(Moles3EPBTest.molesDB)        
         self.logging = logging.getLogger('Moles3EPBTest')
         self.logging.addHandler(StreamHandler())
@@ -32 +30 @@
 
     def runTest(self):
-        self.checkExtractCollectionIdentifierByTitle()        
+        self.checkExtractCollectionIdentifierByTitle()   
+        self.checkExtractObservationByTitleKeywords() 
 
     def checkExtractCollectionIdentifierByTitle(self):
@@ -51 +50 @@
         session.commit()
 
+    def checkExtractObservationByTitleKeywords(self):
+        session = Moles3EPB.getNewMolesSession();
+        observation = createObservation()
+        
+        self.logging.info('Stores an new CEDA_Observation')
+        Moles3EPB.addCedaObject(observation, session)
+        session.commit()
+        
+        obs = Moles3EPB.extractObservationByTitleKeywords('test_code', session)
+        self.assertTrue(obs.count() > 1, "Cannot find a CEDA_Observation")
+        obs = Moles3EPB.extractObservationByTitleKeywords('dummy_code', session)
+        self.assertTrue(obs.count() == 0, "Should NOT find a CEDA_Observation")        
+
+    def _initSearchIndexes(self, db_manager):
+        #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:
+            db_manager._engine.execute("alter table md_identifier add column code_search_vector tsvector")                 
+
+            # This indexes the tsvector column
+            db_manager._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.
+            db_manager._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
+
+
+
 if __name__ == "__main__":
     #import sys;sys.argv = ['', 'Test.testName']

mauRepo/MolesManager/trunk/src/libs/migration/tests/test_utils.py

@@ -7 +7 @@
 from libs.migration.processor.commons import createMD_Identifier,\
     createCI_Citation
+from ea_model.ceda_metadatamodel.ceda_observation.ceda_observation import CEDA_Observation
 
 def createObservationCollection():
@@ -19 +20 @@
     observationCollection.identifier.append(i_identifier)      
     return observationCollection
+
+def createObservation():
+    '''
+        Creates a CEDA_Observation with the following data:
+        oc.identifier[0].code = 'test_code'        
+        oc.identifier[0].authority.title = 'test_title'
+        oc.identifier[1].code = 'mau_code'        
+        oc.identifier[1].authority.title = 'mau_title'        
+    '''
+    observation = CEDA_Observation()
+    i_identifier = createMD_Identifier(code = 'test_code', authority=createCI_Citation('test_title'))
+    observation.identifier.append(i_identifier)      
+    i_identifier = createMD_Identifier(code = 'mau_code', authority=createCI_Citation('mau_title'))
+    observation.identifier.append(i_identifier)  
+    observation.dataLineage = "test_dataLineage"        
+    return observation