Repository: pingcap/dumpling
Pull request #158 (2020-09-29 10:11:18)

make dumpling support dumping view
<!--
Thank you for contributing to Dumpling! Please read the [CONTRIBUTING](https://github.com/pingcap/dumpling/blob/master/CONTRIBUTING.md) document **BEFORE** filing this PR.
-->

### What problem does this PR solve? <!--add issue link with summary if exists-->
close https://github.com/pingcap/dumpling/issues/93

### What is changed and how it works?
support dumping views like `mydumper`.

### Check List <!--REMOVE the items that are not applicable-->

Tests <!-- At least one of them must be included. -->

 - Unit test

Related changes

 - Need to cherry-pick to the release branch
 
### Release note

<!-- bugfixes or new feature need a release note, must in the form of a list, such as

- support -T/--tables-list argument

or if no need to be included in the release note, just add the following line

- No release note
-->
- No release note


Patch:
diff --git a/go.mod b/go.mod
index 0dc6991..dd4d0d4 100644
--- a/go.mod
+++ b/go.mod
@@ -7,8 +7,6 @@ require (
 	github.com/coreos/go-semver v0.3.0
 	github.com/docker/go-units v0.4.0
 	github.com/go-sql-driver/mysql v1.5.0
-	github.com/grpc-ecosystem/grpc-gateway v1.14.3 // indirect
-	github.com/json-iterator/go v1.1.9 // indirect
 	github.com/pingcap/br v0.0.0-20200925095602-bf9cc603382e
 	github.com/pingcap/check v0.0.0-20200212061837-5e12011dc712
 	github.com/pingcap/errors v0.11.5-0.20200902104258-eba4f1d8f6de
@@ -25,9 +23,5 @@ require (
 	golang.org/x/net v0.0.0-20200822124328-c89045814202 // indirect
 	golang.org/x/sync v0.0.0-20200625203802-6e8e738ad208
 	golang.org/x/sys v0.0.0-20200824131525-c12d262b63d8 // indirect
-	golang.org/x/text v0.3.3 // indirect
 	golang.org/x/tools v0.0.0-20200823205832-c024452afbcd // indirect
-	golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1 // indirect
-	gopkg.in/check.v1 v1.0.0-20190902080502-41f04d3bba15 // indirect
-	gopkg.in/yaml.v2 v2.3.0 // indirect
 )
diff --git a/v4/export/dump.go b/v4/export/dump.go
index 2490152..c699a2d 100755
--- a/v4/export/dump.go
+++ b/v4/export/dump.go
@@ -303,11 +303,11 @@ func dumpTable(ctx context.Context, conf *Config, db *sql.Conn, dbName string, t
 	if !conf.NoSchemas {
 		if table.Type == TableTypeView {
 			viewName := table.Name
-			createViewSQL, err := ShowCreateView(db, dbName, viewName)
+			createTableSQL, createViewSQL, err := ShowCreateView(db, dbName, viewName)
 			if err != nil {
 				return nil, err
 			}
-			return nil, writer.WriteTableMeta(ctx, dbName, viewName, createViewSQL)
+			return nil, writer.WriteViewMeta(ctx, dbName, viewName, createTableSQL, createViewSQL)
 		}
 		createTableSQL, err := ShowCreateTable(db, dbName, tableName)
 		if err != nil {
diff --git a/v4/export/ir_impl.go b/v4/export/ir_impl.go
index 377edfa..9dbf3a9 100644
--- a/v4/export/ir_impl.go
+++ b/v4/export/ir_impl.go
@@ -5,6 +5,7 @@ import (
 	"database/sql"
 	"fmt"
 	"strconv"
+	"strings"
 
 	"github.com/pkg/errors"
 	"go.uber.org/zap"
@@ -285,5 +286,8 @@ func (m *metaData) TargetName() string {
 }
 
 func (m *metaData) MetaSQL() string {
+	if !strings.HasSuffix(m.metaSQL, ";\n") {
+		m.metaSQL += ";\n"
+	}
 	return m.metaSQL
 }
diff --git a/v4/export/prepare.go b/v4/export/prepare.go
index f71976e..06c5e6b 100644
--- a/v4/export/prepare.go
+++ b/v4/export/prepare.go
@@ -17,6 +17,7 @@ import (
 const (
 	outputFileTemplateSchema = "schema"
 	outputFileTemplateTable  = "table"
+	outputFileTemplateView   = "view"
 	outputFileTemplateData   = "data"
 
 	defaultOutputFileTemplateBase = `
diff --git a/v4/export/sql.go b/v4/export/sql.go
index 958f557..6c71a94 100644
--- a/v4/export/sql.go
+++ b/v4/export/sql.go
@@ -57,17 +57,72 @@ func ShowCreateTable(db *sql.Conn, database, table string) (string, error) {
 	return oneRow[1], nil
 }
 
-func ShowCreateView(db *sql.Conn, database, view string) (string, error) {
+func ShowCreateView(db *sql.Conn, database, view string) (string, string, error) {
+	var fieldNames []string
+	handleFieldRow := func(rows *sql.Rows) error {
+		var oneRow [6]sql.NullString
+		err := rows.Scan(&oneRow[0], &oneRow[1], &oneRow[2], &oneRow[3], &oneRow[4], &oneRow[5])
+		if err != nil {
+			return err
+		}
+		if oneRow[0].Valid {
+			fieldNames = append(fieldNames, fmt.Sprintf("`%s` int", escapeString(oneRow[0].String)))
+		}
+		return nil
+	}
 	var oneRow [4]string
 	handleOneRow := func(rows *sql.Rows) error {
 		return rows.Scan(&oneRow[0], &oneRow[1], &oneRow[2], &oneRow[3])
 	}
-	query := fmt.Sprintf("SHOW CREATE TABLE `%s`.`%s`", escapeString(database), escapeString(view))
-	err := simpleQuery(db, query, handleOneRow)
+	var createTableSQL, createViewSQL strings.Builder
+
+	// Build createTableSQL
+	query := fmt.Sprintf("SHOW FIELDS FROM `%s`.`%s`", escapeString(database), escapeString(view))
+	err := simpleQuery(db, query, handleFieldRow)
 	if err != nil {
-		return "", errors.WithMessage(err, query)
+		return "", "", errors.WithMessage(err, query)
 	}
-	return oneRow[1], nil
+	fmt.Fprintf(&createTableSQL, "CREATE TABLE `%s`(\n", escapeString(view))
+	createTableSQL.WriteString(strings.Join(fieldNames, ",\n"))
+	createTableSQL.WriteString("\n)ENGINE=MyISAM;\n")
+
+	// Build createViewSQL
+	fmt.Fprintf(&createViewSQL, "DROP TABLE IF EXISTS `%s`;\n", escapeString(view))
+	fmt.Fprintf(&createViewSQL, "DROP VIEW IF EXISTS `%s`;\n", escapeString(view))
+	query = fmt.Sprintf("SHOW CREATE VIEW `%s`.`%s`", escapeString(database), escapeString(view))
+	err = simpleQuery(db, query, handleOneRow)
+	if err != nil {
+		return "", "", errors.WithMessage(err, query)
+	}
+	// The result for `show create view` SQL
+	// mysql> show create view v1;
+	// +------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
+	// | View | Create View                                                                                                                         | character_set_client | collation_connection |
+	// +------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
+	// | v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` (`a`) AS SELECT `t`.`a` AS `a` FROM `test`.`t` | utf8                 | utf8_general_ci      |
+	// +------+-------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
+	SetCharset(&createViewSQL, oneRow[2], oneRow[3])
+	createViewSQL.WriteString(oneRow[1])
+	createViewSQL.WriteString(";\n")
+	RestoreCharset(&createViewSQL)
+
+	return createTableSQL.String(), createViewSQL.String(), nil
+}
+
+func SetCharset(w *strings.Builder, characterSet, collationConnection string) {
+	w.WriteString("SET @PREV_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;\n")
+	w.WriteString("SET @PREV_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;\n")
+	w.WriteString("SET @PREV_COLLATION_CONNECTION=@@COLLATION_CONNECTION;\n")
+
+	fmt.Fprintf(w, "SET character_set_client = %s;\n", characterSet)
+	fmt.Fprintf(w, "SET character_set_results = %s;\n", characterSet)
+	fmt.Fprintf(w, "SET collation_connection = %s;\n", collationConnection)
+}
+
+func RestoreCharset(w *strings.Builder) {
+	w.WriteString("SET character_set_client = @PREV_CHARACTER_SET_CLIENT;\n")
+	w.WriteString("SET character_set_results = @PREV_CHARACTER_SET_RESULTS;\n")
+	w.WriteString("SET collation_connection = @PREV_COLLATION_CONNECTION;\n")
 }
 
 func ListAllDatabasesTables(db *sql.Conn, databaseNames []string, tableType TableType) (DatabaseTables, error) {
@@ -515,6 +570,7 @@ func simpleQueryWithArgs(conn *sql.Conn, handleOneRow func(*sql.Rows) error, sql
 	if err != nil {
 		return withStack(errors.WithMessage(err, sql))
 	}
+	defer rows.Close()
 
 	for rows.Next() {
 		if err := handleOneRow(rows); err != nil {
@@ -522,6 +578,7 @@ func simpleQueryWithArgs(conn *sql.Conn, handleOneRow func(*sql.Rows) error, sql
 			return withStack(errors.WithMessage(err, sql))
 		}
 	}
+	rows.Close()
 	return rows.Err()
 }
 
diff --git a/v4/export/writer.go b/v4/export/writer.go
index 1ac5b77..7209cac 100644
--- a/v4/export/writer.go
+++ b/v4/export/writer.go
@@ -14,6 +14,7 @@ import (
 type Writer interface {
 	WriteDatabaseMeta(ctx context.Context, db, createSQL string) error
 	WriteTableMeta(ctx context.Context, db, table, createSQL string) error
+	WriteViewMeta(ctx context.Context, db, table, createTableSQL, createViewSQL string) error
 	WriteTableData(ctx context.Context, ir TableDataIR) error
 }
 
@@ -42,6 +43,22 @@ func (f SimpleWriter) WriteTableMeta(ctx context.Context, db, table, createSQL s
 	return writeMetaToFile(ctx, db, createSQL, f.cfg.ExternalStorage, fileName+".sql")
 }
 
+func (f SimpleWriter) WriteViewMeta(ctx context.Context, db, view, createTableSQL, createViewSQL string) error {
+	fileNameTable, err := (&outputFileNamer{DB: db, Table: view}).render(f.cfg.OutputFileTemplate, outputFileTemplateTable)
+	if err != nil {
+		return err
+	}
+	fileNameView, err := (&outputFileNamer{DB: db, Table: view}).render(f.cfg.OutputFileTemplate, outputFileTemplateView)
+	if err != nil {
+		return err
+	}
+	err = writeMetaToFile(ctx, db, createTableSQL, f.cfg.ExternalStorage, fileNameTable+".sql")
+	if err != nil {
+		return err
+	}
+	return writeMetaToFile(ctx, db, createViewSQL, f.cfg.ExternalStorage, fileNameView+".sql")
+}
+
 type SQLWriter struct{ SimpleWriter }
 
 func (f SQLWriter) WriteTableData(ctx context.Context, ir TableDataIR) error {
@@ -100,6 +117,9 @@ func writeMetaToFile(ctx context.Context, target, metaSQL string, s storage.Exte
 	return WriteMeta(ctx, &metaData{
 		target:  target,
 		metaSQL: metaSQL,
+		specCmts: []string{
+			"/*!40101 SET NAMES binary*/;",
+		},
 	}, fileWriter)
 }
 
diff --git a/v4/export/writer_util.go b/v4/export/writer_util.go
index 03fe419..acfa3e1 100644
--- a/v4/export/writer_util.go
+++ b/v4/export/writer_util.go
@@ -110,7 +110,7 @@ func WriteMeta(ctx context.Context, meta MetaIR, w storage.Writer) error {
 		}
 	}
 
-	if err := write(ctx, w, fmt.Sprintf("%s;\n", meta.MetaSQL())); err != nil {
+	if err := write(ctx, w, meta.MetaSQL()); err != nil {
 		return err
 	}
 


Test patch:
diff --git a/tests/quote/data/quote-database-schema-create.sql b/tests/quote/data/quote-database-schema-create.sql
index 1a9f18b..a5df1c2 100755
--- a/tests/quote/data/quote-database-schema-create.sql
+++ b/tests/quote/data/quote-database-schema-create.sql
@@ -1,1 +1,2 @@
+/*!40101 SET NAMES binary*/;
 CREATE DATABASE `quo``te/database` /*!40100 DEFAULT CHARACTER SET latin1 */;
diff --git a/tests/quote/data/quote-database.quote-table-schema.sql b/tests/quote/data/quote-database.quote-table-schema.sql
index 49f37d4..b3c55de 100755
--- a/tests/quote/data/quote-database.quote-table-schema.sql
+++ b/tests/quote/data/quote-database.quote-table-schema.sql
@@ -1,3 +1,4 @@
+/*!40101 SET NAMES binary*/;
 CREATE TABLE `quo``te/table` (
   `quo``te/col` int(11) NOT NULL,
   `a` int(11) DEFAULT NULL,
diff --git a/tests/views/data/views-schema-create.sql b/tests/views/data/views-schema-create.sql
index 23ac4bc..af5ca6d 100644
--- a/tests/views/data/views-schema-create.sql
+++ b/tests/views/data/views-schema-create.sql
@@ -1,1 +1,2 @@
+/*!40101 SET NAMES binary*/;
 CREATE DATABASE `views` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */;
diff --git a/tests/views/data/views.v-schema-view.sql b/tests/views/data/views.v-schema-view.sql
new file mode 100644
index 0000000..06d73e7
--- /dev/null
+++ b/tests/views/data/views.v-schema-view.sql
@@ -0,0 +1,13 @@
+/*!40101 SET NAMES binary*/;
+DROP TABLE IF EXISTS `v`;
+DROP VIEW IF EXISTS `v`;
+SET @PREV_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
+SET @PREV_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
+SET @PREV_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
+SET character_set_client = utf8mb4;
+SET character_set_results = utf8mb4;
+SET collation_connection = utf8mb4_general_ci;
+CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `views`.`v` AS select `views`.`t`.`a` AS `a`,`views`.`t`.`b` AS `b` from `views`.`t`;
+SET character_set_client = @PREV_CHARACTER_SET_CLIENT;
+SET character_set_results = @PREV_CHARACTER_SET_RESULTS;
+SET collation_connection = @PREV_COLLATION_CONNECTION;
diff --git a/tests/views/data/views.v-schema.sql b/tests/views/data/views.v-schema.sql
index 83f4b25..8e6bf5d 100644
--- a/tests/views/data/views.v-schema.sql
+++ b/tests/views/data/views.v-schema.sql
@@ -1,1 +1,5 @@
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `views`.`v` AS select `views`.`t`.`a` AS `a`,`views`.`t`.`b` AS `b` from `views`.`t`;
+/*!40101 SET NAMES binary*/;
+CREATE TABLE `v`(
+`a` int,
+`b` int
+)ENGINE=MyISAM;
diff --git a/tests/views/run.sh b/tests/views/run.sh
index d1abf27..f47329c 100644
--- a/tests/views/run.sh
+++ b/tests/views/run.sh
@@ -13,7 +13,9 @@ run_sql "insert into t values $(seq -s, 20 | sed 's/,*$//g' | sed 's/[0-9]*/(\0,
 
 run_dumpling --no-views
 file_not_exist "$DUMPLING_OUTPUT_DIR/views.v-schema.sql"
+file_not_exist "$DUMPLING_OUTPUT_DIR/views.v-schema-view.sql"
 
 run_dumpling --no-views=false
 #diff "$DUMPLING_BASE_NAME/data/views-schema-create.sql" "$DUMPLING_OUTPUT_DIR/views-schema-create.sql"
 diff "$DUMPLING_BASE_NAME/data/views.v-schema.sql" "$DUMPLING_OUTPUT_DIR/views.v-schema.sql"
+diff "$DUMPLING_BASE_NAME/data/views.v-schema-view.sql" "$DUMPLING_OUTPUT_DIR/views.v-schema-view.sql"
\ No newline at end of file
diff --git a/v4/export/dump_test.go b/v4/export/dump_test.go
index 18055ea..c87492f 100644
--- a/v4/export/dump_test.go
+++ b/v4/export/dump_test.go
@@ -17,6 +17,7 @@ type testDumpSuite struct{}
 type mockWriter struct {
 	databaseMeta map[string]string
 	tableMeta    map[string]string
+	viewMeta     map[string]string
 	tableData    []TableDataIR
 }
 
@@ -24,6 +25,7 @@ func newMockWriter() *mockWriter {
 	return &mockWriter{
 		databaseMeta: map[string]string{},
 		tableMeta:    map[string]string{},
+		viewMeta:     map[string]string{},
 		tableData:    nil,
 	}
 }
@@ -46,6 +48,12 @@ func (m *mockWriter) WriteTableMeta(ctx context.Context, db, table, createSQL st
 	return nil
 }
 
+func (m *mockWriter) WriteViewMeta(ctx context.Context, db, table, createTableSQL, createViewSQL string) error {
+	m.tableMeta[fmt.Sprintf("%s.%s", db, table)] = createTableSQL
+	m.viewMeta[fmt.Sprintf("%s.%s", db, table)] = createViewSQL
+	return nil
+}
+
 func (m *mockWriter) WriteTableData(ctx context.Context, ir TableDataIR) error {
 	m.tableData = append(m.tableData, ir)
 	return nil
diff --git a/v4/export/sql_test.go b/v4/export/sql_test.go
index bd9b5b1..9185b18 100644
--- a/v4/export/sql_test.go
+++ b/v4/export/sql_test.go
@@ -203,7 +203,6 @@ func (s *testDumpSuite) TestBuildSelectField(c *C) {
 	c.Assert(selectedField, Equals, "`id`,`name`,`quo``te`")
 	c.Assert(err, IsNil)
 	c.Assert(mock.ExpectationsWereMet(), IsNil)
-
 }
 
 func (s *testDumpSuite) TestParseSnapshotToTSO(c *C) {
@@ -232,6 +231,28 @@ func (s *testDumpSuite) TestParseSnapshotToTSO(c *C) {
 	c.Assert(mock.ExpectationsWereMet(), IsNil)
 }
 
+func (s *testDumpSuite) TestShowCreateView(c *C) {
+	db, mock, err := sqlmock.New()
+	c.Assert(err, IsNil)
+	defer db.Close()
+	conn, err := db.Conn(context.Background())
+	c.Assert(err, IsNil)
+
+	mock.ExpectQuery("SHOW FIELDS FROM `test`.`v`").
+		WillReturnRows(sqlmock.NewRows([]string{"Field", "Type", "Null", "Key", "Default", "Extra"}).
+			AddRow("a", "int(11)", "YES", nil, "NULL", nil))
+
+	mock.ExpectQuery("SHOW CREATE VIEW `test`.`v`").
+		WillReturnRows(sqlmock.NewRows([]string{"View", "Create View", "character_set_client", "collation_connection"}).
+			AddRow("v", "CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` (`a`) AS SELECT `t`.`a` AS `a` FROM `test`.`t`", "utf8", "utf8_general_ci"))
+
+	createTableSQL, createViewSQL, err := ShowCreateView(conn, "test", "v")
+	c.Assert(err, IsNil)
+	c.Assert(createTableSQL, Equals, "CREATE TABLE `v`(\n`a` int\n)ENGINE=MyISAM;\n")
+	c.Assert(createViewSQL, Equals, "DROP TABLE IF EXISTS `v`;\nDROP VIEW IF EXISTS `v`;\nSET @PREV_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;\nSET @PREV_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;\nSET @PREV_COLLATION_CONNECTION=@@COLLATION_CONNECTION;\nSET character_set_client = utf8;\nSET character_set_results = utf8;\nSET collation_connection = utf8_general_ci;\nCREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` (`a`) AS SELECT `t`.`a` AS `a` FROM `test`.`t`;\nSET character_set_client = @PREV_CHARACTER_SET_CLIENT;\nSET character_set_results = @PREV_CHARACTER_SET_RESULTS;\nSET collation_connection = @PREV_COLLATION_CONNECTION;\n")
+	c.Assert(mock.ExpectationsWereMet(), IsNil)
+}
+
 func makeVersion(major, minor, patch int64, preRelease string) *semver.Version {
 	return &semver.Version{
 		Major:      major,
diff --git a/v4/export/writer_test.go b/v4/export/writer_test.go
index 859d50d..79fd9e1 100644
--- a/v4/export/writer_test.go
+++ b/v4/export/writer_test.go
@@ -15,15 +15,12 @@ var _ = Suite(&testWriterSuite{})
 type testWriterSuite struct{}
 
 func (s *testDumpSuite) TestWriteDatabaseMeta(c *C) {
-	dir, err := ioutil.TempDir("", "dumpling")
-	c.Assert(err, IsNil)
-	defer os.RemoveAll(dir)
-
+	dir := c.MkDir()
 	ctx := context.Background()
 
 	config := DefaultConfig()
 	config.OutputDirPath = dir
-	err = adjustConfig(ctx, config)
+	err := adjustConfig(ctx, config)
 	c.Assert(err, IsNil)
 
 	writer, err := NewSimpleWriter(config)
@@ -35,19 +32,17 @@ func (s *testDumpSuite) TestWriteDatabaseMeta(c *C) {
 	c.Assert(err, IsNil)
 	bytes, err := ioutil.ReadFile(p)
 	c.Assert(err, IsNil)
-	c.Assert(string(bytes), Equals, "CREATE DATABASE `test`;\n")
+	c.Assert(string(bytes), Equals, "/*!40101 SET NAMES binary*/;\nCREATE DATABASE `test`;\n")
 }
 
 func (s *testDumpSuite) TestWriteTableMeta(c *C) {
-	dir, err := ioutil.TempDir("", "dumpling")
-	c.Assert(err, IsNil)
-	defer os.RemoveAll(dir)
-
+	dir := c.MkDir()
 	ctx := context.Background()
 
 	config := DefaultConfig()
 	config.OutputDirPath = dir
-	err = adjustConfig(ctx, config)
+	err := adjustConfig(ctx, config)
+	c.Assert(err, IsNil)
 
 	writer, err := NewSimpleWriter(config)
 	c.Assert(err, IsNil)
@@ -58,19 +53,50 @@ func (s *testDumpSuite) TestWriteTableMeta(c *C) {
 	c.Assert(err, IsNil)
 	bytes, err := ioutil.ReadFile(p)
 	c.Assert(err, IsNil)
-	c.Assert(string(bytes), Equals, "CREATE TABLE t (a INT);\n")
+	c.Assert(string(bytes), Equals, "/*!40101 SET NAMES binary*/;\nCREATE TABLE t (a INT);\n")
 }
 
-func (s *testDumpSuite) TestWriteTableData(c *C) {
-	dir, err := ioutil.TempDir("", "dumpling")
+func (s *testDumpSuite) TestWriteViewMeta(c *C) {
+	dir := c.MkDir()
+	ctx := context.Background()
+
+	config := DefaultConfig()
+	config.OutputDirPath = dir
+	err := adjustConfig(ctx, config)
 	c.Assert(err, IsNil)
-	defer os.RemoveAll(dir)
+
+	writer, err := NewSimpleWriter(config)
+	c.Assert(err, IsNil)
+	specCmt := "/*!40101 SET NAMES binary*/;\n"
+	createTableSQL := "CREATE TABLE `v`(\n`a` int\n)ENGINE=MyISAM;\n"
+	createViewSQL := "DROP TABLE IF EXISTS `v`;\nDROP VIEW IF EXISTS `v`;\nSET @PREV_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;\nSET @PREV_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;\nSET @PREV_COLLATION_CONNECTION=@@COLLATION_CONNECTION;\nSET character_set_client = utf8;\nSET character_set_results = utf8;\nSET collation_connection = utf8_general_ci;\nCREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` (`a`) AS SELECT `t`.`a` AS `a` FROM `test`.`t`;\nSET character_set_client = @PREV_CHARACTER_SET_CLIENT;\nSET character_set_results = @PREV_CHARACTER_SET_RESULTS;\nSET collation_connection = @PREV_COLLATION_CONNECTION;\n"
+	err = writer.WriteViewMeta(ctx, "test", "v", createTableSQL, createViewSQL)
+	c.Assert(err, IsNil)
+
+	p := path.Join(dir, "test.v-schema.sql")
+	_, err = os.Stat(p)
+	c.Assert(err, IsNil)
+	bytes, err := ioutil.ReadFile(p)
+	c.Assert(err, IsNil)
+	c.Assert(string(bytes), Equals, specCmt+createTableSQL)
+
+	p = path.Join(dir, "test.v-schema-view.sql")
+	_, err = os.Stat(p)
+	c.Assert(err, IsNil)
+	bytes, err = ioutil.ReadFile(p)
+	c.Assert(err, IsNil)
+	c.Assert(string(bytes), Equals, specCmt+createViewSQL)
+}
+
+func (s *testDumpSuite) TestWriteTableData(c *C) {
+	dir := c.MkDir()
 
 	ctx := context.Background()
 
 	config := DefaultConfig()
 	config.OutputDirPath = dir
-	err = adjustConfig(ctx, config)
+	err := adjustConfig(ctx, config)
+	c.Assert(err, IsNil)
 
 	simpleWriter, err := NewSimpleWriter(config)
 	c.Assert(err, IsNil)
@@ -108,16 +134,15 @@ func (s *testDumpSuite) TestWriteTableData(c *C) {
 }
 
 func (s *testDumpSuite) TestWriteTableDataWithFileSize(c *C) {
-	dir, err := ioutil.TempDir("", "dumpling")
-	c.Assert(err, IsNil)
-	defer os.RemoveAll(dir)
+	dir := c.MkDir()
 
 	ctx := context.Background()
 
 	config := DefaultConfig()
 	config.OutputDirPath = dir
 	config.FileSize = 50
-	err = adjustConfig(ctx, config)
+	err := adjustConfig(ctx, config)
+	c.Assert(err, IsNil)
 	specCmts := []string{
 		"/*!40101 SET NAMES binary*/;",
 		"/*!40014 SET FOREIGN_KEY_CHECKS=0*/;",
@@ -165,8 +190,7 @@ func (s *testDumpSuite) TestWriteTableDataWithFileSize(c *C) {
 }
 
 func (s *testDumpSuite) TestWriteTableDataWithStatementSize(c *C) {
-	dir, err := ioutil.TempDir("", "dumpling")
-	c.Assert(err, IsNil)
+	dir := c.MkDir()
 
 	ctx := context.Background()
 
@@ -174,10 +198,11 @@ func (s *testDumpSuite) TestWriteTableDataWithStatementSize(c *C) {
 	config.OutputDirPath = dir
 	config.StatementSize = 50
 	config.StatementSize += uint64(len("INSERT INTO `employee` VALUES\n"))
+	var err error
 	config.OutputFileTemplate, err = ParseOutputFileTemplate("specified-name")
+	c.Assert(err, IsNil)
 	err = adjustConfig(ctx, config)
 	c.Assert(err, IsNil)
-	defer os.RemoveAll(config.OutputDirPath)
 
 	simpleWriter, err := NewSimpleWriter(config)
 	c.Assert(err, IsNil)
diff --git a/v4/export/writer_util_test.go b/v4/export/writer_util_test.go
index 24473a2..701b274 100644
--- a/v4/export/writer_util_test.go
+++ b/v4/export/writer_util_test.go
@@ -32,7 +32,7 @@ func (s *testUtilSuite) SetUpSuite(c *C) {
 func (s *testUtilSuite) TestWriteMeta(c *C) {
 	createTableStmt := "CREATE TABLE `t1` (\n" +
 		"  `a` int(11) DEFAULT NULL\n" +
-		") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci"
+		") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;\n"
 	specCmts := []string{"/*!40103 SET TIME_ZONE='+00:00' */;"}
 	meta := newMockMetaIR("t1", createTableStmt, specCmts)
 	writer := storage.NewBufferWriter()