X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=sfa%2Fiotlab%2Fiotlabpostgres.py;h=eed8769a4ef53a189510708e365dda5e2a0ac148;hb=0cc524b0816203ed36589fa68337f9291fad0b7b;hp=4e3c6df425ac468b57b48fed0d1afedbf06b6d92;hpb=b0995f45712c5ea9dd26fdf0c4347ccba2d77fce;p=sfa.git

diff --git a/sfa/iotlab/iotlabpostgres.py b/sfa/iotlab/iotlabpostgres.py
index 4e3c6df4..eed8769a 100644
--- a/sfa/iotlab/iotlabpostgres.py
+++ b/sfa/iotlab/iotlabpostgres.py
@@ -1,14 +1,17 @@
+"""
+File defining classes to handle the table in the iotlab dedicated database.
+"""
+
 from sqlalchemy import create_engine
 from sqlalchemy.orm import sessionmaker
-
-from sfa.util.config import Config
+# from sfa.util.config import Config
 from sfa.util.sfalogging import logger
 
 from sqlalchemy import Column, Integer, String
 from sqlalchemy import Table, MetaData
 from sqlalchemy.ext.declarative import declarative_base
 
-from sqlalchemy.dialects import postgresql
+# from sqlalchemy.dialects import postgresql
 
 from sqlalchemy.exc import NoSuchTableError
 
@@ -16,148 +19,246 @@ from sqlalchemy.exc import NoSuchTableError
 #Dict holding the columns names of the table as keys
 #and their type, used for creation of the table
 slice_table = {'record_id_user': 'integer PRIMARY KEY references X ON DELETE \
-CASCADE ON UPDATE CASCADE','oar_job_id':'integer DEFAULT -1',  \
-'record_id_slice':'integer', 'slice_hrn':'text NOT NULL'}
+                CASCADE ON UPDATE CASCADE', 'oar_job_id': 'integer DEFAULT -1',
+               'record_id_slice': 'integer', 'slice_hrn': 'text NOT NULL'}
 
 #Dict with all the specific iotlab tables
-tablenames_dict = {'iotlab_xp': slice_table}
-
+# tablenames_dict = {'lease_table': slice_table}
 
-IotlabBase = declarative_base()
 
+TestbedBase = declarative_base()
 
 
-class IotlabXP (IotlabBase):
-    """ SQL alchemy class to manipulate slice_iotlab table in
-    iotlab_sfa database.
+class LeaseTableXP (TestbedBase):
+    """ SQL alchemy class to manipulate the rows of the slice_iotlab table in
+    lease_table database. Handles the records representation and creates the
+    table if it does not exist yet.
 
     """
-    __tablename__ = 'iotlab_xp'
-
+    __tablename__ = 'lease_table'
 
     slice_hrn = Column(String)
-    job_id = Column(Integer, primary_key = True)
-    end_time = Column(Integer, nullable = False)
-
-
-    #oar_job_id = Column( Integer,default = -1)
-    #node_list = Column(postgresql.ARRAY(String), nullable =True)
+    experiment_id = Column(Integer, primary_key=True)
+    end_time = Column(Integer, nullable=False)
 
-    def __init__ (self, slice_hrn =None, job_id=None,  end_time=None):
+    def __init__(self, slice_hrn=None, experiment_id=None,  end_time=None):
         """
         Defines a row of the slice_iotlab table
         """
         if slice_hrn:
             self.slice_hrn = slice_hrn
-        if job_id :
-            self.job_id = job_id
+        if experiment_id:
+            self.experiment_id = experiment_id
         if end_time:
             self.end_time = end_time
 
-
     def __repr__(self):
         """Prints the SQLAlchemy record to the format defined
         by the function.
         """
-        result = "<iotlab_xp : slice_hrn = %s , job_id %s end_time = %s" \
-            %(self.slice_hrn, self.job_id, self.end_time)
+        result = "<lease_table : slice_hrn = %s , experiment_id %s end_time = %s" \
+            % (self.slice_hrn, self.experiment_id, self.end_time)
         result += ">"
         return result
 
 
-
-class IotlabDB:
+class TestbedAdditionalSfaDB(object):
     """ SQL Alchemy connection class.
     From alchemy.py
     """
-    def __init__(self, config, debug = False):
-        self.sl_base = IotlabBase
-        dbname = "iotlab_sfa"
-        if debug == True :
-            l_echo_pool = True
-            l_echo = True
-        else :
-            l_echo_pool = False
-            l_echo = False
-
-        self.iotlab_session = None
-        # the former PostgreSQL.py used the psycopg2 directly and was doing
-        #self.connection.set_client_encoding("UNICODE")
-        # it's unclear how to achieve this in sqlalchemy, nor if it's needed
-        # at all
-        # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
-        # we indeed have /var/lib/pgsql/data/postgresql.conf where
-        # this setting is unset, it might be an angle to tweak that if need be
-        # try a unix socket first - omitting the hostname does the trick
-        unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"% \
-            (config.SFA_DB_USER, config.SFA_DB_PASSWORD, \
-                                    config.SFA_DB_PORT, dbname)
-
-        # the TCP fallback method
-        tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"% \
-            (config.SFA_DB_USER, config.SFA_DB_PASSWORD, config.SFA_DB_HOST, \
-                                    config.SFA_DB_PORT, dbname)
-        for url in [ unix_url, tcp_url ] :
-            try:
-                self.iotlab_engine = create_engine (url, echo_pool = \
-                                            l_echo_pool, echo = l_echo)
-                self.check()
-                self.url = url
-                return
-            except:
-                pass
-        self.iotlab_engine = None
-        raise Exception, "Could not connect to database"
-
-
-
-    def check (self):
-        """ Cehck if a table exists by trying a selection
-        on the table.
+    # Stores the unique Singleton instance-
+    _connection_singleton = None
+    # defines the database name
+    dbname = "testbed_xp"
 
