def point_meta_from_submit_form(form, is_approved):
columns, labels = form_columns(form)
name = slugify(form['dataset_name'], delimiter='_')[:50]
metatable = MetaTable(
url=form['file_url'],
view_url=form.get('view_url'),
dataset_name=name,
human_name=form['dataset_name'],
attribution=form.get('dataset_attribution'),
description=form.get('dataset_description'),
update_freq=form['update_frequency'],
contributor_name=form['contributor_name'],
contributor_organization=form.get('contributor_organization'),
contributor_email=form['contributor_email'],
approved_status=is_approved,
observed_date=labels['observed_date'],
latitude=labels.get('latitude', None),
longitude=labels.get('longitude', None),
location=labels.get('location', None),
column_names=columns)
postgres_session.add(metatable)
postgres_session.commit()
return metatable
def update_meta(metatable, table):
"""
After ingest/update, update the metatable registry to reflect table information.
:param metatable: MetaTable instance to update.
:param table: Table instance to update from.
:returns: None
"""
metatable.update_date_added()
metatable.obs_from, metatable.obs_to = postgres_session.query(
func.min(table.c.point_date),
func.max(table.c.point_date)
).first()
metatable.bbox = postgres_session.query(
func.ST_SetSRID(
func.ST_Envelope(func.ST_Union(table.c.geom)),
4326
)
).first()[0]
metatable.column_names = {
c.name: str(c.type) for c in metatable.column_info()
if c.name not in {'geom', 'point_date', 'hash'}
}
postgres_session.add(metatable)
postgres_session.commit()
def edit_shape(dataset_name):
form = EditShapeForm()
meta = postgres_session.query(ShapeMetadata).get(dataset_name)
if form.validate_on_submit():
upd = {
'human_name': form.human_name.data,
'description': form.description.data,
'attribution': form.attribution.data,
'update_freq': form.update_freq.data,
}
postgres_session.query(ShapeMetadata) \
.filter(ShapeMetadata.dataset_name == meta.dataset_name) \
.update(upd)
postgres_session.commit()
if not meta.approved_status:
approve_shape(dataset_name)
flash('%s updated successfully!' % meta.human_name, 'success')
return redirect(url_for('views.view_datasets'))
else:
pass
num_rows = meta.num_shapes if meta.num_shapes else 0
context = {
'form': form,
'meta': meta,
'num_rows': num_rows
}
return render_template('admin/edit-shape.html', **context)
def edit_shape(dataset_name):
form = EditShapeForm()
meta = postgres_session.query(ShapeMetadata).get(dataset_name)
if form.validate_on_submit():
upd = {
'human_name': form.human_name.data,
'description': form.description.data,
'attribution': form.attribution.data,
'update_freq': form.update_freq.data,
}
postgres_session.query(ShapeMetadata) \
.filter(ShapeMetadata.dataset_name == meta.dataset_name) \
.update(upd)
postgres_session.commit()
if not meta.approved_status:
approve_shape(dataset_name)
flash('%s updated successfully!' % meta.human_name, 'success')
return redirect(url_for('views.view_datasets'))
else:
pass
num_rows = meta.num_shapes if meta.num_shapes else 0
context = {'form': form, 'meta': meta, 'num_rows': num_rows}
return render_template('admin/edit-shape.html', **context)
def on_model_change(self, form, model, is_created):
network = form.sensor_network.data
validate_node(network)
network_obj = postgres_session.query(NetworkMeta).filter(
NetworkMeta.name == network).first()
network_obj.nodes.append(model)
postgres_session.commit()
def ingest_point_fixture(fixture_meta, fname):
md = MetaTable(**fixture_meta)
postgres_session.add(md)
postgres_session.commit()
path = os.path.join(fixtures_path, fname)
point_etl = PlenarioETL(md, source_path=path)
point_etl.add()
def point_meta_from_submit_form(form, is_approved):
columns, labels = form_columns(form)
name = slugify(form['dataset_name'], delimiter='_')[:50]
metatable = MetaTable(
url=form['file_url'],
view_url=form.get('view_url'),
dataset_name=name,
human_name=form['dataset_name'],
attribution=form.get('dataset_attribution'),
description=form.get('dataset_description'),
update_freq=form['update_frequency'],
contributor_name=form['contributor_name'],
contributor_organization=form.get('contributor_organization'),
contributor_email=form['contributor_email'],
approved_status=is_approved,
observed_date=labels['observed_date'],
latitude=labels.get('latitude', None),
longitude=labels.get('longitude', None),
location=labels.get('location', None),
column_names=columns
)
postgres_session.add(metatable)
postgres_session.commit()
return metatable
def update_dataset_view(source_url_hash):
meta = postgres_session.query(MetaTable).get(source_url_hash)
ticket = worker.update_dataset.delay(meta.dataset_name).id
meta.result_ids = [ticket]
postgres_session.add(meta)
postgres_session.commit()
return redirect(url_for('views.view_datasets'))
def update_dataset_view(source_url_hash):
meta = postgres_session.query(MetaTable).get(source_url_hash)
ticket = worker.update_dataset.delay(meta.dataset_name).id
meta.result_ids = [ticket]
postgres_session.add(meta)
postgres_session.commit()
return redirect(url_for('views.view_datasets'))
def init():
"""Initialize the database.
"""
# TODO(heyzoos)
# Check for dependencies to fail fast and helpfully before running:
# - postgresql-client
base_uri = DATABASE_CONN.rsplit('/', 1)[0]
base_engine = create_engine(base_uri)
connection_attempts = 6
interval = 10
for connection_attempt in range(0, connection_attempts):
try:
create_database(base_engine, DB_NAME)
break
except ProgrammingError:
logger.debug('[plenario] It already exists!')
break
except sqlalchemy.exc.OperationalError:
logger.debug('[plenario] Database has not started yet.')
sleep(interval)
try:
create_extension(plenario_engine, 'postgis')
create_extension(plenario_engine, 'plv8')
except ProgrammingError:
logger.debug('[plenario] It already exists!')
logger.debug('[plenario] Creating metadata tables')
postgres_base.metadata.create_all()
logger.debug('[plenario] Creating weather tables')
WeatherStationsETL().make_station_table()
WeatherETL().make_tables()
# Set up custom functions, triggers and views in postgres
psql('./plenario/dbscripts/sensor_tree.sql')
psql('./plenario/dbscripts/point_from_location.sql')
# Set up the default user if we are running in anything but production
if os.environ.get('CONFIG') != 'prod':
logger.debug('[plenario] Create default user')
user = User(**DEFAULT_USER)
try:
postgres_session.add(user)
postgres_session.commit()
except IntegrityError:
logger.debug('[plenario] Already exists!')
postgres_session.rollback()
# This will get celery to set up its meta tables
try:
health.delay()
except OperationalError:
logger.debug('[plenario] Redis is not running!')
def edit_dataset(source_url_hash):
form = EditDatasetForm()
meta = postgres_session.query(MetaTable).get(source_url_hash)
fieldnames = meta.column_names
num_rows = 0
if meta.approved_status:
try:
table_name = meta.dataset_name
table = Table(table_name,
postgres_base.metadata,
autoload=True,
autoload_with=engine)
# Would prefer to just get the names from the metadata
# without needing to reflect.
fieldnames = list(table.columns.keys())
pk_name = [p.name for p in table.primary_key][0]
pk = table.c[pk_name]
num_rows = postgres_session.query(pk).count()
except NoSuchTableError:
# dataset has been approved, but perhaps still processing.
pass
if form.validate_on_submit():
upd = {
'human_name': form.human_name.data,
'description': form.description.data,
'attribution': form.attribution.data,
'update_freq': form.update_freq.data,
'latitude': form.latitude.data,
'longitude': form.longitude.data,
'location': form.location.data,
'observed_date': form.observed_date.data,
}
postgres_session.query(MetaTable) \
.filter(MetaTable.source_url_hash == meta.source_url_hash) \
.update(upd)
postgres_session.commit()
if not meta.approved_status:
approve_dataset(source_url_hash)
flash('%s updated successfully!' % meta.human_name, 'success')
return redirect(url_for('views.view_datasets'))
else:
pass
context = {
'form': form,
'meta': meta,
'fieldnames': fieldnames,
'num_rows': num_rows,
}
return render_template('admin/edit-dataset.html', **context)
def approve_dataset(source_url_hash):
meta = postgres_session.query(MetaTable).get(source_url_hash)
ticket = worker.add_dataset.delay(meta.dataset_name).id
meta.approved_status = True
meta.result_ids = [ticket]
postgres_session.commit()
send_approval_email(meta.human_name, meta.contributor_name,
meta.contributor_email)
def ingest_fixture(fixture):
# Add the fixture to the metadata first
shape_meta = ShapeMetadata.add(human_name=fixture.human_name,
source_url=None,
update_freq=fixture.update_freq,
approved_status=False)
postgres_session.commit()
# Bypass the celery task and call on a ShapeETL directly
ShapeETL(meta=shape_meta, source_path=fixture.path).add()
return shape_meta
def approve_shape(dataset_name):
meta = postgres_session.query(ShapeMetadata).get(dataset_name)
ticket = worker.add_shape.delay(dataset_name).id
meta.approved_status = True
meta.celery_task_id = ticket
postgres_session.commit()
send_approval_email(meta.human_name, meta.contributor_name,
meta.contributor_email)
def edit_dataset(source_url_hash):
form = EditDatasetForm()
meta = postgres_session.query(MetaTable).get(source_url_hash)
fieldnames = meta.column_names
num_rows = 0
if meta.approved_status:
try:
table_name = meta.dataset_name
table = Table(table_name, postgres_base.metadata,
autoload=True, autoload_with=engine)
# Would prefer to just get the names from the metadata
# without needing to reflect.
fieldnames = list(table.columns.keys())
pk_name = [p.name for p in table.primary_key][0]
pk = table.c[pk_name]
num_rows = postgres_session.query(pk).count()
except NoSuchTableError:
# dataset has been approved, but perhaps still processing.
pass
if form.validate_on_submit():
upd = {
'human_name': form.human_name.data,
'description': form.description.data,
'attribution': form.attribution.data,
'update_freq': form.update_freq.data,
'latitude': form.latitude.data,
'longitude': form.longitude.data,
'location': form.location.data,
'observed_date': form.observed_date.data,
}
postgres_session.query(MetaTable) \
.filter(MetaTable.source_url_hash == meta.source_url_hash) \
.update(upd)
postgres_session.commit()
if not meta.approved_status:
approve_dataset(source_url_hash)
flash('%s updated successfully!' % meta.human_name, 'success')
return redirect(url_for('views.view_datasets'))
else:
pass
context = {
'form': form,
'meta': meta,
'fieldnames': fieldnames,
'num_rows': num_rows,
}
return render_template('admin/edit-dataset.html', **context)
def setUp(self):
postgres_session.rollback()
# Ensure we have metadata loaded into the database
# to mimic the behavior of metadata ingestion preceding file ingestion.
drop_meta('dog_park_permits')
drop_meta('community_radio_events')
drop_meta('public_opera_performances')
# Make new MetaTable objects
self.unloaded_meta = MetaTable(url='nightvale.gov/events.csv',
human_name='Community Radio Events',
business_key='Event Name',
observed_date='Date',
latitude='lat', longitude='lon',
approved_status=True)
self.existing_meta = MetaTable(url='nightvale.gov/dogpark.csv',
human_name='Dog Park Permits',
business_key='Hooded Figure ID',
observed_date='Date',
latitude='lat', longitude='lon',
approved_status=False)
self.opera_meta = MetaTable(url='nightvale.gov/opera.csv',
human_name='Public Opera Performances',
business_key='Event Name',
observed_date='Date',
location='Location',
approved_status=False)
postgres_session.add_all([self.existing_meta, self.opera_meta, self.unloaded_meta])
postgres_session.commit()
# Also, let's have one table pre-loaded...
self.existing_table = sa.Table('dog_park_permits', MetaData(),
Column('hooded_figure_id', Integer),
Column('point_date', TIMESTAMP, nullable=False),
Column('date', Date, nullable=True),
Column('lat', Float, nullable=False),
Column('lon', Float, nullable=False),
Column('hash', String(32), primary_key=True),
Column('geom', Geometry('POINT', srid=4326), nullable=True))
drop_if_exists(self.existing_table.name)
self.existing_table.create(bind=postgres_engine)
# ... with some pre-existing data
ins = self.existing_table.insert().values(hooded_figure_id=1,
point_date=date(2015, 1, 2),
lon=-87.6495076896,
lat=41.7915865543,
geom=None,
hash='addde9be7f59e95fc08e54e29b2a947f')
postgres_engine.execute(ins)
def approve_shape(dataset_name):
meta = postgres_session.query(ShapeMetadata).get(dataset_name)
ticket = worker.add_shape.delay(dataset_name).id
meta.approved_status = True
meta.celery_task_id = ticket
postgres_session.commit()
send_approval_email(
meta.human_name,
meta.contributor_name,
meta.contributor_email
)
def approve_dataset(source_url_hash):
meta = postgres_session.query(MetaTable).get(source_url_hash)
ticket = worker.add_dataset.delay(meta.dataset_name).id
meta.approved_status = True
meta.result_ids = [ticket]
postgres_session.commit()
send_approval_email(
meta.human_name,
meta.contributor_name,
meta.contributor_email
)
def shape_meta_from_submit_form(form, is_approved):
md = ShapeMetadata.add(
human_name=form['dataset_name'],
source_url=form['file_url'],
view_url=form.get('view_url'),
attribution=form.get('dataset_attribution'),
description=form.get('dataset_description'),
update_freq=form['update_frequency'],
contributor_name=form['contributor_name'],
contributor_organization=form.get('contributor_organization'),
contributor_email=form['contributor_email'],
approved_status=is_approved)
postgres_session.commit()
return md
def reset_password():
form = ResetPasswordForm()
errors = []
if form.validate_on_submit():
user = db_session.query(User).get(flask_session['user_id'])
check = user.check_password(user.name, form.old_password.data)
if check:
user.password = form.new_password.data
db_session.add(user)
db_session.commit()
flash('Password reset successful!', 'success')
else:
errors.append('Password is not correct')
return render_template('admin/reset-password.html', form=form, errors=errors)
def shape_meta_from_submit_form(form, is_approved):
md = ShapeMetadata.add(
human_name=form['dataset_name'],
source_url=form['file_url'],
view_url=form.get('view_url'),
attribution=form.get('dataset_attribution'),
description=form.get('dataset_description'),
update_freq=form['update_frequency'],
contributor_name=form['contributor_name'],
contributor_organization=form.get('contributor_organization'),
contributor_email=form['contributor_email'],
approved_status=is_approved)
postgres_session.commit()
return md
def ingest_shapes(cls):
fixtures = [f for k, f in shape_fixtures.items() if k != 'changed_neighborhoods']
fixture_table_names = [f.table_name for f in fixtures]
drop_tables(fixture_table_names)
postgres_session.commit()
for fixture in fixtures:
cls.ingest_fixture(fixture)
# Add a dummy dataset to the metadata without ingesting a shapefile for it
cls.dummy_name = ShapeMetadata.add(human_name='Dummy Name',
source_url=None,
update_freq='yearly',
approved_status=False).dataset_name
postgres_session.commit()
def add_user():
form = AddUserForm()
if form.validate_on_submit():
user_info = {
'name': form.name.data,
'email': form.email.data,
'password': form.password.data
}
user = User(**user_info)
db_session.add(user)
db_session.commit()
context = {
'form': form,
'name': form.name.data,
'email': form.email.data,
'users': db_session.query(User).all()
}
return render_template('admin/add-user.html', **context)
def add(self):
staging_name = 'staging_{}'.format(self.table_name)
with ETLFile(self.source_path, self.source_url, interpret_as='bytes') as file_helper:
handle = open(file_helper.handle.name, "rb")
with zipfile.ZipFile(handle) as shapefile_zip:
import_shapefile(shapefile_zip, staging_name)
add_unique_hash(staging_name)
try:
postgres_engine.execute('drop table {}'.format(self.table_name))
except ProgrammingError:
pass
rename_table = 'alter table {} rename to {}'
rename_table = rename_table.format(staging_name, self.table_name)
postgres_engine.execute(rename_table)
self.meta.update_after_ingest()
postgres_session.commit()
def add(self):
staging_name = 'staging_{}'.format(self.table_name)
with ETLFile(self.source_path, self.source_url,
interpret_as='bytes') as file_helper:
handle = open(file_helper.handle.name, "rb")
with zipfile.ZipFile(handle) as shapefile_zip:
import_shapefile(shapefile_zip, staging_name)
add_unique_hash(staging_name)
try:
postgres_engine.execute('drop table {}'.format(self.table_name))
except ProgrammingError:
pass
rename_table = 'alter table {} rename to {}'
rename_table = rename_table.format(staging_name, self.table_name)
postgres_engine.execute(rename_table)
self.meta.update_after_ingest()
postgres_session.commit()
def test_delete_shape(self):
# Can we remove a shape that's fully ingested?
city_meta = postgres_session.query(ShapeMetadata).get(
shape_fixtures['city'].table_name)
self.assertIsNotNone(city_meta)
city_meta.remove_table()
postgres_session.commit()
city_meta = postgres_session.query(ShapeMetadata).get(
shape_fixtures['city'].table_name)
self.assertIsNone(city_meta)
# Can we remove a shape that's only in the metadata?
dummy_meta = postgres_session.query(ShapeMetadata).get(self.dummy_name)
self.assertIsNotNone(dummy_meta)
dummy_meta.remove_table()
postgres_session.commit()
dummy_meta = postgres_session.query(ShapeMetadata).get(self.dummy_name)
self.assertIsNone(dummy_meta)
# Add them back to return to original test state
ShapeTests.ingest_fixture(shape_fixtures['city'])
ShapeMetadata.add(human_name='Dummy Name',
source_url=None,
update_freq='yearly',
approved_status=False)
postgres_session.commit()
def test_delete_shape(self):
# Can we remove a shape that's fully ingested?
city_meta = postgres_session.query(ShapeMetadata).get(shape_fixtures['city'].table_name)
self.assertIsNotNone(city_meta)
city_meta.remove_table()
postgres_session.commit()
city_meta = postgres_session.query(ShapeMetadata).get(shape_fixtures['city'].table_name)
self.assertIsNone(city_meta)
# Can we remove a shape that's only in the metadata?
dummy_meta = postgres_session.query(ShapeMetadata).get(self.dummy_name)
self.assertIsNotNone(dummy_meta)
dummy_meta.remove_table()
postgres_session.commit()
dummy_meta = postgres_session.query(ShapeMetadata).get(self.dummy_name)
self.assertIsNone(dummy_meta)
# Add them back to return to original test state
ShapeTests.ingest_fixture(shape_fixtures['city'])
ShapeMetadata.add(human_name='Dummy Name',
source_url=None,
update_freq='yearly',
approved_status=False)
postgres_session.commit()
def submit(context):
form = request.form
is_shapefile = context['is_shapefile']
is_admin = context['is_admin']
try: # Store the metadata
if is_shapefile:
if shape_already_submitted(form['dataset_name']):
msg = 'A Shapefile with this name has already been submitted'
raise RuntimeError(msg)
else:
meta = shape_meta_from_submit_form(form, is_approved=is_admin)
else:
meta = point_meta_from_submit_form(form, is_approved=is_admin)
except RuntimeError as e:
context['error_msg'] = str(e)
return render_with_context(context)
else:
# Successfully stored the metadata
# Now fire ingestion task...
if is_admin:
meta.is_approved = True
if is_shapefile:
ticket = worker.add_shape.delay(meta.dataset_name).id
meta.celery_task_id = ticket
postgres_session.commit()
else:
ticket = worker.add_dataset.delay(meta.dataset_name).id
meta.result_ids = [ticket]
postgres_session.commit()
else:
return send_submission_email(
meta.human_name,
meta.contributor_name,
meta.contributor_email
)
return view_datasets()
def submit(context):
form = request.form
is_shapefile = context['is_shapefile']
is_admin = context['is_admin']
try: # Store the metadata
if is_shapefile:
if shape_already_submitted(form['dataset_name']):
msg = 'A Shapefile with this name has already been submitted'
raise RuntimeError(msg)
else:
meta = shape_meta_from_submit_form(form, is_approved=is_admin)
else:
meta = point_meta_from_submit_form(form, is_approved=is_admin)
except RuntimeError as e:
context['error_msg'] = str(e)
return render_with_context(context)
else:
# Successfully stored the metadata
# Now fire ingestion task...
if is_admin:
meta.is_approved = True
if is_shapefile:
ticket = worker.add_shape.delay(meta.dataset_name).id
meta.celery_task_id = ticket
postgres_session.commit()
else:
ticket = worker.add_dataset.delay(meta.dataset_name).id
meta.result_ids = [ticket]
postgres_session.commit()
else:
return send_submission_email(meta.human_name,
meta.contributor_name,
meta.contributor_email)
return view_datasets()
def on_model_change(self, form, model, is_created):
network = form.sensor_network.data
validate_node(network)
network_obj = postgres_session.query(NetworkMeta).filter(NetworkMeta.name == network).first()
network_obj.nodes.append(model)
postgres_session.commit()
def drop_tables(table_names):
drop_template = 'DROP TABLE IF EXISTS {};'
command = ''.join([drop_template.format(table_name) for table_name in table_names])
postgres_session.execute(command)
postgres_session.commit()
def ingest_points(cls):
drop_tables(("flu_shot_clinics", "landmarks", "crimes"))
ingest_point_fixture(flu_shot_meta, flu_path)
ingest_point_fixture(landmarks_meta, landmarks_path)
ingest_point_fixture(crime_meta, crime_path)
postgres_session.commit()
def update_shape_view(dataset_name):
meta = postgres_session.query(ShapeMetadata).get(dataset_name)
ticket = worker.update_shape.delay(dataset_name).id
meta.celery_task_id = ticket
postgres_session.commit()
return redirect(url_for('views.view_datasets'))
def update_shape_view(dataset_name):
meta = postgres_session.query(ShapeMetadata).get(dataset_name)
ticket = worker.update_shape.delay(dataset_name).id
meta.celery_task_id = ticket
postgres_session.commit()
return redirect(url_for('views.view_datasets'))