1, Background

In the process of business scenario development, with the increase of data volume, the table splitting strategy with the same table structure and different table names is one of the common scheme choices. As follows, take golang as the back-end business development, and try to modify beego's orm library to implement a separate table with the same table structure and different table names.

2, Modification logic of different table names with the same table structure in orm

 

 

 

3, orm sub table comparison

exercise do	Use regardless of table code	Use of sub table code
write enter	o := orm.NewOrm() user := User{Name: "slene"} // insert id, err := o.Insert(&user)	o := orm.NewOrm() user := User{Name: "slene"} // set table name to `user_1` o.ShardingTable( func(tableName string) string { return tableName + "_1" }, ) // insert id, err := o.Insert(&user)
more new	o := orm.NewOrm() user := User{Name: "slene"} // update user.Name = "astaxie" num, err := o.Update(&user)	o := orm.NewOrm() user := User{Name: "slene"} // set table name to `user_1` o.ShardingTable( func(tableName string) string { return tableName + "_1" }, ) // update user.Name = "astaxie" num, err := o.Update(&user)
check Inquire	o := orm.NewOrm() user := User{id: 1} // select o.QueryTable(user).One(&user)	o := orm.NewOrm() user := User{id: 1} // set table name to `user_1` o.ShardingTable( func(tableName string) string { return tableName + "_1" }, ) // select o.QueryTable(user).Offset(offset).Limit(limit).One(&user) // select id, name from user_1 where id=1 limit 0,1
Delete except	o := orm.NewOrm() user := User{id: 1} // delete o.Delete(user) // delete from user_1 where id=1	o := orm.NewOrm() user := User{id: 1} // set table name to `user_1` o.ShardingTable( func(tableName string) string { return tableName + "_1" }, ) // delete o.Delete(user) // delete from user_1 where id=1

From the CURD above, we can see the following code to modify the table name in real time

// set table name to `user_1`
o.ShardingTable(
    func(tableName string) string {
        return tableName + "_1"
    },
)

  Let's analyze step by step how orm realizes the real-time modification of table names.

4, Analysis of sub table implementation

  4.1 modify code file: https://github.com/gityf/orm/blob/master/orm/types.go

// Ormer define the orm interface
type Ormer interface {
   // ...

   // set sharding table in time for different table name with same struct.
   // ormer.ShardingTable(
   //    func(tableName string) string {
   //       return tableName + "_" + tableNameSuffix
   //    },
   // )
   ShardingTable(func(string) string)
}

  Modify the Ormer interface of types.go and add a sub table function. The parameter is a function to obtain the sub table, that is, the setting of the table name of each sub table is an ORM instance. Created by orm.NewOrm().

4.2 modify code file: https://github.com/gityf/orm/blob/master/orm/orm.go

Modify the orm.go file and add the following two sub table related functions to the ORM structure

type orm struct {
   alias *alias
   db    dbQuerier
   isTx  bool
   sharding      func(string) string
   shardingTable func(string) string
}

With the above sharding function, we just need to set the sharding function in real time when creating orm, and obtain the new table name through the function.

In the function NewOrm, the newly created

o := new(orm)

Set a sub table function as follows

// switch to another registered database driver by given name.
func (o *orm) Using(name string) error {
	// ...
	if al, ok := dataBaseCache.get(name); ok {
		o.sharding = func(table string) string {
			if o.shardingTable == nil {
				return table
			}
			return o.shardingTable(table)
		}
	}
	// ...
}

The orm structure has a split table function. We need to add a split table function to the member dbQuerier of orm, as follows

o.db = &DB{
   RWMutex: al.DB.RWMutex,
   DB: al.DB.DB,
   stmtDecorators: al.DB.stmtDecorators,
   sharding: o.sharding,
}

  4.3 modify code file: https://github.com/gityf/orm/blob/master/orm/db_alias.go

DB is an implementation of the dbquery interface. A sub table function is added to the implementation of the DB structure

func (d *DB) Sharding(table string) string {
   return d.sharding(table)
}

  4.4 modify code file: https://github.com/gityf/orm/blob/master/orm/db.go

When generating sql in the CURD of dbBase in the db code file, the following implementation of real-time modification of table name is added   q.Sharding(mi.table)

// create insert sql preparation statement object.
func (d *dbBase) PrepareInsert(q dbQuerier, mi *modelInfo) (stmtQuerier, string, error) {
   Q := d.ins.TableQuote()

   // ...

   query := fmt.Sprintf("INSERT INTO %s%s%s (%s%s%s) VALUES (%s)", Q, q.Sharding(mi.table), Q, Q, columns, Q, qmarks)
   // ...
}

For other orm to sql, obtain the table name through q.Sharding(mi.table) of dbquery

 

Add orm the code implementation of the same table result with different table names: https://github.com/gityf/orm

 

Have fun~