 // importing libraries
//import org.apache.commons.io.IOUtils
import java.nio.charset.StandardCharsets
//import org.apache.nifi.controller.ControllerService
import groovy.sql.Sql
import groovy.json.JsonOutput
import groovy.json.JsonSlurper

// declaring variables
//def flowFile = session.get()
//if (!flowFile) return

def metadata_to_mt_ini = ''.toString()

def db_source_name = 'salary-permission'.toString()
def db_source_schema = 'PUBLIC'.toString()
def db_source_table = 'ACCRUAL_TYPE_BLACKLIST_FOR_USER'.toString()
def db_source_columns_str = 'cast("id" as varchar) as "id",cast("user_id" as varchar) as "user_id",cast("client_id" as varchar) as "client_id",cast("contract_id" as varchar) as "contract_id",cast("empl_code" as varchar) as "empl_code",cast("accrual_type_blacklist" as varchar) as "accrual_type_blacklist",cast("created_at" as varchar) as "created_at",cast("updated_at" as varchar) as "updated_at"'.toString().toLowerCase()  //мт  cast(id as varchar) as id,

def db_target_name = 'rbua-data-dev-raw-salary-permission'.toString()
def db_target_table = 'ods/public.accrual_type_blacklist_for_user/'.toString()

def list_source_columns_mt_str = 'cast("id" as varchar) as "id"‡cast("user_id" as varchar) as "user_id"‡cast("client_id" as varchar) as "client_id"‡cast("contract_id" as varchar) as "contract_id"‡cast("empl_code" as varchar) as "empl_code"‡cast("accrual_type_blacklist" as varchar) as "accrual_type_blacklist"‡cast("created_at" as varchar) as "created_at"‡cast("updated_at" as varchar) as "updated_at"'.toString().toLowerCase()//мт cast(id as varchar) as id‡
def list_target_columns_mt_str = '"id","user_id","client_id","contract_id","empl_code","accrual_type_blacklist","created_at","updated_at"'.toString().toLowerCase()  // mt id,

def list_all_target_columns_mt_str = '"id","user_id","client_id","contract_id","empl_code","accrual_type_blacklist","created_at","updated_at"'.toString().toLowerCase() //! mt id,
def list_source_columns_metadata = '[{"name": "ID", "type": "integer", "scale": 0, "length": 0, "nullable": "N", "precision": 32, "primary_key": "N"},{"name": "user_id", "type": "character varying", "scale": 0, "length": 255, "nullable": "N", "precision": 0, "primary_key": "N"},{"name": "client_id", "type": "bigint", "scale": 0, "length": 0, "nullable": "N", "precision": 64, "primary_key": "N"},{"name": "contract_id", "type": "bigint", "scale": 0, "length": 0, "nullable": "N", "precision": 64, "primary_key": "N"},{"name": "empl_code", "type": "character varying", "scale": 0, "length": 5, "nullable": "N", "precision": 0, "primary_key": "N"},{"name": "accrual_type_blacklist", "type": "ARRAY", "scale": 0, "length": 0, "nullable": "N", "precision": 0, "primary_key": "N"},{"name": "created_at", "type": "timestamp without time zone", "scale": 0, "length": 0, "nullable": "N", "precision": 0, "primary_key": "N"},{"name": "updated_at", "type": "timestamp without time zone", "scale": 0, "length": 0, "nullable": "N", "precision": 0, "primary_key": "N"}]'.toString()//.toLowerCase() //system table columns


