def upgrade():
book = Book.__table__
### commands auto generated by Alembic - please adjust! ###
op.add_column('book', sa.Column('edition', sa.String(), nullable=True))
op.add_column('book', sa.Column('editors', sa.String(), nullable=True))
op.add_column('book', sa.Column('translator', sa.String(), nullable=True))
op.create_unique_constraint(None, 'book', ['title', 'subtitle', 'author'])
op.create_unique_constraint(None, 'book', ['isbn_10'])
op.create_unique_constraint(None, 'book', ['isbn_13'])
### end Alembic commands ###
#
# isbn_10 should be varchar; because some rows dropped a leading zero, those must be fixed
#
op.execute("ALTER TABLE book ALTER COLUMN isbn_10 TYPE varchar(10) USING isbn_10::varchar(10);")
update_isbn_10 = book.update().where(sa.sql.func.length(book.c.isbn_10) < 10).values(isbn_10 = sa.sql.func.concat('0', book.c.isbn_10))
op.execute(update_isbn_10)
op.create_check_constraint('check_isbn_10_length', 'book', sa.sql.func.length(sa.sql.column('isbn_10')) == 10)
#
# isbn_13 should be varchar of length 13; because some rows contain hyphens, we have to catch them.
#
op.execute("ALTER TABLE book ALTER COLUMN isbn_13 TYPE varchar(13) USING isbn_13::varchar(13);")
update_isbn_13 = book.update().where(sa.sql.func.length(book.c.isbn_13) > 13).values(isbn_13 = sa.sql.func.replace(book.c.isbn_13, '-', ''))
op.execute(update_isbn_13)
op.create_check_constraint('check_isbn_13_length', 'book', sa.sql.func.length(sa.sql.column('isbn_13')) == 13)
def upgrade():
op.create_table(
'reviews',
sa.Column('book_id', sa.Integer, nullable=False),
sa.Column('user_id', sa.Integer, nullable=False),
sa.Column('rating', sa.Integer, nullable=False),
sa.Column('review', sa.Text),
sa.Column('date_created', sa.DateTime, nullable=False, server_default=sa.func.now()),
sa.Column('date_modified', sa.DateTime, nullable=False, server_default=sa.func.now(), server_onupdate=FetchedValue())
)
op.create_check_constraint(
'rating_range_ck',
'reviews',
sa.sql.expression.between(sa.column('rating'), 1, 5)
)
op.create_foreign_key(
'reviews_books_fkey',
'reviews',
'books',
['book_id'],
['id']
)
op.create_foreign_key(
'reviews_users_fkey',
'reviews',
'users',
['user_id'],
['id']
)
op.create_index('reviews_book_id_ix', 'reviews', ['book_id'])
op.create_index('reviews_user_id_ix', 'reviews', ['user_id'])
def downgrade():
op.drop_constraint('hearing_section_main_image_id_fkey', 'hearing_section')
op.drop_constraint('hearing_main_image_id_fkey', 'hearing')
op.drop_constraint('comment_check', 'comment')
op.create_check_constraint(
'comment_check',
'comment',
'(comment.comment_id IS NOT NULL '
'AND comment.hearing_id IS NULL '
'AND comment.hearing_section_id IS NULL) '
'OR '
'(comment.comment_id IS NULL '
'AND comment.hearing_id IS NOT NULL '
'AND comment.hearing_section_id IS NULL) '
'OR '
'(comment.comment_id IS NULL '
'AND comment.hearing_id IS NULL '
'AND comment.hearing_section_id IS NOT NULL)'
)
op.drop_column('hearing_version', 'main_image_id')
op.drop_column('hearing_section_version', 'main_image_id')
op.drop_column('hearing_section', 'main_image_id')
op.drop_column('hearing', 'main_image_id')
op.drop_index(
op.f('ix_comment_version_image_id'), table_name='comment_version'
)
op.drop_column('comment_version', 'image_id')
op.drop_index(op.f('ix_comment_image_id'), table_name='comment')
op.drop_column('comment', 'image_id')
op.drop_index(op.f('ix_image_hearing_section_id'), table_name='image')
op.drop_index(op.f('ix_image_hearing_id'), table_name='image')
op.drop_table('image')
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column('archived_services', sa.Column('status', sa.String(), nullable=True))
op.add_column('services', sa.Column('status', sa.String(), nullable=True))
op.create_check_constraint(
"ck_services_status",
"services",
"status in ('disabled', 'enabled', 'published')"
)
op.create_check_constraint(
"ck_archived_services_status",
"archived_services",
"status in ('disabled', 'enabled', 'published')"
)
services = table('services', column('status', String))
archived_services = table('archived_services', column('status', String))
op.execute(
services.update(). \
values({'status': op.inline_literal('enabled')})
)
op.execute(
archived_services.update(). \
values({'status': op.inline_literal('enabled')})
)
def downgrade():
op.drop_constraint('ck_principals_valid_enum_type', 'principals', schema='events')
op.create_check_constraint('valid_enum_type', 'principals', "type IN ({})".format(', '.join(map(str, range(1, 5)))),
schema='events')
op.drop_constraint('ck_principals_valid_email', 'principals', schema='events')
op.drop_constraint('ck_principals_valid_local_group', 'principals', schema='events')
op.drop_constraint('ck_principals_valid_multipass_group', 'principals', schema='events')
op.drop_constraint('ck_principals_valid_user', 'principals', schema='events')
op.drop_constraint('ck_principals_valid_network', 'principals', schema='events')
op.create_check_constraint('valid_user', 'principals',
'type != 1 OR (email IS NULL AND local_group_id IS NULL AND mp_group_name IS NULL AND '
'mp_group_provider IS NULL AND user_id IS NOT NULL)',
schema='events'),
op.create_check_constraint('valid_local_group', 'principals',
' type != 2 OR (email IS NULL AND mp_group_name IS NULL AND '
'mp_group_provider IS NULL AND user_id IS NULL AND local_group_id IS NOT NULL)',
schema='events'),
op.create_check_constraint('valid_multipass_group', 'principals',
'type != 3 OR (email IS NULL AND local_group_id IS NULL AND user_id IS NULL AND '
'mp_group_name IS NOT NULL AND mp_group_provider IS NOT NULL)',
schema='events'),
op.create_check_constraint('valid_email', 'principals',
'type != 4 OR (local_group_id IS NULL AND mp_group_name IS NULL AND '
'mp_group_provider IS NULL AND user_id IS NULL AND email IS NOT NULL)',
schema='events'),
op.drop_constraint('ck_principals_networks_read_only', 'principals', schema='events')
op.drop_column('principals', 'ip_network_group_id', schema='events')
def upgrade():
op.add_column('rooms', sa.Column('verbose_name', sa.String(), nullable=True), schema='roombooking')
# set verbose_name only when the name is non-standard (B-F-N)
op.execute("UPDATE roombooking.rooms SET verbose_name = name "
"WHERE name != format('%s-%s-%s', building, floor, number)")
op.create_check_constraint('verbose_name_not_empty', 'rooms', "verbose_name != ''", schema='roombooking')
op.drop_column('rooms', 'name', schema='roombooking')
def upgrade():
op.create_table('research_protocols',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.Text(), nullable=False),
sa.Column('created_at', sa.DateTime(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.add_column(u'organizations', sa.Column('research_protocol_id',
sa.Integer(), nullable=True))
op.create_foreign_key('organizations_rp_id_fkey', 'organizations',
'research_protocols',
['research_protocol_id'], ['id'])
op.add_column('questionnaire_banks',
sa.Column('research_protocol_id', sa.Integer(),
nullable=True))
op.create_foreign_key('questionnaire_banks_rp_id_fkey',
'questionnaire_banks', 'research_protocols',
['research_protocol_id'], ['id'])
op.drop_constraint(u'questionnaire_banks_organization_id_fkey',
'questionnaire_banks', type_='foreignkey')
op.drop_constraint('ck_qb_intv_org_mutual_exclusion',
'questionnaire_banks')
migrate_to_rp(10000, 'TNGR v1')
migrate_to_rp(20000, 'IRONMAN v2')
op.create_check_constraint(
'ck_qb_intv_rp_mutual_exclusion',
'questionnaire_banks',
'NOT(research_protocol_id IS NULL AND intervention_id IS NULL) '
'AND NOT(research_protocol_id IS NOT NULL AND '
'intervention_id IS NOT NULL)')
op.drop_column('questionnaire_banks', 'organization_id')
def upgrade():
op.create_check_constraint("ck_started_before_ended",
"resource",
"started_at <= ended_at")
op.create_check_constraint("ck_started_before_ended",
"resource_history",
"started_at <= ended_at")
def downgrade():
""" Bring back the pull_request_check constraint. """
op.create_check_constraint(
"pull_requests_check",
"pull_requests",
'NOT(project_id_from IS NULL AND remote_git IS NULL)'
)
def downgrade():
if context.get_context().dialect.name not in ("sqlite", "mysql"):
op.drop_constraint("RSE_STAGING_AREA_CHK", "rses", type_="check")
op.drop_constraint("REQUESTS_TYPE_CHK", "requests", type_="check")
op.create_check_constraint(
name="REQUESTS_TYPE_CHK", source="requests", condition="request_type in ('U', 'D', 'T')"
)
op.drop_column("rses", "staging_area")
def upgrade():
op.add_column(
"release_files", sa.Column("sha256_digest", citext.CIText(), nullable=True)
)
op.create_unique_constraint(None, "release_files", ["sha256_digest"])
op.create_check_constraint(
None, "release_files", "sha256_digest ~* '^[A-F0-9]{64}$'"
)
def upgrade():
op.create_check_constraint('client_user_id_or_org_id', 'client',
sa.case([(column('user_id') != None, 1)], else_=0) + sa.case([(column('org_id') != None, 1)], else_=0) == 1 # NOQA
)
op.create_check_constraint('permission_user_id_or_org_id', 'permission',
sa.case([(column('user_id') != None, 1)], else_=0) + sa.case([(column('org_id') != None, 1)], else_=0) == 1 # NOQA
)
def upgrade():
op.add_column('events', sa.Column('cloned_from_id', sa.Integer(), nullable=True), schema='events')
op.create_index(None, 'events', ['cloned_from_id'], schema='events')
op.create_check_constraint('not_cloned_from_self', 'events', 'cloned_from_id != id', schema='events')
op.create_foreign_key(None, 'events', 'events',
['cloned_from_id'], ['id'],
source_schema='events',
referent_schema='events')
def upgrade():
op.add_column('users', sa.Column('role', sa.String(), nullable=False))
op.create_check_constraint(
"ck_users_role",
"users",
"role in ('supplier', 'admin', 'buyer')"
)
def test_add_check_constraint_name_is_none(self):
context = op_fixture(naming_convention={"ck": "ck_%(table_name)s_foo"})
op.create_check_constraint(
None, "user_table", func.len(column("name")) > 5
)
context.assert_(
"ALTER TABLE user_table ADD CONSTRAINT ck_user_table_foo "
"CHECK (len(name) > 5)"
)
def test_add_check_constraint(self):
context = op_fixture()
op.create_check_constraint(
"ck_user_name_len", "user_table", func.len(column("name")) > 5
)
context.assert_(
"ALTER TABLE user_table ADD CONSTRAINT ck_user_name_len "
"CHECK (len(name) > 5)"
)
def upgrade():
op.drop_constraint('ck_menu_entries_valid_title', 'menu_entries', schema='events')
op.create_check_constraint('valid_title', 'menu_entries',
"(type = 1 AND title IS NULL) OR (type IN (3, 5) AND title IS NOT NULL) OR "
"(type NOT IN (1, 3, 5))",
schema='events')
op.create_check_constraint('title_not_empty', 'menu_entries',
"title != ''",
schema='events')
def upgrade():
if context.get_context().dialect.name != "sqlite":
op.add_column("rses", sa.Column("staging_area", sa.Boolean(name="RSE_STAGING_AREA_CHK"), default=False))
if context.get_context().dialect.name not in ("sqlite", "mysql"):
op.drop_constraint("REQUESTS_TYPE_CHK", "requests", type_="check")
op.create_check_constraint(
name="REQUESTS_TYPE_CHK", source="requests", condition="request_type in ('U', 'D', 'T', 'I', '0')"
)
def upgrade():
op.drop_constraint('ck_events_category_id_matches_chain', 'events', schema='events')
op.drop_constraint('ck_events_category_chain_has_root', 'events', schema='events')
op.drop_constraint('ck_events_category_data_set', 'events', schema='events')
op.drop_column('events', 'category_chain', schema='events')
op.create_check_constraint('category_data_set', 'events', 'category_id IS NOT NULL OR is_deleted', schema='events')
op.create_index('ix_events_not_deleted_category', 'events', ['is_deleted', 'category_id'], schema='events')
op.create_index('ix_events_not_deleted_category_dates', 'events',
['is_deleted', 'category_id', 'start_dt', 'end_dt'], schema='events')
def test_add_constraint_schema_hard_quoting(self):
from sqlalchemy.sql.schema import quoted_name
context = op_fixture("postgresql")
op.create_check_constraint(
"ck_user_name_len",
"user_table",
func.len(column("name")) > 5,
schema=quoted_name("some.schema", quote=True),
)
context.assert_('ALTER TABLE "some.schema".user_table ADD ' "CONSTRAINT ck_user_name_len CHECK (len(name) > 5)")
def upgrade():
op.add_column('assignee', sa.Column('current', sa.Boolean(), nullable=True))
op.create_check_constraint('assignee_current_check', 'assignee', u"current != '0'")
op.add_column('assignee', sa.Column('line_item_id', sqlalchemy_utils.types.uuid.UUIDType(binary=False), nullable=False))
op.drop_index('assignee_email_key', table_name='assignee')
op.create_unique_constraint('assignee_line_item_current_key', 'assignee', ['line_item_id', 'current'])
op.drop_constraint(u'assignee_previous_id_fkey', 'assignee', type_='foreignkey')
op.create_foreign_key('assignee_line_item_id', 'assignee', 'line_item', ['line_item_id'], ['id'])
op.drop_column('assignee', 'previous_id')
op.drop_constraint(u'line_item_assignee_id_fkey', 'line_item', type_='foreignkey')
op.drop_column('line_item', 'assignee_id')
def upgrade():
op.add_column("events", sa.Column("category_chain", pg.ARRAY(sa.Integer()), nullable=True), schema="events")
op.add_column("events", sa.Column("category_id", sa.Integer(), nullable=True), schema="events")
op.create_index(None, "events", ["category_chain"], unique=False, schema="events", postgresql_using="gin")
op.create_index(None, "events", ["category_id"], unique=False, schema="events")
op.create_check_constraint(
"category_id_matches_chain", "events", "category_id = category_chain[1]", schema="events"
)
op.create_check_constraint(
"category_chain_has_root", "events", "category_chain[array_length(category_chain, 1)] = 0", schema="events"
)
def upgrade(pyramid_env):
from assembl.models.notification import (
NotificationSubscription, NotificationSubscriptionClasses)
with context.begin_transaction():
tname = "notification_subscription"
cname = 'ck_%s_%s_%s_notification_subscription_classes' % (
config.get('db_schema'), config.get('db_user'), tname)
op.drop_constraint(cname, tname)
op.create_check_constraint(
cname, tname, NotificationSubscription.type.in_(
NotificationSubscriptionClasses.values()))
def downgrade():
op.alter_column(
'users', 'role',
type_=sa.String(),
existing_type=sa.Enum('buyer', 'supplier', 'admin', name='user_roles_enum'),
)
op.create_check_constraint(
"ck_user_supplier_has_supplier_id",
"users",
"((role='buyer') or (role='admin') or (role = 'supplier' and supplier_id is not null))"
)
def upgrade():
op.add_column('users', sa.Column('supplier_id', sa.BigInteger(), nullable=True))
op.create_index(op.f('ix_users_supplier_id'), 'users', ['supplier_id'], unique=False)
op.create_foreign_key(None, 'users', 'suppliers', ['supplier_id'], ['supplier_id'])
op.create_check_constraint(
"ck_user_supplier_has_supplier_id",
"users",
"((role='buyer') or (role='admin') or (role = 'supplier' and supplier_id is not null))"
)
op.drop_constraint('ck_users_role', 'users', type_='check')
def test_add_check_constraint_schema(self):
context = op_fixture()
op.create_check_constraint(
"ck_user_name_len",
"user_table",
func.len(column('name')) > 5,
schema='foo'
)
context.assert_(
"ALTER TABLE foo.user_table ADD CONSTRAINT ck_user_name_len "
"CHECK (len(name) > 5)"
)
def upgrade():
op.alter_column(
'writeup_post_versions', 'threadpost_id',
nullable=False)
op.alter_column(
'writeup_post_versions', 'writeuppost_id',
nullable=True)
op.alter_column(
'writeup_post_versions', 'html',
nullable=True)
op.create_check_constraint(
'writeup_post_versions_check_html', 'writeup_post_versions',
sa.and_(sa.column('writeuppost_id') != sa.null(), sa.column('html') != sa.null()))
def upgrade():
op.create_table('account_attr_map',
sa.Column('account', sa.String(25)),
sa.Column('key', sa.String(255)),
sa.Column('value', sa.String(255)),
sa.Column('updated_at', sa.DateTime),
sa.Column('created_at', sa.DateTime))
if context.get_context().dialect.name != 'sqlite':
op.create_primary_key('ACCOUNT_ATTR_MAP_PK', 'account_attr_map', ['account', 'key'])
op.create_check_constraint('ACCOUNT_ATTR_MAP_CREATED_NN', 'account_attr_map', 'created_at is not null')
op.create_check_constraint('ACCOUNT_ATTR_MAP_UPDATED_NN', 'account_attr_map', 'updated_at is not null')
op.create_foreign_key('ACCOUNT_ATTR_MAP_ACCOUNT_FK', 'account_attr_map', 'accounts', ['account'], ['account'])
op.create_index('ACCOUNT_ATTR_MAP_KEY_VALUE_IDX', 'account_attr_map', ['key', 'value'])
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column('questionnaire_banks', sa.Column('intervention_id', sa.Integer(), nullable=True))
op.alter_column('questionnaire_banks', 'organization_id',
existing_type=sa.INTEGER(),
nullable=True)
op.create_foreign_key('questionnaire_banks_intervention_id_fkey', 'questionnaire_banks',
'interventions', ['intervention_id'], ['id'])
op.create_check_constraint(
'ck_qb_intv_org_mutual_exclusion',
'questionnaire_banks',
'NOT(organization_id IS NULL AND intervention_id IS NULL) '
'AND NOT(organization_id IS NOT NULL AND intervention_id IS NOT NULL)')
def downgrade():
op.add_column('project', sa.Column('old_state', sa.Integer(), nullable=False,
server_default=str(OLD_STATE.DRAFT)))
op.alter_column('project', 'old_state', server_default=None)
op.create_check_constraint(
'project_old_state_check',
'project',
'old_state IN (0, 1, 2, 3, 4, 5, 6)')
for old_state, new_state in downgrade_states.items():
op.execute(
project.update().where(project.c.state == new_state[0]).where(project.c.cfp_state == new_state[1]).values(
{'old_state': old_state}))
def upgrade():
op.drop_constraint('activity_ob_type_check', 'activity', type_='check')
op.drop_constraint('subscription_ob_type_check',
'subscription',
type_='check')
ob_types = array([
'organisation', 'user', 'program', 'survey', 'qnode', 'measure',
'submission', 'rnode', 'response'
],
type_=TEXT)
op.create_check_constraint(
'activity_ob_type_check', 'activity',
cast(column('ob_type'), TEXT) == func.any(ob_types))
op.create_check_constraint(
'subscription_ob_type_check', 'subscription',
cast(column('ob_type'), TEXT) == func.any(ob_types))
def upgrade():
op.add_column("bookmark_tags",
sa.Column("deleted", sa.Boolean(), nullable=False))
op.add_column(
"bookmark_tags",
sa.Column("updated", sa.DateTime(timezone=True), nullable=False),
)
op.create_index(op.f("ix_bookmark_tags_deleted"),
"bookmark_tags", ["deleted"],
unique=False)
op.create_index(op.f("ix_bookmark_tags_updated"),
"bookmark_tags", ["updated"],
unique=False)
op.create_check_constraint("ck_tags_tag_name", "tags",
"tag_name ~ '^[-a-z0-9]+$'")
op.create_check_constraint("ck_users_username", "users",
"username ~ '^[-A-z0-9]+$'")
def upgrade():
op.add_column(
'email_address',
sa.Column('active_at', sa.TIMESTAMP(timezone=True), nullable=True),
)
op.execute(email_address.update().where(
email_address.c.delivery_state == DELIVERY_STATE.ACTIVE).values({
'active_at':
email_address.c.delivery_state_at,
'delivery_state':
DELIVERY_STATE.SENT,
}))
op.create_check_constraint(
'email_address_delivery_state_check',
'email_address',
'delivery_state IN (0, 1, 3, 4)',
)
def upgrade():
'''
Upgrade the database to this revision
'''
if context.get_context().dialect.name in ['oracle', 'mysql', 'postgresql']:
schema = context.get_context(
).version_table_schema if context.get_context(
).version_table_schema else ''
add_column('rules',
sa.Column('purge_replicas',
sa.Boolean(name='RULES_PURGE_REPLICAS_CHK',
create_constraint=True),
default=False),
schema=schema)
create_check_constraint('RULES_PURGE_REPLICAS_NN', 'rules',
"PURGE_REPLICAS IS NOT NULL")
def downgrade():
discount_coupon = table('discount_coupon',
column('quantity_total', sa.Integer()),
column('quantity_available', sa.Integer()),
column('used', sa.Boolean()))
op.add_column(
'discount_policy',
sa.Column('item_quantity_max',
sa.INTEGER(),
autoincrement=False,
nullable=True))
op.add_column(
'discount_coupon',
sa.Column('quantity_total',
sa.INTEGER(),
autoincrement=False,
nullable=True))
op.add_column(
'discount_coupon',
sa.Column('quantity_available',
sa.INTEGER(),
autoincrement=False,
nullable=True))
op.execute(discount_coupon.update().values({'quantity_total': 1}))
op.execute(discount_coupon.update().where(
discount_coupon.c.used == True).values({'quantity_available': 0}))
op.execute(discount_coupon.update().where(
discount_coupon.c.used == False).values({'quantity_available': 1}))
op.execute(discount_coupon.update().where(
discount_coupon.c.used == None).values({'quantity_available': 1}))
op.alter_column('discount_coupon',
'quantity_total',
existing_type=sa.INTEGER(),
nullable=False)
op.alter_column('discount_coupon',
'quantity_available',
existing_type=sa.INTEGER(),
nullable=False)
op.drop_column('discount_coupon', 'used')
op.create_check_constraint('discount_coupon_quantity_check',
'discount_coupon',
u'quantity_available <= quantity_total')
op.create_check_constraint('discount_policy_item_quantity_check',
'discount_policy',
u'item_quantity_min <= item_quantity_max')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
'Assignment',
sa.Column('files_upload_enabled',
sa.Boolean(),
server_default='true',
nullable=False))
op.add_column(
'Assignment',
sa.Column('webhook_upload_enabled',
sa.Boolean(),
server_default='false',
nullable=False))
op.create_check_constraint(
"upload_methods_check", "Assignment",
"files_upload_enabled or webhook_upload_enabled")
def upgrade():
'''
Upgrade the database to this revision
'''
if context.get_context().dialect.name in ['oracle', 'mysql', 'postgresql']:
create_table('replicas_history', sa.Column('rse_id', GUID()),
sa.Column('scope', sa.String(25)),
sa.Column('name', sa.String(255)),
sa.Column('bytes', sa.BigInteger))
create_primary_key('REPLICAS_HIST_PK', 'replicas_history',
['rse_id', 'scope', 'name'])
create_foreign_key('REPLICAS_HIST_RSE_ID_FK', 'replicas_history',
'rses', ['rse_id'], ['id'])
create_check_constraint('REPLICAS_HIST_SIZE_NN', 'replicas_history',
'bytes IS NOT NULL')
def downgrade():
op.add_column("offerer", sa.Column("iban", sa.VARCHAR(27), nullable=True))
op.add_column("offerer", sa.Column("bic", sa.VARCHAR(11), nullable=True))
op.create_check_constraint(
constraint_name="check_iban_and_bic_xor_not_iban_and_not_bic",
table_name="offerer",
condition=
'("iban" IS NULL AND "bic" IS NULL) OR ("iban" IS NOT NULL AND "bic" IS NOT NULL)',
)
op.add_column("venue", sa.Column("iban", sa.VARCHAR(27), nullable=True))
op.add_column("venue", sa.Column("bic", sa.VARCHAR(11), nullable=True))
op.create_check_constraint(
constraint_name="check_iban_and_bic_xor_not_iban_and_not_bic",
table_name="venue",
condition=
'("iban" IS NULL AND "bic" IS NULL) OR ("iban" IS NOT NULL AND "bic" IS NOT NULL)',
)
def upgrade():
op.create_table(
"topic_schedule",
sa.Column("schedule_id", sa.Integer(), nullable=False),
sa.Column("group_id", sa.Integer(), nullable=False),
sa.Column("user_id", sa.Integer(), nullable=True),
sa.Column(
"created_time",
sa.TIMESTAMP(timezone=True),
server_default=sa.text("NOW()"),
nullable=False,
),
sa.Column("title", sa.Text(), nullable=False),
sa.Column("markdown", sa.Text(), nullable=False),
sa.Column("tags", ArrayOfLtree(), server_default="{}", nullable=False),
sa.Column("next_post_time", sa.TIMESTAMP(timezone=True),
nullable=True),
sa.Column("recurrence_rule", sa.Text(), nullable=True),
sa.ForeignKeyConstraint(
["group_id"],
["groups.group_id"],
name=op.f("fk_topic_schedule_group_id_groups"),
),
sa.ForeignKeyConstraint(["user_id"], ["users.user_id"],
name=op.f("fk_topic_schedule_user_id_users")),
sa.PrimaryKeyConstraint("schedule_id", name=op.f("pk_topic_schedule")),
)
op.create_index(op.f("ix_topic_schedule_group_id"),
"topic_schedule", ["group_id"],
unique=False)
op.create_index(
op.f("ix_topic_schedule_next_post_time"),
"topic_schedule",
["next_post_time"],
unique=False,
)
op.create_check_constraint("title_length", "topic_schedule",
"LENGTH(title) <= 200")
# add the generic Tildes user (used to post scheduled topics by default)
op.execute("""
INSERT INTO users (user_id, username, password_hash)
VALUES(-1, 'Tildes', '')
ON CONFLICT DO NOTHING
""")
def upgrade():
op.add_column('forms',
sa.Column('publish_registrations_public',
PyIntEnum(_PublishRegistrationsMode),
server_default='0',
nullable=False),
schema='event_registration')
op.alter_column('forms',
'publish_registrations_public',
server_default=None,
schema='event_registration')
op.execute(
'UPDATE event_registration.forms SET publish_registrations_public = 2 WHERE publish_registrations_enabled'
)
op.add_column('forms',
sa.Column('publish_registrations_participants',
PyIntEnum(_PublishRegistrationsMode),
server_default='0',
nullable=False),
schema='event_registration')
op.alter_column('forms',
'publish_registrations_participants',
server_default=None,
schema='event_registration')
op.execute(
'UPDATE event_registration.forms SET publish_registrations_participants = 2 WHERE publish_registrations_enabled'
)
op.drop_column('forms',
'publish_registrations_enabled',
schema='event_registration')
op.create_check_constraint(
'publish_registrations_more_restrictive_to_public',
'forms',
'publish_registrations_public <= publish_registrations_participants',
schema='event_registration')
op.add_column('registrations',
sa.Column('consent_to_publish',
PyIntEnum(_PublishConsentType),
server_default='2',
nullable=False),
schema='event_registration')
op.alter_column('registrations',
'consent_to_publish',
server_default=None,
schema='event_registration')
def upgrade():
'''
Upgrade the database to this revision
'''
if context.get_context().dialect.name in ['oracle', 'mysql', 'postgresql']:
create_table(
'collection_replicas', sa.Column('scope', sa.String(25)),
sa.Column('name', sa.String(255)),
sa.Column(
'did_type',
sa.Enum(DIDType,
name='COLLECTION_REPLICAS_TYPE_CHK',
create_constraint=True,
values_callable=lambda obj: [e.value for e in obj])),
sa.Column('rse_id', GUID()), sa.Column('bytes', sa.BigInteger),
sa.Column('length', sa.BigInteger),
sa.Column('state',
sa.Enum(
ReplicaState,
name='COLLECTION_REPLICAS_STATE_CHK',
create_constraint=True,
values_callable=lambda obj: [e.value for e in obj]),
default=ReplicaState.UNAVAILABLE),
sa.Column('accessed_at', sa.DateTime),
sa.Column('created_at',
sa.DateTime,
default=datetime.datetime.utcnow),
sa.Column('updated_at',
sa.DateTime,
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow))
create_primary_key('COLLECTION_REPLICAS_PK', 'collection_replicas',
['scope', 'name', 'rse_id'])
create_foreign_key('COLLECTION_REPLICAS_LFN_FK', 'collection_replicas',
'dids', ['scope', 'name'], ['scope', 'name'])
create_foreign_key('COLLECTION_REPLICAS_RSE_ID_FK',
'collection_replicas', 'rses', ['rse_id'], ['id'])
create_check_constraint('COLLECTION_REPLICAS_SIZE_NN',
'collection_replicas', 'bytes IS NOT NULL')
create_check_constraint('COLLECTION_REPLICAS_STATE_NN',
'collection_replicas', 'state IS NOT NULL')
create_index('COLLECTION_REPLICAS_RSE_ID_IDX', 'collection_replicas',
['rse_id'])
def upgrade():
for tablename in tables_with_name_column:
# Create CHECK constraint on name
op.create_check_constraint(tablename + '_name_check', tablename,
"name <> ''")
# RENAME CONSTRAINT works in PostgreSQL >= 9.2
op.execute(
sa.DDL(
'ALTER TABLE comment RENAME CONSTRAINT ck_comment_state_valid TO comment_status_check;'
))
op.execute(
sa.DDL(
'ALTER TABLE proposal RENAME CONSTRAINT ck_proposal_state_valid TO proposal_status_check;'
))
op.execute(
sa.DDL(
'ALTER TABLE proposal_space RENAME CONSTRAINT ck_proposal_space_state_valid TO proposal_space_status_check;'
))
op.execute(
sa.DDL(
'ALTER TABLE rsvp RENAME CONSTRAINT ck_rsvp_state_valid TO rsvp_status_check;'
))
op.execute(
sa.DDL(
'ALTER TABLE contact_exchange RENAME CONSTRAINT contact_exchange_user_id_proposal_space_id_participant_id_key TO contact_exchange_pkey;'
))
op.execute(
sa.DDL(
'ALTER TABLE proposal_space RENAME CONSTRAINT proposal_space_proposal_space_id_fkey TO proposal_space_parent_space_id_fkey;'
))
op.alter_column('proposal_redirect',
'url_id',
existing_type=sa.INTEGER(),
server_default=None)
op.execute(DropSequence(Sequence('proposal_redirect_url_id_seq')))
op.alter_column(
'user',
'userinfo',
type_=JsonDict(),
existing_type=sa.TEXT(),
postgresql_using='userinfo::jsonb',
)
def downgrade():
'''
Downgrade the database to the previous revision
'''
if context.get_context().dialect.name in ['oracle', 'mysql']:
drop_table('bad_pfns')
drop_index('BAD_REPLICAS_EXPIRES_AT_IDX', 'bad_replicas')
drop_constraint('BAD_REPLICAS_STATE_CHK',
'bad_replicas',
type_='check')
create_check_constraint(constraint_name='BAD_REPLICAS_STATE_CHK',
table_name='bad_replicas',
condition="state in ('B', 'D', 'L', 'R', 'S')")
drop_column('bad_replicas', 'expires_at')
drop_constraint('BAD_REPLICAS_STATE_PK',
'bad_replicas',
type_='primary')
create_primary_key('BAD_REPLICAS_STATE_PK', 'bad_replicas',
['scope', 'name', 'rse_id', 'created_at'])
elif context.get_context().dialect.name == 'postgresql':
schema = context.get_context(
).version_table_schema + '.' if context.get_context(
).version_table_schema else ''
drop_table('bad_pfns')
drop_index('BAD_REPLICAS_EXPIRES_AT_IDX', 'bad_replicas')
op.execute(
'ALTER TABLE ' + schema +
'bad_replicas DROP CONSTRAINT IF EXISTS "BAD_REPLICAS_STATE_CHK", ALTER COLUMN state TYPE CHAR'
) # pylint: disable=no-member
create_check_constraint(constraint_name='BAD_REPLICAS_STATE_CHK',
table_name='bad_replicas',
condition="state in ('B', 'D', 'L', 'R', 'S')")
drop_column('bad_replicas', 'expires_at', schema=schema[:-1])
drop_constraint('BAD_REPLICAS_STATE_PK',
'bad_replicas',
type_='primary')
create_primary_key('BAD_REPLICAS_STATE_PK', 'bad_replicas',
['scope', 'name', 'rse_id', 'created_at'])
def downgrade():
'''
Downgrade the database to the previous revision
'''
if context.get_context().dialect.name in ['oracle', 'postgresql', 'mysql']:
drop_constraint('REPLICAS_STATE_CHK', 'replicas', type_='check')
create_check_constraint(
constraint_name='REPLICAS_STATE_CHK',
table_name='replicas',
condition="state in ('A', 'U', 'C', 'B', 'D', 'S', 'T')")
drop_constraint('COLLECTION_REPLICAS_STATE_CHK',
'collection_replicas',
type_='check')
create_check_constraint(
constraint_name='COLLECTION_REPLICAS_STATE_CHK',
table_name='collection_replicas',
condition="state in ('A', 'U', 'C', 'B', 'D', 'S', 'T')")
def upgrade():
op.add_column('users',
sa.Column('is_system',
sa.Boolean(),
nullable=False,
server_default='false'),
schema='users')
op.alter_column('users', 'is_system', server_default=None, schema='users')
op.create_index(None,
'users', ['is_system'],
unique=True,
schema='users',
postgresql_where=sa.text('is_system'))
op.create_check_constraint(
'valid_system_user',
'users',
'NOT is_system OR (NOT is_blocked AND NOT is_pending AND NOT is_deleted)',
schema='users')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.add_column(
"employment",
sa.Column("invite_token", sa.String(length=255), nullable=True),
)
op.add_column(
"employment", sa.Column("email", sa.String(length=255), nullable=True)
)
op.alter_column(
"employment", "user_id", existing_type=sa.INTEGER(), nullable=True
)
op.create_unique_constraint(None, "employment", ["invite_token"])
op.create_check_constraint(
"either_user_id_or_invite_token_is_set",
"employment",
"((user_id is not null)::bool != (invite_token is not null)::bool)",
)
def upgrade():
op.add_column("dossiers", sa.Column("an_id", sa.Text(), nullable=True))
op.add_column("dossiers", sa.Column("senat_id", sa.Text(), nullable=True))
op.create_unique_constraint(op.f("dossiers_an_id_key"), "dossiers", ["an_id"])
op.create_unique_constraint(op.f("dossiers_senat_id_key"), "dossiers", ["senat_id"])
op.execute(
"""
UPDATE dossiers SET an_id = uid WHERE uid LIKE 'DL%';
UPDATE dossiers SET senat_id = uid WHERE uid NOT LIKE 'DL%';
"""
)
op.create_check_constraint(
op.f("ck_dossiers_an_id_senat_id"),
"dossiers",
"an_id IS NOT NULL OR senat_id IS NOT NULL",
)
op.drop_constraint("dossiers_uid_key", "dossiers", type_="unique")
op.drop_column("dossiers", "uid")
def downgrade():
op.execute("UPDATE alert_events SET predicted_delay = null;")
op.execute("ALTER TABLE alert_events ALTER COLUMN predicted_delay TYPE integer "
"USING predicted_delay::integer;")
op.execute("ALTER TABLE alert_events ALTER COLUMN day TYPE integer USING ( "
"CASE day::day "
"WHEN 'Sunday' then '0' "
"WHEN 'Monday' then '1' "
"WHEN 'Tuesday' then '2' "
"WHEN 'Wednesday' then '3' "
"WHEN 'Thursday' then '4' "
"WHEN 'Friday' then '5' "
"WHEN 'Saturday' then '6' "
"ELSE null "
"END)::integer")
op.create_check_constraint(
"alert_events_day_check", "alert_events", 'day >= 0 AND day <= 6')
ENUM(name="day").drop(op.get_bind(), checkfirst=False)
def downgrade():
'''
Downgrade the database to the previous revision
'''
if context.get_context().dialect.name in ['oracle', 'mysql', 'postgresql']:
drop_table('account_limits')
create_table(
'account_limits', sa.Column('account', sa.String(25)),
sa.Column('rse_expression', sa.String(255)),
sa.Column('name', sa.String(255)),
sa.Column('value', sa.BigInteger),
sa.Column('created_at',
sa.DateTime,
default=datetime.datetime.utcnow),
sa.Column('updated_at',
sa.DateTime,
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow))
create_primary_key('ACCOUNT_LIMITS_PK', 'account_limits',
['account', 'rse_expression', 'name'])
create_check_constraint('ACCOUNT_LIMITS_CREATED_NN', 'account_limits',
'created_at is not null')
create_check_constraint('ACCOUNT_LIMITS_UPDATED_NN', 'account_limits',
'updated_at is not null')
create_foreign_key('ACCOUNT_LIMITS_ACCOUNT_FK', 'account_limits',
'accounts', ['account'], ['account'])
elif context.get_context().dialect.name == 'mysql':
drop_table('account_limits')
create_table(
'account_limits', sa.Column('account', sa.String(25)),
sa.Column('rse_expression', sa.String(255)),
sa.Column('name', sa.String(255)),
sa.Column('value', sa.BigInteger),
sa.Column('created_at',
sa.DateTime,
default=datetime.datetime.utcnow),
sa.Column('updated_at',
sa.DateTime,
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow))
create_primary_key('ACCOUNT_LIMITS_PK', 'account_limits',
['account', 'rse_expression', 'name'])
create_check_constraint('ACCOUNT_LIMITS_CREATED_NN', 'account_limits',
'created_at is not null')
create_check_constraint('ACCOUNT_LIMITS_UPDATED_NN', 'account_limits',
'updated_at is not null')
create_foreign_key('ACCOUNT_LIMITS_ACCOUNT_FK', 'account_limits',
'accounts', ['account'], ['account'])
def upgrade():
'''
Upgrade the database to this revision
'''
if context.get_context().dialect.name in ['oracle', 'mysql', 'postgresql']:
create_table(
'quarantined_replicas', sa.Column('rse_id', GUID()),
sa.Column('path', sa.String(1024)),
sa.Column('bytes', sa.BigInteger), sa.Column('md5', sa.String(32)),
sa.Column('adler32', sa.String(8)),
sa.Column('scope', sa.String(SCOPE_LENGTH)),
sa.Column('name', sa.String(NAME_LENGTH)),
sa.Column('created_at',
sa.DateTime,
default=datetime.datetime.utcnow),
sa.Column('updated_at',
sa.DateTime,
default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow))
create_table(
'quarantined_replicas_history', sa.Column('rse_id', GUID()),
sa.Column('path', sa.String(1024)),
sa.Column('bytes', sa.BigInteger), sa.Column('md5', sa.String(32)),
sa.Column('adler32', sa.String(8)),
sa.Column('scope', sa.String(SCOPE_LENGTH)),
sa.Column('name', sa.String(NAME_LENGTH)),
sa.Column('created_at', sa.DateTime),
sa.Column('updated_at', sa.DateTime),
sa.Column('deleted_at',
sa.DateTime,
default=datetime.datetime.utcnow))
create_primary_key('QURD_REPLICAS_STATE_PK', 'quarantined_replicas',
['rse_id', 'path'])
create_check_constraint('QURD_REPLICAS_CREATED_NN',
'quarantined_replicas',
'created_at is not null')
create_check_constraint('QURD_REPLICAS_UPDATED_NN',
'quarantined_replicas',
'updated_at is not null')
create_foreign_key('QURD_REPLICAS_RSE_ID_FK', 'quarantined_replicas',
'rses', ['rse_id'], ['id'])
def upgrade():
bind = op.get_bind()
inspector = Inspector.from_engine(bind)
for table in ("resource", "resource_history"):
existing_cks = [
c['name'] for c in inspector.get_check_constraints(table)
]
if "ck_started_before_ended" in existing_cks:
# Drop non-uniquely named check constraints
# for consistency across DB types.
op.drop_constraint("ck_started_before_ended", table, type_="check")
new_ck_name = "ck_{}_started_before_ended".format(table)
if new_ck_name not in existing_cks:
# Re-create check constraint with unique name
# if needed
op.create_check_constraint(new_ck_name, table,
"started_at <= ended_at")
def upgrade():
'''
Upgrade the database to this revision
'''
if context.get_context().dialect.name in ['oracle', 'mysql', 'postgresql']:
create_table('heartbeats',
sa.Column('executable', sa.String(512)),
sa.Column('hostname', sa.String(128)),
sa.Column('pid', sa.Integer(), autoincrement=False),
sa.Column('thread_id', sa.BigInteger(), autoincrement=False),
sa.Column('thread_name', sa.String(64)),
sa.Column('created_at', sa.DateTime, default=datetime.datetime.utcnow),
sa.Column('updated_at', sa.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow))
create_primary_key('heartbeats_pk', 'heartbeats', ['executable', 'hostname', 'pid', 'thread_id'])
create_index('heartbeats_updated_at', 'heartbeats', ['updated_at'])
create_check_constraint('heartbeats_created_nn', 'heartbeats', 'created_at is not null')
create_check_constraint('heartbeats_updated_nn', 'heartbeats', 'updated_at is not null')
def upgrade():
op.add_column('venue', sa.Column('comment', sa.TEXT, nullable=True))
op.execute(
"""
UPDATE venue
SET comment = 'Merci de contacter l''équipe pass Culture pour lui communiquer le SIRET de ce lieu.'
WHERE siret IS NULL
AND "isVirtual" is FALSE;
"""
)
op.create_check_constraint(
constraint_name='check_has_siret_xor_comment_xor_isVirtual',
table_name='venue',
condition="""
(siret IS NULL AND comment IS NULL AND "isVirtual" IS TRUE)
OR (siret IS NULL AND comment IS NOT NULL AND "isVirtual" IS FALSE)
OR (siret IS NOT NULL AND comment IS NULL AND "isVirtual" IS FALSE)
"""
)
def upgrade():
op.create_table(
'bank_information',
sa.Column('id', sa.BigInteger, primary_key=True, autoincrement=True),
sa.Column('offererId', sa.BigInteger, ForeignKey('offerer.id'), nullable=True),
sa.Column('venueId', sa.BigInteger, ForeignKey('venue.id'), nullable=True),
sa.Column('iban', sa.String(27), nullable=False),
sa.Column('bic', sa.String(11), nullable=False),
sa.Column('applicationId', sa.Integer, nullable=False),
sa.Column('idAtProviders', sa.String(70), nullable=False, unique=True),
sa.Column('dateModifiedAtLastProvider', sa.DateTime, nullable=True, default=datetime.utcnow),
sa.Column('lastProviderId', sa.BigInteger, ForeignKey('provider.id'), nullable=True)
)
op.create_check_constraint(
constraint_name='check_providable_with_provider_has_idatproviders',
table_name='bank_information',
condition='"lastProviderId" IS NULL OR "idAtProviders" IS NOT NULL'
)
def upgrade():
op.add_column('users',
sa.Column('picture', sa.LargeBinary(), nullable=True),
schema='users')
op.add_column('users',
sa.Column('picture_metadata',
postgresql.JSONB(),
nullable=False,
server_default='null'),
schema='users')
op.alter_column('users',
'picture_metadata',
server_default=None,
schema='users')
op.create_check_constraint(
'valid_picture',
'users',
"(picture IS NULL) = (picture_metadata::text = 'null')",
schema='users')
def downgrade():
op.drop_constraint('check_is_virtual_xor_has_address', 'venue')
op.execute("""
UPDATE venue SET address = '' WHERE siret IS NOT NULL AND address IS NULL;
""")
op.create_check_constraint(
constraint_name='check_is_virtual_xor_has_address',
table_name='venue',
condition="""
(
"isVirtual" IS TRUE
AND (address IS NULL AND "postalCode" IS NULL AND city IS NULL AND "departementCode" IS NULL)
)
OR
(
"isVirtual" IS FALSE
AND (address IS NOT NULL AND "postalCode" IS NOT NULL AND city IS NOT NULL AND "departementCode" IS NOT NULL)
)
""")
def downgrade():
'''
Downgrade the database to the previous revision
'''
if context.get_context().dialect.name in ['oracle', 'postgresql']:
drop_constraint('REQUESTS_STATE_CHK', 'requests', type_='check')
create_check_constraint(
constraint_name='REQUESTS_STATE_CHK',
table_name='requests',
condition=
"state in ('Q', 'G', 'S', 'D', 'F', 'L', 'N', 'O', 'A', 'U')")
elif context.get_context().dialect.name == 'mysql':
create_check_constraint(
constraint_name='REQUESTS_STATE_CHK',
table_name='requests',
condition=
"state in ('Q', 'G', 'S', 'D', 'F', 'L', 'N', 'O', 'A', 'U')")
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.drop_index("ix_pages_main_page_for_cluster_id", table_name="pages")
op.drop_constraint("fk_pages_main_page_for_cluster_id_clusters",
"pages",
type_="foreignkey")
op.drop_column("pages", "main_page_for_cluster_id")
op.create_index(
"ix_pages_owner_cluster_id_type",
"pages",
["owner_cluster_id", "type"],
unique=True,
postgresql_where=sa.text("type = 'main_page'"),
)
op.create_check_constraint(
"ck_pages_main_page_owned_by_cluster",
"pages",
"NOT (owner_cluster_id IS NULL AND type = 'main_page')",
)
def upgrade():
# clear existing new email stuff
op.execute(
"UPDATE users SET new_email = NULL, new_email_token = NULL, new_email_token_created = NULL, new_email_token_expiry = NULL"
)
op.add_column(
"users",
sa.Column("need_to_confirm_via_new_email", sa.Boolean(),
nullable=True))
op.add_column(
"users",
sa.Column("need_to_confirm_via_old_email", sa.Boolean(),
nullable=True))
op.add_column("users",
sa.Column("old_email_token", sa.String(), nullable=True))
op.add_column(
"users",
sa.Column("old_email_token_created",
sa.DateTime(timezone=True),
nullable=True))
op.add_column(
"users",
sa.Column("old_email_token_expiry",
sa.DateTime(timezone=True),
nullable=True))
op.create_check_constraint(
constraint_name="check_old_email_token_state",
table_name="users",
condition=
"(need_to_confirm_via_old_email IS NULL AND old_email_token IS NULL AND old_email_token_created IS NULL AND old_email_token_expiry IS NULL) OR \
(need_to_confirm_via_old_email IS TRUE AND old_email_token IS NOT NULL AND old_email_token_created IS NOT NULL AND old_email_token_expiry IS NOT NULL) OR \
(need_to_confirm_via_old_email IS FALSE AND old_email_token IS NULL AND old_email_token_created IS NULL AND old_email_token_expiry IS NULL)",
)
op.create_check_constraint(
constraint_name="check_new_email_token_state",
table_name="users",
condition=
"(need_to_confirm_via_new_email IS NULL AND new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL) OR \
(need_to_confirm_via_new_email IS TRUE AND new_email_token IS NOT NULL AND new_email_token_created IS NOT NULL AND new_email_token_expiry IS NOT NULL) OR \
(need_to_confirm_via_new_email IS FALSE AND new_email_token IS NULL AND new_email_token_created IS NULL AND new_email_token_expiry IS NULL)",
)
def downgrade():
op.execute("""
ALTER TABLE mediation
ADD "firstThumbDominantColor" BYTEA DEFAULT 'b'\x00\x00\x00'' NOT NULL
""")
op.execute("""
ALTER TABLE product
ADD "firstThumbDominantColor" BYTEA DEFAULT 'b'\x00\x00\x00'' NOT NULL
""")
op.create_check_constraint(
constraint_name="check_thumb_has_dominant_color",
table_name="mediation",
condition='"thumbCount"=0 OR "firstThumbDominantColor" IS NOT NULL',
)
op.create_check_constraint(
constraint_name="check_thumb_has_dominant_color",
table_name="product",
condition='"thumbCount"=0 OR "firstThumbDominantColor" IS NOT NULL',
)
