摘要：看下兩個(gè)方法的源碼同樣是使用了對(duì)象來添加命令和。

說明：本文主要學(xué)習(xí)Schema Builder和Migration System的使用及相關(guān)原理。傳統(tǒng)上在設(shè)計(jì)database時(shí)需要寫大量的SQL語句，但Laravel提供了Schema Builder這個(gè)神器使得在設(shè)計(jì)database時(shí)使用面向?qū)ο蠓椒▉碜?，不需要寫一行SQL，并且還提供了另一個(gè)神器Migration System，可以對(duì)database做版本控制，包括回滾上一次的遷移操作。本小系列主要分為上中下三篇。本篇主要學(xué)習(xí)使用Schema Builder來creating,dropping,updating tables;adding,removing,renaming columns;simple index,unique index,foreign keys，同時(shí)也會(huì)學(xué)習(xí)相關(guān)源碼來進(jìn)一步了解Schema Builder。

開發(fā)環(huán)境： Laravel5.3 + PHP7

在設(shè)計(jì)database時(shí)需要?jiǎng)?chuàng)建、刪除和更新表，Schema Builder類提供了一些methods來面向?qū)ο蟮膱?zhí)行這些操作，而不需要寫一行SQL。在寫Laravel程序時(shí)，也經(jīng)常使用類似命令php artisan make:migration create_accounts_table --create=accounts來做一個(gè)遷移類創(chuàng)建數(shù)據(jù)表，會(huì)在database/migrations文件夾下得到類似如下的代碼類：

use IlluminateSupportFacadesSchema;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;

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

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists("accounts");
    }
}

在執(zhí)行php artisan migrate命令時(shí)操作的是up()方法中語句。在創(chuàng)建的遷移類CreateAccountsTable中，Schema::create()就是創(chuàng)建表的語句，并且第一個(gè)參數(shù)就是表的名字，第二個(gè)參數(shù)是個(gè)閉包，是操作columns的語句，并且參數(shù)是個(gè)Blueprint對(duì)象。為什么有這么奇怪的寫法呢？

看下Schema Facade中getFacadeAccessor的源碼：

    /**
     * Get a schema builder instance for the default connection.
     *
     * @return IlluminateDatabaseSchemaBuilder
     */
    protected static function getFacadeAccessor()
    {
        // 這里"db"服務(wù)是在DatabaseServiceProvider中定義的，是DatabaseManager對(duì)象，且laravel默認(rèn)connection是mysql
        // 則返回的是MysqlBuilder，也就是IlluminateDatabaseSchemaBuilder的子類
        return static::$app["db"]->connection()->getSchemaBuilder();
    }

根據(jù)注釋就知道Schema::create就是等同于MysqlBuilder::create()，看下源碼：

    // IlluminateDatabaseSchemaBuilder
    /**
     * Create a new table on the schema.
     *
     * @param  string    $table
     * @param  Closure  $callback
     * @return IlluminateDatabaseSchemaBlueprint
     */
    public function create($table, Closure $callback)
    {
        /** @var IlluminateDatabaseSchemaBlueprint $blueprint */
        $blueprint = $this->createBlueprint($table);

        // 添加"create"命令
        $blueprint->create();

        // 執(zhí)行閉包里的操作，也就是操作columns
        $callback($blueprint);

        $this->build($blueprint);
    }
    
    protected function createBlueprint($table, Closure $callback = null)
    {
        if (isset($this->resolver)) {
            return call_user_func($this->resolver, $table, $callback);
        }

        return new Blueprint($table, $callback);
    }
    
    // IlluminateDatabaseSchemaBlueprint
    public function create()
    {
        return $this->addCommand("create");
    }
    
    protected function build(Blueprint $blueprint)
    {
        $blueprint->build($this->connection, $this->grammar);
    }

create()中的$callback($blueprint);語句執(zhí)行了閉包操作，并且閉包參數(shù)還是個(gè)Blueprint對(duì)象。最關(guān)鍵的方法時(shí)Blueprint對(duì)象的build()方法，下文再聊具體細(xì)節(jié)。

當(dāng)然，Schema Builder是可以在任意模塊中使用的，如在路由中使用，執(zhí)行https://localhost:8888/create_accounts就可以創(chuàng)建一個(gè)accounts表了：

Route::get("create_accounts", function () {
    IlluminateSupportFacadesSchema::create("accounts", function(IlluminateDatabaseSchemaBlueprint $table)
    {
        $table->increments("id");
        $table->string("name");
        $table->string("number");
        $table->tinyInteger("status");
        $table->enum("source", ["bank account", "credit card", "investment account"]);
        $table->timestamps();
    });
});

Schema Builder提供了兩個(gè)方法來刪除表：drop(string $table)和dropIfExists(string $table)，參數(shù)是表名，dropIfExists()表示只有在表存在才刪除，所以dropIfExists()比drop()更優(yōu)雅。看下兩個(gè)方法的源碼：

    /**
     * Drop a table from the schema.
     *
     * @param  string  $table
     * @return IlluminateDatabaseSchemaBlueprint
     */
    public function drop($table)
    {
        $blueprint = $this->createBlueprint($table);

        $blueprint->drop();

        $this->build($blueprint);
    }

    /**
     * Drop a table from the schema if it exists.
     *
     * @param  string  $table
     * @return IlluminateDatabaseSchemaBlueprint
     */
    public function dropIfExists($table)
    {
        $blueprint = $this->createBlueprint($table);

        $blueprint->dropIfExists();

        $this->build($blueprint);
    }
    
    // IlluminateDatabaseSchemaBlueprint
    public function drop()
    {
        return $this->addCommand("drop");
    }
    public function dropIfExists()
    {
        return $this->addCommand("dropIfExists");
    }

