概要
データベースを作成し中身を入れてブラウザで表示するところまで行っていく。
目次
- データベース
- スキーマ
- The ORM
- ブラウザーで確認する
データモデル
データベース
データベース接続パラメータを定義するには、（MySQL使用） app/config/parameters.yml ファイルを編集する必要があるみたいよ。
app/config/parameters.yml
parameters:
database_host: 127.0.0.1
database_port: null
database_name: jobeet
database_user: root
database_password: ******（自分のPC設定）
...
データベースを作成しようとします。エラーに遭遇します。MySQLの立ち上げ忘れなので、立ち上げます。
$ php app/console doctrine:database:create
[Doctrine\DBAL\Exception\ConnectionException]
An exception occured in driver: SQLSTATE[HY000] [2002] Connection refused
[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[HY000] [2002] Connection refused
[PDOException]
SQLSTATE[HY000] [2002] Connection refused
doctrine:database:create [--shard SHARD] [--connection [CONNECTION]] [--if-not-exists] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-s|--shell] [--process-isolation] [-e|--env ENV] [--no-debug] [--] <command>
$ mysql.server start
Starting MySQL
.. SUCCESS!
よしこれで立ち上がるはずなので再度試すものの怒られる。
$ php app/console doctrine:database:create
[Doctrine\DBAL\Exception\ConnectionException]
An exception occured in driver: SQLSTATE[HY000] [2002] No such file or directory
[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[HY000] [2002] No such file or directory
[PDOException]
SQLSTATE[HY000] [2002] No such file or directory
doctrine:database:create [--shard SHARD] [--connection [CONNECTION]] [--if-not-exists] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-s|--shell] [--process-isolation] [-e|--env ENV] [--no-debug] [--] <command>
何かの設定がおかしい。下記のサイトを見てみるとapp/config/parameters.ymlが間違っているっぽいので、修正する。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11128812741
「127.0.0.1からlocalhostに変えて試してみてください」ということらしいので、修正してみる。
app/config/parameters.yml
# This file is auto-generated during the composer install
parameters:
database_host: localhost
database_port: null
database_name: jobeet
database_user: root
database_password: ****** （自分の環境に合わせて）
mailer_transport: smtp
mailer_host: localhost
mailer_user: null
mailer_password: null
secret: 1930c1d803af0c0bd2c4c1b6c8db8c9248835da0
再度データベース作成のコマンドを叩くと無事に作成完了。
$ php app/console doctrine:database:create
Created database `jobeet` for connection named default
スキーマ
Doctrine にオブジェクトを教えるために、「メタデータ」ファイルを作成していく。
まずはsrc/Ibw/JobeetBundle/Resources/configディレクトリの中に doctrineという名前のディレクトリを作成する。その中にCategory.orm.yml、Job.orm.ymlとAffiliate.orm.ymlを作成して中身を下記のようにする。
#src/Ibw/JobeetBundle/Resources/config/doctrine/Category.orm.yml
Ibw\JobeetBundle\Entity\Category:
type: entity
table: category
id:
id:
type: integer
generator: { strategy: AUTO }
fields:
name:
type: string
length: 255
unique: true
oneToMany:
jobs:
targetEntity: Job
mappedBy: category
manyToMany:
affiliates:
targetEntity: Affiliate
mappedBy: categories
#src/Ibw/JobeetBundle/Resources/config/doctrine/Job.orm.yml
Ibw\JobeetBundle\Entity\Job:
type: entity
table: job
id:
id:
type: integer
generator: { strategy: AUTO }
fields:
type:
type: string
length: 255
nullable: true
company:
type: string
length: 255
logo:
type: string
length: 255
nullable: true
url:
type: string
length: 255
nullable: true
position:
type: string
length: 255
location:
type: string
length: 255
description:
type: text
how_to_apply:
type: text
token:
type: string
length: 255
unique: true
is_public:
type: boolean
nullable: true
is_activated:
type: boolean
nullable: true
email:
type: string
length: 255
expires_at:
type: datetime
created_at:
type: datetime
updated_at:
type: datetime
nullable: true
manyToOne:
category:
targetEntity: Category
inversedBy: jobs
joinColumn:
name: category_id
referencedColumnName: id
lifecycleCallbacks:
prePersist: [ setCreatedAtValue ]
preUpdate: [ setUpdatedAtValue ]
#src/Ibw/JobeetBundle/Resources/config/doctrine/Affiliate.orm.yml
Ibw\JobeetBundle\Entity\Affiliate:
type: entity
table: affiliate
id:
id:
type: integer
generator: { strategy: AUTO }
fields:
url:
type: string
length: 255
email:
type: string
length: 255
unique: true
token:
type: string
length: 255
is_active:
type: boolean
nullable: true
created_at:
type: datetime
manyToMany:
categories:
targetEntity: Category
inversedBy: affiliates
joinTable:
name: category_affiliate
joinColumns:
affiliate_id:
referencedColumnName: id
inverseJoinColumns:
category_id:
referencedColumnName: id
lifecycleCallbacks:
prePersist: [ setCreatedAtValue ]
The ORM
Doctrine は以下のコマンドを使用してオブジェクトを定義するクラスを生成します。
$ php app/console doctrine:generate:entities IbwJobeetBundle
Generating entities for bundle "IbwJobeetBundle"
> backing up Affiliate.php to Affiliate.php~
> generating Ibw\JobeetBundle\Entity\Affiliate
> backing up Category.php to Category.php~
> generating Ibw\JobeetBundle\Entity\Category
> backing up Job.php to Job.php~
> generating Ibw\JobeetBundle\Entity\Job
src/Ibw/JobeetBundle/Entity/Job.phpでcreated_atとupdated_atに以下のように修正する。Affiliate.phpも修正する。
#src/Ibw/JobeetBundle/Entity/Job.php
// ...
/**
* @ORM\PrePersist
*/
public function setCreatedAtValue()
{
if(!$this->getCreatedAt()) {
$this->created_at = new \DateTime();
}
}
/**
* @ORM\PreUpdate
*/
public function setUpdatedAtValue()
{
$this->updated_at = new \DateTime();
}
src/Ibw/JobeetBundle/Entity/Affiliate.php
// ...
/**
* @ORM\PrePersist
*/
public function setCreatedAtValue()
{
$this->created_at = new \DateTime();
}
// ...
これによって、オブジェクトの保存または更新をするときに、 Doctrineがcreated_atを設定しupdated_atを更新するようになる。
アップデートされるものを確認していくと大丈夫そう。
$ php app/console doctrine:schema:update --dump-sql
CREATE TABLE affiliate (id INT AUTO_INCREMENT NOT NULL, url VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, token VARCHAR(255) NOT NULL, is_active TINYINT(1) DEFAULT NULL, created_at DATETIME NOT NULL, UNIQUE INDEX UNIQ_597AA5CFE7927C74 (email), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE category_affiliate (affiliate_id INT NOT NULL, category_id INT NOT NULL, INDEX IDX_9E1A77FF9F12C49A (affiliate_id), INDEX IDX_9E1A77FF12469DE2 (category_id), PRIMARY KEY(affiliate_id, category_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE category (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, UNIQUE INDEX UNIQ_64C19C15E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE job (id INT AUTO_INCREMENT NOT NULL, category_id INT DEFAULT NULL, type VARCHAR(255) DEFAULT NULL, company VARCHAR(255) NOT NULL, logo VARCHAR(255) DEFAULT NULL, url VARCHAR(255) DEFAULT NULL, position VARCHAR(255) NOT NULL, location VARCHAR(255) NOT NULL, description LONGTEXT NOT NULL, how_to_apply LONGTEXT NOT NULL, token VARCHAR(255) NOT NULL, is_public TINYINT(1) DEFAULT NULL, is_activated TINYINT(1) DEFAULT NULL, email VARCHAR(255) NOT NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME DEFAULT NULL, UNIQUE INDEX UNIQ_FBD8E0F85F37A13B (token), INDEX IDX_FBD8E0F812469DE2 (category_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
ALTER TABLE category_affiliate ADD CONSTRAINT FK_9E1A77FF9F12C49A FOREIGN KEY (affiliate_id) REFERENCES affiliate (id);
ALTER TABLE category_affiliate ADD CONSTRAINT FK_9E1A77FF12469DE2 FOREIGN KEY (category_id) REFERENCES category (id);
ALTER TABLE job ADD CONSTRAINT FK_FBD8E0F812469DE2 FOREIGN KEY (category_id) REFERENCES category (id);
$ php app/console doctrine:schema:update --force
Updating database schema...
Database schema updated successfully! "7" queries were executed
これでテーブルが作成された。
一応mysqlで確認をしておく。
mysql> use jobeet
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables
-> ;
+--------------------+
| Tables_in_jobeet |
+--------------------+
| affiliate |
| category |
| category_affiliate |
| job |
+--------------------+
4 rows in set (0.01 sec)
テーブルの中身もどうやらあるらしい。
mysql> SHOW COLUMNS FROM job;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| category_id | int(11) | YES | MUL | NULL | |
| type | varchar(255) | YES | | NULL | |
| company | varchar(255) | NO | | NULL | |
| logo | varchar(255) | YES | | NULL | |
| url | varchar(255) | YES | | NULL | |
| position | varchar(255) | NO | | NULL | |
| location | varchar(255) | NO | | NULL | |
| description | longtext | NO | | NULL | |
| how_to_apply | longtext | NO | | NULL | |
| token | varchar(255) | NO | UNI | NULL | |
| is_public | tinyint(1) | YES | | NULL | |
| is_activated | tinyint(1) | YES | | NULL | |
| email | varchar(255) | NO | | NULL | |
| expires_at | datetime | NO | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
17 rows in set (0.00 sec)
テーブルの中身はできているが、まだデータは何も入っていないので初期データをいれていく。
mysql> select * from job;
Empty set (0.00 sec)
composer.json ファイルの require セクションに以下を追加する。
// ...
"require": {
// ...
"doctrine/doctrine-fixtures-bundle": "dev-master",
"doctrine/data-fixtures": "dev-master"
},
// ...
ベンダライブラリを更新します。php5.5を使っていたら怒られたので、php5.6に切り替える。
$ php composer.phar update
Loading composer repositories with package information
Updating dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.
Problem 1
- Installation request for doctrine/data-fixtures dev-master -> satisfiable by doctrine/data-fixtures[dev-master].
- doctrine/data-fixtures dev-master requires php ^5.6 || ^7.0 -> your PHP version (5.5.38) does not satisfy that requirement.
一度pho5.5のバージョンをunlinkする。
$ brew unlink php55
Unlinking /usr/local/Cellar/php55/5.5.38_11... 17 symlinks removed
5.6をインストールする。
$ brew install php56
Updating Homebrew...
==> Auto-updated Homebrew!
Updated 3 taps (caskroom/cask, homebrew/core, homebrew/services).
==> New Formulae
druid jhipster
==> Updated Formulae
ammonite-repl colordiff fop libuv nghttp2 qt strongswan yaz
aws-sdk-cpp cromwell freeradius-server libvidstab node radare2 terragrunt yle-dl
awscli dmenu gmic logtalk node-build rocksdb tmux
babl docker-credential-helper grafana mame pachi rom-tools typescript
bear fluent-bit grails mpg123 pgpdump sbcl wartremover
ckan fonttools highlight mutt qcachegrind shc xmount
...
再度php composer.phar updateを試す。
$ php composer.phar update
Loading composer repositories with package information
Updating dependencies (including require-dev)
Package operations: 0 installs, 0 updates, 0 removals
Generating autoload files
> Incenteev\ParameterHandler\ScriptHandler::buildParameters
Updating the "app/config/parameters.yml" file
> Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::buildBootstrap
> Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::clearCache
// Clearing the cache for the dev environment with debug true
[OK] Cache for the "dev" environment (debug=true) was successfully cleared.
> Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::installAssets
Trying to install assets as relative symbolic links.
--- -------------------------- ------------------
Bundle Method / Error
--- -------------------------- ------------------
✔ FrameworkBundle relative symlink
✔ SensioDistributionBundle relative symlink
--- -------------------------- ------------------
[OK] All assets were successfully installed.
> Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::installRequirementsFile
> Sensio\Bundle\DistributionBundle\Composer\ScriptHandler::prepareDeploymentTarget
今度はうまくいきました。app/AppKernel.phpにバンドルDoctrineFixturesBundleを登録する。
app/AppKernel.php
// ...
public function registerBundles()
{
$bundles = array(
// ...
new Doctrine\Bundle\FixturesBundle\DoctrineFixturesBundle()
);
// ...
}
今ではすべてがセットアップされ、データをロードするために、バンドル内のsrc/Ibw/JobeetBundle/DataFixtures/ORMという名前の新しいフォルダに、いくつかの新しいクラスを作成します。
src/Ibw/JobeetBundle/DataFixtures/ORM/LoadCategoryData.php
<?php
namespace Ibw\JobeetBundle\DataFixtures\ORM;
use Doctrine\Common\Persistence\ObjectManager;
use Doctrine\Common\DataFixtures\AbstractFixture;
use Doctrine\Common\DataFixtures\OrderedFixtureInterface;
use Ibw\JobeetBundle\Entity\Category;
class LoadCategoryData extends AbstractFixture implements OrderedFixtureInterface
{
public function load(ObjectManager $em)
{
$design = new Category();
$design->setName('Design');
$programming = new Category();
$programming->setName('Programming');
$manager = new Category();
$manager->setName('Manager');
$administrator = new Category();
$administrator->setName('Administrator');
$em->persist($design);
$em->persist($programming);
$em->persist($manager);
$em->persist($administrator);
$em->flush();
$this->addReference('category-design', $design);
$this->addReference('category-programming', $programming);
$this->addReference('category-manager', $manager);
$this->addReference('category-administrator', $administrator);
}
public function getOrder()
{
return 1; // the order in which fixtures will be loaded
}
}
src/Ibw/JobeetBundle/DataFixtures/ORM/LoadJobData.php
<?php
namespace Ibw\JobeetBundle\DataFixtures\ORM;
use Doctrine\Common\Persistence\ObjectManager;
use Doctrine\Common\DataFixtures\AbstractFixture;
use Doctrine\Common\DataFixtures\OrderedFixtureInterface;
use Ibw\JobeetBundle\Entity\Job;
class LoadJobData extends AbstractFixture implements OrderedFixtureInterface
{
public function load(ObjectManager $em)
{
$job_sensio_labs = new Job();
$job_sensio_labs->setCategory($em->merge($this->getReference('category-programming')));
$job_sensio_labs->setType('full-time');
$job_sensio_labs->setCompany('Sensio Labs');
$job_sensio_labs->setLogo('sensio-labs.gif');
$job_sensio_labs->setUrl('http://www.sensiolabs.com/');
$job_sensio_labs->setPosition('Web Developer');
$job_sensio_labs->setLocation('Paris, France');
$job_sensio_labs->setDescription('You\'ve already developed websites with symfony and you want to work with Open-Source technologies. You have a minimum of 3 years experience in web development with PHP or Java and you wish to participate to development of Web 2.0 sites using the best frameworks available.');
$job_sensio_labs->setHowToApply('Send your resume to fabien.potencier [at] sensio.com');
$job_sensio_labs->setIsPublic(true);
$job_sensio_labs->setIsActivated(true);
$job_sensio_labs->setToken('job_sensio_labs');
$job_sensio_labs->setEmail('job@example.com');
$job_sensio_labs->setExpiresAt(new \DateTime('+30 days'));
$job_extreme_sensio = new Job();
$job_extreme_sensio->setCategory($em->merge($this->getReference('category-design')));
$job_extreme_sensio->setType('part-time');
$job_extreme_sensio->setCompany('Extreme Sensio');
$job_extreme_sensio->setLogo('extreme-sensio.gif');
$job_extreme_sensio->setUrl('http://www.extreme-sensio.com/');
$job_extreme_sensio->setPosition('Web Designer');
$job_extreme_sensio->setLocation('Paris, France');
$job_extreme_sensio->setDescription('Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in.');
$job_extreme_sensio->setHowToApply('Send your resume to fabien.potencier [at] sensio.com');
$job_extreme_sensio->setIsPublic(true);
$job_extreme_sensio->setIsActivated(true);
$job_extreme_sensio->setToken('job_extreme_sensio');
$job_extreme_sensio->setEmail('job@example.com');
$job_extreme_sensio->setExpiresAt(new \DateTime('+30 days'));
$em->persist($job_sensio_labs);
$em->persist($job_extreme_sensio);
$em->flush();
}
public function getOrder()
{
return 2; // the order in which fixtures will be loaded
}
}
fixtures が一度書き込まれたら、コマンドラインの doctrine:fixtures:load コマンドを使用して、それらをロードする。
$ php app/console doctrine:fixtures:load
一度データを確認してみると、確かに入っている。
mysql> select * from job;
+----+-------------+-----------+----------------+--------------------+--------------------------------+---------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+--------------------+-----------+--------------+-----------------+---------------------+---------------------+------------+
| id | category_id | type | company | logo | url | position | location | description | how_to_apply | token | is_public | is_activated | email | expires_at | created_at | updated_at |
+----+-------------+-----------+----------------+--------------------+--------------------------------+---------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+--------------------+-----------+--------------+-----------------+---------------------+---------------------+------------+
| 1 | 2 | full-time | Sensio Labs | sensio-labs.gif | http://www.sensiolabs.com/ | Web Developer | Paris, France | You've already developed websites with symfony and you want to work with Open-Source technologies. You have a minimum of 3 years experience in web development with PHP or Java and you wish to participate to development of Web 2.0 sites using the best frameworks available. | Send your resume to fabien.potencier [at] sensio.com | job_sensio_labs | 1 | 1 | job@example.com | 2017-07-01 12:31:16 | 2017-06-01 12:31:16 | NULL |
| 2 | 1 | part-time | Extreme Sensio | extreme-sensio.gif | http://www.extreme-sensio.com/ | Web Designer | Paris, France | Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in. | Send your resume to fabien.potencier [at] sensio.com | job_extreme_sensio | 1 | 1 | job@example.com | 2017-07-01 12:31:16 | 2017-06-01 12:31:16 | NULL |
+----+-------------+-----------+----------------+--------------------+--------------------------------+---------------+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------+--------------------+-----------+--------------+-----------------+---------------------+---------------------+------------+
2 rows in set (0.00 sec)
ブラウザーで確認する
新しいコントローラーsrc/Ibw/JobeetBundle/Controllers/JobController.phpを作成する。
$ php app/console doctrine:generate:crud --entity=IbwJobeetBundle:Job --route-prefix=ibw_job --with-write --format=yml
Welcome to the Doctrine2 CRUD generator
This command helps you generate CRUD controllers and templates.
First, give the name of the existing entity for which you want to generate a CRUD
(use the shortcut notation like AcmeBlogBundle:Post)
The Entity shortcut name [IbwJobeetBundle:Job]: IbwJobeetBundle:Job
By default, the generator creates two actions: list and show.
You can also ask it to generate "write" actions: new, update, and delete.
Do you want to generate the "write" actions [yes]?
Determine the format to use for the generated CRUD.
Configuration format (yml, xml, php, or annotation) [yml]:
Determine the routes prefix (all the routes will be "mounted" under this
prefix: /prefix/, /prefix/new, ...).
Routes prefix [/ibw_job]:
Summary before generation
You are going to generate a CRUD controller for "IbwJobeetBundle:Job"
using the "yml" format.
Do you confirm generation [yes]?
CRUD generation
created ./src/Ibw/JobeetBundle/Controller//JobController.php
created ./app/Resources/views/job/
created ./app/Resources/views/job/index.html.twig
created ./app/Resources/views/job/show.html.twig
created ./app/Resources/views/job/new.html.twig
created ./app/Resources/views/job/edit.html.twig
created ./src/Ibw/JobeetBundle/Tests/Controller/
created ./src/Ibw/JobeetBundle/Tests/Controller//JobControllerTest.php
created ./src/Ibw/JobeetBundle/Resources/config/routing/
created ./src/Ibw/JobeetBundle/Resources/config/routing/job.yml
Generating the CRUD code: OK
created ./src/Ibw/JobeetBundle/Form/
created ./src/Ibw/JobeetBundle/Form/JobType.php
Generating the Form code: OK
Updating the routing: Confirm automatic update of the Routing [yes]?
Importing the CRUD routes: updated ./src/Ibw/JobeetBundle/Resources/config/routing.yml
[ERROR] The bundle's "Resources/config/routing.yml" file cannot be imported
from "app/config/routing.yml" because the "IbwJobeetBundle" bundle is
already imported. Make sure you are not using two different
configuration/routing formats in the same bundle because it won't work.
OK
Everything is OK! Now get to work :).
src/Ibw/JobeetBundle/Resources/config/routing.ymlを修正する。
src/Ibw/JobeetBundle/Resources/config/routing.yml
IbwJobeetBundle_job:
resource: "@IbwJobeetBundle/Resources/config/routing/job.yml"
prefix: /job
...
ジョブ編集フォームからドロップダウンでカテゴリを編集するため、 Category クラスに _toString() メソッドを追加する。
src/Ibw/JobeetBundle/Entity/Category.php
// ...
public function __toString()
{
return $this->getName() ? $this->getName() : "";
}
// ...
キャッシュクリアを行う。
$ php app/console cache:clear --env=dev
// Clearing the cache for the dev environment with debug true
[OK] Cache for the "dev" environment (debug=true) was successfully cleared.
Takaharu-no-MacBook-Pro:jobeet takaharukano$ php app/console cache:clear --env=prod
// Clearing the cache for the prod environment with debug false
[OK] Cache for the "prod" environment (debug=false) was successfully cleared.
サーバーを立ち上げて、http://127.0.0.1:8000/job/にアクセスすると見た目がずれてるけど、一応表示される。
