テンプレートエンジンののコードをコードとして管理したくない
-
テンプレート化されたコードを読み解くコストは高い
- コードは書くよりも読まれる時間の方が長い
-
テンプレートのコードは lint/formatter/コード補完などの静的解析ツールの相性が悪い
declare generated_sql string; | |
call `sandbox.template_engine`( | |
(null, "sandbox", "zmock_sample") -- sandbox.zmock_sampleを基準に生成する | |
, ("__mock__data", "(select 2 as a)") -- __mock__dataの呼び出しを (select 2 as a) に置換 | |
, generated_sql | |
); | |
------------------------------- | |
-- 生成されるSQL | |
------------------------------- | |
-- call `sandbox.template_engine`((null, "sandbox", "zmock_sample"), ("__mock__data", "(select 2 as a)") , generated_sql); | |
-- ... DO NOT EDIT | |
with __mock__data as ( | |
select 1 as a | |
) | |
select a from (select 2 as a) |
-- sandbox.zmock_sample | |
with __mock__data as ( | |
select 1 as a | |
) | |
select a from `__mock_data` | |
; |
create or replace procedure `sandbox.templating_view` ( | |
target struct<table_catalog string, table_schema string, table_name string> | |
, placeholders array<struct<identifier string, new_identifier string>> | |
, out generated_sql string | |
) | |
options (description="""Generate New SQL based on `target`'s view_definition. | |
Arguments | |
=== | |
- target: target view | |
- placeholders: | |
- generated_sql: | |
Examples | |
=== | |
declare target default struct(string(null) as table_catalog, "sandbox" as table_schema, "zmock_sample" as table_name); | |
declare placeholders default [struct( | |
"__mock__data" as identifier | |
, "(select 2)" as new_identifier | |
)]; | |
declare generated_sql string; | |
call `sandbox.template_engine`(target, placeholders, generated_sql); | |
""" | |
) | |
begin | |
execute immediate format(""" | |
with recursive template_apply as ( | |
select 0 as stage, view_definition as sql, placeholders from get_mockable_sql | |
union all | |
select stage + 1 as stage | |
, regexp_replace( | |
sql | |
, format(r'`%%s`', json_value(p, '$.identifier')) | |
, json_value(p, '$.new_identifier') | |
) | |
, placeholders | |
from template_apply | |
left join unnest([struct(placeholders[safe_offset(stage)] as p)]) | |
where stage < array_length(placeholders) | |
) | |
, input_view_information_schema as ( | |
select | |
view_definition | |
, @placeholders as placeholders | |
from | |
`%s.INFORMATION_SCHEMA.VIEWS` | |
where | |
table_name = "%s" | |
) | |
, get_mockable_sql as ( | |
select * replace( | |
array(select to_json_string(struct(v.identifier, v.new_identifier)) from data.placeholders as v) as placeholders | |
) from `input_view_information_schema` data | |
) | |
select as struct | |
trim(sql) as sql | |
from template_apply | |
where stage = array_length(placeholders) | |
""" | |
, target.table_schema | |
, target.table_name | |
) | |
into generated_sql | |
using placeholders as placeholders | |
; | |
end |