Repository: pingcap/dumpling
Pull request #30 (2020-03-01 01:59:24)

*: handle views properly and add no-views option
Changes:
- not to dump the data selected from views.
- add a config option `skipview`: only dump views when `skipview` is false.


codecov-io: # [Codecov](https://codecov.io/gh/pingcap/dumpling/pull/30?src=pr&el=h1) Report
> Merging [#30](https://codecov.io/gh/pingcap/dumpling/pull/30?src=pr&el=desc) into [master](https://codecov.io/gh/pingcap/dumpling/commit/1e277e64d356c8f4ce7c1740e972741cbee9d2c3?src=pr&el=desc) will **decrease** coverage by `1.94%`.
> The diff coverage is `61.11%`.


```diff
@@            Coverage Diff             @@
##           master      #30      +/-   ##
==========================================
- Coverage   77.45%   75.51%   -1.95%     
==========================================
  Files          13       13              
  Lines         732      776      +44     
==========================================
+ Hits          567      586      +19     
- Misses        117      140      +23     
- Partials       48       50       +2
```

siddontang: Maybe we can follow the style of `mydumper`, refer `mydumper --help`:

```
-W, --no-views              Do not dump VIEWs
``` 

Patch:
diff --git a/.gitignore b/.gitignore
index a1b4ebd..179915a 100644
--- a/.gitignore
+++ b/.gitignore
@@ -2,4 +2,4 @@
 bin/
 coverage.txt
 .idea
-var
\ No newline at end of file
+var
diff --git a/cmd/dumpling/main.go b/cmd/dumpling/main.go
index 6d2d74a..4bb50c0 100644
--- a/cmd/dumpling/main.go
+++ b/cmd/dumpling/main.go
@@ -38,6 +38,7 @@ var (
 	logLevel    string
 	consistency string
 	snapshot    string
+	noViews     bool
 
 	rootCmd = &cobra.Command{
 		Use:   "dumpling",
@@ -67,6 +68,7 @@ func init() {
 	rootCmd.PersistentFlags().StringVar(&logLevel, "loglevel", "info", "Log level: {debug|info|warn|error|dpanic|panic|fatal}")
 	rootCmd.PersistentFlags().StringVar(&consistency, "consistency", "auto", "Consistency level during dumping: {auto|none|flush|lock|snapshot}")
 	rootCmd.PersistentFlags().StringVar(&snapshot, "snapshot", "", "Snapshot position. Valid only when consistency=snapshot")
+	rootCmd.PersistentFlags().BoolVarP(&noViews, "no-views", "W", true, "Do not dump views")
 }
 
 func run() {
@@ -87,6 +89,7 @@ func run() {
 	conf.Threads = threads
 	conf.FileSize = fileSize
 	conf.OutputDirPath = outputDir
+	conf.NoViews = noViews
 
 	err = export.Dump(conf)
 	if err != nil {
diff --git a/v4/export/config.go b/v4/export/config.go
index 4a28bba..7d40ebf 100644
--- a/v4/export/config.go
+++ b/v4/export/config.go
@@ -25,6 +25,7 @@ type Config struct {
 	Tables        DatabaseTables
 	Snapshot      string
 	Consistency   string
+	NoViews       bool
 }
 
 func DefaultConfig() *Config {
@@ -42,6 +43,7 @@ func DefaultConfig() *Config {
 		Tables:        nil,
 		Snapshot:      "",
 		Consistency:   "auto",
+		NoViews:       true,
 	}
 }
 
@@ -128,9 +130,3 @@ func init() {
 	serverTypeString[ServerTypeMariaDB] = "MariaDB"
 	serverTypeString[ServerTypeTiDB] = "TiDB"
 }
-
-type databaseName = string
-
-type tableName = string
-
-type DatabaseTables map[databaseName][]tableName
diff --git a/v4/export/consistency.go b/v4/export/consistency.go
index 5318ce5..796b0fe 100644
--- a/v4/export/consistency.go
+++ b/v4/export/consistency.go
@@ -75,7 +75,7 @@ type ConsistencyLockDumpingTables struct {
 func (c *ConsistencyLockDumpingTables) Setup() error {
 	for dbName, tables := range c.allTables {
 		for _, table := range tables {
-			err := LockTables(c.db, dbName, table)
+			err := LockTables(c.db, dbName, table.Name)
 			if err != nil {
 				return err
 			}
diff --git a/v4/export/dump.go b/v4/export/dump.go
index 7d71cc2..a20d0d6 100644
--- a/v4/export/dump.go
+++ b/v4/export/dump.go
@@ -3,9 +3,9 @@ package export
 import (
 	"context"
 	"database/sql"
-	"golang.org/x/sync/errgroup"
 
 	_ "github.com/go-sql-driver/mysql"
+	"golang.org/x/sync/errgroup"
 )
 
 func Dump(conf *Config) (err error) {
@@ -29,6 +29,13 @@ func Dump(conf *Config) (err error) {
 	if err != nil {
 		return err
 	}
+	if !conf.NoViews {
+		views, err := listAllViews(pool, databases)
+		if err != nil {
+			return err
+		}
+		conf.Tables.Merge(views)
+	}
 
 	conCtrl, err := NewConsistencyController(conf, pool)
 	if err != nil {
@@ -78,16 +85,26 @@ func dumpDatabases(ctx context.Context, conf *Config, db *sql.DB, writer Writer)
 	return nil
 }
 
-func dumpTable(ctx context.Context, conf *Config, db *sql.DB, dbName, table string, writer Writer) error {
-	createTableSQL, err := ShowCreateTable(db, dbName, table)
+func dumpTable(ctx context.Context, conf *Config, db *sql.DB, dbName string, table *TableInfo, writer Writer) error {
+	if table.Type == TableTypeView {
+		viewName := table.Name
+		createViewSQL, err := ShowCreateView(db, dbName, viewName)
+		if err != nil {
+			return err
+		}
+		return writer.WriteTableMeta(ctx, dbName, viewName, createViewSQL)
+	}
+
+	tableName := table.Name
+	createTableSQL, err := ShowCreateTable(db, dbName, tableName)
 	if err != nil {
 		return err
 	}
-	if err := writer.WriteTableMeta(ctx, dbName, table, createTableSQL); err != nil {
+	if err := writer.WriteTableMeta(ctx, dbName, tableName, createTableSQL); err != nil {
 		return err
 	}
 
-	tableIR, err := SelectAllFromTable(conf, db, dbName, table)
+	tableIR, err := SelectAllFromTable(conf, db, dbName, tableName)
 	if err != nil {
 		return err
 	}
diff --git a/v4/export/prepare.go b/v4/export/prepare.go
index 2349cc3..7e353b2 100644
--- a/v4/export/prepare.go
+++ b/v4/export/prepare.go
@@ -27,15 +27,68 @@ func listAllTables(db *sql.DB, databaseNames []string) (DatabaseTables, error) {
 	log.Zap().Debug("list all the tables")
 	dbTables := DatabaseTables{}
 	for _, dbName := range databaseNames {
-		err := UseDatabase(db, dbName)
+		tables, err := ListAllTables(db, dbName)
 		if err != nil {
 			return nil, err
 		}
-		tables, err := ShowTables(db)
+		dbTables = dbTables.AppendTables(dbName, tables...)
+	}
+	return dbTables, nil
+}
+
+func listAllViews(db *sql.DB, databaseNames []string) (DatabaseTables, error) {
+	log.Zap().Debug("list all the views")
+	dbTables := DatabaseTables{}
+	for _, dbName := range databaseNames {
+		views, err := ListAllViews(db, dbName)
 		if err != nil {
 			return nil, err
 		}
-		dbTables[dbName] = tables
+		dbTables = dbTables.AppendViews(dbName, views...)
 	}
 	return dbTables, nil
 }
+
+type databaseName = string
+
+type TableType int8
+
+const (
+	TableTypeBase TableType = iota
+	TableTypeView
+)
+
+type TableInfo struct {
+	Name string
+	Type TableType
+}
+
+func (t *TableInfo) Equals(other *TableInfo) bool {
+	return t.Name == other.Name && t.Type == other.Type
+}
+
+type DatabaseTables map[databaseName][]*TableInfo
+
+func NewDatabaseTables() DatabaseTables {
+	return DatabaseTables{}
+}
+
+func (d DatabaseTables) AppendTables(dbName string, tableNames ...string) DatabaseTables {
+	for _, t := range tableNames {
+		d[dbName] = append(d[dbName], &TableInfo{t, TableTypeBase})
+	}
+	return d
+}
+
+func (d DatabaseTables) AppendViews(dbName string, viewNames ...string) DatabaseTables {
+	for _, v := range viewNames {
+		d[dbName] = append(d[dbName], &TableInfo{v, TableTypeView})
+	}
+	return d
+}
+
+func (d DatabaseTables) Merge(other DatabaseTables) {
+	for name, infos := range other {
+		d[name] = append(d[name], infos...)
+	}
+}
diff --git a/v4/export/sql.go b/v4/export/sql.go
index 0ac9de5..e580850 100644
--- a/v4/export/sql.go
+++ b/v4/export/sql.go
@@ -49,6 +49,37 @@ func ShowCreateTable(db *sql.DB, database, table string) (string, error) {
 	return oneRow[1], nil
 }
 
+func ShowCreateView(db *sql.DB, database, view string) (string, error) {
+	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", database, view)
+	err := simpleQuery(db, query, handleOneRow)
+	if err != nil {
+		return "", err
+	}
+	return oneRow[1], nil
+}
+
+func ListAllTables(db *sql.DB, database string) ([]string, error) {
+	var tables oneStrColumnTable
+	const query = "SELECT table_name FROM information_schema.tables WHERE table_schema = '%s' and table_type = 'BASE TABLE'"
+	if err := simpleQuery(db, fmt.Sprintf(query, database), tables.handleOneRow); err != nil {
+		return nil, err
+	}
+	return tables.data, nil
+}
+
+func ListAllViews(db *sql.DB, database string) ([]string, error) {
+	var views oneStrColumnTable
+	const query = "SELECT table_name FROM information_schema.tables WHERE table_schema = '%s' and table_type = 'VIEW'"
+	if err := simpleQuery(db, fmt.Sprintf(query, database), views.handleOneRow); err != nil {
+		return nil, err
+	}
+	return views.data, nil
+}
+
 func SelectVersion(db *sql.DB) (string, error) {
 	var versionInfo string
 	handleOneRow := func(rows *sql.Rows) error {


Test patch:
diff --git a/tests/_utils/file_not_exist b/tests/_utils/file_not_exist
new file mode 100755
index 0000000..7055030
--- /dev/null
+++ b/tests/_utils/file_not_exist
@@ -0,0 +1,7 @@
+#!/bin/sh
+
+set -eu
+
+if [ -f "$1" ]; then
+  echo "[$(date)] File $1 already exists." && exit 1
+fi
diff --git a/tests/run.sh b/tests/run.sh
index 614886b..db6b30a 100755
--- a/tests/run.sh
+++ b/tests/run.sh
@@ -1,5 +1,7 @@
 #!/bin/sh
 
+# To avoid permission denied error, please run `chmod +x tests/_utils/*`.
+
 set -e
 
 DUMPLING_TEST_DIR=${DUMPLING_TEST_DIR:-"/tmp/dumpling_test_result"}
@@ -41,6 +43,9 @@ for script in tests/*/run.sh; do
     DUMPLING_OUTPUT_DIR="$DUMPLING_TEST_DIR"/sql_res."$TEST_NAME"
     export DUMPLING_OUTPUT_DIR
 
+    echo "Cleaning up test output dir: $DUMPLING_OUTPUT_DIR"
+    rm "$DUMPLING_OUTPUT_DIR" -rf
+
     PATH="tests/_utils:$PATH" \
     sh "$script"
 done
diff --git a/tests/views/data/views-schema-create.sql b/tests/views/data/views-schema-create.sql
new file mode 100644
index 0000000..8ae8de9
--- /dev/null
+++ b/tests/views/data/views-schema-create.sql
@@ -0,0 +1,1 @@
+CREATE DATABASE `views` /*!40100 DEFAULT CHARACTER SET latin1 */;
diff --git a/tests/views/data/views.v-schema.sql b/tests/views/data/views.v-schema.sql
new file mode 100644
index 0000000..83f4b25
--- /dev/null
+++ b/tests/views/data/views.v-schema.sql
@@ -0,0 +1,1 @@
+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`;
diff --git a/tests/views/run.sh b/tests/views/run.sh
new file mode 100644
index 0000000..88172b5
--- /dev/null
+++ b/tests/views/run.sh
@@ -0,0 +1,22 @@
+#!/bin/sh
+
+set -eu
+
+run_sql "drop database if exists views"
+run_sql "create database views"
+export DUMPLING_TEST_DATABASE="views"
+
+run_sql "create table t (a bigint, b varchar(255))"
+run_sql "create definer = 'root'@'localhost' view v as select * from t;"
+# insert 20 records to `t`.
+i=0; while [ $i -lt 20 ]; do
+  run_sql "insert into t values ($i, \"$i\")"
+  i=$(( i + 1 ))
+done
+
+run_dumpling --no-views
+file_not_exist "$DUMPLING_OUTPUT_DIR/views.v-schema.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 --git a/v4/export/consistency_test.go b/v4/export/consistency_test.go
index 47c62d2..56eae8f 100644
--- a/v4/export/consistency_test.go
+++ b/v4/export/consistency_test.go
@@ -64,10 +64,9 @@ func (s *testConsistencySuite) TestConsistencyController(c *C) {
 	}
 
 	conf.Consistency = "lock"
-	conf.Tables = map[databaseName][]tableName{
-		"db1": {"t1", "t2", "t3"},
-		"db2": {"t4"},
-	}
+	conf.Tables = NewDatabaseTables().
+		AppendTables("db1", "t1", "t2", "t3").
+		AppendViews("db2", "t4")
 	for i := 0; i < 4; i++ {
 		mock.ExpectExec("LOCK TABLES").WillReturnResult(resultOk)
 	}
@@ -129,7 +128,7 @@ func (s *testConsistencySuite) TestConsistencyControllerError(c *C) {
 
 	// lock table fail
 	conf.Consistency = "lock"
-	conf.Tables = map[databaseName][]tableName{"db": {"t"}}
+	conf.Tables = NewDatabaseTables().AppendTables("db", "t")
 	mock.ExpectExec("LOCK TABLE").WillReturnError(errors.New(""))
 	ctrl, _ = NewConsistencyController(conf, db)
 	err = ctrl.Setup()
diff --git a/v4/export/dump_test.go b/v4/export/dump_test.go
index 2a572eb..b9e4739 100644
--- a/v4/export/dump_test.go
+++ b/v4/export/dump_test.go
@@ -45,7 +45,7 @@ func (s *testDumpSuite) TestDumpDatabase(c *C) {
 	mockConfig := DefaultConfig()
 	mockConfig.SortByPk = false
 	mockConfig.Database = "test"
-	mockConfig.Tables = map[string][]tableName{"test": {"t"}}
+	mockConfig.Tables = NewDatabaseTables().AppendTables("test", "t")
 	db, mock, err := sqlmock.New()
 	c.Assert(err, IsNil)
 
@@ -85,7 +85,7 @@ func (s *testDumpSuite) TestDumpTable(c *C) {
 	mock.ExpectQuery("SELECT (.) FROM test.t").WillReturnRows(rows)
 
 	mockWriter := newMockWriter()
-	err = dumpTable(context.Background(), mockConfig, db, "test", "t", mockWriter)
+	err = dumpTable(context.Background(), mockConfig, db, "test", &TableInfo{Name: "t"}, mockWriter)
 	c.Assert(err, IsNil)
 
 	c.Assert(mockWriter.tableMeta["test.t"], Equals, showCreateTableResult)
diff --git a/v4/export/prepare_test.go b/v4/export/prepare_test.go
index a8b510c..7dffae4 100644
--- a/v4/export/prepare_test.go
+++ b/v4/export/prepare_test.go
@@ -42,21 +42,24 @@ func (s *testPrepareSuite) TestListAllTables(c *C) {
 	c.Assert(err, IsNil)
 	defer db.Close()
 
-	resultOk := sqlmock.NewResult(0, 0)
-	data := map[databaseName][]tableName{
-		"db1": {"t1", "t2"},
-		"db2": {"t3", "t4", "t5"},
-	}
+	data := NewDatabaseTables().
+		AppendTables("db1", "t1", "t2").
+		AppendTables("db2", "t3", "t4", "t5").
+		AppendViews("db3", "t6", "t7", "t8")
 
 	var dbNames []databaseName
-	for dbName, tbNames := range data {
+	for dbName, tableInfos := range data {
 		dbNames = append(dbNames, dbName)
-		mock.ExpectExec("USE .").WillReturnResult(resultOk)
-		rows := sqlmock.NewRows([]string{"Tables_in_xx"})
-		for _, name := range tbNames {
-			rows.AddRow(name)
+
+		rows := sqlmock.NewRows([]string{"Table_name"})
+		for _, tbInfo := range tableInfos {
+			if tbInfo.Type == TableTypeView {
+				continue
+			}
+			rows.AddRow(tbInfo.Name)
 		}
-		mock.ExpectQuery("SHOW TABLES").WillReturnRows(rows)
+		query := "SELECT table_name FROM information_schema.tables WHERE table_schema = (.*) and table_type = (.*)"
+		mock.ExpectQuery(query).WillReturnRows(rows)
 	}
 
 	tables, err := listAllTables(db, dbNames)
@@ -65,7 +68,23 @@ func (s *testPrepareSuite) TestListAllTables(c *C) {
 	for d, t := range tables {
 		expectedTbs, ok := data[d]
 		c.Assert(ok, IsTrue)
-		c.Assert(t, DeepEquals, expectedTbs)
+		for i := 0; i < len(t); i++ {
+			cmt := Commentf("%v mismatch: %v", t[i], expectedTbs[i])
+			c.Assert(t[i].Equals(expectedTbs[i]), IsTrue, cmt)
+		}
 	}
+
+	// Test list all tables and not skipping views.
+	data = NewDatabaseTables().
+		AppendTables("db", "t1").
+		AppendViews("db", "t2")
+	query := "SELECT table_name FROM information_schema.tables WHERE table_schema = (.*) and table_type = (.*)"
+	mock.ExpectQuery(query).WillReturnRows(sqlmock.NewRows([]string{"Table_name"}).AddRow("t2"))
+	tables, err = listAllViews(db, []string{"db"})
+	c.Assert(err, IsNil)
+	c.Assert(len(tables), Equals, 1)
+	c.Assert(len(tables["db"]), Equals, 1)
+	c.Assert(tables["db"][0].Equals(data["db"][1]), IsTrue, Commentf("%v mismatch %v", tables["db"][0], data["db"][1]))
+
 	c.Assert(mock.ExpectationsWereMet(), IsNil)
 }
diff --git a/v4/export/sql_test.go b/v4/export/sql_test.go
index 922e302..5dcb45c 100644
--- a/v4/export/sql_test.go
+++ b/v4/export/sql_test.go
@@ -2,6 +2,7 @@ package export
 
 import (
 	"errors"
+
 	"github.com/DATA-DOG/go-sqlmock"
 	"github.com/coreos/go-semver/semver"
 	. "github.com/pingcap/check"
diff --git a/v4/export/writer_test.go b/v4/export/writer_test.go
index 6d32e48..5844474 100644
--- a/v4/export/writer_test.go
+++ b/v4/export/writer_test.go
@@ -3,10 +3,11 @@ package export
 import (
 	"context"
 	"database/sql/driver"
-	. "github.com/pingcap/check"
 	"io/ioutil"
 	"os"
 	"path"
+
+	. "github.com/pingcap/check"
 )
 
 var _ = Suite(&testWriterSuite{})
@@ -126,15 +127,13 @@ func (s *testDumpSuite) TestWriteTableDataWithFileSize(c *C) {
 	err = writer.WriteTableData(ctx, tableIR)
 	c.Assert(err, IsNil)
 
-	cases := map[string]string {
-		"test.employee.0.sql":
-			"/*!40101 SET NAMES binary*/;\n" +
+	cases := map[string]string{
+		"test.employee.0.sql": "/*!40101 SET NAMES binary*/;\n" +
 			"/*!40014 SET FOREIGN_KEY_CHECKS=0*/;\n" +
 			"INSERT INTO `employee` VALUES\n" +
 			"(1, 'male', 'bob@mail.com', '020-1234', NULL),\n" +
 			"(2, 'female', 'sarah@mail.com', '020-1253', 'healthy');\n",
-		"test.employee.1.sql":
-			"/*!40101 SET NAMES binary*/;\n" +
+		"test.employee.1.sql": "/*!40101 SET NAMES binary*/;\n" +
 			"/*!40014 SET FOREIGN_KEY_CHECKS=0*/;\n" +
 			"INSERT INTO `employee` VALUES\n" +
 			"(3, 'male', 'john@mail.com', '020-1256', 'healthy'),\n" +
@@ -149,4 +148,4 @@ func (s *testDumpSuite) TestWriteTableDataWithFileSize(c *C) {
 		c.Assert(err, IsNil)
 		c.Assert(string(bytes), Equals, expected)
 	}
-}
\ No newline at end of file
+}