Golang model and Operation function of database table using template

This is a creation in Article, where the information may have evolved or changed.

• Cause
• Build process
  • Preparatory work
  • Get Table structure Information
  • Generate struct
• Related Template Syntax description
• A little explanation
• A little bit of a problem

Cause

Many years ago, when I first came into contact with the ORM, I was a little puzzled: this thing is convenient to use, is the model structure of a field of a field write, very boring and very tiring, and if the table structure modified, such as adding, reducing or modifying a field, you have to modify the model file. It didn't even occur at that time. The table structure data that can be read from the database to the target table automatically generates the model structure that ORM needs. Until one day I saw a code that automatically generated the ORM model file based on the template, and then I wrote it with Golang. The complete code is here.
　　

Build process

Preparatory work

Suppose I created a library named Dbnote in MySQL and created a table named MSG, creating the following statement:

CREATE TABLEMSG (IDint( One) not NULLAuto_increment, sender_idint( One) not NULLCOMMENT' sender ', receiver_idint( One) not NULLCOMMENT' recipient ', contentvarchar( the) not NULLCOMMENT' content ', Status tinyint (4) not NULL, Createtimetimestamp  not NULL DEFAULT Current_timestamp,PRIMARY KEY(ID));

Then the corresponding ORM model is

typeMsgstruct{IDint        ' DB: ' ID ' JSON: ' ID ' '                   //SenderIDint        ' db: ' sender_id ' json: ' sender_id '     //SenderReceiveridint        ' db: ' receiver_id ' json: ' receiver_id ' //RecipientContentstring     ' DB: ' content ' json: ' Content '         //ContentStatusint8       ' db: ' status ' json: ' Status '           //Createtime *time. Time' db: ' Createtime ' json: ' Createtime '   //}

Get Table structure Information

In order to generate this struct and related additions and deletions to the code, I need to get the result information of this table, and write the corresponding template file for code generation.
Through query statements

SELECT table_name from tables where table_schema='dbnote'

You can get all the table names in this library (and, of course, you can also add filter filters to the target table).

Using the name of the target table through the query statement

SELECT COLUMN_NAME,DATA_TYPE,COLUMN_KEY,COLUMN_COMMENT from COLUMNS where TABLE_NAME='msg' and table_schema = 'dbnote'

You can get the structure information for this table.
The information for the table structure is stored with such a struct

typestruct {    COLUMN_NAME    string`db:"COLUMN_NAME" json:"column_name"`    DATA_TYPE      string`db:"DATA_TYPE" json:"data_type"`    COLUMN_KEY     string`db:"COLUMN_KEY" json:"column_key"`    string`db:"COLUMN_COMMENT" json:"COLUMN_COMMENT"`}

To generate all the information needed for the model and the operation function, a struct is used to store

typestruct {    BDName       string    string    TableName    string    PackageName  string    ModelName    string    TableSchema  *[]TABLE_SCHEMA}

Generate struct

The code for initializing the template object is like this

Data, _: = Ioutil. ReadFile (".. /modeltool/model.tpl "Render: = template. Must (template. New ("Model"). Funcs (template. funcmap{"Firstcharupper": Modeltool. Firstcharupper,"Typeconvert": Modeltool. Typeconvert,"Tags": Modeltool. Tags,"Exportcolumn": Modeltool. Exportcolumn,"Join": Modeltool. Join,"Makequestionmarklist": Modeltool. Makequestionmarklist,"Columnandtype": Modeltool. Columnandtype,"Columnwithpostfix": Modeltool.        Columnwithpostfix,}). Parse (string(data)))

See the code for the definition of the function.

After populating the good one Modelinfo object, you can generate a code file.

ifnil {        log.Fatal(err)    }    fmt.Println(fileName)    cmd := exec.Command("goimports""-w", fileName)    cmd.Run()

Finally with Goimports add need to import the package, and goimports even format work has done, simply too cool.
　　

Related Template Syntax description

• The following statement handles the value of modelname with the function firstcharupper and assigns the value to the $exportmodelname variable

{{$exportModelName := .ModelName | FirstCharUpper}}

• The following statement uses the variable $exportmodelname

type {{$exportModelName}} struct

• The following statement strips down the line from the value of the field column_name with the function exportcolumn and capitalizes the word, and processes the ID into an ID. Exportcolumn content See code, can be adjusted according to actual needs.

{{.COLUMN_NAME | ExportColumn}}

• The following statement loops through the elements in the Modelinfo.tableschema

{{range .} Tableschema}} {{. COLUMN_NAME | exportcolumn}} {{. DATA_TYPE | Typeconvert}} {{. COLUMN_NAME | Tags}} // {{. COLUMN_COMMENT}}{{end}}}

• The following statement uses 3 parameters. Pkcolumns,=?, and Call function Columnwithpostfix

{{ColumnWithPostfix .PkColumns "=?" " and "}}"

The definition of a function is

func ColumnWithPostfix(columns []stringstringstring {    make([]string, 0len(columns))    forrange columns {        append(result, t+Postfix)    }    return strings.Join(result, sep)}

• The following statement is another style of looping

{{range $K:=.PkColumns}}{{$K}},{{end}}

Additions and deletions to the code automatically generated there is no need to specifically explain, see the code.

A little explanation

The directory structure of the code is like this

|____dbnotes  Span class= "hljs-string" >| |____dbhelper  | | |____dbhelper.go  | |____dbnote.go  | |____init.go  | |_ ___model  | | |____mail.go  | | |____msg.go  Span class= "hljs-string" >| | |____notice.go  | |____modelgenerator  | | |____modelgenerator.go  | |____modeltool  | | |____model.tpl  | | |____modeltool.go   

Modelgenerator.go compiled, run Modelgenerator can be based on MODEL.TPL will be the struct and operation functions to generate source files, stored in the Model directory Mail.go, Msg.go, Notice.go's 3 source files are automatically generated. The Create command for 3 tables is in the comment code of the init.go.
Database IP address, username and password in the DBHELPER.GO init () function, the DB instance is used to connect to the library where mail, MSG, notice reside, and SYSDB instance is used to connect INFORMATION_SCHEMA library to get table structure information.
Dbnote.go is the sample code, the demonstration of mail, MSG, NOTICE3 table data additions and deletions to change.

A little bit of a problem

Since the base type of Golang has a default initial value, there are no variables that are not initialized after the definition. So, for NULL in a database, there is no good way to deal with it directly, if the data type in the struct is defined like this

Content    *string

It is possible to receive the value of the content and null, but since then, the value and assignment of the contents is not so convenient, of course, you can use nullstring. Since the basic type of Golang has a default initial value, I personally think it is better to set the table to not accept null values.