"""add keywords

Revision ID: 16223b92479e
Revises: 
Create Date: 2020-06-30 13:22:36.407339

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '16223b92479e'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('keywords',
                    sa.Column('id', sa.Integer(), nullable=False),
                    sa.Column('keyword', sa.String(), nullable=False),
                    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_keywords_keyword'), 'keywords', ['keyword'], unique=True)
    op.create_table('keyword_assoc',
                    sa.Column('expt_id', sa.Integer(), nullable=True),
                    sa.Column('keyword_id', sa.Integer(), nullable=True),
                    sa.ForeignKeyConstraint(['expt_id'], ['experiments.id'], ),
                    sa.ForeignKeyConstraint(['keyword_id'], ['keywords.id'], )
    )
    # ### end Alembic commands ###
    op.execute('PRAGMA user_version=3')

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('keyword_assoc')
    op.drop_index(op.f('ix_keywords_keyword'), table_name='keywords')
    op.drop_table('keywords')
    # ### end Alembic commands ###
    op.execute('PRAGMA user_version=2')

"""add keywords

Revision ID: 16223b92479e
Revises: 
Create Date: 2020-06-30 13:22:36.407339

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy import orm

import cosima_cookbook as cc

# revision identifiers, used by Alembic.
revision = '16223b92479e'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    bind = op.get_bind()
    session = orm.Session(bind=bind)

    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('keywords',
                    sa.Column('id', sa.Integer(), nullable=False),
                    sa.Column('keyword', sa.String(), nullable=False),
                    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_keywords_keyword'), 'keywords', ['keyword'], unique=True)
    op.create_table('keyword_assoc',
                    sa.Column('expt_id', sa.Integer(), nullable=True),
                    sa.Column('keyword_id', sa.Integer(), nullable=True),
                    sa.ForeignKeyConstraint(['expt_id'], ['experiments.id'], ),
                    sa.ForeignKeyConstraint(['keyword_id'], ['keywords.id'], )
    )
    # ### end Alembic commands ###
    op.execute('PRAGMA user_version=3')

    # reindex metadata for experiments
    for expt in session.query(cc.database.NCExperiment):
        cc.database.update_metadata(expt, session)
    session.commit()

def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('keyword_assoc')
    op.drop_index(op.f('ix_keywords_keyword'), table_name='keywords')
    op.drop_table('keywords')
    # ### end Alembic commands ###
    op.execute('PRAGMA user_version=2')

Update metadata for existing experiments in migration