Python alembic.op 模块，execute() 实例源码

def upgrade():
    op.execute(sa.schema.CreateSequence(sa.Sequence('pod_states_id_seq')))
    op.add_column('pod_states', sa.Column('id', sa.Integer(), nullable=False,
                  server_default=sa.text("nextval('pod_states_id_seq'::regclass)")))
    op.execute("ALTER TABLE pod_states DROP CONSTRAINT pod_states_pkey, "
               "ADD CONSTRAINT pod_states_pkey PRIMARY KEY (id);")

    op.add_column('container_states', sa.Column('exit_code', sa.Integer(), nullable=True))
    op.add_column('container_states', sa.Column('pod_state_id', sa.Integer(), nullable=True))
    op.add_column('container_states', sa.Column('reason', sa.Text(), nullable=True))
    op.create_index('ix_pod_id_start_time', 'pod_states', ['pod_id', 'start_time'], unique=True)
    op.create_foreign_key('container_states_pod_state_id_fkey', 'container_states',
                          'pod_states', ['pod_state_id'], ['id'])

    upgrade_data()

    op.alter_column('container_states', 'pod_state_id',
                    existing_type=sa.INTEGER(), nullable=False)
    op.drop_constraint(u'container_states_pod_id_fkey', 'container_states',
                       type_='foreignkey')
    op.drop_column('container_states', 'pod_id')

def downgrade():
    op.add_column('container_states', sa.Column('pod_id', postgresql.UUID(),
                  autoincrement=False, nullable=True))
    op.create_foreign_key(u'container_states_pod_id_fkey',
                          'container_states', 'pods', ['pod_id'], ['id'])

    downgrade_data()

    op.drop_column('container_states', 'reason')
    op.drop_column('container_states', 'exit_code')
    op.drop_constraint('container_states_pod_state_id_fkey', 'container_states',
                       type_='foreignkey')
    op.drop_index('ix_pod_id_start_time', table_name='pod_states')
    op.drop_column('container_states', 'pod_state_id')
    op.execute("ALTER TABLE pod_states DROP CONSTRAINT pod_states_pkey, "
               "ADD CONSTRAINT pod_states_pkey PRIMARY KEY (pod_id, start_time);")
    op.drop_column('pod_states', 'id')
    op.execute(sa.schema.DropSequence(sa.Sequence('pod_states_id_seq')))

def upgrade():
    op.create_table('mastodon_instances',
    sa.Column('instance', sa.String(), nullable=False),
    sa.Column('popularity', sa.Float(), server_default='10', nullable=False),
    sa.PrimaryKeyConstraint('instance', name=op.f('pk_mastodon_instances'))
    )
    op.execute("""
        INSERT INTO mastodon_instances (instance, popularity) VALUES
            ('mastodon.social', 100),
            ('mastodon.cloud', 90),
            ('social.tchncs.de', 80),
            ('mastodon.xyz', 70),
            ('mstdn.io', 60),
            ('awoo.space', 50),
            ('cybre.space', 40),
            ('mastodon.art', 30)
            ;
    """)

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    query_tbl = sa.sql.table('query', sa.sql.column('platform', sa.String))
    pack_tbl = sa.sql.table('pack', sa.sql.column('platform', sa.String))
    op.execute(
        query_tbl.update() \
            .where(
                sa.or_(
                    query_tbl.c.platform==op.inline_literal('redhat,centos'),
                    query_tbl.c.platform==op.inline_literal('ubuntu'),
                )
            ).values({'platform': op.inline_literal('linux')})
    )
    op.execute(
        pack_tbl.update() \
            .where(
                sa.or_(
                    query_tbl.c.platform==op.inline_literal('redhat,centos'),
                    query_tbl.c.platform==op.inline_literal('ubuntu'),
                )
            ).values({'platform': op.inline_literal('linux')})
    )
    op.add_column('query', sa.Column('shard', sa.Integer(), nullable=True))
    ### end Alembic commands ###

def downgrade():
    op.execute(textwrap.dedent("""ALTER TABLE device_group RENAME TO "group";"""))
    op.execute(textwrap.dedent("""
            CREATE OR REPLACE FUNCTION public.upsert_group(p_group_name character varying, p_device_list character varying)
            RETURNS integer AS
            $BODY$
            DECLARE num_rows integer;
            BEGIN
                INSERT INTO public.group AS gro (group_name, device_list)
                VALUES (p_group_name, p_device_list)
                ON CONFLICT (group_name) DO UPDATE
                SET
                    device_list = p_device_list
                WHERE gro.group_name = p_group_name;
                GET DIAGNOSTICS num_rows = ROW_COUNT;
                RETURN num_rows;
            END;
            $BODY$
                LANGUAGE plpgsql VOLATILE
                COST 100;"""))

