# Database migrations

• For our Vinyl Shop application, we need five database tables: users, (music) genres, records, orders and orderlines

# Create a new database

• First you have to create a new database (with name vinylshop) in phpMyAdmin
  • Open http://homestead.test/phpMyAdmin/
  • Add a new database vinylshop with utf8mb4_unicode_ci collation

# Configure the database in .env file

• Open the file .env and change the database configuration

DB_CONNECTION=mysql
DB_HOST=localhost           # change to localhost
DB_PORT=33060               # add a zero
DB_DATABASE=vinylshop       # change to vinylshop
DB_USERNAME=homestead       # change to homestead
DB_PASSWORD=secret          # change to secret

# Creating the models and migrations

• Laravel represents the structure of the database tables (and the changes to it) as migrations
  • Advantages
    • Especially efficient if you code in a team
    • If you change a table, you don't have to manually implement these changes in every database instance (every developer's local database, the production database, ...), but you can adjust or migrate it automatically
  • All database migrations live inside the folder database/migrations
• By default, Laravel has two migrations
  • 2014_10_12_000000_create_users_table.php for the users table
  • 2014_10_12_000000_create_password_resets_table.php which will be used if a user forgot his password
• A new migration (class) can be made with php artisan make:migration (e.g. php artisan make:migration create_genres_table), but it's more interesting to create an Eloquent model together with the migration (e.g. php artisan make:model Genre -m)
  • With the second option, less mistakes against the Laravel (naming) conventions will be made
  • In Laravel, each database table needs a corresponding model class to interact (query data, insert new records, ...) with that table
  • The models are located in the root of the app folder
• Create all necessary models with the corresponding migrations (with the flag -m or --migration)
  • You don't have to make a User model, as this model is already present by default

php artisan make:model Genre -m
php artisan make:model Record -m
php artisan make:model Order -m
php artisan make:model Orderline -m

• In the remainder of this course section we only look at the tables (the migrations). The next section deals with the related models.

# Modify the tables

• Every migration class has two methods
  • up() for running a migration
  • down() for rolling back a migration
• We only have to modify (extend) the up() method with the necessary columns/fields
  • List of available column types

# Users table

• Open database/migrations/2014_10_12_000000_create_users_table.php
  • A new database table is created with the create() method (on the Schema facade). The create() method accepts two arguments: the first is the name of the table, while the second is a function (which in turn receives a Blueprint object as a parameter) that may be used to define the new table.
  • Several columns (of which the purpose will become clear later on in this course) are automatically provided by Laravel
    • The bigIncrements('id') method call adds an auto-incremented, unsigned BIGINT column id for the primary key
    • The STRING column email must have a unique value
    • The TIMESTAMP column email_verified_at may contain the value null
    • The method timestamps() inserts two nullable TIMESTAMP columns (created_at and updated_at) that will be used by Laravel to keep track of the changes to a database row/record
  • Add a BOOLEAN column active with default value true and a BOOLEAN column admin with default value false

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name');
        $table->string('email')->unique();
        $table->boolean('active')->default(true);
        $table->boolean('admin')->default(false);
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });
}

# Genres table

• Open database/migrations/yyyy_mm_dd_hhmmss_create_genres_table.php and add the necessary column

public function up()
{
    Schema::create('genres', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->string('name');
        $table->timestamps();
    });
}

# Records table

• Open database/migrations/yyyy_mm_dd_hhmmss_create_records_table.php and add the necessary columns and foreign key relation
  • Later on in this application, we will retrieve some additional record information (e.g. the cover image and the track list) from MusicBrainz, a music encyclopedia with lots of music metadata. Therefore, we store the MusicBrainz artist id and title id (of a record) in the columns artist_mbid and title_mbid (both STRING columns of 36 characters).
  • The foreign key relation cascade specifies that
    • if a row from the genres table is deleted, all the rows in the records table referencing this genre are deleted as well
    • if an id in the genres table is updated, all the corresponding foreign keys in the records table are updated as well

public function up()
{
    Schema::create('records', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedBigInteger('genre_id');    // use SAME SIZE as id: unsignedInteger() creates an error!
        $table->string('artist');
        $table->string('artist_mbid', 36)->nullable();
        $table->string('title');
        $table->string('title_mbid', 36)->nullable();
        $table->string('cover')->nullable();
        $table->float('price', 5, 2)->default(19.99);
        $table->unsignedInteger('stock')->default(1);
        $table->timestamps();
        
        // Foreign key relation
        $table->foreign('genre_id')->references('id')->on('genres')->onDelete('cascade')->onUpdate('cascade');
    });
}

