def create_database(bind: Engine, database: str) -> None:
def drop_database(bind: Engine, database: str) -> None:
def create_extension(bind: Engine, extension: str) -> None:
def psql(path: str) -> None:
def postgres_session_context():
def redshift_session_context(): class PlenarioETL(object):
    def __init__(self, metadata, source_path=None):
        """
        :param metadata: MetaTable instance of dataset being ETL'd.
        :param source_path: If provided, get source CSV from local filesystem
                            instead of URL in metadata.
        """

        logger.info('Begin.')
        logger.info('metadata: {}'.format(metadata))
        logger.info('source_path: {}'.format(source_path))
        self.metadata = metadata
        # Grab a record of the names we'll need to work with this dataset
        # instead of passing around the unwieldy metadata object to ETL objects.
        # Type of namedtuple('Dataset', 'name date lat lon loc')
        self.dataset = self.metadata.meta_tuple()
        self.staging_table = Staging(self.metadata, source_path=source_path)
        logger.info('End.')

    def add(self):
        """
        Create point table for the first time.
        """
        logger.info('Begin.')
        with self.staging_table as s_table:
            new_table = Creation(s_table.table, self.dataset).table
        update_meta(self.metadata, new_table)
        logger.info('End.')
        return new_table

    def update(self):
        self.add() class ShapeETL:

    def __init__(self, meta, source_path=None):
        self.source_path = source_path
        self.table_name = meta.dataset_name
        self.source_url = meta.source_url
        self.meta = meta

    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 update(self):
        self.add() class MetaTable(postgres_base):
    __tablename__ = 'meta_master'
    # limited to 50 chars elsewhere
    dataset_name = Column(String(100), nullable=False)
    human_name = Column(String(255), nullable=False)
    description = Column(Text)
    source_url = Column(String(255))
    source_url_hash = Column(String(32), primary_key=True)
    view_url = Column(String(255))
    attribution = Column(String(255))
    # Spatial and temporal boundaries of observations in this dataset
    obs_from = Column(Date)
    obs_to = Column(Date)
    bbox = Column(Geometry('POLYGON', srid=4326))
    # TODO: Add restriction list ['daily' etc.]
    update_freq = Column(String(100), nullable=False)
    last_update = Column(DateTime)
    date_added = Column(DateTime)
    # The names of our "special" fields
    observed_date = Column(String, nullable=False)
    latitude = Column(String)
    longitude = Column(String)
    location = Column(String)
    # if False, then do not display without first getting administrator approval
    approved_status = Column(Boolean)
    contributor_name = Column(String)
    contributor_organization = Column(String)
    contributor_email = Column(String)
    result_ids = Column(ARRAY(String))
    column_names = Column(JSONB)  # {'<COLUMN_NAME>': '<COLUMN_TYPE>'}

    def __init__(self, url, human_name, observed_date,
                 approved_status=False, update_freq='yearly',
                 latitude=None, longitude=None, location=None,
                 attribution=None, description=None,
                 column_names=None,
                 contributor_name=None, contributor_email=None,
                 contributor_organization=None, **kwargs):
        """
        :param url: url where CSV or Socrata dataset with this dataset resides
        :param human_name: Nicely formatted name to display to people
        :param business_key: Name of column with the dataset's unique ID
        :param observed_date: Name of column with the datset's timestamp
        :param approved_status: Has an admin signed off on this dataset?
        :param update_freq: one of ['daily', 'weekly', 'monthly', 'yearly']
        :param latitude: Name of col with latitude
        :param longitude: Name of col with longitude
        :param location: Name of col with location formatted as (lat, lon)
        :param attribution: Text describing who maintains the dataset
        :param description: Text describing the dataset.
        """
        def curried_slug(name):
            if name is None:
                return None
            else:
                return slugify(str(name), delimiter='_')

        # Some combination of columns from which we can derive a point in space.
        assert (location or (latitude and longitude))
        # Frontend validation should have slugified column names already,
        # but enforcing it here is nice for testing.
        self.latitude = curried_slug(latitude)
        self.longitude = curried_slug(longitude)
        self.location = curried_slug(location)

        assert human_name
        self.human_name = human_name
        # Known issue: slugify fails hard on Non-ASCII
        self.dataset_name = kwargs.get('dataset_name',
                                       curried_slug(human_name)[:50])

        assert observed_date
        self.observed_date = curried_slug(observed_date)

        assert url
        # Assume a URL has already been slugified,
        # and can only contain ASCII characters
        self.source_url, self.source_url_hash = url, md5(url.encode('ascii')).hexdigest()
        self.view_url = self._get_view_url_val(url)

        assert update_freq
        self.update_freq = update_freq

        # Can be None. In practice,
        # frontend validation makes sure these are always passed along.
        self.description, self.attribution = description, attribution

        # Expect a list of strings
        self.column_names = column_names

        # Boolean
        self.approved_status = approved_status

        self.contributor_name = contributor_name
        self.contributor_organization = contributor_organization
        self.contributor_email = contributor_email

    @staticmethod
    def _get_view_url_val(url):
        trunc_index = url.find('.csv?accessType=DOWNLOAD')
        if trunc_index == -1:
            return None
        else:
            return url[:trunc_index]

    def __repr__(self):
        return '<MetaTable %r (%r)>' % (self.human_name, self.dataset_name)

    def meta_tuple(self):
        PointDataset = namedtuple('PointDataset', 'name date lat lon loc')

        basic_info = PointDataset(name=self.dataset_name,
                                  date=self.observed_date,
                                  lat=self.latitude,
                                  lon=self.longitude,
                                  loc=self.location)
        return basic_info

    def as_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    def column_info(self):
        return self.point_table.c

    @property
    def point_table(self):
        try:
            return self._point_table
        except AttributeError:
            self._point_table = Table(self.dataset_name, postgres_base.metadata, autoload=True, extend_existing=True)
            return self._point_table

    @classmethod
    def attach_metadata(cls, rows):
        """Given a list of dicts that include a dataset_name, add metadata about the datasets to each dict.

        :param rows: List of dict-likes with a dataset_name attribute
        """
        dataset_names = [row['dataset_name'] for row in rows]

        # All the metadata attributes that we can pull in unaltered
        as_is_attr_names = ['dataset_name', 'human_name', 'date_added',
                            'obs_from', 'obs_to', 'last_update',
                            'attribution', 'description', 'update_freq',
                            'view_url', 'source_url',
                            'contributor_name', 'contributor_email',
                            'contributor_organization']
        as_is_attrs = [getattr(cls, name) for name in as_is_attr_names]

        # Bounding box is the exception. We need to process it a bit.
        bbox = func.ST_AsGeoJSON(cls.bbox)

        # Put our as-is and processed attributes together
        attr_names = as_is_attr_names + ['bbox']
        attrs = as_is_attrs + [bbox]

        # Make the DB call
        result = postgres_session.query(*attrs). \
            filter(cls.dataset_name.in_(dataset_names))
        meta_list = [dict(list(zip(attr_names, row))) for row in result]

        # We need to coerce datetimes to strings
        date_attrs = ['date_added', 'obs_from', 'obs_to']
        for row in meta_list:
            row['bbox'] = json.loads(row['bbox'])
            for attr in date_attrs:
                row[attr] = str(row[attr])

        # Align the original list and metadata list...
        meta_list = sorted(meta_list, key=itemgetter('dataset_name'))
        to_coalesce = sorted(rows, key=itemgetter('dataset_name'))

        # and coalesce them.
        for original, meta in zip(to_coalesce, meta_list):
            original.update(meta)

        return to_coalesce

    @classmethod
    def timeseries_all(cls, table_names, agg_unit, start, end, geom=None, ctrees=None):
        """Return a list of
        [
            {
                'dataset_name': 'Foo',
                'items': [{'datetime': dt, 'count': int}, ...]
            }
        ]
        """
        # For each table in table_names, generate a query to be unioned
        selects = []
        for name in table_names:
            # If we have condition trees specified, apply them.
            # .get will return None for those datasets who don't have filters
            ctree = ctrees.get(name) if ctrees else None
            table = cls.get_by_dataset_name(name)
            ts_select = table.timeseries(agg_unit, start, end, geom, ctree)
            selects.append(ts_select)

        # Union the time series selects to get a panel
        panel_query = sa.union(*selects) \
            .order_by('dataset_name') \
            .order_by('time_bucket')
        panel_vals = postgres_session.execute(panel_query)

        panel = []
        for dataset_name, ts in groupby(panel_vals, lambda row: row.dataset_name):

            # ts gets closed after it's been iterated over once,
            # so we need to store the rows somewhere to iterate over them twice.
            rows = [row for row in ts]
            # If no records were found, don't include this dataset
            if all([row.count == 0 for row in rows]):
                continue

            ts_dict = {'dataset_name': dataset_name,
                       'items': []}

            for row in rows:
                ts_dict['items'].append({
                    'datetime': row.time_bucket.date().isoformat(),
                    'count': row.count
                })
                # Aggregate top-level count across all time slices.
                ts_dict['count'] = sum([i['count'] for i in ts_dict['items']])
            panel.append(ts_dict)

        return panel

    # Information about all point datasets
    @classmethod
    def index(cls):
        try:
            q = postgres_session.query(cls.dataset_name)
            q = q.filter(cls.approved_status == True)
            names = [result.dataset_name for result in q.all()]
        except ProgrammingError:
            # Handles a case that causes init_db to crash.
            # Validator calls index when initializing, prevents this call
            # from raising an error when the database is empty.
            names = []
        return names

    @classmethod
    def narrow_candidates(cls, dataset_names, start, end, geom=None):
        """
        :param dataset_names: Names of point datasets to be considered
        :return names: Names of point datasets whose bounding box and date range
                       interesects with the given bounds.
        """
        # Filter out datsets that don't intersect the time boundary
        q = postgres_session.query(cls.dataset_name) \
            .filter(cls.dataset_name.in_(dataset_names), cls.date_added != None,
                    cls.obs_from < end,
                    cls.obs_to > start)

        # or the geometry boundary
        if geom:
            intersecting = cls.bbox.ST_Intersects(func.ST_GeomFromGeoJSON(geom))
            q = q.filter(intersecting)

        return [row.dataset_name for row in q.all()]

    @classmethod
    def get_by_dataset_name(cls, name):
        foo = postgres_session.query(cls).filter(cls.dataset_name == name).first()
        return foo

    def get_bbox_center(self):
        sel = select([func.ST_AsGeoJSON(func.ST_centroid(self.bbox))])
        result = postgres_session.execute(sel)
        # returns [lon, lat]
        return json.loads(result.first()[0])['coordinates']

    def update_date_added(self):
        now = datetime.now()
        if self.date_added is None:
            self.date_added = now
        self.last_update = now

    def make_grid(self, resolution, geom=None, conditions=None, obs_dates={}):
        """
        :param resolution: length of side of grid square in meters
        :type resolution: int
        :param geom: string representation of geojson fragment
        :type geom: str
        :param conditions: conditions on columns to filter on
        :type conditions: list of SQLAlchemy binary operations
                          (e.g. col > value)
        :return: grid: result proxy with all result rows
                 size_x, size_y: the horizontal and vertical size
                                    of the grid squares in degrees
        """
        if conditions is None:
            conditions = []

        # We need to convert resolution (given in meters) to degrees
        # - which is the unit of measure for EPSG 4326 -
        # - in order to generate our grid.
        center = self.get_bbox_center()
        # center[1] is longitude
        size_x, size_y = get_size_in_degrees(resolution, center[1])

        t = self.point_table

        q = postgres_session.query(
                func.count(t.c.hash),
                func.ST_SnapToGrid(
                    t.c.geom,
                    0,
                    0,
                    size_x,
                    size_y
                ).label('squares')
            ).filter(*conditions).group_by('squares')

        if geom:
            q = q.filter(t.c.geom.ST_Within(func.ST_GeomFromGeoJSON(geom)))

        if obs_dates:
            q = q.filter(t.c.point_date >= obs_dates['lower'])
            q = q.filter(t.c.point_date <= obs_dates['upper'])

        return postgres_session.execute(q), size_x, size_y

    # Return select statement to execute or union
    def timeseries(self, agg_unit, start, end, geom=None, column_filters=None):
        # Reading this blog post
        # http://no0p.github.io/postgresql/2014/05/08/timeseries-tips-pg.html
        # inspired this implementation.
        t = self.point_table

        # Special case for the 'quarter' unit of aggregation.
        step = '3 months' if agg_unit == 'quarter' else '1 ' + agg_unit

        # Create a CTE to represent every time bucket in the timeseries
        # with a default count of 0
        day_generator = func.generate_series(func.date_trunc(agg_unit, start),
                                             func.date_trunc(agg_unit, end),
                                             step)
        defaults = select([sa.literal_column("0").label('count'),
                           day_generator.label('time_bucket')]) \
            .alias('defaults')

        where_filters = [t.c.point_date >= start, t.c.point_date <= end]
        if column_filters is not None:
            # Column filters has to be iterable here, because the '+' operator
            # behaves differently for SQLAlchemy conditions. Instead of
            # combining the conditions together, it would try to build
            # something like :param1 + <column_filters> as a new condition.
            where_filters += [column_filters]

        # Create a CTE that grabs the number of records contained in each time
        # bucket. Will only have rows for buckets with records.
        actuals = select([func.count(t.c.hash).label('count'),
                          func.date_trunc(agg_unit, t.c.point_date).
                         label('time_bucket')]) \
            .where(sa.and_(*where_filters)) \
            .group_by('time_bucket')

        # Also filter by geometry if requested
        if geom:
            contains = func.ST_Within(t.c.geom, func.ST_GeomFromGeoJSON(geom))
            actuals = actuals.where(contains)

        # Need to alias to make it usable in a subexpression
        actuals = actuals.alias('actuals')

        # Outer join the default and observed values
        # to create the timeseries select statement.
        # If no observed value in a bucket, use the default.
        name = sa.literal_column("'{}'".format(self.dataset_name)) \
            .label('dataset_name')
        bucket = defaults.c.time_bucket.label('time_bucket')
        count = func.coalesce(actuals.c.count, defaults.c.count).label('count')
        ts = select([name, bucket, count]). \
            select_from(defaults.outerjoin(actuals, actuals.c.time_bucket == defaults.c.time_bucket))

        return ts

    def timeseries_one(self, agg_unit, start, end, geom=None, column_filters=None):
        ts_select = self.timeseries(agg_unit, start, end, geom, column_filters)
        rows = postgres_session.execute(ts_select.order_by('time_bucket'))

        header = [['count', 'datetime']]
        # Discard the name attribute.
        rows = [[count, time_bucket.date()] for _, time_bucket, count in rows]
        return header + rows

    @classmethod
    def get_all_with_etl_status(cls):
        """
        :return: Every row of meta_shape joined with celery task status.
        """
        query = """
            SELECT m.*, c.*
                FROM meta_master AS m
                LEFT JOIN celery_taskmeta AS c
                  ON c.id = (
                    SELECT id FROM celery_taskmeta
                    WHERE task_id = ANY(m.result_ids)
                    ORDER BY date_done DESC
                    LIMIT 1
                  )
            WHERE m.approved_status = 'true'
        """
        return list(postgres_session.execute(query)) class ShapeMetadata(postgres_base):
    __tablename__ = 'meta_shape'

    dataset_name = Column(String, primary_key=True)
    human_name = Column(String, nullable=False)
    source_url = Column(String)
    view_url = Column(String)
    date_added = Column(Date, nullable=False)

    # Organization that published this dataset
    attribution = Column(String)
    description = Column(Text)
    update_freq = Column(String(100), nullable=False)

    # Who submitted this dataset?
    contributor_name = Column(String)
    contributor_organization = Column(String)
    contributor_email = Column(String)

    # Has an admin signed off on it?
    approved_status = Column(Boolean)

    # We always ingest geometric data as 4326
    bbox = Column(Geometry('POLYGON', srid=4326))
    # How many shape records are present?
    num_shapes = Column(Integer)
    # False when admin first submits metadata.
    # Will become true if ETL completes successfully.
    is_ingested = Column(Boolean, nullable=False)
    # foreign key of celery task responsible for shapefile's ingestion
    celery_task_id = Column(String)

    @classmethod
    def get_by_dataset_name(cls, name):
        shape_metatable = postgres_session.query(cls).filter(cls.dataset_name == name).first()
        return shape_metatable

    @classmethod
    def get_all_with_etl_status(cls):
        """
        :return: Every row of meta_shape joined with celery task status.
        """
        shape_query = """
            SELECT meta.*, c.*
            FROM meta_shape as meta
            LEFT JOIN celery_taskmeta as c
            ON c.task_id = meta.celery_task_id
            WHERE meta.approved_status = TRUE;
        """

        return list(postgres_session.execute(shape_query))

    @classmethod
    def index(cls, geom=None):
        # The attributes that we want to pass along as-is
        as_is_attr_names = ['dataset_name', 'human_name', 'date_added',
                            'attribution', 'description', 'update_freq',
                            'view_url', 'source_url', 'num_shapes']

        as_is_attrs = [getattr(cls, name) for name in as_is_attr_names]

        # We need to apply some processing to the bounding box
        bbox = func.ST_AsGeoJSON(cls.bbox)
        attr_names = as_is_attr_names + ['bbox']
        attrs = as_is_attrs + [bbox]

        result = postgres_session.query(*attrs).filter(cls.is_ingested)
        listing = [dict(list(zip(attr_names, row))) for row in result]

        for dataset in listing:
            dataset['date_added'] = str(dataset['date_added'])

        if geom:
            listing = cls.add_intersections_to_index(listing, geom)

        listing = cls._add_fields_to_index(listing)

        return listing

    @classmethod
    def simple_index(cls, geom):
        column_names = {
            'dataset_name',
            'human_name',
            'date_added',
            'attribution',
            'description',
            'update_freq',
            'view_url',
            'source_url',
            'num_shapes'
        }

        columns = [getattr(cls, n) for n in column_names]
        columns.append(func.ST_AsGeoJson(cls.bbox).label('bbox'))

        query = postgres_session.query(*columns).filter(
            cls.bbox.ST_Intersects(
                func.ST_GeomFromGeoJSON(geom)
            )
        )

        results = [dict(zip(row.keys(), row)) for row in query]

        return cls._add_fields_to_index(results)

    @classmethod
    def _add_fields_to_index(cls, listing):
        for dataset in listing:
            name = dataset['dataset_name']
            try:
                # Reflect up the shape table
                table = Table(name,
                              postgres_base.metadata,
                              autoload=True,
                              extend_existing=True)
            except NoSuchTableError:
                # If that table doesn't exist (?!?!)
                # don't try to form the fields.
                continue

            finally:
                # Extract every column's info.
                fields_list = []
                for col in table.columns:
                    if not isinstance(col.type, NullType):
                        # Don't report our internal-use columns
                        if col.name in {'geom', 'ogc_fid', 'hash'}:
                            continue
                        field_object = {
                            'field_name': col.name,
                            'field_type': str(col.type)
                        }
                        fields_list.append(field_object)
                dataset['columns'] = fields_list
        return listing

    @classmethod
    def tablenames(cls):
        return [x.dataset_name for x in postgres_session.query(ShapeMetadata.dataset_name).all()]

    @staticmethod
    def add_intersections_to_index(listing, geom):
        # For each dataset_name in the listing,
        # get a count of intersections
        # and replace num_geoms

        for row in listing:
            name = row['dataset_name']
            num_intersections_query = """
            SELECT count(g.geom) as num_geoms
            FROM "{dataset_name}" as g
            WHERE ST_Intersects(g.geom, ST_GeomFromGeoJSON('{geojson_fragment}'))
            """.format(dataset_name=name, geojson_fragment=geom)

            num_intersections = postgres_session.execute(num_intersections_query) \
                .first().num_geoms
            row['num_shapes'] = num_intersections

        intersecting_rows = [row for row in listing if row['num_shapes'] > 0]
        return intersecting_rows

    @classmethod
    def get_metadata_with_etl_result(cls, table_name):
        query = """
            SELECT meta.*, celery.status, celery.traceback, celery.date_done
            FROM meta_shape as meta
            LEFT JOIN celery_taskmeta as celery
            ON celery.task_id = meta.celery_task_id
            WHERE meta.dataset_name='{}';
        """.format(table_name)

        metadata = postgres_session.execute(query).first()
        return metadata

    @classmethod
    def get_by_human_name(cls, human_name):
        return postgres_session.query(cls).get(cls.make_table_name(human_name))

    @classmethod
    def make_table_name(cls, human_name):
        return slugify(human_name)

    @classmethod
    def add(cls, human_name, source_url, approved_status, **kwargs):
        table_name = ShapeMetadata.make_table_name(human_name)
        new_shape_dataset = ShapeMetadata(
            # Required params
            dataset_name=table_name,
            human_name=human_name,
            source_url=source_url,
            approved_status=approved_status,
            # Params that reflect just-submitted, not yet ingested status.
            is_ingested=False,
            bbox=None,
            num_shapes=None,
            date_added=datetime.now().date(),
            # The rest
            **kwargs)
        postgres_session.add(new_shape_dataset)
        return new_shape_dataset

    @property
    def shape_table(self):
        try:
            return self._shape_table
        except AttributeError:
            self._shape_table = Table(self.dataset_name, postgres_base.metadata, autoload=True, extend_existing=True)
            return self._shape_table

    def remove_table(self):
        if self.is_ingested:
            drop = 'DROP TABLE {};'.format(self.dataset_name)
            postgres_session.execute(drop)
        postgres_session.delete(self)

    def update_after_ingest(self):
        self.is_ingested = True
        self.bbox = self._make_bbox()
        self.num_shapes = self._get_num_shapes()

    def _make_bbox(self):
        bbox_query = 'SELECT ST_Envelope(ST_Union(geom)) FROM {};'. \
            format(self.dataset_name)
        box = postgres_session.execute(bbox_query).first().st_envelope
        return box

    def _get_num_shapes(self):
        table = self.shape_table
        # Arbitrarily select the first column of the table to count against
        count_query = select([func.count(table.c.geom)])
        # Should return only one row.
        # And we want the 0th and only attribute of that row (the count).
        return postgres_session.execute(count_query).fetchone()[0] def create_app():
    logger.info('beginning application setup')

    # API depends on the tables in the database to exist.
    # Don't import until we really need it to create the app
    # Since otherwise it may be called before init_db.py runs.
    from plenario.api.blueprints import api, cache

    # These other imports might eventually use API as well.
    # plenario.views does now. So we'll put them here like
    # API and not import them until they're really needed.
    from plenario.apiary.blueprints import apiary, apiary_bp
    from plenario.auth import auth, login_manager

    app = Flask(__name__)
    app.config.from_object('plenario.settings')
    app.config['JSON_SORT_KEYS'] = False
    app.url_map.strict_slashes = False
    login_manager.init_app(app)
    login_manager.login_view = 'auth.login'
    bcrypt.init_app(app)
    CORS(app)

    if sentry:
        sentry.init_app(app)
    app.register_blueprint(api)
    app.register_blueprint(views)
    app.register_blueprint(auth)
    cache.init_app(app)

    apiary.init_app(app)
    app.register_blueprint(apiary_bp)

    @app.before_request
    def check_maintenance_mode():
        """If maintenance mode is turned on in settings.py, Disable the API and the interactive pages in the explorer.
        """
        maint = app.config.get('MAINTENANCE')
        maint_pages = ['/v1/api', '/explore', '/admin']

        maint_on = False
        for m in maint_pages:
            if m in request.path:
                maint_on = True

        if maint and maint_on and request.path != url_for('views.maintenance'):
            return redirect(url_for('views.maintenance'))

    @app.teardown_appcontext
    def shutdown_session(exception=None):
        db_session.remove()

    @app.errorhandler(404)
    def page_not_found(e):
        return render_template('404.html'), 404

    @app.errorhandler(500)
    def page_not_found(e):
        return render_template('error.html'), 500

    @app.template_filter('slugify')
    def slugify(s):
        return slug(s)

    @app.template_filter('format_number')
    def reverse_filter(s):
        return '{:,}'.format(s)

    @app.template_filter('format_date_sort')
    def reverse_filter(s):
        if s:
            return s.strftime('%Y%m%d%H%M')
        else:
            return '0'

    @app.template_filter('has_description')
    def has_description(list_of_cols):
        try:
            # Any description attribute filled?
            return any([col['description'] for col in list_of_cols])
        except KeyError:
            # Is there even a description attribute?
            return False

    logger.info('application setup completed')
    return app @manager.command
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!')
import os
import unittest
from tests.fixtures.point_meta import flu_shot_meta, landmarks_meta
from tests.fixtures.point_meta import flu_path, landmarks_path
from tests.fixtures.point_meta import crime_meta, crime_path
from plenario.database import postgres_session
from plenario.etl.point import PlenarioETL
from plenario.etl.shape import ShapeETL
from plenario.models import MetaTable, ShapeMetadata
from plenario.server import create_app
from manage import init




