Posted on

就職していた期間の合計を取得する方法

2000/01/08から2005/05/31まで就労
2006/01/08から2010/05/31まで就労
2011/01/08から現在も就労
の全就労日数を求める方法を紹介します。

データ構造サンプル
CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL ,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1 ;
CREATE TABLE works (
id INT NOT NULL AUTO_INCREMENT,
user_id INT NOT NULL ,
start_date DATE NOT NULL ,
retire_date DATE NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci AUTO_INCREMENT=1 ;

データの情報としては、
users.id:1,users.name:奥進太郎
works.id:1,works.user_id:1,works.start_date:2000/01/08,works.retire_date:2005/05/31
works.id:1,works.user_id:1,works.start_date:2006/01/08,works.retire_date:2010/05/31
works.id:1,works.user_id:1,works.start_date:2011/01/08,works.retire_date:null

1回毎の就労日数を求めるVIEWを作成します。
CREATE VIEW work_diffs AS
select works.user_id AS user_id,
(case when isnull(works.retire_date) then (to_days(curdate()) – to_days(works.start_date))
else (to_days(works.retire_date) – to_days(works.start_date)) end end) AS work_diff
from `works`;

上記VIEWをgroup byで合計すると、全就労日数が取得できます。
CREATE VIEW work_sum_diffs as
select work_diffs.user_id as user_id,sum(work_diffs.work_diff) as sum_work_diff
from work_diffs group by work_diffs.user_id;

usersとwork_diffsをJOINすれば、users毎の全就労日数を取得することができます。

Posted on

Say yes or die!

凄く過激なタイトルを付けてしまいました。
これはあるSQL文を見て、それを短く書き換えたときの感情です。


SELECT SUM(CASE WHEN condition > 100 THEN 0 ELSE 1 END) as positive FROM some_table

1行で書いてしまいましたが、もともとはCASE文が数層入れ子になっていて、複雑な条件式を最後にSUMで括っていた、というのが複数項目にも及んでいた感じのSQLでした。そして、CASE式で評価される結果は必ず0か1。つまるところ、CASE文をたくさん組み合わせて条件を作り、条件に合うものを数えるということをやっていたわけです。

最初に思ったのは「WHERE句で条件絞ってcountすればいいじゃん」ってことです

SELECT COUNT(condition) as positive FROM some_table WHERE condition > 100

そうできればよかったんですが…条件に合う件数を数える項目がほかにもたくさんあり、それぞれで
毎回SQLを発行するのもこれまた頭が悪い。

SQLの返答をphp側でforeach回してオレオレカウントする…というのも考えたのですが、
カウント部分で何をやっているかを把握しようとするとCASE文のピラミッドとどっこいどっこいだなとも。

「テーブル全体を検索しつつ、複数条件それぞれにおいて条件に合うものをcountするには…」と調べていて、
このタイトルの「Say yes or die!」にたどり着いたわけです。

MySQLの論理式にはTrue、FalseのほかにNullを叩き込むことができると言う話があって…

  • True AND Null = Null
  • False AND Null = False
  • True OR Null = True
  • False OR Null = Null

つまるところ、Nullの有無にかかわらずTrue/False断言できる式は断言してもらえるという結果になるわけです。
そして、count()はNullでないものを数える。

というわけで生まれるイディオムが count([何か条件式] OR Null) というわけです。
条件式がTrueならレコードはTrueと評価され、カウントの対象になる、
条件式がFalseまたはNullなら、レコードはFalseと評価され、カウントされない。

このイディオムを使って最初のSQLを書き直すと…

SELECT count((condition > 100) OR NULL)as positive FROM some_table

‘OR NULL’という書き方の意味さえ知っていれば、これが何かを「合計」しているわけではなく「数えている」ことが一目でわかりますし、何より、WHERE句を変えながら何度もSQL発行しなくて済みます。別のカウントをしたいときは条件を変えた検索フィールドを追加するだけですから。

「Say yes or die!」もとい「Be true or null!」使えると思います!

Posted on

EC-CUBEでカートに商品を入れすぎるとエラー発生

EC-CUBE2.13(たぶん2系全て?)で商品を100個とか沢山カートに入れるとなぜかエラーが出てしまいます。

MySQLで、dtb_session.sess_dataにカートの内容を保持しており、このフィールドはTEXT型になっているんですが、調べてみるとどうもこのTEXT型ではサイズ(容量)が足りないようです。

結局、TEXT型を使ってたのをMEDIUMTEXT型に変更したらいけたのですが、まあ、通常のBtoC取引では100種類の商品も買うこと想定してなかったんでしょうね。

今回調べたTEXT型について以下にまとめます。
TEXT型には4つのタイプTINYTEXT、TEXT、MEDIUMTEXT、LONGTEXTがあります。

・TINYTEXT 最長255 バイト
・TEXT 最長65,535 バイト(64KB)
・MEDIUMTEXT 最長16,777,215 バイト(約1.6MB)
・LONGTEXT 最長4,294,967,295 バイト(約4.3GB)

カートのセッション情報を保存する、といった最長サイズで引っかかりそうなフィールドは、MEDIUMTEXTにしておいた方がよさそうですね。

(参考)http://webrescue.net/archives/4030

Posted on

DBで使うビューについて

最近仕事で、データベース上にビューを作る機会が何度か出てきました。最初は使い方も使う意味もわからなかったのですが、何度か教えてもらっているうちに、すごく便利なものだということがわかってきました。そのビューの使い方等、この機会に復習がてら自分なりにまとめておきたいと思います。

実際に表示したい項目がDBテーブルに存在しない場合は、新たに項目を設けてビューを作っておくと実際にDBテーブル上の項目と同じように使えるので、その値を表示させたり、あるいはその項目でソートさせたりすることもできるので非常に便利です。また、リアルタイムでビューが更新されるので、誕生日から現在の年齢を求めるときなど動的な値を算出するときにも便利です。
続きを読む DBで使うビューについて

Posted on

BI【Business Intelligence】ツールpentahoを使ってみました。

1.利用要件
 外部にあるMySQLに接続し、データベースを利用する。
 欲しい情報をグラフにして表現する。
 また、絞り込んだデータをEXCELやPDFに出力する。
2.pentahoインストール方法
 1)ダウンロードするファイル
  ・biserver-ce-4.5.0-stable.zip
  ・jdk-7u9-windows-i586.exe
  ・fop-0.20.5.jar.zip
 2)準備しておく環境
  ・Fedore14 MySQLのサーバー
   a.ファイアウォールでポート3306を有効にする。
   b./etc/my.cnfにてbind-addressが指定されていないことを確認
     (指定がないとどこからでもアクセス可能になります。)
   c.特定ユーザーのログイン情報のホストの設定を、「すべてのホスト」に設定する。
 3)インストール
  ・C:\pentahoのフォルダを作成する。
  ・C:\pentahoへ1)でダウンロードしたファイルをコピーする。
  ・biserver-ce-4.5.0-stable.zipを(C:\pentaho)へ展開する。
  ・jdk-7u9-windows-i586.exe(32BIT)版をインストールする。
   (64BIT:マシンは64BIT版をインストールする。)
  ・環境変数JAVA_HOMEへC:\Program Files\Java\jdk1.7.0_09を設定する。
  ・あと、biserver-ce-4.5.0-stable.zipの解凍だけでは、EXCEL、PDFが出力できないため、
   C:\pentaho\biserver-ce-4.5.0-stable\biserver-ce\tomcat\webapps\pentaho\WEB-INF\libの
   fop-0.94.jarを消し、fop-0.20.5.jarをコピーする。
3.pentahoの起動
 1)C:\pentaho\biserver-ce-4.5.0-stable\biserver-ceのstart-pentaho.batを実行する。
 2)ブラウザーを立ち上げてhttp://localhost:8080を入力する。
 3)ログイン画面が立ち上がるので、ID:joe、パスワード:passwordにてログインする。
4.pentahoの利用
 1)お使いのデータにより、集計する対象が変わります。
 2)mySQLへの接続、ファイル->新規作成->DataSourceにてData Source Wizardが立ち上がる。
 3)SourceTypeをDatabase Table(s)を選択する。
 4)+アイコンをクリックしてDatabase Connectionにてお使いの環境の情報を設定する。
  (ここで2.2)・c.のユーザーを設定する。)
5.pentahoの停止
 1)C:\pentaho\biserver-ce-4.5.0-stable\biserver-ceのstop-pentaho.batを実行する。
6.利用方法
 2日程度しか調査していないので、できることに関しては、全て把握した訳ではありません。
 利用の方法としては、mySQLでVIEWをCREATEしてそのテーブルをpentahoで参照し、グラフ化
 しました。pentaho内では、条件文を記述できないようなので、mySQLのVIEWで代用しました。
7.補足情報
 biserver-ce-4.5.0-stable.zipをインストールする前に、biserver-ce-3.10.0-stable.zipを
 インストールして使ったのですが、mySQLのViewを参照することができなかったです。
Posted on

MySQLに大量のデータをインポートする

