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
164 def __init__(self, config, debug = False):
165 self.sl_base = IotlabBase
167 # Check whether we already have an instance
168 if IotlabDB._connection_singleton is None:
169 IotlabDB._connection_singleton = IotlabDB.Singleton(config, debug)
171 # Store instance reference as the only member in the handle
172 self._EventHandler_singleton = IotlabDB._connection_singleton
176 def __getattr__(self, aAttr):
178 Delegate access to implementation.
180 :param attr: Attribute wanted.
183 return getattr(self._connection_singleton, aAttr)
187 # def __setattr__(self, aAttr, aValue):
188 # """Delegate access to implementation.
190 # :param attr: Attribute wanted.
191 # :param value: Vaule to be set.
192 # :return: Result of operation.
194 # return setattr(self._connection_singleton, aAttr, aValue)
200 def exists(self, tablename):
202 Checks if the table specified as tablename exists.
207 metadata = MetaData (bind=self.iotlab_engine)
208 table = Table (tablename, metadata, autoload=True)
211 except NoSuchTableError:
212 logger.log_exc("SLABPOSTGRES tablename %s does not exists" \
217 def createtable(self):
219 Creates all the table sof the engine.
220 Uses the global dictionnary holding the tablenames and the table schema.
224 logger.debug("SLABPOSTGRES createtable IotlabBase.metadata.sorted_tables \
225 %s \r\n engine %s" %(IotlabBase.metadata.sorted_tables , self.iotlab_engine))
226 IotlabBase.metadata.create_all(self.iotlab_engine)
231 # iotlab_alchemy = IotlabDB(Config())
232 # iotlab_engine = iotlab_alchemy.iotlab_engine
233 # iotlab_dbsession = iotlab_alchemy.session()