This is the 25th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

preface

Previously, we used Python to connect to OpenGauss, but because Go’s reflection is better, so this time we use Go language to connect to OpenGauss, and build a simple ORM.

Connecting to a Database

Postgresql drivers are usually OpenGauss drivers. I have chosen bmizeran/ PQ as the driver.

First, we need to build the configuration file:

# app.ini
[database]
User = jack
Password= * * * * * * * *Host= * * * *. * *. * *Port = 26000
Name = postgres
Copy the code

Then build a Setup function to configure the configuration file, and use sql.open() to connect:

// models.go
package models

import (
   "database/sql"
   "fmt"
   _ "github.com/bmizerany/pq"
   "log"
   "main/pkg/setting"
)

var db *sql.DB

Setup initializes the database
func Setup(a) {
   var err error
   dsn := fmt.Sprintf("host=%s port=%d user=%s dbname=%s password=%s sslmode=disable TimeZone=Asia/Shanghai",
         setting.DatabaseSetting.Host,
         setting.DatabaseSetting.Port,
         setting.DatabaseSetting.User,
         setting.DatabaseSetting.Name,
         setting.DatabaseSetting.Password,
      )
   db, err = sql.Open("postgres", dsn)
   iferr ! =nil {
      log.Fatalf("Database configuration error: %v", err)
   }
}
Copy the code

Note that even though it doesn’t look like bmizerany/pg is being used, we still need to import it (requiring a _), which will introduce a postgres driver in the background, otherwise we will get the following error:

By analogy with Python libraries, the relationship between the two is similar to that between BeautifulSoup and LXML, which will not be expanded here.

Automatically build table

Here is an example of how to create a table using a SQL statement:

CREATE TABLE Account (
   id serial PRIMARY KEY,
   name VARCHAR(64) NOT NULL,
   username VARCHAR(64) NOT NULL,
   password VARCHAR(64) NOT NULL,
   role_id INT NOT NULL.FOREIGN KEY(role_id) REFERENCES Role(id)
);
Copy the code

Here we have a total of five fields and one extra row to associate with the role table.

It is not difficult to find that the creation structure of the 5 fields is the same: the field name type constraint

Therefore, you need to implement automatic table building. In practice, you need to find a way to extract the corresponding part from the struct. To achieve this function, you naturally want to use reflection and tag to implement.

The following is a more convenient coding implementation scheme:

type Account struct {
   Id       string `json:"id" type:"serial" constraint:"PRIMARY KEY"`
   Name     string `json:"name" type:"VARCHAR(64)" constraint:"NOT NULL"`
   Username string `json:"account" type:"VARCHAR(64)" constraint:"NOT NULL"`
   Password string `json:"password" type:"VARCHAR(64)" constraint:"NOT NULL"`
   RoleId   string `json:"role_id" type:"INT" constraint:"NOT NULL"`

   extra string `constraint:"FOREIGN KEY(role_id) REFERENCES Role(id)"`
}
Copy the code

The extraction methods of each parameter are as follows:

  1. Field name => Reflect the JSON in the fetch label
  2. Type => Reflection gets the type in the tag
  3. Constraint => Reflect the constraint in the fetch tag

Then use extra’s constraint to save other associations and constraints that appear.

For example, the Field Name can be obtained through the reflection field. Name, the data type can be set automatically by the data type of the Field, and the association condition can be automatically generated by the Tag. But it will increase the amount of coding, and we will optimize it later.

The code implementation of the above functions is generally as follows, mainly through string concatenation to generate SQL statements, and then delivered to the database processing:

CreateTable Creates a table
func CreateTable(tables []interface{})  {
   for _, table := range tables {
      t := reflect.TypeOf(table)
      tableName := strings.Split(t.String(), ".") [1]
      sql := "CREATE TABLE " + tableName + " (\n"
      for i := 0; i < t.Elem().NumField(); i++ {
         field := t.Elem().Field(i)
         if i == t.Elem().NumField() - 1 {
            extra := field.Tag.Get("constraint")
            ifextra ! ="" {
               sql += ",\n\t" + extra
            }
            break
         }
         ifi ! =0 {
            sql += ",\n\t"
         }
         sql += fmt.Sprintf(
            "%s %s %s",
            field.Tag.Get("json"),
            field.Tag.Get("type"),
            field.Tag.Get("constraint"),
         )
      }

      sql += "\n);"
      logrus.Debugln(sql)
      _, err := db.Exec(sql)
      iferr ! =nil {
         logrus.Errorln(tableName, "Create failed :", err.Error())
      } else {
         logrus.Println(tableName, "Created successfully")}}}Copy the code

In it, a loop appears to get the fields of the structure we wrote by way of reflection:

t := reflect.TypeOf(table)
for i := 0; i < t.Elem().NumField(); i++ {
    field := t.Elem().Field(i)
}
Copy the code

With each field retrieved, the tag reading is simple:

name := field.Tag.Get("json")
dType := field.Tag.Get("type")
constraint := field.Tag.Get("constraint")
Copy the code

Method of use

CreateTable([]interface{}{
   &Role{},
   &Account{},
})
Copy the code

Since there are foreign key relationships, we need to first propagate the Role table, then build the Account table, after the database is created, execute this code, the result is as follows:

The results are as expected at the beginning.