Part 2: Continued to the series post of Database operation in Magento 2. In this post, I am going to cover: how to rename and drop a database table.
Rename a table:
As in the last post we created a table name “custom_form”, using a declarative schema. Here we are going to rename the same table and migrate data from that table to the new table.
The declarative schema will create a new table with the new name and drop the table with the old name. To migrate data from another table, specify the onCreate attribute on the table declaration, and add specify the source table name:
db_schema.xml:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?xml version="1.0"?> <!-- /** * Copyright © Magento, Inc. All rights reserved. * See COPYING.txt for license details. */ --> <schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd"> + <table name="custom_form_new_table" onCreate="migrateDataFromAnotherTable(custom_form)"> - <table name="custom_form" resource="default" engine="innodb" comment="creating custom form table"> <column xsi:type="smallint" name="entity_id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID"/> <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name"/> <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email"/> <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message"/> <constraint xsi:type="primary" referenceId="PRIMARY"> <column name="entity_id"/> </constraint> </table> </schema> |
onCreate=”migrateDataFromAnotherTable(custom_form)”
here csutom_form table is source table name.
Generate db_schema_whitelist.json file:
When renaming a table, remember to re generate the db_schema_whitelist.json file so it contains the new name in addition to the old one.
php bin/magento setup:db-declaration:generate-whitelist --module-name=MM_Db
Here MM_Db is my module name. Please replace it with your Module name.
Run upgrade command to create table:
php bin/magento setup:upgrade
Validate your DB for the new custom table, it should already be created in the DB with data migration.
Drop a module table:
To drop a module table just comment out table code from db_schema.xml file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?xml version="1.0"?> <!-- /** * Copyright © Magento, Inc. All rights reserved. * See COPYING.txt for license details. */ --> <schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd"> - <table name="custom_form_new_table" onCreate="migrateDataFromAnotherTable(custom_form)"> - <table name="custom_form" resource="default" engine="innodb" comment="creating custom form table"> - <column xsi:type="smallint" name="entity_id" padding="6" unsigned="false" nullable="false" identity="true" comment="ID"/> - <column xsi:type="varchar" name="name" nullable="false" length="25" comment="Name"/> - <column xsi:type="varchar" name="email" nullable="false" length="25" comment="Email"/> - <column xsi:type="varchar" name="message" nullable="false" length="255" comment="Message"/> - <constraint xsi:type="primary" referenceId="PRIMARY"> - <column name="entity_id"/> - </constraint> - </table> </schema> |
Run upgrade command to drop a table:
php bin/magento setup:upgrade
Validate your DB, it should already be deleted from the DB.