MySQLへの接続の暗号化

基本
MySQLでSSLクライアント証明書を使う - Qiita

※MySQL側のCommon Nameはサーバのホスト名にする


暗号化されていることを確認するSQL

show status like'Ssl_cipher';
MySQL5.6での注意点
MySQL 5.6でSSL接続が有効にならない・クライアントからSSL接続できない。 - ah-2


WindowsのMySQL Query Browserでは、caだけでなく、下記の全てを指定しないと接続できないようだ。

USE_SSL => "Yes"
SSL_CA => ca.crtのファイルパス
SSL_CERT => client.crtのファイルパス
SSL_KEY => client.keyのファイルパス
ファイルパスのバックスラッシュ（￥）はバックスラッシュのままでも、スラッシュにしてもどちらでもOK。
ファイルパスのスペースが含まれていてもOK。
ca.crtは、server.crtとclient.crtの中身を合わせる必要はないようだ。

参考：MySQL :: MySQL Administrator :: 21.4.1 SSL Connections


REQUIRE SSLなユーザーだと、MySQL Query Browserの上部メニューからのMySQL Administratorの起動や、逆にMySQL AdministratorからMySQL Query Browserの起動ができない。（直接起動すれば起動できる。）

PDOでテーブルの列名を全て取得する方法

テーブルの列名を取得するには、MySQLならDESCRIBEで簡単にできるが、PostgreSQLだとpg_attributeカタログから取得しないといけないらしい。

でもPDOを使えばMySQLでもPostgreSQLでも取得できた。

$pdo = new PDO("$driver:dbname=$db", $user, $password);
$pdoStatement = $pdo->query("SELECT * FROM $table_name LIMIT 0");

$columns = array();

for ($i = 0; $i < $pdoStatement->columnCount(); $i++) {
    $meta = $pdoStatement->getColumnMeta($i);
    $columns[] = $meta['name'];
}

var_dump($columns);

もしテーブルに1行以上データがあることが保証されているなら普通に1行SELECTして連想配列の列名見れば済む話だが、データなしでも動くようにしたらこうなった。


ただし、PDOStatement->getColumnMeta()のマニュアルを見ると、

この関数は、 実験的 なものです。この関数の動作・ 名前・その他ドキュメントに書かれている事項は、予告なく、将来的な PHP のリリースにおいて変更される可能性があります。 この関数は自己責任で使用してください。

PDO ドライバの全てが PDOStatement::getColumnMeta(). をサポートしているわけではありません。

と書いてあり、PHPやDBのバージョンによっては上手く動かないかもしれないが、とりあえず手元の環境では動いた。


これでPHPでO/R Mapperを作りたい人も安心だ。

Seasar2入門の参考サイトとプロダクト選択のメモ

久しぶりにJava村に帰ってきた浦島さんの玉手箱メモ。


まずは概要を把握。

• Seasar2 入門
• Seasar2によるDI、AOP、その他についての分かりやすいシンプルな解説
• Java初心者が超俊敏にWebアプリを作る方法
• Seasar2のEclipseプラグインであるDoltengとSAStruts、S2JDBCについての解説
• Seasar2 - Seasar2
• 本家
• 初心者には見辛い

一口にSeasar2と言ってもたくさんプロダクトがあるので、Webアプリを作る場合にどのプロダクトを使うべきか調査。

• MVCは、S2Struts or SAStruts or Teedaのどれがいい？
• S2StrutsかSAStrutsだとどっち？
• SAStrutsかS2Strutsか - ひがやすを blog
  

  既にS2Strutsで開発している案件の追加開発ならS2Struts。
  JDK1.4をつかうならS2Struts。
  それ以外は、SAStruts。

• SAStrutsとTeedaでは？
• Teeda
• TeedaはJSFの実装
• Teeda Extensionを使えばHTMLをテンプレートにできる
• 「Seasarの問題点など」にそろそろ一言いっておくか - ひがやすを blog
  

  ただ、無責任に何でも自由というのは良くないので、SAStrutsとS2JDBCの組み合わせが、もっとも無難だというメッセージを出しているわけです。

• SAStrutsとS2JDBCを作ったわけ - ひがやすを blog
  

  TeedaとS2Daoは、基本的に規約ベースのフレームワークです。規約を知っていれば、本当にさくさく開発できます。でも、規約ベースなので、規約を知らないと、何にもできなくなっちゃう。この辺が、大規模開発には向かないところ。

  SAStrutsとS2JDBCは、大規模案件にも耐えられるように最初から設計されています。

