概要

データベースの一時表（一時テーブル、temporary table）は、使いこなすととても便利な機能なのですが、唯一扱いづらいところは、別セッションからレコードが覗けないというところです。

この難点のためだけに一時表を使わないのは勿体無いのでどうにかしたいと思います。

ビューは使わない

タイトルにある「定義を変更せず」というのは、一時表の代わりに実テーブルとビューを組み合わせて一時表モドキにするという手法は用いないということです。

やり方

それでは手順を説明します。サンプルのSQLはOracleで実行できる形式とします。
（MySQLやPostgreSQLでも応用可能です）

サンプルテーブル

今回の説明用のサンプルテーブル定義は以下の通りです。

CREATE GLOBAL TEMPORARY TABLE USER1.TMPTBL (
  "ID"   NUMBER(3,0) NOT NULL ENABLE,
  "NAME" CHAR(128)   NOT NULL ENABLE,
   CONSTRAINT TMPTBL_IDX_00 PRIMARY KEY ("ID")
) ON COMMIT PRESERVE ROWS

ログテーブルの作成

一時表の内容は当然他のセッションでは見れませんので、その内容のコピーを記録するログテーブルを作成します。

CREATE TABLE USER1.TMPTBL_LOG (
  "SESSIONID" NUMBER DEFAULT SYS_CONTEXT('USERENV', 'SESSIONID') NOT NULL ENABLE,
  "ID"        CHAR(3)   NOT NULL ENABLE,
  "NAME"      CHAR(128) NOT NULL ENABLE,
  CONSTRAINT TMPTBL_LOG_IDX_00 PRIMARY KEY ("SESSIONID", "ID")
);

一時表と同じテーブル定義にSESSIONID列を追加します。
名前からわかる通りセッションIDを格納します。セッションIDは同時には同じものが存在しない事が保証されますが、時間があくと同じIDを振られる可能性があるので、長時間保存しておく必要がある場合はさらに更新時刻も主キーに追加する必要があります。
（特にPostgreSQLはセッションIDの代わりにサーバプロセスIDを使用するので更新時刻は必須です）

トリガーの追加

一時表（USER1.TMPTBL）にトリガーを追加します。

CREATE OR REPLACE TRIGGER USER1.TMPTBL_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON USER1.TMPTBL FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO USER1.TMPTBL_LOG(ID,NAME) VALUES(:NEW.ID,:NEW.NAME);
  ELSIF UPDATING THEN
    UPDATE USER1.TMPTBL_LOG SET
      ID = :NEW.ID
      , NAME = :NEW.NAME
    WHERE
      SESSIONID = SYS_CONTEXT('USERENV', 'SESSIONID')
      AND ID = :OLD.ID;
  ELSE
    DELETE FROM
      USER1.TMPTBL_LOG
    WHERE
      SESSIONID = SYS_CONTEXT('USERENV', 'SESSIONID')
      AND ID = :NEW.ID;
  END IF;
END;

これで一時表にINSERT、UPDATE、DELETEが発生した際にログテーブルに内容が書き込まれるので、別セッションから内容を確認できる様になりました。

他のデータベースの場合

MySQL

MySQLの場合は、SYS_CONTEXT('USERENV', 'SESSIONID')をCONNECTION_ID()に変更してセッションIDを取得します。

PostgreSQL

PostgreSQLの場合は、SYS_CONTEXT('USERENV', 'SESSIONID')をpg_backend_pid()に変更してサーバプロセスIDを取得します。
前述の注意通りサーバプロセスIDはそこそこ同じIDが割り当てられる可能性があるので更新時刻も主キーに含めて一意制約違反が発生しない様に注意が必要です。

DockerでLaravel環境を構築する②~PHPの導入~の続きです。これで最後です。

MySQLのインストール

次にDBを使えるようにするため、MySQLをインストールしていきます。

docker-compose.yml

services:
#　省略
  mysql:
    image: mysql:5.7
    environment:
      MYSQL_DATABASE: sample
      MYSQL_USER: user
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: password
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql
volumes:
  mysql-data:

environmentはDockerコンテナ内で使用する環境変数を指定できます。
ただしこれで終わりではなく、最後に（laravelをインストールした後に）
今設定したこれらの値を、laravelのアプリ内に設定することで、実際にmysqlを使えるようにしていきます。