def downgrade():
    # Drop trigger
    op.execute(
        'DROP TRIGGER increment_num_rows_in_db_array ON callset_to_db_array_association CASCADE')
    op.drop_column(u'db_array', 'num_rows')
    op.create_table(
        'db_row',
        sa.Column('id', sa.BIGINT(), nullable=False),
        sa.Column('db_array_id', sa.BIGINT(), autoincrement=False, nullable=False),
        sa.Column('tile_row_id', sa.BIGINT(), autoincrement=False, nullable=False),
        sa.ForeignKeyConstraint(['db_array_id'],[u'db_array.id'], name=u'db_row_db_array_id_fkey'),
        sa.PrimaryKeyConstraint('id', name=u'db_row_pkey'))
    op.add_column(
        u'callset',
        sa.Column('individual_id', sa.BIGINT(), autoincrement=False, nullable=False))
    op.add_column(u'callset', sa.Column('dbrow_id', sa.BIGINT(), autoincrement=False, nullable=False))
    op.drop_constraint('callset_source_sample_id_fkey','callset', type_='foreignkey')
    op.drop_constraint('callset_target_sample_id_fkey','callset', type_='foreignkey')
    op.create_foreign_key(u'callset_individual_id_fkey','callset', 'individual', ['individual_id'], ['id'])
    op.create_foreign_key(u'callset_dbrow_id_fkey','callset', 'db_row', ['dbrow_id'], ['id'])
    op.drop_column(u'callset', 'target_sample_id')
    op.drop_column(u'callset', 'source_sample_id')
    op.drop_index('db_array_id_tile_row_id_idx',table_name='callset_to_db_array_association')
    op.drop_table('callset_to_db_array_association')
    op.drop_table('sample')

def upgrade():
    # When making changes to a column that has a foreign key, we need to drop
    # and then re-add the constraint
    op.execute('DELETE FROM txn_reconciles WHERE txn_id IS NULL;')
    op.execute('LOCK TABLES txn_reconciles WRITE, transactions WRITE;')
    op.drop_constraint('fk_txn_reconciles_txn_id_transactions',
                       'txn_reconciles', type_='foreignkey')
    op.alter_column(
        'txn_reconciles',
        'txn_id',
        existing_type=mysql.INTEGER(display_width=11),
        nullable=False
    )
    op.create_foreign_key('fk_txn_reconciles_txn_id_transactions',
                          'txn_reconciles', 'transactions', ['txn_id'], ['id'])
    op.execute('UNLOCK TABLES;')

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('skin')
    op.drop_table('modules')
    op.drop_column('node', 'mods')
    op.drop_column('node', 'parentid')
    op.drop_column('node', 'folderid')
    op.drop_column('node', 'image')
    op.drop_column('node', 'folder')
    op.drop_column('node', 'custom')
    op.drop_column('node', 'target')
    op.drop_column('node', 'type')
    op.drop_column('node', 'skin_id')
    op.drop_column('node', 'modules_id')
    op.rename_table('node', 'page')
    op.execute('ALTER SEQUENCE node_id_seq RENAME TO page_id_seq')
    # ### end Alembic commands ###

def upgrade():
    # we dont retain historical data as we simply dont care yet
    op.execute('truncate table filecoverage')
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('filecoverage', sa.Column(
        'build_id', zeus.db.types.guid.GUID(), nullable=False))
    op.create_index(op.f('ix_filecoverage_build_id'),
                    'filecoverage', ['build_id'], unique=False)
    op.create_unique_constraint('unq_coverage_filname', 'filecoverage', [
                                'build_id', 'filename'])
    op.drop_constraint('unq_job_filname', 'filecoverage', type_='unique')
    op.drop_constraint('filecoverage_job_id_fkey',
                       'filecoverage', type_='foreignkey')
    op.create_foreign_key(None, 'filecoverage', 'build', [
                          'build_id'], ['id'], ondelete='CASCADE')
    op.drop_column('filecoverage', 'job_id')
    # ### end Alembic commands ###

def set_nullable_columns_default_values():
    role = sa.table(u'jobs', sa.column(u'address_is_valid'))
    op.execute(role.update().values(address_is_valid=True))

    role = sa.table(u'jobs', sa.column(u'geolocation_is_valid'))
    op.execute(role.update().values(geolocation_is_valid=True))

    role = sa.table(u'jobs', sa.column(u'last_sync'))
    op.execute(role.update().values(last_sync=datetime(1970, 1, 1)))
    role = sa.table(u'jobs', sa.column(u'latitude'))
    op.execute(role.update().values(latitude=0.0))
    role = sa.table(u'jobs', sa.column(u'longitude'))
    op.execute(role.update().values(longitude=0.0))

    role = sa.table(u'jobs', sa.column(u'pushed_on_twitter'))
    op.execute(role.update().values(pushed_on_twitter=True))

    role = sa.table(u'jobs', sa.column(u'publication_datetime_is_fake'))
    op.execute(role.update()
               .where(role.c.publication_datetime_is_fake == None)
               .values(publication_datetime_is_fake=False))

