<commit_before>// Copyright 2020 Google LLC
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//      http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package mysql

import (
	"bufio"
	"fmt"
	"math/bits"
	"strings"
	"testing"
	"time"

	"cloud.google.com/go/spanner"
	"github.com/cloudspannerecosystem/harbourbridge/internal"
	"github.com/cloudspannerecosystem/harbourbridge/spanner/ddl"
	"github.com/stretchr/testify/assert"
)

func TestProcessMySQLDump_Scalar(t *testing.T) {
	// First, test scalar types.
	scalarTests := []struct {
		ty       string
		expected ddl.ScalarType
	}{
		{"bigint", ddl.Int64{}},
		{"bool", ddl.Bool{}},
		{"boolean", ddl.Bool{}},
		{"tinyint(1)", ddl.Bool{}},
		{"tinyint(4)", ddl.Int64{}},
		{"blob", ddl.Bytes{Len: ddl.MaxLength{}}},
		{"char(42)", ddl.String{Len: ddl.Int64Length{Value: 42}}},
		{"date", ddl.Date{}},
		{"decimal(4,10)", ddl.Float64{}},
		{"double(4,10)", ddl.Float64{}},
		{"float(4,10)", ddl.Float64{}},
		{"integer", ddl.Int64{}},
		{"mediumint", ddl.Int64{}},
		{"int", ddl.Int64{}},
		{"bit", ddl.Bytes{Len: ddl.MaxLength{}}},
		{"smallint", ddl.Int64{}},
		{"text", ddl.String{Len: ddl.MaxLength{}}},
		{"tinytext", ddl.String{Len: ddl.MaxLength{}}},
		{"mediumtext", ddl.String{Len: ddl.MaxLength{}}},
		{"longtext", ddl.String{Len: ddl.MaxLength{}}},
		{"enum('a','b')", ddl.String{Len: ddl.MaxLength{}}},
		{"timestamp", ddl.Timestamp{}},
		{"datetime", ddl.Timestamp{}},
		{"varchar(42)", ddl.String{Len: ddl.Int64Length{Value: 42}}},
	}
	for _, tc := range scalarTests {
		t.Run(tc.ty, func(t *testing.T) {
			conv, _ := runProcessMySQLDump(fmt.Sprintf("CREATE TABLE t (a %s);", tc.ty))
			noIssues(conv, t, "Scalar type: "+tc.ty)
			assert.Equal(t, conv.SpSchema["t"].ColDefs["a"].T, tc.expected, "Scalar type: "+tc.ty)
		})
	}
}

func TestProcessMySQLDump_SingleCol(t *testing.T) {
	// Test array types and not null.
	singleColTests := []struct {
		ty       string
		expected ddl.ColumnDef
	}{
		{"set('a','b','c')", ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, IsArray: true}},
		{"text NOT NULL", ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true}},
	}
	for _, tc := range singleColTests {
		t.Run(tc.ty, func(t *testing.T) {
			conv, _ := runProcessMySQLDump(fmt.Sprintf("CREATE TABLE t (a %s);", tc.ty))
			noIssues(conv, t, "Not null: "+tc.ty)
			cd := conv.SpSchema["t"].ColDefs["a"]
			cd.Comment = ""
			assert.Equal(t, tc.expected, cd, "Not null: "+tc.ty)
		})
	}
}

