Create Magento 2 installation script

Hello to Amasty blog readers!

Today I wanted to share a simple tool which will save you some time, if you’re a Magento developer and if you create extensions with a significant amount of database tables.

Note: to use this script, you should have basic knowledge of Magento 2 databases. If you don’t understand the terms and processes described here, it’s better to update your knowledge and come back when you’re ready.

When we create Magento 2 extensions, we firstly think over the database structure, create it in a chosen editor and after that transfer it to the installation scripts of the extension. Well, it sounds okay if you’re not going to create around 20 (or even more) database tables manually.

On the tenth one I decided to cut the story short and to automatize this process. That’s how the script was born. You just upload the database tables dump into it and receive a ready function for creating this table.

First of all, you should download the script (see the end of the article) and install it.

Now it’s time to make a dump of the database table via the console. Here’s the example of the command:

mysqldump -h mysql55.sty -p barbarich_allinone mp_blog_authors

It will give us the following:

CREATE TABLE `amasty_test` (
  `purchased_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Purchased ID',
  `order_id` int(10) unsigned DEFAULT '0' COMMENT 'Order ID',
  `order_increment_id` varchar(50) DEFAULT NULL COMMENT 'Order Increment ID',
  `order_item_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Order Item ID',
  `customer_id` int(10) unsigned DEFAULT '0' COMMENT 'Customer ID',
  `product_name` varchar(255) DEFAULT NULL COMMENT 'Product name',
  `product_sku` varchar(255) DEFAULT NULL COMMENT 'Product sku',
  `link_section_title` varchar(255) DEFAULT NULL COMMENT 'Link_section_title',
  PRIMARY KEY (`purchased_id`),
  KEY `DOWNLOADABLE_LINK_PURCHASED_ORDER_ID` (`order_id`),
  KEY `DOWNLOADABLE_LINK_PURCHASED_ORDER_ITEM_ID` (`order_item_id`),
  KEY `DOWNLOADABLE_LINK_PURCHASED_CUSTOMER_ID` (`customer_id`),
  CONSTRAINT `DL_LNK_PURCHASED_CSTR_ID_CSTR_ENTT_ENTT_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL,
  CONSTRAINT `DOWNLOADABLE_LINK_PURCHASED_ORDER_ID_SALES_ORDER_ENTITY_ID` FOREIGN KEY (`order_id`) REFERENCES `sales_order` (`entity_id`) ON DELETE SET NULL
) 

Copy this piece of code and insert it into the field of the script. When the script is done working, you’ll see something like this:

 protected function createdownloadable_link_purchased($installer) 
    {
        $table = $installer->getConnection()->newTable(
            $installer->getTable('downloadable_link_purchased')
        )->addColumn(
            'purchased_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            10,
            ['unsigned' => true, 'nullable' => false, 'identity' => true, 'primary' => true],
            ''
        )->addColumn(
            'order_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            10,
            ['unsigned' => true, 'default' => '0'],
            ''
        )->addColumn(
            'order_increment_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            50,
            ['default' => 'NULL'],
            ''
        )->addColumn(
            'order_item_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            10,
            ['unsigned' => true, 'nullable' => false, 'default' => '0'],
            ''
        )->addColumn(
            'customer_id',
            \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
            10,
            ['unsigned' => true, 'default' => '0'],
            ''
        )->addColumn(
            'product_name',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            255,
            ['default' => 'NULL'],
            ''
        )->addColumn(
            'product_sku',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            255,
            ['default' => 'NULL'],
            ''
        )->addColumn(
            'link_section_title',
            \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            255,
            ['default' => 'NULL'],
            ''
        )->addIndex(
            $installer->getIdxName('downloadable_link_purchased', ['order_id']),
            ['order_id']
        )->addIndex(
            $installer->getIdxName('downloadable_link_purchased', ['order_item_id']),
            ['order_item_id']
        )->addIndex(
            $installer->getIdxName('downloadable_link_purchased', ['customer_id']),
            ['customer_id']
        )->addForeignKey(
            $installer->getFkName('downloadable_link_purchased', 'customer_id', 'customer_entity', 'entity_id'),
            'customer_id',
            $installer->getTable('customer_entity'),
            'entity_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_SET_NULL
        )
        ->addForeignKey(
            $installer->getFkName('downloadable_link_purchased', 'order_id', 'sales_order', 'entity_id'),
            'order_id',
            $installer->getTable('sales_order'),
            'entity_id',
            \Magento\Framework\DB\Ddl\Table::ACTION_SET_NULL
        )
        ;
        $installer->getConnection()->createTable($table);
    }

Now you can use this function and add it to the extension installation script. How to use it? In short, go to InstallSchema.php, find the Install function, call the generated function and copy the function itself. It will look like this (I didn’t copy the whole body of the function, it’s just for giving you the idea):

public function install(SchemaSetupInterface $setup, ModuleContextInterface $context)
{
   $installer = $setup;

   $installer->startSetup();

   $this->createamasty_test22($installer);


   $installer->endSetup();
}

protected function createamasty_test22($installer)
{
   $table = $installer->getConnection()->newTable(
       $installer->getTable('amasty_test22')
   )->addColumn(
       'purchased_id',
       \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
       10,
       ['unsigned' => true, 'nullable' => false, 'identity' => true, 'primary' => true]
   )

Here’s the download link of the script:

Download Now!

I hope this small tool will be useful for you. If you have any questions, welcome to ask them in comments!