C Sharpens you up

http://qiita.com/yuba に移しつつあります

SQLプログラミング課題・表示順変更の実現

CodeIQの問題・パズルを考えよう!(by CodeIQ×はてな

タイトル見ただけで苦労の記憶がよみがえってきた方はその時点で合格ってことでいいかもしれません。

INT型の主キーを持ったテーブルがあり、1行を1アイテムとして表示できます。

CREATE TABLE items (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL);

このアイテムリストの表示順を、ユーザが自由に変更できるようにしてほしいのです。

もちろん、業務アプリならば「表示優先度」なんていう整数値をユーザが設定できるようにすれば要件は達成ですが、コンシューマ向けWebアプリではそうはいきません。

ケース1

UIでは、各アイテムに「上げる」「下げる」ボタンをつけました。
「上げる」「下げる」それぞれの操作を実現するためのテーブル変更と、操作時に発行するSQL文を書き下ろしてください。

操作のパラメータとして無効なIDが渡された場合や、一番上にいるのに「上げる」を指示された場合の動作は、エラーになるか何も起こらないかなど妥当なものを定義してください。

ケース2

UIでは各アイテムのドラッグ&ドロップを可能にしました。
ドロップ時の操作を実現するためのテーブル変更、ドロップ先領域にどんな識別値を与えておくか、操作時に発行するSQL文を書き下ろしてください。

無効な指示に対してはケース1と同様、妥当な動作を定義してください。

ポイント

表示優先度にあたるカラムを作ることになりますが、それはユニーク値を格納することになると思います。
それを変更するにあたって、UPDATE文が複数行を変更するときすべて変更し終わるまでUNIQUE制約はチェックされないという点がコツになります。だから例えば値の入れ替えも一文で書けば制約に引っかかることなく実行可能です。