func TestProcessMySQLDump_MultiCol(t *testing.T) {
	// Next test more general cases: multi-column schemas and data conversion.
	multiColTests := []struct {
		name           string
		input          string
		expectedSchema map[string]ddl.CreateTable
		expectedData   []spannerData
		expectIssues   bool // True if we expect to encounter issues during conversion.
	}{
		{
			name: "Shopping cart",
			input: "CREATE TABLE cart (productid text, userid text, quantity bigint);\n" +
				"ALTER TABLE cart ADD CONSTRAINT cart_pkey PRIMARY KEY (productid, userid);\n",
			expectedSchema: map[string]ddl.CreateTable{
				"cart": ddl.CreateTable{
					Name:     "cart",
					ColNames: []string{"productid", "userid", "quantity"},
					ColDefs: map[string]ddl.ColumnDef{
						"productid": ddl.ColumnDef{Name: "productid", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"userid":    ddl.ColumnDef{Name: "userid", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"quantity":  ddl.ColumnDef{Name: "quantity", T: ddl.Int64{}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "productid"}, ddl.IndexKey{Col: "userid"}}}},
		},
		{
			name:  "Shopping cart with no primary key",
			input: "CREATE TABLE cart (productid text, userid text NOT NULL, quantity bigint);\n",
			expectedSchema: map[string]ddl.CreateTable{
				"cart": ddl.CreateTable{
					Name:     "cart",
					ColNames: []string{"productid", "userid", "quantity", "synth_id"},
					ColDefs: map[string]ddl.ColumnDef{
						"productid": ddl.ColumnDef{Name: "productid", T: ddl.String{Len: ddl.MaxLength{}}},
						"userid":    ddl.ColumnDef{Name: "userid", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"quantity":  ddl.ColumnDef{Name: "quantity", T: ddl.Int64{}},
						"synth_id":  ddl.ColumnDef{Name: "synth_id", T: ddl.Int64{}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "synth_id"}}}},
		},
		{
			name:  "Create table with single primary key",
			input: "CREATE TABLE test (a text PRIMARY KEY, b text);\n",
			expectedSchema: map[string]ddl.CreateTable{
				"test": ddl.CreateTable{
					Name:     "test",
					ColNames: []string{"a", "b"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}}}},
		},
		{
			name:  "Create table with multiple primary keys",
			input: "CREATE TABLE test (a text, b text, n bigint, PRIMARY KEY (a, b) );\n",
			expectedSchema: map[string]ddl.CreateTable{
				"test": ddl.CreateTable{
					Name:     "test",
					ColNames: []string{"a", "b", "n"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"n": ddl.ColumnDef{Name: "n", T: ddl.Int64{}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}, ddl.IndexKey{Col: "b"}}}},
		},
		{
			name:  "Create table with mysql schema",
			input: "CREATE TABLE myschema.test (a text PRIMARY KEY, b text);\n",
			expectedSchema: map[string]ddl.CreateTable{
				"myschema_test": ddl.CreateTable{
					Name:     "myschema_test",
					ColNames: []string{"a", "b"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}}}},
		},
		{
			name: "ALTER TABLE SET NOT NULL",
			input: "CREATE TABLE test (a text PRIMARY KEY, b text);\n" +
				"ALTER TABLE test MODIFY b text NOT NULL;\n",
			expectedSchema: map[string]ddl.CreateTable{
				"test": ddl.CreateTable{
					Name:     "test",
					ColNames: []string{"a", "b"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}}}},
		},
		{
			name: "Multiple statements on one line",
			input: "CREATE TABLE t1 (a text, b text); CREATE TABLE t2 (c text);\n" +
				"ALTER TABLE t1 ADD CONSTRAINT t1_pkey PRIMARY KEY (a);\n" +
				"ALTER TABLE t2 ADD CONSTRAINT t2_pkey PRIMARY KEY (c);",
			expectedSchema: map[string]ddl.CreateTable{
				"t1": ddl.CreateTable{
					Name:     "t1",
					ColNames: []string{"a", "b"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}}},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}}},
				"t2": ddl.CreateTable{
					Name:     "t2",
					ColNames: []string{"c"},<commit_msg>Add mysqldump test for functions, triggers and procedures. (#76)<commit_after>// Copyright 2020 Google LLC
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//      http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package mysql

import (
	"bufio"
	"fmt"
	"math/bits"
	"strings"
	"testing"
	"time"

	"cloud.google.com/go/spanner"
	"github.com/cloudspannerecosystem/harbourbridge/internal"
	"github.com/cloudspannerecosystem/harbourbridge/spanner/ddl"
	"github.com/stretchr/testify/assert"
)

func TestProcessMySQLDump_Scalar(t *testing.T) {
	// First, test scalar types.
	scalarTests := []struct {
		ty       string
		expected ddl.ScalarType
	}{
		{"bigint", ddl.Int64{}},
		{"bool", ddl.Bool{}},
		{"boolean", ddl.Bool{}},
		{"tinyint(1)", ddl.Bool{}},
		{"tinyint(4)", ddl.Int64{}},
		{"blob", ddl.Bytes{Len: ddl.MaxLength{}}},
		{"char(42)", ddl.String{Len: ddl.Int64Length{Value: 42}}},
		{"date", ddl.Date{}},
		{"decimal(4,10)", ddl.Float64{}},
		{"double(4,10)", ddl.Float64{}},
		{"float(4,10)", ddl.Float64{}},
		{"integer", ddl.Int64{}},
		{"mediumint", ddl.Int64{}},
		{"int", ddl.Int64{}},
		{"bit", ddl.Bytes{Len: ddl.MaxLength{}}},
		{"smallint", ddl.Int64{}},
		{"text", ddl.String{Len: ddl.MaxLength{}}},
		{"tinytext", ddl.String{Len: ddl.MaxLength{}}},
		{"mediumtext", ddl.String{Len: ddl.MaxLength{}}},
		{"longtext", ddl.String{Len: ddl.MaxLength{}}},
		{"enum('a','b')", ddl.String{Len: ddl.MaxLength{}}},
		{"timestamp", ddl.Timestamp{}},
		{"datetime", ddl.Timestamp{}},
		{"varchar(42)", ddl.String{Len: ddl.Int64Length{Value: 42}}},
	}
	for _, tc := range scalarTests {
		t.Run(tc.ty, func(t *testing.T) {
			conv, _ := runProcessMySQLDump(fmt.Sprintf("CREATE TABLE t (a %s);", tc.ty))
			noIssues(conv, t, "Scalar type: "+tc.ty)
			assert.Equal(t, conv.SpSchema["t"].ColDefs["a"].T, tc.expected, "Scalar type: "+tc.ty)
		})
	}
}

func TestProcessMySQLDump_SingleCol(t *testing.T) {
	// Test array types and not null.
	singleColTests := []struct {
		ty       string
		expected ddl.ColumnDef
	}{
		{"set('a','b','c')", ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, IsArray: true}},
		{"text NOT NULL", ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true}},
	}
	for _, tc := range singleColTests {
		t.Run(tc.ty, func(t *testing.T) {
			conv, _ := runProcessMySQLDump(fmt.Sprintf("CREATE TABLE t (a %s);", tc.ty))
			noIssues(conv, t, "Not null: "+tc.ty)
			cd := conv.SpSchema["t"].ColDefs["a"]
			cd.Comment = ""
			assert.Equal(t, tc.expected, cd, "Not null: "+tc.ty)
		})
	}
}

