Creating Setup script in Magento 2

 

This is part 1 of two parts article. See part 2 by clicking here.

Magento 1’s ORM (object relationship mapper) is still available in Magento 2. That workes on Active Record inspired pattern that uses Model, Resource Model, and Resource Model collection classes. But because of Magento’s new object system we need to create different type of class in order to support for Magento 2. We will see under this article how to use Magento 2’s Models.

Advantage of Magento 2 Setup script over Magento 1

In Magento 1 database setup script is devided into two parts a) install script b) update script. On the same track database setup of Magento 2 database setup has managed in two files itself. Magento 2 has a very good setup mechanism for database related implementation. Complete implementation is managed in Magento 2 using two scripts. With the improved technology of magento you will see couple of good things.

i) It still based on setup version, at the time of setup upgrade command, if it sees that larger setup version available under module.xml file then it automatically calls the UpgradeSchema.php and runs the corresponding script.

ii) Other good thing is that now there is no need to create multiple upgrade file for schema upgrade instead only one upgradeschema.php file to be created.

In this article consist of two parts.
a) Create table using setup script in Magento 2 – We will see how to create a table using installscript (InstallSchema.php) in magento .Purpose of InstallSchema.php is to create databse table structure for database models.

b) Update table schema script in Magento 2 – we will see how to update a table using setup based updateSchema.php file.Purpose of UpgradeSchema is to update the existing db table structure in order to update into existing models.

Lets see both part in action

Part a) Create table using setup script in Magento 2


Create a new script app\code\Cutehits\First\Setup\InstallSchema.php
Goal: Create a table named cutehits_custom_users which has following structure. If you wanted to see how its working on Magento 1 then click here.

 

cutehits_custom_users
user_id int
name varchar
mobile varchar
email varchar
status int
user_image varchar
<?php
namespace Cutehits\First\Setup;

class InstallSchema implements \Magento\Framework\Setup\InstallSchemaInterface
{
    /**
     * install tables
     *
     * @param \Magento\Framework\Setup\SchemaSetupInterface $setup
     * @param \Magento\Framework\Setup\ModuleContextInterface $context
     * @return void
     * @SuppressWarnings(PHPMD.ExcessiveMethodLength)
     */
    public function install(\Magento\Framework\Setup\SchemaSetupInterface $setup, \Magento\Framework\Setup\ModuleContextInterface $context)
    {
        $installer = $setup;
        $installer->startSetup();
        if (!$installer->tableExists('cutehits_custom_users')) {
            $table = $installer->getConnection()->newTable(
                $installer->getTable('cutehits_custom_users')
            )
            ->addColumn(
                'user_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                [
                    'identity' => true,
                    'nullable' => false,
                    'primary'  => true,
                    'unsigned' => true,
                ],
                'User ID'
            )
            ->addColumn(
                'name',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                255,
                ['nullable => false'],
                'Name'
            )
            ->addColumn(
                'mobile',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                50,
                [],
                'Mobile No.'
            )
            ->addColumn(
                'email',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                '64k',
                [],
                'Email'
            )
            ->addColumn(
                'status',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                1,
                [],
                'Post Status'
            )
            ->addColumn(
                'user_image',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                255,
                [],
                'Post Featured Image'
            )

            ->addColumn(
                'created_at',
                \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
                null,
                [],
                'Created At'
            )
            ->addColumn(
                'updated_at',
                \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP,
                null,
                [],
                'Updated At'
            )
            ->setComment('User Table');
            $installer->getConnection()->createTable($table);

            $installer->getConnection()->addIndex(
                $installer->getTable('cutehits_custom_users'),
                $setup->getIdxName(
                    $installer->getTable('cutehits_custom_users'),
                    ['name','email'],
                    \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
                ),
                ['name','email'],
                \Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
            );
        }
        $installer->endSetup();
    }
}

Code Analysis:
Above script check if the table name “cutehits_custom_users” doesn’t exist then it will create a new table and we have added
multiple columns into it by using addColumn (link addColumn of magento 1) by below line of code.

->addColumn(
                'user_id',
                \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                null,
                [
                    'identity' => true,
                    'nullable' => false,
                    'primary'  => true,
                    'unsigned' => true,
                ],
                'User ID'
            )

where user_id is column name then it has data type (\Magento\Framework\DB\Ddl\Table::TYPE_INTEGER) with its default value null and their property like its primary key and column description.

Apart from the addColumn, We have also used one more method “getIdxName” which creates fulltext index for name and email field.

 

After creating the script we need to run the command

php bin/magento setup:upgrade

and you are done. Just check your table in database it should be there.

If not there then its because your module is already installed previously. So just delete this entry from

select * from setup_module where module = 'Module_Name';
and rerun the above command.

those install file will be used for first time install the module. If you want to change the database when upgrade module, please try to use UpgradeSchema.php and UpgradeData.php.

b) Update table schema script in Magento 2


Why update Schema: It might be that due to any requirement change you need to modify existing table architecture. In this case we need to create UpdateSchema.php file. Creating and running UpdateSchema is a 4 step process

Step 1: Crerate UpdateSchema.php file

<?php
namespace Cutehits\First\Setup;
use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
class UpgradeSchema implements  UpgradeSchemaInterface
{
    public function upgrade(SchemaSetupInterface $setup,
                            ModuleContextInterface $context){
        $setup->startSetup();

        //if (version_compare($context->getVersion(), '1.0.0') < 0) {

            // Get module table
            $tableName = $setup->getTable('cutehits_custom_users');

            // Check if the table already exists
            if ($setup->getConnection()->isTableExists($tableName) == true) {

                // Declare data
                $columns = [
                    'age' => [
                        'type' => \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
                        'nullable' => false,
                        'comment' => 'Age',
                    ],
                ];

                $connection = $setup->getConnection();
                foreach ($columns as $name => $definition) {
                    $connection->addColumn($tableName, $name, $definition);
                }
                /* $connection->addColumn(
                'age',
                \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
                255,
                ['nullable => false'],
                'Name'
            );*/

                //Update data
                $connection->changeColumn(
                    $tableName,
                    'created_at',
                    'registration_date',
                    ['type' => \Magento\Framework\DB\Ddl\Table::TYPE_TIMESTAMP, 'nullable' => false, 'default' => ''],
                    'Registration date'
                );

            }
       // }

        $setup->endSetup();
    }
}

Step 2: updated module.xml file

modify your app\code\Cutehits\First\etc\module.xml and update setup_version for example

<module name="Cutehits_First" setup_version="1.0.0"></module>

to

<module name="Cutehits_First" setup_version="1.0.1"> </module>

This new version number will be compared from existing version number.
Step 3: Run setup upgrade

php bin/magento setup:upgrade

Related posts:

The following two tabs change content below.

Chandra Shekhar

Consultant at Infogain
Chandra Shekhar is a LAMP Developer,Thinker,Blogger and currently working as Magento(Ecommerce) specialist.. I am always open to learn new things in LAMP as well as other newer technology. In the journey of my last 9 years of web and product development, I have decided to share my knowledge and experience to Open Source Community in 2011. In this regard I have started CuteHits.com. Since then I am actively sharing my knowledge on various LAMP technology through CuteHits.com’s technical ... Read More about Chandra Shekhar

Latest posts by Chandra Shekhar (see all)