また、volumesによって、mysqlのデータを永続化しています。
最終的なdocker-compose.ymlは以下のようになります。

docker-compose.yml

version: '3'
services:
  web:
    image: nginx:1.15.6
    ports:
      - "8000:80"
    depends_on:
      - app
    volumes:
      - ./docker/web/default.conf:/etc/nginx/conf.d/default.conf
      - .:/var/www/html
  app:
    image: php:7.2-fpm
    depends_on:
    - mysql
    volumes:
      - .:/var/www/html
  mysql:
    image: mysql:5.7
    environment:
      MYSQL_DATABASE: sample
      MYSQL_USER: user
      MYSQL_PASSWORD: password
      MYSQL_ROOT_PASSWORD: password
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql
volumes:
  mysql-data:

Laravelのインストール

次は、Laravelをインストールします。

laravelのインストールにはcomporserが必要ですが、最初に作成したPHPにはそれがありません。なので,
imageを取得してPHPを立ち上げるのではなく、「phpのimage+comporserのインストール」ができるDockerfileを作成します。
dockerディレクトリの下にphpというディレクトリを作り、そこにDockerfileを作成し、内容を以下のようにします。

docker/php/Dockerfile ↓

FROM php:7.2-fpm

# install composer
RUN cd /usr/bin && curl -s http://getcomposer.org/installer | php && ln -s /usr/bin/composer.phar /usr/bin/composer
RUN apt-get update \
&& apt-get install -y \
git \
zip \
unzip \
vim

RUN apt-get update \
    && apt-get install -y libpq-dev \
    && docker-php-ext-install pdo_mysql pdo_pgsql

WORKDIR /var/www/html

次に、docker-compose.yml内のappサービスを以下のように書き換え、「build: ./docker/php」で、Dockerにホストされているimageからではなく、
作成したDockerfileからビルドするようにします。

docker-compose.yml

app:
    build: ./docker/php
    depends_on:
      - mysql
    volumes:
      - .:/var/www/html

そして、Nginxの設定(docker/default.conf)のroot(ドキュメントルート、4行目あたり)を書き換えて以下のようにします。

root  /var/www/html/my-laravel-app(自分の作成したlaravelアプリの名前)/public;

これで、nginxをリスタートします。

docker-compose restart

再度アクセスし、

このようになればOKです！

