1 from sqlalchemy import create_engine
2 from sqlalchemy.orm import sessionmaker
3 from sfa.util.config import Config
4 from sfa.util.sfalogging import logger
6 from sqlalchemy import Column, Integer, String
7 from sqlalchemy import Table, MetaData
8 from sqlalchemy.ext.declarative import declarative_base
10 from sqlalchemy.dialects import postgresql
12 from sqlalchemy.exc import NoSuchTableError
15 #Dict holding the columns names of the table as keys
16 #and their type, used for creation of the table
17 slice_table = {'record_id_user': 'integer PRIMARY KEY references X ON DELETE \
18 CASCADE ON UPDATE CASCADE','oar_job_id':'integer DEFAULT -1', \
19 'record_id_slice':'integer', 'slice_hrn':'text NOT NULL'}
21 #Dict with all the specific iotlab tables
22 tablenames_dict = {'iotlab_xp': slice_table}
25 IotlabBase = declarative_base()
29 class IotlabXP (IotlabBase):
30 """ SQL alchemy class to manipulate slice_iotlab table in
34 __tablename__ = 'iotlab_xp'
37 slice_hrn = Column(String)
38 job_id = Column(Integer, primary_key = True)
39 end_time = Column(Integer, nullable = False)
42 #oar_job_id = Column( Integer,default = -1)
43 #node_list = Column(postgresql.ARRAY(String), nullable =True)
45 def __init__ (self, slice_hrn =None, job_id=None, end_time=None):
47 Defines a row of the slice_iotlab table
50 self.slice_hrn = slice_hrn
54 self.end_time = end_time
58 """Prints the SQLAlchemy record to the format defined
61 result = "<iotlab_xp : slice_hrn = %s , job_id %s end_time = %s" \
62 %(self.slice_hrn, self.job_id, self.end_time)
68 class IotlabDB(object):
69 """ SQL Alchemy connection class.
72 # Stores the unique Singleton instance-
73 _connection_singleton = None
74 _dbname = "iotlab_sfa"
79 Class used with this Python singleton design pattern
80 @todo Add all variables, and methods needed for the
84 def __init__(self, config, debug = False):
85 self.iotlab_engine = None
86 self.iotlab_session = None
87 self.create_engine(config, debug)
90 def create_engine(self, config, debug = False):
99 # the former PostgreSQL.py used the psycopg2 directly and was doing
100 #self.connection.set_client_encoding("UNICODE")
101 # it's unclear how to achieve this in sqlalchemy, nor if it's needed
103 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
104 # we indeed have /var/lib/pgsql/data/postgresql.conf where
105 # this setting is unset, it might be an angle to tweak that if need
106 # be try a unix socket first
107 # - omitting the hostname does the trick
108 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"% \
109 (config.SFA_DB_USER, config.SFA_DB_PASSWORD, \
110 config.SFA_DB_PORT, IotlabDB._dbname)
112 # the TCP fallback method
113 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"% \
114 (config.SFA_DB_USER, config.SFA_DB_PASSWORD, config.SFA_DB_HOST, \
115 config.SFA_DB_PORT, IotlabDB._dbname)
117 for url in [ unix_url, tcp_url ] :
119 self.iotlab_engine = create_engine (url, echo_pool =
120 l_echo_pool, echo = l_echo)
126 self.iotlab_engine = None
129 raise Exception, "Could not connect to database"
132 """ Check if a table exists by trying a selection
136 self.iotlab_engine.execute ("select 1").scalar()
141 Creates a SQLalchemy session. Once the session object is created
142 it should be used throughout the code for all the operations on
143 tables for this given database.
146 if self.iotlab_session is None:
147 Session = sessionmaker()
148 self.iotlab_session = Session(bind = self.iotlab_engine)
149 return self.iotlab_session
151 def close_session(self):
153 Closes connection to database.
156 if self.iotlab_session is None:
158 self.iotlab_session.close()
159 self.iotlab_session = None
161 def update_jobs_in_iotlabdb( job_oar_list, jobs_psql):
162 """ Cleans the iotlab db by deleting expired and cancelled jobs.
163 Compares the list of job ids given by OAR with the job ids that
164 are already in the database, deletes the jobs that are no longer in
166 :param job_oar_list: list of job ids coming from OAR
167 :type job_oar_list: list
168 :param job_psql: list of job ids cfrom the database.
171 #Turn the list into a set
172 set_jobs_psql = set(jobs_psql)
174 kept_jobs = set(job_oar_list).intersection(set_jobs_psql)
175 logger.debug ( "\r\n \t\ update_jobs_in_iotlabdb jobs_psql %s \r\n \t \
176 job_oar_list %s kept_jobs %s "%(set_jobs_psql, job_oar_list, kept_jobs))
177 deleted_jobs = set_jobs_psql.difference(kept_jobs)
178 deleted_jobs = list(deleted_jobs)
179 if len(deleted_jobs) > 0:
180 self.iotlab_session.query(IotlabXP).filter(IotlabXP.job_id.in_(deleted_jobs)).delete(synchronize_session='fetch')
181 self.iotlab_session.commit()
187 def __init__(self, config, debug = False):
188 self.sl_base = IotlabBase
190 # Check whether we already have an instance
191 if IotlabDB._connection_singleton is None:
192 IotlabDB._connection_singleton = IotlabDB.Singleton(config, debug)
194 # Store instance reference as the only member in the handle
195 self._EventHandler_singleton = IotlabDB._connection_singleton
199 def __getattr__(self, aAttr):
201 Delegate access to implementation.
203 :param attr: Attribute wanted.
206 return getattr(self._connection_singleton, aAttr)
210 # def __setattr__(self, aAttr, aValue):
211 # """Delegate access to implementation.
213 # :param attr: Attribute wanted.
214 # :param value: Vaule to be set.
215 # :return: Result of operation.
217 # return setattr(self._connection_singleton, aAttr, aValue)
223 def exists(self, tablename):
225 Checks if the table specified as tablename exists.
230 metadata = MetaData (bind=self.iotlab_engine)
231 table = Table (tablename, metadata, autoload=True)
234 except NoSuchTableError:
235 logger.log_exc("SLABPOSTGRES tablename %s does not exists" \
240 def createtable(self):
242 Creates all the table sof the engine.
243 Uses the global dictionnary holding the tablenames and the table schema.
247 logger.debug("SLABPOSTGRES createtable IotlabBase.metadata.sorted_tables \
248 %s \r\n engine %s" %(IotlabBase.metadata.sorted_tables , self.iotlab_engine))
249 IotlabBase.metadata.create_all(self.iotlab_engine)
254 # iotlab_alchemy = IotlabDB(Config())
255 # iotlab_engine = iotlab_alchemy.iotlab_engine
256 # iotlab_dbsession = iotlab_alchemy.session()