まだプログラマーですが何か？

プログラマーネタ中心。たまに作成したウェブサービス関連の話も https://twitter.com/dotnsf

タグ：sql

以前に Linux 版 Db2 を導入した時の手順を紹介したブログがありました：
http://dotnsf.blog.jp/archives/2960284.html


３年以上前のこの時の仮想環境が奇跡的に残っていたので久しぶりに使ってみました。で、折角の Db2 コマンドライン環境（？）なので、このコマンドラインインターフェースから IBM Cloud 上の DBaaS である Db2 に接続して使ってみました。以下、その手順の紹介です。

まずは IBM Cloud にログインして Db2 インスタンスを作成します。無料のライトアカウントでも使えるサービスが用意されているので、派手に使わない限りは無料で試すこともできます：
2018052302


無料のライトプランを使う場合はデプロイ先ロケーションを「米国南部」に設定します。加えて自分のメールアドレスを指定します：
2018052303


デプロイ先ロケーションが米国南部の場合、無料の Lite プランが選択可能になります（もちろん他のプランでも構いません）。プラン設定後に「作成」ボタンでインスタンスが作成されます：
2018052304



インスタンス作成後、「サービス資格情報」メニューから「資格情報の表示」を選択します（「資格情報の表示」が存在しない場合は、最初に「新規資格情報」をクリックして作成し、その後に改めて「資格情報の表示」を選択します）。すると画面下部に JSON フォーマットで接続先サーバー名やユーザー、パスワードといった情報を確認できます：
2018052301


この中で、今回使うのは以下の５つの情報です：
　- hostname（接続先のサーバー名）
　- password（接続時のパスワード）
　- port（接続先のポート番号）
　- db（データベース名）
　- username（接続時のユーザー名）


以下では、これら５つの値がこのような内容になっていたと仮定して説明を続けます：
{
  "hostname": "dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net",
  "password": "xxxxxxxx",
  "port": 50000,
  "db": "BLUDB",
  "username": "lrz77612",
    :
}



ではこの DBaaS 上のデータベース（BLUDB）にコマンドラインから接続するための手順を紹介します。


db2inst1 ユーザーへの変更

ここから下のコマンドはすべて db2 インストール時に指定したユーザー（今回の例では db2inst1）に切り替えて行う必要があります。というわけでターミナル上でのユーザーを切り替えます：
$ su - db2inst1

ノードカタログの確認

接続先となる Db2 サーバーはサーバーノードとしてカタログに記録されている必要があります。というわけで、まず現在のノードカタログを確認します（青字は出力結果）：
$ db2 list node directory
SQL1037W  The node directory is empty.  SQLSTATE=01606

この出力結果によるとノードカタログは空で何も登録されていないようです。


ノードのカタログ登録

というわけで今回の接続先となる IBM Cloud 上の Db2 サーバーをカタログに登録します。以下のように接続先のサーバー名とポート番号を指定し、今回は "dashdb" という名前でカタログに登録しています（太字部分は各自の環境や希望に併せて変更してください）：
$ db2 catalog tcpip node dashdb remote dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net server 50000
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
成功したようです。この状態で再度ノードカタログを確認すると、今度は dashdb という名前のカタログが確認できるはずです：
$ db2 list node directory

 Node Directory

 Number of entries in the directory = 1

Node 1 entry:

 Node name                      = DASHDB
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net
 Service name                   = 50000


ノードカタログを削除

今回は作業しませんが、登録したカタログを削除する場合はカタログ名を指定して以下のように実行します：
$ db2 uncatalog node dashdb


エイリアスの確認

DB2 でデータベースに接続するにはデータベースをエイリアスとして登録する必要があります。というわけで次に接続先となるリモートデータベースのエイリアスを作成します。まずは現在登録されているエイリアスの一覧を確認します：
$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = JSONDB
 Database name                        = JSONDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = MYDB
 Database name                        = MYDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

この例では（いずれもローカルサーバー内の）JSONDB と MYDB という２つのデータベースエイリアスが登録されていました。この例ではリモートサーバーのエイリアスは登録されていません。


エイリアスの登録

では先程作成した dashdb ノード上の接続先データベース（BLUDB）を "remotedb" という名前でエイリアス登録します：
$ db2 catalog database BLUDB as remotedb at node dashdb
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

成功したようです。念の為もう１回エイリアス一覧を確認します：
$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 3