def upgrade():
    for table_name in ("resource", "resource_history", "metric"):
        creator_col = sa.Column("creator", sa.String(255))
        created_by_user_id_col = sa.Column("created_by_user_id",
                                           sa.String(255))
        created_by_project_id_col = sa.Column("created_by_project_id",
                                              sa.String(255))
        op.add_column(table_name, creator_col)
        t = sa.sql.table(
            table_name, creator_col,
            created_by_user_id_col, created_by_project_id_col)
        op.execute(
            t.update().values(
                creator=(
                    created_by_user_id_col + ":" + created_by_project_id_col
                )).where((created_by_user_id_col is not None)
                         | (created_by_project_id_col is not None)))
        op.drop_column(table_name, "created_by_user_id")
        op.drop_column(table_name, "created_by_project_id")

def upgrade():
    op.add_column("resource_type", sa.Column('tablename', sa.String(18),
                                             nullable=True))

    resource_type = sa.Table(
        'resource_type', sa.MetaData(),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('tablename', sa.String(18), nullable=True)
    )
    op.execute(resource_type.update().where(
        resource_type.c.name == "instance_network_interface"
    ).values({'tablename': op.inline_literal("'instance_net_int'")}))
    op.execute(resource_type.update().where(
        resource_type.c.name != "instance_network_interface"
    ).values({'tablename': resource_type.c.name}))

    op.alter_column("resource_type", "tablename", type_=sa.String(18),
                    nullable=False)
    op.create_unique_constraint("uniq_resource_type0tablename",
                                "resource_type", ["tablename"])

def upgrade():
    op.add_column('instance', sa.Column('created_by', mysql.VARCHAR(collation='utf8_bin', length=64), nullable=False))
    op.add_column('instance', sa.Column('created_at', sa.DateTime(), nullable=False))
    op.add_column('instance', sa.Column('updated_by', mysql.VARCHAR(collation='utf8_bin', length=64), nullable=False))
    op.add_column('instance', sa.Column('updated_at', sa.DateTime(), nullable=False))

    op.execute("""
        UPDATE instance JOIN (
            SELECT instance_id, min(created) min_created, max(created) max_created, author
            FROM log
            GROUP BY instance_id, author
        ) log
        ON id = instance_id
        SET created_at = min_created, created_by = author,
            updated_at = max_created, updated_by = author
    """)

def downgrade():
    for seqname in ('contact_pronouns_position', 'contact_name_position',
                    'contact_email_position',
        ):
        op.execute(DropSequence(Sequence(seqname)))
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('contact_pronouns')
    op.drop_table('contact_name')
    op.drop_table('contact_email')
    op.drop_table('roles_users')
    op.drop_table('contact')
    op.drop_index(op.f('ix_user_username'), table_name='user')
    op.drop_table('user')
    op.drop_table('role')
    op.drop_table('pronouns')
    # ### end Alembic commands ###

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('tag',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('user_id', sa.Integer(), nullable=False),
    sa.Column('name', sa.String(length=80), nullable=False),
    sa.Column('color', ColorType(length=20), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'name')
    )
    op.create_table('contact_tag',
    sa.Column('contact_id', sa.Integer(), nullable=False),
    sa.Column('tag_id', sa.Integer(), nullable=False),
    sa.Column('position', sa.Integer(), nullable=False),
    sa.Column('note', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['contact_id'], ['contact.id'], ),
    sa.ForeignKeyConstraint(['tag_id'], ['tag.id'], ),
    sa.PrimaryKeyConstraint('contact_id', 'tag_id')
    )
    # ### end Alembic commands ###
    op.execute(CreateSequence(Sequence('contact_tag_position')))

def upgrade():
    """Upgrade the database to a newer revision."""
    # ### commands auto generated by Alembic - please adjust! ###
    # See https://bitbucket.org/zzzeek/alembic/issues/123/a-way-to-run-non-transactional-ddl
    connection = None
    if not op.get_context().as_sql:
        connection = op.get_bind()
        connection.execution_options(isolation_level='AUTOCOMMIT')

    op.execute("ALTER TYPE ecosystem_backend_enum ADD VALUE 'nuget'")
    op.execute("INSERT INTO ecosystems VALUES "
               "('{id}', '{name}', '{backend}', '{url}', '{fetch_url}')".
               format(id=8, name='nuget', backend='nuget',
                      url='https://nuget.org/', fetch_url='https://api.nuget.org/packages/'))

    if connection is not None:
        connection.execution_options(isolation_level='READ_COMMITTED')
    # ### end Alembic commands ###

def upgrade():
    conn = op.get_bind()

    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('projects', sa.Column('task_creation_mode', sa.Integer(), nullable=True))
    op.create_index('idx_geometry', 'projects', ['geometry'], unique=False, postgresql_using='gist')
    op.add_column('tasks', sa.Column('extra_properties', sa.Unicode(), nullable=True))

    for project in conn.execute(projects.select()):
        zooms = conn.execute(
            sa.sql.expression.select([tasks.c.zoom]).distinct(tasks.c.zoom)
                .where(tasks.c.project_id == project.id))
        zooms = zooms.fetchall()

        if len(zooms) == 1 and zooms[0] == (None,):
            op.execute(
                projects.update().where(projects.c.id == project.id)
                    .values(task_creation_mode=1))
    # ### end Alembic commands ###

