I love iPhone, Android, Cocos2d-x
Posts tagged SQL
CakePHP 環境によってデータベースを切り替える
2テストデータを入れるなどテストと環境でデータベースを切り替えたいときがあります。
CakePHP でそれをやる方法です。
モデルの $useDbConfig に app/config/database.php で定義されている $default がデフォルトで使用されます。
database.php に $test など使用したいデータベースの分だけ定義を増やし、
それをモデルで
$this->useDbConfig = 'test';
のように指定すればいいだけです。
app/app_model.php のコンストラクタで設定するのが一番簡単かもしれません。
CakePHP 環境に応じてDBの設定を変える | Shin x blog
で色々な方法が紹介されています。
“CakePHPで超簡単スケーラビリティ” フォーラム – CakePHP Users in Japan
また、この $useDbConfig を使用して
「マスターとスレーブのMYSQLサーバがあります。レプリケーション機能で、マスターからスレーブにデータが常にコピーされています。データの更新・追加はマスターに対して行い、データの検索はスレーブで、という場合にはどうすればよいでしょうか？」
というような場合のすごく簡単な方法が紹介されています。
モデルの beforeSave,afterSave,beforeDelete,afterDelete を使用してマスターとスレーブを切り替えています。
CakePHP MySQL で文字化けを防ぐ設定
4MySQL で文字化けを防ぐためには
SET NAMES utf8
のように SET NAMES を実行するのが有効なのですが、これを app/config/database.php で設定する方法です。
'encoding'=>'文字コード'
をデータベースの設定項目に追加してやるだけです。
具体的には app/config/database.php が下記のようになります。
var $default = array(
'driver' => 'mysql',
'connect' => 'mysql_connect',
'host' => 'localhost',
'login' => 'user',
'password' => 'password',
'database' => 'dbname',
'prefix' => '',
'encoding' => 'utf8'
);
cake/libs/model/dbo/dbo_mysql.php で下記のように実行されています。
function connect() {
(略)
if (isset($config['encoding']) && !empty($config['encoding'])) {
$this->setEncoding($config['encoding']);
}
return $this->connected;
}
function setEncoding($enc) {
return $this->_execute('SET NAMES ' . $enc) != false;
}
CakePHP findBy での複数指定を調べたメモ
2先のエントリで書いた「CakePHP findBy で複数条件の指定で OR も指定可能」ですが、findByHogeAndFoo などの処理がどうなっているか気になったので調べたときのメモです。
- findByHogeAndFoo メソッドが実行されると model_php5.php ( Model クラス ) の __call メソッドが実行される
- __call メソッドでは DboSource クラスの query メソッドに findByHogeAndFoo のメソッド名と条件の値を渡す
- DboSource クラスの query メソッドでは、findBy または、findAllBy の後ろの文字列を取り出す
- 取り出した文字列を小文字に変換してさらに AND を _and_、OR を _or_ に変換する
- _or_ または _and_ で分割してフィールドを取り出す
- AND または OR にしたがって SQL の条件を組み立てる
というようなこと処理がされていました。
まとめると
- 複数指定はいくつでも可能
- AND または OR は混在して指定はできない
いくつでも指定はできるようですが、たくさんの条件をこれで指定するとソースの可読性が下がりそうですし、AND と OR が混在できないので普通はそんな指定はしないでしょう。
指定する場合は2つくらいにとどめておいて、それより多くなる場合は find の $conditions で指定した方が無難だと思います。
CakePHP ファイルアップロードするコンポーネント
2CakePHP でファイルアップロードを簡単に行うコンポーネントです。
ReverseFolds – CakePHP File Uploads
データベースにファイル名、MIMEタイプ、サイズなどを記録し、ファイルは指定したディレクトリへアップロードするようになっています。
ビューにファイルアップロード用のタグを設置
<input type='file' name='userfile[]'/>
複数配置したいときは
<input type='file' name='userfile[]'/> <input type='file' name='userfile[]'/>
コントローラに使用するコンポーネントを追加
var $components = array('FileHandler');
アップロード処理
$this->FileHandler->setDebugLevel(1);
$this->FileHandler->setRequired(0);
$this->FileHandler->setHandlerType('db');
$this->FileHandler->setDbModel('FileUpload');
$uploadDir = 'path/to'; // アップロードするディレクトリを指定
if ($this->FileHandler->upload('userfile', $uploadDir)) {
echo 'アップロード成功';
} else {
echo 'アップロード失敗';
}
実際の処理などはリンク先からダウンロードしたファイルにコンポーネントの他に、ビュー、コントローラ、モデル、SQL と必要なものは入っているのでソースを見てみるとすぐに分かると思います。
アップロードされるファイルは $uploadDir で指定したディレクトリ内にユニークなディレクトリ名を作成してそのディレクトリ内に元のファイル名で保存してくれます。
その他のメソッド
アップロードできるファイルタイプを限定するメソッド
void setAllowedMime( array $mimeTypes )
ファイルの最大容量を設定するメソッド
void setMaxSize( int $size )
などもあります。
SQL BLOB の容量を計算する方法
1OpenPNE のカスタマイズをしていて会員毎に画像容量の制限をする必要がでてきました。
quota を使用できれば簡単なのですが、OpenPNE は DB に バイナリデータとして画像を保存しているため使用できません。
そこで SQL で容量を計算する方法を考えました。
LENGTH 関数を使用して
SELECT LENGTH(blobカラム)
とすればバイト数を計算できます。
OpenPNE の場合は c_image というテーブルに画像データが格納されています。
そこで特定の会員の日記に使用した画像の容量を計算するのに下記のようにしてみました。
SELECT SUM(LENGTH(i.bin)) FROM c_image as i LEFT JOIN c_diary d ON(d.image_filename_1=i.filename OR d.image_filename_2=i.filename OR d.image_filename_3=i.filename) WHERE d.c_member_id=会員ID
得られる値は byte 数になります。
CakePHP findAll で INNER JOIN する方法
1開発中の CakePHP の案件でどうしても INNER JOIN する必要があり、かといって SQL を直接書くのは他のコンポーネントとの兼ね合いでできるだけ避けたい（というより無理）な状況だったので、絶対何か方法があるだろうと思いソースを調べたのでメモしておきます。
/cake/libs/model/model_php5.php の findAll メソッドに
$queryData = array('conditions' => $conditions,
'fields' => $fields,
'joins' => array(),
'limit' => $limit,
'offset' => $offset,
'order' => $order
);
というのがあり、’joins’ に 空の配列を入れている。
この ‘joins’ の使い方を調べたら、
array(
"type" => "INNER",
"alias" => "",
"table" => INNER JOIN するテーブル,
"conditions" => INNER JOIN の ONに指定する条件,
);
という配列を入れると INNER JOIN してくれるようです。
ただ、findAll の中で joins に空配列を入れているので、findAll を実行するときのパラメータに joins を指定しても無視されてしまう。
そこで、モデルの beforeFind メソッドを使用することにした。このメソッドは findAll の実行する前に呼ばれるメソッドです。
findAll を実行するモデルに次の beforeFind を定義
function beforeFind(&$queryData) {
$queryData["joins"][] = array(
"type" => "INNER",
"alias" => "",
"table" => "`model_name`",
"conditions" => array("model_name.field=model_name2.field2"),
);
return true;
}
これで実行された SQL を確認すれば INNER JOIN されれいます。
PHP で多次元配列のソートを SQL の ORDER BY のように複数項目でソートする
1PHP で多次元配列の項目を複数使用して SQL の ORDER BY のようにソートする方法です。
$answer =
array(
"category1" =>
array(
"ans1" => 1,
"ans2" => 1,
"ans3" => 1
),
"category2" =>
array(
"ans1" => 1,
"ans2" => 2,
"ans3" => 0
),
"category3" =>
array(
"ans1" => 0,
"ans2" => 2,
"ans3" => 1,
),
);
というような配列を下記条件でソートします。
- ans1 の多い順
- ans1 が同じ場合は ans2 の多い順
- ans2 が同じ場合は ans3 の多い順
PHP の uasort 関数を使用します。
この関数はユーザーが比較関数を定義でき、その関数を使用して連想インデックスを保持したまま配列をソートします。
uasort($answer, "compare");
function compare($a, $b) {
foreach ($a as $k=>$v) {
if ($a[$k] > $b[$k]) {
return -1;
} else if ($a[$k] < $b[$k]) {
return 1;
}
}
}
上記の関数を使用してソートした結果 $answer は下記のようになりました。
$answer =
array(
"category2" =>
array(
"ans1" => 1,
"ans2" => 2,
"ans3" => 0
),
"category1" =>
array(
"ans1" => 1,
"ans2" => 1,
"ans3" => 1
),
"category3" =>
array(
"ans1" => 0,
"ans2" => 2,
"ans3" => 1,
),
);
MySQL で NULL を一番最後にして昇順にソートする
42008.2.27 追記
コメントで教えていただきました下記方法で簡単にできました。
is null asc の指定と 通常の asc の指定をするのがポイントですね。
SELECT id, comment FROM table ORDER BY comment IS NULL ASC, comment ASC;
—–追記ここまで—–
MySQL で昇順にソートすると NULL は一番最初に来ます。
それを最後にできないかということで下記のようなSQL を考えてみました。
SELECT
id,
comment,
CASE WHEN comment IS NULL
THEN 10000
ELSE ASCII(LEFT(comment,1))
END AS dummy
FROM table
ORDER BY dummy ASC, COMMENT ASC
comment カラムを昇順でソートして、NULL の場合は一番最後に NULL がきます。
あまりスマートな方法ではないのですが、これで取り合えず NULL を最後にして昇順でソートするということができました。
ORACLE とかだと下記のように簡単にできますね。
ORDER BY comment ASC NULL LAST
NULLS FIRST
NULL 値を順序の最初にするソートを行なう
NULLS LAST
NULL 値を順序の最後にするソートを行なう
ランダムに抽出する SQL
4ランダムに10件 SELECT する SQL です。
ORDER BY RAND() でランダムにソートして LIMIT 句で取得したい件数だけ取得します。
SELECT * FROM table ORDER BY RAND() LIMIT 10;
ランダム関数は MySQL, PostgreSQL, SQLServer で使用できます。
ランダム関数は
MySQL, SQLServer は RAND()
PostgreSQL は RANDOM()
です。
ランキングを取得するSQL
3ランキングを取得するSQL です。
同じスコアの場合は同じ順位にするなどの必要があるため結構面倒ですが、
下記SQL でイッパツで取得できます。
実際に携帯ゲームのランキングを取得するのに使用しているSQLです。
ranking テーブル
CREATE TABLE ranking ( id bigint(20) NOT NULL auto_increment, name varchar(20) NOT NULL, score bigint(20) NOT NULL, );
SQL
SELECT
r1.name,
r1.score,
(SELECT count(r2.score)
FROM ranking as r2
WHERE r2.score>r1.score)+1 as rank
FROM ranking as r1
ORDER BY r1.score DESC
LIMIT 10;
rank というカラム名で順位が計算されます。
同じスコアの場合は順位は同じになります。
上記SQL はトップ10を取得していますが、実際にはLIMIT句を変更することにより
ページング処理などで下位のランキングを見れるようにしています。
副問い合わせで順位を計算しています。
MySQL ではバージョンが4.1 以降でないと副問い合わせが使用できないので注意してください。
※本運用ではインデックスを適宜作成しています。