Creating an install schema script (InstallSchema.php)

Now that we understand the flow of schema and data scripts and their relation to the module version number, let us go ahead and start assembling our InstallSchema. We start by defining the app/code/Foggyline/Office/Setup/InstallSchema.php file with (partial) content as follows:

namespace Foggyline\Office\Setup;

use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

class InstallSchema implements InstallSchemaInterface
{
    public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();
        /* #snippet1 */
        $setup->endSetup();
    }
}

InstallSchema conforms to InstallSchemaInterface, which requires the implementation of the install method that accepts two parameters of type SchemaSetupInterface and ModuleContextInterface.

The install method is all that is required here. Within this method, we would add any relevant code we might have to create the tables and columns we need.

Looking through the code base, we can see that Magento\Setup\Module\Setup is the one extending \Magento\Framework\Module\Setup and implementing SchemaSetupInterface. The two methods seen in the preceding code, startSetup and endSetup, are used to run additional environment setup before and after our code.

Going further, let's replace the /* #snippet1 */ bit with code that will create our Department model entity table as follows:

$table = $setup->getConnection()
    ->newTable($setup->getTable('foggyline_office_department'))
    ->addColumn(
        'entity_id',
        \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
        null,
        ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
        'Entity ID'
    )
    ->addColumn(
        'name',
        \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
        64,
        [],
        'Name'
    )
    ->setComment('Foggyline Office Department Table');
$setup->getConnection()->createTable($table);
/* #snippet2 */

Here, we are instructing Magento to create a table named foggyline_office_department, add entity_id and name columns to it, and set the comment on the table. Assuming we are using the MySQL server, when code executes, the following SQL gets executed in the database:

