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

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

タグ:sql

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

まずやりたいことはこれです。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
);

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

例えばテーブルの中身がこのようになっているものとします(タイムスタンプの値がいいかげんですが、大小関係だけ分かればいいものとします):
idemailbodycreated
1aaa@xxx.jpToDo その110
2bbb@xxx.jpToDo その220
3aaa@xxx.jpToDo その330


全部で3件のレコードがありますが、email = aaa@xxx.jp のレコードが2件あります。この場合は 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);

これはこれで実現可能な方法の1つです。ただ上記の例とは逆に、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 その220
3aaa@xxx.jpToDo その330

ということは、「有効でないレコード」(削除対象のレコード)はこの 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 その110

ここまでできれば後は単純に削除対象のレコードを削除すればよいので、サブクエリーを使ってこんな 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 ) );

ただこれだと該当部分が2箇所あり、今度は複数のテンポラリテーブルは異なるエイリアスを持たなければならない、という制約に引っかかってエラーになってしまいます:
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 、何も見ずに一発でさらっと書ける人いるんかな・・・


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 のカテゴリに属している、ということになります。

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


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 でブログタイトルや説明を無理やり書き換える、ということも可能になります。


StrongLoopLoopBack を使うと、データベースのモデルを定義するだけで 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
 

Node-RED だけで SQL Database(DB2) 内のデータを検索して結果を表示する、という仕組みを作ってみます。

IBM Bluemix などから提供されているオープンソース・データフローエディタである Node-RED は、ノードと呼ばれる機能単位のブロックを組み合わせて、ほぼコーディングをすることなくデータのワークフローを作れるウェブアプリケーションです。そのフローにデータベースを組み合わせて問い合わせしたり、結果を格納したり、といったことも少ない(或いはほとんどない)コーディングで実現できる、という便利なツールです。特に Bluemix と組わせることで IBM IoT Foundation と連携した MQTT システムを簡単に構築することが可能になります:
2016020101


データのフローを得意とする一方で、Node-RED 単体では UI はあまり得意ではありません。全くできないわけではないのですが、基本的には HTML などの知識を前提として、動的に HTML を作成するような形になります。むしろサーブレット的というか、UI を外出しした仕組みの1パーツとして利用するのが便利だと思っています。


例えば、現在 SQL DB 内に MYTABLE というテーブルが作られ、以下の様な状態になっているものとします。ここから ID 値が 'fff57f93.000a8' であるデータを検索する、ということを目的としてみます。この ID はパラメータとして与えるようにして、リクエストの度に変更できるようなものにします:
2016020101


普通に SQL を使うのであれば(言語によって JDBC などの設定をした上で)、SELECT * FROM MYTABLE WHERE ID = 'fff57f93.000a8' といった具合にクエリーを実行して結果を得ることになると思います。

この処理を NodeRED で行うにはどうすればよいでしょうか?そのためにはまずこの SQLDB に NodeRED からアクセスできるようになっている必要があります。Bluemix 環境であれば、目的の NodeRED プロジェクトに、この SQLDB を事前にバインドしておきます(下図ではサービス名が SQL Database-iv となっています。適宜みなさんの実際の環境と読み替えてください):
2016020101


バインドした上で NodeRED のパレット内の SQLDB を使います。なお、ここでの SQLDB ノードは(検索をしたいので)左右両方にノード接続点があるものを使います。
2016020102


そして以下の様な NodeRED データフローチャートを作ります。HTTP Request ノード、SQL DB ノード、JSON ノード、Template ノード、HTTP Response ノード、そして Debug ノードの6つを以下のようにつなぎます。以下、各ノードの属性を説明します:
2016020101


HTTP Request ノードでは Method として GET、そして URL として /query を指定します。この URL で指定した内容が実際に問い合わせをする際の URL になります:
2016020102


SQL DB ノードでは問い合わせ処理を記述します。Service では SQL DB のサービス名をプルダウンから選択し、Query には ID をキーに MYTABLE テーブルを検索したいので select * from MYTABLE where id = ? と入力します。そしてこの ? パラメータに代入される値を直前の HTTP Request ノードの URL パラメータから取得するようにします(Prepared Statement っぽい感じ)。HTTP Request ノードの URL パラメータの値は msg.req.query で取得できます。今回は id というパラメータにこの値が入っているよう想定するので、Parameter Markers の欄に msg.req.query.id と指定します:
2016020103


JSON ノードは JSON ブロックをそのまま配置するだけです(名前は適当に指定してください):
2016020104


そして Template ノードで検索結果を表示用に整形します。ここでは HTML 形式で、背景を薄い黄色にして表示してみましょう。以下の様な内容を入力します。HTML 内の {{payload}} 部分に検索結果が入り、その結果を msg.payload として返す、という指定をしていることになります:
2016020105


最後に HTTP Response ノードと debug ノードをそれぞれ配置します。この状態でフローを Deploy します:
2016020106


そしてウェブブラウザで、http://(Node-RED のあるサーバー)/query?id=(SQL DB から検索したいレコードのID値) を指定してアクセスします。成功すると指定した ID 値を持つレコードが JSON テキスト形式で、指定したフォーマット(背景が薄い黄色)で表示されるはずです:
2016020107


URL パラメータの id の値を別のレコードのものに変えると、指定したレコードの内容に変わるはずです:
2016020108


同時にこれらの検索結果は Node-RED 画面の debug タブ内にも表示されているはずです:
2016020109


上記例では結果を(わざわざ)HTML 化して出力しましたが、現実には JSON のまま返したり、XML 化して返す、といったサーブレット的な処理をする機会が多いと思います。要は見た目については Node-RED の外で管理すべきと考えています。


・・・と、そう思っていたのですが、最近になって Node-RED に UI パーツを追加できるようなノード部品が提供されたようです。ある程度用途は限られていますが、UI も Node-RED で作れるようになっていくのかもしれません:
https://github.com/andrei-tatar/node-red-contrib-ui


とりあえず、Node-RED だけでサーブレット的な検索機能を実装することもできそうだ、ということが分かりました。


このページのトップヘ