LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ukijumotahaneniarukenia

sqlite 回転コマンドの作成

Posted at

まえがき

sqliteコマンドで回転コマンド作成した。right_stairが難しかった。

参考文献

環境

$sqlite3 --version
-- Loading resources from /home/sqlite/.sqliterc
3.30.0 2019-10-04 15:03:17 c20a35336432025445f9f7e289d0cc3e4003fb17f45a4ce74c6269c407c6e09f
$bash --version
GNU bash, バージョン 5.0.0(1)-release (x86_64-pc-linux-gnu)
Copyright (C) 2019 Free Software Foundation, Inc.
ライセンス GPLv3+: GNU GPL バージョン 3 またはそれ以降 <http://gnu.org/licenses/gpl.html>

This is free software; you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

スクリプト

0.~/.sqliterc


$cat ~/.sqliterc
.mode column
.headers off
.width 0

1.left_stair.sh

先頭要素取得して末尾要素に追加していくパターン。

$cat ./left_stair.sh
# !/bin/bash

usage(){
  cat <<EOF
Usage:
\$$0 '\U1f4a'{0..9}
\$echo -e '\U1f4a'{0..9} | $0
EOF
}

chk_args(){
  declare -a argv=()
  local argv=($@)
  local argc="${#argv[@]}"
  [ "${argc}" -le 1 ] && usage && exit 1
}