今回レンタルサーバーの区画のMySQLに大量データを流し込まなくなったのですが、レンタルサーバーが用意してくれているphpMyAdminでは容量が大きくてインポートが出来ませんでした。
自社で管理しているサーバーであればコマンド一発で難なく出来るのですが、レンタルサーバーなものでコマンドも使えない、php.iniの値も変えれないということでどうした物かという状況になりました。
困ってググッてみたらBigDumpという便利そうなものを発見!!
詳しくはhttp://d.hatena.ne.jp/istwar/20101019/1287505139を見てもらった方が早いので説明は書かないですけど、とにかくPHPファイル1つでインポートが出来ちゃうのでどんなサーバー相手でもへっちゃらというのは素晴らしい!!
すごい重宝するので皆さんも是非使ってみてください。

Posted on

DB暗号化を調べてみた

DBに保存する時に個人情報をそのまま保存するのは、万が一DBの情報が流出した時にまずいので、色々と調べてみました。

まず、パスワードはphpのmd5でハッシュ化して保存すれば、復号できないのでOK、パスワードを忘れた場合は、新しいパスワードをランダム生成。
次に、暗号・復号するデータ。
氏名やメールアドレスなど、
php側で暗号・復号する方法と、DB側で暗号・復号する方法があります。(ハードディスク自体暗号の方法もありますが)
で、問題は、LIKE検索とINDEXでした。
基本、暗号化して保存すると上記2点が出来なくなることが問題です。
その点も踏まえてどんな方法があるか調査してみました。
とりあえず今回は
php mysqlの環境で出来ればということで・・・

続きを読む DB暗号化を調べてみた

Posted on

cakephpのdatabase.phpのprefixを使用しました。

新しいシステムをレンタルサーバーにアップする際に、データベースが1つしか存在せず、新たにデータベースを作ることもできず、すでに他のシステムのテーブルが存在し、データベースが利用されている状態にありました。既存システムと新システムのテーブルを簡単に区別する必要があると思い調べました。単純にテーブル名を変更すると、MVCモデルのファイル名やフォルダ名も変更する必要が出てきます。調べていくうちに、cakephpのdatabase.phpでプレフィックスを指定できることが判明し、’prefix’ => ‘okushin_’,ように定義しました。当然MySQL側でも
mysql> ALTER TABLE 古いテーブル名 RENAME 新しいテーブル名;
のコマンドにてテーブル名をusersからokushin_usersのように新システムに関係するテーブルすべてにokushin_をつけてテーブル名を変更しました。するとMVCモデルでのMのuser.php、Vのフォルダ名users、Cのusers_controller.phpのファイル名フォルダー名はそのままでシステムが正常に動作しました。とても簡単で利用しやすい機能だと思い紹介してみました。
Posted on

A5SQL Mk-2のツールを検証しました。

A5SQL Mk-2のツールを検証しました。
検証は、fedora14上のMySQLにODBC経由で接続し行いました。
ODBCの設定に、MySQL側にも設定が必要で、時間を要しました。
MySQLの設定は、以下のような設定です。
mysql> grant all privileges on DB名.* to アカウント名@’IPアドレス/サブネットマスク’ identified by ‘パスワード’;
コントロール パネル\すべてのコントロール パネル項目\管理ツールにて、
データソース(ODBC)をクリックして、ODBCデータソースアドミニストレーターにて、
システム DSNを設定します。
これで準備は完了です。
A5SQL Mk-2のツール[A5M2.exe]を立ち上げて、データベースの追加と削除を行います。
ODBCシステムデータソースを列挙するをチェックして、追加ボタンを押下します。
データリンクプロパティで先ほど設定したODBCのデータソースを選択して、接続テストをします。
これで、A5SQL Mk-2のツールが使用できる状態になります。
確認した内容としましては、下記4点です。
1.SQLの実行
2.データの参照
3.ER図のリバース生成
4.テーブル定義書作成
【よかった点】
1.データの参照では、レコードの追加や削除ができ、こちらはテストデータを作成するなどで利用できそう。
2.テーブル定義書作成は、体裁を整える機能としては有効に利用できるのではないかと思います。
【残念だった点】
1.テーブル作成のSQLを実行しましたが、その場ではテーブルが追加されず、立ち上げ直して初めて参照できるようになる点
2.ER図のリバース生成は、MySQLなので、外部キーの設定情報などの確認が出来なかった点
総合評価:弊社では、現在EXCELにてテーブル定義を行い、MySQLに登録して使用しています。EXCEL作成時の項目に対するデータの定義も行っており、その記述がMySQLではできない点もあるために、このツールを積極的に利用するという結果には至りませんでした。ツールの持っている機能はとてもすばらしいと思います。用意されている機能は、他のデータベースではもっと有効に利用できるのだろうと思いますが、MySQLでは、少し物足りないように思いました。また、MySQLだけなのかもしれませんが、設計するためのツールという位置づけでは利用できないと思いました。
利用されている方で、私どもが間違った認識をしている点などが御座いましたら、ご指摘頂けましたら幸いです。