d. データベース設計演習(4/4)

2017年度資料

CakePHP の bookmark チュートリアルの演習は前回までで終了したものとする。

先回の演習の応用例についてコメント：前回の資料に応用例を記載したので参照する。

今回から、期末課題の制作を進める。

期末課題の提出物

・ fp フォルダを zip ファイルにアーカイブして Webclassにアップロード

・データベースのエクスポートファイルを Webclassにアップロード

の２点を期日までに提出する。

■提出〆切

・７月３１日（月）

間に合うように取り組むこと。締切日当日に０からスタートでは難しいと予測。こまめに相談に来ること。

データベースの設計に問題があるか不安な受講生は、確認を求めてください。

■期末課題採点基準：（３０点満点＋）

・データベースの設計正規化・テーブル間の関係（２０点）

・サンプルデータ（５点）

・独自の工夫（カスタマイズビューの作成・操作メニューの作成・Ｗｅｂリンク・並べ替え・集計機能・セキュリティなど）（５点～）

PHPのコード、モデルとビューとコントローラーは、 cake bake で自動的に作成してもよい。

テーブルが１つしかないデータベースの制作でもかまわないが、複数テーブルのデータベースに対して、評価を半減する。

データベースの構造についての減点分を、独自の工夫でカバーするように取り組む。

ログイン認証やセキュリティなど高度な設定については、各自で調べて取り組むことする。

CakePHP のサイトのほか、Ｗｅｂに豊富に情報がある。

https://book.cakephp.org/3.0/ja/index.html

作業例

bookmarkチュートリアルを作成したときと同様に、CakePHP用のフォルダを作成しCakePHPを設定する。

※レポートフォルダの fp（期末課題用CakePHP）を丸ごとxamppのhtdocsにコピーして、名前を修正 fp としておく。

期末課題の作成デモ：

ボードゲーム貸し出し管理データベースの作成

作業の概要：

d:\xampp\htdocs\ に cakephpのフォルダをレポートフォルダからコピーして、フォルダ名を fp に変更する。

PHPMyAdminで 期末課題データベースを作成する。

PHPMyAdminの新規テーブル作成 か SQL コマンドで、テーブルを作成する。

cake bake コマンドを使用して、モデル、コントローラー、ビューの生成を行う。

・データベースを切り替える。

CakePHPとMySQLの接続設定を編集

vscode で fp/config/app.php

を編集。データベースの名前は、各自の企画のものを指定する。

232～234行目付近。赤文字の部分、データベースのユーザ名やパスワードは例のとおりに設定する。データベース名（太字）の部分は各自のデータベースに合わせて修正する。

return [

    // More configuration above.

    'Datasources' => [

        'default' => [

            'className' => 'Cake\Database\Connection',

            'driver' => 'Cake\Database\Driver\Mysql',

            'persistent' => false,

            'host' => 'localhost',

            'username' => 'root',

            'password' => '',

            'database' => 'boardgame',

            'encoding' => 'utf8',

            'timezone' => 'UTC',

            'cacheMetadata' => true,

],

],

    // More configuration below.

];

保存する。

・xampp の phpMyadmin でデータベースとテーブルを作成する。

データベース名と文字コードを指定して作成

テーブルをSQLで作成:

※ CakePHPの規約 テーブル名は複数形を指定する（単語の末尾に s）。フィールド名は単数形を指定する。

CakePHPによる開発で使用するテーブル設計のコツ

以下の規約に従うと、苦労が少ない。

・テーブル名は英単語の複数形を使う

・テーブル名とフィールド名には PHPやMySQLの予約語を使用しない。

使用できない単語の例） for select list character release など

・テーブル名は単語を2個以上使わない。どうしても2個以上の単語で名前をつける際は、スネークケース（ _ で単語をつなぐ）

・フィールド id を主キーとして持つ。auto_incremental 設定もしておく。

・フィールド名は単語を2個以上使わない。どうしても2個以上の単語で名前をつける際は、スネークケース（ _ で単語をつなぐ）

・外部キーをフィールドとして持つ場合、別のテーブル名（複数形）のidフィールドを参照するものとする。

フィールド名は別のテーブル名の単数形_id とする。

・テーブル間の多対多をあらわすテーブルの名前はテーブル名１_テーブル名2 とする。テーブル名は複数形のままつかう。テーブル名を並べる順番はアルファベット順。

ゲームテーブル： ID ゲーム名説明を登録

