# Migration

Code Generation starts with database design.

{% hint style="warning" %}
DO NOT create or modify database thru tools like PHPMyadmin. Use code instead.
{% endhint %}

Best practice in Yee is not to manually create your database, but to use code to do it. This allows collaboration in large team possible where everybody may have their own version of local database in development environment.

Migration also is a key player in modular architecture, where module can be install and upgrade easily by super admin. In this case, migration code either sit in `insallDb` function at module like `SampleModule.php`or; sit in the `upgrades` folder. 

### Create Table

{% hint style="info" %}
Table column naming is use to instruct Yee on how to generate code for models, controllers and views. Hence, naming convention for table columns is strict and must be follow by all developers. Check [Fundemental Concept ](https://exiang.gitbook.io/yeebase/fundamental-concept)for more detail.
{% endhint %}

```php
$migration->createTable('sample', array(
			'id' => 'pk',
			'code' => 'string NOT NULL',
			'sample_group_id' => 'integer NULL',
			'sample_zone_code' => 'string NULL',
			'title_en' => 'string NOT NULL',
			'title_ms' => 'string NOT NULL',
			'title_zh' => 'string NOT NULL',
			'text_short_description_en' => 'string NOT NULL',
			'text_short_description_ms' => 'string NOT NULL',
			'text_short_description_zh' => 'string NOT NULL',
			'html_content_en' => 'longtext NULL',
			'html_content_ms' => 'longtext NULL',
			'html_content_zh' => 'longtext NULL',
			'image_main' => 'string NULL',
			'file_backup' => 'string NULL',
			'price_main' => 'decimal(10,0) NULL DEFAULT 0',
			'gender' => 'string NULL',
			'age' => 'integer NULL',
			'csv_keyword' => 'mediumtext NULL',
			'ordering' => 'double NOT NULL DEFAULT 1',
			'date_posted' => 'integer NOT NULL',
			'is_active' => 'boolean NOT NULL DEFAULT 1',
			'is_public' => 'boolean NOT NULL DEFAULT 1',
			'is_member' => 'boolean NOT NULL DEFAULT 1',
			'is_admin' => 'boolean NOT NULL DEFAULT 1',
			'date_added' => 'integer NOT NULL',
			'date_modified' => 'integer NOT NULL',
		));
```

### Alter Table

```php
$migration->alterColumn('sample', 'code', 'varchar(32) NOT NULL');
$migration->alterColumn('sample', 'file_backup', 'varchar(255) NULL');
$migration->alterColumn('sample', 'gender', "ENUM('male', 'female', 'secret') NULL");
$migration->alterColumn('sample', 'age', 'smallint(6) NULL');
```

### Create Index

Two type of index here, the simple one:

```php
$migration->createIndex('code', 'sample', 'code', true); // a unique index
$migration->createIndex('sample_group_id', 'sample', 'sample_group_id', false); // a not unique index
```

and the combination (combine more than 1 table fields as an unique index, mostly appear in many-to-many table):

```php
$migration->createIndex('campus_member2organization-campus_member_id-organization_id', 'campus_member2organization', array('campus_member_id', 'organization_id'), true);
```

### Create Foreign Key

```php
$migration->addForeignKey('fk_sample-sample_zone_code', 'sample', 'sample_zone_code', 'sample_zone', 'code', 'CASCADE', 'CASCADE');
```

### Execute Raw SQL

This is handy when you need to bulk update your existing data in database.

```php
$sql = 'UPDATE `eventbrite_organization_webhook` as t LEFT JOIN `organization` as f ON t.organization_code=f.code SET t.organization_id=f.id';
Yii::app()->db->createCommand($sql)->execute();
```

### Create Meta

```php
MetaStructure::initMeta('organization', 'community', 'urlYoutubeCoverVideo', 'string', 'Cover Youtube Video URL', 'URL to display youtube video in community organization page', '');
```

### Create Setting

```php
Setting::setSetting('sample-var1', 'Hello World 0.2', 'string');
```

### Create Embed

```php
$embed = Embed::setEmbed('ntis-signup-tncContent', array(
    'is_title_enabled' => true,
    'is_text_description_enabled' => false,
    'is_html_content_enabled' => true,
    'is_image_main_enabled' => false,
    'is_default' => true,
    'title_en' => 'Terms and Conditions',
    'html_content_en' => 'Hello Admin, please remember to update this terms and condition content inside Embed \ <b>#signup-tncContent</b>',
));
```

{% hint style="info" %}
Remember: always prefix your embed with module code (e.g. `boilerplateStart-`,`sample-`) or realm / context code (e.g. `cpanel-`)
{% endhint %}

### Create Service

```php
Service::setService('cv', 'CV Portfolio', 'A talent directory showcasing  experience and qualifications for job opportunity and cofounder matching', array('is_bookmarkable' => 1, 'is_active' => 1));
```

### Create Access Role

```php
Access::setAccessRole('ntis', 'BackendController', ['manageSolutionProvider'], ['ntisTechSecretariat', 'ntisRegulatorySecretariat']);
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://exiang.gitbook.io/yeebase/code-generator/migration.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.