You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by ka...@apache.org on 2020/11/19 20:56:37 UTC
[airflow] branch v1-10-test updated: Manage Flask AppBuilder Tables
using Alembic Migrations (#12352)
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
The following commit(s) were added to refs/heads/v1-10-test by this push:
new c3aadf5 Manage Flask AppBuilder Tables using Alembic Migrations (#12352)
c3aadf5 is described below
commit c3aadf5b07cfcf59001c8f31fa7c32ac5e63de77
Author: Kaxil Naik <ka...@gmail.com>
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)