create table games (

     id int not null auto_increment primary key,

     name varchar(20) not null,

     description text,

     created datetime,

     modified datetime

);

※ 赤文字の部分、テーブル名 games（複数形）やフィールド名 name （単数形）は、各自の企画に合わせて変更が必要。

※ id フィールドは必須。上記の例同様に各自の企画のテーブルにも id フィールドを設定する。

利用者テーブル： ID 利用者名を登録

create table users (

     id int not null auto_increment primary key,

     name varchar(20) not null,

     created datetime,

modified datetime

);

※ 上記以外のフィールドを追加してもよい。

貸出テーブル：ゲームID 利用者ID 日付を記録 ※規約に従っていないので、来週にコードを修正して対応する。

create table games_users (

user_id int not null,

game_id int not null,

    date datetime,

       PRIMARY KEY (game_id, user_id),

    FOREIGN KEY user_key(user_id) REFERENCES users(id),

    FOREIGN KEY game_key(game_id) REFERENCES games(id)

);

※ テーブル名 games_usersはこのテーブルは games テーブルと users テーブルの多対多の関係を記録するテーブルであるので、テーブル名をアルファベット順に並べて _ でつないだ名前をつける（CakePHPの規約）。

※ 外部キーの設定 user_id や game_id の部分は、テーブル名を単数形にして _id を付加したフィールド名を用いている（CakePHPの規約）。複合主キーの設定（PRIMARY KEY）や、外部キーの設定（FOREIGN KEY）の設定は、対応するフィールドに応じて名前を変更すること。

↑

貸し出しテーブルの設計上の問題点：

bookmarkチュートリアルの設定を流用しているので発生

ゲームを返却した場合は、貸し出し記録を削除する。貸し出し履歴は保存されない。仕様。

user_id と game_id が複合キーで主キーとなっている。ゲームと利用者は多対多の関係である：

１人で複数のゲームを借りることが出来る

１つのゲームを複数の利用者で共有して借りることが出来る

ゲームを返却するには、借りた利用者全ての記録を削除する必要がある

ある利用者が借りているゲームを返却することなくもう一度借りることは出来ない。 ←これはOK

※テーブルの構造に関する CakePHPの規約

・テーブルの外部キーのフィールド名は、参照先テーブル users の主キーが id なら user_id という名前を指定する。

【参照】 CakePHPの命名規約まとめ http://qiita.com/nvtomo1029/items/1147b8796af3d906c3a9

サンプルデータの入力

各テーブルに動作確認用のデータを入力する。

日付と時刻の入力には、関数の NOW() を指定するとよい。現在の日付と時刻を入力できる。

以上で、課題作成の要求水準２５点クリア

残り５点（３０点満点）は、各自の工夫を評価する。

課題提出

・期末課題のデータベースのER図

xampp のデザイナ画面を利用して、スクリーンショットを撮影して提出。画像を Webclass にアップロード。

デザイナ画面は、テーブルの関連が分かるように整理してからスクリーンショットを撮影すること。

・データベースのスナップショット

xampp からデータベースをエクスポートする。

127.0.0.1.sql というファイルがブラウザにダウンロードされる。そのまま Webclass にアップロードする。

2016年度資料

メモ：

データベース mytest の提出について。

phpMyAdmin からデータベース mytest学籍番号 をエクスポートしてレポートフォルダに提出。

ページの一番下に、【裏技】解説があります。

お勧めのテキストエディター： sublime text 3 。ただし日本語入力がインライン化されていないので要対応。

やり方→proxy の設定が必要 http://keidrun.tumblr.com/post/73152267421/tipssublime-text-sublime-text-3%E3%81%A7package

やり方→http://keidrun.tumblr.com/post/73033053898/tipssublime-text-sublime-text

期末課題の作成：

作成ファイル

Controller

xxxxController.php -> scaffold のみのシンプルなものでよい。

Model

xxxxxxx.php

テーブルが2つ以上ある場合、テーブル間の関係（１対多など）を、アソシエーション機能で設定しておくこと。

今回の授業では、なるべく楽をして課題を作成する方法について解説予定。

ポイント１ CakePHP の設定を簡単に済ます。 Blogシステムの設定を流用する。

ポイント２a PHPプログラムをしない。 Controller と Model を必要最低限で用意する。ビューは scaffold のレイアウトを利用する。今回解説する方式。

ポイント２b PHPプログラムをしない。 CakePHP の bake コマンドで、必要なコードを自動生成する。次回解説する方式。

