class Database(object):
    """
    An object representing a database connection.
    """
    _connection = None
    _configured = False
    transaction = None
    cursor = None
    session = None

    # this makes this class a singleton
    _instance = None

    def __new__(cls, *args, **kwargs):
        if not cls._instance:
            cls._instance = object.__new__(cls)
        return cls._instance

    def __init__(self, **kwargs):
        if self._configured:
            if kwargs:
                logger.warning("Not configuring pre-configured database")
            return
        elif not kwargs:
            kwargs = tkp.config.get_database_config()

        self.engine = kwargs['engine']
        self.database = kwargs['database']
        self.user = kwargs['user']
        self.password = kwargs['password']
        self.host = kwargs['host']
        self.port = kwargs['port']
        logger.info("Database config: %s://%s@%s:%s/%s" % (self.engine,
                                                           self.user,
                                                           self.host,
                                                           self.port,
                                                           self.database))
        self._configured = True

        self.alchemy_engine = create_engine('%s://%s:%s@%s:%s/%s' %
                                            (self.engine,
                                             self.user,
                                             self.password,
                                             self.host,
                                             self.port,
                                             self.database),
                                            echo=False,
                                            poolclass=NullPool,
                                            )
        self.Session = sessionmaker(bind=self.alchemy_engine)
        self.session = self.Session()

    def connect(self, check=True):
        """
        connect to the configured database

        args:
            check (bool): check if schema version is correct
        """
        logger.info("connecting to database...")

        self._connection = self.alchemy_engine.connect()
        self._connection.execution_options(autocommit=False)

        if check:
            # Check that our database revision matches that expected by the
            # codebase.
            q = "SELECT value FROM version WHERE name='revision'"
            cursor = self.connection.execute(q)
            schema_version = cursor.fetchone()[0]
            if schema_version != SCHEMA_VERSION:
                error = ("Database version incompatibility (needed %d, got %d)" %
                         (SCHEMA_VERSION, schema_version))
                logger.error(error)
                self._connection.close()
                self._connection = None
                raise Exception(error)

            logger.info("connected to: %s://%s@%s:%s/%s" % (self.engine,
                                                            self.user,
                                                            self.host,
                                                            self.port,
                                                            self.database))

    @property
    def connection(self):
        """
        The database connection, will be created if it doesn't exists.

        This is a property to be backwards compatible with the rest of TKP.

        :return: a database connection
        """
        if not self._connection:
            self.connect()

        self.cursor = self._connection.connection.cursor()

        return self._connection

    def close(self):
        """
        close the connection if open
        """
        if self.session:
            self.session.close()

        if self._connection:
            self._connection.close()

        self._connection = None

    def vacuum(self, table):
        """
        Force a vacuum on a table, which removes dead rows. (Postgres only)

        Normally the auto vacuum process does this for you, but in some cases
        (for example when the table receives many insert and deletes)  manual
        vacuuming is necessary for performance reasons.

        args:
            table: name of the table in the database you want to vacuum
        """

        if self.engine != "postgresql":
            return

        from psycopg2.extensions import (ISOLATION_LEVEL_AUTOCOMMIT,
                                            ISOLATION_LEVEL_READ_COMMITTED)

        # disable autocommit since can't vacuum in transaction
        self.connection.connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
        cursor = self.connection.connection.cursor()
        cursor.execute("VACUUM ANALYZE %s" % table)
        # reset settings
        self.connection.connection.set_isolation_level(ISOLATION_LEVEL_READ_COMMITTED)

    def execute(self, query, parameters={}, commit=False):
        if commit:
           self.transaction = self.connection.begin()

        try:
            cursor = self.connection.execute(query, parameters)
            if commit:
                self.transaction.commit()
            return cursor
        except Exception as e:
            logger.error("Query failed: %s. Query: %s." % (e, query % parameters))
            raise

    def rollback(self):
        if self.transaction:
            self.transaction.rollback()

    def reconnect(self):
        self._connection.close()
        self.connect() class Runningcatalog(Base):
    __tablename__ = 'runningcatalog'

    id = Column(Integer, primary_key=True)

    xtrsrc_id = Column('xtrsrc', ForeignKey('extractedsource.id'), nullable=False, unique=True)
    xtrsrc = relationship('Extractedsource',
                          primaryjoin='Runningcatalog.xtrsrc_id == Extractedsource.id',
                          backref=backref('extractedsources', cascade="all,delete"))

    dataset_id = Column('dataset', ForeignKey('dataset.id'), nullable=False, index=True)
    dataset = relationship('Dataset')

    datapoints = Column(Integer, nullable=False)
    zone = Column(Integer, nullable=False, index=True)
    wm_ra = Column(Double, nullable=False, index=True)
    wm_decl = Column(Double, nullable=False, index=True)
    wm_uncertainty_ew = Column(Double, nullable=False, index=True)
    wm_uncertainty_ns = Column(Double, nullable=False, index=True)
    avg_ra_err = Column(Double, nullable=False)
    avg_decl_err = Column(Double, nullable=False)
    avg_wra = Column(Double, nullable=False)
    avg_wdecl = Column(Double, nullable=False)
    avg_weight_ra = Column(Double, nullable=False)
    avg_weight_decl = Column(Double, nullable=False)
    x = Column(Double, nullable=False, index=True)
    y = Column(Double, nullable=False, index=True)
    z = Column(Double, nullable=False, index=True)
    inactive = Column(Boolean, nullable=False, server_default=text("false"))
    mon_src = Column(Boolean, nullable=False, server_default=text("false"))
    forcedfits_count = Column(Integer, server_default=text("0"))

    extractedsources = relationship('Extractedsource',
                                    secondary='assocxtrsource',
                                    backref='runningcatalogs')

    varmetric = relationship("Varmetric", uselist=False, backref="runcat",
                             cascade="all,delete") class Extractedsource(Base):
    __tablename__ = 'extractedsource'

    id = Column(Integer, primary_key=True)

    image_id = Column('image', ForeignKey('image.id'), nullable=False, index=True)
    image = relationship('Image', backref=backref('extractedsources', cascade="all,delete"))

    ff_runcat_id = Column('ff_runcat', ForeignKey('runningcatalog.id'))
    ff_runcat = relationship('Runningcatalog',  primaryjoin='Extractedsource.ff_runcat_id == Runningcatalog.id')

    ff_monitor_id = Column('ff_monitor', ForeignKey('monitor.id'))
    ff_monitor = relationship('Monitor')

    zone = Column(Integer, nullable=False)
    ra = Column(Double, nullable=False, index=True)
    decl = Column(Double, nullable=False, index=True)
    uncertainty_ew = Column(Double, nullable=False)
    uncertainty_ns = Column(Double, nullable=False)
    ra_err = Column(Double, nullable=False, index=True)
    decl_err = Column(Double, nullable=False, index=True)
    ra_fit_err = Column(Double, nullable=False)
    decl_fit_err = Column(Double, nullable=False)
    ew_sys_err = Column(Double, nullable=False)
    ns_sys_err = Column(Double, nullable=False)
    error_radius = Column(Double, nullable=False)
    x = Column(Double, nullable=False, index=True)
    y = Column(Double, nullable=False, index=True)
    z = Column(Double, nullable=False, index=True)
    racosdecl = Column(Double, nullable=False)
    margin = Column(Boolean, nullable=False, server_default=text("false"))
    det_sigma = Column(Double, nullable=False)
    semimajor = Column(Double)
    semiminor = Column(Double)
    pa = Column(Double)
    f_peak = Column(Double)
    f_peak_err = Column(Double)
    f_int = Column(Double)
    f_int_err = Column(Double)
    chisq = Column(Double)
    reduced_chisq = Column(Double)
    extract_type = Column(SmallInteger)
    fit_type = Column(SmallInteger)

    node = Column(SmallInteger, nullable=False, server_default=text("1"))
    nodes = Column(SmallInteger, nullable=False, server_default=text("1")) FORCED_FIT = 1 class Temprunningcatalog(Base):
    __tablename__ = 'temprunningcatalog'

    id = Column(Integer, primary_key=True)

    runcat_id = Column('runcat', ForeignKey('runningcatalog.id'), nullable=False, index=True)
    runcat = relationship('Runningcatalog')

    xtrsrc_id = Column('xtrsrc', ForeignKey('extractedsource.id'), nullable=False, index=True)
    xtrsrc = relationship('Extractedsource')

    dataset_id = Column('dataset', ForeignKey('dataset.id'), nullable=False, index=True)
    dataset = relationship('Dataset')

    band_id = Column('band', ForeignKey('frequencyband.id'), nullable=False, index=True)
    band = relationship('Frequencyband', cascade="delete")

    distance_arcsec = Column(Double, nullable=False)
    r = Column(Double, nullable=False)
    stokes = Column(SmallInteger, nullable=False, server_default=text("1"))
    datapoints = Column(Integer, nullable=False)
    zone = Column(Integer, nullable=False)
    wm_ra = Column(Double, nullable=False)
    wm_decl = Column(Double, nullable=False)
    wm_uncertainty_ew = Column(Double, nullable=False)
    wm_uncertainty_ns = Column(Double, nullable=False)
    avg_ra_err = Column(Double, nullable=False)
    avg_decl_err = Column(Double, nullable=False)
    avg_wra = Column(Double, nullable=False)
    avg_wdecl = Column(Double, nullable=False)
    avg_weight_ra = Column(Double, nullable=False)
    avg_weight_decl = Column(Double, nullable=False)
    x = Column(Double, nullable=False)
    y = Column(Double, nullable=False)
    z = Column(Double, nullable=False)
    margin = Column(Boolean, nullable=False, server_default=text("false"))
    inactive = Column(Boolean, nullable=False, server_default=text("false"))
    beam_semimaj = Column(Double)
    beam_semimin = Column(Double)
    beam_pa = Column(Double)
    f_datapoints = Column(Integer)
    avg_f_peak = Column(Double)
    avg_f_peak_sq = Column(Double)
    avg_f_peak_weight = Column(Double)
    avg_weighted_f_peak = Column(Double)
    avg_weighted_f_peak_sq = Column(Double)
    avg_f_int = Column(Double)
    avg_f_int_sq = Column(Double)
    avg_f_int_weight = Column(Double)
    avg_weighted_f_int = Column(Double)
    avg_weighted_f_int_sq = Column(Double) def _update_1_to_1_runcat():
    """Update the running catalog with the values in temprunningcatalog"""
    query = """\
        UPDATE runningcatalog
           SET datapoints = (SELECT datapoints
                               FROM temprunningcatalog
                              WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                            )
              ,zone = (SELECT zone
                         FROM temprunningcatalog
                        WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                      )
              ,wm_ra = (SELECT wm_ra
                          FROM temprunningcatalog
                         WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                       )
              ,wm_decl = (SELECT wm_decl
                            FROM temprunningcatalog
                           WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                         )
              ,avg_ra_err = (SELECT avg_ra_err
                              FROM temprunningcatalog
                             WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                           )
              ,avg_decl_err = (SELECT avg_decl_err
                                FROM temprunningcatalog
                               WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                             )
              ,wm_uncertainty_ew = (SELECT wm_uncertainty_ew
                                FROM temprunningcatalog
                               WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                             )
              ,wm_uncertainty_ns = (SELECT wm_uncertainty_ns
                                FROM temprunningcatalog
                               WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                             )
              ,avg_wra = (SELECT avg_wra
                            FROM temprunningcatalog
                           WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                         )
              ,avg_wdecl = (SELECT avg_wdecl
                              FROM temprunningcatalog
                             WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                           )
              ,avg_weight_ra = (SELECT avg_weight_ra
                                  FROM temprunningcatalog
                                 WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                               )
              ,avg_weight_decl = (SELECT avg_weight_decl
                                    FROM temprunningcatalog
                                   WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                                 )
              ,x = (SELECT x
                      FROM temprunningcatalog
                     WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                   )
              ,y = (SELECT y
                      FROM temprunningcatalog
                     WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                   )
              ,z = (SELECT z
                      FROM temprunningcatalog
                     WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                   )
              ,forcedfits_count = 0
         WHERE EXISTS (SELECT runcat
                         FROM temprunningcatalog
                        WHERE temprunningcatalog.runcat = runningcatalog.id
                          AND temprunningcatalog.inactive = FALSE
                      )
"""
    tkp.db.execute(query, commit=True) def gen_band(dataset, central=150**6, low=None, high=None):
    if not low:
        low = central * .9
    if not high:
        high = central * 1.1
    return tkp.db.model.Frequencyband(dataset=dataset, freq_low=low, freq_central=central,
                                      freq_high=high) def gen_dataset(description='A test dataset without a description'):
    return tkp.db.model.Dataset(process_start_ts=datetime.now(),
                                description=description) def gen_skyregion(dataset):
    return tkp.db.model.Skyregion(dataset=dataset, centre_ra=1, centre_decl=1,
                                  xtr_radius=1, x=1, y=1, z=1) def gen_extractedsource(image):
    return tkp.db.model.Extractedsource(zone=1, ra=1, decl=1, uncertainty_ew=1, x=1, y=1,
                                        z=1, uncertainty_ns=1, ra_err=1, decl_err=1,
                                        ra_fit_err=1, decl_fit_err=1, ew_sys_err=1,
                                        ns_sys_err=1, error_radius=1, racosdecl=1,
                                        det_sigma=1, f_int=0.01, image=image) def gen_runningcatalog(xtrsrc, dataset):
    return tkp.db.model.Runningcatalog(xtrsrc=xtrsrc, dataset=dataset, datapoints=1,
                                       zone=1, wm_ra=1., wm_decl=1, wm_uncertainty_ew=1,
                                       wm_uncertainty_ns=1, avg_ra_err=1, avg_decl_err=1,
                                       avg_wra=1, avg_wdecl=1, avg_weight_ra=1, avg_weight_decl=1,
                                       x=1, y=1, z=1) def gen_assocskyrgn(runcat, skyrgn):
    return tkp.db.model.Assocskyrgn(runcat=runcat, skyrgn=skyrgn,
                                    distance_deg=10) def gen_assocxtrsource(runningcatalog, xtrsrc):
    return tkp.db.model.Assocxtrsource(runcat=runningcatalog, xtrsrc=xtrsrc,
                                       type=0, r=0, distance_arcsec=0, v_int=0,
                                       eta_int=0, f_datapoints=0) def gen_image(band=None, dataset=None, skyregion=None, taustart_ts=None):
    if not dataset:
        dataset = gen_dataset()
    if not band:
        band = gen_band(dataset=dataset)
    if not skyregion:
        skyregion=gen_skyregion(dataset=dataset)
    if not taustart_ts:
        taustart_ts = datetime.now()
    return tkp.db.model.Image(band=band, dataset=dataset, skyrgn=skyregion,
                              freq_eff=2, rb_smin=1, taustart_ts=taustart_ts,
                              rb_smaj=1, rb_pa=1, deltax=1, deltay=1, rms_qc=0) def database_disabled():
    """Return ``True`` if the database is disabled for test purposes."""
    return os.environ.get("TKP_DISABLEDB", False)