• 感想：チームでの開発や今後のメンテを考えると、参考情報も経験者も多いStrutsの名前を冠するSAStrutsに比べてTeedaは学習コストがかかりそう
• 結論：SAStrutsが無難
• S2JDBC or S2Daoのどっちを使う？
• HibernateとS2DaoとS2JDBCの考え方 - ひがやすを blogより引用
• S2Daoについて
  

  S2DaoはSQLを中心に考えます。とはいえ、すべてのSQLを開発者が書くのは効率が悪いので、挿入、更新、削除は、S2DaoがSQLを自動生成しますが、検索は、開発者にSQLを書いてもらいます。

  デメリットは、SQLを書くのが面倒なことです。また、検索の結果セットごとにDTOを作らなければいけないので、DTOが増える傾向があります。

  Webのフレームワークとの組みあわせでいくと、Teeda Extensionは、S2Dao(DTO)と相性が良いように設計されているので、Teedaを使う場合は、S2Daoが良いと思います。

• S2JDBCについて
  

  S2JDBCは、エンティティ(Java)とテーブル(データベース)は、同一のモデルだとみなしています。また、複雑なSQL以外は、すべて自動生成します。

  デメリットは、エンティティの設計が、テーブルに引きずられ、完全にドメインをあらわしたものにならないこと。

• 感想
• S2DaoはINSERT/UPDATE/DELETEのSQL自動生成と、手作りしたSQLの実行をしてくれるみたい
• S2JDBCはDAOではなくApache CommonsのDbUtilsのようなSQLビルダー？
• 結論：TeedaでないならS2JDBCで

久しぶりにJavaの世界に帰ってきたら、昔使ってたStruts1.3（1.x系は1.3で止まってるとは意外）のラッパーであるSAStrutsと、同じく昔使ってたJakarta Commons（現在は昇格してApache Commons）のDbUtilsに似たS2JDBCが最前線だなんて、奇遇と言うか、あるいは必然なのかな。

PostgreSQLで 最後にVACUUM / ANALYZEした日時を取得する SQL

PostgreSQLで、最後にVACUUM/ANALYZEした日時（AUTOと手動のそれぞれについて）を確認するSQL。

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE schemaname = 'public' -- schemaname or relnameで絞りこむと見やすい
ORDER BY relname

参考：

• Studio ODIN - blog風小ネタ集 > PostgreSQL の VACUUM の動きを確認する
• 稼動統計情報を活用しよう(2) — Let's Postgres
  

PostgreSQLで日付や日時の差を計算する

PostgreSQLでは単純に引き算すれば日付間の差を取得できる。

SELECT '2011-12-31 23:59:59' - TIMESTAMP '2011-01-01 00:00:00' -- => "364 days 23:59:59"

結果はInterval型になる。

もちろん普通の日付型や日時型の列でもOK。

SELECT updated_at - created_at

条件として使う

条件として判定したい場合は下記のように文字列と比較できる。

SELECT '2011-12-31 23:59:59' - TIMESTAMP '2011-01-01 00:00:00' > '300 days' -- => TRUE
SELECT '2011-01-31 23:59:59' - TIMESTAMP '2011-01-01 00:00:00' > '300 days' -- => FALSE
SELECT '2012-12-31 23:59:59' - TIMESTAMP '2011-01-01 00:00:00' > '1 year' -- => TRUE

左辺を考慮して'300 days'のような文字列はInterval型にCASTして比較してくれる。
もちろWHERE句でも使える。



注意点

DATE型とDATE型の引き算の場合は結果がInterval型ではなく、日数を表すInteger型になる。

SELECT '2011-12-31' - DATE '2011-01-01' -- => 364

これを条件として判定したい場合は、'300 days'のような文字列（あるいはInterval型）ではなく、素直に数値で判定するか、あるいはTIMESTAMP型にCASTにする。

-- 数値で判定
SELECT '2011-12-31' - DATE '2011-01-01' > 300

-- TIMESTAMP型にCAST
SELECT '2011-12-31' - TIMESTAMP '2011-01-01' > '300 days'

参考:日付/時刻関数と演算子 （PostgreSQLマニュアル）

SQLの集計関数で NULLを含む列を集計する場合の注意点

MySQLとPostgreSQLの話。他のRDBMSは試してない。


SQLでNULLを含む列を集計する場合、NULLの行は無視される。

SELECT
  SUM(t.v) -- => 15
