Pattern Generators for Dummies – Chapter 2

The first chapter covered the fundamental concepts of metadata-driven code generation based on patterns, including the distinction between a Pattern and a pattern template, which is dependent on the implementation platform and other specific factors. In this next section, we will provide examples of how the same metadata can be utilized for the same patterns when implementing them on a PostgreSQL database instead of Oracle. Despite using different SQL dialects, both implementations are fundamentally similar.

In this basic example, it is clear that the same metadata can be applied when transferring from one platform to another in instances where the code cannot be easily ported without substantial modifications.

In our first situation, we consider the generator is placed on the target (implementation) platform, i.e. PostgreSQL. We create a similar PLPgSQL function to work with templates as in Oracle and prepare it to work with the same metadata.

create or replace function treat_template(
   p_metadata varchar  -- metadata of the generated code
  ,p_template varchar  -- template of the generation pattern 
) returns varchar as
$$
declare -- a generic function for using templates based on json parametrization
...

https://github.com/bobjankovsky/metaswamp/blob/main/postgres/shards/treat_template.sql

Examples

Let’s create a similar structure as in the previous chapter in Postgresql.

create table zzz_source_data_sample(
  the_key_column_1  varchar( 100)
 ,the_key_column_2  varchar(  50)    
 ,some_value_col_1  varchar(1000)
 ,some_value_col_2  numeric(27,4)
 ,some_value_col_3  date
);

create table zzz_target_data_sample(
  the_key_column_1  varchar( 100)
 ,the_key_column_2  varchar(  50)    
 ,some_value_col_1  varchar(1000)
 ,some_value_col_2  numeric(27,4)
 ,some_value_col_3  date
 ,some_other_col_1  varchar(200)
 ,constraint zzz_target_data_sample_pk primary key (the_key_column_1,the_key_column_2)
);   

The transformation will be represented by the same metadata as in Chapter 1:

{
    "SourceName":"ZZZ_SOURCE_DATA_SAMPLE",
    "TargetName":"ZZZ_TARGET_DATA_SAMPLE",
    "cols":[
        {"ColTargetName":"THE_KEY_COLUMN_1","ColKeyFlag":"Y","ColSourceExpr":"THE_KEY_COLUMN_1"},
        {"ColTargetName":"THE_KEY_COLUMN_2","ColKeyFlag":"Y","ColSourceExpr":"THE_KEY_COLUMN_2"},
        {"ColTargetName":"SOME_VALUE_COL_1","ColKeyFlag":"N","ColSourceExpr":"SOME_VALUE_COL_1"},
        {"ColTargetName":"SOME_VALUE_COL_2","ColKeyFlag":"N","ColSourceExpr":"SOME_VALUE_COL_2"},
        {"ColTargetName":"SOME_VALUE_COL_3","ColKeyFlag":"N","ColSourceExpr":"SOME_VALUE_COL_3"}
    ]
}

Insert pattern

The simplest way of the data population.

The pattern template for INSERT in PostgreSQL is very similar to that in Oracle:

insert into {TargetName} (
{cols:[\n  ,|{ColTargetName}]}
)
select
{cols:[\n    ,|{ColSourceExpr} as {ColTargetName}]}
from {SourceName}

As well as the generated sample code:

insert into ZZZ_TARGET_DATA_SAMPLE (
   THE_KEY_COLUMN_1
  ,THE_KEY_COLUMN_2
  ,SOME_VALUE_COL_1
  ,SOME_VALUE_COL_2
  ,SOME_VALUE_COL_3
)
select
     THE_KEY_COLUMN_1 as THE_KEY_COLUMN_1
    ,THE_KEY_COLUMN_2 as THE_KEY_COLUMN_2
    ,SOME_VALUE_COL_1 as SOME_VALUE_COL_1
    ,SOME_VALUE_COL_2 as SOME_VALUE_COL_2
    ,SOME_VALUE_COL_3 as SOME_VALUE_COL_3
from ZZZ_SOURCE_DATA_SAMPLE

MERGE pattern

The pattern template for MERGE in PostgreSQL significantly differs from the one in Oracle:

insert into {TargetName} as trg$(
{cols:[\n  ,|{ColTargetName}]}
)
select
{cols:[\n    ,|{ColSourceExpr} as {ColTargetName}]}
from {SourceName}
on conflict ({cols:[,|{ColTargetName}?ColKeyFlag]})
do update set 
{cols:[\n  ,|{ColTargetName} = EXCLUDED.{ColTargetName}?!ColKeyFlag]}

