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
82 def __init__(self, config, debug = False):
83 self.iotlab_engine = None
84 self.iotlab_session = None
85 self.create_iotlab_engine(config, debug)
88 def create_iotlab_engine(self, config, debug = False):
97 # the former PostgreSQL.py used the psycopg2 directly and was doing
98 #self.connection.set_client_encoding("UNICODE")
99 # it's unclear how to achieve this in sqlalchemy, nor if it's needed
101 # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
102 # we indeed have /var/lib/pgsql/data/postgresql.conf where
103 # this setting is unset, it might be an angle to tweak that if need
104 # be try a unix socket first
105 # - omitting the hostname does the trick
106 unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"% \
107 (config.SFA_DB_USER, config.SFA_DB_PASSWORD, \
108 config.SFA_DB_PORT, IotlabDB._dbname)
110 # the TCP fallback method
111 tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"% \
112 (config.SFA_DB_USER, config.SFA_DB_PASSWORD, config.SFA_DB_HOST, \
113 config.SFA_DB_PORT, IotlabDB._dbname)
115 for url in [ unix_url, tcp_url ] :
117 self.iotlab_engine = create_engine (url, echo_pool =
118 l_echo_pool, echo = l_echo)
124 self.iotlab_engine = None
127 raise Exception, "Could not connect to database"
130 """ Check if a table exists by trying a selection
134 self.iotlab_engine.execute ("select 1").scalar()
139 Creates a SQLalchemy session. Once the session object is created
140 it should be used throughout the code for all the operations on
141 tables for this given database.
144 if self.iotlab_session is None:
145 Session = sessionmaker()
146 self.iotlab_session = Session(bind = self.iotlab_engine)
147 return self.iotlab_session
149 def close_session(self):
151 Closes connection to database.
154 if self.iotlab_session is None:
156 self.iotlab_session.close()
157 self.iotlab_session = None
160 def update_jobs_in_iotlabdb(self, job_oar_list, jobs_psql):
161 """ Cleans the iotlab db by deleting expired and cancelled jobs.
162 Compares the list of job ids given by OAR with the job ids that
163 are already in the database, deletes the jobs that are no longer in
165 :param job_oar_list: list of job ids coming from OAR
166 :type job_oar_list: list
167 :param job_psql: list of job ids cfrom the database.
170 #Turn the list into a set
171 set_jobs_psql = set(jobs_psql)
173 kept_jobs = set(job_oar_list).intersection(set_jobs_psql)
174 logger.debug ( "\r\n \t\ update_jobs_in_iotlabdb jobs_psql %s \r\n \t \
175 job_oar_list %s kept_jobs %s "%(set_jobs_psql, job_oar_list, kept_jobs))
176 deleted_jobs = set_jobs_psql.difference(kept_jobs)
177 deleted_jobs = list(deleted_jobs)
178 if len(deleted_jobs) > 0:
179 self.iotlab_session.query(IotlabXP).filter(IotlabXP.job_id.in_(deleted_jobs)).delete(synchronize_session='fetch')
180 self.iotlab_session.commit()
186 def __init__(self, config, debug = False):
187 self.sl_base = IotlabBase
189 # Check whether we already have an instance
190 if IotlabDB._connection_singleton is None:
191 IotlabDB._connection_singleton = IotlabDB.Singleton(config, debug)
193 # Store instance reference as the only member in the handle
194 self._EventHandler_singleton = IotlabDB._connection_singleton
198 def __getattr__(self, aAttr):
200 Delegate access to implementation.
202 :param attr: Attribute wanted.
205 return getattr(self._connection_singleton, aAttr)
209 # def __setattr__(self, aAttr, aValue):
210 # """Delegate access to implementation.
212 # :param attr: Attribute wanted.
213 # :param value: Vaule to be set.
214 # :return: Result of operation.
216 # return setattr(self._connection_singleton, aAttr, aValue)
222 def exists(self, tablename):
224 Checks if the table specified as tablename exists.
229 metadata = MetaData (bind=self.iotlab_engine)
230 table = Table (tablename, metadata, autoload=True)
233 except NoSuchTableError:
234 logger.log_exc("SLABPOSTGRES tablename %s does not exists" \
239 def createtable(self):
241 Creates all the table sof the engine.
242 Uses the global dictionnary holding the tablenames and the table schema.
246 logger.debug("SLABPOSTGRES createtable IotlabBase.metadata.sorted_tables \
247 %s \r\n engine %s" %(IotlabBase.metadata.sorted_tables , self.iotlab_engine))
248 IotlabBase.metadata.create_all(self.iotlab_engine)
253 # iotlab_alchemy = IotlabDB(Config())
254 # iotlab_engine = iotlab_alchemy.iotlab_engine
255 # iotlab_dbsession = iotlab_alchemy.session()