To add a new table to the Magento 2 database, you need to create a file in the module folder:
app/code/<VendorName>/<ModuleName>/Setup/InstallSchema.php
add the following code to it:
<?php
namespace VendorName\ModuleName\Setup;
use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\DB\Adapter\AdapterInterface;
class InstallSchema implements InstallSchemaInterface
{
public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
{
$installer = $setup;
$installer->startSetup();
//new table script will be there
$installer->endSetup();
}
}
InstallSchema.php — is a file that is responsible for modifying the database structure during module installation. When executing the CLI command php bin/magento setup:upgrade Magento 2 checks whether a new module has appeared in the system and if it contains the InstallSchema.php file. If it does, this file calls its install method ($ setup, context).
Note: InstallSchema.php will not work if the module has been installed before.
Data on the versions of the installed modules is stored in the table "setup_module", located in the Magento 2 database. If there was created an unexpected structure in the database during the test installation of the module, delete it together with a record of your module from the table "setup_module".
Correct the code and run the php bin/magento setup:upgrade command again.
In the example, we will create a table for Frequently Asked Question (FAQ) and a table for storing FAQ dependencies — Store View, in other words, which languages the FAQ article will be available in.
Replace the "// new table script will be there" comment in the InstallSchema.php file with the following code:
/**
* Create table 'magefan_faq'
*/
$table = $installer->getConnection()->newTable(
$installer->getTable('magefan_faq')
)->addColumn(
'faq_id',
\Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
null,
['identity' => true, 'nullable' => false, 'primary' => true],
'FAQ ID'
)->addColumn(
'title',
\Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
255,
['nullable' => true],
'FAQ Title'
)->addColumn(
'meta_keywords',
\Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
'64k',
['nullable' => true],
'FAQ Meta Keywords'
)->addColumn(
'meta_description',
\Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
'64k',
['nullable' => true],
'FAQ Meta Description'
)->addColumn(
'identifier',
\Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
100,
['nullable' => true, 'default' => null],
'FAQ String Identifier'
)->addColumn(
'content_heading',
\Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
255,
['nullable' => true],
'FAQ Content Heading'
)->addColumn(
'content',
\Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
'2M',
[],
'FAQ Content'
)->addColumn(
'creation_time',
\Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
null,
[],
'FAQ Creation Time'
)->addColumn(
'update_time',
\Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
null,
[],
'FAQ Modification Time'
)->addColumn(
'is_active',
\Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
null,
['nullable' => false, 'default' => '1'],
'Is FAQ Active'
)->addIndex(
$installer->getIdxName('magefan_faq', ['identifier']),
['identifier']
)->setComment(
'Magefan FAQ Table'
);
$installer->getConnection()->createTable($table);
/**
* Create table 'magefan_faq_store'
*/
$table = $installer->getConnection()->newTable(
$installer->getTable('magefan_faq_store')
)->addColumn(
'faq_id',
\Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
null,
['nullable' => false, 'primary' => true],
'FAQ ID'
)->addColumn(
'store_id',
\Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT,
null,
['unsigned' => true, 'nullable' => false, 'primary' => true],
'Store ID'
)->addIndex(
$installer->getIdxName('magefan_faq_store', ['store_id']),
['store_id']
)->addForeignKey(
$installer->getFkName('magefan_faq_store', 'faq_id', 'magefan_faq', 'faq_id'),
'faq_id',
$installer->getTable('magefan_faq'),
'faq_id',
\Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)->addForeignKey(
$installer->getFkName('magefan_faq_store', 'store_id', 'store', 'store_id'),
'store_id',
$installer->getTable('store'),
'store_id',
\Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)->setComment(
'Magefan FAQ To Store Linkage Table'
);
$installer->getConnection()->createTable($table);
The following is a specification of the methods.
Method addColumn:
/** * Adds a column to the table. * * $options contains additional options for the column. Allowed parameters: * - 'unsigned', is used only for numeric types. By default, gets the FALSE value; * - 'precision', is used only for numeric types. By default, gets a value from the $ size parameter, otherwise 0; * - 'scale', is used only for numeric types. By default, gets a value from the $ size parameter, otherwise equal to 10; * - 'default', no default value; * - 'nullable', default value: TRUE; * - 'primary', adds a column to the primary index. By default, the column is not added to the primary index; * - 'primary_position', the column from the primary index is used. The default value: number of primary columns + 1; * - 'identity' or 'auto_increment'. no default value; * * @param string $name - column name; * @param string $type - column data type; * @param string|integer|array $size - length (size) of the column; * @param array $options array with additional options; * @param string $comment - comment to the column; * @return $this */ public function addColumn($name, $type, $size = null, $options = [], $comment = null)
Method setComment:
/**
* Sets a comment to the table.
*
* @param string $comment - text of the comment;
* @return $this
*/
public function setComment($comment)
Method addIndex:
/**
* Adds an index to the table.
*
* @param string $indexName - index name;
* @param array or string $fields - an array of column names or a column name;
* @param array $options - an array of additional options;
* @return $this
*/
public function addIndex($indexName, $fields, $options = [])
Method addForeignKey:
/** * Adds foreign key to the table. * * @param string $fkName - the name of the foreign key; * @param string $column - the name of the column key for the foreign key; * @param string $refTable - the name of the foreign table; * @param string $refColumn - name of foreign columns; * @param string $onDelete - event to delete a line; * @return $this */ public function addForeignKey($fkName, $column, $refTable, $refColumn, $onDelete = null)
All these methods are declared in the vendor/magento/framework/DB/Ddl/Table.php file.
Save the InstallSchema.php file. If your module is in the 'setup_module' table, remove it.
Start the module installation process, run the CLI command:
php bin/magento setup:upgrade
Check the structure of the database, for example with phpMyAdmin. You will see new tables.
The Magento 2 database contains about 200 tables, if you can't find yours, go to the next page of the list.
You can view the modified files of the Magefan_Faq module on GitHub.
