+"""
+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
#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.
"""
- 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()
git://git.onelab.eu / sfa.git / blobdiff