Skip to content

Instantly share code, notes, and snippets.

@k-holy

k-holy/composer.json

Created December 4, 2012 08:44
Show Gist options
  • Save k-holy/4201903 to your computer and use it in GitHub Desktop.
Save k-holy/4201903 to your computer and use it in GitHub Desktop.
Download ZIP
Silex + Volcanus_Csv + PDO_SQLITE で KEN_ALL.CSV の中身を調査
Raw
composer.json
{
"require": {
"silex/silex": "1.0.*",
"volcanus/csv": "dev-master"
},
"minimum-stability": "dev"
}
Raw
ken_all.examples.php
<?php
// PDO_SQLiteのユーザー定義関数を使って、郵便番号データ（KEN_ALL.CSV）を調査
$app->get('/ken_all/examples', function(Application $app, Request $request) {
$db = $app['pdo'];
$statements = array();
$statements['町域名カナの終端が「ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ」'] = $db->query(
"SELECT id, code7, prefecture, city, town, town_ruby, flag1, flag2, flag3, flag4 FROM ken_all WHERE town_ruby REGEXP 'ｲｶﾆｹｲｻｲｶﾞﾅｲﾊﾞｱｲ$';",
\PDO::FETCH_ASSOC
); // 1875件
$statements['町域名カナの終端が「ﾂｷﾞﾆﾊﾞﾝﾁｶﾞｸﾙﾊﾞｱｲ」'] = $db->query(
"SELECT id, code7, prefecture, city, town, town_ruby, flag1, flag2, flag3, flag4 FROM ken_all WHERE town_ruby REGEXP 'ﾂｷﾞﾆﾊﾞﾝﾁｶﾞｸﾙﾊﾞｱｲ$';",
\PDO::FETCH_ASSOC
); // 17件
$statements['町域名カナの終端が「ｲﾁｴﾝ」、ただし 5220317 滋賀県犬上郡多賀町一円 は除く'] = $db->query(
"SELECT id, code7, prefecture, city, town, town_ruby, flag1, flag2, flag3, flag4 FROM ken_all WHERE code7 <> '5220317' AND town_ruby REGEXP 'ｲﾁｴﾝ$';",
\PDO::FETCH_ASSOC
); // 22件
$statements['郵便番号が同じかつ町域名が異なり、マージが必要'] = $db->query(
"SELECT id, code7, prefecture, city, town, town_ruby, flag1, flag2, flag3, flag4 FROM ken_all WHERE code7 IN (SELECT code7 FROM ken_all GROUP BY code7 HAVING COUNT(code7) <> 1) AND flag4 = 0;",
\PDO::FETCH_ASSOC
); // 550件
$statements['町域名カナに半角カナ以外の文字がある'] = $db->query(
"SELECT id, code7, prefecture, city, town, town_ruby, flag1, flag2, flag3, flag4 FROM ken_all WHERE NOT preg_match('/\A(\xef(\xbd[\xa6-\xbf]|\xbe[\x80-\x9f]))+\z/', town_ruby);",
\PDO::FETCH_ASSOC
); // 8373件
return new StreamedResponse(
function() use ($app, $statements) {
$start = <<< HTML
<!DOCTYPE html>
<html>
<head>
<title>郵便番号データの様々な例</title>
<meta charset="utf-8" />
<style type="text/css">
body {font-family:monospace;}
table {border-collapse:collapse; width:90%;}
caption {font-size:200%;}
th,td {padding:2px; border:solid 1px #999999;}
</style>
</head>
<body>
HTML;
echo $start;
foreach ($statements as $name => $statement) {
echo "<table>\n";
echo sprintf(" <caption>%s</caption>\n", $app->escape($name));
foreach ($statement as $index => $item) {
echo " <tr>\n";
echo sprintf(" <td>%d</td>\n", $index + 1);
array_walk($item, function($val) use ($app) {
echo sprintf(" <td>%s</td>\n", $app->escape($val));
});
echo " </tr>\n";
}
echo "</table>\n";
}
$end = <<< HTML
</body>
</html>
HTML;
echo $end;
}, 200
);
});
Raw
ken_all.import.php
<?php
// ken_allテーブルにKEN_ALL.CSVをインポート
$app->get('/ken_all/import', function(Application $app, Request $request) {
$file = new \SplFileObject(realpath(__DIR__ . '/../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
ken_all
(
region_code
,code5
,code7
,prefecture_ruby
,city_ruby
,town_ruby
,prefecture
,city
,town
,flag1
,flag2
,flag3
,flag4
,flag5
,flag6
) VALUES (
:region_code
,:code5
,:code7
,:prefecture_ruby
,:city_ruby
,:town_ruby
,:prefecture
,:city
,:town
,:flag1
,:flag2
,:flag3
,:flag4
,:flag5
,:flag6
);
SQL;
$statement = $db->prepare($sql);
$parameters = array();
$parameters[':region_code' ] = $fields[0];
$parameters[':code5' ] = $fields[1];
$parameters[':code7' ] = $fields[2];
$parameters[':prefecture_ruby'] = $fields[3];
$parameters[':city_ruby' ] = $fields[4];
$parameters[':town_ruby' ] = $fields[5];
$parameters[':prefecture' ] = $fields[6];
$parameters[':city' ] = $fields[7];
$parameters[':town' ] = $fields[8];
$parameters[':flag1' ] = intval($fields[9]);
$parameters[':flag2' ] = intval($fields[10]);
$parameters[':flag3' ] = intval($fields[11]);
$parameters[':flag4' ] = intval($fields[12]);
$parameters[':flag5' ] = intval($fields[13]);
$parameters[':flag6' ] = intval($fields[14]);
$statement->execute($parameters);
});
$reader->file = $file;
$time = 0;
$db->beginTransaction();
try {
set_time_limit(0);
$started_at = microtime(true);
$results = $reader->fetchAll();
$finished_at = microtime(true);
$time = $finished_at - $started_at;
$db->commit();
} 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));
});
Raw
ken_all.prepare.php
<?php
// ken_allテーブル作成
$app->get('/ken_all/prepare', function(Application $app, Request $request) {
$db = $app['pdo'];
$db->exec('DROP TABLE IF EXISTS ken_all;');
$query = <<< SQL
CREATE TABLE ken_all
(
id INTEGER NOT NULL PRIMARY KEY,
region_code VARCHAR(6) NOT NULL,
code5 CHAR(5) NOT NULL,
code7 CHAR(7) NOT NULL,
prefecture_ruby VARCHAR(255) NOT NULL,
city_ruby VARCHAR(255) NOT NULL,
town_ruby VARCHAR(255) NOT NULL,
prefecture VARCHAR(4) NOT NULL,
city VARCHAR(255) NOT NULL,
town VARCHAR(255) NOT NULL,
flag1 INTEGER NOT NULL,
flag2 INTEGER NOT NULL,
flag3 INTEGER NOT NULL,
flag4 INTEGER NOT NULL,
flag5 INTEGER NOT NULL,
flag6 INTEGER NOT NULL
);
SQL;
$db->exec($query);
$db->exec('CREATE INDEX idx_ken_all_code7 ON ken_all(code7);');
$content = <<< HTML
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
</head>
<body>
<h1>郵便番号テーブルを作成しました</h1>
<ul>
<li><a href="/ken_all/import">郵便番号テーブル取り込み</a></li>
</ul>
</body>
</html>
HTML;
return new Response($content);
});
Raw
pdo.php
<?php
$app['pdo'] = $app->share(function(Application $app) {
$db = new \PDO(sprintf('sqlite:%s', realpath(__DIR__ . '/../test.sqlite')));
$db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
// ユーザー定義関数 regexp を登録
$db->sqliteCreateFunction('regexp', function($pattern, $subject) {
mb_regex_encoding('UTF-8');
return (false !== mb_ereg($pattern, $subject)) ? 1 : 0;
});
// ユーザー定義関数 preg_match を登録
$db->sqliteCreateFunction('preg_match', function($pattern, $subject) {
return (preg_match($pattern, $subject)) ? 1 : 0;
});
return $db;
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment