Symfony 1 - Doctrine - Set up a new database

In this tutorial we will see how to set up a database with Symfony 1 and doctrine examples.

Let's start by writing some code in the schema.sql file.
We will create the Hello project with this hello database.

# config/doctrine/schema.yml
HelloCategory:
  actAs: { Timestampable: ~ }
  columns:
    name: { type: string(255), notnull: true, unique: true }
 
HelloBook:
  actAs: { Timestampable: ~ }
  columns:
    category_id:  { type: integer, notnull: true }
    email:        { type: string(255), notnull: true }
    expires_at:   { type: timestamp, notnull: true }
  relations:
    HelloCategory: { onDelete: CASCADE, local: category_id, foreign: id, foreignAlias: HelloBooks }

We continue by typing a command line for connecting to the database.
The name of your project will be also the name of the database:

$ mysqladmin -uroot -p create hello

A new line appears and you have to enter your MySQL password.

Then let's configure the database in the database.yml file:

$ php symfony configure:database "mysql:host=localhost;dbname=hello" root yourPassword

Open the database.yml file to see the changements.

Let's create the model:

$ php symfony doctrine:build --model

It will create 3 files in the lib/model/ directory.

Let's create the SQL snippet:

$ php symfony doctrine:build --sql

The changements are now present in the data/sql/schema.sql file:

CREATE TABLE hello_category (id BIGINT AUTO_INCREMENT, name VARCHAR(255) NOT NULL UNIQUE, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY(id)) ENGINE = INNODB;
CREATE TABLE hello_book (id BIGINT AUTO_INCREMENT, category_id BIGINT NOT NULL, email VARCHAR(255) NOT NULL, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX category_id_idx (category_id), PRIMARY KEY(id)) ENGINE = INNODB;
ALTER TABLE hello_book ADD CONSTRAINT hello_book_category_id_hello_category_id FOREIGN KEY (category_id) REFERENCES hello_category(id) ON DELETE CASCADE;

Go in your SQL administration, such as phpMyAdmin and create e new database named hello.

We can now insert these request in the database:

$ php symfony doctrine:insert-sql

You can not execute twice this command line, because the tables already exist in the database.
If you try to retype this command, you will have probably something like that:

$ php symfony doctrine:insert-sql
>> doctrine  creating tables

SQLSTATE[HY000]: General error: 1005 Can't create table 'cms.#sql-788_6a' (errno: 121). Failing Query: "ALTER TABLE * ADD CONSTRAINT *_id_*_id FOREIGN KEY (id) REFERENCES *(id) ON UPDATE CASCADE ON DELETE CASCADE". Failing Query: ALTER TABLE * ADD CONSTRAINT *_id_*_id FOREIGN KEY (id) REFERENCES *(id) ON UPDATE CASCADE ON DELETE CASCADE

All these steps can be made by only one task from scratch:

$ php symfony doctrine:build --all --no-confirmation

Be careful with this command because it will erase all your database before reconstruct it.
The same for all files and directories created before.

We add some fixture files:

# data/fixtures/books.yml
HelloBook:
  book_1:
    HelloCategory: sciences
    email:        book1@example.com
    expires_at:   '2021-03-04'
 
  book_2:
    HellocCategory:  english
    email:        book2@example.com
    expires_at:   '2021-03-04'

And

# data/fixtures/categories.yml
HelloCategory:
  sciences:
    name: Sciences
  english:
    name: English
  novel:
    name: Novel

We can now load these fixture files into the database:

$ php symfony doctrine:data-load

And to finish, we want to see this result on our webpage.
We have then to create the module book of the frontend application:

$ php symfony doctrine:generate-module --with-show --non-verbose-templates frontend book HelloBook

Let's try this address: http://yourProjectPath/frontend_dev.php/book

Normally you can see 2 data displayed.

Good job!