1aee35976c94_add_download_metrics_view_1.py

"""add download metrics view 1

Revision ID: 1aee35976c94
Revises: 565c61a53263
Create Date: 2021-03-31 11:57:31.893606

"""
import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = "1aee35976c94"
down_revision = "565c61a53263"
branch_labels = None
depends_on = None


def upgrade():
    op.execute(
        """
    CREATE OR REPLACE VIEW download_metrics as
    SELECT ce.capability_request_id as request_id,
       cr.capability_name as cap_name,
       ce.created_at as execution_created,
       ce.state as state,
       cr.parameters->>'product_locator' as product_locator,
       sp.filegroup_id,
       f.datasize
    FROM capability_executions ce
         JOIN capability_requests cr on cr.capability_request_id = ce.capability_request_id
         JOIN science_products sp on parameters->>'product_locator' = sp.science_product_locator
         JOIN filegroups f on sp.filegroup_id = f.parent_filegroup_id
    WHERE capability_name = 'test_download'
    """
    )


def downgrade():
    op.execute(
        """
    DROP VIEW IF EXISTS download_metrics
    """
    )