pwd = os.path.dirname(os.path.realpath(__file__))

fixtures_path = pwd
FIXTURE_PATH = pwd


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


class ShapeFixture(object):
    def __init__(self, human_name, file_name):
        self.human_name = human_name
        self.table_name = ShapeMetadata.make_table_name(human_name)
        self.path = os.path.join(FIXTURE_PATH, file_name)
        self.update_freq = 'yearly'


shape_fixtures = {
    'city': ShapeFixture(human_name='Chicago City Limits',
                         file_name='chicago_city_limits.zip'),
    'streets': ShapeFixture(human_name='Pedestrian Streets',
                            file_name='chicago_pedestrian_streets.zip'),
    'zips': ShapeFixture(human_name='Zip Codes',
                         file_name='chicago_zip_codes.zip'),
    'neighborhoods': ShapeFixture(human_name='Chicago Neighborhoods',
                                  file_name='chicago_neighborhoods.zip'),
    'changed_neighborhoods': ShapeFixture(human_name='Chicago Neighborhoods',
                                          file_name='chicago_neighborhoods_changed.zip', )
}


class BasePlenarioTest(unittest.TestCase):
    @classmethod
    def setUpClass(cls, shutdown=False):
        # Remove tables that we're about to recreate.
        # This doesn't happen in teardown because I find it helpful
        # to inspect them in the DB after running the tests.

        meta_table_names = ['meta_master', 'meta_shape', 'etl_task']
        drop_tables(meta_table_names)

        # Re-add meta tables
        init()

        cls.app = create_app().test_client()

    @classmethod
    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()