def deldataset(options):
from tkp.db.database import Database
from tkp.db.model import Dataset
from sqlalchemy.orm.exc import NoResultFound
dbconfig = get_db_config()
if not options.yes:
print("\nThis script will delete dataset {} with "
"these settings:".format(options.id))
print("")
print("\tengine: " + (dbconfig['engine'] or ""))
print("\tdatabase: " + (dbconfig['database'] or ""))
print("\tuser: "******""))
print("\tpassword: "******""))
print("\thost: " + (dbconfig['host'] or ""))
print("\tport: " + str(dbconfig['port']))
answer = raw_input("\nAre you sure you want to delete dataset {}? "
"[y/N]: ".format(options.id))
if answer.lower() != 'y':
sys.stderr.write("Aborting.\n")
sys.exit(1)
db = Database(**dbconfig)
try:
dataset = db.session.query(Dataset).filter(Dataset.id==options.id).one()
except NoResultFound:
print("\ndataset {} does not exist!\n".format(options.id))
sys.exit(1)
db.session.delete(dataset)
db.session.commit()
print("\ndataset {} has been deleted!\n".format(options.id))
db.close()
def deldataset(options):
from tkp.db.database import Database
from tkp.db.model import Dataset
from sqlalchemy.orm.exc import NoResultFound
dbconfig = get_db_config()
if not options.yes:
print("\nThis script will delete dataset {} with "
"these settings:".format(options.id))
print("")
print("\tengine: " + (dbconfig['engine'] or ""))
print("\tdatabase: " + (dbconfig['database'] or ""))
print("\tuser: "******""))
print("\tpassword: "******""))
print("\thost: " + (dbconfig['host'] or ""))
print("\tport: " + str(dbconfig['port']))
answer = raw_input("\nAre you sure you want to delete dataset {}? "
"[y/N]: ".format(options.id))
if answer.lower() != 'y':
sys.stderr.write("Aborting.\n")
sys.exit(1)
db = Database(**dbconfig)
try:
dataset = db.session.query(Dataset).filter(
Dataset.id == options.id).one()
except NoResultFound:
print("\ndataset {} does not exist!\n".format(options.id))
sys.exit(1)
db.session.delete(dataset)
db.session.commit()
print("\ndataset {} has been deleted!\n".format(options.id))
db.close()
class TestVersion(unittest.TestCase):
def setUp(self):
self.database = Database()
self.database.connect()
def test_version(self):
session = self.database.Session()
v = session.query(Version).filter(Version.name == 'revision').one()
self.assertEqual(v.value, SCHEMA_VERSION)
def execute(query, parameters={}, commit=False):
"""
A generic wrapper for doing any query to the database
:param query: the query string
:param parameters: The query parameters. These will be converted and escaped.
:param commit: should a commit be performed afterwards, boolean
:returns: a database cursor object
"""
database = Database()
return database.execute(query, parameters=parameters, commit=commit)
def execute(query, parameters={}, commit=False):
"""
A generic wrapper for doing any query to the database
:param query: the query string
:param parameters: The query parameters. These will be converted and escaped.
:param commit: should a commit be performed afterwards, boolean
:returns: a database cursor object
"""
database = Database()
return database.execute(query, parameters=parameters, commit=commit)
def store_images(images_metadata, extraction_radius_pix, dataset_id):
""" Add images to database.
Note that all images in one dataset should be inserted in one go, since the
order is very important here. If you don't add them all in once, you should
make sure they are added in the correct order e.g. sorted by observation
time.
Note: Should only be used in a master recipe
Args:
images_metadata: list of dicts containing image metadata
extraction_radius_pix: (float) Used to calculate the 'skyregion'
dataset_id: dataset id to be used. don't use value from parset file
since this can be -1 (TraP way of setting auto increment)
Returns:
the database ID of this dataset
"""
database = Database()
dataset = DataSet(id=dataset_id, database=database)
image_ids = []
# sort images by timestamp
images_metadata.sort(key=lambda m: m['taustart_ts'])
for metadata in images_metadata:
metadata['xtr_radius'] = extraction_radius_pix * abs(
metadata['deltax'])
filename = metadata['url']
db_image = Image(data=metadata, dataset=dataset)
image_ids.append(db_image.id)
logger.info("stored %s with ID %s" %
(os.path.basename(filename), db_image.id))
return image_ids
def connection():
"""
A generic wrapper to create a connection to the database if
it does not exist
"""
database = Database()
return database.connection
def __init__(self, data=None, database=None, id=None):
"""If id is supplied, the data and image arguments are ignored."""
super(DataSet, self).__init__(
data=data, database=database, id=id)
self.images = set()
if not self.database:
self.database = Database()
self._init_data()
def commit():
"""
A generic wrapper to commit a query transaction
It saves the changes involved by a transaction
"""
database = Database()
return database.connection.commit()
def rollback():
"""
A generic wrapper to rollback a query transaction
Undo changes involved by a transaction that have not been saved
"""
database = Database()
return database.connection.rollback()
def get_newsources_for_dataset(dsid):
"""
Returns dicts representing all newsources for this dataset.
Args:
dsid: Dataset id
Returns:
list: (list of dicts) Each dict represents one newsource.
The dict keys are all the columns in the newsources table, plus
the 'taustart_ts' from the image table, which represents the
trigger time.
"""
qry = """\
SELECT tr.id
,tr.previous_limits_image
,rc.id as runcat_id
,img.taustart_ts
,img.band
,ax.v_int
,ax.eta_int
, ((ex.f_peak - limits_image.detection_thresh*limits_image.rms_min)
/ limits_image.rms_min) AS low_thresh_sigma
, ((ex.f_peak - limits_image.detection_thresh*limits_image.rms_max)
/ limits_image.rms_max) AS high_thresh_sigma
FROM newsource tr
,runningcatalog rc
,extractedsource ex
,image img
,assocxtrsource ax
,image limits_image
WHERE rc.dataset = %(dsid)s
AND tr.runcat = rc.id
AND tr.trigger_xtrsrc = ex.id
AND ex.image = img.id
AND ax.runcat = rc.id
AND ax.xtrsrc = ex.id
AND tr.previous_limits_image = limits_image.id
"""
cursor = Database().connection.cursor()
cursor.execute(qry, {'dsid':dsid})
newsource_rows_for_dataset = get_db_rows_as_dicts(cursor)
return newsource_rows_for_dataset
def get_newsources_for_dataset(dsid):
"""
Returns dicts representing all newsources for this dataset.
Args:
dsid: Dataset id
Returns:
list: (list of dicts) Each dict represents one newsource.
The dict keys are all the columns in the newsources table, plus
the 'taustart_ts' from the image table, which represents the
trigger time.
"""
qry = """\
SELECT tr.id
,tr.previous_limits_image
,rc.id as runcat_id
,img.taustart_ts
,img.band
,ax.v_int
,ax.eta_int
, ((ex.f_peak - limits_image.detection_thresh*limits_image.rms_min)
/ limits_image.rms_min) AS low_thresh_sigma
, ((ex.f_peak - limits_image.detection_thresh*limits_image.rms_max)
/ limits_image.rms_max) AS high_thresh_sigma
FROM newsource tr
,runningcatalog rc
,extractedsource ex
,image img
,assocxtrsource ax
,image limits_image
WHERE rc.dataset = %(dsid)s
AND tr.runcat = rc.id
AND tr.trigger_xtrsrc = ex.id
AND ex.image = img.id
AND ax.runcat = rc.id
AND ax.xtrsrc = ex.id
AND tr.previous_limits_image = limits_image.id
"""
cursor = Database().cursor
cursor.execute(qry, {'dsid':dsid})
newsource_rows_for_dataset = get_db_rows_as_dicts(cursor)
return newsource_rows_for_dataset
def deldataset(options):
if not options.yes:
answer = raw_input("\nAre you sure you want to delete dataset {}? "
"[y/N]: ".format(options.id))
if answer.lower() != 'y':
sys.stderr.write("Aborting.\n")
sys.exit(1)
from tkp.db.database import Database
from tkp.db.model import Dataset
from sqlalchemy.orm.exc import NoResultFound
db = Database()
try:
dataset = db.session.query(Dataset).filter(Dataset.id==options.id).one()
except NoResultFound:
print("\ndataset {} does not exist!\n".format(options.id))
sys.exit(1)
db.session.delete(dataset)
db.session.commit()
print("\ndataset {} has been deleted!\n".format(options.id))
db.close()
def create_dataset(dataset_id, description):
""" Creates a dataset if it doesn't exists
Note: Should only be used in a master recipe
Returns:
the database ID of this dataset
"""
database = Database()
if dataset_id == -1:
dataset = DataSet({'description': description}, database)
logger.info("created dataset %s (%s)" %
(dataset.id, dataset.description))
else:
dataset = DataSet(id=dataset_id, database=database)
logger.info("using dataset %s (%s)" %
(dataset.id, dataset.description))
return dataset.id
def __init__(self, data=None, dataset=None, database=None, id=None):
"""If id is supplied, the data and image arguments are ignored."""
super(Image, self).__init__(data=data, database=database, id=id)
# Special part to deal when a DataSet() is supplied
self.dataset = dataset
self.rejected = False
if self.dataset:
if self.dataset.database and not self.database:
self.database = self.dataset.database
self.dataset.images.add(self)
self._data.setdefault('dataset', self.dataset.id)
self.sources = set()
if not self.database:
self.database = Database()
self._init_data()
if not self.dataset:
self.dataset = DataSet(id=self._data['dataset'], database=self.database)
self.update_rejected()
def test_delete_dataset(self):
db = Database()
dataset = gen_dataset('delete test')
band = gen_band(dataset)
skyregion = gen_skyregion(dataset)
image = gen_image(band, dataset, skyregion)
extractedsource = gen_extractedsource(image)
runningcatalog = gen_runningcatalog(extractedsource, dataset)
db.session.add_all((
dataset,
band,
skyregion,
image,
extractedsource,
runningcatalog,
))
db.session.flush()
db.session.delete(dataset)
db.session.flush()
images = db.session.query(Image).filter(Image.dataset == dataset).all()
self.assertEqual(len(images), 0)
def execute(query, parameters={}, commit=False):
"""
A generic wrapper for doing any query to the database
:param query: the query string
:param parameters: The query parameters. These will be converted and escaped.
:param commit: should a commit be performed afterwards, boolean
:returns: a database cursor object
"""
#logger.info('executing query\n%s' % query % parameters)
database = Database()
cursor = database.connection.cursor()
try:
cursor.execute(query, sanitize_db_inputs(parameters))
if commit:
database.connection.commit()
except database.connection.Error as e:
logger.error("Query failed: %s. Query: %s." % (e, query % parameters))
raise
except Exception as e:
logger.error("Big problem here: %s" % e)
raise
return cursor
def setUp(self):
self.db = Database()
self.image = gen_image()
self.db.session.add(self.image)
self.db.session.flush()
def connect():
"""
A generic wrapper to connect to the configured database
"""
database = Database()
return database.connect()
def connect():
database = Database()
return database.connect()
def rollback():
database = Database()
return database.rollback()
def configure(*args, **kwargs):
database = Database()
return database.configure(*args, **kwargs)
def rollback():
database = Database()
return database.rollback()
def setUpClass(cls):
cls.database = Database()
cls.database.connect()
def setUp(self):
self.database = Database()
def setUp(self):
self.database = Database()
self.database.connect()
def connect():
"""
A generic wrapper to connect to the configured database
"""
database = Database()
return database.connect()