import unittest
import logging
from datetime import datetime, timedelta
from tkp.db.database import Database
from tkp.db.model import Runningcatalog, Extractedsource, FORCED_FIT,\
    Temprunningcatalog
from tkp.db.nulldetections import get_nulldetections, associate_nd
from tkp.db.associations import _update_1_to_1_runcat
from tkp.testutil.alchemy import gen_band, gen_dataset, gen_skyregion, \
    gen_extractedsource, gen_runningcatalog, gen_assocskyrgn, \
    gen_assocxtrsource, gen_image
from tkp.testutil.decorators import database_disabled
"""
All tests related to the 'expiration' of extracted sources.

Without expiration sources will *always* be extracted, also when the source
was only above the detection threshold for one time. This introduces a lot of
false positive light curves in the case of a noise image, and eventually the
whole image will be monitored.

"""




logging.basicConfig(level=logging.DEBUG)
#logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)


class TestExpire(unittest.TestCase):
    @classmethod
    def setUpClass(cls):
        # Can't use a regular skip here, due to a Nose bug:
        # https://github.com/nose-devs/nose/issues/946
        if database_disabled():
            raise unittest.SkipTest("Database functionality disabled "
                                    "in configuration.")
        cls.database = Database()
        cls.database.connect()

    def tearDown(self):
        self.session.rollback()

    def setUp(self):
        """
        make a dataset with 10 images with 2 ligthcurves + one empty image
        """
        self.session = self.database.Session()
        self.dataset = gen_dataset('expiring runningcatalog test')

        self.band = gen_band(dataset=self.dataset, central=150**6)