同樣是使用了Blueprint對(duì)象來添加命令drop和dropIfExists。在路由中刪除accounts表：

Route::get("delete_accounts", function () {
    IlluminateSupportFacadesSchema::dropIfExists("accounts");
//    IlluminateSupportFacadesSchema::drop("accounts");
});

更新表的操作包括更新表名和更新表字段。

使用Schema::rename($from, $to)方法來更新表名：

Route::get("rename_bank_accounts", function () {
    IlluminateSupportFacadesSchema::rename("accounts", "bank_accounts");
});

看下Schema的rename()源碼，同樣是使用Blueprint對(duì)象添加rename命令：

    public function rename($from, $to)
    {
        $blueprint = $this->createBlueprint($from);

        $blueprint->rename($to);

        $this->build($blueprint);
    }
    
    
    // IlluminateDatabaseSchemaBlueprint
    public function rename($to)
    {
        return $this->addCommand("rename", compact("to"));
    }

使用Schema::table()方法來更新表字段值，如更新accounts的number字段，，不過如果該表中有字段類型為enum就不支持修改：

Route::get("update_accounts", function () {
    IlluminateSupportFacadesSchema::table("accounts", function(IlluminateDatabaseSchemaBlueprint $table)
    {
        $table->string("number", 50)->change();
    });
});

同時(shí)，Schema還提供了幾個(gè)有用的方法，如hasTable($table),hasColumn($table, $column),hasColumns($table, array $column),getColumnListing($table):

Route::get("get_column_listing", function () {
//    if (IlluminateSupportFacadesSchema::hasTable("accounts"))
//    if (IlluminateSupportFacadesSchema::hasColumn("accounts", "name"))
    if (IlluminateSupportFacadesSchema::hasColumns("accounts", ["name"]))
    {
        // ["id", "name", "number", "status", "source", "created_at", "updated_at"]
        return IlluminateSupportFacadesSchema::getColumnListing("accounts");
    }
});

設(shè)計(jì)database時(shí)需要添加columns，上文說過這段邏輯是在Schema::create(),Schema::table()的閉包里執(zhí)行的，利用Blueprint對(duì)象來依次添加每一個(gè)column字段屬性和Mysql中數(shù)據(jù)類型對(duì)應(yīng)，如：

IlluminateSupportFacadesSchema::create("accounts", function(IlluminateDatabaseSchemaBlueprint $table)
    {
        $table->string("name"); // => $this->addColumn("string", $column, compact("length"));
    });

常用的Schema字段類型函數(shù)表如下：

``

Schema提供了dropColumn()方法來刪除表中字段：

Route::get("drop_column", function () {
    IlluminateSupportFacadesSchema::table("accounts", function(IlluminateDatabaseSchemaBlueprint $table)
    {
        $table->dropColumn(["number", "status"]);
    });
});

Schema提供了renameColumn()來修改column名稱，不過如果該表中有字段類型為enum就不支持修改：

Route::get("rename_column", function () {
    IlluminateSupportFacadesSchema::table("accounts", function(IlluminateDatabaseSchemaBlueprint $table)
    {
        $table->renameColumn("name", "title");
    });
});

Schema提供了index()方法來給column加索引，且索引名稱約定為table-name_column-name_index-type:

Route::get("index_column", function () {
    IlluminateSupportFacadesSchema::table("accounts", function(IlluminateDatabaseSchemaBlueprint $table)
    {
        $table->index("name");
    });
});

同時(shí)，Schema提供了dropIndex("table-name_column-name_index")來刪除simple index。

Schema提供了unique()方法來給column加索引，且索引名稱約定為table-name_column-name_index-type:

Route::get("unique_column", function () {
    IlluminateSupportFacadesSchema::table("accounts", function(IlluminateDatabaseSchemaBlueprint $table)
    {
        $table->unique(["title"]);
    });
});

同時(shí)，Schema提供了dropUnique("table-name_column-name_unique")來刪除unique index。

Schema提供了foreign()->reference()->on() fluent api來設(shè)計(jì)foreign key，且索引名稱約定為table-name_column-name_index-type:

Route::get("foreign_key_column", function () {
    IlluminateSupportFacadesSchema::create("bills", function(IlluminateDatabaseSchemaBlueprint $table)
    {
        $table->increments("id");
        $table->unsignedInteger("account_id");
        $table->float("amount", 5, 5);

        $table->foreign("account_id")
            ->references("id")
            ->on("accounts")
            ->onUpdate("CASCADE")
            ->onDelete("CASCADE");
    });
});

同時(shí)，Schema提供了dropForeign("table-name_column-name_foreign")來刪除foreign index。

總結(jié)：本篇主要學(xué)習(xí)下Laravel使用了Schema和Blueprint兩個(gè)類來設(shè)計(jì)database，中篇將以Schema::create()為例仔細(xì)研究下源碼是如何轉(zhuǎn)換為SQL并執(zhí)行SQL語句的，下篇聊下Migration System的使用及其原理。到時(shí)見。

RightCapital招聘Laravel DevOps