Clean database and re-sync it with a couple of clicks

So, what is the boring part of Developing a Web app?

Testing!! yes! Build a test case and then for each case clean up the database and re-create one.

Doing this every time blows up anyone's mind. For some relief, MySQL workbench does the synchronization of database fast. But still dropping all the tables from phpMyAdmin MySQL database is a headache.

Can we do it with just a couple of clicks?

Yes sure! You just need to write a couple of batch programs and execute them in a sequence.

So, Let's do that!!

We should begin by dropping all the tables and views from the database.

• Open Notepad or Notepad++

• Then write a SQL query for getting the names of all the tables from the database and a Drop query sequence for all the tables with a semicolon (;), write the code shown below and save that file with .sql extension.

SET FOREIGN_KEY_CHECKS = 0;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
  FROM information_schema.tables 
  WHERE table_schema = 'DBNAME' and table_type = 'Base Table';
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;

• Write similar code for the views as shown below,

SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @views
  FROM information_schema.tables 
  WHERE table_schema = 'DBNAME' and table_type = 'View';
SET @tables = CONCAT('DROP VIEW IF EXISTS ', @views);
PREPARE stmt FROM @views;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• Now again create a new notepad for batch code, write the code below and do not forget to change the Path, Username and Database name and save it with a .bat extension.

@echo off
SET USERNAME=root
SET PASSWORD=
SET HOST=localhost
set  search=DBNAME
set  Database=erp_pcb
SET PATH=C:\wamp64\bin\mysql\mysql5.7.9\bin\

setlocal enabledelayedexpansion
set tablefile=drop_table.sql
set newtable=newdrop.sql
if exist "%newtable%" del /f /q "%newtable%"
for /f "tokens=*" %%a in (%tablefile%) do (
   set newline=%%a
   set newline=!newline:%search%=%Database%!
   echo !newline! >> %newtable%
)
endlocal
setlocal enabledelayedexpansion
@echo on
%PATH%mysql -h %HOST% -u %USERNAME% -B < newdrop.sql
del newdrop.sql
endlocal
pause
setlocal enabledelayedexpansion
set viewfile=drop_view.sql
set newview=newdrop.sql
if exist "%newview%" del /f /q "%newview%"
for /f "tokens=*" %%a in (%viewfile%) do (
   set line=%%a
   set line=!line:%search%=%Database%!
   echo !line! >> %newview%
)
endlocal
setlocal enabledelayedexpansion
@echo on
%PATH%mysql -h %HOST% -u %USERNAME% -B < newdrop.sql
del newdrop.sql
endlocal
pause

• That's all for Dropping table and view.

• And do not forget to keep all these files in one directory.

Now after dropping all tables and views, we need to recreate the database in MySQL, so for that, I am using MySQL workbench. To use MySQL Workbench to sync database, initially, you must deploy the structure of the database to the workbench. So, I suppose you have done that already.

• Open Notepad or Notepad++ again,

• Write the code below and save it as .bat extension

@echo off

SET WORKBENCH="D:\mysql-workbench-community-6.3.6-win32-noinstall\MySQL Workbench 6.3.6 CE (win32)\MySQLWorkbench.exe"
SET OUTPUT=D:\erp_pcb_app\webapp_view_build\dump.sql
SET USERNAME=root
SET HOST=localhost
SET PATH=C:\wamp64\bin\mysql\mysql5.7.9\bin\
set  search=erp_pcb
set  Database=erp_pcb
setlocal enabledelayedexpansion
set newview=D:\erp_pcb_app\webapp_view_build\newdump.sql
if exist "%OUTPUT%" del /f /q "%OUTPUT%"
%WORKBENCH% ^
  -open D:\erp_pcb_app\db\erp_pcb.mwb ^
  -run-python "import os;import grt;from grt.modules import DbMySQLFE as fe;c = grt.root.wb.doc.physicalModels[0].catalog;fe.generateSQLCreateStatements(c, c.version, {});fe.createScriptForCatalogObjects(os.getenv('OUTPUT'), c, {})" ^
 -quit-when-done
setlocal enabledelayedexpansion
if exist "%newview%" del /f /q "%newview%"
for /f "tokens=*" %%a in (%OUTPUT%) do (
   set newline=%%a
   set newline=!newline:%search%=%Database%!
   echo !newline! >> %newview%
)
endlocal
setlocal enabledelayedexpansion
@echo on
%PATH%mysql -h %HOST% -u %USERNAME% -B < newdump.sql
del dump.sql
del newdump.sql
pause

• And do not forget to change the path of Workbench, Output, and SQL.

This makes the testing part less boring as it will require only one-time code and several clicks.

Tip: Use this kind of script only on already backed up database or local/dev database.

---