, MIN(t.v) -- => 0
, MAX(t.v) -- => 10
, COUNT(t.v) -- => 3 (NULLの行も入れれば4行)
, AVG(t.v) -- => 5.0000 (NULLの行をゼロと考えれば3.75)
FROM (
  SELECT NULL v
  UNION
  SELECT 0 v
  UNION
  SELECT 5 v
  UNION
  SELECT 10 v
) t

（PostgreSQL 8.4とMySQL 5.1で確認。）


SUM()やMIN()、MAX()では問題ないが、COUNT()やAVG()ではNULLの行の分も含めて計算したい場合に困る。

そういう場合、COALESCE()でNULLをゼロに変換するとよい。

SELECT
  COUNT(COALESCE(t.v, 0)) -- => 4
, AVG(COALESCE(t.v, 0)) -- => 3.7500
FROM (
  SELECT NULL v
  UNION
  SELECT 0 v
  UNION
  SELECT 5 v
  UNION
  SELECT 10 v
) t

MySQLの場合はIFNULL()でもOK。


参考
PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Conditional Expressions
MySQL :: MySQL 5.5 Reference Manual :: 11.2.3 Comparison Functions and Operators

Webブラウザで DBを参照・更新・管理できるツールいろいろ

クラウドの時代ですから。

個人的にお薦めなのはAdminer。一般受けしそうなのはSQL Buddy。


MySQL用（上の方が使いやすい）

• Adminer
• PHP1ファイルのみ（無理に1ファイルにすることないと思うけど）。設置すれば設定不要ですぐ使える
• シンプルだが普段使う機能は一通り揃ってる。シンプルだからこそ使いやすい
• SQLをガリガリ書きたい人にお薦め
  
• データのclone（コピー）ができるのが何気に便利
  
• 見た目は少々無骨。CSSファイルをダウンロードすればSkinを変えられる
• 昔はphpMinAdminという名前だったが改名した
• 通常版より"English only"の方が少しファイルサイズが小さい。そんなに変わらないか
• ライセンスはApache License
• Adminer Editor
• Adminerの簡易版。データの参照・登録・更新・削除だけしかできない
• 別途Extensionsを用意しないと動かないようだ
• データをいじるだけならこれでいいかも。逆に、データをいじる以上のこと（テーブルの変更とか、DBのDROPとか）をさせたくないユーザー向けに渡すのにちょうどいい（それを意識して作ったんだと思う）
  
• SQL Buddy
• 見た目は一番きれい。スッキリしている。Ajaxでサクサク動く
  
• 日本語にも対応している
• パッケージ更新のお知らせ機能があるみたい
• 複数の列を一気に編集（更新）できる（スプレッドシート形式ではないが）
  
• データを登録する際にDatetime型の列に現在日時を自動でセットしてくれない（下記のAdminerやMySQL Quick Adminはやってくれる）
  
• ライセンスはMITライセンス
  
• 参考
• ブラウザから快適に使えるMySQL管理ツール『SQL Buddy』を試してみた - IDEA*IDEA ～ 百式管理人のライフハックブログ
• MOONGIFT: » WebベースのMySQL管理インタフェースの新参「SQL Buddy」:オープンソースを毎日紹介
  
• phpMyAdmin
• 定番だが、使いづらい。いろいろできるようにした結果、あまり使わない機能の中によく使う機能が埋もれてしまっている
• yumでもインストールできる
• ライセンスはGPL v2
  
• 参考：floatingdays: CentOSに phpMyAdminをインストール
  
• MySQL Quick Admin
• 見た目はわりと良い。Ajaxでサクサク動く
• 最初にconfig.phpを編集する必要があるみたい（ログイン機能がうまく動かなかったので）
  
• SELECTのSQLを自分で書けない？実用するには辛いかも
  
• 最後のUpdateは2007年6月。PHP5.3で動かすとDeprecatedが出る
• ライセンスは明記されていないが、フォーラムにこんなやり取りが書いてあった
  
• PHP Mini Admin for MySQL
• PHP1ファイルのみ
• Screenshotsを見る限りAdminerの方が良さそうなので、試していない
  
• 参 考：MySQLのウェブ管理ツール比較 - ♪8th Note♪
• Webadmin-mysql
• あまり使われていない？

PostgreSQL用

• phpPgAdmin
• 名前はphpMyAdminと似てるが、UIは洗練されていて使いやすそう
• ライセンスはGPL
  
• 参考：CentOSに phpPgAdminをインストール
  

