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
9 from sfa.storage.model import Base, AlchemyObj
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 # class LeaseTableXP (Base,AlchemyObj):
34 class LeaseTableXP (Base):
35 """ SQL alchemy class to manipulate the rows of the slice_iotlab table in
36 lease_table database. Handles the records representation and creates the
37 table if it does not exist yet.
40 __tablename__ = 'lease_table'
42 slice_hrn = Column(String)
43 experiment_id = Column(Integer, primary_key=True)
44 end_time = Column(Integer, nullable=False)
46 def __init__(self, slice_hrn=None, experiment_id=None, end_time=None):
48 Defines a row of the slice_iotlab table
51 self.slice_hrn = slice_hrn
53 self.experiment_id = experiment_id
55 self.end_time = end_time
58 """Prints the SQLAlchemy record to the format defined
61 result = "<lease_table : slice_hrn = %s , experiment_id %s end_time = %s" \
62 % (self.slice_hrn, self.experiment_id, self.end_time)
67 # class TestbedAdditionalSfaDB(object):
68 # """ SQL Alchemy connection class.
71 # # Stores the unique Singleton instance-
72 # _connection_singleton = None
73 # # defines the database name
74 # dbname = "testbed_xp"
78 # Class used with this Python singleton design pattern to allow the
79 # definition of one single instance of iotlab db session in the whole
80 # code. Wherever a conenction to the database is needed, this class
81 # returns the same instance every time. Removes the need for global
82 # variable throughout the code.
85 # def __init__(self, config, debug=False):
86 # self.testbed_engine = None
87 # self.testbed_session = None
89 # self.create_testbed_engine(config, debug)
92 # def create_testbed_engine(self, config, debug=False):
93 # """Creates the SQLAlchemy engine, which is the starting point for
94 # any SQLAlchemy application.
95 # :param config: configuration object created by SFA based on the
96 # configuration file in /etc
97 # :param debug: if set to true, echo and echo pool will be set to true
98 # as well. If echo is True, all statements as well as a repr() of
99 # their parameter lists to the engines logger, which defaults to
100 # sys.stdout. If echo_pool is True, the connection pool will log all
101 # checkouts/checkins to the logging stream. A python logger can be
102 # used to configure this logging directly but so far it has not been
103 # configured. Refer to sql alchemy engine documentation.
105 # :type config: Config instance (sfa.util.config)
114 # l_echo_pool = False
116 # # the former PostgreSQL.py used the psycopg2 directly and was doing
117 # #self.connection.set_client_encoding("UNICODE")
118 # # it's unclear how to achieve this in sqlalchemy, nor if it's needed
120 # # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
121 # # we indeed have /var/lib/pgsql/data/postgresql.conf where
122 # # this setting is unset, it might be an angle to tweak that if need
123 # # be try a unix socket first
124 # # - omitting the hostname does the trick
125 # unix_url = "postgresql+psycopg2://%s:%s@:%s/%s" \
126 # % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
127 # config.SFA_DB_PORT, TestbedAdditionalSfaDB.dbname)
129 # # the TCP fallback method
130 # tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s" \
131 # % (config.SFA_DB_USER, config.SFA_DB_PASSWORD,
132 # config.SFA_DB_HOST, config.SFA_DB_PORT, TestbedAdditionalSfaDB.dbname)
134 # for url in [unix_url, tcp_url]:
136 # self.testbed_engine = create_engine(
137 # url, echo_pool=l_echo_pool, echo=l_echo)
143 # self.testbed_engine = None
145 # raise Exception("Could not connect to database")
148 # """ Check if a table exists by trying a selection
152 # self.testbed_engine.execute("select 1").scalar()
157 # Creates a SQLalchemy session. Once the session object is created
158 # it should be used throughout the code for all the operations on
159 # tables for this given database.
162 # if self.testbed_session is None:
163 # Session = sessionmaker()
164 # self.testbed_session = Session(bind=self.testbed_engine)
165 # return self.testbed_session
167 # def close_session(self):
169 # Closes connection to database.
172 # if self.testbed_session is None:
174 # self.testbed_session.close()
175 # self.testbed_session = None
178 # def update_experiments_in_additional_sfa_db(self,
179 # experiment_list_from_testbed, experiment_list_in_db):
180 # """ Cleans the iotlab db by deleting expired and cancelled jobs.
182 # Compares the list of experiment ids given by the testbed with the
183 # experiment ids that are already in the database, deletes the
184 # experiments that are no longer in the testbed experiment id list.
186 # :param experiment_list_from_testbed: list of experiment ids coming
188 # :type experiment_list_from_testbed: list
189 # :param experiment_list_in_db: list of experiment ids from the sfa
190 # additionnal database.
191 # :type experiment_list_in_db: list
195 # #Turn the list into a set
196 # set_experiment_list_in_db = set(experiment_list_in_db)
198 # kept_experiments = set(experiment_list_from_testbed).intersection(set_experiment_list_in_db)
199 # logger.debug("\r\n \t update_experiments_in_additional_sfa_db \
200 # experiment_list_in_db %s \r\n \
201 # experiment_list_from_testbed %s \
202 # kept_experiments %s "
203 # % (set_experiment_list_in_db,
204 # experiment_list_from_testbed, kept_experiments))
205 # deleted_experiments = set_experiment_list_in_db.difference(
207 # deleted_experiments = list(deleted_experiments)
208 # if len(deleted_experiments) > 0:
209 # self.testbed_session.query(LeaseTableXP).filter(LeaseTableXP.experiment_id.in_(deleted_experiments)).delete(synchronize_session='fetch')
210 # self.testbed_session.commit()
213 # def __init__(self, config, debug=False):
214 # self.sl_base = TestbedBase
216 # # Check whether we already have an instance
217 # if TestbedAdditionalSfaDB._connection_singleton is None:
218 # TestbedAdditionalSfaDB._connection_singleton = \
219 # TestbedAdditionalSfaDB.Singleton(config, debug)
221 # # Store instance reference as the only member in the handle
222 # self._EventHandler_singleton = \
223 # TestbedAdditionalSfaDB._connection_singleton
225 # def __getattr__(self, aAttr):
227 # Delegate access to implementation.
229 # :param aAttr: Attribute wanted.
230 # :returns: Attribute
232 # return getattr(self._connection_singleton, aAttr)
236 # # def __setattr__(self, aAttr, aValue):
237 # # """Delegate access to implementation.
239 # # :param attr: Attribute wanted.
240 # # :param value: Vaule to be set.
241 # # :return: Result of operation.
243 # # return setattr(self._connection_singleton, aAttr, aValue)
245 # def exists(self, tablename):
247 # Checks if the table specified as tablename exists.
248 # :param tablename: name of the table in the db that has to be checked.
249 # :type tablename: string
250 # :returns: True if the table exists, False otherwise.
254 # metadata = MetaData(bind=self.testbed_engine)
256 # table = Table(tablename, metadata, autoload=True)
259 # except NoSuchTableError:
260 # logger.log_exc("SLABPOSTGRES tablename %s does not exist"
264 # def createtable(self):
266 # Creates all the table sof the engine.
267 # Uses the global dictionnary holding the tablenames and the table schema.
271 # logger.debug("IOTLABPOSTGRES createtable \
272 # TestbedBase.metadata.sorted_tables %s \r\n engine %s"
273 # % (TestbedBase.metadata.sorted_tables, self.testbed_engine))
274 # TestbedBase.metadata.create_all(self.testbed_engine)