テンプレートエンジンののコードをコードとして管理したくない
-
テンプレート化されたコードを読み解くコストは高い
- コードは書くよりも読まれる時間の方が長い
-
テンプレートのコードは lint/formatter/コード補完などの静的解析ツールの相性が悪い
Last active
November 6, 2022 12:18
-
-
Save takegue/c516297b7f525a09286abb2f64fcf612 to your computer and use it in GitHub Desktop.
SQLのためのテンプレートエンジン再考
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- sandbox.zmock_sample | |
| with __mock__data as ( | |
| select 1 as a | |
| ) | |
| select a from `__mock_data` | |
| ; |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment