DBテーブルの差分を出力するMySQL::diffをインストールする

二つの MySQL データベースを比較し差分 ALTER SQL 文を出力してくれる perl 製ツール、MySQL::diffのインストール方法です。

このツールは、稼働中のデータベースのテーブルスキーマを最新に更新する場合など使えます。

Usage: mysqldiff [ options ] <database1> <database2>

今回はサーバデプロイ時のスキーマ更新作業を自動化するためにインストールしてみました。上手く使うと DDL のスマートなバージョン管理に一役買ってくれると思います。

コマンドラインツールなので GUI で分かりやすく差分を出力したい場合には不向きです。そういった場合はこのツールよりも MySQLWorkbench を使ったほうが便利でしょう。

MySQL::diff のインストール

MySQL::diff は mysqldiff – search.cpan.org で公開されています。

インストール先を /usr/local/mysqldiff にしました。

wget http://search.cpan.org/CPAN/authors/id/A/AS/ASPIERS/MySQL-Diff-0.43.tar.gz
tar xzvf MySQL-Diff-0.43.tar.gz
mv MySQL-Diff-0.43 /usr/local/mysqldiff

/usr/local/bin/mysqldiff にシンボリックリンクをはります。

ln -s /usr/local/mysqldiff/bin/mysqldiff /usr/local/bin/mysqldiff

これでインストール完了と言いたいところなのですが、このままだとパスが通っておらず以下のエラーが起こります。

Can't locate MySQL/Diff.pm in @INC (@INC contains: /usr/local/mysqldiff/bin /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at /usr/local/bin/mysqldiff line 112.
BEGIN failed--compilation aborted at /usr/local/bin/mysqldiff line 112.

このエラーを修正するために、ソースコードの冒頭に use lib 文を一行追記します。

vi /usr/local/mysqldiff/bin/mysqldiff
#!/usr/bin/perl -w
use lib '/usr/local/mysqldiff/lib';

=head1 NAME

また、CentOS の場合では CPAN で File::Slurp をインストールする必要がありました。

cpan
cpan > install File::Slurp

mysqldiff コマンドを空打ちしてみて、usage が表示されれば無事にインストール完了です。

Androidのアドレス帳を読み書きするContactsAPIとは何者か暴く

Android Contacts APIとは、どんなAPIか その可能性

AndroidSDKは端末内部のアドレス帳へアクセスするためのインターフェースとなるContactsAPIを用意しています。

端末内連絡先という重要なリソースへのアクセスを担うContactsAPIはアプリ開発の幅を広げるためには重要なAPIではないでしょうか。

ですが、このAPIの仕様は煩雑で癖が強いです。直感的に欲しいデータにアクセスできませんし、書き込みもできません。

根本的なContactsAPIの概念的な部分を分かっていないとちょっとイライラすると思います。

また、似ているアドレス情報の連絡先を勝手に集約してまとめるような仕組みがAndroidOSレベルで用意されていたりするため開発者の混乱を招く原因になっていたりします。

また、データにアクセスするためのキー項目の特徴を知っていないと思わぬことが起きたりします。

ネット上に散らばっているサンプルソースを取り敢えず動かしてみて「何かよく分からないけど欲しいデータが取れた!」「でもホントはこういうデータを取り出したいんだけどどう書けばいいか分からない」というケースが結構想定されるAPIなのではないかなーと思います。

ContactsAPIについての抑えるべきポイントと概要を説明していきます。

Androidではアドレス帳をどういった形態で保持しているか

Android内部ではSQLiteに連絡先データが保存されているようです。
ただ、そのSQLiteデータベースに直接アクセスすることはできません。危険すぎます。

そこでアクセスインターフェースとしてContactsAPIが用意されています。
ContactsAPIもかなりSQLite側を意識した作りになっているため、
アクセスの仕方自体はほぼ変わりはないです。

アドレス帳データのSQLiteのテーブル設計はどうなっているか

Android内部のDB設計が分かっていないばかりに以前苦労した記憶があります。

まさに「何かよく分からないけど欲しいデータが取れた」状態で使っていましたが、テーブル構成が分かった瞬間にかなり道がひらけました。

これをもっと早く知っていればよかった…と思いましたが、同じ思いをする人が一人でも減ってくれることを願いER図を用意しました。

AndroidContactsAPIのER図

※緑のフィールドは外部キーです。主要なフィールドのみ載せています。

SQLiteは全てのテーブルに_idという一意のサロゲートキーを必ず持ちますが、ContactsAPIにおいてもSQLiteシステムの_idを主キーとして利用しています。

全てのテーブルに_idがあるという点が一つのポイントとなります。

それではテーブルごとに概要を説明していきます。

dataテーブル

上記ER図中では最小単位となるテーブルで、行ごとに電話番号やEメールアドレスといった実データが保存されています。ER図の関係の通り一つのraw_contactsレコードに所属するデータが複数あり、データの数だけdataレコード沢山があります。

そのデータ種別を区別するためにMIMEタイプというフィールドが用意されています。種類についてはContactsContract.CommonDataKindsのリファレンスを参照してください。

また、実データはdata1, data2といったフィールドに格納されています。MIMEタイプごとにそれぞれのdataが何を表すかということが決められています。

例えば電話番号の場合はdata1に電話番号、data2に種別(自宅や勤務先)、data3にラベルが格納されています。MIMEタイプごとのdataフィールド群の対応については、先ほどのContactsContract.CommonDataKindsのリファレンスからたどって調べれば分かると思います。

raw_contactsテーブル

dataテーブルにある一人分の名前や電話番号のデータレコードを一つにまとめます。1レコードで一人分のデータを表現します。直訳で生のコンタクトテーブルになり、後述のコンタクトテーブルよりもより直接的なデータとなります。

アカウント名やアカウントタイプといったフィールドもありますが、これは外部のクラウドストレージサービスの情報になります。例えば自分のGoogleアカウントやDocomoアカウントと紐付けて保存されていたりします。

contactsテーブル

このテーブルがクセモノです。いくつかのraw_contactsテーブルのレコードを更にまとめます。

どういうことかというとAndroidには似ている連絡先情報を自動で集約する仕組みが備わっており、ある基準で似ている連絡先と判断された連絡先を一つにまとめるためにこのテーブルは用意されています。

つまり、いくつかの似ているrow_contactsレコードをひとまとめにしてcontactsレコードを持つことがあるということです。ただ、この集約は恒久的なものではなく自動的に集約が解除されたりもします。

何を持って似ているかという判断基準はOSでちゃんと定められています。例えば姓名がそれぞれ一致したとか、姓だけ一致したら更にうんにゃらとか。興味がある人は調べてみるとよいでしょう。

こういった仕組みが用意されている意味としては、raw_contactsテーブルでは複数のアカウントの情報を持つ関係上、クラウドの連絡先と同期をとった時に似たような同一人物のデータが複数端末内にできてしまうため可能な限りそれを一括りにまとめようというような目的のようです。

一般的にユーザーに連絡先データを表示する場合はcontactsテーブルのレベルで見せるべきです。そのためのビューも提供されています。

自動集約の弊害

普通に考えて一人を特定するためのキーとしてはcontactsテーブルの_id(contacts_id)があればよいこととなります、が、集約機能のための弊害があります。

自動集約でレコードがまとめられる時にcontacts_idが変わってしまうことがあります。なのでcontacts_idをユーザーを特定するための恒久的なキーとして利用すべきではありません。

では、恒久的に1ユーザーを特定するためにはどうするのか。そのためにルックアップキーというものが用意されています。ユーザーへの参照をアプリ内で持っておきたい場合はこのキーを保持しておくのがよいでしょう。ただ、このキーにもデメリットがありますので、次の項の中で解説します。

プログラムコードサンプル

実際にコードサンプルを紹介していきます。

アドレス帳内の全コンタクトにループ処理でアクセスする

ループで全ユーザーにアクセスします。上記ER図でいうところのcontactsテーブルへのアクセスになります。

Cursor c = contentResolver.query(Contacts.CONTENT_URI, new String[] {Contacts._ID, Contacts.LOOKUP_KEY, Contacts.DISPLAY_NAME },null, null, null);
c.moveToPosition(-1);
while (c.moveToNext()) {
    // カーソルからデータ取り出し
    int contactsId = c.getInt(0);
    String lookupKey = c.getString(1);
    String displayName = c.getString(2);
    // ダンプ
    Log.d("contacts", "contactsId" + contactsId);
    Log.d("lookupKey", "lookupKey" + lookupKey);
    Log.d("displayName", "displayName" + displayName);
}

ルックアップキーで特定のユーザーの表示名を取得するコード例

ルックアップキーは基本的に固定の検索キーで、ユーザーを特定するためのキーとしては最適ですが検索キーとしての高速性に欠けます。

以下がルックアップキーのみでUriを作成し名前を取得する例です。ループで大量に処理をしたりしないのであればこの方法でも問題ないでしょう。

Uri lookupUri = Uri.withAppendedPath(Contacts.CONTENT_LOOKUP_URI, lookupKey);

Cursor c = getContentResolver().query(lookupUri, new String[]{Contacts.DISPLAY_NAME}, ...);
try {
    c.moveToFirst();
    String displayName = c.getString(0);
} finally {
    c.close();
}

ルックアップキーとコンタクトIDを組み合わせたユーザー検索

contacts_idによる高速な検索とルックアップキーによる確実な検索の2つのメリットを合わせたユーザー検索方法が予め用意されています。

まずcontacts_idでの検索を試みて、失敗した場合はルックアップキーを利用した検索を行なってくれます。

大量にデータを処理する場合はこの方法でUriを作成すべきです。

Uri lookupUri = Contacts.getLookupUri(contactId, lookupKey);

Dataテーブルの実データの取得

contacts_idを元に電話番号を取得してみます。一人に対して複数の電話番号が設定されている場合があるのでカーソルループで取得します。

Dataテーブルには電話番号以外のデータも沢山入っているため、Where句でMIME TYPEで絞り込んでいます。

Cursor cursor = null;
try {
    cursor = contentResolver.query(Data.CONTENT_URI, null,
        Data.CONTACT_ID + " = ? AND " + Data.MIMETYPE + " = ?",
        new String[] { contactsId, Phone.CONTENT_ITEM_TYPE }, null);
    // 電話番号ループ
    cursor.moveToPosition(-1);
    while (cursor.moveToNext()) {
        String tel = cursor.getString(cursor.getColumnIndex(Data.DATA1));
        Log.d("tel", tel);
    }
} finally {
    cursor.close();
}

ContactsAPIまとめ

テーブル構成と自動集約に伴う罠はありますが、逆にいうとそこら辺さえ抑えればさほど難しくないと思います。

テーブル構成さえ分かっていれば上記で載せたサンプルコード以外にも色々と応用したアドレス帳アクセスもできるかと思います。

あと今回はサンプルコードを載せませんでしたが、プロフィール画像についてもDataテーブル内にBlobで直接格納されています。ちょっと探せばサンプルあるはずです。

それではよいContactsAPIライフを。

参考サイト

SQLiteでEXISTS / NOT EXISTS句を使う

MySQLやOracleなどのデータベースでおなじみのWHERE EXISTS句を
SQLiteで使えるかどうかを調べてみたら調べ方が悪いのか、
日本語の情報がほぼ無かったのでメモ。

SQL叩いて試してみたらEXISTS句使えました。
SQL文の例。

SELECT * FROM t1 WHERE EXISTS(SELECT * FROM t2 WHERE t1.id = t2.t1_id);
SELECT * FROM t1 WHERE NOT EXISTS(SELECT * FROM t2 WHERE t1.id = t2.t1_id);

これで片方のテーブルだけにある行などの抽出もできます。