Database 1 entry:

 Database alias                       = REMOTEDB
 Database name                        = BLUDB
 Node name                            = DASHDB
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = JSONDB
 Database name                        = JSONDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 3 entry:

 Database alias                       = MYDB
 Database name                        = MYDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

エイリアスが２つから３つに増え、REMOTEDB が新たに登録されました。


エイリアスを削除

この作業も今回は不要ですが、登録したエイリアスを削除する場合はエイリアス名を指定して次のように実行します：
$ db2 uncatalog db remotedb


エイリアスを指定して接続

では改めてエイリアスを作ったデータベースに接続します。接続時にはユーザー名とパスワードが必要になるので、以下のように指定します：
$ db2 connect to remotedb user lrz77612 using xxxxxxxx

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.2.2
 SQL authorization ID   = LRZ77612
 Local database alias   = REMOTEDB

IBM Cloud 上のデータベースに接続できました。後はこのリモートデータベースに対して各種 SQL を実行してテーブルやデータの作成／更新／削除／検索といったコマンドが実行できます。


接続しているエイリアスから切断

なお、接続しているエイリアスから切断する場合は以下のコマンドを実行します：
$ db2 terminate




タグ ：
#ibm
#cli
#sql
#db2

自分が苦手なので、備忘録的に残しておきます。

まずやりたいことはこれです。RDB （今回は MySQL）のこんなテーブル(todos)があったとします。email の人の ToDo を管理するようなテーブルだと考えてください：
列名列型目的
idintプライマリキー、自動インクリメント
emailvarchar(50)メールアドレス
bodyvarchar(255)本文
createdlongレコードを作成した日時のタイムスタンプ


これだけであれば、以下の SQL でテーブルを定義するようなものだと思います：
create table todos(
  id int primary key auto_increment,
  email varchar(50),
  body varchar(255),
  created long
);

ここに運用上の条件を１つ加えます。１つの email に対しては最新の body のみを有効としたいのです。要するに同じ email に異なる body があっても（なくても）いいのですが、有効なものは最新の（created が最も大きな）body のみとして扱えるようにしたい、というものです。

例えばテーブルの中身がこのようになっているものとします（タイムスタンプの値がいいかげんですが、大小関係だけ分かればいいものとします）：
idemailbodycreated
1aaa@xxx.jpToDo その１10
2bbb@xxx.jpToDo その２20
3aaa@xxx.jpToDo その３30


全部で３件のレコードがありますが、email = aaa@xxx.jp のレコードが２件あります。この場合は created が最大である id=3 のレコードのみが有効と考えます（id=1 のレコードは削除されていても構いません）。

このような条件を付けた上で、「有効なレコードだけの一覧を取り出したい」のですが、どのようにデータベースを定義して運用すればいいでしょう？

まず email 列は実質的にユニークになるとも考えられます。が、テーブル定義時に UNIQUE 属性を付けてしまうと、INSERT 時にエラーとなってしまうので「まず同じ email のデータが存在しているかどうかを確認し、存在していなければ INSERT 、していれば UPDATE するような SQL を実行する」ことになります（こうすると実は UNIQUE 属性はあってもなくても同じと言えます）。INSERT 時の処理がちと面倒ですが、読み込み時にはシンプルに "select * from todos" だけで実現可能な方法といえます。

次に email はユニークとは考えずに、とにかくレコードは全て INSERT して、有効なレコードを取り出す時に工夫する、という方法もあります。これは SQL としては以下を実行すればよいことになります：
> select * from todos where ( email, created ) in (select email,max(created) as max_created from todos group by email);

これはこれで実現可能な方法の１つです。ただ上記の例とは逆に、INSERT は無条件に行いますが、読み込み時に比較的複雑な SQL が実行されることになります。また有効でない（使われることのない）レコードがテーブル内にずっと残ってしまうことになります。


ではこれらのいいとこ取りを考えてみます。つまり、以下のようなロジックです：
(1) INSERT は無条件に行い、
(2) INSERT 直後に有効でないレコードがあったら削除し、
(3) 検索はシンプルに "select * from todos" だけで行えるようにする


(1) の直後に (2) が出来れば、(1) も (3) もシンプルな処理になるので理想的とも言えます。で、前書きが長くなりましたが、今回はこの (2) を実現するのに苦労した話です（苦笑）。

まず、このケースでの「有効なレコード」を取り出す場合の SQL クエリーは上記のようにこのようになります：
> select * from todos where ( email, created ) in (select email,max(created) as max_created from todos group by email);

