yoshiokatsuneo

create or replace procedure f.assert_table_primary_key(table_name string, key_name string)
begin
  execute immediate '''
    with
      results as (
        select
          (select count(*) from ''' || table_name || ''') as cnt,
          (select count(distinct ''' || key_name || ''') from ''' || table_name || ''') as cnt_distinct,
      )
    select * from results

yoshiokatsuneo

create or replace procedure yoshioka_test1.assert_table_count_equal(table_name1 string, table_name2 string)
begin
  execute immediate '''
    with
      results as (
        select
          (select count(*) from ''' || table_name1 || ''') as expected,
          (select count(*) from ''' || table_name2 || ''') as actual,
      )
    select * from results

yoshiokatsuneo

with
  tbl1 as (
    select 1 as tbl1_id, 'hoge1' as name, '2020-01-01' as created_at
    union all
    select 2 as tbl1_id, 'hoge2' as name, '2020-01-01' as created_at
  ),
  tbl2 as (
    select 1 as tbl1_id, 11 as tbl2_id, 'fuga1-1' as name, '2020-02-01' as created_at
    union all
    select 1 as tbl1_id, 12 as tbl2_id, 'fuga1-2' as name, '2020-02-02' as created_at

yoshiokatsuneo

with
  tbl1 as (
    select 1 as tbl1_id, 'hoge1' as name, '2020-01-01' as created_at
    union all
    select 2 as tbl1_id, 'hoge2' as name, '2020-01-01' as created_at
  ),
  tbl2 as (
    select 1 as tbl1_id, 11 as tbl2_id, 'fuga1-1' as name, '2020-02-01' as created_at
    union all
    select 1 as tbl1_id, 12 as tbl2_id, 'fuga1-2' as name, '2020-02-02' as created_at

yoshiokatsuneo

-- 行の差分を取得
create temp function row_diff(obj1 json, obj2 json)
returns ARRAY<STRUCT<key STRING, value STRING, old_value STRING>>
language js as """
  const diffs = [];
  if (obj1 === null || obj2 === null ){
    return null;
  }
  const excludeFields = ['updated_at'];
  const keys = [...new Set(Object.keys(obj1).concat(Object.keys(obj2)))].filter(key => !excludeFields.includes(key));

yoshiokatsuneo

-- https://github.com/markmcspadden/rails/blob/master/activesupport/lib/active_support/inflections.rb
CREATE TEMP FUNCTION singularize(input_string STRING)
RETURNS STRING
AS (
  CASE
    WHEN REGEXP_CONTAINS(input_string, '(database)s$') THEN REGEXP_REPLACE(input_string, '(database)s$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(quiz)zes$') THEN REGEXP_REPLACE(input_string, '(quiz)zes$', '\\1')
    WHEN REGEXP_CONTAINS(input_string, '(matr)ices$') THEN REGEXP_REPLACE(input_string, '(matr)ices$', '\\1ix')
    WHEN REGEXP_CONTAINS(input_string, '(vert|ind)ices$') THEN REGEXP_REPLACE(input_string, '(vert|ind)ices$', '\\1ex')
    WHEN REGEXP_CONTAINS(input_string, '^(ox)en$') THEN REGEXP_REPLACE(input_string, '^(ox)en$', '\\1')

yoshiokatsuneo

# Your code here!

puts 333334444

yoshiokatsuneo

# Here your code !

puts "hoeeeeeeeeeeeeeee3333333333333333eeeeeeeee2222"

yoshiokatsuneo

// Here your code !

print("111XX333XX33XXXX")

yoshiokatsuneo

line1
line2
line3
line4