Option to specify table prefix during code generation

[lem0na]

Could be possible to add option to provide table prefix while generating go structs? What I mean: sometime people use cheap database hosting with one database only and to distinct tables that belong to different application the tables are prefixed. This will not be case if you have dedicated database this such prefixing is not needed but to make generated structs to cover both cases will be nice to have to provide the prefix string via some kind of configuration from the application.

[bokwoon95]

sometime people use cheap database hosting with one database only and to distinct tables that belong to different application the tables are prefixed

I understand this use case, but if the tables are already prefixed in the database why not just generate them into Go structs as usual? Or do the tables exist in the database, but are not prefixed?

[bokwoon95]

Once the table structs have been generated without the prefix, how do you intend to use them? Won't queries generated using those table structs fail because they have no prefix, while the tables in the database have a prefix?

[lem0na]

This is the idea like in the example code that you provided
prefix is set in configuration file, it can be empty (no prefix) or something
Let say that in the database I have table serendipity_account
if struct is generated without prefix it will be
type ACCOUNT struct {
...............
}
var prefix = "serendipity_"
and then
a := New[ACCOUNT]("a", prefix)

same will work if the table name is "account" and prefix = ""
an user just take the binary, depending on the database sets configuration to have prefix to "serendipity_" or "" and runs the app

I hope I was able to explain the case
I know that this is rare case with legacy apps, but will be nice option if it is possible

[bokwoon95]

I won't make a specific flag, but you can actually do this on your own quite easily using the underlying ddl library. I just committed a change to https://github.com/bokwoon95/sqddl to make it easier. Here is some example code to show how you can generate a list of ddl.TableStructs from a database connection and remove a prefix from each ddl.TableStruct's name, before converting it into Go code and printing it to the console.

# Do a go install to get the latest version of sqddl that I just pushed (v0.3.3)
go install https://github.com/bokwoon95/sqddl@latest

package main

import (
    "database/sql"
    "fmt"
    "go/format"
    "log"
    "strings"

    "github.com/bokwoon95/sqddl/ddl"
    _ "github.com/lib/pq"
)

func main() {
    db, err := sql.Open("postgres", "postgres://user1:Hunter2!@localhost:5456/sakila?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    // Get the TableStructs.
    tableStructs, err := ddl.NewTableStructs("postgres", db, ddl.Filter{})
    if err != nil {
        log.Fatal(err)
    }
    // Modify the names of each TableStruct (TrimPrefix).
    for i, tableStruct := range tableStructs {
        if strings.HasPrefix(tableStruct.Name, "SERENDIPITY_") {
            tableStructs[i].Name = strings.TrimPrefix(tableStruct.Name, "SERENDIPITY_")
        }
    }
    // Convert the TableStructs into Go source code.
    text, err := tableStructs.MarshalText()
    if err != nil {
        log.Fatal(err)
    }
    // Run gofmt on the source code.
    text, err = format.Source(text)
    if err != nil {
        log.Fatal(err)
    }
    // Print the table structs, but you can write it to a file instead.
    fmt.Println("package tables\n\nimport \"github.com/bokwoon95/sq\"\n\n" + string(text))
}

The idea is that you instead of using the sqddl command to generate table structs, you will maintain a custom binary using the source code above to generate it for you as necessary.

[lem0na]

thank you

[bokwoon95]

good luck with the CMS port, always a big undertaking

[bokwoon95]

Ohh I just had an idea, you can basically copy the implementation of sq.New (it doesn't access any private fields) and modify it to take in an additional table prefix.

Here's a runnable example: https://go.dev/play/p/So4M2iGy6hk

type USERS struct {
    sq.TableStruct
    USER_ID    sq.NumberField
    NAME       sq.StringField
    EMAIL      sq.StringField
    CREATED_AT sq.TimeField
}

prefixes := []string{"", "t001_", "lorem_ipsum_"}
for _, prefix := range prefixes {
    u := New[USERS]("u", prefix)
    q := sq.Select(u.NAME, u.EMAIL).From(u)
    query, _, err := sq.ToSQL("", q, nil)
    if err != nil {
        log.Fatal(err)
    }
    log.Println(query)
}
// 2009/11/10 23:00:00 SELECT u.name, u.email FROM users AS u
// 2009/11/10 23:00:00 SELECT u.name, u.email FROM t001_users AS u
// 2009/11/10 23:00:00 SELECT u.name, u.email FROM lorem_ipsum_users AS u

// New is copied from https://pkg.go.dev/github.com/bokwoon95/sq#New, but takes
// an additional table prefix.
func New[T sq.Table](alias string, prefix string) T {
    var tbl T
    ptrvalue := reflect.ValueOf(&tbl)
    value := reflect.Indirect(ptrvalue)
    typ := value.Type()
    if typ.Kind() != reflect.Struct {
        return tbl
    }
    if value.NumField() == 0 {
        return tbl
    }
    firstfield := value.Field(0)
    firstfieldType := typ.Field(0)
    if !firstfield.CanInterface() {
        return tbl
    }
    _, ok := firstfield.Interface().(sq.TableStruct)
    if !ok {
        return tbl
    }
    if !firstfield.CanSet() {
        return tbl
    }
    var tableSchema, tableName string
    tag := firstfieldType.Tag.Get("sq")
    if i := strings.IndexByte(tag, '.'); i >= 0 {
        tableSchema = tag[:i]
        tableName = tag[i+1:]
    } else {
        tableName = tag
    }
    if tableName == "" {
        tableName = strings.ToLower(typ.Name())
    }
    tableStruct := sq.NewTableStruct(tableSchema, prefix+tableName, alias)
    firstfield.Set(reflect.ValueOf(tableStruct))
    for i := 1; i < value.NumField(); i++ {
        v := value.Field(i)
        if !v.CanInterface() {
            continue
        }
        if !v.CanSet() {
            continue
        }
        fieldType := typ.Field(i)
        name := fieldType.Tag.Get("sq")
        if name == "" {
            name = strings.ToLower(fieldType.Name)
        }
        switch v.Interface().(type) {
        case sq.AnyField:
            v.Set(reflect.ValueOf(sq.NewAnyField(name, tableStruct)))
        case sq.ArrayField:
            v.Set(reflect.ValueOf(sq.NewArrayField(name, tableStruct)))
        case sq.BinaryField:
            v.Set(reflect.ValueOf(sq.NewBinaryField(name, tableStruct)))
        case sq.BooleanField:
            v.Set(reflect.ValueOf(sq.NewBooleanField(name, tableStruct)))
        case sq.EnumField:
            v.Set(reflect.ValueOf(sq.NewEnumField(name, tableStruct)))
        case sq.JSONField:
            v.Set(reflect.ValueOf(sq.NewJSONField(name, tableStruct)))
        case sq.NumberField:
            v.Set(reflect.ValueOf(sq.NewNumberField(name, tableStruct)))
        case sq.StringField:
            v.Set(reflect.ValueOf(sq.NewStringField(name, tableStruct)))
        case sq.TimeField:
            v.Set(reflect.ValueOf(sq.NewTimeField(name, tableStruct)))
        case sq.UUIDField:
            v.Set(reflect.ValueOf(sq.NewUUIDField(name, tableStruct)))
        }
    }
    return tbl
}

Let me know if this is what you need.

[lem0na]

Hi,
To me it seems to be good solution.
I hope this can be part of the library one day.
But this option will good to be available while generating model from database. Like --omitprefix="foo_".
Thanks a lot.