Business background

In this business scenario, there is a table tablea on the line and a mirror table tablea in the production environment_ Mirror, now you need to access tablea when there are some tag identifiers in the request_ For the mirror table, DefaultTableNameHandler is sometimes used

Install sqlite first

https://wangxiaoming.blog.csdn.net/article/details/121884736

code

You can use DefaultTableNameHandler to implement the prefix or suffix function.

import (
 "code.byted.org/gopkg/gorm"
 "context"
)

type dbStagingPostfixKeyType struct{}

var dbStagingPostfixKey = dbStagingPostfixKeyType{}

func WithDbStagingPostfix(ctx context.Context, postfix string) context.Context {
 return context.WithValue(ctx, dbStagingPostfixKey, postfix)
}

func ReWriteTableName() {
 gorm.DefaultTableNameHandler = func(db *gorm.DB, defaultTableName string) string {
  if v := db.Ctx.Value(dbStagingPostfixKey); v != nil {
   return defaultTableName + v.(string)
  }
  return defaultTableName
 }
}

Test code

package mysql

import (
 "fmt"
 "testing"

 "github.com/jinzhu/gorm"
 //_ "github.com/jinzhu/gorm/dialects/sqlite"
 _ "github.com/mattn/go-sqlite3"
)

type Product struct {
 gorm.Model
 Code  string
 Price uint
}

func (Product) TableName() string {
 return "hax_products"
}
func Test(t *testing.T) {
 db, err := gorm.Open("sqlite3", "test.db")
 if err != nil {
  panic("failed to connect database")
 }
 defer db.Close()
 gorm.DefaultTableNameHandler = func(db *gorm.DB, defaultTableName string) string {
  return "hax_" + defaultTableName
 }
 db.LogMode(true)
 // Migrate the schema
 db.AutoMigrate(&Product{})
 db.Create(&Product{Code: "L1212", Price: 1000})
 var product Product
 db.First(&product, 1)
 var products []Product
 db.Find(&products)
 fmt.Printf("Total count %d", len(products))
}

Execution results:

(/Users/xxx/go/src/xxx/xxx.xx/GoProject/mysql/sqllite_test.go:33) 
[2021-12-14 21:43:33]  [1.38ms]  INSERT INTO "hax_products" ("created_at","updated_at","deleted_at","code","price") VALUES ('2021-12-14 21:43:33','2021-12-14 21:43:33',NULL,'L1212',1000)  
[1 rows affected or returned ] 

(/Users/xxx/go/src/xxx/xxx.xx/GoProject/mysql/sqllite_test.go:35) 
[2021-12-14 21:43:33]  [0.23ms]  SELECT * FROM "hax_products"  WHERE "hax_products"."deleted_at" IS NULL AND (("hax_products"."id" = 1)) ORDER BY "hax_products"."id" ASC LIMIT 1  
[1 rows affected or returned ] 