# Orders table

• Open database/migrations/yyyy_mm_dd_hhmmss_create_orders_table.php and add the necessary columns and foreign key relation

public function up()
{
    Schema::create('orders', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedBigInteger('user_id');
        $table->float('totalPrice', 6, 2);
        $table->timestamps();

        // Foreign key relation
        $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade')->onUpdate('cascade');
    });
}

# Orderlines table

• Open database/migrations/yyyy_mm_dd_hhmmss_create_orderlines_table.php and add the necessary columns and foreign key relation

public function up()
{
    Schema::create('orderlines', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unsignedBigInteger('order_id');
        $table->string('artist');
        $table->string('title');
        $table->string('cover')->nullable();
        $table->float('totalPrice', 6, 2);
        $table->unsignedInteger('quantity');
        $table->timestamps();

        // Foreign key relation
        $table->foreign('order_id')->references('id')->on('orders')->onDelete('cascade')->onUpdate('cascade');
    });
}

# Migrate the database

• Migrate the database with the command php artisan migrate, which results in (the creation of) the database tables according to the specifications in the migration classes

# Add fixed data to the tables

• Laravel supports different methods to insert data into a table
• If the data is fixed, you can insert table rows within the (up() method of the) migration

# Users table

• Open database/migrations/yyyy_mm_dd_hhmmss_create_users_table.php and insert some users
  • Insert some users using the insert() method (from Laravel's Query Builder). The insert() method accepts an array of associative arrays as parameter. These associative arrays (in which the column names of the table are used as keys) represent the rows inserted into the table.
  • The (Carbon) function now() is used for the created_at and email_verified_at columns
  • The password is hashed using Laravel's Hash facade

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        ...
    });
    
    // Insert some users
    DB::table('users')->insert(
        [
            [
                'name' => 'John Doe',
                'email' => 'john.doe@example.com',
                'admin' => true,
                'password' => Hash::make('admin1234'),
                'created_at' => now(),
                'email_verified_at' => now()
            ],
            [
                'name' => 'Jane Doe',
                'email' => 'jane.doe@example.com',
                'admin' => false,
                'password' => Hash::make('user1234'),
                'created_at' => now(),
                'email_verified_at' => now()
            ]
        ]
    );
    
    // Add 40 dummy users inside a loop
    for ($i = 0; $i <= 40; $i++) {
        DB::table('users')->insert(
            [
                'name' => "ITF User $i",
                'email' => "itf_user_$i@mailinator.com",
                'password' => Hash::make("itfuser$i"),
                'created_at' => now(),
                'email_verified_at' => now()
            ]
        );
    }
}

# EXERCISE

• Replace the name and email of the first user with your credentials

# Genres table

• Open database/migrations/yyyy_mm_dd_hhmmss_create_genres_table.php and insert some genres
  (Copy the full list from this genres and records gist)

public function up()
{
    Schema::create('genres', function (Blueprint $table) {
        ...
    });
    
    // Insert some genres
    DB::table('genres')->insert(
        [
            ['name' => 'pop/rock', 'created_at' => now()],
            ...
        ]
    );
}

# Records table

• Open database/migrations/yyyy_mm_dd_hhmmss_create_records_table.php and insert some records
  (Copy the full list from this genres and records gist)

public function up()
{
    Schema::create('records', function (Blueprint $table) {
        ...
    });
    
    // Insert some records
    DB::table('records')->insert(
        [
            [
                'genre_id' => 1,
                'created_at' => now(),
                'stock' => 1,
                'artist' => 'Queen',
                'artist_mbid' => '0383dadf-2a4e-4d10-a46a-e9e041da8eb3',
                'title' => 'Greatest Hits',
                'title_mbid' => 'fcb78d0d-8067-4b93-ae58-1e4347e20216',
                'cover' => null
            ],
            ...
         ]
    );
}

# Migrate the database

• Replace the old version of the database with php artisan migrate:fresh

# Timezone

• Inspect the table genres in phpMyAdmin
• Look at the dates in the created_at column
• If there is a time difference between the time in this column and the time on your clock, you have to set the default timezone of your app to your local timezone, as explained in Config -> Work environment -> 6: PhpMyAdmin -> Update timezone
• After you adjusted the timezone, you can run the migration again (php artisan migrate:fresh)