2017-01-24

Laravel 5 :（5）Migrations

Laravel 範例實作

此筆記主要紀錄學習以下 Laravel 的範例教學

Laravel 範例

Migrations

你可以用 Migrations 來建立或修改 database tables

使用資料庫

首先建立一個空的資料庫larashop

並修改/config/database.php

除了修改/config/database.php外

記得跟目錄下的.env也要一起改

DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=larashop       // 修改
DB_USERNAME=root           // 修改
DB_PASSWORD=melody         // 修改

Artisan migration 指令

當你使用Artisan migration 指令時

會建立檔案在/database/migrations底下

1.首先在跟目錄下執行

php artisan migrate:install

成功會出現

Migration table created successfully.

如果出現以下訊息，檢查/config/database.php和.env是否修改正確

Access denied for user 'homestead'@'localhost' (using password: YES)

執行完此指令後會在larashop資料庫下面建立一個資料表migrate

接著在執行

php artisan make:migration create_drinks_table

會在database/migrations/底下多出一個檔案

2017_01_24_133914_create_drinks_table.php

內容為

class CreateDrinksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        //
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}

Migration 結構

將2017_01_24_133914_create_drinks_table.php修改為

class CreateDrinksTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('drinks', function (Blueprint $table) {
            $table->increments('id');               // auto increment 
            $table->string('name',75)->unique();    // 75 = length of the field, unique
            $table->text('comments')->nullable();   // nullable = accept null values
            $table->integer('rating');              // integer 
            $table->date('brew_date');              // date 
            $table->timestamps();                   // automatically create two time stamp
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down() {
        Schema::drop('drinks');
    }
}

再來執行

php artisan migrate

會出現

Migrated: 2014_10_12_000000_create_users_table
Migrated: 2014_10_12_100000_create_password_resets_table
Migrated: 2017_01_24_133914_create_drinks_table

此時你的資料庫會出現

larashop
    -drinks
    -migrations
    -password_resets
    -users

Laravel migration rollback

你可以執行以下指令來回復上一次動作

php artisan migrate:rollback

所以執行後資料庫會變為

larashop    
    -migrations

再次執行以下指令建立資料表

php artisan migrate

Migration 操作

接下來要建立employees table並插入亂數

亂數插入可參考 Faker Library，教學參考

首先執行

php artisan make:migration employees
// 成功會出現 database/migrations/2017_01_24_151737_employees.php

修改database/migrations/2017_01_24_151737_employees.php

class Employees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // 建立 table
        Schema::create('employees', function (Blueprint $table) {
            // 5 個欄位
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('contact_number');
            $table->timestamps();       
        });

        // faker 物件，建立 33 個亂數        
        $faker = Faker\Factory::create();
        $limit = 33;

        for ($i = 0; $i < $limit; $i++) 
        {
            DB::table('employees')->insert([ //,
                'name'           => $faker->name,
                'email'          => $faker->unique()->email,
                'contact_number' => $faker->phoneNumber,
            ]);
        }
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('employees');
    }
}

接著執行

php artisan migrate

此時你的資料庫會出現

larashop
    -drinks
    -employees   // 新出現
    -migrations
    -password_resets
    -users

點下employees或執行SELECT * FROM employees;

可以看到裡面有我們插入的假帳號資訊

新增/刪除資料表欄位

資料表的欄位也會跟著變動

現在我們想要加一個gender欄位到contact_number之後

php artisan make:migration add_gender_to_employees --table=employees

此時會新增\database\migrations\2017_01_24_152951_add_gender_to_employees.php

-table=employees表示此動作是在作用在employees資料表

修改2017_01_24_152951_add_gender_to_employees.php為

class AddGenderToEmployees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // 新增欄位 gender 在 contact_number 之後
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender')->after('contact_number');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->dropColumn('gender');
        });
    }
}

接下來要安裝Database Abstract Layer DDBAL，官網

