depends_on = None
authorizations_view = CustomSql('authorizations_view',
'SELECT e.business_identifier, '
'e.NAME AS entity_name, '
'e.corp_type_code, '
'm.membership_type_code AS org_membership, '
'u.keycloak_guid, '
'u.id AS user_id, '
'o.id AS org_id, '
'o.type_code AS org_type, '
'ps.product_code AS product_code, '
'(SELECT String_agg(prc.code, \',\') '
' FROM product_role pr, '
' product_role_code prc '
'WHERE pr.product_subscription_id = ps.id '
' AND prc.id = pr.product_role_id) AS roles '
'FROM membership m '
' LEFT JOIN org o '
' ON m.org_id = o.id '
' LEFT JOIN "user" u '
' ON u.id = m.user_id '
' LEFT JOIN affiliation a '
' ON o.id = a.org_id '
' LEFT JOIN entity e '
' ON e.id = a.entity_id '
' LEFT JOIN product_subscription ps '
' ON ps.org_id = o.id '
' WHERE m.status = 1;')
def upgrade():
revision = '69a7e464fef3'
down_revision = '5053985bdfc6'
branch_labels = None
depends_on = None
authorizations_view = CustomSql(
'authorizations_view', ' SELECT e.business_identifier,'
'e.name AS entity_name,'
'e.folio_number,'
'e.corp_type_code,'
'm.membership_type_code AS org_membership,'
'u.keycloak_guid,'
'u.id AS user_id,'
'o.id AS org_id,'
'o.name AS org_name,'
'o.status_code,'
'o.type_code AS org_type,'
'ps.product_code,'
'o.bcol_user_id,'
'o.bcol_account_id'
' FROM memberships m '
'LEFT JOIN orgs o ON m.org_id = o.id '
'LEFT JOIN users u ON u.id = m.user_id '
'LEFT JOIN affiliations a ON o.id = a.org_id '
'LEFT JOIN entities e ON e.id = a.entity_id '
'LEFT JOIN product_subscriptions ps ON ps.org_id = o.id '
'WHERE m.status = 1;')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
permissions_table = table(
"""
import sqlalchemy as sa
from alembic import op
from auth_api.utils.custom_sql import CustomSql
# revision identifiers, used by Alembic.
revision = 'd59e32d1f78b'
down_revision = '93ba1db65ed3'
branch_labels = None
depends_on = None
authorizations_view = CustomSql(
'authorizations_view',
' SELECT e.business_identifier, e.name AS entity_name,e.corp_type_code as corp_type_code, m.membership_type_code AS org_membership, u.keycloak_guid, u.id AS user_id, o.id AS org_id, o.type_code AS org_type'
' FROM (( ( membership m left join org o on m.org_id = o.id ) '
'left join "user" u on u.id = m.user_id ) '
'left join affiliation a on o.id = a.org_id) '
'left join entity e on e.id = a.entity_id '
'where m.status =1 ')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
corp_type_table = op.create_table(
'corp_type', sa.Column('created', sa.DateTime(), nullable=True),
sa.Column('modified', sa.DateTime(), nullable=True),
sa.Column('code', sa.String(length=15), nullable=False),
sa.Column('desc', sa.String(length=100), nullable=True),
sa.Column('default', sa.Boolean(), nullable=False),
sa.Column('created_by_id', sa.Integer(), nullable=True),
sa.Column('modified_by_id', sa.Integer(), nullable=True),
authorizations_view_new = CustomSql('authorizations_view',
'SELECT '
' e.business_identifier, '
' e.name AS entity_name, '
' e.folio_number AS folio_number, '
' e.corp_type_code, '
' m.membership_type_code AS org_membership, '
' u.keycloak_guid, '
' u.id AS user_id, '
' o.id AS org_id, '
' o.name AS org_name, '
' o.type_code AS org_type, '
' ps.product_code AS product_code, '
' pay.preferred_payment_code as preferred_payment_code, '
' pay.bcol_user_id as bcol_user_id, '
' pay.bcol_account_id as bcol_account_id, '
' (SELECT String_agg(prc.code, \',\') '
' FROM product_subscription_role pr, '
' product_role_code prc '
' WHERE pr.product_subscription_id = ps.id '
' AND prc.id = pr.product_role_id) AS ROLES '
'FROM '
' membership m '
' left join org o '
' ON m.org_id = o.id '
' left join "user" u '
' ON u.id = m.user_id '
' left join affiliation a '
' ON o.id = a.org_id '
' left join entity e '
' ON e.id = a.entity_id '
' left join product_subscription ps '
' ON ps.org_id = o.id '
' left join account_payment_settings pay '
' ON pay.org_id = o.id '
'WHERE '
' m.status = 1'
' AND pay.is_active = true; ')
from alembic import op
from auth_api.utils.custom_sql import CustomSql
# revision identifiers, used by Alembic.
revision = 'b32d868d628c'
down_revision = 'd46ec0214eb2'
branch_labels = None
depends_on = None
authorizations_view = CustomSql(
'authorizations_view', 'select '
'e.business_identifier, e.name as entity_name, m.membership_type_code as role, '
'u.keycloak_guid, u.id as user_id '
'from '
'entity e, affiliation a, org o, membership m, public.user u '
'where '
'e.id = a.entity_id '
'and o.id = a.org_id '
'and m.org_id = o.id '
'and u.id = m.user_id ')
def upgrade():
op.execute(
f'CREATE VIEW {authorizations_view.name} AS {authorizations_view.sql}')
def downgrade():
op.execute(f'DROP VIEW {authorizations_view.name}')