circle(){
  echo -e "$@" | tr ' ' ',' | xargs -I@ bash -c 'sqlite3 testdb \
  "
  with
    sub(rsv_args,ele_cnt) as (select \"@\" as rsv_args,length(\"@\")-length(replace(\"@\",\",\",\"\"))+1 as ele_cnt)
    ,rec(rn,rsv_args,ele_cnt,ele,rest_args,done_args,rotate_args)as(
      select
        1
        ,rsv_args
        ,ele_cnt
        ,substr(rsv_args,1,instr(rsv_args,\",\")-1)
        ,substr(rsv_args,instr(rsv_args,\",\")+1)
        ,substr(rsv_args,1,instr(rsv_args,\",\")-1)
        ,rsv_args
      from sub
      union all
      select
        rn+1
        ,rsv_args
        ,ele_cnt
        ,case when substr(rest_args,1,instr(rest_args,\",\")-1)=\"\" then rest_args else substr(rest_args,1,instr(rest_args,\",\")-1) end
        ,case when substr(rest_args,1,instr(rest_args,\",\")-1)=\"\" then \"\" else substr(rest_args,instr(rest_args,\",\")+1) end
        ,done_args||\",\"||case when substr(rest_args,1,instr(rest_args,\",\")-1)=\"\" then rest_args else substr(rest_args,1,instr(rest_args,\",\")-1) end
        ,rest_args||\",\"||done_args
      from rec
      where rn<ele_cnt
    )select rotate_args from rec;
  "
' && rm testdb
}

main(){
  local init_args="$(cat -)";
  chk_args "${init_args}"
  while read ln;do circle "${ln}";done <<<"${init_args}"
}

[ -p /dev/stdin ] && cat - | main
[ -p /dev/stdin ] || echo -ne "$@" | main

先頭要素が次の対象行の末尾要素に追加されていく様子がわかる。

$./left_stair.sh '\U1f4a'{0..9}
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨
$echo -e '\U1f4a'{0..9} | ./left_stair.sh
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨

2.right_stair.sh

末尾要素取得して先頭要素に追加していくパターン。

$cat ./right_stair.sh
# !/bin/bash

usage(){
  cat <<EOF
Usage:
\$$0 '\U1f4a'{0..9}
\$echo -e '\U1f4a'{0..9} | $0
EOF
}

chk_args(){
  declare -a argv=()
  local argv=($@)
  local argc="${#argv[@]}"
  [ "${argc}" -le 1 ] && usage && exit 1
}

circle(){
  echo -e "$@" | tr ' ' ',' | xargs -I@ bash -c 'sqlite3 testdb \
    "
    with
      sub(rsv_args,ele_cnt) as (select \"@\" as rsv_args,length(\"@\")-length(replace(\"@\",\",\",\"\"))+1 as ele_cnt)
      ,rec(rn,rsv_args,ele_cnt,ele,rest_args,done_args,rotate_args)as(
        select
          1
          ,rsv_args
          ,ele_cnt
          ,replace(rsv_args,rtrim(rsv_args,replace(rsv_args,\",\",\"\")),\"\")
          ,rtrim(rtrim(rsv_args,replace(rsv_args,\",\",\"\")),\",\")
          ,replace(rsv_args,rtrim(rsv_args,replace(rsv_args,\",\",\"\")),\"\")
          ,replace(rsv_args,rtrim(rsv_args,replace(rsv_args,\",\",\"\")),\"\")||\",\"||rtrim(rtrim(rsv_args,replace(rsv_args,\",\",\"\")),\",\")
        from sub
        union all
        select
          rn+1
          ,rsv_args
          ,ele_cnt
          ,replace(rest_args,rtrim(rest_args,replace(rest_args,\",\",\"\")),\"\")
          ,rtrim(rtrim(rest_args,replace(rest_args,\",\",\"\")),\",\")
          ,replace(rest_args,rtrim(rest_args,replace(rest_args,\",\",\"\")),\"\")||\",\"||done_args
          ,replace(rest_args,rtrim(rest_args,replace(rest_args,\",\",\"\")),\"\")||\",\"||done_args||
            case when rtrim(rtrim(rest_args,replace(rest_args,\",\",\"\")),\",\")=\"\" then \"\" else \",\" end ||rtrim(rtrim(rest_args,replace(rest_args,\",\",\"\")),\",\")
        from rec
        where rn<ele_cnt
      )select rotate_args from rec;
    "
  ' && rm testdb
}

main(){
  local init_args="$(cat -)";
  chk_args "${init_args}"
  while read ln;do circle "${ln}";done <<<"${init_args}"
}

[ -p /dev/stdin ] && cat - | main
[ -p /dev/stdin ] || echo -ne "$@" | main

末尾要素が先頭要素に追加されていく様子がわかる。

$./right_stair.sh '\U1f4a'{0..9}
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩
$echo -e '\U1f4a'{0..9} | ./right_stair.sh
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩

3.square.sh

上記スクリプトをそれぞれ左と右、右と左に並べて、与える引数を正順と逆順の2つにしたパターン。
サブシェルの標準エラー出力が親シェルまで引き継がれてきてエラー出力してきたから、2>/dev/nullでミュート。
プロセス置換だと、こういう動きになるんかな。

$cat ./square.sh 
# !/bin/bash

usage(){
  cat <<EOF
Usage:
\$$0 '\U1f4a'{0..9}
\$echo -e '\U1f4'{a..b}{{0..9},{A..F}} | $0
\$$0 '\U1f4a'{{0..9},{A..F}}
EOF
}

chk_args(){
  declare -a argv=()
  local argv=($@)
  local argc="${#argv[@]}"
  [ "${argc}" -le 1 ] && usage && exit 1
}

circle(){
  paste -d' ' <(echo "$@" | ./left_stair.sh 2>/dev/null) <(echo "$@" | rev | ./right_stair.sh 2>/dev/null)
  paste -d' ' <(echo "$@" | ./right_stair.sh 2>/dev/null) <(echo "$@" | rev | ./left_stair.sh 2>/dev/null)
}

main(){
  local init_args="$(cat -)";
  chk_args "${init_args}"
  while read ln;do circle "${ln}";done <<<"${init_args}"
}

[ -p /dev/stdin ] && cat - | main
[ -p /dev/stdin ] || echo -ne "$@" | main

なんかちょっといつもと違うけど、いいか。

$./square.sh '\U1f4a'{0..9}
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩 💠,💩,💨,💧,💦,💥,💤,💣,💢,💡
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠 💡,💠,💩,💨,💧,💦,💥,💤,💣,💢
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡 💢,💡,💠,💩,💨,💧,💦,💥,💤,💣
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢 💣,💢,💡,💠,💩,💨,💧,💦,💥,💤
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣 💤,💣,💢,💡,💠,💩,💨,💧,💦,💥
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤 💥,💤,💣,💢,💡,💠,💩,💨,💧,💦
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥 💦,💥,💤,💣,💢,💡,💠,💩,💨,💧
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦 💧,💦,💥,💤,💣,💢,💡,💠,💩,💨
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧 💨,💧,💦,💥,💤,💣,💢,💡,💠,💩
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨 💩,💨,💧,💦,💥,💤,💣,💢,💡,💠
💩,💠,💡,💢,💣,💤,💥,💦,💧,💨 💩,💨,💧,💦,💥,💤,💣,💢,💡,💠
💨,💩,💠,💡,💢,💣,💤,💥,💦,💧 💨,💧,💦,💥,💤,💣,💢,💡,💠,💩
💧,💨,💩,💠,💡,💢,💣,💤,💥,💦 💧,💦,💥,💤,💣,💢,💡,💠,💩,💨
💦,💧,💨,💩,💠,💡,💢,💣,💤,💥 💦,💥,💤,💣,💢,💡,💠,💩,💨,💧
💥,💦,💧,💨,💩,💠,💡,💢,💣,💤 💥,💤,💣,💢,💡,💠,💩,💨,💧,💦
💤,💥,💦,💧,💨,💩,💠,💡,💢,💣 💤,💣,💢,💡,💠,💩,💨,💧,💦,💥
💣,💤,💥,💦,💧,💨,💩,💠,💡,💢 💣,💢,💡,💠,💩,💨,💧,💦,💥,💤
💢,💣,💤,💥,💦,💧,💨,💩,💠,💡 💢,💡,💠,💩,💨,💧,💦,💥,💤,💣
💡,💢,💣,💤,💥,💦,💧,💨,💩,💠 💡,💠,💩,💨,💧,💦,💥,💤,💣,💢
💠,💡,💢,💣,💤,💥,💦,💧,💨,💩 💠,💩,💨,💧,💦,💥,💤,💣,💢,💡

あとがき

sqliteは提供されているファンクションがその他のDBベンダよりもコンパクトだから、ちょっと大変だった。

以上、ありがとうございました。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?