（結果）



idemailbodycreated


2bbb@xxx.jpToDo その２20


3aaa@xxx.jpToDo その３30

ということは、「有効でないレコード」（削除対象のレコード）はこの SQL で取り出せます：
> select * from todos where ( email, created ) not in (select email,max(created) as max_created from todos group by email);

（結果）



idemailbodycreated


1aaa@xxx.jpToDo その１10

ここまでできれば後は単純に削除対象のレコードを削除すればよいので、サブクエリーを使ってこんな SQL を実行するだけ・・・
> delete from todos where id in ( select id from todos where ( email, created ) not in (select email,max(created) as max_created from todos group by email) );

と思ったのですが、これを実行すると以下のようなエラーが発生します：
ERROR 1093 (HY000): You can't specify target table 'todos' for update in FROM clause

これがサブクエリーのややこしい（というか、自分がよく分かっていなかった）所です。サブクエリーを使った時に意識する構文がいくつか存在しています：
https://dev.mysql.com/doc/refman/5.6/ja/subquery-errors.html

↑これの「サブクエリー内の誤って使用されているテーブル」に書かれた項目が今回のエラーの原因でした。サブクエリーの FROM で指定されるテーブルと、更新系のテーブルに同じものを使用することができない、というルールです。

これを解決するにはサブクエリー内の FROM 部分（"todos" と書かれた部分）を更にサブクエリーにしてテンポラリテーブルにします。上記例だとこんな感じ：
> delete from todos where id in ( select id from (select * from todos) where ( email, created ) not in ( select email,max(created)
 as max_created from (select * from todos) group by email ) );

ただこれだと該当部分が２箇所あり、今度は複数のテンポラリテーブルは異なるエイリアスを持たなければならない、という制約に引っかかってエラーになってしまいます：
ERROR 1248 (42000): Every derived table must have its own alias

というわけで、最終的な答はこちら。テンポラリテーブルに異なるエイリアスを指定して完成です：
> delete from todos where id in ( select id from (select * from todos) as temp1 where ( email, created ) not in ( select email,max(created)
 as max_created from (select * from todos) as temp2 group by email ) );

こんな SQL 、何も見ずに一発でさらっと書ける人いるんかな・・・


タグ ：
#mysql
#sql
#subquery

WordPress のデータベースから SQL でデータを取り出すシリーズ（？）の応用編。今回のテーマは
　ポストした本文とタイトルと、その文書に付与されたカテゴリの一覧だけを取り出す
というものです。

最初に結論を書いておきます。この SQL で目的を達成できるはず（公開データと未公開データの両方を取り出しています）：
select wp_posts.post_content as content, wp_posts.post_title, wp_terms.name as category
 from wp_posts, wp_terms, wp_term_relationships
 where wp_posts.post_type = 'post' 
 and ( wp_posts.post_status = 'publish' or wp_posts.post_status = 'draft' )
 and wp_posts.ID = wp_term_relationships.object_id
 and wp_term_relationships.term_taxonomy_id = wp_terms.term_id;

説明する上で、WordPress のテーブルの相関関係を理解しておく必要があります。関連図についてはこちらを参照ください：
データベース構造 - WordPress Codex 日本語版


まず WordPress にポストしたレコードデータは wp_posts テーブル内に格納されています。特に（固定ページなどではなく）ポストデータは
　post_type = 'post'
となっているものが該当します。

そして今回は公開済みレコードと未公開（ドラフト）レコードの両方を取り出すことにしました。公開状況は同テーブルの post_status に格納されており、
　post_status = 'publish' or post_status = 'draft'
のいずれかの条件を満たしているものを取り出し、そこから本文（post_content）とタイトル（post_title）を取りだせばよい、ということになります。もしもドラフトが不要な場合はこの行の or 以降は不要です。

取り出すレコードの選別はこれだけです。次に各レコードに紐付けられたカテゴリ名称を取り出す必要があります。

カテゴリーのデータは wp_terms テーブルに（カテゴリ名は name 列に）含まれています。そしてどの文書がどのカテゴリに属しているのか、というリレーションは wp_term_relationships テーブルに格納されており、同テーブル内の object_id が wp_posts.ID 、term_taxonomy_id が wp_terms.term_id に該当しています。

例えば、wp_term_relationships テーブルに以下のようなレコードが存在していた場合、
object_idterm_taxonomy_id
41
61
82

