カテゴリーアーカイブ SQL

著者:杉浦

StrategyパターンとSQL文の構築

 Strategyパターンはコーディングのやり方の一つです。
Strategy パターン – Wikipedia
 
 画像は増補改訂版 Java言語で学ぶデザインパターン入門 | 結城 浩 | コンピュータ・IT | Kindleストア | Amazonより引用。
 Strategyパターンはとある目的を達成するためのアルゴリズムをごっそり代えることを容易にするプログラミングパターンです。業務においては仕様がごっそり変わっても、コードへの影響を小さく抑えるために用います。場当たり的な変更を繰り返すと仕様が変わって、関係を持つクラスやメソッドが変わって、更に共通箇所が変わって、変わった共通箇所に合わせて全く関係ない場所が変わって……なんてことも起き得ます。Strategyパターンを実現する方法の一つは、変わることが予想される領域をあらかじめまとめて、まとめた部分の入出力の型を固定する、というものです。変わることが予想される領域は主に業務知識(八百屋さんのプログラムなら八百屋さんの行う業務についての知識)によって把握します。業務知識に重点を置く設計をドメイン駆動設計と言い、ドメイン駆動設計の説明はエリック・エヴァンスのドメイン駆動設計 | Eric Evans, 和智右桂, 牧野祐子, 今関剛 | 工学 | Kindleストア | Amazonに詳しく載っているらしいです。私は買ったはいいですが未だに半分も理解できていません。
 ドメイン設計ほど大仰でなくとも簡単にStrategyパターンを用いることのできる部分があります。それがSQL文の構築です。データベースからデータを取得する時が特に典型です。データ取得のSQL文の中身は、任意の種類の値を任意の条件で任意の順で取得する、という言葉で抽象化できます。種類がselect, join, group by句あたり、条件がwhrere, join句あたり、順がorder by句になりやすいです。joinが二つあるのは関係するデータを引っ張ってくる理由に見たい、絞り込みたいになりやすいからです。見えない値による順番で値を引っ張ってきたいことはレアです。例えば次の様にクエリ構築をまとめた関数を作れます。

/** 次の様にニュースモデルに記述 */
public function scopeNewsListSelectQuery(QueryBuilder $query){
    return // 表示したいカラム、計算結果等を決定する部分を構築するクエリビルダの集合
}

public function scopeNewsListWhereQuery(QueryBuilder $query){
    return // 表示したいデータの条件を決定する部分を構築するクエリビルダの集合
}

public function scopeNewsListOrderQuery(QueryBuilder $query){
    return // 表示したいデータの順を決定する部分を構築するクエリビルダの集合
}
/** 次の様にコントローラからクエリの集合体メソッドを呼び出し */
$news_list = query()->scopeNewsListSelectQuery()
                    ->scopeNewsListWhereQuery()
                    ->scopeNewsListOrderQuery()
                    ->get();

 これだけでは一見、単にクエリを分解しただけに見えます。実際は大きな変更や追記が必要になった時、次の様に部分的にまとまりを入れ替えることで変更部を減らせます。
 例えば、ニュースと同じ仕組みでレビューを表示したい、どのニュースとレビューを表示するかはif文で記述すると複雑になるくらい微細に違う条件で決める、となった場合は次の様にできます。

/** 次の様にページモデル(ニュースモデルを抽象化したモデル)に記述 */
public function scopePageListSelectQuery(QueryBuilder $query){
    return // 表示したいカラム、計算結果等を決定する部分を構築するクエリビルダの集合
}

public function scopeNewsListWhereQuery(QueryBuilder $query){
    return // 表示したいニュースの条件を決定する部分を構築するクエリビルダの集合
}

public function scopeReviewListWhereQuery(QueryBuilder $query){
    return // 表示したいレビューの条件を決定する部分を構築するクエリビルダの集合
}

public function scopePageListOrderQuery(QueryBuilder $query){
    return // 表示したいデータの順を決定する部分を構築するクエリビルダの集合
}
/** 次の様にコントローラからクエリの集合体メソッドを呼び出し */
$news_list = query()->scopePageListSelectQuery()
                    ->scopeNewsListWhereQuery()
                    ->scopePageListOrderQuery()
                    ->get();
