やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3でTSVからJSON文字列を作成する（3.33.0以降）

SQLite3でTSVからJSON文字列を作成する（3.33.0以降）

SQLite3

　TSV→DB→JSON文字列。

成果物

手順

　キモは.modeの第一引数にjsonを渡すこと。SQLite 3.33.0から追加された機能である。

.mode json

　例は以下。

sqlite3 :memory: -batch -interactive '.mode json' \
'select 12 as Age, "Yamada" as Name'

[{"Age":12,"Name":"Yamada"}]

　JSON配列を表す[,]が先頭と末尾に付与されている。不要なので削除する。

sqlite3 :memory: -batch -interactive '.mode json' \
'select 12 as Age, "Yamada" as Name' \
| sed 's/^\[//' | sed 's/\]$//'

{"Age":12,"Name":"Yamada"}

　上記API用のJSONを作ってみる。

NAME='RepoName'
DESCRIPTION='リポジトリの説明文。'
HOMEPAGE='https://...'
KEYS=(name description homepage)
VALUES=("$NAME" "$DESCRIPTION" "$HOMEPAGE")
TABLE_NAME=Parameters
cat <(IFS=$'\t'; echo "${KEYS[*]}") <(IFS=$'\t'; echo "${VALUES[*]}") | 
sqlite3 :memory: -batch -interactive \
'.mode tabs' '.import /dev/stdin '"$TABLE_NAME" \
'.mode json' 'select * from '"$TABLE_NAME" \
| sed 's/^\[//' | sed 's/\]$//'

{"name":"RepoName","description":"リポジトリの説明文。","homepage":"https://..."}

　連想配列でやってみる。順序は定義順に保持されずハッシュ順である。

declare -A KV
KV['name']='RepoName'
KV['description']='リポジトリの説明文。'
KV['homepage']='https://...'
TABLE_NAME=Parameters
cat <(IFS=$'\t'; echo "${!KV[*]}") <(IFS=$'\t'; echo "${KV[*]}") | 
sqlite3 :memory: -batch -interactive \
'.mode tabs' '.import /dev/stdin '"$TABLE_NAME" \
'.mode json' 'select * from '"$TABLE_NAME" \
| sed 's/^\[//' | sed 's/\]$//'

{"homepage":"https://...","description":"リポジトリの説明文。","name":"RepoName"}

問題

数値型のときはどうする？

　.importでテーブル作成したらすべてTEXT型になってしまう。JSONは文字列型のとき値を""で囲うが、数値型のときは""で囲わない。だが.importしてすべてTEXT型になってしまうため、すべて""で囲われてしまう。数値型は""で囲わぬようにするにはどうすればいい？

　解法は以下の2つ。

  • create tableしてから.importする
  • TSVの値から型を推測してcreate table文を作る

　以下のJSONを取得すると想定したとき、それぞれの方法でコードを書いてみる。

{"Age":12,"Name":"Yamada"}

create tableしてから.importする

　まずはcreate文だけで成功するか確認。

create.sh

TABLE_NAME=Parameters
SQL=$(cat <<-EOS
  create table ${TABLE_NAME}(
      Age  int,
      Name text
  );
 EOS
)
sqlite3 :memory: "$SQL"

　OK。次はレコードをTSVで渡して.importしJSON出力する。

import.sh

#!/usr/bin/env bash
TABLE_NAME=Parameters
SQL=$(cat <<-EOS
  create table ${TABLE_NAME}(
      Age  int,
      Name text
  );
 EOS
)
echo -e "12\tYamada" | 
sqlite3 :memory: "$SQL" \
'.mode tabs' '.import /dev/stdin '"$TABLE_NAME" \
'.mode json' 'select * from '"$TABLE_NAME" \
| sed 's/^\[//' | sed 's/\]$//'

{"Age":12,"Name":"Yamada"}

　成功。

　だが、shell構文とSQL構文でそれぞれに情報がバラバラになってしまう。テーブル名、列名、列順、列型、データ列順がバラバラになる。

TSVの値から型を推測してcreate table文を作る

　create文を自動作成する。

unset KV
declare -A KV
KV['Age']=12
KV['Name']='Yamada'
IsInt() { test 0 -eq $1 > /dev/null 2>&1 || expr $1 + 0 > /dev/null 2>&1; }
IsFloat() { [[ "$1" =~ ^[0-9]+\.[0-9]+$ ]] && return 0 || return 1; }
FIELDS=()
for KEY in ${!KV[*]}; do
    TYPE='TEXT'
    IsFloat "${KV["$KEY"]}" && TYPE='REAL'
    IsInt "${KV["$KEY"]}" && TYPE='INT'
    FIELDS+=("${KEY} ${TYPE}")
done
SQL='create table '"$TABLE_NAME"'('
SQL+="$(IFS=,; echo "${FIELDS[*]}")"')'
echo -e "12\tYamada" | 
sqlite3 :memory: "$SQL" \
'.mode tabs' '.import /dev/stdin '"$TABLE_NAME" \
'.mode json' 'select * from '"$TABLE_NAME" \
| sed 's/^\[//' | sed 's/\]$//'

{"Age":12,"Name":"Yamada"}

　SQL文に改行を含めるのに苦労した。結論から言えばシェルの$'\n'を使う。以下URLにはchar(10),x'0a',改行そのまま等の方法が提示さているが、どれも構文エラーになった。

sqlite3 :memory: 'select 1;'
sqlite3 :memory: 'select char(10) 1;'
sqlite3 :memory: 'select x'0a' 1;'
sqlite3 :memory: 'select \
 1;'
sqlite3 :memory: "select $'\n' 1;"
sqlite3 :memory: 'select '$'\n'' 1;'

1
Error: near "1": syntax error
Error: near "1": syntax error
Error: unrecognized token: "\"
Error: unrecognized token: "$"
1

　sedで改行を出力するには、さらに面倒なエスケープが必要だった……。

$ SQL='create table Parameters(Age INT,Name TEXT)'
$ echo -e "$SQL" | sed 's/ INT,/ INT,\'$'\n/g'
create table Parameters(Age INT,
Name TEXT)

所感

　できたのはいいが、シェルと連携する難易度が高すぎる。TSVからもっと楽にcreate tableできたらいいのに……。

対象環境

$ uname -a
Linux raspberrypi 4.19.97-v7l+ #1294 SMP Thu Jan 30 13:21:14 GMT 2020 armv7l GNU/Linux