ポイント３ CakePHPの解説サイトをネットで検索、CakePHP ＋やりたいこと（例：ユーザ認証、ページ付け、画像投稿、カレンダー、など）のキーワードでヒットした記事を参考にする。

■期末課題の提出物

blog を作成したときと同様に、データベース名のフォルダを作成し、CakePHPを準備する。

・学籍番号＋データベース名のフォルダ

例） 3113999boardgame

・学籍番号＋データベース名.sql のエクスポートファイル

例） 3113999boardgame.sql

の２点を期日までに提出する。

■提出〆切

・７月３１日（日）

間に合うように取り組むこと。締切日当日に０からスタートでは難しいと予測。相談に来ること。

■期末課題採点基準：（３０点満点＋）

・データベースの設計正規化・テーブル間の関係（２０点）

・サンプルデータ（５点）

・独自の工夫（カスタマイズビューの作成・操作メニューの作成・Ｗｅｂリンク・並べ替え・集計機能・セキュリティなど）（５点～）

ビューとコントローラーは、 scaffold で単純に作成してもよい。

テーブルが１つしかないデータベースの制作でもかまわないが、複数テーブルのデータベースに対して、評価を半減する。

データベースの構造についての減点分を、独自の工夫でカバーするように取り組む。

ログイン認証やセキュリティなど高度な設定については、各自で調べて取り組むとする。

CakePHP のサイトのほか、Ｗｅｂに豊富に情報がある。

http://book.cakephp.org/2.0/ja/getting-started.html

作業例

新規に CakePHP を htdocs に設定して課題を作成してもよい。

しかし、設定の手間を省くため、

ここでは、第１０～１２回(a. b. c.) で作成した blog システムをコピーして修正しながら作業を進める。

ボードゲーム貸し出し管理データベースの作成

d:\xampp\htdocs\ に cakephpのフォルダを新規にコピーして、フォルダ名を期末課題の名前に変更する。

PHPMyAdminで 期末課題データベースを作成する。

PHPMyAdminの新規テーブル作成 か SQL コマンドで、テーブルを作成する。

bake コマンドを使用して、database.php、モデル、コントローラー、ビューの生成を行う。

以下は、２０１５－２０１６年度版の方法

・D:ドライブのXAMPP のドキュメントルート(htdocs) にある、blog フォルダをコピーして名前を変更する。

blog → boardgame

フォルダの名前は、各自の企画のものを指定する。

・データベースを切り替える。

D:\XAMPP\htdocs\boardgame\app\Config の database.php を編集

'database' => 'blog',

を

'database' => 'boardgame',

に変更。データベースの名前は、各自の企画のものを指定する。

・CakePHPの debugレベルが 0 になっていたら、 2 に戻し（システム完成後０にする）、

次の Blog を参考に、モデルのキャッシュファイルを削除する。

http://www.hangout.co.jp/blog/archives/145

例）

D:\XAMPP\htdocs\boardgame\app\tmp\cache\models にある、

myapp_cake_model_default_データベース名_テーブル名

のファイルを削除。

・データベースとテーブルを作成する。

データベース名と文字コードを指定して作成

テーブルをSQLで作成:

※ CakePHPの規約 テーブル名は複数形を指定する（単語の末尾に s）。フィールド名は単数形を指定する。

ゲームテーブル： ID ゲーム名説明を登録

create table games (

     id int not null auto_increment primary key,

     name varchar(20) not null,

     description text

);

利用者テーブル： ID 利用者名を登録

create table users (

     id int not null auto_increment primary key,

name varchar(20) not null

);

貸出テーブル：利用者ID ゲームID 日付を記録

create table rentals (

    id int not null auto_increment primary key,

    user_id int not null,

    game_id int not null,

    date datetime

);

※テーブルの構造に関する CakePHPの規約

・テーブルの外部キーのフィールド名は、参照先テーブル users の主キーが id なら user_id という名前を指定する。

・複合キーはテーブルに持たせない。

【参照】 CakePHPの命名規約まとめ http://qiita.com/nvtomo1029/items/1147b8796af3d906c3a9

サンプルデータの入力

各テーブルに動作確認用のデータを入力する。

Modelの準備：

※ ファイル名の先頭と単語の先頭は大文字。単数形。

Game.php

アソシエーションの設定。ゲームと貸出は１対多の関係。あるゲームは何度も貸し出可能。$hasManyを指定する。

<?php

