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 conenction 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
92 for 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()
97 of their parameter lists to the engines logger, which defaults
98 to sys.stdout. If echo_pool is True, the connection pool will
99 log all checkouts/checkins to the logging stream. A python
100 logger can be used to configure this logging directly but
101 so far it has not been configured. Refer to sql alchemy engine
103 :type config: Config instance (sfa.util.config)
113 # the former PostgreSQL.py used the psycopg2 directly and was doing
114 #self.connection.set_client_encoding("UNICODE")
115 # it's unclear how to achieve this in sqlalchemy, nor if it's needed
117 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
118 # we indeed have /var/lib/pgsql/data/postgresql.conf where
119 # this setting is unset, it might be an angle to tweak that if need
120 # be try a unix socket first
121 # - omitting the hostname does the trick
122 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s" \
123 % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
124 config.SFA_DB_PORT, IotlabDB.dbname)
126 # the TCP fallback method
127 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s" \
128 % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
129 config.SFA_DB_HOST, config.SFA_DB_PORT, IotlabDB.dbname)
131 for url in [unix_url, tcp_url]:
133 self.iotlab_engine = create_engine(
134 url, echo_pool=l_echo_pool, echo=l_echo)
140 self.iotlab_engine = None
142 raise Exception("Could not connect to database")
145 """ Check if a table exists by trying a selection
149 self.iotlab_engine.execute("select 1").scalar()
154 Creates a SQLalchemy session. Once the session object is created
155 it should be used throughout the code for all the operations on
156 tables for this given database.
159 if self.iotlab_session is None:
160 Session = sessionmaker()
161 self.iotlab_session = Session(bind=self.iotlab_engine)
162 return self.iotlab_session
164 def close_session(self):
166 Closes connection to database.
169 if self.iotlab_session is None:
171 self.iotlab_session.close()
172 self.iotlab_session = None
175 def update_jobs_in_iotlabdb(self, job_oar_list, jobs_psql):
176 """ Cleans the iotlab db by deleting expired and cancelled jobs.
178 Compares the list of job ids given by OAR with the job ids that
179 are already in the database, deletes the jobs that are no longer in
182 :param job_oar_list: list of job ids coming from OAR
183 :type job_oar_list: list
184 :param job_psql: list of job ids from the database.
188 #Turn the list into a set
189 set_jobs_psql = set(jobs_psql)
191 kept_jobs = set(job_oar_list).intersection(set_jobs_psql)
192 logger.debug("\r\n \t update_jobs_in_iotlabdb jobs_psql %s \r\n \
193 job_oar_list %s kept_jobs %s "
194 % (set_jobs_psql, job_oar_list, kept_jobs))
195 deleted_jobs = set_jobs_psql.difference(kept_jobs)
196 deleted_jobs = list(deleted_jobs)
197 if len(deleted_jobs) > 0:
198 self.iotlab_session.query(IotlabXP).filter(IotlabXP.job_id.in_(deleted_jobs)).delete(synchronize_session='fetch')
199 self.iotlab_session.commit()
202 def __init__(self, config, debug=False):
203 self.sl_base = IotlabBase
205 # Check whether we already have an instance
206 if IotlabDB._connection_singleton is None:
207 IotlabDB._connection_singleton = IotlabDB.Singleton(config, debug)
209 # Store instance reference as the only member in the handle
210 self._EventHandler_singleton = IotlabDB._connection_singleton
212 def __getattr__(self, aAttr):
214 Delegate access to implementation.
216 :param aAttr: Attribute wanted.
219 return getattr(self._connection_singleton, aAttr)
223 # def __setattr__(self, aAttr, aValue):
224 # """Delegate access to implementation.
226 # :param attr: Attribute wanted.
227 # :param value: Vaule to be set.
228 # :return: Result of operation.
230 # return setattr(self._connection_singleton, aAttr, aValue)
232 def exists(self, tablename):
234 Checks if the table specified as tablename exists.
235 :param tablename: name of the table in the db that has to be checked.
236 :type tablename: string
237 :returns: True if the table exists, False otherwise.
241 metadata = MetaData(bind=self.iotlab_engine)
243 table = Table(tablename, metadata, autoload=True)
246 except NoSuchTableError:
247 logger.log_exc("SLABPOSTGRES tablename %s does not exist"
251 def createtable(self):
253 Creates all the table sof the engine.
254 Uses the global dictionnary holding the tablenames and the table schema.
258 logger.debug("SLABPOSTGRES createtable \
259 IotlabBase.metadata.sorted_tables %s \r\n engine %s"
260 % (IotlabBase.metadata.sorted_tables, self.iotlab_engine))
261 IotlabBase.metadata.create_all(self.iotlab_engine)