Add mysql queries

[brianvoe]

Need to add the ability to generate mysql queries for mass imports

[petergeorgas]

I would love to take a crack at this as an opportunity to do more with SQL & Go.

What would be the primary use case of fake SQL queries?

[brianvoe]

So we seed our local databases for testing and to have data to be able to pass to the front end so front end developers have data they can use to visualize what the website looks like with actual data in it.

[petergeorgas]

So, for example, we would have a function named, say, InsertQuery that takes a struct that is to be populated with our fake data, as well as the table name, and then generates an insertion query to do so? Say the struct was this:

type PersonEntry struct {
	Fname string `fake:"{firstname}"`
	Lname string `fake:"{lastname}"`
	Age int `fake:"{number}"`
}

var p PersonEntry
gofakeit.Struct(&p)

and say the function call was faker.InsertQuery(&p, "PEOPLE") and we would get back:

INSERT INTO PEOPLE VALUES('<p.Fname>','<p.Lname>',<p.Age>);

Is this what you're looking for? Basically generating various queries based on structs populated with fake data?

[brianvoe]

So I would probably think about it in terms of batch inserts.

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

So just like the json function they would pass in a MysqlOptions and one of the fields would be an array of fields.

https://github.com/brianvoe/gofakeit/blob/master/json.go#L58

[petergeorgas]

I get it! That makes sense. I will give this a shot as soon as I have some free time this week.

[petergeorgas]

This is what I've got so far:

type SQLOptions struct {
	Table      string  `json:"table" xml:"table"`
	EntryCount int     `json:"entry_count" xml:"entry_count"`
	Fields     []Field `json:"fields" xml:"fields"`
}

func SQLInsert(so *SQLOptions) ([]byte, error) {
	return sqlInsertFunc(globalFaker.Rand, so)
}

func sqlInsertFunc(r *rand.Rand, so *SQLOptions) ([]byte, error) {
	if so.Table == "" {
		return nil, errors.New("must provide table name to generate SQL")
	}
	if so.Fields == nil || len(so.Fields) <= 0 {
		return nil, errors.New(("must pass fields in order to generate SQL queries"))
	}
	if so.EntryCount <= 0 {
		return nil, errors.New("must have entry count")
	}

	var sb strings.Builder
	sb.WriteString("INSERT INTO " + so.Table + " VALUES")

	for i := 0; i < so.EntryCount; i++ {
		sb.WriteString(" (")
		// Now, we need to add all of our fields
		for _, field := range so.Fields {
			if field.Function == "autoincrement" { // One
				// TODO: We need to do something here still...
				continue
			}

			// Get the function info for the field
			funcInfo := GetFuncLookup(field.Function)
			if funcInfo == nil {
				return nil, errors.New("invalid function, " + field.Function + " does not exist")
			}

			// Generate the value
			val, err := funcInfo.Generate(r, &field.Params, funcInfo)
			if err != nil {
				return nil, err
			}
			
			sb.
		}
	}

	return []byte(sb.String()), nil
}

What I am not understanding here is how we would do batch inserts when given an array of fields, to me this is looking like this would just end up doing one tuple, no?

EDIT: I totally get it now, I found the implementation for the array type in Json.go really helpful. I keep forgetting that each time we are going to randomly generate whatever is specified, not just once, because we are calling the function to generate each time we go around. My question now is how are we supposed to know the type of value we generate, since our value is just interface{}? Like, in SQL, if we want to insert a TEXT, we have to wrap the value with '. Could I just check to see if val.(type) is string and if so add in the necessary '? What about for everything else? I'm not sure the best way to do this, since SQL is typed like that.

[brianvoe]

Haha these are all great questions! Its also probably why I havent tackled this one yet. That being said as long as you can generate the value you can use the funcInfo to know what the output type is and have a conversion function that looks at the type and converts it to the type that sql supports. Thats where I would start and see how far you get and hit me up if you need anymore help.

[petergeorgas]

Is the ContentType property of Info what indicates the output type? Or is it Output?

EDIT: It seems to be Output.

[petergeorgas]

I'm thinking, I would say 95% of the use cases are putting either TEXT, FLOAT, INTEGER, DECIMAL, ETC., and DATE types into the DB.

Would something as simple as this suffice for now? I'm missing the DATE stuff right now, but I can probably put something together. Basically, we just add ' around the string types and convert the numerical types to a string and it should be good. At the end of the day, we're putting this all in one string to return so their types don't matter, most of SQL from what I'm recalling, besides strings and DATEs has nothing surrounding it.

func ConvertType(t string, val interface{}) string {
	switch t {
	case "string":
		return `'` + fmt.Sprintf("%v", t) + `'`
	default: 
		return fmt.Sprintf("%v", t)
	}
}

EDIT: Actually, you're already returning dates as strings, which I believe is what you do for SQL anyways to insert it into a table.

This is what I have so far:

type SQLOptions struct {
	Table      string  `json:"table" xml:"table"`
	EntryCount int     `json:"entry_count" xml:"entry_count"`
	Fields     []Field `json:"fields" xml:"fields"`
}

func SQLInsert(so *SQLOptions) ([]byte, error) {
	return sqlInsertFunc(globalFaker.Rand, so)
}