As well as the generated sample code:

 insert into ZZZ_TARGET_DATA_SAMPLE as trg$(
    THE_KEY_COLUMN_1
   ,THE_KEY_COLUMN_2
   ,SOME_VALUE_COL_1
   ,SOME_VALUE_COL_2
   ,SOME_VALUE_COL_3
 )
 select
      THE_KEY_COLUMN_1 as THE_KEY_COLUMN_1
     ,THE_KEY_COLUMN_2 as THE_KEY_COLUMN_2
     ,SOME_VALUE_COL_1 as SOME_VALUE_COL_1
     ,SOME_VALUE_COL_2 as SOME_VALUE_COL_2
     ,SOME_VALUE_COL_3 as SOME_VALUE_COL_3
 from ZZZ_SOURCE_DATA_SAMPLE
 on conflict (THE_KEY_COLUMN_1,THE_KEY_COLUMN_2)
 do update set
    SOME_VALUE_COL_1 = EXCLUDED.SOME_VALUE_COL_1
   ,SOME_VALUE_COL_2 = EXCLUDED.SOME_VALUE_COL_2
   ,SOME_VALUE_COL_3 = EXCLUDED.SOME_VALUE_COL_3

DIFF MERGE pattern

Similar to the previous one, but eliminating “empty” updates.

The pattern template for DIFF MERGE in PostgreSQL also significantly differs from the one in Oracle:

insert into {TargetName} as trg$(
{cols:[\n  ,|{ColTargetName}]}
)
select
{cols:[\n    ,|{ColSourceExpr} as {ColTargetName}]}
from {SourceName}
on conflict ({cols:[,|{ColTargetName}?ColKeyFlag]})
do update set 
{cols:[\n  ,|{ColTargetName} = EXCLUDED.{ColTargetName}?!ColKeyFlag]}
where 
{cols:[\n  or |trg$.{ColTargetName} is distinct from EXCLUDED.{ColTargetName}?!ColKeyFlag]}

As well as the generated sample code:

insert into ZZZ_TARGET_DATA_SAMPLE as trg$(
   THE_KEY_COLUMN_1
  ,THE_KEY_COLUMN_2
  ,SOME_VALUE_COL_1
  ,SOME_VALUE_COL_2
  ,SOME_VALUE_COL_3
)
select
     THE_KEY_COLUMN_1 as THE_KEY_COLUMN_1
    ,THE_KEY_COLUMN_2 as THE_KEY_COLUMN_2
    ,SOME_VALUE_COL_1 as SOME_VALUE_COL_1
    ,SOME_VALUE_COL_2 as SOME_VALUE_COL_2
    ,SOME_VALUE_COL_3 as SOME_VALUE_COL_3
from ZZZ_SOURCE_DATA_SAMPLE
on conflict (THE_KEY_COLUMN_1,THE_KEY_COLUMN_2)
do update set
   SOME_VALUE_COL_1 = EXCLUDED.SOME_VALUE_COL_1
  ,SOME_VALUE_COL_2 = EXCLUDED.SOME_VALUE_COL_2
  ,SOME_VALUE_COL_3 = EXCLUDED.SOME_VALUE_COL_3
where
     trg$.SOME_VALUE_COL_1 is distinct from EXCLUDED.SOME_VALUE_COL_1
  or trg$.SOME_VALUE_COL_2 is distinct from EXCLUDED.SOME_VALUE_COL_2
  or trg$.SOME_VALUE_COL_3 is distinct from EXCLUDED.SOME_VALUE_COL_3

In conclusion, metadata-driven pattern generators have the potential to facilitate the easy porting of code from one platform to another while minimizing vendor lock-in. By using a metadata-driven approach, code can be generated based on patterns that are abstracted from specific implementation details, thereby increasing the likelihood of compatibility across different platforms.

This approach can reduce the burden of redeveloping code for different databases or software systems, allowing developers to focus on other aspects of the project. Additionally, using pattern generators can increase code consistency and maintainability by promoting the reuse of proven design patterns and reducing the potential for human error.

Common generator implementation

Differently from the first diagram, where generators have been placed on the implementation platforms, in hybrid implementation, there could be reasonable to use a common generator implemented for example in Python.

import json
import re
def treat_template(p_metadata, p_template):
...

https://github.com/bobjankovsky/metaswamp/blob/main/py/shards/treat_template.py

Conclusion

Modularity, or latent modularity, offers numerous advantages when developing software systems. By starting with a Metadata management system, a pattern generator, and the implementation on the same platform, developers can take advantage of the logical independence of these components to separate them later without causing unnecessary pain.

This approach offers several benefits, such as the ability to reuse the metadata and patterns across different platforms, reducing development time and costs. It also promotes flexibility by allowing developers to easily modify the components independently without affecting others.

Furthermore, latent modularity enables developers to easily integrate new features and functionality into the system without disrupting the existing codebase. It also enhances maintainability by simplifying the process of debugging and troubleshooting problems within the system.

Others in the series