@Grapes([
    @Grab(group='postgresql', module='postgresql', version='9.0-801.jdbc4'),
    @Grab(group='com.ibm.icu', module='icu4j', version='56.1'),
    @GrabConfig(systemClassLoader=true, initContextClassLoader=true)
])
import com.ibm.icu.text.Transliterator

//------------- script -----------------//
println "-----start-----" + new Date()

def lineSeparator = System.properties['line.separator']
def resultFile = new File("C:\\dev\\groovy\\updateLoginIdResult.sql")

def db = groovy.sql.Sql.newInstance(
    "jdbc:postgresql://localhost:5432/databaseName",
    "postgres",
    "postgres1",
    "org.postgresql.Driver"
)
def query =
    """
       select p.person_id as id, text.item_text as kana
       from person p
           inner join text
               on text.person_id = p.person_id and text.item_id = 103
       order by p.person_id;
    """

resultFile.text = ""
db.eachRow(query){
    row ->
    latin = getLatin(row.kana)
    resultFile.append(getQuery(row.id, latin) + lineSeparator)
}

println "-----finished-----" + new Date()

//--------------- functions -----------------//
def String getQuery(userId, login){
    def query = "update user set login_id = \'%s\', update_timestamp = current_timestamp, update_function = 'FunctionName' where user_id = %s;"
    def f = new Formatter()

    f.format(query, login, userId)
}

def String getLatin(katakana){
    tmp = Transliterator.getInstance("Katakana-Latin").transliterate(katakana)
    //TODO ｷﾀﾉ ｼﾝﾉｽｹ->kitano shin'nosukeになるため'を抜いているが、icu4jの設定等で解決できるならそっちでやる
    tmp.replace(' ', '.').replace('\'', '')
}