class Game extends AppModel {

public $hasMany = 'Rental';

User.php

アソシエーションの設定。利用者と貸出は１対多の関係。ある利用者はゲームを何度も借りられる。

<?php

class User extends AppModel {

public $hasMany = 'Rental';

Rental.php

アソシエーションの設定。貸出とゲーム、貸出と利用者は、多対１の関係。 $belongsToを以下のように指定する。

<?php

class Rental extends AppModel {

public $belongsTo = array(

'User', 'Game'

);

【参照】モデルのアソシエーション設定のまとめ http://qiita.com/rana_kualu/items/1be3457e7b58950399b9

Controllerの設定：

ビューは作成せずに scaffold を利用して、モデルとデータベースの接続を確認する。

※ ファイル名コントローラー名は複数形（モデル名の末尾にｓがつく）単語の先頭は大文字。

GamesController.php の準備。

<?php

class GamesController extends AppController {

public $scaffold;

}

以下のURLをブラウザで表示確認： urlの一部がコントローラー名に対応していることに注意。

http://localhost/boardgame/games/

UsersController.php と RentalsController.php も同様に設定し、動作を確認する。

※cakePHPの scaffold 機能では、テーブルのフィールドに name か title があると、その値をレコードの表示名として利用するようになっている。

上記の表示例では、 Rentals テーブルのレコードで、 user_id と game_id の部分が、 users テーブルと games テーブルの name フィールドの値で表示されている。

※※ scaffoldで、テーブルの表示名用フィールドを name や title 以外に変更するには、 cakePHPのモデルを修正する。

例）

モデルに以下の行を追加

public $displayField = 'custom_name';

↑で'custom_name' の部分を、scaffoldのプルダウンメニューで表示される項目のフィールドに修正する。

以上で、課題作成の要求水準２５点クリア

残り５点（３０点満点）は、各自の工夫を評価する。

例えば、

検索： CakePHP Scaffold カスタマイズ

で、スキャフォールドの修正方法を探して、設定する。表示項目の選択（不要なものをＯＦＦ）や、表示順の並べ替えなど。

(ヒント) scaffold 機能を利用したままで、カスタムビューを作成せずに工夫できる内容を考える。

・デバッグモードを 0 にして、SQLの実行文や、エラーを非表示に設定する。（本番運用環境設定）

・D:\XAMPP\htdocs\boardgame\app\View\Layouts の default.ctp を編集して、Webページの

ヘッダーやフッター、背景のデザインを修正する。 HOME のリンク先をTOP ページに変更など。

・ルーティングの設定

例）

http://localhost/boardgame/ の短いURLで、 http://localhost/boardgame/rentals/ を表示するように、

app/Config/routes.php を修正。

・データの表示順の指定

例）

上のボードゲーム貸出し管理DBで、貸し出し状況の一覧表示を日付の降順にするには、Rental.php モデルを修正する。

$order にモデル名．並べ替えるフィールド名 ASC（昇順）または DESC（降順を指定）

class Rental extends AppModel {

public $order = "Rental.Date DESC";

・表示項目の設定

MySQLで表示項目を制限するテーブルのビューを作成する。

例）

ゲームテーブルの表示で、id name description の３項目の表示を、 id name だけの表示に制限する。

MySQLに、 games に対するビュー game2s を作成して登録する。

create view game2s as

SELECT `games`.`id`, `games`.`name`

FROM `games`

ORDER BY `games`.`name` ASC

CakePHP に、モデル Game2 とコントローラー Game2sController を作成する。

※ Scaffold の利用したビューの作成では、モデルの id フィールドを省略するとエラーになる。

チャレンジ：以下の作業は、調査・デバッグで、かなりの時間を必要とします。時間に余裕がある場合は挑戦してみましょう。

・ビューの作成

Blogシステムの作成と同様に、ビューを作成する。 Scaffold をオフにして、PHPのプログラムで表示画面を作成する。

・ログイン画面の作成

CakePHPで、ログイン認証を設定する方法を調べ、ログイン画面を作成する。

・WebAPIの利用

データベースに登録したフィールドの内容と、 WebAPIを組み合わせ、ビューで利用する。

例）位置情報、商品画像、etc

裏技１： blog データベース全体をエクスポートし、別のデータベースとしてインポートできるかもしれない。そうした場合、blogシステムのクローンが作成される。テーブルのフィールド名を再設計すれば、blogシステムを改造して、期末課題が作成できるかもしれない。

裏技２： CakePHP の bake コマンドで、phpやctpのコードを自動生成。楽ちんコースはこちら。