---
title: Identify Sharding Key & CRUD statement for sharding function
tags: SQL
description: ID Generator & CRUD statement for sharding function
---
# Identify Sharding Key
- INT Identity
- 64 bits
- sortable by time

## Sharding strategies
- The Hash strategy
- increase loading, distribute data across the shards in a way.
- The Range strategy
- group related item together in the same shard, and order them by shard key
- split hot data and cold data

### Config file

{
  "versionControl": 
  [
    {
      "ver" : number, //version number
      "rules" : [ // each bounded context
        {
          "entities": [ // each logical table config
            {
              "name": string, // logical table name
              "key": string, // column name of primary key
              "timeZone": string, // time zone string like "Pacific/Guam", defalt is UTC
              "table": [
                  {  // default table
                      "name": "*",
                      "id": 0
                  },
                  { // each brand or product are involved 
                      "name": string, // business name
                      "id": number, // business number
                      "hash": number, 
                      "date": string, // day,month,year, week, year_day
                  }
              ]
            }
          ],
          "nodes": [ // each db machine
              { // machine name 
                "name": string // ex : tranDB00, tranDB31, matchDB00,  
              }
          ]
        }
      ]
    }
  ]
}

### Sharding Key Generator
* 單一業務操作

iw, err := NewIdGenerator("order", "AG", vc)
if err != nil {
    panic(err)
}
id, err := iw.NextID()

* 關聯性事務操作

// master
iw, err := NewIdGenerator("order", "AG", vc)
if err != nil {
    panic(err)
}
id, err := iw.NextID()
// sub
transIW, err := NewIdGenerator("transaction", "AG", vc)
subId, _ := transIW.NextIDWithDbIndex(id)

### SQL CRUD
同一句不能有多個表，都參與分表規則;
ex: log 與 訂單trans 都參與了分表規則,
有訂單編號, 123
select * from trans as t inner join log as l on l.id = t.id where t.id = 123;
可以知道訂單表的實體表名, 但無法得知對應訂單的所有log紀錄坐落在哪些實體表名.
* 單一業務操作

cmd := dc.NewCommand(context.TODO())
txt := "select * from `order` left join `tranxxx` on o.order_id = tranxxx.order_id WHERE order_id={id}"

// RawSQL 先執行 佔位符的變數替換
// 解析該句所有表名, 及對應的sharding key-value
// 
cmd.RawSQL(configRule, "AG",txt, map[string]interface[}]) // "id":1153055933662756864
// sql -> select * from order_00_186_00  left join tranxxx on order_00_186_00.order_id = tranxxx.order_id where order_00_186_00.oder_id = 1153055933662756864
if result, err := cmd.Exec(); err != nil {
  // xxx
}


newSqlCmd, err := sqlparser.Parse(vc, "AG", "select * from `order` as o left join `trans1` on o.order_id = trans1.order_id where o.oder_id =  ?; update tableB set uname = (select name from `order` as o left join `order1` on `order`.order_id = order1.order_id where o.oder_id =  ?); ", 1153055933662756864, 1153055933662756864)

* 關聯性事務操作

dc := db.NewContext(NewContext(), params)
dc.BeginTx()
// execute first command
cmd := dc.NewCommand(context.TODO())
txt := "xxx"
cmd.RawSQL(configRule, "AG", txt, map[string]interface[}])

if result, err := cmd.Exec(); err != nil {
    dc.Rollback()
}

cmd1 := dc.NewCommand(context.TODO())
txt1 := "xxx"
cmd1, err := cmd1.RawSQL(configRule, "AG", txt1, map[string]interface[}])
if err != nil {
    // 該句跨資料庫做操作
    dc.Rollback()
}

if result, err := cmd1.Exec(); err != nil {
    dc.Rollback()
}
dc.Commit()

[
  "select * from order_AG_186_00 as o left join trans1 on o.order_id = trans1.order_id where o.oder_id = 1153055933662756864",
  "update tableB set uname = (select name from order_AG_186_00 as o left join order1 on order_AG_186_00.order_id = order1.order_id where o.oder_id = 1153055933662756864)"
]

 order_00_186_00

###