2 File defining classes to handle the table in the iotlab dedicated database.
5 from sqlalchemy import create_engine
6 from sqlalchemy.orm import sessionmaker
7 # from sfa.util.config import Config
8 from sfa.util.sfalogging import logger
10 from sqlalchemy import Column, Integer, String
11 from sqlalchemy import Table, MetaData
12 from sqlalchemy.ext.declarative import declarative_base
14 # from sqlalchemy.dialects import postgresql
16 from sqlalchemy.exc import NoSuchTableError
19 #Dict holding the columns names of the table as keys
20 #and their type, used for creation of the table
21 slice_table = {'record_id_user': 'integer PRIMARY KEY references X ON DELETE \
22 CASCADE ON UPDATE CASCADE', 'oar_job_id': 'integer DEFAULT -1',
23 'record_id_slice': 'integer', 'slice_hrn': 'text NOT NULL'}
25 #Dict with all the specific iotlab tables
26 tablenames_dict = {'iotlab_xp': slice_table}
29 IotlabBase = declarative_base()
32 class IotlabXP (IotlabBase):
33 """ SQL alchemy class to manipulate the rows of the slice_iotlab table in
34 iotlab_sfa database. Handles the records representation and creates the
35 table if it does not exist yet.
38 __tablename__ = 'iotlab_xp'
40 slice_hrn = Column(String)
41 job_id = Column(Integer, primary_key=True)
42 end_time = Column(Integer, nullable=False)
44 def __init__(self, slice_hrn=None, job_id=None, end_time=None):
46 Defines a row of the slice_iotlab table
49 self.slice_hrn = slice_hrn
53 self.end_time = end_time
56 """Prints the SQLAlchemy record to the format defined
59 result = "<iotlab_xp : slice_hrn = %s , job_id %s end_time = %s" \
60 % (self.slice_hrn, self.job_id, self.end_time)
65 class IotlabDB(object):
66 """ SQL Alchemy connection class.
69 # Stores the unique Singleton instance-
70 _connection_singleton = None
71 # defines the database name
76 Class used with this Python singleton design pattern to allow the
77 definition of one single instance of iotlab db session in the whole
78 code. Wherever a connection to the database is needed, this class
79 returns the same instance every time. Removes the need for global
80 variable throughout the code.
83 def __init__(self, config, debug=False):
84 self.iotlab_engine = None
85 self.iotlab_session = None
87 self.create_iotlab_engine(config, debug)
90 def create_iotlab_engine(self, config, debug=False):
91 """Creates the SQLAlchemy engine, which is the starting point for
92 any SQLAlchemy application.
93 :param config: configuration object created by SFA based on the
94 configuration file in /etc
95 :param debug: if set to true, echo and echo pool will be set to true
96 as well. If echo is True, all statements as well as a repr() of
97 their parameter lists to the engines logger, which defaults to
98 sys.stdout. If echo_pool is True, the connection pool will log all
99 checkouts/checkins to the logging stream. A python logger can be
100 used to configure this logging directly but so far it has not been
101 configured. Refer to sql alchemy engine documentation.
103 :type config: Config instance (sfa.util.config)
114 # the former PostgreSQL.py used the psycopg2 directly and was doing
115 #self.connection.set_client_encoding("UNICODE")
116 # it's unclear how to achieve this in sqlalchemy, nor if it's needed
118 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
119 # we indeed have /var/lib/pgsql/data/postgresql.conf where
120 # this setting is unset, it might be an angle to tweak that if need
121 # be try a unix socket first
122 # - omitting the hostname does the trick
123 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s" \
124 % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
125 config.SFA_DB_PORT, IotlabDB.dbname)
127 # the TCP fallback method
128 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s" \
129 % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
130 config.SFA_DB_HOST, config.SFA_DB_PORT, IotlabDB.dbname)
132 for url in [unix_url, tcp_url]:
134 self.iotlab_engine = create_engine(
135 url, echo_pool=l_echo_pool, echo=l_echo)
141 self.iotlab_engine = None
143 raise Exception("Could not connect to database")
146 """ Check if a table exists by trying a selection
150 self.iotlab_engine.execute("select 1").scalar()
155 Creates a SQLalchemy session. Once the session object is created
156 it should be used throughout the code for all the operations on
157 tables for this given database.
160 if self.iotlab_session is None:
161 Session = sessionmaker()
162 self.iotlab_session = Session(bind=self.iotlab_engine)
163 return self.iotlab_session
165 def close_session(self):
167 Closes connection to database.
170 if self.iotlab_session is None:
172 self.iotlab_session.close()
173 self.iotlab_session = None
176 def update_jobs_in_iotlabdb(self, job_oar_list, jobs_psql):
177 """ Cleans the iotlab db by deleting expired and cancelled jobs.
179 Compares the list of job ids given by OAR with the job ids that
180 are already in the database, deletes the jobs that are no longer in
183 :param job_oar_list: list of job ids coming from OAR
184 :type job_oar_list: list
185 :param job_psql: list of job ids from the database.
190 #Turn the list into a set
191 set_jobs_psql = set(jobs_psql)
193 kept_jobs = set(job_oar_list).intersection(set_jobs_psql)
194 logger.debug("\r\n \t update_jobs_in_iotlabdb jobs_psql %s \r\n \
195 job_oar_list %s kept_jobs %s "
196 % (set_jobs_psql, job_oar_list, kept_jobs))
197 deleted_jobs = set_jobs_psql.difference(kept_jobs)
198 deleted_jobs = list(deleted_jobs)
199 if len(deleted_jobs) > 0:
200 self.iotlab_session.query(IotlabXP).filter(IotlabXP.job_id.in_(deleted_jobs)).delete(synchronize_session='fetch')
201 self.iotlab_session.commit()
204 def __init__(self, config, debug=False):
205 self.sl_base = IotlabBase
207 # Check whether we already have an instance
208 if IotlabDB._connection_singleton is None:
209 IotlabDB._connection_singleton = IotlabDB.Singleton(config, debug)
211 # Store instance reference as the only member in the handle
212 self._EventHandler_singleton = IotlabDB._connection_singleton
214 def __getattr__(self, aAttr):
216 Delegate access to implementation.
218 :param aAttr: Attribute wanted.
221 return getattr(self._connection_singleton, aAttr)
225 # def __setattr__(self, aAttr, aValue):
226 # """Delegate access to implementation.
228 # :param attr: Attribute wanted.
229 # :param value: Vaule to be set.
230 # :return: Result of operation.
232 # return setattr(self._connection_singleton, aAttr, aValue)
234 def exists(self, tablename):
236 Checks if the table specified as tablename exists.
237 :param tablename: name of the table in the db that has to be checked.
238 :type tablename: string
239 :returns: True if the table exists, False otherwise.
243 metadata = MetaData(bind=self.iotlab_engine)
245 table = Table(tablename, metadata, autoload=True)
248 except NoSuchTableError:
249 logger.log_exc("IOTLABPOSTGRES tablename %s does not exist"
253 def createtable(self):
255 Creates all the table sof the engine.
256 Uses the global dictionnary holding the tablenames and the table schema.
260 logger.debug("IOTLABPOSTGRES createtable \
261 IotlabBase.metadata.sorted_tables %s \r\n engine %s"
262 % (IotlabBase.metadata.sorted_tables, self.iotlab_engine))
263 IotlabBase.metadata.create_all(self.iotlab_engine)