def upgrade():
    op.execute("""
        BEGIN;
        ALTER TYPE taskflow_statuses RENAME TO taskflow_statuses_old;
        CREATE TYPE taskflow_statuses AS ENUM('queued','pushed','running','retry','dequeued','failed','success');
        CREATE TYPE taskflow_statuses_inter AS ENUM('queued','pushed','running','retry','retrying','dequeued','failed','success');

        ALTER TABLE workflow_instances ALTER COLUMN status TYPE taskflow_statuses_inter USING status::text::taskflow_statuses_inter;
        UPDATE workflow_instances SET status = 'retry' WHERE status = 'retrying';
        ALTER TABLE workflow_instances ALTER COLUMN status TYPE taskflow_statuses USING status::text::taskflow_statuses;
        ALTER TABLE task_instances ALTER COLUMN status TYPE taskflow_statuses_inter USING status::text::taskflow_statuses_inter;
        UPDATE task_instances SET status = 'retry' WHERE status = 'retrying';
        ALTER TABLE task_instances ALTER COLUMN status TYPE taskflow_statuses USING status::text::taskflow_statuses;

        DROP TYPE taskflow_statuses_old;
        DROP TYPE taskflow_statuses_inter;
        COMMIT;
        """)

def downgrade():
    op.execute("""
        BEGIN;
        ALTER TYPE taskflow_statuses RENAME TO taskflow_statuses_old;
        CREATE TYPE taskflow_statuses AS ENUM('queued','pushed','running','retrying','dequeued','failed','success');
        CREATE TYPE taskflow_statuses_inter AS ENUM('queued','pushed','running','retry','retrying','dequeued','failed','success');

        ALTER TABLE workflow_instances ALTER COLUMN status TYPE taskflow_statuses_inter USING status::text::taskflow_statuses_inter;
        UPDATE workflow_instances SET status = 'retrying' WHERE status = 'retry';
        ALTER TABLE workflow_instances ALTER COLUMN status TYPE taskflow_statuses USING status::text::taskflow_statuses;
        ALTER TABLE task_instances ALTER COLUMN status TYPE taskflow_statuses_inter USING status::text::taskflow_statuses_inter;
        UPDATE task_instances SET status = 'retrying' WHERE status = 'retry';
        ALTER TABLE task_instances ALTER COLUMN status TYPE taskflow_statuses USING status::text::taskflow_statuses;

        DROP TYPE taskflow_statuses_old;
        DROP TYPE taskflow_statuses_inter;
        COMMIT;
        """)

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute("UPDATE translated_string SET base_string='' WHERE base_string is NULL")
    op.alter_column('translated_string', 'base_string',
               existing_type=sa.TEXT(),
               nullable=False)
    op.execute("UPDATE translated_string SET comment='' WHERE comment is NULL")
    op.alter_column('translated_string', 'comment',
               existing_type=sa.TEXT(),
               nullable=False)
    op.execute("UPDATE translated_string SET context='' WHERE context is NULL")
    op.alter_column('translated_string', 'context',
               existing_type=sa.TEXT(),
               nullable=False)
    op.execute("UPDATE translated_string SET translation='' WHERE translation is NULL")
    op.alter_column('translated_string', 'translation',
               existing_type=sa.TEXT(),
               nullable=False)
    op.execute("UPDATE translated_string SET translator_comment='' WHERE translator_comment is NULL")
    op.alter_column('translated_string', 'translator_comment',
               existing_type=sa.TEXT(),
               nullable=False)
    # ### end Alembic commands ###