$review_list = query()->scopePageListSelectQuery()
                    ->scopeReviewListWhereQuery()
                    ->scopePageListOrderQuery()
                    ->get();

 すっきりです。さらに突飛な要求が多く来たとしてもページという枠組みに収まっているならば、そうそう困りません。もし工夫なく作ろうとした場合、変更時に漏れのありがちなコピペコードになるか、複雑で読み難いうえにインデックスも張りにくい謎クエリビルディングをすることになります。
 もし、SelectもOrderも違うとしたら、それはそれで入力部と表示部は同じだがクエリはごっそり代わるというStrategyパターンにするべきでしょう。

  • この記事いいね! (1)
takahashi 著者:takahashi

EclipseのDBViewerPluginでMySQLに接続する方法

以前の記事でEclipseにDBViewerPluginをインストールする方法についてご説明しました。

今回は実際に接続できるようにするところまでご説明したいと思います。

前回の時点で下記の画像のようにDBViewerPluginが表示されているかと思います。

左上の”DBViewerPlugin”を右クリックし、”登録”をクリックします。

すると、下記のようなウインドウが開きます。

ここで、EclipseがDBを直接扱えるようにするための”ドライバ”を指定する必要があります。

今回はMySQLを例にして説明します。
MySQLのEclipse(Java)用のドライバは下記URLから入手できます。

Download Connector/J – MySQL

Windowsの場合はzip形式の方が扱いやすい(専用ソフトなしで解凍できる)と思います。

ダウンロードしたら、Eclipseからいつでも参照できる場所に保存します。
(自分の場合は、Eclipseのフォルダの中に”drivers”フォルダを作成してそこに配置しました。)

ドライバを配置したら、Eclipse上の先ほどの画面で”ファイルの追加”をクリックし、ドライバの場所を指定します。

ドライバを指定したら次へをクリックします。

JDBC Driverに”com.mysql.jdbc.Driver”を選択します。

OKをクリックします。

すると接続文字列にサンプルの文字列が入るので、<host>の部分をMySQLサーバーのホスト名、<database>の部分をDB名にそれぞれ置き換え、さらにMySQLのユーザー名とパスワードを入力します。

ここまで出来たら”テスト接続”をクリックしてエラーが出なければ接続設定はOKです。

次へをクリックします。

最後に接続設定(読み込むテーブルや文字コードの指定など)が出てくるので、必要があれば指定し、完了をクリックします。

これで設定は完了です。
この時点でEclipse上からDBへのアクセスが可能になっているかと思います。

EclipseでMySQLをお使いの方は参考にしてみてください。

  • この記事いいね! (0)
takahashi 著者:takahashi

PHPでMS SQL ServerをCygwinで使うべきではない理由

何故なら、公式のMSSQLドライバがCygwin用に提供されていないからです。

GitHubのMicrosoftの公式リポジトリでも、Cygwinはサポートしないとはっきりと明言されています。

Driver in cygwin enviroment #258 – GitHub

Transact SQL クライアントのオープンソースの実装であるFreeTDSについてはCygwinにもドライバがあるので、これを使えば”とりあえずは”Cygwin上のPHPからSQL Serverへ接続することはできます。

しかしFreeTDSではSQL文実行後一定時間経過しても結果が返ってこない場合、接続を切ってしまううえ、この仕様を修正するにはFreeTDSのソースを修正するしかない、という状況になっているようです。(調べて見つけた記事にそのようなことが書かれていたのですが、その記事のURLを見失ってしまいました…また見つけたら追記します。)

代替えとして、Windowsネイティブでありながら、基本的なLAMP環境を簡単に動作させることができるXAMPPというパッケージがあります

XAMPP

こちらなら、SQL Serverの公式PHPドライバが利用可能ですので、 個人的にはこちらを利用するのがおすすめです。

本当はUNIXライクなOSで動作させるのが一番なのですが、もしWindowsでどうしても…という方は一度検討してみてください。

  • この記事いいね! (0)
takahashi 著者:takahashi

fuelPHP + PDO_sqlsrvでストアドプロシージャ実行時にIMSSPエラーが出てきたときの対処法

FuelPHP上でMSSQLサーバーにあるストアドプロシージャを実行しようとしたところ、こんなエラーが発生。

