[CetnOS7+Nodejs+SQLite3]郵便番号WebAPI作成

目的

とりあえず自分用に動くやつを用意しようかと・・・

以下、手順

SQLite3を最新版にしておく。（なんとなく）

sudo yum install sqlite
パッケージ sqlite-3.7.17-8.el7.x86_64 はインストール済みか最新バージョンです

sqlite3 zipcd.sqlite3

sudo wget https://sqlite.org/2016/sqlite-autoconf-3150000.tar.gz

tar xzf sqlite-autoconf-3150000.tar.gz 
cd sqlite-autoconf-3150000
gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread

#curl -o csv.c https://www.sqlite.org/src/raw/ext/misc/csv.c?name=816a3715356e4210dae2d242057745e937050896 
#gcc -g -fPIC -shared csv.c -o csv.so -I.

sudo rm /usr/bin/sqlite3
sudo cp -p ./sqlite3 /usr/bin/sqlite3

郵便番号DBのsqliteファイルを作成する。

wget http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
unzip ken_all.zip
nkf -Sw KEN_ALL.CSV > KEN_ALL_UTF8.CSV

sqlite3 /tmp/zipcd.db<<EOF
create table zipcdmst (
  govcd  TEXT,
  zipcd_old  TEXT,
  zipcd  TEXT,
  pref_kana  TEXT,
  city_kana  TEXT,
  town_kana  TEXT,
  pref  TEXT,
  city  TEXT,
  town  TEXT,
  flg_n1  NUMERIC,
  flg_kaza  NUMERIC,
  flg_chome  NUMERIC,
  flg_1n  NUMERIC,
  upd_stat  INTEGER,
  upd_reason  INTEGER);
pragma table_info(zipcd);
.exit
EOF

sqlite3 /tmp/zipcd.db<<EOF
.mode csv
.import KEN_ALL_UTF8.CSV zipcdmst
select city from zipcdmst limit 1;
.exit
EOF

スクリプトにして自動更新処理

sudo vim /usr/local/bin/update_zipcd_sqlite.sh

update_zipcd_sqlite.sh

#!/bin/sh

echo `date "+[%Y/%m/%d %H:%M:%S]"`"** START! update zipcdmst **"
cd /tmp
wget http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
unzip ken_all.zip
nkf -Sw KEN_ALL.CSV > KEN_ALL_UTF8.CSV
rm ken_all.zip
rm KEN_ALL.CSV
sqlite3 zipcd.db<<EOF
delete from zipcdmst;
VACUUM;
.mode csv
.import KEN_ALL_UTF8.CSV zipcdmst
select count(*) from zipcdmst;
.exit
EOF
rm /tmp/KEN_ALL_UTF8.CSV
echo `date "+[%Y/%m/%d %H:%M:%S]"`"** FINISH! update zipcdmst **"

sudo chmod +x /usr/local/bin/update_zipcd_sqlite.sh
sudo crontab -u root -e
0 3 1 * * /usr/local/bin/update_zipcd_sqlite.sh >> /var/log/zip_upd.log

APIサーバ作成(Node.js)

sudo yum install nodejs --enablerepo=epel
sudo mkdir -p /usr/node/zipcd/
cd /usr/node/zipcd/
sudo vim /usr/node/zipcd/zipcdapi.js

zipcdapi.js

process.on('uncaughtException', function(err) {
    console.error(err);
});

var express = require('express');
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('/tmp/zipcd.db');
var app = express();

server.get('/', function(req, res){
    console.log(req.uri);
    if(!req.query.z)res.status(400).send();
    if(!new String(req.query.z).match("^[0-9]{7}$"))res.status(400).send();
    db.get("select * from zipcdmst where zipcd = ?", [req.query.z], function(err, row){
      if(err){res.status(500).send();return;}
      if(typeof row === 'undefined'){res.status(400).send();return;}
      res.json(row);
    });
});

/* サーバー待ちうけ開始 */
runserver();
function runserver(){
    var server;
    var servercallback = function(req, res){
      var host = server.address().address;
      var port = server.address().port;
      console.log('zipcd api ready! request to -> http://%s:%s/?z=<zipcd>', host, port);
    }
    /* HTTP(非SSL) */
    server = app.listen(80, "127.0.0.1",servercallback);
}

sudo npm install -g express
sudo npm install -g sqlite3
sudo vim /usr/local/bin/zipcdapiserver.sh

zipcdapiserver.sh

#!/bin/sh
node /usr/node/zipcd/zipcdapi.js

sudo chmod +x /usr/local/bin/zipcdapiserver.sh

サービス化

sudo vim /etc/systemd/system/zipcdapi.service

zipcdapi.service

[Unit]
Description = zipcdapi daemon

[Service]
ExecStart = /usr/local/bin/zipcdapiserver.sh
Restart = always
Type = simple

[Install]
WantedBy = multi-user.target

sudo systemctl enable zipcdapi
sudo systemctl start zipcdapi
sudo systemctl status zipcdapi
● zipcdapi.service - zipcdapi daemon
   Loaded: loaded (/etc/systemd/system/zipcdapi.service; enabled; vendor preset: disabled)
   Active: active (running) since 月 2016-10-17 16:39:49 JST; 5s ago
 Main PID: 10651 (zipcdapiserver.)
   CGroup: /system.slice/zipcdapi.service
           ├─10651 /bin/sh /usr/local/bin/zipcdapiserver.sh
           └─10652 node /usr/node/zipcd/zipcdapi.js

10月 17 16:39:49 zipcdapi.local systemd[1]: Started zipcdapi daemon.
10月 17 16:39:49 zipcdapi.local systemd[1]: Starting zipcdapi daemon...
10月 17 16:39:49 zipcdapi.local zipcdapiserver.sh[10651]: zipcd api ready! request to -> http://127.0.0.1:80/?z=<zipcd>

動作確認

$ sudo curl -X GET -i http://localhost/
HTTP/1.1 400 Bad Request
X-Powered-By: Express
Date: Mon, 17 Oct 2016 08:37:52 GMT
Connection: keep-alive
Content-Length: 0

$ sudo curl -X GET -i http://127.0.0.1/?z=1000000
HTTP/1.1 200 OK
X-Powered-By: Express
Content-Type: application/json; charset=utf-8
Content-Length: 327
ETag: W/"147-1knoxUQ+ApNanTMl/oY3jA"
Date: Mon, 17 Oct 2016 08:39:23 GMT
Connection: keep-alive

{"govcd":"13101","zipcd_old":"100  ","zipcd":"1000000","pref_kana":"トウキョウト","city_kana":"チヨダク","town_kana":"イカニケイサイガナイバアイ","pref":"東京都","city":"千代田区","town":"以下に掲載がない場合","flg_n1":0,"flg_kaza":0,"flg_chome":0,"flg_1n":0,"upd_stat":0,"upd_reason":0}

$ sudo curl -X GET -i http://127.0.0.1/?z=2000000
HTTP/1.1 404 Not Found
X-Powered-By: Express
Date: Mon, 17 Oct 2016 08:39:43 GMT
Connection: keep-alive
Content-Length: 0

参考

http://d.hatena.ne.jp/u87/20100324/1269418824
http://qiita.com/kaikusakari/items/f2812ea1e9ea6949f214
http://qiita.com/kaikusakari/items/a64c447167efdcf068c2
https://github.com/mapbox/node-sqlite3/wiki/API