def upgrade():
    op.execute("""
    UPDATE track SET
        first_play = (SELECT MIN(date) FROM play WHERE track_id = track.id),
        last_play = (SELECT MAX(date) FROM play WHERE track_id = track.id)
    ;
    """)
    op.execute("""
    UPDATE album SET
        first_play = (SELECT MIN(first_play) FROM track WHERE album_id = album.id),
        last_play = (SELECT MAX(last_play) FROM track WHERE album_id = album.id)
    ;
    """)
    op.execute("""
    UPDATE artist SET
        first_play = (SELECT MIN(first_play) FROM album WHERE artist_id = artist.id),
        last_play = (SELECT MAX(last_play) FROM album WHERE artist_id = artist.id)
    ;
    """)

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('patient_demographics', sa.Column('ethnicity_id', sa.Integer(), nullable=True))
    op.add_column('patient_demographics', sa.Column('nationality_id', sa.Integer(), nullable=True))
    op.execute('update patient_demographics set ethnicity_id = (select id from ethnicities where code = ethnicity)')
    op.create_foreign_key(
        'patient_demographics_ethnicity_id_fkey',
        'patient_demographics',
        'ethnicities',
        ['ethnicity_id'],
        ['id']
    )
    op.create_foreign_key(
        'patient_demographics_nationality_id_fkey',
        'patient_demographics',
        'nationalities',
        ['nationality_id'],
        ['id']
    )
    op.drop_column('patient_demographics', 'ethnicity')
    # ### end Alembic commands ###

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column('idea_idea_link', sa.Column(
            'rdf_temp', sa.String(60), nullable=False))
    with context.begin_transaction():
        op.execute('UPDATE idea_idea_link SET "rdf_temp" = "rdf_type"')
        op.execute('''UPDATE idea_idea_link
                    SET "rdf_temp" = 'idea:InclusionRelation'
                    WHERE "rdf_temp" = 'idea:GenericIdeaNode' ''')
        mark_changed()
    with context.begin_transaction():
        op.drop_column('idea_idea_link', 'rdf_type')
        op.add_column('idea_idea_link', sa.Column(
            'rdf_type', sa.String(60), nullable=False,
            server_default='idea:InclusionRelation'))
        op.drop_column('idea_idea_link', 'rdf_temp')

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.create_table(
            'action_on_post',
            sa.Column(
                'id', sa.Integer,
                sa.ForeignKey('action.id', ondelete="CASCADE", onupdate='CASCADE'),
                primary_key=True),
            sa.Column(
                'post_id', sa.Integer,
                sa.ForeignKey('content.id', ondelete="CASCADE", onupdate='CASCADE'),
                nullable=False))
        # Alchemy put the post_id from the abstract class on the superclass... wtf?
        op.execute('''INSERT INTO action_on_post (id, post_id)
            SELECT id, post_id FROM action''')
        op.drop_column('action', 'post_id')
        for name in post_tables.keys():
            op.drop_table(name)

def downgrade(pyramid_env):
    with context.begin_transaction():
        for table_name, type_name in list(post_tables.items()):
            op.create_table(
                table_name,
                sa.Column(
                    'id', sa.Integer,
                    sa.ForeignKey('action.id', ondelete="CASCADE", onupdate='CASCADE'),
                    primary_key=True))
            op.execute('''INSERT INTO %s (id)
                SELECT id FROM action WHERE "type" = '%s' ''' % (table_name, type_name))
        op.add_column('action', sa.Column(
                'post_id', sa.Integer,
                sa.ForeignKey('content.id', ondelete="CASCADE", onupdate='CASCADE')))
        op.execute('''UPDATE action SET post_id = (
            SELECT post_id FROM action_on_post WHERE action.id = action_on_post.id)''')
        op.drop_table('action_on_post')