+    class Singleton:
         """
-        self.iotlab_engine.execute ("select 1").scalar()
-
-
-    def session (self):
+        Class used with this Python singleton design pattern to allow the
+        definition of one single instance of iotlab db session in the whole
+        code. Wherever a conenction to the database is needed, this class
+        returns the same instance every time. Removes the need for global
+        variable throughout the code.
         """
-        Creates a SQLalchemy session. Once the session object is created
-        it should be used throughout the code for all the operations on
-        tables for this given database.
 
+        def __init__(self, config, debug=False):
+            self.testbed_engine = None
+            self.testbed_session = None
+            self.url = None
+            self.create_testbed_engine(config, debug)
+            self.session()
+
+        def create_testbed_engine(self, config, debug=False):
+            """Creates the SQLAlchemy engine, which is the starting point for
+            any SQLAlchemy application.
+            :param config: configuration object created by SFA based on the
+            configuration file in /etc
+            :param debug: if set to true, echo and echo pool will be set to true
+            as well. If echo is True, all statements as well as a repr() of
+            their parameter lists to the engines logger, which defaults to
+            sys.stdout. If echo_pool is True, the connection pool will log all
+            checkouts/checkins to the logging stream. A python logger can be
+            used to configure this logging directly but so far it has not been
+            configured. Refer to sql alchemy engine documentation.
+
+            :type config: Config instance (sfa.util.config)
+            :type debug: bool
+
+            """
+
+            if debug is True:
+                l_echo_pool = True
+                l_echo = True
+            else:
+                l_echo_pool = False
+                l_echo = False
+             # the former PostgreSQL.py used the psycopg2 directly and was doing
+            #self.connection.set_client_encoding("UNICODE")
+            # it's unclear how to achieve this in sqlalchemy, nor if it's needed
+            # at all
+            # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
+            # we indeed have /var/lib/pgsql/data/postgresql.conf where
+            # this setting is unset, it might be an angle to tweak that if need
+            # be try a unix socket first
+            #  - omitting the hostname does the trick
+            unix_url = "postgresql+psycopg2://%s:%s@:%s/%s" \
+                % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
+                   config.SFA_DB_PORT, TestbedAdditionalSfaDB.dbname)
+
+            # the TCP fallback method
+            tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s" \
+                % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
+                    config.SFA_DB_HOST, config.SFA_DB_PORT, TestbedAdditionalSfaDB.dbname)
+
+            for url in [unix_url, tcp_url]:
+                try:
+                    self.testbed_engine = create_engine(
+                        url, echo_pool=l_echo_pool, echo=l_echo)
+                    self.check()
+                    self.url = url
+                    return
+                except:
+                    pass
+                self.testbed_engine = None
+
+            raise Exception("Could not connect to database")
+
+        def check(self):
+            """ Check if a table exists by trying a selection
+            on the table.
+
+            """
+            self.testbed_engine.execute("select 1").scalar()
+
+
+        def session(self):
+            """
+            Creates a SQLalchemy session. Once the session object is created
+            it should be used throughout the code for all the operations on
+            tables for this given database.
+
+            """
+            if self.testbed_session is None:
+                Session = sessionmaker()
+                self.testbed_session = Session(bind=self.testbed_engine)
+            return self.testbed_session
+
+        def close_session(self):
+            """
+            Closes connection to database.
+
+            """
+            if self.testbed_session is None:
+                return
+            self.testbed_session.close()
+            self.testbed_session = None
+
+
+        def update_experiments_in_additional_sfa_db(self,
+            experiment_list_from_testbed, experiment_list_in_db):
+            """ Cleans the iotlab db by deleting expired and cancelled jobs.
+
+            Compares the list of experiment ids given by the testbed with the
+            experiment ids that are already in the database, deletes the
+            experiments that are no longer in the testbed experiment id list.
+
+            :param  experiment_list_from_testbed: list of experiment ids coming
+                from testbed
+            :type experiment_list_from_testbed: list
+            :param experiment_list_in_db: list of experiment ids from the sfa
+                additionnal database.
+            :type experiment_list_in_db: list
+
+            :returns: None
+            """
+            #Turn the list into a set
+            set_experiment_list_in_db = set(experiment_list_in_db)
+
+            kept_experiments = set(experiment_list_from_testbed).intersection(set_experiment_list_in_db)
+            logger.debug("\r\n \t update_experiments_in_additional_sfa_db \
+                            experiment_list_in_db %s \r\n \
+                            experiment_list_from_testbed %s \
+                            kept_experiments %s "
+                         % (set_experiment_list_in_db,
+                          experiment_list_from_testbed, kept_experiments))
+            deleted_experiments = set_experiment_list_in_db.difference(
+                kept_experiments)
+            deleted_experiments = list(deleted_experiments)
+            if len(deleted_experiments) > 0:
+                self.testbed_session.query(LeaseTableXP).filter(LeaseTableXP.experiment_id.in_(deleted_experiments)).delete(synchronize_session='fetch')
+                self.testbed_session.commit()
+            return
+
+    def __init__(self, config, debug=False):
+        self.sl_base = TestbedBase
+
+         # Check whether we already have an instance
+        if TestbedAdditionalSfaDB._connection_singleton is None:
+            TestbedAdditionalSfaDB._connection_singleton = \
+                TestbedAdditionalSfaDB.Singleton(config, debug)
+
+        # Store instance reference as the only member in the handle
+        self._EventHandler_singleton = \
+            TestbedAdditionalSfaDB._connection_singleton
+
+    def __getattr__(self, aAttr):
         """
-        if self.iotlab_session is None:
-            Session = sessionmaker()
-            self.iotlab_session = Session(bind = self.iotlab_engine)
-        return self.iotlab_session
+        Delegate access to implementation.
 
-    def close_session(self):
+        :param aAttr: Attribute wanted.
+        :returns: Attribute
         """
