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 = {'lease_table': slice_table}
29 TestbedBase = declarative_base()
32 class LeaseTableXP (TestbedBase):
33 """ SQL alchemy class to manipulate the rows of the slice_iotlab table in
34 lease_table database. Handles the records representation and creates the
35 table if it does not exist yet.
38 __tablename__ = 'lease_table'
40 slice_hrn = Column(String)
41 experiment_id = Column(Integer, primary_key=True)
42 end_time = Column(Integer, nullable=False)
44 def __init__(self, slice_hrn=None, experiment_id=None, end_time=None):
46 Defines a row of the slice_iotlab table
49 self.slice_hrn = slice_hrn
51 self.experiment_id = experiment_id
53 self.end_time = end_time
56 """Prints the SQLAlchemy record to the format defined
59 result = "<lease_table : slice_hrn = %s , experiment_id %s end_time = %s" \
60 % (self.slice_hrn, self.experiment_id, self.end_time)
65 class TestbedAdditionalSfaDB(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.testbed_engine = None
85 self.testbed_session = None
87 self.create_testbed_engine(config, debug)
90 def create_testbed_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, TestbedAdditionalSfaDB.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, TestbedAdditionalSfaDB.dbname)
132 for url in [unix_url, tcp_url]:
134 self.testbed_engine = create_engine(
135 url, echo_pool=l_echo_pool, echo=l_echo)
141 self.testbed_engine = None
143 raise Exception("Could not connect to database")
146 """ Check if a table exists by trying a selection
150 self.testbed_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.testbed_session is None:
161 Session = sessionmaker()
162 self.testbed_session = Session(bind=self.testbed_engine)
163 return self.testbed_session
165 def close_session(self):
167 Closes connection to database.
170 if self.testbed_session is None:
172 self.testbed_session.close()
173 self.testbed_session = None
176 def update_experiments_in_additional_sfa_db(self,
177 experiment_list_from_testbed, experiment_list_in_db):
178 """ Cleans the iotlab db by deleting expired and cancelled jobs.
180 Compares the list of experiment ids given by the testbed with the
181 experiment ids that are already in the database, deletes the
182 experiments that are no longer in the testbed experiment id list.
184 :param experiment_list_from_testbed: list of experiment ids coming
186 :type experiment_list_from_testbed: list
187 :param experiment_list_in_db: list of experiment ids from the sfa
188 additionnal database.
189 :type experiment_list_in_db: list
193 #Turn the list into a set
194 set_experiment_list_in_db = set(experiment_list_in_db)
196 kept_experiments = set(experiment_list_from_testbed).intersection(set_experiment_list_in_db)
197 logger.debug("\r\n \t update_experiments_in_additional_sfa_db \
198 experiment_list_in_db %s \r\n \
199 experiment_list_from_testbed %s \
200 kept_experiments %s "
201 % (set_experiment_list_in_db,
202 experiment_list_from_testbed, kept_experiments))
203 deleted_experiments = set_experiment_list_in_db.difference(
205 deleted_experiments = list(deleted_experiments)
206 if len(deleted_experiments) > 0:
207 self.testbed_session.query(LeaseTableXP).filter(LeaseTableXP.experiment_id.in_(deleted_experiments)).delete(synchronize_session='fetch')
208 self.testbed_session.commit()
211 def __init__(self, config, debug=False):
212 self.sl_base = TestbedBase
214 # Check whether we already have an instance
215 if TestbedAdditionalSfaDB._connection_singleton is None:
216 TestbedAdditionalSfaDB._connection_singleton = \
217 TestbedAdditionalSfaDB.Singleton(config, debug)
219 # Store instance reference as the only member in the handle
220 self._EventHandler_singleton = \
221 TestbedAdditionalSfaDB._connection_singleton
223 def __getattr__(self, aAttr):
225 Delegate access to implementation.
227 :param aAttr: Attribute wanted.
230 return getattr(self._connection_singleton, aAttr)
234 # def __setattr__(self, aAttr, aValue):
235 # """Delegate access to implementation.
237 # :param attr: Attribute wanted.
238 # :param value: Vaule to be set.
239 # :return: Result of operation.
241 # return setattr(self._connection_singleton, aAttr, aValue)
243 def exists(self, tablename):
245 Checks if the table specified as tablename exists.
246 :param tablename: name of the table in the db that has to be checked.
247 :type tablename: string
248 :returns: True if the table exists, False otherwise.
252 metadata = MetaData(bind=self.testbed_engine)
254 table = Table(tablename, metadata, autoload=True)
257 except NoSuchTableError:
258 logger.log_exc("SLABPOSTGRES tablename %s does not exist"
262 def createtable(self):
264 Creates all the table sof the engine.
265 Uses the global dictionnary holding the tablenames and the table schema.
269 logger.debug("IOTLABPOSTGRES createtable \
270 TestbedBase.metadata.sorted_tables %s \r\n engine %s"
271 % (TestbedBase.metadata.sorted_tables, self.testbed_engine))
272 TestbedBase.metadata.create_all(self.testbed_engine)