public function install(Application $app)
{
$schema = $app->schema();
$schema->create('users', function ($table) {
$table->integer('id')->unsigned()->autoincrement();
$table->string('name', 64)->notNull()->unique();
$table->string('password', 64);
$table->string('email', 128)->notNull()->unique();
$table->string('registration_email', 128)->notNull()->unique();
$table->dateTime('registration_date');
$table->dateTime('last_login');
$table->boolean('active')->defaultValue(false);
});
$schema->create('roles', function ($table) {
$table->integer('id')->unsigned()->autoincrement();
$table->string('name', 64)->notNull()->unique();
});
$schema->create('permissions', function ($table) {
$table->integer('id')->unsigned()->autoincrement();
$table->string('name', 128)->notNull()->unique();
$table->string('description');
});
$schema->create('user_roles', function ($table) {
$table->integer('rid')->unsigned();
$table->integer('uid')->unsigned();
$table->primary('pk', array('rid', 'uid'));
$table->foreign('user_roles_rid', 'rid')->references('roles')->on('id')->onUpdate('cascade')->onDelete('cascade');
$table->foreign('user_roles_uid', 'uid')->references('users')->on('id')->onUpdate('cascade')->onDelete('cascade');
});
$schema->create('role_permissions', function ($table) {
$table->integer('rid')->unsigned();
$table->integer('pid')->unsigned();
$table->primary('pk', array('rid', 'pid'));
$table->foreign('role_permissions_rid', 'rid')->references('roles')->on('id')->onUpdate('cascade')->onDelete('cascade');
$table->foreign('role_permissions_pid', 'pid')->references('permissions')->on('id')->onUpdate('cascade')->onDelete('cascade');
});
$roles = array('anonymous', 'authenticated');
foreach ($roles as $role) {
$app->database()->insert(array('name' => $role))->into('roles');
}
}