JSON -> DDL作成ツール (create table)

MySQL

(8.0.32)

-- テーブル定義のJSONファイル

{
"schema_name": "test",
"table_name": "tab1",
"table_desc": "テストテーブル1",
"cols": [
{ "col_name": "col1", "col_type": "bigint", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "numeric(30,2)", "not_null": "true", "col_desc": "カラム2" },
{ "col_name": "col3", "col_type": "datetime", "not_null": "false", "col_desc": "カラム3" },
{ "col_name": "col4", "col_type": "varchar(100)", "not_null": "false", "col_desc": "カラム4" },
{ "col_name": "col5", "col_type": "varchar(10)", "not_null": "false", "col_desc": "カラム5" }
],
"partition": {
"partition_type": "range columns",
"partition_col": "col3",
"partition_defs" : [
{ "partition_name": "p1", "partition_limit": "'2022-02-01'" },
{ "partition_name": "p2", "partition_limit": "'2022-03-01'" },
{ "partition_name": "p3", "partition_limit": "'2022-04-01'" },
{ "partition_name": "pmax", "partition_limit": "maxvalue" }
]
},
"primary": { "constraint_name": "tab1pk", "index_cols": ["col3","col1"] },
"indexes": [
{ "index_name": "ind1_1", "index_cols": ["col3","col2"] ,"unique": true},
{ "index_name": "ind1_2", "index_cols": ["col5"] ,"unique": false}
]
}

vim tab2.json

{
"schema_name": "test",
"table_name": "tab2",
"table_desc": "テストテーブル2",
"cols": [
{ "col_name": "col1", "col_type": "bigint", "not_null": "true", "col_desc": "カラム1" }
],
"partition": {},
"primary": {},
"indexes": []
}

vim tab3.json

{
"schema_name": "test",
"table_name": "tab3",
"table_desc": "テストテーブル3",
"cols": [
{ "col_name": "col1", "col_type": "datetime", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "numeric(30,2)", "not_null": "true", "col_desc": "カラム2" },
{ "col_name": "col3", "col_type": "bigint", "not_null": "false", "col_desc": "カラム3" },
{ "col_name": "col4", "col_type": "varchar(100)", "not_null": "false", "col_desc": "カラム4" },
{ "col_name": "col5", "col_type": "varchar(1)", "not_null": "false", "col_desc": "カラム5" }
],
"partition": {
"partition_type": "list columns",
"partition_col": "col5",
"partition_defs" : [
{ "partition_name": "p1", "partition_limit": "('A','B')" },
{ "partition_name": "p2", "partition_limit": "('C','D')" },
{ "partition_name": "p3", "partition_limit": "('E')" }
]
},
"primary": { "constraint_name": "tab3pk", "index_cols": ["col5","col1","col2"] },
"indexes": [
{ "index_name": "ind3_1", "index_cols": ["col5","col4"] ,"unique": false},
{ "index_name": "ind3_2", "index_cols": ["col3"] ,"unique": false}
]
}

vim tab4.json

{
"schema_name": "test",
"table_name": "tab4",
"table_desc": "テストテーブル4",
"cols": [
{ "col_name": "col1", "col_type": "bigint", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "numeric(10)", "not_null": "true", "col_desc": "カラム2" }
],
"partition": {
"partition_type": "hash",
"partition_col": "col1",
"partition_defs" : [
{ "partition_name": "p1" },
{ "partition_name": "p2" },
{ "partition_name": "p3" },
{ "partition_name": "p4" }
]
},
"primary": { "constraint_name": "tab4pk", "index_cols": ["col1"] },
"indexes": []
}

vim json2ddl.sh

#!/bin/bash

# トップレベルの各要素を取得

INPUT=${1:-tab1.json}
OUTPUT=${INPUT/.json/.sql}

: > ${OUTPUT}

json=$(cat ${INPUT})

