|
Magento is an open-source e-commerce platform, developed by Roy Rubin and Yoav Kutner. It is a PHP framework that utilizes the PHP language. Magento leverages a Content Management System (CMS) to create online stores. To create a new database table in Magento, you need to define a schema file and use Magento’s setup system to create the table during module installation or upgrade.
Below are the approaches to create a database table in Magento:
By using InstallSchemaTo create a table in Magento using InstallSchema, follow these steps:
- Create a file InstallSchema.php inside your module directory app/code/[ Vendor ]/[ Module] /Setup
- Implement the InstallSchemaInterface, which includes an install function.
- Start the setup process.
- Check if the table already exists. If it does not, proceed to create the table.
- Use the addColumn method to define the columns for the table.
- End the setup process.
Here’s a detailed step-by-step guide to illustrate this process:First, create a PHP file for the schema installation script. This file will implement the InstallSchemaInterface and its install method. Within the install method, you will start the setup process and check if the table exists. If it does not, you will create the table by defining its columns using the addColumn method. Finally, you will end the setup process to complete the table creation.
By following these steps, you can efficiently create a new database table in Magento during module installation
Example: This example shows the creation of the table in Magento.
PHP
<?php
/**
* Copyright © Magento, Inc. All rights reserved.
* See COPYING.txt for license details.
*/
namespace Md\Blog\Setup;
use Magento\Framework\DB\Ddl\Table;
use Magento\Framework\Setup\InstallSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
/**
* @codeCoverageIgnore
*/
class InstallSchema implements InstallSchemaInterface {
/**
* {@inheritdoc}
* @SuppressWarnings(PHPMD.ExcessiveMethodLength)
*/
public function install(SchemaSetupInterface $setup,
ModuleContextInterface $context) {
$installer = $setup;
$installer->startSetup();
/**
* Create table 'md_blog'
*/
if (!$installer->tableExists('md_blog')) {
$table = $installer->getConnection()->newTable(
$installer->getTable('md_blog')
)->addColumn(
'id',
Table::TYPE_INTEGER,
null,
[
'identity' => true,
'nullable' => false,
'primary' => true,
'unsigned' => true,
],
'ID'
)->addColumn(
'name',
Table::TYPE_TEXT,
255,
['nullable' => false],
'Name'
)->addColumn(
'title',
Table::TYPE_TEXT,
255,
['nullable' => false],
'Blog Title'
)->addColumn(
'content',
Table::TYPE_TEXT,
'2M',
[],
'Blog Content'
)->addColumn(
'status',
Table::TYPE_SMALLINT,
null,
['nullable' => false],
'Status'
)->addColumn(
'created_at',
Table::TYPE_TIMESTAMP,
null,
['nullable' => false, 'default' => Table::TIMESTAMP_INIT],
'Created At'
)->setComment('Blog Table');
$installer->getConnection()->createTable($table);
// Add full-text index
$installer->getConnection()->addIndex(
$installer->getTable('md_blog'),
$setup->getIdxName(
$installer->getTable('md_blog'),
['name', 'title', 'content'],
\Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
),
['name', 'title', 'content'],
\Magento\Framework\DB\Adapter\AdapterInterface::INDEX_TYPE_FULLTEXT
);
}
$installer->endSetup();
}
}
Upgrade the version of module:
 Run command:
php bin/magento setup:upgrade Check your database is created or not:
By using UpgradeSchemaIn Magento, the UpgradeSchema class is used to modify existing database tables during module upgrades. This allows you to add new columns, change existing ones, or make other structural changes. In this guide, we’ll walk through the process of creating an UpgradeSchema.php file to add new columns to an existing table.
Step-by-Step Guide to Using UpgradeSchema:- Create the UpgradeSchema.php File
- First, navigate to the Setup folder of your module and create a new file named UpgradeSchema.php. This file will implement the UpgradeSchemaInterface.
- Implement UpgradeSchemaInterfaceIn UpgradeSchema.php, implement the UpgradeSchemaInterface and its upgrade method. This method will contain the logic for modifying the database schema.
- Start the Setup Process
- Within the upgrade method, start the setup process by initializing the schema setup.
- Check for Existing Table
- Before making changes, check if the table you want to modify exists. This ensures that your script runs without errors if the table is missing.
- Add New Columns
- Use the addColumn method to add new columns to the existing table. In this example, we’ll add email and cust_id fields.
- End the Setup Process
- Finally, end the setup process to apply the changes.
Example: Here is an example of what your UpgradeSchema.php file might look like:
PHP
<?php
namespace Md\Blog\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
{
public function upgrade(SchemaSetupInterface $setup,
ModuleContextInterface $context)
{
$installer = $setup;
$installer->startSetup();
if (version_compare($context->getVersion(), '1.3.0', '<')) {
$tableName = $installer->getTable('Md_blog');
if ($installer->getConnection()->isTableExists($tableName)) {
$table = $installer->getConnection()
->addColumn(
$tableName,
'email',
[
'type' => Table::TYPE_TEXT,
'nullable' => false,
'length' => 255,
'comment' => 'Email'
]
);
}
}
if (version_compare($context->getVersion(), '1.6.0', '<')) {
$tableName = $installer->getTable('Md_blog');
if ($installer->getConnection()->isTableExists($tableName)) {
// Add a new column with the desired data type
$installer->getConnection()->addColumn(
$tableName,
'cust_id',
[
'type' => Table::TYPE_INTEGER,
'nullable' => false,
'comment' => 'Customer ID (New)',
'after' => 'id'
]
);
} }
$installer->endSetup();
}}
- For running upgrade schema you have to go etc/module.xml file
- Increase the setup_version to the version you mentioned in the UpgradeSchema.php file
Run command:
php bin/magento setup:upgrade By Creating a db_schema.xml file- In module directory create a etc folder if not exist. If exist then create a db_schema.xml file
- In this file we are defining structure of a database table , column, constraints
- db_schema.xml is easier way to create a table in magento.
Example: This example shows the creation of the database.
XML
<?xml version="1.0"?>
<schema xmlns:xsi=
"http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation=
"urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
<table name="mageplaza_helloworld_post" resource="default"
engine="innodb" comment="Post Table">
<column xsi:type="smallint" name="post_id"
padding="6" unsigned="false"
nullable="false" identity="true"
comment="Post ID"/>
<column xsi:type="varchar" name="name"
nullable="false" length="255"
comment="Post Name"/>
<column xsi:type="varchar" name="url_key"
nullable="false" length="255"
comment="Post URL Key"/>
<column xsi:type="text" name="post_content"
nullable="false" comment="Post Post Content"/>
<column xsi:type="varchar" name="tags"
nullable="false" length="255"
comment="Post Tags"/>
<column xsi:type="int" name="status"
nullable="false" comment="Post Status"/>
<column xsi:type="varchar" name="featured_image"
nullable="false" length="255"
comment="Post Featured Image'"/>
<column xsi:type="timestamp" name="created_at"
nullable="false" default="CURRENT_TIMESTAMP"
comment="Created At"/>
<column xsi:type="timestamp" name="updated_at"
nullable="false" default="CURRENT_TIMESTAMP"
comment="Updated At"/>
<constraint xsi:type="primary" referenceId="PRIMARY">
<column name="post_id"/>
</constraint>
<index referenceId="MAGEPLAZA_HELLOWORLD_POST_FT_INDEX"
indexType="fulltext">
<column name="name"/>
<column name="url_key"/>
<column name="post_content"/>
<column name="tags"/>
<column name="featured_image"/>
</index>
</table>
</schema>
|