def insert_query_mt="begin;"
def deactivate_query_mt=""
def column_name=""
def new_target_column=""
def list_target_columns_mt=[]
def list_all_target_columns_mt=[]
def db_source_columns=[]
def list_new_source_columns=[]
def list_source_columns_mt0=[]
def list_target_columns_mt0=[]
def list_source_columns_mt_with_exp=[]
///get data from attributes/////
//list_source_columns_mt=new ArrayList<String>(Arrays.asList(list_source_columns_mt_str.split(",")))
if(list_source_columns_mt_str.toString().trim()!="null" && list_source_columns_mt_str.toString().trim()!="")
{
   db_source_columns=new ArrayList<String>(Arrays.asList(list_source_columns_mt_str.split("‡")))
println 'db_source_columns (те що записано в  МТ):'
println db_source_columns // те що записано в  МТ
}
if(list_target_columns_mt_str.toString().trim()!="null" && list_target_columns_mt_str.toString().trim()!="")
{
   list_target_columns_mt=new ArrayList<String>(Arrays.asList(list_target_columns_mt_str.split(",")))
println '\nlist_target_columns_mt:'
println list_target_columns_mt
}
if(list_all_target_columns_mt_str.toString().trim()!="null" && list_all_target_columns_mt_str.toString().trim()!="")
{
   list_all_target_columns_mt = new ArrayList<String>(Arrays.asList(list_all_target_columns_mt_str.split(",")))
}
if (db_source_columns && !db_source_columns.empty && metadata_to_mt_ini!="true")
{
db_source_columns.eachWithIndex 
{ nit, n -> 
  list_source_columns_mt_with_exp.add(db_source_columns[n].substring(0,db_source_columns[n].lastIndexOf(" as ")).toString().trim())
  list_target_columns_mt0.add(db_source_columns[n].substring(db_source_columns[n].lastIndexOf(" as ")+4).toString().trim())
  if (db_source_columns[n].trim().startsWith("to_char(")){list_source_columns_mt0.add(db_source_columns[n].substring(8,db_source_columns[n].lastIndexOf(")")).toString().trim().replace(",'yyyy-mm-dd hh24:mi:ss.us'","").replace("\"",""))}
  if (db_source_columns[n].trim().startsWith("cast(")){list_source_columns_mt0.add(db_source_columns[n].substring(5,db_source_columns[n].substring(0,db_source_columns[n].lastIndexOf(" as ")).toString().trim().lastIndexOf(" as ")).toString().trim().replace("\"",""))}
  if (db_source_columns[n].trim().startsWith("xmlserialize(")){list_source_columns_mt0.add(db_source_columns[n].substring(db_source_columns[n].indexOf("document")+8,db_source_columns[n].substring(0,db_source_columns[n].lastIndexOf(" as ")).toString().trim().lastIndexOf(" as ")).toString().trim())}
  if (db_source_columns[n].substring(0,db_source_columns[n].lastIndexOf(" as ")).toString().trim().length()<=list_target_columns_mt0[n].trim().length()){list_source_columns_mt0.add(db_source_columns[n].substring(0,db_source_columns[n].lastIndexOf(" as ")).toString().trim())}
  if (!db_source_columns[n].trim().startsWith("to_char(")&&!db_source_columns[n].trim().startsWith("cast(")&&!db_source_columns[n].trim().startsWith("xmlserialize(")&&(db_source_columns[n].substring(0,db_source_columns[n].lastIndexOf(" as ")).toString().trim().length()>list_target_columns_mt0[n].trim().length())) 
      //{list_source_columns_mt0.add(db_source_columns[n].substring(1,db_source_columns[n].lastIndexOf(" as ")).toString().trim())}
      {list_source_columns_mt0.add(db_source_columns[n])}
}
println '\nПарсимо => list_source_columns_mt_with_exp:'
println list_source_columns_mt_with_exp
println '\nПарсимо => list_target_columns_mt0'
println list_target_columns_mt0
println '\n'

}

//flowFile.putAttribute('list_source_columns_mt0',list_source_columns_mt0.toString())

def parser = new JsonSlurper()
def json = parser.parseText(list_source_columns_metadata)
def column_cnt = json.General.size()

println 'Дані з БД list_source_columns_metadata'
println json

