This is an automated email from the ASF dual-hosted git repository. kaxilnaik pushed a commit to branch v1-10-test in repository https://gitbox.apache.org/repos/asf/airflow.git
commit 10eee5a966423a8f16c32957b8b680f20e353925 Author: Kaxil Naik <[email protected]> AuthorDate: Sat Nov 14 03:25:36 2020 +0000 Manage Flask AppBuilder Tables using Alembic Migrations (#12352) closes https://github.com/apache/airflow/issues/9155 The Migration is idempotent and allows both upgrade and downgrade. It also takes care of https://github.com/dpgaspar/Flask-AppBuilder/pull/1368 i.e. increasing the length of ab_view_menu.name column from 100 to 250 (cherry picked from commit 02ef8e1cb37c6232db9e5bc32555cf114125eabb) --- ...6b6f902_increase_length_of_fab_ab_view_menu_.py | 90 ++++++++++ .../versions/92c57b58940d_add_fab_tables.py | 182 +++++++++++++++++++++ 2 files changed, 272 insertions(+) diff --git a/airflow/migrations/versions/03afc6b6f902_increase_length_of_fab_ab_view_menu_.py b/airflow/migrations/versions/03afc6b6f902_increase_length_of_fab_ab_view_menu_.py new file mode 100644 index 0000000..aeb5665 --- /dev/null +++ b/airflow/migrations/versions/03afc6b6f902_increase_length_of_fab_ab_view_menu_.py @@ -0,0 +1,90 @@ +# +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +"""Increase length of FAB ab_view_menu.name column + +Revision ID: 03afc6b6f902 +Revises: 92c57b58940d +Create Date: 2020-11-13 22:21:41.619565 + +""" + +import sqlalchemy as sa +from alembic import op +from sqlalchemy.engine.reflection import Inspector + +# revision identifiers, used by Alembic. +revision = '03afc6b6f902' +down_revision = '92c57b58940d' +branch_labels = None +depends_on = None + + +def upgrade(): + """Apply Increase length of FAB ab_view_menu.name column""" + conn = op.get_bind() + inspector = Inspector.from_engine(conn) + tables = inspector.get_table_names() + + if "ab_view_menu" in tables: + if conn.dialect.name == "sqlite": + op.execute("PRAGMA foreign_keys=off") + op.execute( + """ + CREATE TABLE IF NOT EXISTS ab_view_menu_dg_tmp + ( + id INTEGER NOT NULL PRIMARY KEY, + name VARCHAR(250) NOT NULL UNIQUE + ); + """ + ) + op.execute("INSERT INTO ab_view_menu_dg_tmp(id, name) select id, name from ab_view_menu;") + op.execute("DROP TABLE ab_view_menu") + op.execute("ALTER TABLE ab_view_menu_dg_tmp rename to ab_view_menu;") + op.execute("PRAGMA foreign_keys=on") + else: + op.alter_column( + table_name='ab_view_menu', column_name='name', type_=sa.String(length=250), nullable=False + ) + + +def downgrade(): + """Unapply Increase length of FAB ab_view_menu.name column""" + conn = op.get_bind() + inspector = Inspector.from_engine(conn) + tables = inspector.get_table_names() + if "ab_view_menu" in tables: + if conn.dialect.name == "sqlite": + op.execute("PRAGMA foreign_keys=off") + op.execute( + """ + CREATE TABLE IF NOT EXISTS ab_view_menu_dg_tmp + ( + id INTEGER NOT NULL PRIMARY KEY, + name VARCHAR(100) NOT NULL UNIQUE + ); + """ + ) + op.execute("INSERT INTO ab_view_menu_dg_tmp(id, name) select id, name from ab_view_menu;") + op.execute("DROP TABLE ab_view_menu") + op.execute("ALTER TABLE ab_view_menu_dg_tmp rename to ab_view_menu;") + op.execute("PRAGMA foreign_keys=on") + else: + op.alter_column( + table_name='ab_view_menu', column_name='name', type_=sa.String(length=100), nullable=False + ) diff --git a/airflow/migrations/versions/92c57b58940d_add_fab_tables.py b/airflow/migrations/versions/92c57b58940d_add_fab_tables.py new file mode 100644 index 0000000..38f3c61 --- /dev/null +++ b/airflow/migrations/versions/92c57b58940d_add_fab_tables.py @@ -0,0 +1,182 @@ +# +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +"""Create FAB Tables + +Revision ID: 92c57b58940d +Revises: da3f683c3a5a +Create Date: 2020-11-13 19:27:10.161814 + +""" + +import sqlalchemy as sa +from alembic import op +from sqlalchemy.engine.reflection import Inspector + +# revision identifiers, used by Alembic. +revision = '92c57b58940d' +down_revision = 'da3f683c3a5a' +branch_labels = None +depends_on = None + + +def upgrade(): + """Create FAB Tables""" + conn = op.get_bind() + inspector = Inspector.from_engine(conn) + tables = inspector.get_table_names() + if "ab_permission" not in tables: + op.create_table( + 'ab_permission', + sa.Column('id', sa.Integer(), nullable=False, primary_key=True), + sa.Column('name', sa.String(length=100), nullable=False), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('name'), + ) + + if "ab_view_menu" not in tables: + op.create_table( + 'ab_view_menu', + sa.Column('id', sa.Integer(), nullable=False, primary_key=True), + sa.Column('name', sa.String(length=100), nullable=False), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('name'), + ) + + if "ab_role" not in tables: + op.create_table( + 'ab_role', + sa.Column('id', sa.Integer(), nullable=False, primary_key=True), + sa.Column('name', sa.String(length=64), nullable=False), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('name'), + ) + + if "ab_permission_view" not in tables: + op.create_table( + 'ab_permission_view', + sa.Column('id', sa.Integer(), nullable=False, primary_key=True), + sa.Column('permission_id', sa.Integer(), nullable=True), + sa.Column('view_menu_id', sa.Integer(), nullable=True), + sa.ForeignKeyConstraint(['permission_id'], ['ab_permission.id']), + sa.ForeignKeyConstraint(['view_menu_id'], ['ab_view_menu.id']), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('permission_id', 'view_menu_id'), + ) + + if "ab_permission_view_role" not in tables: + op.create_table( + 'ab_permission_view_role', + sa.Column('id', sa.Integer(), nullable=False, primary_key=True), + sa.Column('permission_view_id', sa.Integer(), nullable=True), + sa.Column('role_id', sa.Integer(), nullable=True), + sa.ForeignKeyConstraint(['permission_view_id'], ['ab_permission_view.id']), + sa.ForeignKeyConstraint(['role_id'], ['ab_role.id']), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint("permission_view_id", "role_id"), + ) + + if "ab_user" not in tables: + op.create_table( + 'ab_user', + sa.Column('id', sa.Integer(), nullable=False, primary_key=True), + sa.Column('first_name', sa.String(length=64), nullable=False), + sa.Column('last_name', sa.String(length=64), nullable=False), + sa.Column('username', sa.String(length=64), nullable=False), + sa.Column('password', sa.String(length=256), nullable=True), + sa.Column('active', sa.Boolean(), nullable=True), + sa.Column('email', sa.String(length=64), nullable=False), + sa.Column('last_login', sa.DateTime(), nullable=True), + sa.Column('login_count', sa.Integer(), nullable=True), + sa.Column('fail_login_count', sa.Integer(), nullable=True), + sa.Column('created_on', sa.DateTime(), nullable=True), + sa.Column('changed_on', sa.DateTime(), nullable=True), + sa.Column('created_by_fk', sa.Integer(), nullable=True), + sa.Column('changed_by_fk', sa.Integer(), nullable=True), + sa.ForeignKeyConstraint(['changed_by_fk'], ['ab_user.id']), + sa.ForeignKeyConstraint(['created_by_fk'], ['ab_user.id']), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('email'), + sa.UniqueConstraint('username'), + ) + + if "ab_user_role" not in tables: + op.create_table( + 'ab_user_role', + sa.Column('id', sa.Integer(), nullable=False, primary_key=True), + sa.Column('user_id', sa.Integer(), nullable=True), + sa.Column('role_id', sa.Integer(), nullable=True), + sa.ForeignKeyConstraint( + ['role_id'], + ['ab_role.id'], + ), + sa.ForeignKeyConstraint( + ['user_id'], + ['ab_user.id'], + ), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('user_id', 'role_id'), + ) + + if "ab_register_user" not in tables: + op.create_table( + 'ab_register_user', + sa.Column('id', sa.Integer(), nullable=False, primary_key=True), + sa.Column('first_name', sa.String(length=64), nullable=False), + sa.Column('last_name', sa.String(length=64), nullable=False), + sa.Column('username', sa.String(length=64), nullable=False), + sa.Column('password', sa.String(length=256), nullable=True), + sa.Column('email', sa.String(length=64), nullable=False), + sa.Column('registration_date', sa.DateTime(), nullable=True), + sa.Column('registration_hash', sa.String(length=256), nullable=True), + sa.PrimaryKeyConstraint('id'), + sa.UniqueConstraint('username'), + ) + + +def downgrade(): + """Drop FAB Tables""" + conn = op.get_bind() + inspector = Inspector.from_engine(conn) + tables = inspector.get_table_names() + fab_tables = [ + "ab_permission", + "ab_view_menu", + "ab_role", + "ab_permission_view", + "ab_permission_view_role", + "ab_user", + "ab_user_role", + "ab_register_user", + ] + + for table in fab_tables: + if table in tables: + indexes = inspector.get_foreign_keys(table) + for index in indexes: + if conn.dialect.name != "sqlite": + op.drop_constraint(index.get('name'), table, type_='foreignkey') + + for table in fab_tables: + if table in tables: + if conn.dialect.name == "sqlite": + op.execute("PRAGMA foreign_keys=off") + op.drop_table(table) + op.execute("PRAGMA foreign_keys=on") + else: + op.drop_table(table)