CREATE TABLE 'foggyline_office_department' (
  'entity_id' int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  'name' varchar(64) DEFAULT NULL COMMENT 'Name',
  PRIMARY KEY ('entity_id')
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='Foggyline Office Department Table';

The addColumn method is the most interesting one here. It takes five parameters, from column name, column data type, column length, array of additional options, and column description. However, only column name and column data type are mandatory! Accepted column data types can be found under the Magento\Framework\DB\Ddl\Table class, and go as follows:

boolean     smallint    integer     bigint
float       numeric     decimal     date
timestamp   datetime    text        blob
varbinary

An additional options array might contain some of the following keys: unsigned, precision, scale, unsigned, default, nullable, primary, identity, auto_increment.

Having gained insight into the addColumn method, let's go ahead and create the foggyline_office_employee_entity table for the Employee entity as well. We do so by replacing the /* #snippet2 */ bit from the preceding code with the following code:

$employeeEntity = \Foggyline\Office\Model\Employee::ENTITY;
$table = $setup->getConnection()
    ->newTable($setup->getTable($employeeEntity . '_entity'))
    ->addColumn(
        'entity_id',
        \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
        null,
        ['identity' => true, 'unsigned' => true, 'nullable' => false, 'primary' => true],
        'Entity ID'
    )
    ->addColumn(
        'department_id',
        \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
        null,
        ['unsigned' => true, 'nullable' => false],
        'Department Id'
    )
    ->addColumn(
        'email',
        \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
        64,
        [],
        'Email'
    )
    ->addColumn(
        'first_name',
        \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
        64,
        [],
        'First Name'
    )
    ->addColumn(
        'last_name',
        \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
        64,
        [],
        'Last Name'
    )
    ->setComment('Foggyline Office Employee Table');
$setup->getConnection()->createTable($table);
/* #snippet3 */

Following good database design practices, we might notice one thing here. If we agree that every employee can be assigned a single department, we should add a foreign key to this table's department_id column. For the moment, we will purposely skip this bit, as we want to demonstrate this through the update schema script later on.

EAV models scatter their data across several tables, three at a minimum. The table foggyline_office_employee_entity that we just created is one of them. The other one is the core Magento eav_attribute table. The third table is not a single table, rather a list of multiple tables; one for each EAV type. These tables are the result of our install script.

Information stored within the core Magento eav_attribute table is not the value of an attribute or anything like it; information stored there is an attribute's metadata. So how does Magento know about our Employee attributes (service_years, dob, salary, vat_number, note)? It does not; not yet. We need to add the attributes into that table ourselves. We will do so later on, as we demonstrate the InstallData.

Depending on the EAV attribute data type, we need to create the following tables:

• foggyline_office_employee_entity_datetime
• foggyline_office_employee_entity_decimal
• foggyline_office_employee_entity_int
• foggyline_office_employee_entity_text
• foggyline_office_employee_entity_varchar

The names of these attribute value tables come from a simple formula, which says {name of the entity table}+{_}+{eav_attribute.backend_type value}. If we look at the salary attribute, we need it to be a decimal value, thus it will get stored in foggyline_office_employee_entity_decimal.

Given the chunkiness of code behind defining attribute value tables, we will focus only on a single, decimal type table. We define it by replacing /* #snippet3 */ from the preceding code with the following bit:

$table = $setup->getConnection()
    ->newTable($setup->getTable($employeeEntity . '_entity_decimal'))
    ->addColumn(
        'value_id',
        \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
        null,
        ['identity' => true, 'nullable' => false, 'primary' => true],
        'Value ID'
    )
    ->addColumn(
        'attribute_id',
        \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
        null,
        ['unsigned' => true, 'nullable' => false, 'default' => '0'],
        'Attribute ID'
    )
    ->addColumn(
        'store_id',
        \Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
        null,
        ['unsigned' => true, 'nullable' => false, 'default' => '0'],
        'Store ID'
    )
    ->addColumn(
        'entity_id',
        \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
        null,
        ['unsigned' => true, 'nullable' => false, 'default' => '0'],
        'Entity ID'
    )
    ->addColumn(
        'value',
        \Magento\Framework\DB\Ddl\Table::TYPE_DECIMAL,
        '12,4',
        [],
        'Value'
    )
    //->addIndex
    //->addForeignKey
    ->setComment('Employee Decimal Attribute Backend Table');
$setup->getConnection()->createTable($table);

Notice the //->addIndex part within code above. Lets replace it with the following bit.

->addIndex(
    $setup->getIdxName(
        $employeeEntity . '_entity_decimal',
        ['entity_id', 'attribute_id', 'store_id'],
        \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE
    ),
    ['entity_id', 'attribute_id', 'store_id'],
    ['type' => \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_UNIQUE]
)
->addIndex(
    $setup->getIdxName($employeeEntity . '_entity_decimal', ['store_id']),
    ['store_id']
)
->addIndex(
    $setup->getIdxName($employeeEntity . '_entity_decimal', ['attribute_id']),
    ['attribute_id']
)

The preceding code adds three indexes on the foggyline_office_employee_entity_decimal table, resulting in a SQL as follows:

• UNIQUE KEY 'FOGGYLINE_OFFICE_EMPLOYEE_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID' ('entity_id','attribute_id','store_id')
• KEY 'FOGGYLINE_OFFICE_EMPLOYEE_ENTITY_DECIMAL_STORE_ID' ('store_id')
• KEY 'FOGGYLINE_OFFICE_EMPLOYEE_ENTITY_DECIMAL_ATTRIBUTE_ID' ('attribute_id')

Similarly, we replace the //->addForeignKey part from the preceding code with the following bit:

->addForeignKey(
    $setup->getFkName(
        $employeeEntity . '_entity_decimal',
        'attribute_id',
        'eav_attribute',
        'attribute_id'
    ),
    'attribute_id',
    $setup->getTable('eav_attribute'),
    'attribute_id',
    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)
->addForeignKey(
    $setup->getFkName(
        $employeeEntity . '_entity_decimal',
        'entity_id',
        $employeeEntity . '_entity',
        'entity_id'
    ),
    'entity_id',
    $setup->getTable($employeeEntity . '_entity'),
    'entity_id',
    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)
->addForeignKey(
    $setup->getFkName($employeeEntity . '_entity_decimal', 'store_id', 'store', 'store_id'),
    'store_id',
    $setup->getTable('store'),
    'store_id',
    \Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)

The preceding code adds foreign key relations into the foggyline_office_employee_entity_decimal table, resulting in a SQL as follows:

• CONSTRAINT 'FK_D17982EDA1846BAA1F40E30694993801' FOREIGN KEY ('entity_id') REFERENCES 'foggyline_office_employee_entity' ('entity_id') ON DELETE CASCADE,
• CONSTRAINT 'FOGGYLINE_OFFICE_EMPLOYEE_ENTITY_DECIMAL_STORE_ID_STORE_STORE_ID' FOREIGN KEY ('store_id') REFERENCES 'store' ('store_id') ON DELETE CASCADE,
• CONSTRAINT 'FOGGYLINE_OFFICE_EMPLOYEE_ENTT_DEC_ATTR_ID_EAV_ATTR_ATTR_ID' FOREIGN KEY ('attribute_id') REFERENCES 'eav_attribute' ('attribute_id') ON DELETE CASCADE

Notice how we added the store_id column to our EAV attribute value tables. Though our examples won't find use of it, it is a good practice to use store_id with your EAV entities to scope the data for a possible multi-store setup. To clarify further, imagine we had a multi-store setup, and with EAV attribute tables set up like the preceding one, we would be able to store a different attribute value for each store, since the unique entry in the table is defined as a combination of entity_id, attribute_id, and store_id columns.

Tip

For the reasons of performance and data integrity, it is important to define indexes and foreign key as per good database design practice. We can do so within InstallSchema when defining new tables.