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'))