wp_posts テーブルの ID が 4 の文書と 6 の文書は、wp_terms テーブルの term_id = 1 のカテゴリに属していて、ID が 8 の文書は term_id = 2 のカテゴリに属している、ということになります。

これらの関係を１つの SQL に書き直すと上記の SQL になります。
2016070701


タグ ：
#wordpress
#db
#mysql
#sql

WordPress に使っているデータベース（一般的には MySQL のデータベースだと思います）から、SQL を使って、ブログの名称や説明文を取り出す方法です：

2016070501
↑この図の赤枠部分がブログの名称、青枠部分が説明文です。


前提として、データベース作成時のテーブルのプレフィックスはデフォルトの 'wp_' をそのまま使っているものとします。つまりデータベース内には wp_posts とか wp_terms といったテーブルが存在している状態で利用中のものとします：
2016070502


で、ブログの名称は wp_options テーブル内に option_name の値が 'blogname' であるレコードの option_value 値として格納されています。したがってブログ名称を取り出す際の SQL は以下になります：
select wp_options.option_value as name from wp_options where wp_options.option_name = 'blogname';

2016070503
↑取り出せました


同様に、説明文は option_name の値が 'blogdescription' であるレコードの option_value 値です。したがって SQL だと以下になります：
select wp_options.option_value as name from wp_options where wp_options.option_name = 'blogdescription';
2016070504


この辺りがわかっていると、SQL でブログタイトルや説明を無理やり書き換える、ということも可能になります。


タグ ：
#wordpress
#db
#sql
#mysql

StrongLoop の LoopBack を使うと、データベースのモデルを定義するだけで CRUD の REST API を生成し、OpenAPI(Swagger) スタイルのドキュメントと併せて簡単に公開できます。この辺りについては以前のブログエントリを参照してください：
CentOS に StrongLoop をインストールする


ところで、LoopBack を使って公開された API にはパラメータで挙動を指定できるものもあります。例えばモデルの一覧を取得する GET リクエストでは一覧を絞り込むためのクエリーを指定したり、取得結果の数やオフセットを指定することも可能です：
2016020601


そのための方法を紹介します。例えば items というテーブルに対して LoopBack で CRUD の API を作成したと仮定します。items の一覧を取得するには以下の様な URL に対する HTTP リクエストを GET で実行することになります（XX.XX.XX.XX は LoopBack が動いているサーバー）：
http://XX.XX.XX.XX/api/items/

さて、一覧の検索条件（例えば id < 100）を指定する場合、SQL ではこのように指定することになります：
> select * from items where id < 100;

この条件を指定して上記の HTTP GET リクエストを実行するには、つまり id < 100 のものだけの一覧を API で取得するには、以下の様なパラメータを指定して実行します：
http://XX.XX.XX.XX/api/items?filter[where][id][lt]=100

filter という名前に [where] 句が指定され、更に条件である [id] が [lt] (Less Than) で 100 である、という条件が指定されていることになります。


一方、一覧の検索結果の取得数（例えば10件）を指定する場合、SQL(MySQL) ではこのように指定します：
> select * from items limit 10;

この条件を指定して HTTP GET リクエストを実行するには、以下の様なパラメータを指定して実行します：
http://XX.XX.XX.XX/api/items?filter[limit]=10

filter という名前に [limit] 句が指定され、その値が 10 である、という条件が指定されていることになります。なんとなくコツが分かってきましたか？

ちなみに一覧の検索結果の取得数を 50 件目からの 10 件、とするには SQL(MySQL) ではこのように指定します：
> select * from items limit 50, 10;

limit 句を指定し、オフセットが 0 以外の場合は limit 数の前にオフセット数を指定します。これを API のパラメータで指定するには以下のようにします：
http://XX.XX.XX.XX/api/items?filter[limit]=10&filter[offset]=50

クエリーや結果取得の条件は同時に指定することができます。例えば「 id < 100 のものをオフセット 50 で10 件取得」するのであれば、API ではこのように指定します：
http://XX.XX.XX.XX/api/items?filter[where][id][lt]=100&filter[limit]=10&filter[offset]=50

filter の使い方、なんとなくコツがわかってきましたか？ 使っているデータベースの種類は何で、そのデータベースでは SQL ではどういった指定になるか、をイメージできるとわかりやすいです。


このパラメータに関する API ドキュメントはこちら：
https://docs.strongloop.com/display/public/LB/Where+filter
 
タグ ：
#sql
#filter
#loopback
#rest
#http
#parameter

このページのトップヘ