在跟目錄打開composer.json

"require": {
    "php": ">=5.6.4",
    "laravel/framework": "5.3.*"
},
改為
"require": {
    "php": ">=5.6.4",
    "laravel/framework": "5.3.*",
    "doctrine/dbal": "v2.5.5"
},

修改完後執行composer update

composer 會開始下載檔案到Composer\files\doctrine

此為 composer 安裝目錄，不是在網站專案中

接著執行

php artisan make:migration modify_gender_in_employees --table=employees

此時會新增\database\migrations\2017_01_24_155118_modify_gender_in_employees.php

然後修改此檔案為

class ModifyGenderInEmployees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // 修改 gender 欄位長度為 5 
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender', 5)->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender', 255)->change();
        });
    }
}

最後執行

php artisan migrate

現在把gender欄位改為可以為空的欄位

php artisan make:migration make_gender_null_in_employees --table=employees

修改2017_01_24_165034_make_gender_null_in_employees.php

class MakeGenderNullInEmployees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender', 5)->nullable()->change();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->string('gender', 5)->change();
        });
    }
}

執行php artisan migrate

你可以發現gender欄位屬性改變

                            空值  預設值
gender  varchar(5)          否       無
變為
gender  varchar(5)          是       NULL

使用 foreign key

我們想要將employees中的人以departments來分類

先建立depts檔案

php artisan make:migration depts

修改2017_01_24_165501_depts.php

class Depts extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('depts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('depts');
    }
}

執行php artisan migrate

建立修改欄位檔案

php artisan make:migration add_dept_id_in_employees --table=employees

修改2017_01_24_170042_add_dept_id_in_employees.php

class AddDeptIdInEmployees extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->unsignedInteger ('dept_id')->nullable()->after('gender');
            $table->foreign('dept_id')->references('id')->on('depts')->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('employees', function (Blueprint $table) {
            $table->dropColumn('dept_id');
        });
    }
}

執行php artisan migrate

輸入php artisan make:seeder DrinksTableSeeder

打開database\seeds\DrinksTableSeeder.php修改為

class DrinksTableSeeder extends Seeder {

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run() {
        DB::table('drinks')->insert([
            'name' => 'Vodka',
            'comments' => 'Blood of creativity',
            'rating' => 9,
            'brew_date' => '1973-09-03',
        ]);
    }
}

執行php artisan db:seed --class=DrinksTableSeeder

資料表drinks會增加一個資料

id  name    comments                rating  brew_date   created_at  updated_at  
1   Vodka   Blood of creativity     9       1973-09-03  NULL        NULL

實際練習

我們需要建立以下 4 個資料表

以下欄位所有資料表都要有
S/N     FIELD       DATA TYPE       DESCRIPTION
1   created_at      Timestamp       Timestamp when record was created
2   updated_at      Timestamp       Timestamp when record was last updated
3   created_at_ip   Varchar(45)     IP address used to create the record
4   updated_at_ip   Varchar(45)     IP address used to last update record


// Posts Table
S/N     FIELD   DATA TYPE           DESCRIPTION
1       id          INT             Primary key (AUTOINCREMENT)
2       url         Varchar(255)    Page URL
3       title       Varchar(140)    Page title
4       description Varchar(170)    Description that shows in search engine results
5       content     Text            The content of the page or blog post
6       blog        Tinyint(1)      Determines if a post is a page is blog

// Products Table
S/N     FIELD           DATA TYPE   DESCRIPTION
1       id              INT             Primary key (AUTOINCREMENT)
2       name            Varchar(255)    Product name
3       title           Varchar(140)    Product title
4       description     Varchar(500)    Product description
5       price   int     Product         price
6       category_id     int             Product category id
7       brand_id        int             Product brand id

// Categories Table
S/N     FIELD   DATA TYPE       DESCRIPTION
1       id      int             Primary key (AUTOINCREMENT)
2       name    Varchar(255)    Category name