最後に、(MySQLのインストールの続き）
環境変数の各値を、Laravel内のプロジェクトの .envファイルに記入します。

$ cd my-laravel-app(作成したlaravelのアプリに移動)
$ vi .env 

こんな感じで、.envファイルを開き、

以下のようになっている箇所を

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

docker-compose.ymlで定義した内容に合わせ、このように変更してください。

DB_HOSTのところは、docker-composeで定義したサービス名になります。

.env

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=sample
DB_USERNAME=user
DB_PASSWORD=password

以上で、作成したlaravelアプリで、mysqlが使えるようになっています。

nginxをリスタートし

$ docker-compose restart

再度アクセスして、コンテナの中に入ります。

$ docker-compose exec app bash
root@xxxxxxxxx:/var/www/html# 

#で入力待ちになっていればOKです。

作成したlaravelのアプリに移動して、マイグレーションを実行し、以下のようになれば完成です！！

root@xxxxxxxxx:/var/www/html#  cd my-laravel-app(作成したlaravelのアプリに移動)
root@xxxxxxxxx:/var/www/html#  php artisan migrate
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table

DBを利用するだけなのに、DAO、entity、XMLを３セット書かなくちゃ！
フレームワークは面倒くさいと思いつつ、今回のライブラリを作成した。

１．メリット

・XML不要（sql文を記述する設定ファイル）
・DAO不要（sqlを実行するクラス）
・フレーム不要

・・・・・・・

Q：なにも不要？ちゃんとしている機能はないじゃない？？
A：いえいえ、sql及びentityによるDB操作はしっかりサポートしているし、かつ一つクラスで完結！

２．ライブラリ紹介

sqlの作成を手助ける

早速サンプルコードを見ましょう！

    SqlWritter writter = new SqlWritter();
    writter.select("*")
            .from("speech_data")
            .where("name")
            .like("新垣結衣 ニンゲン観察バラエティモニタリング");

    System.out.println(writter);

?コンソール出力

select
*
from
speech_data
where
name LIKE '%新垣結衣 ニンゲン観察バラエティモニタリング%'

Q： ('ω') うわあああ！sql文を生成してくれたねといいたかったところ、どういうメリットがあるか？
A：確かにこれだけだと、メリットはあんまり実感できなさそうだね。しかし、以上の機能をコアとして、さらに便利な機能を提供できるんだって！

sqlによる検索

生成したsqlを用いて、DAO不要のメリットを見てみよう。

    List<SpeechData> entities;
    try (Accessor accessor = new Accessor()) {
        entities= accessor.selectBySql(writter, SpeechData.class);
    }

上記のコードを簡単に説明してみると、上記はsql文による検索操作だ。
　SpeechDataは事前作成したspeech_dataというテーブルのエンティティクラス
　entitiesは結果を格納するためのリスト
　Accessorはデータベースへアクセスするためのクラス
　writterは先ほど生成したSQL

Q：うんーーー、よくわからない！
A：大丈夫、覚えておいてほしいのはAccessorクラスはすべてのエンティティクラスに対応できるため、DAOはもう不要だ！

下記のコードを追加して、実行結果を見てみよう。

    for (SpeechData entity : entities) {
        System.out.println(String.format("ファイル：%sの分析結果：", entity.getName()));
        System.out.print(String.format("喜び：%s,", entity.getJoy()));
        System.out.print(String.format("悲しみ：%s,", entity.getSorrow()));
        System.out.print(String.format("怒り：%s,", entity.getAnger()));
        System.out.print(String.format("エネルギー：%s,", entity.getEnergy()));
        System.out.println(String.format("穏やかさ：%s", entity.getCalm()));
        System.out.println();
    }

?コンソール出力

ファイル：新垣結衣 ニンゲン観察バラエティモニタリング_00.wavの分析結果：
喜び：0,悲しみ：9,怒り：0,エネルギー：0,穏やかさ：40

ファイル：新垣結衣 ニンゲン観察バラエティモニタリング_01.wavの分析結果：
喜び：18,悲しみ：2,怒り：0,エネルギー：16,穏やかさ：29

ファイル：新垣結衣 ニンゲン観察バラエティモニタリング_02.wavの分析結果：
喜び：11,悲しみ：0,怒り：0,エネルギー：11,穏やかさ：38

・
・
・

Q：怪しいデータが出てきた！
A：ごめんごめん、音声による感情識別データだ。データの中身はとりあえず無視して、実行したsqlは無事に結果を戻しれくれた。

エンティティによる検索

フレームのように、エンティティによる検索も可能！

    SpeechData speechData = new SpeechData();
    speechData.setName("新垣結衣 ニンゲン観察バラエティモニタリング_02.wav");

    List<SpeechData> entities;
    try (Accessor accessor = new Accessor()) {
        entities = accessor.selectByEntity(speechData);
    }

    for (SpeechData entity : entities) {
        System.out.println(String.format("ファイル：%sの分析結果：", entity.getName()));
        System.out.print(String.format("喜び：%s,", entity.getJoy()));
        System.out.print(String.format("悲しみ：%s,", entity.getSorrow()));
        System.out.print(String.format("怒り：%s,", entity.getAnger()));
        System.out.print(String.format("エネルギー：%s,", entity.getEnergy()));
        System.out.println(String.format("穏やかさ：%s", entity.getCalm()));
        System.out.println();
    }

?コンソール出力

ファイル：新垣結衣 ニンゲン観察バラエティモニタリング_02.wavの分析結果：
喜び：11,悲しみ：0,怒り：0,エネルギー：11,穏やかさ：38

Q：エンティティによる検索だね
A：はい、ライブラリだけど、機能は半端ない！

３．ライブラリ共有

ライブラリを使ってみたい方は、下記のリンクからソースをダウンロードすることができる。
https://github.com/chaofanzheng/leadinge

わからない時があれば、下記の仕様を参考すれば助けになる！！

４．ライブラリを使用するための仕様

システムプロパティ

system.properties

    #mysqlのユーザID（各自の設定に従う）
    USER_ID = root

    #mysqlのパスワード（各自の設定に従う）
    USER_PASSWORD = MySql

    #mysqlへアクセスのURL（各自の設定に従う）
    DB_URL = jdbc:mysql://localhost:3306/speech_recognition?useSSL=false&&allowPublicKeyRetrieval=true

エンティティ

SpeechData.java

//CommonEntityを継承する必要がある
public class SpeechData extends CommonEntity {

    //コンストラクタ
    public  SpeechData() {
        //データタイプの初期化（テーブル情報に合わせて設定する）
        columnsType = new HashMap<String,Class<?>>();
        columnsType.put("id", int.class);
        columnsType.put("name", String.class);
        columnsType.put("wav", byte[].class);
        columnsType.put("error", int.class);
        columnsType.put("calm", int.class);
        columnsType.put("anger", int.class);
        columnsType.put("joy", int.class);
        columnsType.put("sorrow", int.class);
        columnsType.put("energy", int.class);
        //テーブル名を設定する（スネークケース）
        setTableName("speech_data");
    }

    public int getId() {
        return (int)columns.get("id");
    }

    public void setId(int value) {
         columns.put("id",value);
    }

    public String getName() {
        return (String)columns.get("name");
    }

    public void setName(String value) {
         columns.put("name",value);
    }

    public byte[] getWav() {
        return (byte[])columns.get("wav");
    }

    public void setWav(byte[] value) {
         columns.put("wav",value);
    }

    public int getError() {
        return (int)columns.get("error");
    }

    public void setError(int value) {
         columns.put("error",value);
    }

    public int getCalm() {
        return (int)columns.get("calm");
    }

    public void setCalm(int value) {
         columns.put("calm",value);
    }
    public int getAnger() {
        return (int)columns.get("anger");
    }

    public void setAnger(int value) {
         columns.put("anger",value);
    }

    public int getJoy() {
        return (int)columns.get("joy");
    }

    public void setJoy(int value) {
         columns.put("joy",value);
    }

    public int getSorrow() {
        return (int)columns.get("sorrow");
    }

    public void setSorrow(int value) {
         columns.put("sorrow",value);
    }

    public int getEnergy() {
        return (int)columns.get("energy");
    }

    public void setEnergy(int value) {
         columns.put("energy",value);
    }
}

DBを操作するためには

Select.java

    //sqlを作成した場合は...もちろん、外部ファイルでも手書きでもオケ
    SqlWritter writter = new SqlWritter();
    writter.select("*")
            .from("speech_data")
            .where("name")
            .like("新垣結衣 ニンゲン観察バラエティモニタリング");

    List<SpeechData> entities;

    //DBアクセサの初期化
    try (Accessor accessor = new Accessor()) {
        //sqlによる選択
        entities = accessor.selectBySql(writter, SpeechData.class);

        //エンティティの初期化
        SpeechData speechData = new SpeechData();
        speechData.setName("新垣結衣 ニンゲン観察バラエティモニタリング_02.wav");
        //エンティティによる選択
        entities = accessor.selectByEntity(speechData);
    }

Insert.java

    //sqlを作成した場合は...もちろん、外部ファイルでも手書きでもオケ
    SqlWritter writter = new SqlWritter();  
    writter.insertInto("speech_data")
            .colums("id","name")
            .values(999,"新垣結衣 ニンゲン観察バラエティモニタリング_999.wav");

    //DBアクセサの初期化
    try (Accessor accessor = new Accessor()) {
        //sqlによる選択操作
        accessor.insertBySql(writter);

        //エンティティの初期化
        SpeechData speechData = new SpeechData();
        speechData.setId(999);
        speechData.setName("新垣結衣 ニンゲン観察バラエティモニタリング_999.wav");
        //エンティティによるインサート操作
        accessor.insertByEntity(speechData);
    }

５．ソースコードについて

現状ではinsertとselectしかサポートしていないので、
更新や削除でもできるようにしたい場合は...各自がコードを改修してください。

実際にDBへアクセスするときに使っていただくか、またはソースが参考になれば、
うれしいと思います。