def test_list(self):
        """
        Test that ModifiedJulianDate.get_list() gets results that are consistent
        with creating a list of ModifiedJulianDates by hand.
        """

        rng = np.random.RandomState(88)
        tol = 10  # decimal place tolerance

        tai_list = 40000.0 + 10000.0 * rng.random_sample(20)
        tai_list = np.append(tai_list, 59580.0 + 10000.0 * rng.random_sample(20))
        mjd_list = ModifiedJulianDate.get_list(TAI=tai_list)
        for tai, mjd in zip(tai_list, mjd_list):
            msg = "Offending TAI: %f" % tai
            control = ModifiedJulianDate(TAI=tai)
            self.assertAlmostEqual(mjd.TAI, tai, 11, msg=msg)
            self.assertAlmostEqual(mjd.TAI, control.TAI, tol, msg=msg)
            self.assertAlmostEqual(mjd.UTC, control.UTC, tol, msg=msg)
            self.assertAlmostEqual(mjd.UT1, control.UT1, tol, msg=msg)
            self.assertAlmostEqual(mjd.TT, control.TT, tol, msg=msg)
            self.assertAlmostEqual(mjd.TDB, control.TDB, tol, msg=msg)
            self.assertAlmostEqual(mjd.dut1, control.dut1, tol, msg=msg)

        utc_list = 40000.0 + 10000.0 * rng.random_sample(20)
        utc_list = np.append(utc_list, 59580.0 + 10000.0 * rng.random_sample(20))
        mjd_list = ModifiedJulianDate.get_list(UTC=utc_list)
        for utc, mjd in zip(utc_list, mjd_list):
            msg = "Offending UTC: %f" % utc
            control = ModifiedJulianDate(UTC=utc)
            self.assertAlmostEqual(mjd.UTC, utc, tol, msg=msg)
            self.assertAlmostEqual(mjd.TAI, control.TAI, tol, msg=msg)
            self.assertAlmostEqual(mjd.UTC, control.UTC, tol, msg=msg)
            self.assertAlmostEqual(mjd.UT1, control.UT1, tol, msg=msg)
            self.assertAlmostEqual(mjd.TT, control.TT, tol, msg=msg)
            self.assertAlmostEqual(mjd.TDB, control.TDB, tol, msg=msg)
            self.assertAlmostEqual(mjd.dut1, control.dut1, tol, msg=msg)

        # Now test the case where we only have dates in the future (this
        # is an edge case since good_dexes in ModifiedJulianDate._get_ut1_from_utc
        # will have len = 0
        tai_list = 60000.0 + 10000.0 * rng.random_sample(20)
        mjd_list = ModifiedJulianDate.get_list(TAI=tai_list)
        for tai, mjd in zip(tai_list, mjd_list):
            msg = "Offending TAI: %f" % tai
            control = ModifiedJulianDate(TAI=tai)
            self.assertAlmostEqual(mjd.TAI, tai, 11, msg=msg)
            self.assertAlmostEqual(mjd.TAI, control.TAI, tol, msg=msg)
            self.assertAlmostEqual(mjd.UTC, control.UTC, tol, msg=msg)
            self.assertAlmostEqual(mjd.UT1, control.UT1, tol, msg=msg)
            self.assertAlmostEqual(mjd.TT, control.TT, tol, msg=msg)
            self.assertAlmostEqual(mjd.TDB, control.TDB, tol, msg=msg)
            self.assertAlmostEqual(mjd.dut1, control.dut1, tol, msg=msg)
    def test_alert_data_generation(self):

        dmag_cutoff = 0.005
        mag_name_to_int = {'u': 0, 'g': 1, 'r': 2, 'i': 3, 'z' : 4, 'y': 5}

        _max_var_param_str = self.max_str_len

        class StarAlertTestDBObj(StellarAlertDBObjMixin, CatalogDBObject):
            objid = 'star_alert'
            tableid = 'stars'
            idColKey = 'simobjid'
            raColName = 'ra'
            decColName = 'dec'
            objectTypeId = 0
            columns = [('raJ2000', 'ra*0.01745329252'),
                       ('decJ2000', 'dec*0.01745329252'),
                       ('parallax', 'px*0.01745329252/3600.0'),
                       ('properMotionRa', 'pmra*0.01745329252/3600.0'),
                       ('properMotionDec', 'pmdec*0.01745329252/3600.0'),
                       ('radialVelocity', 'vrad'),
                       ('variabilityParameters', 'varParamStr', str, _max_var_param_str)]

        class TestAlertsVarCatMixin(object):

            @register_method('alert_test')
            def applyAlertTest(self, valid_dexes, params, expmjd, variability_cache=None):
                if len(params) == 0:
                    return np.array([[], [], [], [], [], []])

                if isinstance(expmjd, numbers.Number):
                    dmags_out = np.zeros((6, self.num_variable_obj(params)))
                else:
                    dmags_out = np.zeros((6, self.num_variable_obj(params), len(expmjd)))

                for i_star in range(self.num_variable_obj(params)):
                    if params['amp'][i_star] is not None:
                        dmags = params['amp'][i_star]*np.cos(params['per'][i_star]*expmjd)
                        for i_filter in range(6):
                            dmags_out[i_filter][i_star] = dmags

                return dmags_out

        class TestAlertsVarCat(TestAlertsVarCatMixin, AlertStellarVariabilityCatalog):
            pass

        class TestAlertsTruthCat(TestAlertsVarCatMixin, CameraCoords, AstrometryStars,
                                 Variability, InstanceCatalog):
            column_outputs = ['uniqueId', 'chipName', 'dmagAlert', 'magAlert']

            camera = obs_lsst_phosim.PhosimMapper().camera

            @compound('delta_umag', 'delta_gmag', 'delta_rmag',
                      'delta_imag', 'delta_zmag', 'delta_ymag')
            def get_TruthVariability(self):
                return self.applyVariability(self.column_by_name('varParamStr'))

            @cached
            def get_dmagAlert(self):
                return self.column_by_name('delta_%smag' % self.obs_metadata.bandpass)

            @cached
            def get_magAlert(self):
                return self.column_by_name('%smag' % self.obs_metadata.bandpass) + \
                       self.column_by_name('dmagAlert')

        star_db = StarAlertTestDBObj(database=self.star_db_name, driver='sqlite')

        # assemble the true light curves for each object; we need to figure out
        # if their np.max(dMag) ever goes over dmag_cutoff; then we will know if
        # we are supposed to simulate them
        true_lc_dict = {}
        true_lc_obshistid_dict = {}
        is_visible_dict = {}
        obs_dict = {}
        max_obshistid = -1
        n_total_observations = 0
        for obs in self.obs_list:
            obs_dict[obs.OpsimMetaData['obsHistID']] = obs
            obshistid = obs.OpsimMetaData['obsHistID']
            if obshistid > max_obshistid:
                max_obshistid = obshistid
            cat = TestAlertsTruthCat(star_db, obs_metadata=obs)

            for line in cat.iter_catalog():
                if line[1] is None:
                    continue

                n_total_observations += 1
                if line[0] not in true_lc_dict:
                    true_lc_dict[line[0]] = {}
                    true_lc_obshistid_dict[line[0]] = []

                true_lc_dict[line[0]][obshistid] = line[2]
                true_lc_obshistid_dict[line[0]].append(obshistid)

                if line[0] not in is_visible_dict:
                    is_visible_dict[line[0]] = False

                if line[3] <= self.obs_mag_cutoff[mag_name_to_int[obs.bandpass]]:
                    is_visible_dict[line[0]] = True

        obshistid_bits = int(np.ceil(np.log(max_obshistid)/np.log(2)))

        skipped_due_to_mag = 0

        objects_to_simulate = []
        obshistid_unqid_set = set()
        for obj_id in true_lc_dict:

            dmag_max = -1.0
            for obshistid in true_lc_dict[obj_id]:
                if np.abs(true_lc_dict[obj_id][obshistid]) > dmag_max:
                    dmag_max = np.abs(true_lc_dict[obj_id][obshistid])

            if dmag_max >= dmag_cutoff:
                if not is_visible_dict[obj_id]:
                    skipped_due_to_mag += 1
                    continue

                objects_to_simulate.append(obj_id)
                for obshistid in true_lc_obshistid_dict[obj_id]:
                    obshistid_unqid_set.add((obj_id << obshistid_bits) + obshistid)

        self.assertGreater(len(objects_to_simulate), 10)
        self.assertGreater(skipped_due_to_mag, 0)

        log_file_name = tempfile.mktemp(dir=self.output_dir, suffix='log.txt')
        alert_gen = AlertDataGenerator(testing=True)

        alert_gen.subdivide_obs(self.obs_list, htmid_level=6)

        for htmid in alert_gen.htmid_list:
            alert_gen.alert_data_from_htmid(htmid, star_db,
                                            photometry_class=TestAlertsVarCat,
                                            output_prefix='alert_test',
                                            output_dir=self.output_dir,
                                            dmag_cutoff=dmag_cutoff,
                                            log_file_name=log_file_name)

        dummy_sed = Sed()

        bp_dict = BandpassDict.loadTotalBandpassesFromFiles()

        phot_params = PhotometricParameters()

        # First, verify that the contents of the sqlite files are all correct

        n_tot_simulated = 0

        alert_query = 'SELECT alert.uniqueId, alert.obshistId, meta.TAI, '
        alert_query += 'meta.band, quiescent.flux, alert.dflux, '
        alert_query += 'quiescent.snr, alert.snr, '
        alert_query += 'alert.ra, alert.dec, alert.chipNum, '
        alert_query += 'alert.xPix, alert.yPix, ast.pmRA, ast.pmDec, '
        alert_query += 'ast.parallax '
        alert_query += 'FROM alert_data AS alert '
        alert_query += 'INNER JOIN metadata AS meta ON meta.obshistId=alert.obshistId '
        alert_query += 'INNER JOIN quiescent_flux AS quiescent '
        alert_query += 'ON quiescent.uniqueId=alert.uniqueId '
        alert_query += 'AND quiescent.band=meta.band '
        alert_query += 'INNER JOIN baseline_astrometry AS ast '
        alert_query += 'ON ast.uniqueId=alert.uniqueId'

        alert_dtype = np.dtype([('uniqueId', int), ('obshistId', int),
                                ('TAI', float), ('band', int),
                                ('q_flux', float), ('dflux', float),
                                ('q_snr', float), ('tot_snr', float),
                                ('ra', float), ('dec', float),
                                ('chipNum', int), ('xPix', float), ('yPix', float),
                                ('pmRA', float), ('pmDec', float), ('parallax', float)])

        sqlite_file_list = os.listdir(self.output_dir)

        n_tot_simulated = 0
        obshistid_unqid_simulated_set = set()
        for file_name in sqlite_file_list:
            if not file_name.endswith('db'):
                continue
            full_name = os.path.join(self.output_dir, file_name)
            self.assertTrue(os.path.exists(full_name))
            alert_db = DBObject(full_name, driver='sqlite')
            alert_data = alert_db.execute_arbitrary(alert_query, dtype=alert_dtype)
            if len(alert_data) == 0:
                continue

            mjd_list = ModifiedJulianDate.get_list(TAI=alert_data['TAI'])
            for i_obj in range(len(alert_data)):
                n_tot_simulated += 1
                obshistid_unqid_simulated_set.add((alert_data['uniqueId'][i_obj] << obshistid_bits) +
                                                  alert_data['obshistId'][i_obj])

                unq = alert_data['uniqueId'][i_obj]
                obj_dex = (unq//1024)-1
                self.assertAlmostEqual(self.pmra_truth[obj_dex], 0.001*alert_data['pmRA'][i_obj], 4)
                self.assertAlmostEqual(self.pmdec_truth[obj_dex], 0.001*alert_data['pmDec'][i_obj], 4)
                self.assertAlmostEqual(self.px_truth[obj_dex], 0.001*alert_data['parallax'][i_obj], 4)

                ra_truth, dec_truth = applyProperMotion(self.ra_truth[obj_dex], self.dec_truth[obj_dex],
                                                        self.pmra_truth[obj_dex], self.pmdec_truth[obj_dex],
                                                        self.px_truth[obj_dex], self.vrad_truth[obj_dex],
                                                        mjd=mjd_list[i_obj])
                distance = angularSeparation(ra_truth, dec_truth,
                                             alert_data['ra'][i_obj], alert_data['dec'][i_obj])

                distance_arcsec = 3600.0*distance
                msg = '\ntruth: %e %e\nalert: %e %e\n' % (ra_truth, dec_truth,
                                                          alert_data['ra'][i_obj],
                                                          alert_data['dec'][i_obj])

                self.assertLess(distance_arcsec, 0.0005, msg=msg)

                obs = obs_dict[alert_data['obshistId'][i_obj]]


                chipname = chipNameFromRaDec(self.ra_truth[obj_dex], self.dec_truth[obj_dex],
                                             pm_ra=self.pmra_truth[obj_dex],
                                             pm_dec=self.pmdec_truth[obj_dex],
                                             parallax=self.px_truth[obj_dex],
                                             v_rad=self.vrad_truth[obj_dex],
                                             obs_metadata=obs,
                                             camera=self.camera)

                chipnum = int(chipname.replace('R', '').replace('S', '').
                              replace(' ', '').replace(';', '').replace(',', '').
                              replace(':', ''))

                self.assertEqual(chipnum, alert_data['chipNum'][i_obj])

                xpix, ypix = pixelCoordsFromRaDec(self.ra_truth[obj_dex], self.dec_truth[obj_dex],
                                                  pm_ra=self.pmra_truth[obj_dex],
                                                  pm_dec=self.pmdec_truth[obj_dex],
                                                  parallax=self.px_truth[obj_dex],
                                                  v_rad=self.vrad_truth[obj_dex],
                                                  obs_metadata=obs,
                                                  camera=self.camera)

                self.assertAlmostEqual(alert_data['xPix'][i_obj], xpix, 4)
                self.assertAlmostEqual(alert_data['yPix'][i_obj], ypix, 4)

                dmag_sim = -2.5*np.log10(1.0+alert_data['dflux'][i_obj]/alert_data['q_flux'][i_obj])
                self.assertAlmostEqual(true_lc_dict[alert_data['uniqueId'][i_obj]][alert_data['obshistId'][i_obj]],
                                       dmag_sim, 3)

                mag_name = ('u', 'g', 'r', 'i', 'z', 'y')[alert_data['band'][i_obj]]
                m5 = obs.m5[mag_name]

                q_mag = dummy_sed.magFromFlux(alert_data['q_flux'][i_obj])
                self.assertAlmostEqual(self.mag0_truth_dict[alert_data['band'][i_obj]][obj_dex],
                                       q_mag, 4)

                snr, gamma = calcSNR_m5(self.mag0_truth_dict[alert_data['band'][i_obj]][obj_dex],
                                        bp_dict[mag_name],
                                        self.obs_mag_cutoff[alert_data['band'][i_obj]],
                                        phot_params)

                self.assertAlmostEqual(snr/alert_data['q_snr'][i_obj], 1.0, 4)

                tot_mag = self.mag0_truth_dict[alert_data['band'][i_obj]][obj_dex] + \
                          true_lc_dict[alert_data['uniqueId'][i_obj]][alert_data['obshistId'][i_obj]]

                snr, gamma = calcSNR_m5(tot_mag, bp_dict[mag_name],
                                        m5, phot_params)
                self.assertAlmostEqual(snr/alert_data['tot_snr'][i_obj], 1.0, 4)

        for val in obshistid_unqid_set:
            self.assertIn(val, obshistid_unqid_simulated_set)
        self.assertEqual(len(obshistid_unqid_set), len(obshistid_unqid_simulated_set))

        astrometry_query = 'SELECT uniqueId, ra, dec, TAI '
        astrometry_query += 'FROM baseline_astrometry'
        astrometry_dtype = np.dtype([('uniqueId', int),
                                     ('ra', float),
                                     ('dec', float),
                                     ('TAI', float)])

        tai_list = []
        for obs in self.obs_list:
            tai_list.append(obs.mjd.TAI)
        tai_list = np.array(tai_list)

        n_tot_ast_simulated = 0
        for file_name in sqlite_file_list:
            if not file_name.endswith('db'):
                continue
            full_name = os.path.join(self.output_dir, file_name)
            self.assertTrue(os.path.exists(full_name))
            alert_db = DBObject(full_name, driver='sqlite')
            astrometry_data = alert_db.execute_arbitrary(astrometry_query, dtype=astrometry_dtype)

            if len(astrometry_data) == 0:
                continue

            mjd_list = ModifiedJulianDate.get_list(TAI=astrometry_data['TAI'])
            for i_obj in range(len(astrometry_data)):
                n_tot_ast_simulated += 1
                obj_dex = (astrometry_data['uniqueId'][i_obj]//1024) - 1
                ra_truth, dec_truth = applyProperMotion(self.ra_truth[obj_dex], self.dec_truth[obj_dex],
                                                        self.pmra_truth[obj_dex], self.pmdec_truth[obj_dex],
                                                        self.px_truth[obj_dex], self.vrad_truth[obj_dex],
                                                        mjd=mjd_list[i_obj])

                distance = angularSeparation(ra_truth, dec_truth,
                                             astrometry_data['ra'][i_obj],
                                             astrometry_data['dec'][i_obj])

                self.assertLess(3600.0*distance, 0.0005)

        del alert_gen
        gc.collect()
        self.assertGreater(n_tot_simulated, 10)
        self.assertGreater(len(obshistid_unqid_simulated_set), 10)
        self.assertLess(len(obshistid_unqid_simulated_set), n_total_observations)
        self.assertGreater(n_tot_ast_simulated, 0)
            assert np.diff(obs_params['obsHistID']).min()>0

            obsid_query = np.array(htmid_to_obs[htmid_query])
            obs_dex = np.searchsorted(obs_params['obsHistID'].value, obsid_query)
            np.testing.assert_array_equal(obs_params['obsHistID'].value[obs_dex],
                                          obsid_query)

            ra_obs = obs_params['ra'].value[obs_dex]
            dec_obs = obs_params['dec'].value[obs_dex]
            mjd_obs = obs_params['mjd'].value[obs_dex]
            rotsky_obs = obs_params['rotSkyPos'].value[obs_dex]
            filter_obs = obs_params['filter'].value[obs_dex]
            m5_obs = obs_params['m5'].value[obs_dex]

            mjd_obj_list = ModifiedJulianDate.get_list(TAI=mjd_obs)
            obs_md_list = []
            for ii in range(len(ra_obs)):
                obs = ObservationMetaData(pointingRA=ra_obs[ii],
                                          pointingDec=dec_obs[ii],
                                          mjd=mjd_obj_list[ii],
                                          rotSkyPos=rotsky_obs[ii],
                                          bandpassName='ugrizy'[filter_obs[ii]])
                obs_md_list.append(obs)

            print('%d time steps' % len(filter_obs))

            constraint = 'isvar=1 '
            htmid_21_min = htmid_query<<2*(21-query_level)
            htmid_21_max = (htmid_query+1)<<2*(21-query_level)
            constraint += 'AND htmid>=%d AND htmid<%d' % (htmid_21_min, htmid_21_max)
    def test_alert_data_generation(self):

        dmag_cutoff = 0.005
        mag_name_to_int = {'u': 0, 'g': 1, 'r': 2, 'i': 3, 'z' : 4, 'y': 5}

        _max_var_param_str = self.max_str_len

        class StarAlertTestDBObj(StellarAlertDBObjMixin, CatalogDBObject):
            objid = 'star_alert'
            tableid = 'stars'
            idColKey = 'simobjid'
            raColName = 'ra'
            decColName = 'dec'
            objectTypeId = 0
            columns = [('raJ2000', 'ra*0.01745329252'),
                       ('decJ2000', 'dec*0.01745329252'),
                       ('parallax', 'px*0.01745329252/3600.0'),
                       ('properMotionRa', 'pmra*0.01745329252/3600.0'),
                       ('properMotionDec', 'pmdec*0.01745329252/3600.0'),
                       ('radialVelocity', 'vrad'),
                       ('variabilityParameters', 'varParamStr', str, _max_var_param_str)]

        class TestAlertsVarCatMixin(object):

            @register_method('alert_test')
            def applyAlertTest(self, valid_dexes, params, expmjd, variability_cache=None):
                if len(params) == 0:
                    return np.array([[], [], [], [], [], []])

                if isinstance(expmjd, numbers.Number):
                    dmags_out = np.zeros((6, self.num_variable_obj(params)))
                else:
                    dmags_out = np.zeros((6, self.num_variable_obj(params), len(expmjd)))

                for i_star in range(self.num_variable_obj(params)):
                    if params['amp'][i_star] is not None:
                        dmags = params['amp'][i_star]*np.cos(params['per'][i_star]*expmjd)
                        for i_filter in range(6):
                            dmags_out[i_filter][i_star] = dmags

                return dmags_out

        class TestAlertsVarCat(TestAlertsVarCatMixin, AlertStellarVariabilityCatalog):
            pass

        class TestAlertsTruthCat(TestAlertsVarCatMixin, CameraCoordsLSST, AstrometryStars,
                                 Variability, InstanceCatalog):
            column_outputs = ['uniqueId', 'chipName', 'dmagAlert', 'magAlert']

            @compound('delta_umag', 'delta_gmag', 'delta_rmag',
                      'delta_imag', 'delta_zmag', 'delta_ymag')
            def get_TruthVariability(self):
                return self.applyVariability(self.column_by_name('varParamStr'))

            @cached
            def get_dmagAlert(self):
                return self.column_by_name('delta_%smag' % self.obs_metadata.bandpass)

            @cached
            def get_magAlert(self):
                return self.column_by_name('%smag' % self.obs_metadata.bandpass) + \
                       self.column_by_name('dmagAlert')

        star_db = StarAlertTestDBObj(database=self.star_db_name, driver='sqlite')

        # assemble the true light curves for each object; we need to figure out
        # if their np.max(dMag) ever goes over dmag_cutoff; then we will know if
        # we are supposed to simulate them
        true_lc_dict = {}
        true_lc_obshistid_dict = {}
        is_visible_dict = {}
        obs_dict = {}
        max_obshistid = -1
        n_total_observations = 0
        for obs in self.obs_list:
            obs_dict[obs.OpsimMetaData['obsHistID']] = obs
            obshistid = obs.OpsimMetaData['obsHistID']
            if obshistid > max_obshistid:
                max_obshistid = obshistid
            cat = TestAlertsTruthCat(star_db, obs_metadata=obs)

            for line in cat.iter_catalog():
                if line[1] is None:
                    continue

                n_total_observations += 1
                if line[0] not in true_lc_dict:
                    true_lc_dict[line[0]] = {}
                    true_lc_obshistid_dict[line[0]] = []

                true_lc_dict[line[0]][obshistid] = line[2]
                true_lc_obshistid_dict[line[0]].append(obshistid)

                if line[0] not in is_visible_dict:
                    is_visible_dict[line[0]] = False

                if line[3] <= self.obs_mag_cutoff[mag_name_to_int[obs.bandpass]]:
                    is_visible_dict[line[0]] = True

        obshistid_bits = int(np.ceil(np.log(max_obshistid)/np.log(2)))

        skipped_due_to_mag = 0

        objects_to_simulate = []
        obshistid_unqid_set = set()
        for obj_id in true_lc_dict:

            dmag_max = -1.0
            for obshistid in true_lc_dict[obj_id]:
                if np.abs(true_lc_dict[obj_id][obshistid]) > dmag_max:
                    dmag_max = np.abs(true_lc_dict[obj_id][obshistid])

            if dmag_max >= dmag_cutoff:
                if not is_visible_dict[obj_id]:
                    skipped_due_to_mag += 1
                    continue

                objects_to_simulate.append(obj_id)
                for obshistid in true_lc_obshistid_dict[obj_id]:
                    obshistid_unqid_set.add((obj_id << obshistid_bits) + obshistid)

        self.assertGreater(len(objects_to_simulate), 10)
        self.assertGreater(skipped_due_to_mag, 0)

        log_file_name = tempfile.mktemp(dir=self.output_dir, suffix='log.txt')
        alert_gen = AlertDataGenerator(testing=True)

        alert_gen.subdivide_obs(self.obs_list, htmid_level=6)

        for htmid in alert_gen.htmid_list:
            alert_gen.alert_data_from_htmid(htmid, star_db,
                                            photometry_class=TestAlertsVarCat,
                                            output_prefix='alert_test',
                                            output_dir=self.output_dir,
                                            dmag_cutoff=dmag_cutoff,
                                            log_file_name=log_file_name)

        dummy_sed = Sed()

        bp_dict = BandpassDict.loadTotalBandpassesFromFiles()

        phot_params = PhotometricParameters()

        # First, verify that the contents of the sqlite files are all correct

        n_tot_simulated = 0

        alert_query = 'SELECT alert.uniqueId, alert.obshistId, meta.TAI, '
        alert_query += 'meta.band, quiescent.flux, alert.dflux, '
        alert_query += 'quiescent.snr, alert.snr, '
        alert_query += 'alert.ra, alert.dec, alert.chipNum, '
        alert_query += 'alert.xPix, alert.yPix, ast.pmRA, ast.pmDec, '
        alert_query += 'ast.parallax '
        alert_query += 'FROM alert_data AS alert '
        alert_query += 'INNER JOIN metadata AS meta ON meta.obshistId=alert.obshistId '
        alert_query += 'INNER JOIN quiescent_flux AS quiescent '
        alert_query += 'ON quiescent.uniqueId=alert.uniqueId '
        alert_query += 'AND quiescent.band=meta.band '
        alert_query += 'INNER JOIN baseline_astrometry AS ast '
        alert_query += 'ON ast.uniqueId=alert.uniqueId'

        alert_dtype = np.dtype([('uniqueId', int), ('obshistId', int),
                                ('TAI', float), ('band', int),
                                ('q_flux', float), ('dflux', float),
                                ('q_snr', float), ('tot_snr', float),
                                ('ra', float), ('dec', float),
                                ('chipNum', int), ('xPix', float), ('yPix', float),
                                ('pmRA', float), ('pmDec', float), ('parallax', float)])

        sqlite_file_list = os.listdir(self.output_dir)

        n_tot_simulated = 0
        obshistid_unqid_simulated_set = set()
        for file_name in sqlite_file_list:
            if not file_name.endswith('db'):
                continue
            full_name = os.path.join(self.output_dir, file_name)
            self.assertTrue(os.path.exists(full_name))
            alert_db = DBObject(full_name, driver='sqlite')
            alert_data = alert_db.execute_arbitrary(alert_query, dtype=alert_dtype)
            if len(alert_data) == 0:
                continue

            mjd_list = ModifiedJulianDate.get_list(TAI=alert_data['TAI'])
            for i_obj in range(len(alert_data)):
                n_tot_simulated += 1
                obshistid_unqid_simulated_set.add((alert_data['uniqueId'][i_obj] << obshistid_bits) +
                                                  alert_data['obshistId'][i_obj])

                unq = alert_data['uniqueId'][i_obj]
                obj_dex = (unq//1024)-1
                self.assertAlmostEqual(self.pmra_truth[obj_dex], 0.001*alert_data['pmRA'][i_obj], 4)
                self.assertAlmostEqual(self.pmdec_truth[obj_dex], 0.001*alert_data['pmDec'][i_obj], 4)
                self.assertAlmostEqual(self.px_truth[obj_dex], 0.001*alert_data['parallax'][i_obj], 4)

                ra_truth, dec_truth = applyProperMotion(self.ra_truth[obj_dex], self.dec_truth[obj_dex],
                                                        self.pmra_truth[obj_dex], self.pmdec_truth[obj_dex],
                                                        self.px_truth[obj_dex], self.vrad_truth[obj_dex],
                                                        mjd=mjd_list[i_obj])
                distance = angularSeparation(ra_truth, dec_truth,
                                             alert_data['ra'][i_obj], alert_data['dec'][i_obj])

                distance_arcsec = 3600.0*distance
                msg = '\ntruth: %e %e\nalert: %e %e\n' % (ra_truth, dec_truth,
                                                          alert_data['ra'][i_obj],
                                                          alert_data['dec'][i_obj])

                self.assertLess(distance_arcsec, 0.0005, msg=msg)

                obs = obs_dict[alert_data['obshistId'][i_obj]]

                chipname = chipNameFromRaDecLSST(self.ra_truth[obj_dex], self.dec_truth[obj_dex],
                                                 pm_ra=self.pmra_truth[obj_dex],
                                                 pm_dec=self.pmdec_truth[obj_dex],
                                                 parallax=self.px_truth[obj_dex],
                                                 v_rad=self.vrad_truth[obj_dex],
                                                 obs_metadata=obs,
                                                 band=obs.bandpass)

                chipnum = int(chipname.replace('R', '').replace('S', '').
                              replace(' ', '').replace(';', '').replace(',', '').
                              replace(':', ''))

                self.assertEqual(chipnum, alert_data['chipNum'][i_obj])

                xpix, ypix = pixelCoordsFromRaDecLSST(self.ra_truth[obj_dex], self.dec_truth[obj_dex],
                                                      pm_ra=self.pmra_truth[obj_dex],
                                                      pm_dec=self.pmdec_truth[obj_dex],
                                                      parallax=self.px_truth[obj_dex],
                                                      v_rad=self.vrad_truth[obj_dex],
                                                      obs_metadata=obs,
                                                      band=obs.bandpass)

                self.assertAlmostEqual(alert_data['xPix'][i_obj], xpix, 4)
                self.assertAlmostEqual(alert_data['yPix'][i_obj], ypix, 4)

                dmag_sim = -2.5*np.log10(1.0+alert_data['dflux'][i_obj]/alert_data['q_flux'][i_obj])
                self.assertAlmostEqual(true_lc_dict[alert_data['uniqueId'][i_obj]][alert_data['obshistId'][i_obj]],
                                       dmag_sim, 3)

                mag_name = ('u', 'g', 'r', 'i', 'z', 'y')[alert_data['band'][i_obj]]
                m5 = obs.m5[mag_name]

                q_mag = dummy_sed.magFromFlux(alert_data['q_flux'][i_obj])
                self.assertAlmostEqual(self.mag0_truth_dict[alert_data['band'][i_obj]][obj_dex],
                                       q_mag, 4)

                snr, gamma = calcSNR_m5(self.mag0_truth_dict[alert_data['band'][i_obj]][obj_dex],
                                        bp_dict[mag_name],
                                        self.obs_mag_cutoff[alert_data['band'][i_obj]],
                                        phot_params)

                self.assertAlmostEqual(snr/alert_data['q_snr'][i_obj], 1.0, 4)

                tot_mag = self.mag0_truth_dict[alert_data['band'][i_obj]][obj_dex] + \
                          true_lc_dict[alert_data['uniqueId'][i_obj]][alert_data['obshistId'][i_obj]]

                snr, gamma = calcSNR_m5(tot_mag, bp_dict[mag_name],
                                        m5, phot_params)
                self.assertAlmostEqual(snr/alert_data['tot_snr'][i_obj], 1.0, 4)

        for val in obshistid_unqid_set:
            self.assertIn(val, obshistid_unqid_simulated_set)
        self.assertEqual(len(obshistid_unqid_set), len(obshistid_unqid_simulated_set))

        astrometry_query = 'SELECT uniqueId, ra, dec, TAI '
        astrometry_query += 'FROM baseline_astrometry'
        astrometry_dtype = np.dtype([('uniqueId', int),
                                     ('ra', float),
                                     ('dec', float),
                                     ('TAI', float)])

        tai_list = []
        for obs in self.obs_list:
            tai_list.append(obs.mjd.TAI)
        tai_list = np.array(tai_list)

        n_tot_ast_simulated = 0
        for file_name in sqlite_file_list:
            if not file_name.endswith('db'):
                continue
            full_name = os.path.join(self.output_dir, file_name)
            self.assertTrue(os.path.exists(full_name))
            alert_db = DBObject(full_name, driver='sqlite')
            astrometry_data = alert_db.execute_arbitrary(astrometry_query, dtype=astrometry_dtype)

            if len(astrometry_data) == 0:
                continue

            mjd_list = ModifiedJulianDate.get_list(TAI=astrometry_data['TAI'])
            for i_obj in range(len(astrometry_data)):
                n_tot_ast_simulated += 1
                obj_dex = (astrometry_data['uniqueId'][i_obj]//1024) - 1
                ra_truth, dec_truth = applyProperMotion(self.ra_truth[obj_dex], self.dec_truth[obj_dex],
                                                        self.pmra_truth[obj_dex], self.pmdec_truth[obj_dex],
                                                        self.px_truth[obj_dex], self.vrad_truth[obj_dex],
                                                        mjd=mjd_list[i_obj])

                distance = angularSeparation(ra_truth, dec_truth,
                                             astrometry_data['ra'][i_obj],
                                             astrometry_data['dec'][i_obj])

                self.assertLess(3600.0*distance, 0.0005)

        del alert_gen
        gc.collect()
        self.assertGreater(n_tot_simulated, 10)
        self.assertGreater(len(obshistid_unqid_simulated_set), 10)
        self.assertLess(len(obshistid_unqid_simulated_set), n_total_observations)
        self.assertGreater(n_tot_ast_simulated, 0)