PDOException [ IMSSP ]:
SQLSTATE[IMSSP]: The active result for the query contains no fields.
エラー

見慣れないエラーコードだったので少し戸惑いましたが、調べてみたところ、解決策が見つかりました。

“The active result contains no fields” using PDO with MS SQL – Stack Overflow

このエラーが出た場合、実行するSQL文の前に

SET NOCOUNT ON;

を実行しておくとエラーを回避できるようです。

このオプションを追加したところ、自分の環境でもエラーが出なくなりました。

同じエラーでお困りの方は、是非試してみてください。

  • この記事いいね! (0)
takahashi 著者:takahashi

FuelPHP + pdo_sqlsrv + SQL Server で「’NAMES’ は SET オプション として認識されません。」となったときの対処法

FuelPHPにSQL Serverを連携させたPHPのWebアプリを作ったときに出た問題。

SQL文自体には問題がなかったのですが、このようなエラーが発生しました。

原因はFuelPHP側の設定ファイルに文字コード設定を入れてしまっていたこと。

// fuel/app/config/db.php

return array(
    'default' => array(
        'type' => 'pdo', 
        'connection' => array(
            'dsn' => 'sqlsrv:Server=localhost;Database=hoge_db;',
            'username' => 'user',
            'password' => 'password123',
            'persistent' => false,
        ),
        'identifier' => '', 
        'table_prefix' => NULL,
        'charset' => 'utf8', //←これが原因
    )
);

charsetを入れると、DB呼び出し時にfuelが自動的に

SET NAMES 文字コード;

のようなSQL文を実行しようとします。

ところが、SQL Serverではこの”SET NAMES”オプションが廃止されており、これが原因でSQL実行時に毎回エラーになってしまいます。

対策としては

'charset' => 'utf8', 

'charset' => '', 

と書き換えれば、SET NAMESが行われなくなり、エラーが解決します。

いろいろ調べまくってやっと判明したぐらい細かい項目で、原因がわかるまで苦労しました…

お困りの方の参考になれば幸いです。

  • この記事いいね! (0)
takahashi 著者:takahashi

MySQL 5.7で厳格になったパスワードポリシーを解除する方法

MySQLは5.7からrootパスワードがデフォルトで設定されていたりと、なにかと面倒な仕様変更が多いのですが、その流れで今回困ったことがあったのでご紹介したいと思います。

とりあえず、開発用のテスト環境としてセットアップをしたサーバーにMySQL5.7をセットアップし、エディタからSQLサーバーへアクセスできるようにするために、いつも通り外部からのアクセスが可能なユーザーを作成しようとしたのですが…

-- 実際にはもっと予想されにくい認証情報を指定しています。
GRANT ALL PRIVILEGES ON DBNAME.* TO user@'%' IDENTIFIED BY 'PASSWD123' WITH GRANT OPTION;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

…はいでました、ポリシー違反(汗
MySQL5.6あたりまでは平気で設定できていたパスワードも、ものによっては5.7以降になると単純すぎるとはじかれます。

本番環境であれば十分に複雑で、かつ大文字や記号が入っているパスワードがベストなのはわかっていますが、開発中は手動で何度も接続しないといけない場合があるため、複雑なパスワードにしてしまうとちょっと困ります。(もちろんオフィス以外から接続できないようにするなどの別の対策はとってます。)

なんとか緩いパスワードを設定したいと探したところ、SQL上でパスワードポリシーを変更する方法を見つけました。

mysql5.7でパスワードを変更する – Qiita

MySQLのシェルに入った状態で、次のSQL文を入力します。

-- パスワードの最低文字数を4文字に変更
SET GLOBAL validate_password_length=4;
-- 要求するポリシーレベルを"LOW"に変更
SET GLOBAL validate_password_policy=LOW;

これで再度

GRANT ALL PRIVILEGES ON DBNAME.* TO user@'%' IDENTIFIED BY 'PASSWD123' WITH GRANT OPTION;

を実行してみると…

Query OK, 0 rows affected (0.00 sec)

無事成功しました。

MySQL 5.7から結構あちこち変更されているので、今までのノリで触っていると戸惑ってしまうような変更が結構ありますが、その仕様変更に当たっても、焦らずに一つ一つ解決していきたいですね。

  • この記事いいね! (0)
著者:ym

PostgreSQL で正規表現の$と\Z

便利なので、 SQL の SELECT句や、WHERE句で、良く正規表現を使うのですが、変数の最後とマッチさせたくて悩んだのですが、無事解決したので書いておきます。

ネット検索すると PostgreSQL の末尾は \Z だと書かれていますが、なんどやっても一致しないのです。

えい、それじゃ小文字で \z なんでどうだ?とすると “z” にマッチするし。

でも無事解決しました。多分、PostgreSQL が古すぎるのです😂

regexp_replace(mojimoji, E’.[0-9]+$’, ”))

