44d5bbbf2615_workspaces_init.py

"""workspaces-init

Revision ID: 44d5bbbf2615
Revises: 
Create Date: 2020-09-02 11:25:01.571392

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '44d5bbbf2615'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    op.create_table('workflows',
                    sa.Column('workflow_id', sa.Integer, primary_key=True, autoincrement=True,
                              comment='the unique id of the workflow. auto-generated.'),
                    sa.Column('name', sa.String, unique=True, comment='a human-readable name for the workflow.'),
                    comment='A listing of the available workflows in the system.')

    """
    create table workflow_requests (
        workflow_request_id serial  primary key ,
                                            job_id              int,
                                                                workflow_id         int references workflows(workflow_id),
                                                                                                   argument            json
    );
    comment on table workflow_requests is 'A listing of requests for workflows and te resulting job ids.';
    comment on column workflow_requests.workflow_request_id is 'the unique id of the request.  auto-generated';
    comment on column workflow_requests.job_id is 'the id of the job that this request generted in the ? system.';
    comment on column workflow_requests.workflow_id is 'the id of the workflow used in this request.';
    comment on column workflow_requests.argument is 'the argument(s) used for the workflow in this request.';

    create table workflow_request_files (
        workflow_request_id int references workflow_requests(workflow_request_id),
                                           file_id int references files(file_id),
                                                                  primary key (workflow_request_id, file_id)
    );
    comment on table workflow_request_files is 'A man-to-many mapping table tracking which files were used for workflow requests.';
    comment on column workflow_request_files.workflow_request_id is 'the id of the workflow request.';
    comment on column workflow_request_files.file_id is 'the id of the file referenced by the workflow request.';

    create table capabilities (
        capability_id   serial primary key ,
                                       name            varchar not null ,
                                                               template_files  varchar,
                                                                               steps           varchar not null
    );
    comment on table capabilities is 'A listing of the available capabilities in the system.';
    comment on column capabilities.capability_id is 'the unique id of the capability.  auto-generated.';
    comment on column capabilities.name is 'the human-readable name of the capability.';
    comment on column capabilities.template_files is '?';
    comment on column capabilities.steps is 'the unique id of the capability.  auto-generated.';

    create table capability_requests (
        capability_request_id serial primary key ,
                                             capability_id int references capabilities(capability_id),
                                                                          user_id int not null ,
                                                                                      argument json
    );

    comment on table capability_requests is 'A listing of requests for capabilities, with the id of the requesting user.';
    comment on column capability_requests.capability_request_id is 'The unique id of the request.  auto-generated.';
    comment on column capability_requests.capability_id is 'the id of the capability being requested.';
    comment on column capability_requests.user_id is 'the id of the user requesting the capability.';
    comment on column capability_requests.argument is 'the JSON holding the details of the request.';
    """


def downgrade():
    op.drop_table('workflows')