Repository: pingcap/dumpling
Pull request #135 (2020-08-14 13:08:41)

support argument "--complete-insert"
<!--
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-->
resolve https://github.com/pingcap/dumpling/issues/134

### What is changed and how it works?
Add argument `--complete-insert` in dumpling.

### Check List <!--REMOVE the items that are not applicable-->

Tests <!-- At least one of them must be included. -->

 - Unit test
 - Integration test
 - Manual test (add detailed scripts or steps below)
 - No code

Related changes

 - Need to cherry-pick to the release branch
 - Need to update the documentation
 
### 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
-->


csuzhangxc: another question, does dumpling handle generated columns well?
lichunzhu: > another question, does dumpling handle generated columns well?
dumpling will use "complete-insert" mode if there are generated columns in the table.
codecov[bot]: # [Codecov](https://codecov.io/gh/pingcap/dumpling/pull/135?src=pr&el=h1) Report
> Merging [#135](https://codecov.io/gh/pingcap/dumpling/pull/135?src=pr&el=desc) into [master](https://codecov.io/gh/pingcap/dumpling/commit/45df63f5539ff4ee9f9e5a9e3764efa10df66cf3&el=desc) will **not change** coverage.
> The diff coverage is `75.00%`.


```diff
@@           Coverage Diff           @@
##           master     #135   +/-   ##
=======================================
  Coverage   50.28%   50.28%           
=======================================
  Files          17       17           
  Lines        1941     1941           
=======================================
  Hits          976      976           
  Misses        892      892           
  Partials       73       73           
```

csuzhangxc: @kennytm @overvenus cloud you take a look for this?
kennytm: one thing i'm afraid is that this overlaps with the `--no-header` flag with CSV (with opposite meaning) 🤔
lichunzhu: > one thing i'm afraid is that this overlaps with the `--no-header` flag in CSV (with opposite meaning) 🤔

Keep `complete-insert` for SQL and `no-header` for csv? 🤔

Patch:
diff --git a/cmd/dumpling/main.go b/cmd/dumpling/main.go
index fe93d77..0217777 100644
--- a/cmd/dumpling/main.go
+++ b/cmd/dumpling/main.go
@@ -67,6 +67,7 @@ var (
 	csvSeparator  string
 	csvDelimiter  string
 
+	completeInsert       bool
 	dumpEmptyDatabase    bool
 	escapeBackslash      bool
 	tidbMemQuotaQuery    uint64
@@ -122,6 +123,7 @@ func main() {
 	pflag.StringVar(&csvSeparator, "csv-separator", ",", "The separator for csv files, default ','")
 	pflag.StringVar(&csvDelimiter, "csv-delimiter", "\"", "The delimiter for values in csv files, default '\"'")
 	pflag.StringVar(&outputFilenameFormat, "output-filename-template", "", "The output filename template (without file extension), default '{{.DB}}.{{.Table}}.{{.Index}}'")
+	pflag.BoolVar(&completeInsert, "complete-insert", false, "Use complete INSERT statements that include column names")
 
 	printVersion := pflag.BoolP("version", "V", false, "Print Dumpling version")
 
@@ -208,6 +210,7 @@ func main() {
 	conf.CsvSeparator = csvSeparator
 	conf.CsvDelimiter = csvDelimiter
 	conf.OutputFileTemplate = tmpl
+	conf.CompleteInsert = completeInsert
 
 	err = export.Dump(context.Background(), conf)
 	if err != nil {
diff --git a/v4/export/config.go b/v4/export/config.go
index d1bf824..87f9d6e 100644
--- a/v4/export/config.go
+++ b/v4/export/config.go
@@ -54,6 +54,7 @@ type Config struct {
 	Rows               uint64
 	Where              string
 	FileType           string
+	CompleteInsert     bool
 	EscapeBackslash    bool
 	DumpEmptyDatabase  bool
 	OutputFileTemplate *template.Template
diff --git a/v4/export/ir_impl.go b/v4/export/ir_impl.go
index 5da9a54..9be6bc5 100644
--- a/v4/export/ir_impl.go
+++ b/v4/export/ir_impl.go
@@ -315,7 +315,7 @@ func splitTableDataIntoChunks(
 	estimatedStep := (max-min)/estimatedChunks + 1
 	cutoff := min
 
-	selectedField, err := buildSelectField(db, dbName, tableName)
+	selectedField, err := buildSelectField(db, dbName, tableName, conf.CompleteInsert)
 	if err != nil {
 		errCh <- withStack(err)
 		return
diff --git a/v4/export/sql.go b/v4/export/sql.go
index 6a9aebd..3fbc464 100644
--- a/v4/export/sql.go
+++ b/v4/export/sql.go
@@ -133,7 +133,7 @@ func SelectVersion(db *sql.DB) (string, error) {
 }
 
 func SelectAllFromTable(conf *Config, db *sql.Conn, database, table string) (TableDataIR, error) {
-	selectedField, err := buildSelectField(db, database, table)
+	selectedField, err := buildSelectField(db, database, table, conf.CompleteInsert)
 	if err != nil {
 		return nil, err
 	}
@@ -455,7 +455,7 @@ func createConnWithConsistency(ctx context.Context, db *sql.DB) (*sql.Conn, erro
 	return conn, nil
 }
 
-func buildSelectField(db *sql.Conn, dbName, tableName string) (string, error) {
+func buildSelectField(db *sql.Conn, dbName, tableName string, completeInsert bool) (string, error) {
 	query := `SELECT COLUMN_NAME,EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME=?;`
 	rows, err := db.QueryContext(context.Background(), query, dbName, tableName)
 	if err != nil {
@@ -479,7 +479,7 @@ func buildSelectField(db *sql.Conn, dbName, tableName string) (string, error) {
 		}
 		availableFields = append(availableFields, wrapBackTicks(escapeString(fieldName)))
 	}
-	if hasGenerateColumn {
+	if completeInsert || hasGenerateColumn {
 		return strings.Join(availableFields, ","), nil
 	}
 	return "*", nil


Test patch:
diff --git a/v4/export/sql_test.go b/v4/export/sql_test.go
index ee02e53..bd9b5b1 100644
--- a/v4/export/sql_test.go
+++ b/v4/export/sql_test.go
@@ -75,7 +75,7 @@ func (s *testDumpSuite) TestBuildSelectAllQuery(c *C) {
 		WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg()).
 		WillReturnRows(sqlmock.NewRows([]string{"column_name", "extra"}).AddRow("id", ""))
 
-	selectedField, err := buildSelectField(conn, "test", "t")
+	selectedField, err := buildSelectField(conn, "test", "t", false)
 	c.Assert(err, IsNil)
 	q := buildSelectQuery("test", "t", selectedField, "", orderByClause)
 	c.Assert(q, Equals, "SELECT * FROM `test`.`t` ORDER BY _tidb_rowid")
@@ -91,7 +91,7 @@ func (s *testDumpSuite) TestBuildSelectAllQuery(c *C) {
 		WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg()).
 		WillReturnRows(sqlmock.NewRows([]string{"column_name", "extra"}).AddRow("id", ""))
 
-	selectedField, err = buildSelectField(conn, "test", "t")
+	selectedField, err = buildSelectField(conn, "test", "t", false)
 	c.Assert(err, IsNil)
 	q = buildSelectQuery("test", "t", selectedField, "", orderByClause)
 	c.Assert(q, Equals, "SELECT * FROM `test`.`t`")
@@ -114,7 +114,7 @@ func (s *testDumpSuite) TestBuildSelectAllQuery(c *C) {
 			WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg()).
 			WillReturnRows(sqlmock.NewRows([]string{"column_name", "extra"}).AddRow("id", ""))
 
-		selectedField, err = buildSelectField(conn, "test", "t")
+		selectedField, err = buildSelectField(conn, "test", "t", false)
 		c.Assert(err, IsNil)
 		q = buildSelectQuery("test", "t", selectedField, "", orderByClause)
 		c.Assert(q, Equals, "SELECT * FROM `test`.`t` ORDER BY `id`", cmt)
@@ -138,7 +138,7 @@ func (s *testDumpSuite) TestBuildSelectAllQuery(c *C) {
 			WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg()).
 			WillReturnRows(sqlmock.NewRows([]string{"column_name", "extra"}).AddRow("id", ""))
 
-		selectedField, err = buildSelectField(conn, "test", "t")
+		selectedField, err = buildSelectField(conn, "test", "t", false)
 		c.Assert(err, IsNil)
 		q := buildSelectQuery("test", "t", selectedField, "", orderByClause)
 		c.Assert(q, Equals, "SELECT * FROM `test`.`t`", cmt)
@@ -157,7 +157,7 @@ func (s *testDumpSuite) TestBuildSelectAllQuery(c *C) {
 			WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg()).
 			WillReturnRows(sqlmock.NewRows([]string{"column_name", "extra"}).AddRow("id", ""))
 
-		selectedField, err := buildSelectField(conn, "test", "t")
+		selectedField, err := buildSelectField(conn, "test", "t", false)
 		c.Assert(err, IsNil)
 		q := buildSelectQuery("test", "t", selectedField, "", "")
 		c.Assert(q, Equals, "SELECT * FROM `test`.`t`", cmt)
@@ -177,18 +177,29 @@ func (s *testDumpSuite) TestBuildSelectField(c *C) {
 		WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg()).
 		WillReturnRows(sqlmock.NewRows([]string{"column_name", "extra"}).AddRow("id", ""))
 
-	selectedField, err := buildSelectField(conn, "test", "t")
+	selectedField, err := buildSelectField(conn, "test", "t", false)
 	c.Assert(selectedField, Equals, "*")
 	c.Assert(err, IsNil)
 	c.Assert(mock.ExpectationsWereMet(), IsNil)
 
+	// user assigns completeInsert
+	mock.ExpectQuery("SELECT COLUMN_NAME").
+		WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg()).
+		WillReturnRows(sqlmock.NewRows([]string{"column_name", "extra"}).AddRow("id", "").
+			AddRow("name", "").AddRow("quo`te", ""))
+
+	selectedField, err = buildSelectField(conn, "test", "t", true)
+	c.Assert(selectedField, Equals, "`id`,`name`,`quo``te`")
+	c.Assert(err, IsNil)
+	c.Assert(mock.ExpectationsWereMet(), IsNil)
+
 	// found generate columns, rest columns is `id`,`name`
 	mock.ExpectQuery("SELECT COLUMN_NAME").
 		WithArgs(sqlmock.AnyArg(), sqlmock.AnyArg()).
 		WillReturnRows(sqlmock.NewRows([]string{"column_name", "extra"}).
 			AddRow("id", "").AddRow("name", "").AddRow("quo`te", "").AddRow("generated", "VIRTUAL GENERATED"))
 
-	selectedField, err = buildSelectField(conn, "test", "t")
+	selectedField, err = buildSelectField(conn, "test", "t", false)
 	c.Assert(selectedField, Equals, "`id`,`name`,`quo``te`")
 	c.Assert(err, IsNil)
 	c.Assert(mock.ExpectationsWereMet(), IsNil)