[GitHub] [airflow] cansjt edited a comment on issue #17374: The new weberser does not seem to care about AIRFLOW__CORE__SQL_ALCHEMY_SCHEMA

[GitBox <gi...@apache.org>]

cansjt edited a comment on issue #17374:
URL: https://github.com/apache/airflow/issues/17374#issuecomment-891177075


   FYI here is the SQL script I used to move things around:
   ```sql
   TRUNCATE TABLE airflow.ab_permission ;
   TRUNCATE TABLE airflow.ab_view_menu ;
   TRUNCATE TABLE airflow.ab_user_role ;
   TRUNCATE TABLE airflow.ab_role ;
   TRUNCATE TABLE airflow.ab_user ;
   TRUNCATE TABLE airflow.ab_register_user ;
   TRUNCATE TABLE airflow.ab_permission_view_role ;
   TRUNCATE TABLE airflow.ab_permission_view ;
   INSERT INTO airflow.ab_permission (name) SELECT name FROM public.ab_permission ;
   INSERT INTO airflow.ab_view_menu (name) SELECT name FROM public.ab_view_menu ;
   INSERT INTO airflow.ab_role (name) SELECT name FROM public.ab_role ;
   INSERT INTO airflow.ab_user (first_name,
                                last_name,
                                username,
                                password,
                                active,
                                email,
                                last_login,
                                login_count,
                                fail_login_count,
                                created_on,
                                changed_on,
                                created_by_fk,
                                changed_by_fk)
       SELECT
           first_name,
           last_name,
           username,
           password,
           active,
           email,
           last_login,
           login_count,
           fail_login_count,
           created_on,
           changed_on,
           created_by_fk,
           changed_by_fk
       FROM public.ab_user ;
   INSERT INTO airflow.ab_register_user (first_name,
                                         last_name,
                                         username,
                                         password,
                                         email,
                                         registration_date,
                                         registration_hash)
       SELECT
           first_name,
           last_name,
           username,
           password,
           email,
           registration_date,
           registration_hash
       FROM public.ab_register_user ;
   INSERT INTO airflow.ab_user_role (user_id, role_id)
       SELECT
           au.id AS user_id,
           ar.id AS role_id
       FROM
           public.ab_user_role  AS pur
           JOIN public.ab_user  AS pu  ON pur.user_id = pu.id
           JOIN public.ab_role  AS pr  ON pur.role_id = pr.id
           JOIN airflow.ab_user AS au  ON pu.username = au.username
           JOIN airflow.ab_role AS ar  ON pr.name     = ar.name
       ;
   INSERT INTO airflow.ab_permission_view (permission_id, view_menu_id)
       SELECT
           ap.id  AS permission_id,
           avm.id AS view_menu_id
       FROM
           public.ab_permission_view  AS ppv
           JOIN public.ab_permission  AS pp  ON ppv.permission_id = pp.id
           JOIN public.ab_view_menu   AS pvm ON ppv.view_menu_id  = pvm.id
           JOIN airflow.ab_view_menu  AS avm ON pvm.name          = avm.name
           JOIN airflow.ab_permission AS ap  ON ap.name           = pp.name
       ;
   INSERT INTO airflow.ab_permission_view_role (permission_view_id, role_id)
       SELECT
           pv_map.schema_permission_view_id AS permission_view_id,
           ar.id  AS role_id
       FROM
           public.ab_permission_view_role AS ppvr
           JOIN (SELECT
                     ppv.id AS public_permission_view_id,
                     apv.id AS schema_permission_view_id
                 FROM
                     public.ab_permission_view  AS ppv
                     JOIN public.ab_permission  AS pp  ON ppv.permission_id = pp.id
                     JOIN public.ab_view_menu   AS pvm ON ppv.view_menu_id  = pvm.id
                     JOIN airflow.ab_view_menu  AS avm ON pvm.name          = avm.name
                     JOIN airflow.ab_permission AS ap  ON ap.name           = pp.name
                     JOIN airflow.ab_permission_view AS apv
                         ON apv.view_menu_id = avm.id AND apv.permission_id = ap.id
            ) AS pv_map ON ppvr.permission_view_id = pv_map.public_permission_view_id
           JOIN public.ab_role                 AS pr   ON ppvr.role_id            = pr.id
           JOIN airflow.ab_role                AS ar   ON pr.name                 = ar.name
       ;
   ```
   Ensures, in the most portable way I could think of, that there will be no issues with ID (or sequences or whatever).
   Controlled things went okay with these ones. They should not return any row, if everything went fine:
   ```sql
   SELECT
       ar.name,
       avm.name,
       ap.name
   FROM
       airflow.ab_permission_view_role AS apvr
       JOIN airflow.ab_permission_view AS apv ON apv.id = apvr.permission_view_id
       JOIN airflow.ab_view_menu AS avm ON avm.id = apv.view_menu_id
       JOIN airflow.ab_role AS ar ON ar.id = apvr.role_id
       JOIN airflow.ab_permission AS ap ON ap.id = apv.permission_id
   EXCEPT
       SELECT
           pr.name,
           pvm.name,
           pp.name
       FROM
           public.ab_permission_view_role AS ppvr
           JOIN public.ab_permission_view AS ppv ON ppv.id = ppvr.permission_view_id
           JOIN public.ab_view_menu AS pvm ON pvm.id = ppv.view_menu_id
           JOIN public.ab_role AS pr ON pr.id = ppvr.role_id
           JOIN public.ab_permission AS pp ON pp.id = ppv.permission_id
   ;
   SELECT
       au.username,
       ar.name
   FROM
       airflow.ab_user_role AS aur
       JOIN airflow.ab_user AS au ON aur.user_id = au.id
       JOIN airflow.ab_role AS ar ON ar.id = aur.role_id
   EXCEPT
       SELECT
           pu.username,
           pr.name
       FROM
           public.ab_user_role AS pur
           JOIN public.ab_user AS pu ON pur.user_id = pu.id
           JOIN public.ab_role AS pr ON pr.id = pur.role_id
   ;
   ```
   And to clean up:
   ```sql
   DROP TABLE public.ab_permission ;
   DROP TABLE public.ab_view_menu ;
   DROP TABLE public.ab_user_role ;
   DROP TABLE public.ab_role ;
   DROP TABLE public.ab_user ;
   DROP TABLE public.ab_register_user ;
   DROP TABLE public.ab_permission_view_role ;
   DROP TABLE public.ab_permission_view ;
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org