((/Users/xxx/go/src/xxx/xxx.xx/GoProject/mysql/sqllite_test.go:37) 
[2021-12-14 21:43:33]  no such table: hax_hax_products 

((/Users/xxx/go/src/xxx/xxx.xx/GoProject/mysql/sqllite_test.go:37) 
[2021-12-14 21:43:33]  [0.10ms]  SELECT * FROM "hax_hax_products"  WHERE "hax_hax_products"."deleted_at" IS NULL  
[0 rows affected or returned ] 
Total count 0--- PASS: Test (0.00s)
PASS

According to the execution results, you can see that when creating a language and querying a single record, the table name is hax_products uses the table name hax when querying multiple records_ hax_products.

This is Pit 1

When querying a single record, the table name returned by TableName() is used. When the query result is Array, the table name is prefixed with TableName().

Gorm structure is generally analyzed as follows: struct

• type DB struct (gorm/main.go) represents the database connection. The clone object will be created each time the database is operated. Method Gorm The value type returned by open () is the structure pointer.
• type Scope struct (gorm/scope.go) is the information of the current database operation. The clone object will also be created each time a condition is added.
• type Callback struct (gorm/callback.go) is the callback function for various database operations. SQL generation also depends on these callback functions. Each type of callback function is put in a separate file. For example, the query callback function is in gorm/callback_query.go, created in gorm/callback_create.go

db.First() code analysis

The First() method is located in Gorm / main Go file callCallbacks(s.parent.callbacks.queries) called the query callback function.

// file: gorm/main.go
// First find first record that match given conditions, order by primary key
func (s *DB) First(out interface{}, where ...interface{}) *DB {
 newScope := s.NewScope(out)
 newScope.Search.Limit(1)
 return newScope.Set("gorm:order_by_primary_key", "ASC").
  inlineCondition(where...).callCallbacks(s.parent.callbacks.queries).db
}

queries is defined as a function pointer array in the Callback structure, and the default value is initialized in Gorm / Callback_ query. In the init() method of go, the query method is queryCallback, and the queryCallback() method calls scope Preparequerysql(), where the method in the scope actually generates SQL.

// file: gorm/callback.go
type Callback struct {
 logger     logger
 creates    []*func(scope *Scope)
 updates    []*func(scope *Scope)
 deletes    []*func(scope *Scope)
 queries    []*func(scope *Scope)
 rowQueries []*func(scope *Scope)
 processors []*CallbackProcessor
}
// file: gorm/callback_query.go
// Define callbacks for querying
func init() {
 DefaultCallback.Query().Register("gorm:query", queryCallback)
 DefaultCallback.Query().Register("gorm:preload", preloadCallback)
 DefaultCallback.Query().Register("gorm:after_query", afterQueryCallback)
}
// queryCallback used to query data from database
func queryCallback(scope *Scope) {
...
    scope.prepareQuerySQL()
...
}

Trace code to scope Go file, the function TableName() is the place to get the database table name. It determines the table name in the following order:

• scope. Search. If the table name is set in the tablename query criteria, it is used directly
• scope. Value. If the (tabler) value object implements the tabler interface (method TableName() string), it is obtained from the calling method
• scope. Value. If the (dbTabler) value object implements the dbTabler interface (method TableName(*DB) string), it is obtained from the calling method
• If none of the above conditions is true, start from scope Getmodelstruct() gets the structure information of the object and generates the table name from the structure name

See scope for details Go source code

// file: gorm/scope.go
func (scope *Scope) prepareQuerySQL() {
 if scope.Search.raw {
  scope.Raw(scope.CombinedConditionSql())
 } else {
  scope.Raw(fmt.Sprintf("SELECT %v FROM %v %v", scope.selectSQL(), scope.QuotedTableName(), scope.CombinedConditionSql()))
 }
 return
}
// QuotedTableName return quoted table name
func (scope *Scope) QuotedTableName() (name string) {
 if scope.Search != nil && len(scope.Search.tableName) > 0 {
  if strings.Contains(scope.Search.tableName, " ") {
   return scope.Search.tableName
  }
  return scope.Quote(scope.Search.tableName)
 }
 return scope.Quote(scope.TableName())
}
// TableName return table name
func (scope *Scope) TableName() string {
 if scope.Search != nil && len(scope.Search.tableName) > 0 {
  return scope.Search.tableName
 }
 if tabler, ok := scope.Value.(tabler); ok {
  return tabler.TableName()
 }
 if tabler, ok := scope.Value.(dbTabler); ok {
  return tabler.TableName(scope.db)
 }
 return scope.GetModelStruct().TableName(scope.db.Model(scope.Value))
}

Compared with the above conditions, the Product structure in the example defines the method TableName() string. If condition 2 is met, DB The table name used by first (& Product, 1) is hax_products.

db.Find() code analysis

The code of Find() is as follows, and the same as First() uses callbacks Queries callback method. The difference is that newscope is set Search. Limit (1) returns only one result, and sorting by id is added.

// Find find records that match given conditions
func (s *DB) Find(out interface{}, where ...interface{}) *DB {
 return s.NewScope(out).inlineCondition(where...).callCallbacks(s.parent.callbacks.queries).db
}

In debug mode, trace the code to scope In tablename(), the difference between the two queries is shown: their result value types are different. db. The value type of first (& Product, 1) is the pointer * Product of the structure, and DB The value type of find & products is the pointer * [] Product of the array, resulting in dB Find & products to enter the condition scope GetModelStruct(). Tablename (scope. DB. Model (scope. Value))}, the table name needs to be generated by analyzing the struct structure.

// file: gorm/model_struct.go
// TableName returns model's table name
func (s *ModelStruct) TableName(db *DB) string {
 s.l.Lock()
 defer s.l.Unlock()
 if s.defaultTableName == "" && db != nil && s.ModelType != nil {
  // Set default table name
  if tabler, ok := reflect.New(s.ModelType).Interface().(tabler); ok {
   s.defaultTableName = tabler.TableName()
  } else {
   tableName := ToTableName(s.ModelType.Name())
   db.parent.RLock()
   if db == nil || (db.parent != nil && !db.parent.singularTable) {
    tableName = inflection.Plural(tableName)
   }
   db.parent.RUnlock()
   s.defaultTableName = tableName
  }
 }
 return DefaultTableNameHandler(db, s.defaultTableName)
}

When the default table name s.defaultTableName is null, it is evaluated first, and reflect New(s.ModelType). Interface(). (tabler) first judge whether the tabler interface is implemented. If yes, call its TableName() value; Otherwise, the table name is generated from the name of the structure. Call the DefaultTableNameHandler(db, s.defaultTableName) method before returning the result.

The TableName method of this ModelStruct is the same as scope There are two inconsistencies in the logic in TableName():

1. scope.TableName() will judge whether to implement the two interfaces of tabler and dbTabler, and only tabler is judged here
2. scope.TableName() returns the tableName result directly, and DefaultTableNameHandler() is called here.

Because logical scope For the existence of tablename(), when the DefaultTableNameHandler() method is overridden, the table prefix will be added before the table name again.

Question 2

DefaultTableNameHandler() is confused when using multiple databases

Through the analysis of the above code, another pit is found: when two different databases are used in a program, rewriting the method DefaultTableNameHandler() will affect the table names in the two databases. When one database needs to set a table prefix, tables accessing another database may also be prefixed. Because it is a package level method, the value can only be set once in the whole code.

// file: gorm/model_struct.go
// DefaultTableNameHandler default table name handler
var DefaultTableNameHandler = func(db *DB, defaultTableName string) string {
 return defaultTableName
}

summary

• When the TableName() method is implemented for the structure, do not set the DefaultTableNameHandler.
• Keep the table names of all models generated in the same way, either use the automatically generated table names or implement the tabler interface (implement - TableName() method)
• When you need to use multiple databases, avoid setting DefaultTableNameHandler
• It is strongly recommended that all Model structures implement the tabler interface

reference material

• http://blog.vikazhou.com/2020/02/09/GORM-Problem-Analyze/