func sqlInsertFunc(r *rand.Rand, so *SQLOptions) ([]byte, error) {
	if so.Table == "" {
		return nil, errors.New("must provide table name to generate SQL")
	}
	if so.Fields == nil || len(so.Fields) <= 0 {
		return nil, errors.New(("must pass fields in order to generate SQL queries"))
	}
	if so.EntryCount <= 0 {
		return nil, errors.New("must have entry count")
	}

	var sb strings.Builder
	sb.WriteString("INSERT INTO " + so.Table + " VALUES")

	for i := 0; i < so.EntryCount; i++ {
		sb.WriteString(" (")
		// Now, we need to add all of our fields
		for i, field := range so.Fields {
			if field.Function == "autoincrement" { // One
				// TODO: We need to do something here still...
				continue
			}

			// Get the function info for the field
			funcInfo := GetFuncLookup(field.Function)
			if funcInfo == nil {
				return nil, errors.New("invalid function, " + field.Function + " does not exist")
			}

			// Generate the value
			val, err := funcInfo.Generate(r, &field.Params, funcInfo)
			if err != nil {
				return nil, err
			}

			convertType := ConvertType(funcInfo.Output, val)
			if i == len(so.Fields)-1 { // Last field
				sb.WriteString(convertType)
			} else {
				sb.WriteString(convertType + ", ")
			}
		}
		if i == so.EntryCount-1 { // Last tuple
			sb.WriteString(");")
		} else {
			sb.WriteString("),")
		}
	}

	return []byte(sb.String()), nil
}

func ConvertType(t string, val interface{}) string {
	switch t {
	case "string":
		return `'` + fmt.Sprintf("%v", t) + `'`
	default:
		return fmt.Sprintf("%v", t)
	}
}

[petergeorgas]

Here's what I have so far, what do you think?

sql.go
sql_test.go

[brianvoe]

I think those look great. One last thing you may want to do is throw more variations at it to make sure your covering all your bases. Things like floats. Also json can be a column type in mysql so you might want to try that as well.

If those look good and tests run ill get it merged in.

Nice job!

[petergeorgas]

Floats/Decimal values are handled by my ConvertType function! I will write a few tests to make sure.

Looking at the MySQL documentation , JSON is inserted into a table by just stringifying it, so I can just add the logic to do the same thing we do for the string data type as we do for json -- wrap the string in ' characters. I will also add a test for this to make sure.

EDIT: I see that JSON's "type"(Output) is "[]byte". Would it be safe to me to convert this to a string and wrap it in ' like this?

func ConvertType(t string, val interface{}) string {
	switch t {
	case "string":
		return `'` + fmt.Sprintf("%v", val) + `'`
	case "[]byte":
		return `'` + fmt.Sprintf("%s", val) + `'`
	default:
		return fmt.Sprintf("%v", val)
	}
}

[brianvoe]

Thats a good question. I think that should be the case but youll have to test it out.

[petergeorgas]

Float works great! I am having a bit of trouble understanding how I would specify I want to generate fake JSON data within a field for my SQLOptions.

res, _ := SQLInsert(&SQLOptions{
		Table:      "People",
		EntryCount: 3,
		Fields: []Field{
			{Name: "first_name", Function: "firstname"},
			{Name: "last_name", Function: "lastname"},
			{Name: "age", Function: "number", Params: MapParams{"min": {"1"}, "max": {"99"}}},
		},
	})

I know that my Function property will be "json", but what do I use for MapParams?

[brianvoe]

Ya it can get a little tricky putting together a sub json field. The cleanest way without having large amounts of param strings is to just create a simple lookup function with some fields in it and then in your sql test field just add the custom lookup function you added.

Here is an example of adding a custom function. https://github.com/brianvoe/gofakeit/blob/master/lookup_test.go#L11

Let me know if that makes sense to you.

We are really close. If you want to start a pr submission I can start looking at it more closely after you add the json test.

[petergeorgas]

I am having some trouble here, and I really can't seem to figure out what I'm doing incorrect. I did something nearly similar to your example, but I'm just getting random jumbled nonsense. This is my lookup function that I created, I'm working on integrating the two together so bear with me please:

func TestMultiSQLInsertJSON(t *testing.T) {

	Seed(12)

	AddFuncLookup("jsonperson", Info{
		Category:    "custom",
		Description: "random JSON of a person",
		Example:     `{"first_name":"Bob", "last_name":"Jones"}`,
		Output:      "[]byte",
		Generate: func(r *rand.Rand, m *MapParams, info *Info) (interface{}, error) {

			v, _ := JSON(&JSONOptions{
				Type:     "object",
				RowCount: 1,
				Fields: []Field{
					{Name: "first_name", Function: "firstname"},
					{Name: "last_name", Function: "lastname"},
				},
			})

			fmt.Printf("Z: %s\n", string(v))

			return v, nil
		},
	})

	type RandomPerson struct {
		FakePerson []byte `fake:{jsonperson}"`
	}

	var f RandomPerson
	Struct(&f)
	fmt.Println(string(f.FakePerson))
	//fmt.Printf("%s\n", string(f.FakePerson))
}

v, _ := JSON(&JSONOptions{
				Type:     "object",
				RowCount: 1,
				Fields: []Field{
					{Name: "first_name", Function: "firstname"},
					{Name: "last_name", Function: "lastname"},
				},
			})

Properly generates the JSON that I want, but something does not seem right when I actually generate the random JSON using the lookup function, I get nonsense like:

Any insight?

[brianvoe]

Can you submit a pr and ill pull it down and take a look at it?

[petergeorgas]

#201 is the PR! Let me know what I can do!

[brianvoe]

Just merged your commit into v6.16.0. I made some updates, cleanups and added the json usage and lookup usage. Once again nice job on this integration!