//add only new columns from source//
json.eachWithIndex { mit, m -> 
list_new_source_columns.add(json.name.get(m).toString())

if (!(list_source_columns_mt0.toString().toLowerCase()).contains(json.name.get(m).toString().toLowerCase()) ) //  Add 93 93
{
column_name=json.name.get(m).toString()




def data_type=json.type.get(m).toString().toLowerCase()

println '\nnew columns:\n'+column_name



//replace special symbols to underscore AND replace first number character//
new_target_column=replace_spec_symbol(column_name.toString().toLowerCase(),list_all_target_columns_mt)
////

//replace first number character//
//new_target_column=new_target_column.substring(0,1).replaceAll("[0-9]+","_")+new_target_column.substring(1)
////

if(column_name.replaceAll("(^[0-9]{1}|[^a-zA-Z0-9])+", "_")==column_name)
{new_target_column='"'+new_target_column+'"'}

def source_column_name='cast("'+column_name+'" as varchar)'
if(data_type=="raw" || data_type=="clob" || data_type=="blob" || data_type=="bytea"){source_column_name=column_name}
if(data_type=="xmltype"){source_column_name="xmlserialize(document "+column_name+" as clob)"}
if(data_type=="date"){source_column_name="to_char("+'"'+column_name+'"'+",''YYYY-MM-DD HH24:MI:SS.US'')"}
//except blob,clob,xmltype

//println("Activation")
//println("source_column_name2: "+source_column_name)
//println("new_target_column: "+new_target_column)

if(new_target_column!="null" && source_column_name!="null" && data_type!="clob" && data_type!="blob" && data_type!="xmltype" && data_type!="bytea")
    {
      insert_query_mt=insert_query_mt+"""select * from nifi.add_column_map(source_system=>'$db_source_name',source_catalog=>null,source_schema=>'$db_source_schema',source_table=>'$db_source_table',
                                  source_column=>'$source_column_name',target_system=>'$db_target_name',target_catalog=>null,target_schema=>null,target_table=>'$db_target_table',
                                  target_column=>'"""+new_target_column+"""',source_data_type=>'"""+data_type+"""',target_data_type=>'string',status=>'A');"""
    
    }
//add new target columns to all target columns from mt//                                   
    if(!list_all_target_columns_mt.contains(new_target_column))
      {list_all_target_columns_mt.add(new_target_column)}
}
 // else{insert_query_mt=""}
}

//println '\nTarger:'
//println list_source_columns_mt0
//println new_target_column
//println list_new_source_columns

//deactivate deleted columns//
list_source_columns_mt0.eachWithIndex { kit, k -> 
def old_source_column_with_exp=list_source_columns_mt_with_exp[k].toString()
def old_target_column=list_target_columns_mt0[k].toString()
def exists=0

//println k+' '+old_source_column_with_exp
//println k+' '+ old_target_column
//println list_new_source_columns

list_new_source_columns.eachWithIndex { jit, j -> 
if (list_source_columns_mt0[k].toString().toLowerCase().trim()==list_new_source_columns[j].toString().toLowerCase().trim())
{
   exists=exists+1         
}   
  else {exists=exists}
}
//except custom condition
if(list_source_columns_mt0[k].toString().trim().contains(" ")){exists=-1}


if(exists==0)

{
//println("DEACTIVATIN")
//println(old_source_column_with_exp)
//println(old_target_column)
  deactivate_query_mt=deactivate_query_mt+"""select * from nifi.add_column_map(source_system=>'$db_source_name',source_catalog=>null,source_schema=>'$db_source_schema',source_table=>'$db_source_table',
                     source_column=>'"""+old_source_column_with_exp+"""',target_system=>'$db_target_name',target_catalog=>null,target_schema=>null,target_table=>'$db_target_table',
                     target_column=>'"""+old_target_column+"""',status=>'D');"""
}  
}                   