-        Closes connection to database.
+        return getattr(self._connection_singleton, aAttr)
 
-        """
-        if self.iotlab_session is None: return
-        self.iotlab_session.close()
-        self.iotlab_session = None
 
 
+    # def __setattr__(self, aAttr, aValue):
+    #     """Delegate access to implementation.
+
+    #      :param attr: Attribute wanted.
+    #      :param value: Vaule to be set.
+    #      :return: Result of operation.
+    #      """
+    #     return setattr(self._connection_singleton, aAttr, aValue)
+
     def exists(self, tablename):
         """
         Checks if the table specified as tablename exists.
+        :param tablename: name of the table in the db that has to be checked.
+        :type tablename: string
+        :returns: True if the table exists, False otherwise.
+        :rtype: bool
 
         """
-
+        metadata = MetaData(bind=self.testbed_engine)
         try:
-            metadata = MetaData (bind=self.iotlab_engine)
-            table = Table (tablename, metadata, autoload=True)
+            table = Table(tablename, metadata, autoload=True)
             return True
 
         except NoSuchTableError:
-            logger.log_exc("SLABPOSTGRES tablename %s does not exists" \
-                            %(tablename))
+            logger.log_exc("SLABPOSTGRES tablename %s does not exist"
+                           % (tablename))
             return False
 
-
     def createtable(self):
         """
         Creates all the table sof the engine.
@@ -165,13 +266,8 @@ class IotlabDB:
 
         """
 
-        logger.debug("SLABPOSTGRES createtable IotlabBase.metadata.sorted_tables \
-            %s \r\n engine %s" %(IotlabBase.metadata.sorted_tables , iotlab_engine))
-        IotlabBase.metadata.create_all(iotlab_engine)
+        logger.debug("IOTLABPOSTGRES createtable \
+                    TestbedBase.metadata.sorted_tables %s \r\n engine %s"
+                     % (TestbedBase.metadata.sorted_tables, self.testbed_engine))
+        TestbedBase.metadata.create_all(self.testbed_engine)
         return
-
-
-
-iotlab_alchemy = IotlabDB(Config())
-iotlab_engine = iotlab_alchemy.iotlab_engine
-iotlab_dbsession = iotlab_alchemy.session()