Created
November 14, 2012 14:30
-
-
Save yatt/4072417 to your computer and use it in GitHub Desktop.
駅データ.jpのデータをsqlite3に取り込むシェルスクリプト
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#! /bin/sh | |
# | |
# データベース作成スクリプト. | |
# 引数に渡されたパスにデータベースを構成・データを投入する | |
# | |
# | |
# sample: | |
# sh createDatabaseAs.sh db.sqlite3 | |
# | |
DST=$1 | |
SRC=. | |
# inject ddl | |
sqlite3 $DST < $SRC/ddl.sql | |
# skip header | |
sed -e '1d' $SRC/company20121010.csv > $SRC/$$.csv | |
# import csv | |
sqlite3 -separator , $DST ".import $SRC/$$.csv company" | |
sed -e '1d' $SRC/line20121030free.csv > $SRC/$$.csv | |
sqlite3 -separator , $DST ".import $SRC/$$.csv line" | |
sed -e '1d' $SRC/join20121010.csv > $SRC/$$.csv | |
sqlite3 -separator , $DST ".import $SRC/$$.csv connection" | |
sed -e '1d' $SRC/station20121102free.csv > $SRC/$$.csv | |
sqlite3 -separator , $DST ".import $SRC/$$.csv station" | |
# remove temporary file | |
rm $SRC/$$.csv |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE STATION ( | |
station_cd INTEGER PRIMARY KEY | |
, station_g_cd INTEGER | |
, station_name TEXT | |
, station_name_k TEXT | |
, station_name_r TEXT | |
, line_cd INTEGER | |
, pref_cd INTEGER | |
, post TEXT | |
--, add TEXT | |
, addr TEXT | |
, lon REAL | |
, lat REAL | |
, open_ymd DATE | |
, close_ymd DATE | |
, e_status INTEGER | |
, e_sort INTEGER); | |
--CREATE TABLE JOIN ( | |
CREATE TABLE CONNECTION ( | |
line_cd INTEGER | |
, station_cd1 INTEGER | |
, station_cd2 INTEGER | |
, PRIMARY KEY(line_cd, station_cd1, station_cd2)); | |
CREATE TABLE LINE ( | |
line_cd INTEGER PRIMARY KEY | |
, company_cd INTEGER | |
, line_name TEXT | |
, line_name_k TEXT | |
, line_name_h TEXT | |
, line_color_c TEXT | |
, line_color_t TEXT | |
, line_type INTEGER | |
, lon REAL | |
, lat REAL | |
, zoom INTEGER | |
, e_status INTEGER | |
, e_sort INTEGER); | |
CREATE TABLE COMPANY ( | |
company_cd INTEGER PRIMARY KEY | |
, rr_cd INTEGER | |
, company_name TEXT | |
, company_name_k TEXT | |
, company_name_h TEXT | |
, company_name_r TEXT | |
, company_type INTEGER | |
, e_status INTEGER | |
, e_sort INTEGER); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment