-- 调度脚本： 只需要执行当前脚本，即可创建所有必要的兼容性脚本
-- =============================================================================

-- 设置应用名称
set application_name = 'runMe';
set statement_timeout = 60000;

-- 清理测试用例
do $$
declare
    l_exists   bigint;
begin
    for l_exists in select count(*)
                      from pg_tables
                     where schemaname = 'compat_tools'
                       and tablename = 'compat_testing'
                    having count(*) >= 1
    loop
        truncate table compat_tools.compat_testing;
    end loop;
end;
$$ language plpgsql;


-- 1. Oracle 视图
\i Oracle_Views.sql

-- 2. Oracle 管理包
\i Oracle_Packages.sql

-- 3. Oracle 内置函数
\i Oracle_Functions.sql

-- 4. Oracle 内置类型
\i Oracle_Types.sql

-- 5. MySQL 内置函数
\i MySQL_Functions.sql

-- 6. DB5 内置函数
\i DB2_Functions.sql

-- 7. 刷新对象权限（授予public）
\i flush_privileges.sql

-- Show result & Exit
do $RESULT_SUMMARY$
declare
    l_app_name      text;
    l_failed_cnt    bigint;
begin
    set client_min_messages='notice';

    raise notice '';
    raise notice '-- =====================================================================';
    raise notice '-- Compat Object List: ';
    raise notice '-- =====================================================================';
    for l_app_name in select '   |' || pad_char
                          || rpad(coalesce(object_type, ' '), max_object_type, pad_char) || pad_char || '|' || pad_char
                          || rpad(coalesce(object_name, ' '), max_object_name, pad_char) || pad_char || '|' || pad_char
                          || rpad(coalesce(object_version, ' '), max_object_version, pad_char) || pad_char || '|' || pad_char
                          || rpad(coalesce(object_language, ' '), max_object_language, pad_char) || pad_char || '|' || pad_char
                          || rpad(coalesce(operation, ' '), max_operation, pad_char) || pad_char || '|' as result_data
                        from (select greatest(max(length(object_type)), 5) max_object_type
                                   , greatest(max(length(object_name)), 6) max_object_name
                                   , greatest(max(length(case when local_version = script_version then local_version else local_version || ' => ' || script_version end)), 7) max_object_version
                                   , greatest(max(length(case when local_language = script_language then local_language else local_language || ' => ' || script_language end)), 8) max_object_language
                                   , greatest(max(length(operation)), 9) max_operation
                                from temp_result
                             ) l
                        join (select 'type' as object_type
                                   , 'name' as object_name
                                   , 'version' as object_version
                                   , 'language' as object_language
                                   , 'operation' as operation
                                   , ' ' as pad_char
                               union all
                              select '-' as object_type
                                   , '-' as object_name
                                   , '-' as object_version
                                   , '-' as object_language
                                   , '-' as operation
                                   , '-' as pad_char
                               union all
                              select object_type, object_name
                                   , case when local_version is null or local_version = script_version then script_version
                                          else local_version || ' => ' || script_version end as object_version
                                   , case when local_language is null or local_language = script_language then script_language
                                          else local_language || ' => ' || script_language end as object_language
                                   , operation, ' '
                                from temp_result
                             ) r
                          on 1 = 1
    loop
        raise notice '%', l_app_name;
    end loop;

    raise notice '';
    raise notice '-- =====================================================================';
    raise notice '-- Test Summary: ';
    raise notice '-- =====================================================================';
    for l_app_name in select '   | result_type | case_count | start_time          | complete_time       |'
                       union all
                      select '   |-------------|------------|---------------------|---------------------|'
                       union all
                      select '   | '
                          || rpad((case when test_ok then 'PASSED' when not test_ok then 'FAILED' else 'NULL' end)::text, 11)
                          || ' | '
                          || lpad(count(*)::text, 10)
                          || ' | '
                          || to_char(min(test_timestamp), 'YYYY-MM-DD HH24:MI:SS')
                          || ' | '
                          || to_char(max(test_timestamp), 'YYYY-MM-DD HH24:MI:SS')
                          || ' |' as result_data
                        from compat_tools.compat_testing
                       group by case when test_ok then 'PASSED' when not test_ok then 'FAILED' else 'NULL' end
    loop
        raise notice '%', l_app_name;
    end loop;

    raise notice '';
    raise notice '-- =====================================================================';
    raise notice '-- Test Detail (Failed or Null): ';
    raise notice '-- =====================================================================';
    select count(*) into l_failed_cnt
      from compat_tools.compat_testing
     where test_ok is null or (not test_ok);
    if l_failed_cnt = 0
    then
        raise notice '-- <<< ALL SUCCEED >>>';
    else
        for l_app_name in select '   | test_expression                              | result          | expect          | complete_time       |'
                           union all
                          select '   |----------------------------------------------|-----------------|-----------------|---------------------|'
                           union all
                          select '   | '
                              || case when length(test_expr) > 44 then substr(test_expr, 1, 40)||' ...' else rpad(test_expr, 44) end
                              || ' | '
                              || lpad(coalesce(test_result, ' '), 15)
                              || ' | '
                              || rpad(coalesce(expect_result, ' '), 15)
                              || ' | '
                              || to_char(test_timestamp, 'YYYY-MM-DD HH24:MI:SS')
                              || ' |' as result_data
                            from compat_tools.compat_testing
                           where test_ok is null or (not test_ok)
        loop
            raise notice '%', l_app_name;
        end loop;
    end if;
end;
$RESULT_SUMMARY$ language plpgsql;

\q