// Brands Table
S/N     FIELD   DATA TYPE       DESCRIPTION
1       id      int             Primary key (AUTOINCREMENT)
2       name    Varchar(255)    Brand name

執行以下指令

php artisan make:migration create_posts_table
php artisan make:migration create_products_table
php artisan make:migration create_categories_table
php artisan make:migration create_brands_table

修改 4 個檔案

1. 2017_01_24_172304_create_posts_table.php

class CreatePostsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('posts', function (Blueprint $table) {
            $table->increments('id');
            $table->string('url', 255)->unique();
            $table->string('title', 140);
            $table->string('description', 170);
            $table->text('content');
            $table->boolean('blog');
            $table->timestamps();
            $table->string('created_at_ip');
            $table->string('updated_at_ip');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down() {
        Schema::drop('posts');
    }
}

2.2017_01_24_172307_create_products_table.php

class CreateProductsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 255)->unique();
            $table->string('title', 140);
            $table->string('description', 500);
            $table->integer('price');
            $table->integer('category_id');
            $table->integer('brand_id');
            $table->timestamps();
            $table->string('created_at_ip');
            $table->string('updated_at_ip');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down() {
        Schema::drop('products');
    }
}

3.2017_01_24_172310_create_categories_table.php

class CreateCategoriesTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('categories', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 255)->unique();
            $table->timestamps();
            $table->string('created_at_ip');
            $table->string('updated_at_ip');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down() {
        Schema::drop('categories');
    }
}

4.2017_01_24_172314_create_brands_table.php

class CreateBrandsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up() {
        Schema::create('brands', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name', 255)->unique();
            $table->timestamps();
            $table->string('created_at_ip');
            $table->string('updated_at_ip');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down() {
        Schema::drop('brands');
    }
}

執行php artisan migrate

加入假資料

php artisan make:seeder ProductsTableSeeder
php artisan make:seeder CategoriesTableSeeder
php artisan make:seeder BrandsTableSeeder

修改檔案

1.ProductsTableSeeder.php

class ProductsTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table('products')->insert(['name' => 'Mini skirt black edition', 'title' => 'Mini skirt black edition','description' => 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna','price' => 35,'category_id' => 1,'brand_id' => 1,]);
        DB::table('products')->insert(['name' => 'T-shirt blue edition', 'title' => 'T-shirt blue edition','description' => 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna','price' => 64,'category_id' => 2,'brand_id' => 3,]);
        DB::table('products')->insert(['name' => 'Sleeveless Colorblock Scuba', 'title' => 'Sleeveless Colorblock Scuba','description' => 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna','price' => 13,'category_id' => 3,'brand_id' => 2,]);
    }
}

2.CategoriesTableSeeder.php

class CategoriesTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table('categories')->insert(['name' => 'MENS']);
        DB::table('categories')->insert(['name' => 'WOMENS']);
        DB::table('categories')->insert(['name' => 'KIDS']);
        DB::table('categories')->insert(['name' => 'FASHION']);
        DB::table('categories')->insert(['name' => 'CLOTHING']);
    }
}

3.BrandsTableSeeder.php

class BrandsTableSeeder extends Seeder {

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run() {
        DB::table('brands')->insert(['name' => 'ACNE']);
        DB::table('brands')->insert(['name' => 'RONHILL']);
        DB::table('brands')->insert(['name' => 'ALBIRO']);
        DB::table('brands')->insert(['name' => 'ODDMOLLY']);
    }
}

重點

// 建立資料表 migrate
php artisan migrate:install

// 建立檔案到 database\migrations
php artisan make:migration create_drinks_table
php artisan make:migration employees

// 執行 database\migrations 底下檔案到資料庫
// Run the database migrations
php artisan migrate

// 新增資料表欄位
php artisan make:migration add_gender_to_employees --table=employees

// 回覆上一次動作
php artisan migrate:rollback

Laravel

Laravel