def downgrade(pyramid_env):
    # with context.begin_transaction():
    #     op.add_column("content", sa.Column(
    #         "subject", sa.Unicode, server_default=""))
    #     op.add_column("content", sa.Column(
    #         "body", sa.UnicodeText, server_default=""))
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        for target in ("subject", "body"):
            r = db.execute(
                """select content.id, langstring_entry.value from content
                join langstring_entry
                    on content.{0}_id = langstring_entry.langstring_id
                join locale on langstring_entry.locale_id = locale.id
                where locale.code not like '%-x-mtfrom-%'""".format(target))
            for id, text in r:
                if len(text):
                    db.execute("UPDATE content set %s = :txt WHERE id= :id" % (
                        (target,)), dict(txt=text, id=id))
        mark_changed()

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column(
            'idea', sa.Column(
                'hidden', sa.SmallInteger, server_default='0'))
        op.execute('UPDATE idea set hidden=0')
        op.execute('ALTER TABLE idea ADD CHECK (hidden IN (0, 1))')
        op.add_column(
            'idea', sa.Column(
                'widget_id', sa.Integer, sa.ForeignKey('widget.id')))
        op.add_column(
            'content', sa.Column(
                'hidden', sa.SmallInteger, server_default='0'))
        op.execute('UPDATE content set hidden=0')
        op.execute('ALTER TABLE content ADD CHECK (hidden IN (0, 1))')

    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        pass

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column('idprovider_agent_account',
                      sa.Column('temp_userid', sa.String(200)))
        op.execute('UPDATE idprovider_agent_account SET temp_userid = userid')
        op.drop_column('idprovider_agent_account', 'userid')
        op.add_column('idprovider_agent_account',
                      sa.Column('userid', sa.String(200), nullable=False))
        op.execute('UPDATE idprovider_agent_account \
                    SET userid = temp_userid' )
        op.drop_column('idprovider_agent_account', 'temp_userid')

    # Do stuff with the app's models here.
    from assembl import models as m
    db = m.get_session_maker()()
    with transaction.manager:
        pass

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.drop_index("%s_%s_locale_UNQC_code" % (
            config.get('db_schema'), config.get('db_user')))
        op.add_column(
            "locale",
            sa.Column("temp_code", sa.String))
        op.execute("UPDATE locale set temp_code = code")
    with context.begin_transaction():
        op.drop_column("locale", "code")
        op.add_column(
            "locale",
            sa.Column("code", sa.String(32)))
        op.execute("UPDATE locale set code = temp_code")
    with context.begin_transaction():
        op.drop_column("locale", "temp_code")
        op.create_index(
            '%s_%s_locale_UNQC_code' % (
                config.get('db_schema'), config.get('db_user')),
            'locale', ['code'], unique=True)

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.drop_index("%s_%s_locale_UNQC_code" % (
            config.get('db_schema'), config.get('db_user')))
        op.add_column(
            "locale",
            sa.Column("temp_code", sa.String))
        op.execute("UPDATE locale set temp_code = code")
    with context.begin_transaction():
        op.drop_column("locale", "code")
        op.add_column(
            "locale",
            sa.Column("code", sa.String(20)))
        op.execute("UPDATE locale set code = temp_code")
    with context.begin_transaction():
        op.drop_column("locale", "temp_code")
        op.create_index(
            '%s_%s_locale_UNQC_code' % (
                config.get('db_schema'), config.get('db_user')),
            'locale', ['code'], unique=True)

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.create_table(
            'idea_view_widget',
            sa.Column(
                'id', sa.Integer, sa.ForeignKey(
                    'widget.id', ondelete='CASCADE', onupdate='CASCADE'),
                primary_key=True),
            sa.Column(
                'main_idea_view_id', sa.Integer, sa.ForeignKey(
                    'idea_graph_view.id', ondelete="CASCADE",
                    onupdate="CASCADE"),
                nullable=True))
        op.execute("""INSERT INTO idea_view_widget (id, main_idea_view_id)
            SELECT id, main_idea_view_id FROM widget
            WHERE widget_type = 'creativity'""")
        op.execute("""UPDATE widget SET type = 'creativity_widget'
                      WHERE widget_type = 'creativity'""")
        op.execute("""UPDATE widget SET type = 'multicriterion_voting_widget'
                      WHERE widget_type = 'vote'""")
        op.drop_column('widget', 'widget_type')
        op.drop_column('widget', 'main_idea_view_id')

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column('widget', sa.Column(
            'widget_type', sa.String(120), nullable=False))
        op.add_column('widget', sa.Column(
            'main_idea_view_id', sa.Integer, sa.ForeignKey(
                'idea_graph_view.id', ondelete="CASCADE",
                onupdate="CASCADE"),
            nullable=True))
        op.execute("""UPDATE widget SET widget_type = 'creativity'
                      WHERE type = 'creativity_widget'""")
        op.execute("""UPDATE widget SET widget_type = 'vote'
                      WHERE type = 'multicriterion_voting_widget'""")
        op.execute("UPDATE widget SET widget_type ='widget'")
        op.execute("""UPDATE widget SET main_idea_view_id = (
            SELECT main_idea_view_id FROM idea_view_widget
            WHERE idea_view_widget.id = widget.id)""")
        op.drop_table('idea_view_widget')

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.execute("""INSERT INTO idea_content_positive_link (id)
            SELECT id FROM idea_content_widget_link""")
    with context.begin_transaction():
        try:
            op.drop_constraint(
                "idea_content_widget_link_idea_content_link_id_id",
                "idea_content_widget_link")
        except:
            pass
    with context.begin_transaction():
        op.create_foreign_key(
            "idea_content_widget_link_idea_content_positive_link_id_id",
            "idea_content_widget_link", "idea_content_positive_link",
            ["id"], ["id"])

def upgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column('idea', sa.Column(
            'rdf_type', sa.String(60), nullable=False,
            server_default='idea:GenericIdeaNode'))
        op.add_column('idea', sa.Column(
            'last_modified', sa.types.TIMESTAMP))
        op.add_column('idea_idea_link', sa.Column(
            'rdf_type', sa.String(60), nullable=False,
            server_default='idea:InclusionRelation'))
        op.drop_table("root_idea")

    with context.begin_transaction():
        op.execute('UPDATE idea SET "rdf_type" = "sqla_type"')
        op.execute('UPDATE idea_idea_link SET "rdf_type" = "sqla_type"')
        op.execute("UPDATE idea SET sqla_type = 'root_idea' WHERE sqla_type = 'assembl:RootIdea'")
        op.execute("UPDATE idea SET sqla_type = 'idea' WHERE sqla_type <> 'root_idea'")
        mark_changed()

    with context.begin_transaction():
        op.drop_column('idea_idea_link', 'sqla_type')