なんと、ずばり “$” でした。って、これかよ普通じゃんか。付けなくても動くけど、”E” と付けないと怒られるので付けました。

  • この記事いいね! (0)
著者:杉浦

ORDER BY句で指定するカラムはFROM句の頭のテーブルの方が高速

ORDER BY 句と別の GROUP BY 句がある場合、または、ORDER BY または GROUP BY に結合キュー内の最初のテーブルと異なるテーブルのカラムが含まれている場合は、一時テーブルが作成されます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.4.4 MySQL が内部一時テーブルを使用する仕組み

 この一時テーブルを作る動作が案外重く、FROM句の最初のテーブルで指定できるならば、そちらで指定しましょうという話です。具体的には下図です。結合に用いている同じ値を持つmembers.primary_keyとpoints.member_keyそれぞれによるソートを用いたクエリの実行時間です。10数回程度しか試していませんが、ここまで差があるならば違いがあることは明らかです。

 一時テーブルが作られているかどうかを確かめるためにはEXPLAIN句、その結果が次の図等です(どちらもtype=Allなのは気にしないでください)。それぞれ上がmembers.primary_keyによるソート、下がpoints.member_keyによるソートです。上側のEXtraにのみあるUsing temporaryは一時テーブルを作るという意味です。これがある場合FROM句の最初のテーブル中のカラムでソートを指定するとかで、どうにか消すと高速化が望めます。

  • この記事いいね! (0)
asaba 著者:asaba

【mysql】接続エラーの回数制限を超えてアクセスできなくなったときのメモ

アプリからブログを投稿するアプリで機能のプロトタイプを作っていた時に遭遇しました。

こちら

 


<span style="color: #ff0000; font-size: 14pt;">Host hogehoge.jp' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'</span>

複数回にわたる接続を確認したためブロックされました・・・初見ではなにが原因かさっぱり。

 

リファレンスを見る限り、予め許可する回数が決まっておりその上限を超えると、mysqldは送信元に問題があるとみなしホストがアクセ

スできないように遮断してしまうみたいです。

 

外部からの不正な攻撃を防ぐための対策なので、原因を一本に絞ることができなくて狼狽えましたがとりあえず

クッキーを全削除して再びアプリを使って投稿したところ正常に動きました。

 

正規ではないと思いますが、基本的にはまずクッキーを全部消してみることをお勧めします。

それがだめならブラウザを閉じてchromeを再起動をしてみてください。自分のアカウントを使っている場合はいったんログアウト

してから再度chromeを開いてみてください。

 

  • この記事いいね! (0)
著者:杉浦

PhpStormの何でも屋コマンドAlt+Enter

インテンション・アクション – 公式ヘルプ | PhpStorm
 自分が使っているIDEのPhpStormにはAlt+Enterというとりあえず使おうという意味合いのショートカットキーがあります。Alt+Enterコマンドはカーソルを合わせた場所に合わせた何でも屋の様なコマンドです。操作名はインテンション・アクション、公式の説明はPhpStormの提案を実行する、ととてもふんわりしています。インテンション・アクションはふんわりとした分様々なことを行ってくれます。
 Alt+Enterから実際よく使う動作としてスペルミス、インポート、PHPDoc補完があります。波線が現れたらとりあえずAlt+Enterしてみるぐらいでちょうどいい塩梅で、PhpStormがかくあるべしというコードを教えてくれます。



 PhpStormという名前ですが縁があるくらいの他言語、例えばSQLのコマンドでも同じように有効です。識別子で修飾はスキーマ名やテーブル名を修飾してくれます。

 

  • この記事いいね! (0)