schema_name=$(echo ${json} | jq -r .schema_name)
table_name=$(echo ${json} | jq -r .table_name)
table_desc=$(echo ${json} | jq -r .table_desc)
cols=$(echo ${json} | jq -r .cols)
partition=$(echo ${json} | jq -r .partition)
primary=$(echo ${json} | jq -r .primary)
indexes=$(echo ${json} | jq -r .indexes)

# 各要素の長さ取得
partition_len=$(echo ${json} | jq -r '.partition| length')
primary_len=$(echo ${json} | jq -r '.primary| length')
indexes_len=$(echo ${json} | jq -r '.indexes| length')

echo "DROP TABLE ${schema_name}.${table_name};" >> ${OUTPUT}
echo "" >> ${OUTPUT}
echo "CREATE TABLE ${schema_name}.${table_name} (" >> ${OUTPUT}

# カラム出力
len=$(echo ${cols} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
col=$(echo ${cols} | jq .[${i}])

col_name=$(echo ${col} | jq -r .col_name)
col_type=$(echo ${col} | jq -r .col_type)
not_null=$(echo ${col} | jq -r .not_null)
col_desc=$(echo ${col} | jq -r .col_desc)

if [ ${i} -eq 0 ];then
c1=" "
else
c1=" ,"
fi

if [ "${not_null}" == "true" ];then
c2="not null"
else
c2=""
fi

echo "${c1} ${col_name} ${col_type} ${c2} COMMENT '${col_desc}'" >> ${OUTPUT}
done

echo ")" >> ${OUTPUT}
echo "COMMENT='${table_desc}'" >> ${OUTPUT}

echo " PARTITION BY ${partition_type} (${partition_col})" >> ${OUTPUT}

len=$(echo ${partition_defs} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
partition_def=$(echo ${partition_defs} | jq .[${i}])
partition_name=$(echo ${partition_def} | jq -r .partition_name)
partition_limit=$(echo ${partition_def} | jq -r .partition_limit)
if [ ${i} -eq 0 ];then
c1=" ("
else
c1=" ,"
fi

if [ "${partition_type,,}" == "range columns" ] ; then
echo "${c1} PARTITION ${partition_name} VALUES LESS THAN ( ${partition_limit} )" >> ${OUTPUT}
elif [ "${partition_type,,}" == "list columns" ] ; then
echo "${c1} PARTITION ${partition_name} VALUES IN ${partition_limit}" >> ${OUTPUT}
elif [ "${partition_type,,}" == "hash" ] ; then
echo "${c1} PARTITION ${partition_name}" >> ${OUTPUT}
else
echo "unknown partition type"
exit 100
fi
done

echo ")" >> ${OUTPUT}
fi

echo ";" >> ${OUTPUT}

# 主キー出力
if [ ${primary_len} -gt 0 ]; then
constraint_name=$(echo ${primary} | jq -r .constraint_name)
index_cols=$(echo ${primary} | jq -r .index_cols)

c1=""
len=$(echo ${index_cols} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
index_col=$(echo ${index_cols} | jq -r .[${i}])
if [ ${i} -eq 0 ];then
c1=${index_col}
else
c1="${c1},${index_col}"
fi
done

echo "" >> ${OUTPUT}
echo "ALTER TABLE ${schema_name}.${table_name} ADD CONSTRAINT ${constraint_name} PRIMARY KEY (${c1});" >> ${OUTPUT}
fi

echo "" >> ${OUTPUT}

# インデックス出力
if [ ${indexes_len} -gt 0 ]; then

len=$(echo ${indexes} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
index=$(echo ${indexes} | jq .[${i}])

index_name=$(echo ${index} | jq -r .index_name)
index_cols=$(echo ${index} | jq -r .index_cols)
unique=$(echo ${index} | jq -r .unique)

if [ "${unique}" == "true" ];then
c1="UNIQUE"
else
c1=" "
fi

c2=""
len2=$(echo ${index_cols} | jq length)
for i2 in $( seq 0 $((${len2} - 1)) ); do
index_col=$(echo ${index_cols} | jq -r .[${i2}])
if [ ${i2} -eq 0 ];then
c2=${index_col}
else
c2="${c2},${index_col}"
fi
done
echo "CREATE ${c1} INDEX ${index_name} ON ${schema_name}.${table_name}(${c2});" >> ${OUTPUT}

done
fi

echo "" >> ${OUTPUT}

exit 0

./json2ddl.sh

./json2ddl.sh tab1.json
./json2ddl.sh tab2.json
./json2ddl.sh tab3.json
./json2ddl.sh tab4.json

cat tab1.sql
cat tab2.sql
cat tab3.sql
cat tab4.sql

show create table tab1;
show create table tab2;
show create table tab3;
show create table tab4;

Oracle

(19c)

-- テーブル定義のJSONファイル

vim tab1.json

{
"schema_name": "test",
"table_name": "tab1",
"table_desc": "テストテーブル1",
"cols": [
{ "col_name": "col1", "col_type": "timestamp", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "number(30,2)", "not_null": "true", "col_desc": "カラム2" },
{ "col_name": "col3", "col_type": "integer", "not_null": "false", "col_desc": "カラム3" },
{ "col_name": "col4", "col_type": "varchar2(100)", "not_null": "false", "col_desc": "カラム4" },
{ "col_name": "col5", "col_type": "varchar2(10)", "not_null": "false", "col_desc": "カラム5" }
],
"partition": {
"partition_type": "range",
"partition_col": "col1",
"partition_defs" : [
{ "partition_name": "p1", "partition_limit": "to_date('20170101','yyyymmdd')" },
{ "partition_name": "p2", "partition_limit": "to_date('20180101','yyyymmdd')" },
{ "partition_name": "p3", "partition_limit": "to_date('20190101','yyyymmdd')" },
{ "partition_name": "pmax", "partition_limit": "maxvalue" }
]
},
"primary": { "constraint_name": "tab1pk", "index_cols": ["col1"] },
"indexes": [
{ "index_name": "ind1_1", "index_cols": ["col2"] ,"unique": true},
{ "index_name": "ind1_2", "index_cols": ["col3","col4"] ,"unique": false}
]
}

vim tab2.json

{
"schema_name": "test",
"table_name": "tab2",
"table_desc": "テストテーブル2",
"cols": [
{ "col_name": "col1", "col_type": "timestamp", "not_null": "true", "col_desc": "カラム1" }
],
"partition": {},
"primary": {},
"indexes": []
}

vim tab3.json

{
"schema_name": "test",
"table_name": "tab3",
"table_desc": "テストテーブル3",
"cols": [
{ "col_name": "col1", "col_type": "timestamp", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "number(30,2)", "not_null": "true", "col_desc": "カラム2" },
{ "col_name": "col3", "col_type": "integer", "not_null": "false", "col_desc": "カラム3" },
{ "col_name": "col4", "col_type": "varchar2(100)", "not_null": "false", "col_desc": "カラム4" },
{ "col_name": "col5", "col_type": "varchar2(1)", "not_null": "false", "col_desc": "カラム5" }
],
"partition": {
"partition_type": "list",
"partition_col": "col5",
"partition_defs" : [
{ "partition_name": "p1", "partition_limit": "('A','B')" },
{ "partition_name": "p2", "partition_limit": "('C','D')" },
{ "partition_name": "p3", "partition_limit": "('E')" },
{ "partition_name": "pmax", "partition_limit": "(default)" }
]
},
"primary": { "constraint_name": "tab3pk", "index_cols": ["col1","col2","col3"] },
"indexes": [
{ "index_name": "ind3_1", "index_cols": ["col2"] ,"unique": false},
{ "index_name": "ind3_2", "index_cols": ["col3","col4"] ,"unique": false}
]
}

vim tab4.json

{
"schema_name": "test",
"table_name": "tab4",
"table_desc": "テストテーブル4",
"cols": [
{ "col_name": "col1", "col_type": "number", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "number(10)", "not_null": "true", "col_desc": "カラム2" }
],
"partition": {
"partition_type": "hash",
"partition_col": "col1",
"partition_defs" : [
{ "partition_name": "p1" },
{ "partition_name": "p2" },
{ "partition_name": "p3" },
{ "partition_name": "p4" }
]
},
"primary": { "constraint_name": "tab4pk", "index_cols": ["col1"] },
"indexes": []
}

vim json2ddl.sh

#!/bin/bash

# トップレベルの各要素を取得

INPUT=${1:-tab1.json}
OUTPUT=${INPUT/.json/.sql}

: > ${OUTPUT}

json=$(cat ${INPUT})

# 各要素の長さ取得
partition_len=$(echo ${json} | jq -r '.partition| length')
primary_len=$(echo ${json} | jq -r '.primary| length')
indexes_len=$(echo ${json} | jq -r '.indexes| length')

echo "DROP TABLE ${schema_name}.${table_name} PURGE;" >> ${OUTPUT}
echo "" >> ${OUTPUT}
echo "CREATE TABLE ${schema_name}.${table_name} (" >> ${OUTPUT}

# カラム出力
len=$(echo ${cols} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
col=$(echo ${cols} | jq .[${i}])

echo "${c1} ${col_name} ${col_type} ${c2}" >> ${OUTPUT}
done

echo ")" >> ${OUTPUT}

echo " PARTITION BY ${partition_type} (${partition_col})" >> ${OUTPUT}

len=$(echo ${partition_defs} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
partition_def=$(echo ${partition_defs} | jq .[${i}])
partition_name=$(echo ${partition_def} | jq -r .partition_name)
partition_limit=$(echo ${partition_def} | jq -r .partition_limit)
if [ ${i} -eq 0 ];then
c1=" ("
else
c1=" ,"
fi

if [ "${partition_type,,}" == "range" ] ; then
echo "${c1} PARTITION ${partition_name} VALUES LESS THAN ( ${partition_limit} )" >> ${OUTPUT}
elif [ "${partition_type,,}" == "list" ] ; then
echo "${c1} PARTITION ${partition_name} VALUES ${partition_limit}" >> ${OUTPUT}
elif [ "${partition_type,,}" == "hash" ] ; then
echo "${c1} PARTITION ${partition_name}" >> ${OUTPUT}
else
echo "unknown partition type"
exit 100
fi
done

echo ")" >> ${OUTPUT}
fi

echo ";" >> ${OUTPUT}

# 主キー出力
if [ ${primary_len} -gt 0 ]; then
constraint_name=$(echo ${primary} | jq -r .constraint_name)
index_cols=$(echo ${primary} | jq -r .index_cols)

echo "" >> ${OUTPUT}
echo "ALTER TABLE ${schema_name}.${table_name} ADD CONSTRAINT ${constraint_name} PRIMARY KEY (${c1});" >> ${OUTPUT}
fi

echo "" >> ${OUTPUT}

# インデックス出力
if [ ${indexes_len} -gt 0 ]; then

len=$(echo ${indexes} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
index=$(echo ${indexes} | jq .[${i}])

index_name=$(echo ${index} | jq -r .index_name)
index_cols=$(echo ${index} | jq -r .index_cols)
unique=$(echo ${index} | jq -r .unique)

if [ "${unique}" == "true" ];then
c1="UNIQUE"
else
c1=" "
fi

c2=""
len2=$(echo ${index_cols} | jq length)
for i2 in $( seq 0 $((${len2} - 1)) ); do
index_col=$(echo ${index_cols} | jq -r .[${i2}])
if [ ${i2} -eq 0 ];then
c2=${index_col}
else
c2="${c2},${index_col}"
fi
done
echo "CREATE ${c1} INDEX ${schema_name}.${index_name} ON ${schema_name}.${table_name}(${c2});" >> ${OUTPUT}

done
fi

echo "" >> ${OUTPUT}

# テーブルコメント
echo "COMMENT ON TABLE ${schema_name}.${table_name} IS '${table_desc}';" >> ${OUTPUT}
echo "" >> ${OUTPUT}

# カラムコメント
len=$(echo ${cols} | jq length)
for i in $( seq 0 $(($len - 1)) ); do
col=$(echo ${cols} | jq .[${i}])
col_name=$(echo ${col} | jq -r .col_name)
col_desc=$(echo ${col} | jq -r .col_desc)

echo "COMMENT ON COLUMN ${schema_name}.${table_name}.${col_name} IS '${col_desc}';" >> ${OUTPUT}
done

echo "" >> ${OUTPUT}
echo "" >> ${OUTPUT}

exit 0

./json2ddl.sh

./json2ddl.sh tab1.json
./json2ddl.sh tab2.json
./json2ddl.sh tab3.json
./json2ddl.sh tab4.json

cat tab1.sql
cat tab2.sql
cat tab3.sql
cat tab4.sql

PostgreSQL

(15)

-- テーブル定義のJSONファイル

vim tab1.json

{
"schema_name": "public",
"table_name": "tab1",
"table_desc": "テストテーブル1",
"cols": [
{ "col_name": "col1", "col_type": "timestamp", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "numeric(30,2)", "not_null": "true", "col_desc": "カラム2" },
{ "col_name": "col3", "col_type": "integer", "not_null": "false", "col_desc": "カラム3" },
{ "col_name": "col4", "col_type": "varchar(100)", "not_null": "false", "col_desc": "カラム4" },
{ "col_name": "col5", "col_type": "varchar(10)", "not_null": "false", "col_desc": "カラム5" }
],
"partition": {
"partition_type": "range",
"partition_col": "col3",
"partition_defs" : [
{ "partition_name": "p1", "partition_limit": "100" },
{ "partition_name": "p2", "partition_limit": "200" },
{ "partition_name": "p3", "partition_limit": "300" },
{ "partition_name": "pmax", "partition_limit": "maxvalue" }
]
},
"primary": { "constraint_name": "tab1pk", "index_cols": ["col3","col1"] },
"indexes": [
{ "index_name": "ind1_1", "index_cols": ["col3","col2"] ,"unique": true},
{ "index_name": "ind1_2", "index_cols": ["col5"] ,"unique": false}
]
}

vim tab2.json

{
"schema_name": "public",
"table_name": "tab2",
"table_desc": "テストテーブル2",
"cols": [
{ "col_name": "col1", "col_type": "timestamp", "not_null": "true", "col_desc": "カラム1" }
],
"partition": {},
"primary": {},
"indexes": []
}

vim tab3.json

{
"schema_name": "public",
"table_name": "tab3",
"table_desc": "テストテーブル3",
"cols": [
{ "col_name": "col1", "col_type": "timestamp", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "numeric(30,2)", "not_null": "true", "col_desc": "カラム2" },
{ "col_name": "col3", "col_type": "integer", "not_null": "false", "col_desc": "カラム3" },
{ "col_name": "col4", "col_type": "varchar(100)", "not_null": "false", "col_desc": "カラム4" },
{ "col_name": "col5", "col_type": "varchar(1)", "not_null": "false", "col_desc": "カラム5" }
],
"partition": {
"partition_type": "list",
"partition_col": "col5",
"partition_defs" : [
{ "partition_name": "p1", "partition_limit": "('A','B')" },
{ "partition_name": "p2", "partition_limit": "('C','D')" },
{ "partition_name": "p3", "partition_limit": "('E')" }
]
},
"primary": { "constraint_name": "tab3pk", "index_cols": ["col5","col1"] },
"indexes": [
{ "index_name": "ind3_1", "index_cols": ["col5","col2"] ,"unique": false},
{ "index_name": "ind3_2", "index_cols": ["col3","col4"] ,"unique": false}
]
}

vim tab4.json

{
"schema_name": "public",
"table_name": "tab4",
"table_desc": "テストテーブル4",
"cols": [
{ "col_name": "col1", "col_type": "bigint", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "numeric(10)", "not_null": "true", "col_desc": "カラム2" }
],
"partition": {
"partition_type": "hash",
"partition_col": "col1",
"partition_defs" : [
{ "partition_name": "p1" },
{ "partition_name": "p2" },
{ "partition_name": "p3" },
{ "partition_name": "p4" }
]
},
"primary": { "constraint_name": "tab4pk", "index_cols": ["col1"] },
"indexes": []
}

vim json2ddl.sh

#!/bin/bash

# トップレベルの各要素を取得

INPUT=${1:-tab1.json}
OUTPUT=${INPUT/.json/.sql}

: > ${OUTPUT}

json=$(cat ${INPUT})

# 各要素の長さ取得
partition_len=$(echo ${json} | jq -r '.partition| length')
primary_len=$(echo ${json} | jq -r '.primary| length')
indexes_len=$(echo ${json} | jq -r '.indexes| length')

echo "DROP TABLE ${schema_name}.${table_name} CASCADE;" >> ${OUTPUT}
echo "" >> ${OUTPUT}
echo "CREATE TABLE ${schema_name}.${table_name} (" >> ${OUTPUT}

# カラム出力
len=$(echo ${cols} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
col=$(echo ${cols} | jq .[${i}])

echo "${c1} ${col_name} ${col_type} ${c2}" >> ${OUTPUT}
done

echo ")" >> ${OUTPUT}

# パーティション出力
if [ ${partition_len} -gt 0 ]; then
partition_type=$(echo ${partition} | jq -r .partition_type)
partition_col=$(echo ${partition} | jq -r .partition_col)
partition_defs=$(echo ${partition} | jq -r .partition_defs)

echo "PARTITION BY ${partition_type} (${partition_col})" >> ${OUTPUT}

echo ";" >> ${OUTPUT}
echo "" >> ${OUTPUT}

# パーティション出力
if [ ${partition_len} -gt 0 ]; then

c1="minvalue"
len=$(echo ${partition_defs} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
partition_def=$(echo ${partition_defs} | jq .[${i}])
partition_name=$(echo ${partition_def} | jq -r .partition_name)
partition_limit=$(echo ${partition_def} | jq -r .partition_limit)


if [ "${partition_type,,}" == "range" ] ; then
echo "CREATE TABLE ${schema_name}.${table_name}_${partition_name} PARTITION OF ${table_name} FOR VALUES FROM (${c1}) TO (${partition_limit});" >> ${OUTPUT}
elif [ "${partition_type,,}" == "list" ] ; then
echo "CREATE TABLE ${schema_name}.${table_name}_${partition_name} PARTITION OF ${table_name} FOR VALUES IN ${partition_limit};" >> ${OUTPUT}
elif [ "${partition_type,,}" == "hash" ] ; then
echo "CREATE TABLE ${schema_name}.${table_name}_${partition_name} PARTITION OF ${table_name} FOR VALUES WITH (modulus ${len},remainder ${i});" >> ${OUTPUT}
else
echo "unknown partition type"
exit 100
fi

c1=${partition_limit}
done

# 主キー出力
if [ ${primary_len} -gt 0 ]; then
constraint_name=$(echo ${primary} | jq -r .constraint_name)
index_cols=$(echo ${primary} | jq -r .index_cols)

echo "" >> ${OUTPUT}
echo "ALTER TABLE ${schema_name}.${table_name} ADD CONSTRAINT ${constraint_name} PRIMARY KEY (${c1});" >> ${OUTPUT}
fi

echo "" >> ${OUTPUT}

# インデックス出力
if [ ${indexes_len} -gt 0 ]; then

len=$(echo ${indexes} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
index=$(echo ${indexes} | jq .[${i}])

index_name=$(echo ${index} | jq -r .index_name)
index_cols=$(echo ${index} | jq -r .index_cols)
unique=$(echo ${index} | jq -r .unique)

if [ "${unique}" == "true" ];then
c1="UNIQUE"
else
c1=" "
fi

done
fi

echo "" >> ${OUTPUT}

# テーブルコメント
echo "COMMENT ON TABLE ${schema_name}.${table_name} IS '${table_desc}';" >> ${OUTPUT}
echo "" >> ${OUTPUT}

echo "COMMENT ON COLUMN ${schema_name}.${table_name}.${col_name} IS '${col_desc}';" >> ${OUTPUT}
done

echo "" >> ${OUTPUT}
echo "" >> ${OUTPUT}

exit 0

./json2ddl.sh

./json2ddl.sh tab1.json
./json2ddl.sh tab2.json
./json2ddl.sh tab3.json
./json2ddl.sh tab4.json

cat tab1.sql
cat tab2.sql
cat tab3.sql
cat tab4.sql

\d+ tab1
\d+ tab2
\d+ tab3
\d+ tab4

SQL Server

(2022)

-- テーブル定義のJSONファイル

vim tab1.json

{
"schema_name": "dbo",
"table_name": "tab1",
"table_desc": "テストテーブル1",
"cols": [
{ "col_name": "col1", "col_type": "datetime2", "not_null": "true", "col_desc": "カラム1" },
{ "col_name": "col2", "col_type": "numeric(30,2)", "not_null": "true", "col_desc": "カラム2" },
{ "col_name": "col3", "col_type": "bigint", "not_null": "false", "col_desc": "カラム3" },
{ "col_name": "col4", "col_type": "varchar(100)", "not_null": "false", "col_desc": "カラム4" },
{ "col_name": "col5", "col_type": "varchar(10)", "not_null": "false", "col_desc": "カラム5" }
],
"partition": {
"partition_type": "range",
"partition_col": "col1",
"partition_defs" : [
{ "partition_limit": "'2022-02-01'" },
{ "partition_limit": "'2022-03-01'" },
{ "partition_limit": "'2022-04-01'" },
{ "partition_limit": "'2022-05-01'" }
]
},
"primary": { "constraint_name": "tab1pk", "index_cols": ["col1","col2"] },
"indexes": [
{ "index_name": "ind1_1", "index_cols": ["col1","col3"] ,"unique": true},
{ "index_name": "ind1_2", "index_cols": ["col5"] ,"unique": false}
]
}

vim tab2.json

{
"schema_name": "dbo",
"table_name": "tab2",
"table_desc": "テストテーブル2",
"cols": [
{ "col_name": "col1", "col_type": "datetime2", "not_null": "true", "col_desc": "カラム1" }
],
"partition": {},
"primary": {},
"indexes": [[]]
}

vim json2ddl.sh

#!/bin/bash

# トップレベルの各要素を取得

INPUT=${1:-tab1.json}
OUTPUT=${INPUT/.json/.sql}

: > ${OUTPUT}

json=$(cat ${INPUT})

# 各要素の長さ取得
partition_len=$(echo ${json} | jq -r '.partition| length')
primary_len=$(echo ${json} | jq -r '.primary| length')
indexes_len=$(echo ${json} | jq -r '.indexes| length')

echo "DROP TABLE ${schema_name}.${table_name};" >> ${OUTPUT}
echo "" >> ${OUTPUT}

# パーティション出力
if [ ${partition_len} -gt 0 ]; then
partition_type=$(echo ${partition} | jq -r .partition_type)
partition_col=$(echo ${partition} | jq -r .partition_col)
partition_defs=$(echo ${partition} | jq -r .partition_defs)

echo "DROP PARTITION SCHEME ps_${table_name};" >> ${OUTPUT}
echo "DROP PARTITION FUNCTION pf_${table_name};" >> ${OUTPUT}
echo "" >> ${OUTPUT}

# パーティションキーのデータ型を取得する
len=$(echo ${cols} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
col=$(echo ${cols} | jq .[${i}])

col_name=$(echo ${col} | jq -r .col_name)
col_type=$(echo ${col} | jq -r .col_type)

c1=""
if [ "${col_name}" == "${partition_col}" ] ; then
c1=${col_type}
break
fi
done

echo "CREATE PARTITION FUNCTION pf_${table_name}(${c1}) AS RANGE RIGHT" >> ${OUTPUT}

c2=""
if [ "${partition_type,,}" == "range" ] ; then

len=$(echo ${partition_defs} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
partition_def=$(echo ${partition_defs} | jq .[${i}])
partition_limit=$(echo ${partition_def} | jq -r .partition_limit)

if [ ${i} -eq 0 ];then
c2=${partition_limit}
else
c2="${c2},${partition_limit}"
fi
done
echo "FOR VALUES (${c2});" >> ${OUTPUT}
else
echo "unknown partition type"
exit 100
fi

echo "" >> ${OUTPUT}

echo "CREATE PARTITION SCHEME ps_${table_name} AS PARTITION pf_${table_name} ALL TO ([PRIMARY]);" >> ${OUTPUT}

echo "CREATE TABLE ${schema_name}.${table_name} (" >> ${OUTPUT}

# カラム出力
len=$(echo ${cols} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
col=$(echo ${cols} | jq .[${i}])

echo "${c1} ${col_name} ${col_type} ${c2}" >> ${OUTPUT}
done

echo ")" >> ${OUTPUT}

# パーティション出力
if [ ${partition_len} -gt 0 ]; then
partition_type=$(echo ${partition} | jq -r .partition_type)
partition_col=$(echo ${partition} | jq -r .partition_col)
partition_defs=$(echo ${partition} | jq -r .partition_defs)

echo "ON ps_${table_name}(${partition_col})" >> ${OUTPUT}

echo ";" >> ${OUTPUT}
echo "" >> ${OUTPUT}

# 主キー出力
if [ ${primary_len} -gt 0 ]; then
constraint_name=$(echo ${primary} | jq -r .constraint_name)
index_cols=$(echo ${primary} | jq -r .index_cols)

echo "" >> ${OUTPUT}
echo "ALTER TABLE ${schema_name}.${table_name} ADD CONSTRAINT ${constraint_name} PRIMARY KEY (${c1});" >> ${OUTPUT}
fi

echo "" >> ${OUTPUT}

# インデックス出力
if [ ${indexes_len} -gt 0 ]; then

len=$(echo ${indexes} | jq length)
for i in $( seq 0 $((${len} - 1)) ); do
index=$(echo ${indexes} | jq .[${i}])

index_name=$(echo ${index} | jq -r .index_name)
index_cols=$(echo ${index} | jq -r .index_cols)
unique=$(echo ${index} | jq -r .unique)

if [ "${unique}" == "true" ];then
c1="UNIQUE"
else
c1=" "
fi

done
fi

echo "" >> ${OUTPUT}

# テーブルコメント

echo "EXEC sys.sp_addextendedproperty" >> ${OUTPUT}
echo " @name=N'MS_Description'" >> ${OUTPUT}
echo " , @value=N'テストテーブル1'" >> ${OUTPUT}
echo " , @level0type=N'SCHEMA'" >> ${OUTPUT}
echo " , @level0name=N'${schema_name}'" >> ${OUTPUT}
echo " , @level1type=N'TABLE'" >> ${OUTPUT}
echo " , @level1name=N'${table_name}'" >> ${OUTPUT}
echo ";" >> ${OUTPUT}

echo "" >> ${OUTPUT}

echo "EXEC sys.sp_addextendedproperty" >> ${OUTPUT}
echo " @name=N'MS_Description'" >> ${OUTPUT}
echo " , @value=N'${col_desc}'" >> ${OUTPUT}
echo " , @level0type=N'SCHEMA'" >> ${OUTPUT}
echo " , @level0name=N'${schema_name}'" >> ${OUTPUT}
echo " , @level1type=N'TABLE'" >> ${OUTPUT}
echo " , @level1name=N'${table_name}'" >> ${OUTPUT}
echo " , @level2type=N'COLUMN'" >> ${OUTPUT}
echo " , @level2name=N'${col_name}'" >> ${OUTPUT}
echo ";" >> ${OUTPUT}

done

echo "GO" >> ${OUTPUT}
echo "" >> ${OUTPUT}
echo "" >> ${OUTPUT}

exit 0

./json2ddl.sh

./json2ddl.sh tab1.json
./json2ddl.sh tab2.json

cat tab1.sql
cat tab2.sql