これらのツールのようにPHPでつくっちゃえば、DB側がバージョンアップしてもphp-pdo/php-mysqlが対応してくれるので、ツール側での対応は特にいらないというのはクライアントアプリのツールには無い利点だと気付いた。

PostGISで使う SRID

自分に必要なのはこれだけかな。

select * from spatial_ref_sys where srid in (4301, 4326)

結果を要約すると、

4301 → Tokyo （いわゆる日本測地系。日本の地図でけっこう使われている）
4326 → WGS 84 （いわゆる世界測地系。Google Maps等で使われている）

参考：Bubble://ちずろぐ/別巻/ SRIDList

MySQLの FULLTEXTインデックスと MATCH AGAINSTによる検索

FLLTEXTインデックスについて

• MyISAMのみ
  
• 設定ファイルで検索対象の最低文字数を設定する（デフォルトは4）
  

  [mysqld]
  ft_min_word_len=1
  

• TEXT型の列などにスペース区切り等で区切ったデータを入れ、FULLTEXTインデックスを付与する
• 該当データが全体の50%を超えるような単語は検索対象から除外されるので注意
• 日本語でもOK
  

FULLTEXTインデックスを付与された列は、MATCH AGAINSTによる検索ができる。

SELECT * FROM members
WHERE MATCH keywords AGAINST('メロン');

さらに、マッチはしないが関連しそうなデータも含める「クエリ拡張」というオプションもある。

SELECT * FROM members
WHERE MATCH keywords AGAINST('ソーダ' WITH QUERY EXPANSION);

「注記 :ブラインド クエリ拡張は関連性のない雑多な資料も戻しがちなため、検索フレーズが短い時にだけ使用することをお薦めします」そうだが。


参考：
　MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.7 全文検索関数
　MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.7.2 クエリ拡張を伴う全文検索
　[ヅラド] MySQL を使った全文検索 (Full-Text Search)
　MySQL FULLTEXT + Ngram : LIKE検索より数十倍高速な、お手軽 日本語全文検索 について｜blog｜たたみラボ

MySQLで INSERT時に重複する KEYが既に存在する場合の動作のオプション

INSERT IGNORE INTO ...
（効能）重複するKEYが既に存在する場合、データは登録されない

INSERT IGNORE INTO members(id, name) VALUES(2, 'TARO');

INSERT INTO ... ON DUPLICATE KEY UPDATE ...
（効能）重複するKEYが既に存在する場合、そのデータをUPDATEする

INSERT INTO members(id, name) VALUES(2, 'TARO') ON DUPLICATE KEY UPDATE cnt = cnt + 1;

REPLACE INTO ...
（効能）重複するKEYが既に存在する場合、そのデータをDELETEしてからINSERTする（※UPDATEではない）

REPLACE INTO members(id, name) VALUES(2, 'TARO');

いずれも、重複するKEYが存在しなければ普通のINSERT文と同じ結果になる。



参考：MySQL :: MySQL 4.1 リファレンスマニュアル :: 6.4.3 INSERT 構文

MySQLの timestampの自動更新ができない場合の修復方法

例えばcreated_atにはINSERT文でvalueにシステム時刻をセットし、updated_atは自動更新させることを想定している場合。

下記のように、間違えてtimestamp型の列を複数定義すると、2つ目以降のtimestampは初期値がオールゼロになり、自動更新もされない。

DROP TABLE IF EXISTS `test`.`members`;
CREATE TABLE `test`.`members` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

間違いに気づいて、Query Browserからテーブル定義を変更（created_atをdatetimeにして、updated_atのdefaultにCURRENT_TIMESTAMPをセット）しても、自動更新はされない。
なぜなら、Query Browserからテーブル定義を変更してtimestampにした場合、下記のようにON UPDATEの定義が無いから。

DROP TABLE IF EXISTS `test`.`members`;
CREATE TABLE `test`.`members` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query Browserは、外部KEY以外のトリガーを作成できない。
なのでSQL文（DDL TDL）で列の定義を修正する。

ALTER TABLE members MODIFY updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

修正できた。

DROP TABLE IF EXISTS `test`.`members`;
CREATE TABLE `test`.`members` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

参考：MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.1.2 ALTER TABLE 構文

MySQLで Database （Schema）を作成するDDL

よく忘れるのでメモメモ。
文字コードがデフォルトでよい場合は指定不要。

CREATE DATABASE データベース名 CHARACTER SET utf8;