def downgrade(pyramid_env):
    with context.begin_transaction():
        op.add_column('idea_idea_link', sa.Column(
            'sqla_type', sa.String(60), nullable=False))
        op.create_table('root_idea', sa.Column(
            'id', sa.Integer, sa.ForeignKey(
                'idea.id', ondelete='CASCADE', onupdate='CASCADE'),
            primary_key=True))
    with context.begin_transaction():
        op.execute('UPDATE idea SET "sqla_type" = "rdf_type"')
        op.execute('UPDATE idea_idea_link SET "sqla_type" = "rdf_type"')
        op.execute("INSERT INTO root_idea (id) SELECT id FROM idea WHERE sqla_type ='assembl:RootIdea'")
        mark_changed()
    with context.begin_transaction():
        op.drop_column('idea_idea_link', 'rdf_type')
        op.drop_column('idea', 'rdf_type')
        op.drop_column('idea', 'last_modified')

def downgrade(pyramid_env):
    schema, user = config.get('db_schema'), config.get('db_user')
    with context.begin_transaction():
        for tablename in tables:
            index_name = "%s_%s_%s_UNQC_base_id_tombstone_date" % (
                schema, user, tablename)
            op.drop_constraint(index_name, tablename, schema=schema)
            op.add_column(tablename, sa.Column('is_tombstone', sa.SmallInteger, server_default="0"))

    # repopulate is_tombstone
    with transaction.manager:
        for tablename in tables:
            op.execute('UPDATE %s set "is_tombstone" = (CASE WHEN "tombstone_date" IS NULL THEN 0 ELSE 1 END)' % (tablename,))
        mark_changed()

    with context.begin_transaction():
        for tablename in tables:
            op.drop_column(tablename, "base_id")
            op.drop_column(tablename, "tombstone_date")
            op.drop_table(tablename+ID_TABLE)
            op.execute('ALTER TABLE %s ADD CHECK ("is_tombstone" IN (0, 1))'%(tablename,))

def upgrade():
    op.add_column('user', sa.Column('privacy_mode', sa.Boolean, default=True))
    query = 'UPDATE "user" SET privacy_mode=true;'
    op.execute(query)

def upgrade():
    op.add_column('blogpost', sa.Column('published', sa.Boolean, default=False))
    op.add_column('blogpost', sa.Column('updated', sa.Text,
                                       default=make_timestamp))
    sql = 'update blogpost set published=true'
    op.execute(sql)

def upgrade():
    # First, create column and fill all records with default value.
    # To avoid integrity error, the constraint non-nullable will be set after that
    op.add_column('app', sa.Column('featured', sa.Boolean, default=False))
    query = 'UPDATE "app" SET featured=false;'
    op.execute(query)
    op.alter_column('app', 'featured', nullable=False)
    query = 'UPDATE "app" SET featured=true WHERE app.id IN (SELECT app_id FROM FEATURED);'
    op.execute(query)
    op.drop_table('featured')

def downgrade():
    op.create_table(
        'featured',
        sa.Column('id', sa.Integer, primary_key=True),
        sa.Column('created', sa.Text, default=make_timestamp),
        sa.Column('app_id', sa.Integer, sa.ForeignKey('app.id'), unique=True)
    )
    query = 'INSERT INTO "featured" (app_id) SELECT id FROM "app" WHERE featured=true;'
    op.execute(query)
    op.drop_column('app', 'featured')

def upgrade():
    op.add_column('user', sa.Column('newsletter_prompted', sa.Boolean, default=False))
    query = 'UPDATE "user" SET newsletter_prompted=false;'
    op.execute(query)

def upgrade():
    query = '''UPDATE "user"
                 SET ckan_api=null
                 WHERE id IN (SELECT id 
                    FROM (SELECT id, row_number() over (partition BY ckan_api ORDER BY id) AS rnum
                          FROM "user") t
               WHERE t.rnum > 1);
            '''
    op.execute(query)
    op.create_unique_constraint('ckan_api_uq', 'user', ['ckan_api'])

def upgrade():
    query = 'UPDATE "user" SET locale=\'en\';'
    op.execute(query)
    op.alter_column('user', 'locale', nullable=False)
    op.alter_column('user', 'privacy_mode', nullable=False)

def upgrade():
    query = 'ALTER TABLE project ALTER COLUMN info TYPE JSON USING info::JSON;'
    op.execute(query)
    query = 'ALTER TABLE "user" ALTER COLUMN info TYPE JSON USING info::JSON;'
    op.execute(query)
    query = 'ALTER TABLE task ALTER COLUMN info TYPE JSON USING info::JSON;'
    op.execute(query)
    query = 'ALTER TABLE task_run ALTER COLUMN info TYPE JSON USING info::JSON;'
    op.execute(query)

def downgrade():
    query = 'ALTER TABLE project ALTER COLUMN info TYPE TEXT USING info::TEXT;'
    op.execute(query)
    query = 'ALTER TABLE "user" ALTER COLUMN info TYPE TEXT USING info::TEXT;'
    op.execute(query)
    query = 'ALTER TABLE task ALTER COLUMN info TYPE TEXT USING info::TEXT;'
    op.execute(query)
    query = 'ALTER TABLE task_run ALTER COLUMN info TYPE TEXT USING info::TEXT;'
    op.execute(query)

