Upgrade Schema in Magento 2

When you want to update your database tables, You need to write UpgradeSchema Script instead of InstallSchema.

So lets start,

To run upgradeSchema.php first you need to change schema_versionin module.xml. For example if it is 1.0.0 then you need to change it to 1.0.1.

Then Write UpgradeSchema.php

<?php

namespace PME\ReviewBooster\Setup;

use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\DB\Ddl\Table;


class UpgradeSchema implements  UpgradeSchemaInterface
{
    /**
     * @param SchemaSetupInterface $setup
     * @param ModuleContextInterface $context
     * @throws \Zend_Db_Exception
     */
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        $setup->startSetup();

        if (version_compare($context->getVersion(), '1.0.1', '<')) {
          /**
         * create table 'pme_review_voting'
         */
          $table = $setup->getConnection()
          ->newTable($setup->getTable('pme_review_voting'))
            ->addColumn(
              'voting_id', 
              Table::TYPE_SMALLINT, 
              11,
              ['nullable' => true, 'primary' => true, 'auto_increment' => true],
              'Voting ID'
              )
              ->addColumn(
                  'pme_review_id',
                  Table::TYPE_BIGINT,
                  null, ['nullable' => false,'unsigned' => true],
                  'pme_review_id ID'
              )
              ->addColumn(
                  'customer_id',
                  Table::TYPE_BIGINT,
                  null, ['nullable' => false],
                  'customer ID'
              )
            ->addColumn(
                'upvotes',
                Table::TYPE_SMALLINT,
                11, ['nullable' => false],
                'Upvotes'
            )
            ->addColumn(
                'downvotes',
                Table::TYPE_TEXT,
                11, ['nullable' => false],
                'Downvotes'
            )
              ->addForeignKey(
                  $setup->getFkName(
                      'pme_review_voting',
                      'pme_review_id',
                      'review_id',
                      'review'
                  ),
                  'pme_review_id',
                  $setup->getTable('review'),
                  'review_id',
                  Table::ACTION_CASCADE
              )
            ->setComment(
                'Review Voting'
            ) ;

        $setup->getConnection()->createTable($table);
            /* review_details table */

          $setup->getConnection()->addColumn(
            $setup->getTable("review_detail"),
            'email',
            [
              "type" => Table::TYPE_TEXT,
              "nullable" => true,
              "comment" => "Customer Email"
            ]
          );
          $setup->getConnection()->addColumn(
            $setup->getTable("review_detail"),
            'admin_reply_title',
            [
              "type" => Table::TYPE_TEXT,
              "nullable" => true,
              "comment" => "Admin Reply Title"
            ]
          );
          $setup->getConnection()->addColumn(
            $setup->getTable("review_detail"),
            'admin_reply_comment',
            [
              "type" => Table::TYPE_TEXT,
              "nullable" => true,
              "comment" => "Admin Reply Comment"
            ]
          );
          $setup->getConnection()->addColumn(
            $setup->getTable("review_detail"),
            'verified_user',
            [
              "type" => Table::TYPE_INTEGER,
              "default" => 0,
              "comment" => "Verified User"
            ]
          );
          $setup->getConnection()->addColumn(
            $setup->getTable("review_detail"),
            'pros',
            [
              "type" => Table::TYPE_TEXT,
              "nullable" => true,
              "comment" => "Product Pros"
            ]
          );
          $setup->getConnection()->addColumn(
            $setup->getTable("review_detail"),
            'cons',
            [
              "type" => Table::TYPE_TEXT,
              "nullable" => true,
              "comment" => "Product Cons"
            ]
          );
          $setup->getConnection()->addColumn(
            $setup->getTable("review_detail"),
            'images',
            [
              "type" => Table::TYPE_TEXT,
              "nullable" => true,
              "comment" => "User Upload Images"
            ]
          );
          $setup->getConnection()->addColumn(
            $setup->getTable("review_detail"),
            'recommended',
            [
              "type" => Table::TYPE_INTEGER,
              "default" => 0,
              "comment" => "Recommended Product"
            ]
          );
          $setup->getConnection()->addColumn(
            $setup->getTable("review_detail"),
            'is_subscribed',
            [
              "type" => Table::TYPE_INTEGER,
              "default" => 0,
              "comment" => "Subscribed Review Notifiactions"
            ]
          );

        }
        $setup->endSetup();
    }
}

Remember one thing. I have added foreign key in above table. so the type and size of the foreign key should be the same. Otherwise it will create an error.

About Ejaz Alam

A Software Engineer who Believes in a simple lifestyle and follow a simple logic to make himself better than yesterday. :)

View all posts by Ejaz Alam →

Leave a Reply

Your email address will not be published. Required fields are marked *