SQLiteのコマンドラインインターフェースを導入して、シェルスクリプトからSQLiteのデータベースを作成して、データの表示を行います。データはSQLのwindow関数を用いて、増減率も表示したいと思います。
目次
SQLiteのコマンドラインインターフェースの導入
SQLiteをシェルで利用するために、SQLiteのコマンドラインインターフェースを環境にセットアップします。
まず、SQLiteのホームページより最新のソースコードをダウンロードします(最新のバージョンはSQLiteのホームページで確認してください)。
※この後の例で、SQLのwindow関数(SQLiteの場合>=3.25より)を利用する予定なので、apt等のパッケージ管理ツールでダウンロードする場合、バージョンが古い可能性があります。
|
1 |
$ wget https://www.sqlite.org/2020/sqlite-amalgamation-3320100.zip |
そして、ダウンロードしたファイルを展開します。
|
1 |
$ unzip sqlite-amalgamation-3320100.zip |
展開されたファイルの内容は、単純です。
|
1 2 |
$ ls shell.c sqlite3.c sqlite3.h sqlite3ext.h |
これをgccを用いてコンパイルします。コンパイルオプションはsqliteをフル機能で使えるようにしています。公式サイトでのフル機能のコンパイル方法とほぼ一緒ですが、さらにライブラリの追加が必要で、"-lm"も追加してください。
|
1 |
$ gcc -Os -I. -DSQLITE_THREADSAFE=0 -DSQLITE_ENABLE_FTS4 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DHAVE_USLEEP -DHAVE_READLINE shell.c sqlite3.c -ldl -lreadline -lncurses -lm -o sqlite3 |
また、コンパイルするときにncursesがないと警告されたら、必要なパッケージをインストールしてください。(以下は、Ubuntuでの例になります。)
|
1 |
$ sudo apt install lib32ncurses5-dev |
シェルスクリプトの作成
日本の人口について、データベースに保存・表示を行うシェルスクリプトであるshellsqlite.shを作成します。
実装する機能については、create,insert,insertfile,update,delete,displayの機能を実装したいと思います。
利用するデータについて
日本の人口データは総務省統計局のホームページの「人口推計」を参考にデータをcsvファイルへ加工したものを利用します。また、人口の単位は千人単位になります。
population.csv
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
year,tp,tpm,tpf,jp,jpm,jpf 2000,126926,62111,64815,125613,61488,64125 2001,127316,62265,65051,125930,61615,64316 2002,127486,62295,65190,126053,61629,64424 2003,127694,62368,65326,126206,61677,64529 2004,127787,62380,65407,126266,61674,64592 2005,127768,62349,65419,126205,61618,64587 2006,127901,62387,65514,126286,61630,64656 2007,128033,62424,65608,126347,61635,64712 2008,128084,62422,65662,126340,61609,64730 2009,128032,62358,65674,126343,61586,64757 2010,128057,62328,65730,126382,61572,64810 2011,127834,62207,65627,126210,61477,64732 2012,127593,62080,65513,126023,61379,64644 2013,127414,61985,65429,125803,61263,64540 2014,127237,61901,65336,125562,61142,64419 2015,127095,61842,65253,125319,61023,64297 |
それぞれのアルファベットの意味は
year:年
tp:総人口
tpm:総人口での男性
tpf:総人口での女性
jp:日本人人口
jpm:日本人人口での男性
jpf:日本人人口での女性
になります。
シェルスクリプトの実装
いくつかの機能を実装するため、引数によってそれぞれの機能が利用できるようにします。
つまり、シェルスクリプトの後にcreateやinsertなどのコマンドを入力することでそれぞれの機能を利用できるようにします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
case "$1" in create ) shift shellsqlite_create "$@";; insert ) shift shellsqlite_insert "$@";; insertfile ) shift shellsqlite_insertfile "$@";; update ) shift shellsqlite_update "$@";; delete ) shift shellsqlite_delete "$@";; display ) shift shellsqlite_display "$@";; * ) shellsqlite_help exit 0 ;; esac |
また、機能の簡単なヘルプをそれぞれ記述します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
shellsqlite_help(){ progname="$(basename $0)" echo "$progname help" echo "* $progname create <dbfile>" cat <<insertmessage * $progname insert <dbfile> -year <year> \ -tp <total population> -tpm <male of the total population> -tpf <female of the total population> \ -jp <japanease population> -jpm <male of the japanease population> -jpf <female of the japanease population> insertmessage cat <<insertfilemessage * $progname insertfile <dbfile> <insertfile> insertfile format (The poplutation is shown in thousands): year,tp,tpm,tpf,jp,jpm,jpf 2000,126926,62111,64815,125613,61488,64125 ... insertfilemessage cat <<updatemessage * $progname update <dbfile> -year <year> \ [-tp <total population>] [-tpm <male of the total population>] [-tpf <female of the total population>] \ [-jp <japanease population>] [-jpm <male of the japanease population>] [-jpf <female of the japanease population>] updatemessage echo "* $progname delete <dbfile> -year <year>" echo "* $progname display <dbfile>" } |
createコマンド
createコマンドはデータベースファイルを作成する機能になっています。
このコマンドは以下のような形式で利用できるようにします。
|
1 |
./shellsqlite.sh create <ファイル名> |
この機能は以下のように実装します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
shellsqlite_create(){ filename="$1" if ! [ -e "$filename" ] ; then sqlite3 "$filename" " create table population ( year integer, tp integer, tpm integer, tpf integer, jp integer, jpm integer, jpf integer ) ;" else echo "error: $filename exists. Don't create $filename." fi } |
insertコマンド
insertコマンドはデータベースにデータを１件入力する機能になります。
このコマンドは、少々長い形式ですが、以下のような形式で利用できるようにします。
|
1 |
$progname insert <DBファイル> -year <年> -tp <総人口> -tpm <総人口の男性> -tpf <総人口の女性> -jp <日本人人口> -jpm <日本人人口の男性> -jpf <日本人人口の女性> |
この機能は以下のように実装します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
shellsqlite_insert(){ filename="$1" shift args="$@" declare -i year tp tpm tpf jp jpm jpf if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi while ! [ -z "$1" ] ; do case "$1" in -year) shift;year="$1";shift;; -tp) shift;tp="$1";shift;; -tpm) shift;tpm="$1";shift;; -tpf) shift;tpf="$1";shift;; -jp) shift;jp="$1";shift;; -jpm) shift;jpm="$1";shift;; -jpf) shift;jpf="$1";shift;; *) echo "warning: invalid argument: $1" shift ;; esac done if [ -z "$year" ] || [ -z "$tp" ] || [ -z "$tpm" ] || [ -z "$tpf" ] || [ -z "$jp" ] || [ -z "$jpm" ] || [ -z "$jpf" ] ; then echo "error: insert invalid argument." echo "args:$args" echo "year:$year" echo "tp:$tp" echo "tpm:$tpm" echo "tpf:$tpf" echo "jp:$jp" echo "jpm:$jpm" echo "jpf:$jpf" exit 1 fi sqlite3 "$filename" " insert into population ( year, tp, tpm, tpf, jp, jpm, jpf ) values ( $year , $tp , $tpm , $tpf , $jp , $jpm , $jpf ) ;" } |
insertfileコマンド
insertfileコマンドはデータベースにcsvファイルをsqliteの機能を用いて、インポートする機能になっています。
このコマンドは以下のような形式で利用できるようにします。
|
1 |
./shellsqlite.sh insertfile <DBファイル> <インポートするファイル> |
この機能は以下のように実装します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
shellsqlite_insertfile(){ filename="$1" insertfile="$2" if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi if [ -e "$insertfile" ] ; then csvfile=$(mktemp) trap "rm -f $csvfile" EXIT else echo "error: insertfile \"$insertfile\" not found" exit 1 fi sed -e "1d;/^$/d" $insertfile > $csvfile sqlite3 "$filename" <<EOS .mode csv .import $csvfile population EOS } |
updateコマンド
updateコマンドは"年"をキーにして、データを更新する機能になります。
このコマンドは年のみ必須項目として、他の項目は任意な項目として、以下のような形式で利用できるようにします。
|
1 |
./shellsqlite update <DBファイル> -year <年> [-tp <総人口>] [-tpm <総人口の男性>] [-tpf <総人口の女性>] [-jp <日本人人口>] [-jpm <日本人人口の男性>] [-jpf <日本人人口の女性>] |
この機能は以下のように実装します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
shellsqlite_update(){ filename="$1" shift args="$@" tp="tp=tp" tpm="tpm=tpm" tpf="tpf=tpf" jp="jp=jp" jpm="jpm=jpm" jpf="jpf=jpf" declare -i year ntp ntpm ntpf njp njpm njpf if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi while ! [ -z "$1" ] ; do case "$1" in -year) shift;year="$1";shift;; -tp) shift;ntp="$1";tp="tp=$ntp";shift;; -tpm) shift;ntpm="$1";tpm="tpm=$ntpm";shift;; -tpf) shift;ntpf="$1";tpf="tpf=$ntpf";shift;; -jp) shift;njp="$1";jp="jp=$njp";shift;; -jpm) shift;njpm="$1";jpm="jpm=$njpm";shift;; -jpf) shift;njpf="$1";jpf="jpf=$njpf";shift;; *) echo "warning: invalid argument: $1" shift ;; esac done if [ -z "$year" ] ; then echo "error: update invalid argument. update is required '-year'." echo "args: $args" exit 1 fi sqlite3 "$filename" " update population set $tp , $tpm , $tpf , $jp , $jpm , $jpf where year=$year ;" } |
deleteコマンド
deleteコマンドは"年"をキーにして、データを削除する機能になります。
また、deleteコマンドは
|
1 |
./shellsqlite delete population.db -year 2002 -year 2003 |
みたいに複数の"年"を指定して、複数のデータを削除できるような形式で実装してみます。
この機能は以下のように実装します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
shellsqlite_delete_datadelete(){ filename="$1" declare -i year="$2" sqlite3 "$filename" " delete from population where year=$year ;" } shellsqlite_delete(){ filename="$1" args="$@" declare -i year if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi while ! [ -z "$1" ] ; do case "$1" in -year) shift year="$1" shellsqlite_delete_datadelete $filename $year shift ;; *) echo "warning: invalid argument: $1" shift ;; esac done if [ -z "$year" ] ; then echo "error: delete invalid argument. args: $args" exit 1 fi } |
displayコマンド
displayコマンドは、データベースを読み込んで、年・総人口・総人口の増減率・日本人人口・日本人人口の増減率を表示する機能になっています。
lag(tp) over (order by year)のような部分はSQLのwindow関数になっています。これはテーブルを"年"に並べて現在行の前行のデータを出します。これによって、前年との増減率を計算できます。
このコマンドは以下のような形式で利用できるようにします。
|
1 |
./shellsqlite.sh display <DBファイル> |
この機能は以下のように実装します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
shellsqlite_display{ filename="$1" if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi sqlite3 -header "$filename" " select year as '年度', tp as '総人口', round((tp*1.0/(lag(tp) over (order by year))-1)*100,2) as '総人口(増減率)', jp as '日本人人口', round((jp*1.0/(lag(jp) over (order by year))-1)*100,2) as '日本人人口(増減率)' from population ;" } |
シェルスクリプトのソースコード
shellsqlite.shのソースコードは以下のようになります。
また、完成したshellsqlite.shのソースコードは以下のようになります。
shellsqlite.sh
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 |
#!/bin/bash shellsqlite_help(){ progname="$(basename $0)" echo "$progname help" echo "* $progname create <dbfile>" cat <<insertmessage * $progname insert <dbfile> -year <year> \ -tp <total population> -tpm <male of the total population> -tpf <female of the total population> \ -jp <japanease population> -jpm <male of the japanease population> -jpf <female of the japanease population> insertmessage cat <<insertfilemessage * $progname insertfile <dbfile> <insertfile> insertfile format (The poplutation is shown in thousands): year,tp,tpm,tpf,jp,jpm,jpf 2000,126926,62111,64815,125613,61488,64125 ... insertfilemessage cat <<updatemessage * $progname update <dbfile> -year <year> \ [-tp <total population>] [-tpm <male of the total population>] [-tpf <female of the total population>] \ [-jp <japanease population>] [-jpm <male of the japanease population>] [-jpf <female of the japanease population>] updatemessage echo "* $progname delete <dbfile> -year <year>" echo "* $progname display <dbfile>" } shellsqlite_create(){ filename="$1" if ! [ -e "$filename" ] ; then sqlite3 "$filename" " create table population ( year integer, tp integer, tpm integer, tpf integer, jp integer, jpm integer, jpf integer ) ;" else echo "error: $filename exists. Don't create $filename." fi } shellsqlite_insert(){ filename="$1" shift args="$@" declare -i year tp tpm tpf jp jpm jpf if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi while ! [ -z "$1" ] ; do case "$1" in -year) shift;year="$1";shift;; -tp) shift;tp="$1";shift;; -tpm) shift;tpm="$1";shift;; -tpf) shift;tpf="$1";shift;; -jp) shift;jp="$1";shift;; -jpm) shift;jpm="$1";shift;; -jpf) shift;jpf="$1";shift;; *) echo "warning: invalid argument: $1" shift ;; esac done if [ -z "$year" ] || [ -z "$tp" ] || [ -z "$tpm" ] || [ -z "$tpf" ] || [ -z "$jp" ] || [ -z "$jpm" ] || [ -z "$jpf" ] ; then echo "error: insert invalid argument." echo "args:$args" echo "year:$year" echo "tp:$tp" echo "tpm:$tpm" echo "tpf:$tpf" echo "jp:$jp" echo "jpm:$jpm" echo "jpf:$jpf" exit 1 fi sqlite3 "$filename" " insert into population ( year, tp, tpm, tpf, jp, jpm, jpf ) values ( $year , $tp , $tpm , $tpf , $jp , $jpm , $jpf ) ;" } shellsqlite_insertfile(){ filename="$1" insertfile="$2" if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi if [ -e "$insertfile" ] ; then csvfile=$(mktemp) trap "rm -f $csvfile" EXIT else echo "error: insertfile \"$insertfile\" not found" exit 1 fi sed -e "1d;/^$/d" $insertfile > $csvfile sqlite3 "$filename" <<EOS .mode csv .import $csvfile population EOS } shellsqlite_update(){ filename="$1" shift args="$@" tp="tp=tp" tpm="tpm=tpm" tpf="tpf=tpf" jp="jp=jp" jpm="jpm=jpm" jpf="jpf=jpf" declare -i year ntp ntpm ntpf njp njpm njpf if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi while ! [ -z "$1" ] ; do case "$1" in -year) shift;year="$1";shift;; -tp) shift;ntp="$1";tp="tp=$ntp";shift;; -tpm) shift;ntpm="$1";tpm="tpm=$ntpm";shift;; -tpf) shift;ntpf="$1";tpf="tpf=$ntpf";shift;; -jp) shift;njp="$1";jp="jp=$njp";shift;; -jpm) shift;njpm="$1";jpm="jpm=$njpm";shift;; -jpf) shift;njpf="$1";jpf="jpf=$njpf";shift;; *) echo "warning: invalid argument: $1" shift ;; esac done if [ -z "$year" ] ; then echo "error: update invalid argument. update is required '-year'." echo "args: $args" exit 1 fi sqlite3 "$filename" " update population set $tp , $tpm , $tpf , $jp , $jpm , $jpf where year=$year ;" } shellsqlite_delete_datadelete(){ filename="$1" declare -i year="$2" sqlite3 "$filename" " delete from population where year=$year ;" } shellsqlite_delete(){ filename="$1" args="$@" declare -i year if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi while ! [ -z "$1" ] ; do case "$1" in -year) shift year="$1" shellsqlite_delete_datadelete $filename $year shift ;; *) echo "warning: invalid argument: $1" shift ;; esac done if [ -z "$year" ] ; then echo "error: delete invalid argument. args: $args" exit 1 fi } shellsqlite_display(){ filename="$1" if ! [ -e "$filename" ] ; then echo "error: dbfile \"$filename\" not found" exit 1 fi sqlite3 -header "$filename" " select year as '年度', tp as '総人口', round((tp*1.0/(lag(tp) over (order by year))-1)*100,2) as '総人口(増減率)', jp as '日本人人口', round((jp*1.0/(lag(jp) over (order by year))-1)*100,2) as '日本人人口(増減率)' from population ;" } case "$1" in create ) shift shellsqlite_create "$@";; insert ) shift shellsqlite_insert "$@";; insertfile ) shift shellsqlite_insertfile "$@";; update ) shift shellsqlite_update "$@";; delete ) shift shellsqlite_delete "$@";; display ) shift shellsqlite_display "$@";; * ) shellsqlite_help exit 0 ;; esac |
作成したシェルスクリプトの利用
shellsqlite.shを簡単に利用してみます。まず、shellsqlite.shに実行権限を付与します。
|
1 2 3 |
$ chmod +x shellsqlite.sh $ ls -l shellsqlite.sh -rwxrwxr-x 1 ubuntu ubuntu 5735 6月 1 13:24 shellsqlite.sh |
createコマンドを行って、データベースを作成し、insertfileコマンドでデータのcsvファイルをインポートします。その後、displayコマンドを用いると日本の総人口と日本人人口のデータが前年との増減率と一緒に表示されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
$ ./shellsqlite.sh create population.db $ ./shellsqlite.sh insertfile population.db population.csv $ ./shellsqlite.sh display population.db 年度|総人口|総人口(増減率)|日本人人口|日本人人口(増減率) 2000|126926||125613| 2001|127316|0.31|125930|0.25 2002|127486|0.13|126053|0.1 2003|127694|0.16|126206|0.12 2004|127787|0.07|126266|0.05 2005|127768|-0.01|126205|-0.05 2006|127901|0.1|126286|0.06 2007|128033|0.1|126347|0.05 2008|128084|0.04|126340|-0.01 2009|128032|-0.04|126343|0.0 2010|128057|0.02|126382|0.03 2011|127834|-0.17|126210|-0.14 2012|127593|-0.19|126023|-0.15 2013|127414|-0.14|125803|-0.17 2014|127237|-0.14|125562|-0.19 2015|127095|-0.11|125319|-0.19 |
2011年以降から日本の人口がどんどん減少している様子が見えます・・・。
今回はシェルスクリプトとSQLiteを利用してデータベースを利用した簡単なプログラムを作成しました。displayの機能を変えれば、別の表も表示できます。また、コマンドの追加を行ったり、別のデータを読み込めるようにしたり、いろいろなことができると思います。
参考
データの出典：総務省統計局ホームページの「人口推計」（総務省統計局）
人口推計 長期時系列データ 長期時系列データ（平成12年～27年）
全国 1 男女別人口（各年10月1日現在）- 総人口，日本人人口（平成12年～27年）より
(2020年5月30日に利用)