func TestProcessMySQLDump_MultiCol(t *testing.T) {
	// Next test more general cases: multi-column schemas and data conversion.
	multiColTests := []struct {
		name           string
		input          string
		expectedSchema map[string]ddl.CreateTable
		expectedData   []spannerData
		expectIssues   bool // True if we expect to encounter issues during conversion.
	}{
		{
			name: "Shopping cart",
			input: "CREATE TABLE cart (productid text, userid text, quantity bigint);\n" +
				"ALTER TABLE cart ADD CONSTRAINT cart_pkey PRIMARY KEY (productid, userid);\n",
			expectedSchema: map[string]ddl.CreateTable{
				"cart": ddl.CreateTable{
					Name:     "cart",
					ColNames: []string{"productid", "userid", "quantity"},
					ColDefs: map[string]ddl.ColumnDef{
						"productid": ddl.ColumnDef{Name: "productid", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"userid":    ddl.ColumnDef{Name: "userid", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"quantity":  ddl.ColumnDef{Name: "quantity", T: ddl.Int64{}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "productid"}, ddl.IndexKey{Col: "userid"}}}},
		},
		{
			name:  "Shopping cart with no primary key",
			input: "CREATE TABLE cart (productid text, userid text NOT NULL, quantity bigint);\n",
			expectedSchema: map[string]ddl.CreateTable{
				"cart": ddl.CreateTable{
					Name:     "cart",
					ColNames: []string{"productid", "userid", "quantity", "synth_id"},
					ColDefs: map[string]ddl.ColumnDef{
						"productid": ddl.ColumnDef{Name: "productid", T: ddl.String{Len: ddl.MaxLength{}}},
						"userid":    ddl.ColumnDef{Name: "userid", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"quantity":  ddl.ColumnDef{Name: "quantity", T: ddl.Int64{}},
						"synth_id":  ddl.ColumnDef{Name: "synth_id", T: ddl.Int64{}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "synth_id"}}}},
		},
		{
			name:  "Create table with single primary key",
			input: "CREATE TABLE test (a text PRIMARY KEY, b text);\n",
			expectedSchema: map[string]ddl.CreateTable{
				"test": ddl.CreateTable{
					Name:     "test",
					ColNames: []string{"a", "b"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}}}},
		},
		{
			name:  "Create table with multiple primary keys",
			input: "CREATE TABLE test (a text, b text, n bigint, PRIMARY KEY (a, b) );\n",
			expectedSchema: map[string]ddl.CreateTable{
				"test": ddl.CreateTable{
					Name:     "test",
					ColNames: []string{"a", "b", "n"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"n": ddl.ColumnDef{Name: "n", T: ddl.Int64{}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}, ddl.IndexKey{Col: "b"}}}},
		},
		{
			name:  "Create table with mysql schema",
			input: "CREATE TABLE myschema.test (a text PRIMARY KEY, b text);\n",
			expectedSchema: map[string]ddl.CreateTable{
				"myschema_test": ddl.CreateTable{
					Name:     "myschema_test",
					ColNames: []string{"a", "b"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}}}},
		},
		{
			name: "Create table with function, trigger and procedure",
			input: `
DELIMITER ;;
CREATE PROCEDURE test_procedure( x INT )
    DETERMINISTIC
BEGIN
  SELECT concat(x, ' is a nice number');
END ;;
DELIMITER ;

DELIMITER ;;
CREATE FUNCTION test_function( x INT ) RETURNS int(11)
    DETERMINISTIC
BEGIN
  RETURN x + 42;
END ;;
DELIMITER ;

DELIMITER ;;
/*!50003 CREATE TRIGGER test_trigger BEFORE INSERT ON MyTable FOR EACH ROW If NEW.id < 0 THEN SET NEW.id = -NEW.id; END IF */;;
DELIMITER ;

CREATE TABLE test (a text PRIMARY KEY, b text);`,
			expectedSchema: map[string]ddl.CreateTable{
				"test": ddl.CreateTable{
					Name:     "test",
					ColNames: []string{"a", "b"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}}}},
			expectIssues: true, // conv.Stats.Reparsed != 0
		},
		{
			name: "ALTER TABLE SET NOT NULL",
			input: "CREATE TABLE test (a text PRIMARY KEY, b text);\n" +
				"ALTER TABLE test MODIFY b text NOT NULL;\n",
			expectedSchema: map[string]ddl.CreateTable{
				"test": ddl.CreateTable{
					Name:     "test",
					ColNames: []string{"a", "b"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
					},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}}}},
		},
		{
			name: "Multiple statements on one line",
			input: "CREATE TABLE t1 (a text, b text); CREATE TABLE t2 (c text);\n" +
				"ALTER TABLE t1 ADD CONSTRAINT t1_pkey PRIMARY KEY (a);\n" +
				"ALTER TABLE t2 ADD CONSTRAINT t2_pkey PRIMARY KEY (c);",
			expectedSchema: map[string]ddl.CreateTable{
				"t1": ddl.CreateTable{
					Name:     "t1",
					ColNames: []string{"a", "b"},
					ColDefs: map[string]ddl.ColumnDef{
						"a": ddl.ColumnDef{Name: "a", T: ddl.String{Len: ddl.MaxLength{}}, NotNull: true},
						"b": ddl.ColumnDef{Name: "b", T: ddl.String{Len: ddl.MaxLength{}}}},
					Pks: []ddl.IndexKey{ddl.IndexKey{Col: "a"}}},
				"t2": ddl.CreateTable{
					Name:     "t2",
					ColNames: []string{"c"},
{'fraction_non_alphanumeric': 0.18735769922109047, 'fraction_numerical': 0.008807669263031755, 'mean_word_length': 3.7620542082738946, 'pattern_counts': {'":': 55, '<': 4, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3675440', 'n_tokens_mistral': 7199, 'n_tokens_neox': 6251, 'n_words': 1474}