def upgrade():
    op.add_column('task_run', sa.Column(field, sa.String))
    op.add_column('project', sa.Column('secret_key', sa.String))
    query = 'update project set secret_key=md5(random()::text);'
    op.execute(query)

def upgrade():
    op.add_column('user', sa.Column('pro', sa.Boolean, default=False))
    query = 'UPDATE "user" SET pro=false;'
    op.execute(query)

def upgrade():
    op.add_column('app', sa.Column(field, sa.BOOLEAN, default=True))
    query = 'UPDATE app SET %s = True;' % field
    op.execute(query)

def upgrade():
    # Rename table name
    query = 'ALTER TABLE app RENAME TO project;'
    op.execute(query)
    # Rename id sequence
    query = 'ALTER SEQUENCE app_id_seq RENAME TO project_id_seq;'
    op.execute(query)
    # Rename foreign keys in other tables
    query = 'ALTER TABLE blogpost RENAME app_id TO project_id;'
    op.execute(query)
    query = 'ALTER TABLE task RENAME app_id TO project_id;'
    op.execute(query)
    query = 'ALTER TABLE task_run RENAME app_id TO project_id;'
    op.execute(query)
    query = 'ALTER TABLE auditlog RENAME app_id TO project_id;'
    op.execute(query)
    query = 'ALTER TABLE auditlog RENAME app_short_name TO project_short_name;'
    op.execute(query)
    # Rename primary and unique keys
    query = 'ALTER TABLE app_pkey RENAME TO project_pkey;'
    op.execute(query)
    query = 'ALTER TABLE app_name_key RENAME TO project_name_key;'
    op.execute(query)
    query = 'ALTER TABLE app_short_name_key RENAME TO project_short_name_key;'
    op.execute(query)
    # Rename foreign key constraints project table. NOTE: requires PostgreSQL 9.2 or above
    query = 'ALTER TABLE project RENAME CONSTRAINT app_category_id_fkey TO project_category_id_fkey;'
    op.execute(query)
    query = 'ALTER TABLE project RENAME CONSTRAINT app_owner_id_fkey TO project_owner_id_fkey;'
    op.execute(query)
    # Rename foreign key constraints in other tables. NOTE: requires PostgreSQL 9.2 or above
    query = 'ALTER TABLE blogpost RENAME CONSTRAINT blogpost_app_id_fkey TO blogpost_project_id_fkey;'
    op.execute(query)
    query = 'ALTER TABLE task RENAME CONSTRAINT task_app_id_fkey TO task_project_id_fkey;'
    op.execute(query)
    query = 'ALTER TABLE task_run RENAME CONSTRAINT task_run_app_id_fkey TO task_run_project_id_fkey;'
    op.execute(query)

def downgrade():
    # Rename table name
    query = 'ALTER TABLE project RENAME TO app;'
    op.execute(query)
    # Rename id sequence
    query = 'ALTER SEQUENCE project_id_seq RENAME TO app_id_seq;'
    op.execute(query)
    # Rename foreign keys in other tables
    query = 'ALTER TABLE blogpost RENAME project_id TO app_id;'
    op.execute(query)
    query = 'ALTER TABLE task RENAME project_id TO app_id;'
    op.execute(query)
    query = 'ALTER TABLE task_run RENAME project_id TO app_id;'
    op.execute(query)
    query = 'ALTER TABLE auditlog RENAME project_id TO app_id;'
    op.execute(query)
    query = 'ALTER TABLE auditlog RENAME project_short_name TO app_short_name;'
    op.execute(query)
    # Rename primary and unique keys
    query = 'ALTER TABLE project_pkey RENAME TO app_pkey;'
    op.execute(query)
    query = 'ALTER TABLE project_name_key RENAME TO app_name_key;'
    op.execute(query)
    query = 'ALTER TABLE project_short_name_key RENAME TO app_short_name_key;'
    op.execute(query)
    # Rename foreign key constraints app table. NOTE: requires PostgreSQL 9.2 or above
    query = 'ALTER TABLE app RENAME CONSTRAINT project_category_id_fkey to app_category_id_fkey;'
    op.execute(query)
    query = 'ALTER TABLE app RENAME CONSTRAINT project_owner_id_fkey to app_owner_id_fkey;'
    op.execute(query)
    # Rename foreign key constraints in other tables. NOTE: requires PostgreSQL 9.2 or above
    query = 'ALTER TABLE blogpost RENAME CONSTRAINT blogpost_project_id_fkey TO blogpost_app_id_fkey;'
    op.execute(query)
    query = 'ALTER TABLE task RENAME CONSTRAINT task_project_id_fkey TO task_app_id_fkey;'
    op.execute(query)
    query = 'ALTER TABLE task_run RENAME CONSTRAINT task_run_project_id_fkey TO task_run_app_id_fkey;'
    op.execute(query)