//replace special symbols to underscore AND replace first number character//
def replace_spec_symbol(String source_column_name,List<String> list_all_target_columns_mt1)
{
def c_name=source_column_name.replaceAll("(^[0-9]{1}|[^a-zA-Z0-9\r\n])+", "_")

if(!list_all_target_columns_mt1.empty)
{ 
  if(list_all_target_columns_mt1.contains(c_name))
  {     
   while(list_all_target_columns_mt1.contains(c_name)&& c_name.length()<=255)
      {    
        c_name=c_name+"_"        
      }
   }     
      return c_name
}
else return c_name
}



//return script for mt//
def update_query_mt=insert_query_mt+deactivate_query_mt+"commit;"
//flowFile.putAttribute('update_query_mt',update_query_mt) //println(update_query_mt)
////
println '\n'
println update_query_mt
//REL_SUCCESS << flowFile

created 7 months ago by Don Ruslan

Groovy online compiler

Write, Run & Share Groovy code online using OneCompiler's Groovy online compiler for free. It's one of the robust, feature-rich online compilers for Groovy language, running the latest Groovy version 2.6. Getting started with the OneCompiler's Groovy editor is easy and fast. The editor shows sample boilerplate code when you choose language as Groovy and start coding.

Read inputs from stdin

OneCompiler's Groovy online editor supports stdin and users can give inputs to programs using the STDIN textbox under the I/O tab. Following is a sample Groovy program which takes name as input and prints hello message with your name.

def name = System.in.newReader().readLine()
println "Hello " + name

About Groovy

Groovy is an object-oriented programming language based on java. Apache Groovy is a dynamic and agile language which is similar to Python, Ruby, Smalltalk etc.

Key Features

It's not a replacement for java but it's an enhancer to Java with extra features like DSL support, dynamic typing, closures etc.
Accepts Java code as it extends JDK
Greater flexibility
Concise and much simpler compared to Java
Can be used as both programming language and scripting language.

Syntax help

Data Types

Data type	Description	Range
String	To represent text literals	NA
char	To represent single character literal	NA
int	To represent whole numbers	-2,147,483,648 to 2,147,483,647
short	To represent short numbers	-32,768 to 32,767
long	To represent long numbers	-9,223,372,036,854,775,808 to +9,223,372,036,854,775,807
double	To represent 64 bit floating point numbers	4.94065645841246544e-324d to 1.79769313486231570e+308d
float	To represent 32 bit floating point numbers	1.40129846432481707e-45 to 3.40282346638528860e+38
byte	To represent byte value	-128 to 127
boolean	To represent boolean values either true or false	True or False

Variables

You can define variables in two ways

Syntax:

data-type variable-name;

[or]

def variable-name;

Loops

0.upto(n) {println "$it"}

n.times{println "$it"}

where n is the number of loops and 0 specifies the starting index

Decision-Making

1. If / Nested-If / If-Else:

When ever you want to perform a set of operations based on a condition or set of conditions, then If / Nested-If / If-Else is used.

if(conditional-expression) {
  // code
} else {
  // code
}

2. Switch:

Switch is an alternative to If-Else-If ladder and to select one among many blocks of code.

switch(conditional-expression) {    
case value1:    
 // code    
 break;  // optional  
case value2:    
 // code    
 break;  // optional  
...    
    
default:     
 //code to be executed when all the above cases are not matched;    
}

List

List allows you to store ordered collection of data values.

Example:

def mylist = [1,2,3,4,5];

List Methods	Description
size()	To find size of elements
sort()	To sort the elements
add()	To append new value at the end
contains()	Returns true if this List contains requested value.
get()	Returns the element of the list at the definite position
pop()	To remove the last item from the List
isEmpty()	Returns true if List contains no elements
minus()	This allows you to exclude few specified elements from the elements of the original
plus()	This allows you to add few specified elements to the elements of the original
remove()	To remove the element present at the specific position
reverse()	To reverse the elements of the original List and creates new list