roaresearch / yii2-migrate
Yii 2 Library to handle normalized table creation
Installs: 2 922
Dependents: 2
Suggesters: 0
Security: 0
Stars: 0
Watchers: 2
Forks: 1
Open Issues: 0
Type:yii2-extension
Requires
- php: >=8.1
- yiisoft/yii2: ~2.0.27
Requires (Dev)
- codeception/codeception: ~5.0.0
- codeception/module-db: ~3.0.0
- codeception/verify: ~1.2.0
- phpunit/php-code-coverage: ~10.0.0
- squizlabs/php_codesniffer: ~3.5.0
- yiisoft/yii2-debug: ~2.1.0
README
This library eases the creation of normalized databases by providing classes to create tables separatedly with a simple logic.
Installation
You can use composer to install the library roaresearch/yii2-migrate
by running the
command;
composer require roaresearch/yii2-migrate
or edit the composer.json
file
require: { "roaresearch/yii2-migrate": "*", }
Usage
Create Table Migrations
You can use the roaresearch\yii2\migrate\CreateTableMigration
to generate different
type of migration tables to be used by each of your table types.
For example lets say you want to save who and when edits and creates each the entities on your system but your pivot tables only require to know when they were created since they can't be edited.
abstract class EntityTable extends \roaresearch\yii2\migrate\CreateTableMigration { public function defaultColumns(): array { return [ 'created_at' => $this->datetime()->notNull(), 'updated_at' => $this->datetime()->notNull(), 'created_by' => $this->normalKey(), 'updated_by' => $this->normalKey(), ]; } public function defaultForeignKeys(): array { return [ 'created_by' => 'user', 'updated_by' => 'user', ]; } } abstract class PivotTable extends \roaresearch\yii2\migrate\CreateTableMigration { public function defaultColumns(): array { return [ 'created_at' => $this->datetime()->notNull(), ]; } }
Then you can use them separatedly for each type of table you have
class m170101_010101_ticket extends EntityTable { public function tableName(): string { return 'ticket'; } public function columns(): array { return [ 'id' => $this->primaryKey(), 'project_id' => $this->normalKey(), 'title' => $this->string(150)->notNull(), 'description' => $this->text(), ]; } public function foreignKeys() { return ['project_id' => ['table' => 'project']]; } } class m17010101_010102_ticket_employee extends PivotTable { public function tableName(): string { return 'ticket_employee'; } public function columns(): array { return [ 'ticket_id' => $this->normalKey(), 'employee_id' => $this->normalKey(), ]; } public function foreignKeys(): array { return [ 'ticket_id' => ['table' => 'ticket'], 'employee_id' => ['table' => 'employee'], ]; } public function compositePrimaryKeys(): array { return ['ticket_id', 'employee_id']; } }
When running the migration m170101_010101_ticket
it will generate a table
with the columns
id
,project_id
,title
,description
,created_by
,updated_by
created_at
,updated_at
With primary key id
and three foreign keys linked to the columns project_id
,
created_by
and updated_by
.
And the migration m170101_010101_ticket_employee
will generate a table with
the columns.
ticket_id
employee_id
created_at
With a composite primary key of the columns ticket_id
and employee_id
and
two foreign keys linked to the previous two columns.
Create View Migrations
You can use the roaresearch\yii2\migrate\CreateViewMigration
to generate SQL views
migrations.
use common\models\Ticket; use roaresearch\yii2\migrate\CreateViewMigration; use yii\db\Query; class m17010101_010101_ticket_details extends CreateViewMigration { /** * @inheritdoc */ public function viewName(): string { return 'ticket_details'; } /** * @inheritdoc */ public function viewQuery(): Query { return Ticket::find() ->alias('t') ->innerJoinWith([ 'project' => function ($query) { $query->alias('p'); }, ])->select([ 'ticket_id' => 't.id', 'project_id' => 'p.id', 'ticket_title' => 't.title', 'ticket_description' => 't.description', 'project_name' => 'p.name',t ]); } }