Created
December 3, 2012 09:22
-
-
Save k-holy/4193821 to your computer and use it in GitHub Desktop.
Volcanus\Csv\Reader
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
| <?php | |
| $app = new Application(); | |
| $app['pdo'] = $app->share(function(Application $app) { | |
| $db = new \PDO(sprintf('sqlite:%s', __DIR__ . '/../test.sqlite')); | |
| $db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); | |
| return $db; | |
| }); |
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 postals | |
| ( | |
| id INTEGER NOT NULL PRIMARY KEY, | |
| code CHAR(7) NOT NULL, -- (2) 郵便番号(7桁) 半角数字 | |
| prefecture VARCHAR(4) NOT NULL, -- (6) 都道府県名 漢字 | |
| prefecture_ruby VARCHAR(255) NOT NULL, -- (3) 都道府県名 半角カタカナ | |
| city VARCHAR(255) NOT NULL, -- (7) 市区町村名 漢字 | |
| city_ruby VARCHAR(255) NOT NULL, -- (4) 市区町村名 半角カタカナ | |
| town VARCHAR(255) NOT NULL, -- (8) 町域名 漢字 | |
| town_ruby VARCHAR(255) NOT NULL, -- (5) 町域名 半角カタカナ | |
| flag1 INTEGER NOT NULL, -- (9) 一町域が二以上の郵便番号で表される場合の表示 | |
| flag2 INTEGER NOT NULL, -- (10) 小字毎に番地が起番されている町域の表示 | |
| flag3 INTEGER NOT NULL, -- (11) 丁目を有する町域の場合の表示 | |
| flag4 INTEGER NOT NULL -- (12) 一つの郵便番号で二以上の町域を表す場合の表示 | |
| ); |
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
| <?php | |
| $app->get('/postals/import', function(Application $app, Request $request) { | |
| $file = new \SplFileObject(__DIR__ . '/../x-ken-all.csv', 'r'); | |
| $reader = new \Volcanus\Csv\Reader(array( | |
| 'inputEncoding' => 'SJIS-win', | |
| 'outputEncoding' => 'UTF-8', | |
| )); | |
| $db = $app['pdo']; | |
| $reader->appendFilter(function($fields) use ($db) { | |
| $sql = <<< SQL | |
| INSERT INTO | |
| postals | |
| ( | |
| code | |
| ,prefecture | |
| ,prefecture_ruby | |
| ,city | |
| ,city_ruby | |
| ,town | |
| ,town_ruby | |
| ,flag1 | |
| ,flag2 | |
| ,flag3 | |
| ,flag4 | |
| ) VALUES ( | |
| :code | |
| ,:prefecture | |
| ,:prefecture_ruby | |
| ,:city | |
| ,:city_ruby | |
| ,:town | |
| ,:town_ruby | |
| ,:flag1 | |
| ,:flag2 | |
| ,:flag3 | |
| ,:flag4 | |
| ); | |
| SQL; | |
| $statement = $db->prepare($sql); | |
| $parameters = array(); | |
| $parameters[':code' ] = $fields[2]; | |
| $parameters[':prefecture' ] = $fields[6]; | |
| $parameters[':prefecture_ruby'] = mb_convert_kana($fields[3], 'KV', 'UTF-8'); | |
| $parameters[':city' ] = $fields[7]; | |
| $parameters[':city_ruby' ] = mb_convert_kana($fields[4], 'KV', 'UTF-8'); | |
| $parameters[':town' ] = $fields[8]; | |
| $parameters[':town_ruby' ] = mb_convert_kana($fields[5], 'KV', 'UTF-8'); | |
| $parameters[':flag1' ] = intval($fields[9]); | |
| $parameters[':flag2' ] = intval($fields[10]); | |
| $parameters[':flag3' ] = intval($fields[11]); | |
| $parameters[':flag4' ] = intval($fields[12]); | |
| $statement->execute($parameters); | |
| }); | |
| $reader->file = $file; | |
| $time = 0; | |
| $db->beginTransaction(); | |
| try { | |
| set_time_limit(0); | |
| $started_at = microtime(true); | |
| $results = $reader->fetchAll(); | |
| $db->commit(); | |
| $finished_at = microtime(true); | |
| $time = $finished_at - $started_at; | |
| } catch (\Exception $e) { | |
| $db->rollback(); | |
| throw $e; | |
| } | |
| $content = <<< HTML | |
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <meta charset="utf-8" /> | |
| </head> | |
| <body> | |
| <h1>郵便番号情報を %d 件取り込みました。(%d 秒)</h1> | |
| </body> | |
| </html> | |
| HTML; | |
| return new Response(sprintf($content, $reader->fetched, $time)); | |
| }); | |
| $app->run(); |
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
| <?php | |
| $app->get('/postals/prepare', function(Application $app, Request $request) { | |
| $db = $app['pdo']; | |
| $db->exec('DROP TABLE IF EXISTS postals;'); | |
| $query = <<< SQL | |
| CREATE TABLE postals | |
| ( | |
| id INTEGER NOT NULL PRIMARY KEY, | |
| code CHAR(7) NOT NULL, | |
| prefecture VARCHAR(4) NOT NULL, | |
| prefecture_ruby VARCHAR(255) NOT NULL, | |
| city VARCHAR(255) NOT NULL, | |
| city_ruby VARCHAR(255) NOT NULL, | |
| town VARCHAR(255) NOT NULL, | |
| town_ruby VARCHAR(255) NOT NULL, | |
| flag1 INTEGER NOT NULL, | |
| flag2 INTEGER NOT NULL, | |
| flag3 INTEGER NOT NULL, | |
| flag4 INTEGER NOT NULL | |
| ); | |
| SQL; | |
| $db->exec($query); | |
| $db->exec('CREATE INDEX idx_postals_code ON postals(code);'); | |
| $content = <<< HTML | |
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <meta charset="utf-8" /> | |
| </head> | |
| <body> | |
| <h1>郵便番号テーブルを作成しました</h1> | |
| <ul> | |
| <li><a href="/postals/import">郵便番号テーブル取り込み</a></li> | |
| </ul> | |
| </body> | |
| </html> | |
| HTML; | |
| return new Response($content); | |
| }); | |
| $app->run(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment