123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255 |
- '''
- This module contains DB logic
- '''
- import logging
- from datetime import datetime, timedelta
- import sys
- from sqlalchemy import create_engine, Column, Integer, String, DateTime, inspect
- from sqlalchemy.orm import sessionmaker
- from sqlalchemy.ext.declarative import declarative_base
- from config_manager import get_config
- PAGESIZE = 10
- Base = declarative_base()
- class Rec(Base):
- '''Entry on the DB'''
- __tablename__ = 'rec'
- id = Column(Integer, primary_key=True)
- name = Column(String, nullable=True)
- starttime = Column(DateTime, nullable=True)
- endtime = Column(DateTime, nullable=True)
- filename = Column(String, nullable=True)
- def __init__(self, name="", starttime=None, endtime=None,
- filename=None):
- self.name = name
- self.starttime = starttime
- self.endtime = endtime
- self.filename = filename
- def serialize(self):
- '''json-friendly encoding'''
- return {'id': self.id,
- 'name': self.name,
- 'starttime': self.starttime,
- 'endtime': self.endtime,
- 'filename': self.filename
- }
- def __repr__(self):
- contents = "id:'%s',name:'%s',Start: '%s',End: '%s'" % \
- (self.id, self.name, self.starttime, self.endtime)
- if self.filename is not None:
- contents += ",Filename: '%s'" % self.filename
- return "<Rec(%s)>" % contents
- class RecDB:
- def __init__(self, uri):
- self.engine = create_engine(uri, echo=False)
- self.conn = self.engine.connect()
- self.log = logging.getLogger(name=self.__class__.__name__)
- logging.getLogger('sqlalchemy.engine').setLevel(logging.FATAL)
- logging.getLogger('sqlalchemy.engine.base.Engine')\
- .setLevel(logging.FATAL)
- logging.getLogger('sqlalchemy.dialects').setLevel(logging.FATAL)
- logging.getLogger('sqlalchemy.pool').setLevel(logging.FATAL)
- logging.getLogger('sqlalchemy.orm').setLevel(logging.FATAL)
- Base.metadata.create_all(self.engine) # create Database
- Session = sessionmaker(bind=self.engine)
- self.session = Session()
- self.err = ""
- def add(self, simplerecord):
- s = self.get_session()
- s.add( simplerecord )
- s.commit()
- self.log.info("New Record: %s" % simplerecord)
- return ( simplerecord )
- def update(self, id, rec):
- # TODO: rlist = results list
- _rlist = self._search(_id=id)
- if not len(_rlist) == 1:
- raise ValueError('Too many recs with id=%s' % id)
- self.log.debug("DB:: Update request %s:%s " % (id, rec))
- self.log.debug("DB:: Update: data before %s" % _rlist[0])
- # 2013-11-24 22:22:42
- _rlist[0].starttime = rec["starttime"]
- _rlist[0].endtime = rec["endtime"]
- if 'name' in rec:
- _rlist[0].name = rec["name"]
- self.log.debug("DB:: Update: data AFTER %s" % _rlist[0])
- self.get_session(_rlist[0]).commit()
- self.log.debug("DB:: Update complete")
- return _rlist[0]
- def delete(self, recid):
- _rlist = self._search(id=recid)
- if len(_rlist) == 0:
- self.log.info("DB: Delete: no record found!")
- self.err = "No rec found"
- return False
- if len(_rlist) > 1:
- self.log.warning("DB: Delete: multiple records found!")
- self.err = "multiple ID Found %s" % (_rlist)
- return False
- s = self.get_session()
- s.delete(_rlist[0])
- self.log.info("Delete: delete complete")
- s.commit()
- return True
- def commit(self):
- self.log.info("Commit!!")
- self.session.commit()
- def get_session(self, rec=None):
- if rec is None:
- Session = sessionmaker(bind=self.engine)
- return Session()
- else:
- return inspect(rec).session
- def get_all(self, page=0, page_size=PAGESIZE):
- return self._search(page=page, page_size=page_size)
- def get_ongoing(self, page=0, page_size=PAGESIZE):
- query = self._query_page(self._query_ongoing(), page, page_size)
- return query.all()
- def get_not_completed(self, minseconds=36000):
- query = self._query_ongoing()
- query = self._query_older(timedelta(seconds=minseconds), query)
- return query.all()
- def get_archive_recent(self):
- query = self._query_saved()
- query = self._query_newer(timedelta(days=15), query)
- query = query.order_by(Rec.starttime.desc())
- return query.all()
- def _query_ongoing(self, query=None):
- '''
- Not terminated AND recent.
- The meaning is "a query that makes sense to stop"
- '''
- delta = timedelta(seconds=get_config()['FORGE_MAX_DURATION'])
- return self._query_newer(delta, self._query_not_saved(query))
- def _query_not_saved(self, query=None):
- '''Still not saved'''
- if query is None:
- query = self.get_session().query(Rec)
- return query.filter(Rec.filename == None)
- def _query_saved(self, query=None):
- '''Still not saved'''
- if query is None:
- query = self.get_session().query(Rec)
- return query.filter(Rec.filename != None)
- def _query_newer(self, delta, query=None):
- '''Get Rec older than delta seconds'''
- if query is None:
- query = self.get_session().query(Rec)
- return query.filter(Rec.starttime > datetime.now() - delta)
- def _query_older(self, delta, query=None):
- '''Get Rec older than delta seconds'''
- if query is None:
- query = self.get_session().query(Rec)
- return query.filter(Rec.starttime < datetime.now() - delta)
- def _query_page(self, query, page=0, page_size=PAGESIZE):
- if page_size:
- page_size = int(page_size)
- query = query.limit(page_size)
- if page:
- query = query.offset(page*page_size)
- return query
- def _query_generic(self, query, _id=None, name=None, starttime=None,
- endtime=None):
- if _id is not None:
- query = query.filter_by(id=_id)
- if name is not None:
- query = query.filter(Rec.name.like("%"+name+"%"))
- if starttime is not None:
- _st = starttime
- query = query.filter(Rec.starttime > _st)
- if endtime is not None:
- _et = endtime
- query = query.filter(Rec.endtime < _et)
- return query
- def _search(self, _id=None, name=None, starttime=None,
- endtime=None, page=0, page_size=PAGESIZE):
- self.log.debug(
- "DB: Search => id:%s name:%s starttime:%s endtime=%s" %
- (_id, name, starttime, endtime))
- query = self.get_session().query(Rec)
- query = self._query_generic(query, _id, name, starttime,
- endtime)
- query = self._query_page(query, page, page_size)
- self.log.debug("Searching: %s" % str(query))
- ret = query.all()
- return ret
- def get_err(self):
- print "DB error: %s" % (self.err)
- t = self.err
- self.err = ""
- return t
- if __name__ == "__main__":
- def printall(queryres):
- for record in queryres:
- print "Record: %s" % record
- db = RecDB()
- _mytime = datetime(2014, 05, 23, 15, 12, 17)
- _endtime = datetime(2014, 05, 24, 17, 45, 17)
- a = Rec(name="Mimmo1", starttime=_mytime, endtime=_endtime)
- printall(db._search())
- sys.exit("End test job")
- # a = Rec(name="Mimmo1", starttime=_mytime, endtime=None)
- print "Aggiunto", db.add(a)
- printall(db.get_all(page_size=5, page=0))
- print "Mimmo "
- printall(db._search(name="Mimmo1"))
- print "Search"
- printall(db._search(name="Mimmo1",
- starttime=datetime(2014, 05, 24, 15, 16, 1) ))
- a = db.get_by_id(5)
- a.start()
- db.delete(1)
- db.delete(2)
- db.delete(4)
- db.delete(1)
- printall( db._search() )
|