// build a new shard DB after more the limit
// or no any one shard DB.
func buildNewShardDB(grp *host.Group) (*MysqlShardDB, error) {
mut.Lock()
defer mut.Unlock()
shardDBName := "shard" + strconv.Itoa(ShardDBCnt+1)
// to check this new shard db has been exists.
isExists := IsExistsShardDB(shardDBName)
if isExists != nil {
return isExists, nil
}
newShardDBId := redis.BuildPrimaryKey(shardDBName, true)
shardDB := &MysqlShardDB{
Id: newShardDBId,
Name: shardDBName,
TableTotal: uint64(0),
SizeTotal: uint64(0),
HostGroupId: grp.Id,
Created: time.Now().Unix(),
HostGroup: grp,
}
// create the database to host.
//
master := host.GetBetterHost(grp.Master, "master")
db, err := (&master).ConnToDB("mysql")
if err != nil {
return nil, err
}
stmt, err := db.Prepare(fmt.Sprintf("CREATE DATABASE `%s` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci", shardDBName))
if err != nil {
return nil, err
}
_, err = stmt.Exec()
if err != nil {
return nil, err
}
stmt.Close()
db.Close()
// to write the new shard db to redis
//
err = redis.WriteDB(newShardDBId, redis.EncodeData(shardDB), "MysqlShardDB")
if err != nil {
return nil, err
}
// notice Mysql Project object to add a new shard db memery.
NewShardDBCh <- shardDB
return shardDB, nil
}
// get the better slave host db connection by shard tables.
//
func (tbl *MysqlTable) GetSlaveShardDBConn(shardTabOrderId int) (*sql.DB, error) {
if len(tbl.Shards) == 0 {
return nil, errors.New("No any one shard table")
}
shardDB := tbl.Shards[shardTabOrderId].ShardDB
h := host.GetBetterHost(shardDB.HostGroup.Slave, "slave")
if h == nil {
return nil, errors.New("no found out any one valid host")
}
db, err := h.ConnToDB(shardDB.Name)
if err != nil {
return nil, err
}
return db, err
}
func (d *DDL) alterOnAllShard(alterType string) (interface{}, *schema.MysqlTable, error) {
// to found out the alter table's host.
//var err error
var shardTables []*schema.MysqlShardTable
var curTable *schema.MysqlTable
for i := 0; i < len(schema.Tables); i++ {
if schema.Tables[i].Name == d.TableName {
curTable = schema.Tables[i]
shardTables = curTable.Shards
break
}
}
if shardTables == nil {
return nil, nil, errors.New("no found any one this table " + d.TableName)
}
// when alter table name, then need change MysqlProxyTable log
resultCh := make(chan *alterResult, runtime.NumCPU())
shardTBLen := len(shardTables)
// to do the sql at the same time.
for i := 0; i < shardTBLen; i++ {
go func(i int) {
stb := shardTables[i]
conn, err := host.GetBetterHost(stb.ShardDB.HostGroup.Master, "master").ConnToDB(stb.ShardDB.Name)
if err != nil && i == 0 {
conn.Close()
resultCh <- &alterResult{Res: nil, Err: err}
return
}
trueSql := ""
if alterType == "renameddl" {
oldSTbName := stb.Name
stb.Name = strings.Replace(stb.Name, curTable.Name, d.NewName, -1)
trueSql = "alter table " + oldSTbName + " rename " + stb.Name
stb.UpdateToRedisDB()
} else if alterType == "dropddl" {
oldSTbName := stb.Name
stb.Name = strings.Replace(stb.Name, curTable.Name, d.NewName, -1)
trueSql = "drop table " + oldSTbName
} else {
trueSql = strings.Replace(d.Sql, d.TableName, stb.Name, -1)
}
stmt, err := conn.Prepare(trueSql)
if err != nil {
conn.Close()
resultCh <- &alterResult{Res: nil, Err: err}
return
}
res, err := stmt.Exec()
if err != nil {
conn.Close()
resultCh <- &alterResult{Res: nil, Err: err}
return
}
stmt.Close()
conn.Close()
if i == 0 {
resultCh <- &alterResult{Res: res, Err: nil}
return
}
}(i)
}
res := <-resultCh
if res.Err != nil {
return nil, nil, res.Err
}
result, err := res.Res.RowsAffected()
return result, curTable, err
}
// restore the schema ddl.
func (tbl *MysqlTable) BuildNewShardTable() (*MysqlShardTable, error) {
curSharded := len(tbl.Shards)
tTableName := tbl.Name + "_shard" + strconv.Itoa(curSharded+1)
newTabId := redis.BuildPrimaryKey(tTableName, true)
shardTable := &MysqlShardTable{
Id: newTabId,
Name: tTableName,
RowTotal: uint64(0),
ShardDBId: "",
Created: time.Now().Unix(),
ShardDB: nil,
}
// get bettet group.
group := host.GetBetterMasterGroup()
shardDB, err := tbl.GetMasterShardDBByGroup(group)
if err != nil {
return nil, err
}
shardTable.ShardDBId = shardDB.Id
shardTable.ShardDB = shardDB
betterHost := host.GetBetterHost(group.Master, "master")
ddlSql, err := tbl.GetSchemaDDLByDb()
if err != nil {
return nil, err
}
ddlSql = strings.Replace(ddlSql, tbl.Shards[0].Name, tTableName, -1)
db, err := betterHost.ConnToDB(shardDB.Name)
if err != nil {
return nil, err
}
stmt, err := db.Prepare(ddlSql)
if err != nil {
return nil, err
}
_, err = stmt.Exec()
if err != nil {
return nil, err
}
stmt.Close()
// write the new shard table to redis
err = redis.WriteDB(newTabId, redis.EncodeData(shardTable), "MysqlShardTable")
if err != nil {
return nil, err
}
tbl.Shards = append(tbl.Shards, shardTable)
tbl.ShardIds = append(tbl.ShardIds, newTabId)
err = redis.UpdateDB(tbl.Id, redis.EncodeData(tbl), "MysqlTable")
if err != nil {
return nil, err
}
return shardTable, nil
}
func (d *DML) doOnAllShard() (interface{}, *schema.MysqlTable, error) {
// to found out the alter table's host.
//var err error
var shardTables []*schema.MysqlShardTable
var curTable *schema.MysqlTable
for i := 0; i < len(schema.Tables); i++ {
if schema.Tables[i].Name == d.TableName {
curTable = schema.Tables[i]
shardTables = curTable.Shards
break
}
}
if shardTables == nil {
return nil, nil, errors.New("no found any one this table " + d.TableName)
}
// when alter table name, then need change MysqlProxyTable log
resultCh := make(chan *alterResult, runtime.NumCPU())
shardTBLen := len(shardTables)
// the the sql param pointer
params := getInterfaceParams(d.Sql[1:])
// to do the sql at the same time.
for i := 0; i < shardTBLen; i++ {
go func(i int) {
stb := shardTables[i]
conn, err := host.GetBetterHost(stb.ShardDB.HostGroup.Master, "master").ConnToDB(stb.ShardDB.Name)
if err != nil {
conn.Close()
resultCh <- &alterResult{Res: nil, Err: err}
return
}
trueSql := strings.Replace(d.Sql[0], d.TableName, stb.Name, -1)
stmt, err := conn.Prepare(trueSql)
if err != nil {
conn.Close()
resultCh <- &alterResult{Res: nil, Err: err}
return
}
res, err := stmt.Exec(params...)
if err != nil {
conn.Close()
resultCh <- &alterResult{Res: nil, Err: err}
return
}
stmt.Close()
conn.Close()
resultCh <- &alterResult{Res: res, Err: nil}
}(i)
}
result := int64(0)
for i := 0; i < shardTBLen; i++ {
res := <-resultCh
if res.Err != nil {
return nil, nil, res.Err
}
rowAffected, err := res.Res.RowsAffected()
if err != nil {
return nil, nil, err
}
result += rowAffected
}
return result, curTable, nil
}
// to come true ExecCore interface
func (d *InsertDML) Do() (interface{}, error) {
d.Mut.Lock()
defer d.Mut.Unlock()
// get the relation true table.
var err error
var shardTables []*schema.MysqlShardTable
var curTable *schema.MysqlTable
for i := 0; i < len(schema.Tables); i++ {
if schema.Tables[i].Name == d.TableName {
curTable = schema.Tables[i]
shardTables = curTable.Shards
break
}
}
if shardTables == nil {
return nil, errors.New("no found any one this table " + d.TableName)
}
// get the last true table.
tableLen := len(shardTables)
var table *schema.MysqlShardTable
for i := 0; i < tableLen; i++ {
if shardTables[i].RowTotal < uint64(5000000) {
table = shardTables[i]
break
}
}
if table == nil {
// to build new shard table
table, err = curTable.BuildNewShardTable()
if err != nil {
return nil, err
}
}
// parse the sql, to find the auto increment key,
// then change its' value to table global id.
sql, params, err := curTable.ParseMergeInsertGlobalId(d.Sql, table)
if err != nil {
return nil, err
}
master := host.GetBetterHost(table.ShardDB.HostGroup.Master, "master")
conn, err := master.ConnToDB(table.ShardDB.Name)
if err != nil {
conn.Close()
return nil, err
}
stmt, err := conn.Prepare(sql)
if err != nil {
conn.Close()
return nil, err
}
res, err := stmt.Exec(params...)
if err != nil {
conn.Close()
return nil, err
}
stmt.Close()
conn.Close()
curTable.RowTotal++
curTable.UpdateToRedisDB()
return res.LastInsertId()
}
// to come true ExecCore interface
func (d *SelectDML) Do() (interface{}, error) {
d.Mut.Lock()
defer d.Mut.Unlock()
var err error
var shardTables []*schema.MysqlShardTable
var curTable *schema.MysqlTable
for i := 0; i < len(schema.Tables); i++ {
if schema.Tables[i].Name == d.TableName {
curTable = schema.Tables[i]
shardTables = curTable.Shards
break
}
}
if shardTables == nil {
return nil, errors.New("no found any one this table " + d.TableName)
}
// Select expressions
selects, err := analyzeSelectExprs(d.PDML.SelectExprs, curTable)
limit := 0
if d.PDML.Limit != nil {
// offset, err = strconv.Atoi(sqlparser.String(d.PDML.Limit.Offset))
// if err != nil { return nil, err }
limit, err = strconv.Atoi(sqlparser.String(d.PDML.Limit.Rowcount))
if err != nil {
return nil, err
}
}
if err != nil {
return nil, err
}
if selects == nil {
return nil, errors.New("no any found select column")
}
// where
// conditions := analyzeWhere(d.PDML.Where)
params := getInterfaceParams(d.Sql[1:])
resultCh := make(chan *selectResult, runtime.NumCPU())
shardTBLen := len(shardTables)
maxTotal := 5000
// to select the data from all shard tabl at the same time.
for i := 0; i < shardTBLen; i++ {
go func(i int) {
stb := shardTables[i]
conn, err := host.GetBetterHost(stb.ShardDB.HostGroup.Slave, "slave").ConnToDB(stb.ShardDB.Name)
if err != nil {
conn.Close()
resultCh <- &selectResult{Res: nil, Err: err}
}
trueSql := strings.Replace(d.Sql[0], d.TableName, stb.Name, -1)
if limit == 0 {
trueSql += " limit " + strconv.Itoa(maxTotal)
}
stmt, err := conn.Prepare(trueSql)
if err != nil {
conn.Close()
resultCh <- &selectResult{Res: nil, Err: err}
}
rows, err := stmt.Query(params...)
if err != nil {
conn.Close()
resultCh <- &selectResult{Res: nil, Err: err}
}
stmt.Close()
conn.Close()
dataCols, err := rows.Columns()
if err != nil {
resultCh <- &selectResult{Res: nil, Err: err}
}
dataLen := len(dataCols)
rowData := make([]interface{}, dataLen)
valuePtrs := make([]interface{}, dataLen)
backData := []map[string]interface{}{}
curFound := 0
for rows.Next() {
if curFound >= maxTotal {
break
}
oneData := map[string]interface{}{}
for j, _ := range dataCols {
valuePtrs[j] = &rowData[j]
}
err = rows.Scan(valuePtrs...)
if err != nil {
resultCh <- &selectResult{Res: nil, Err: err}
return
}
for j, col := range dataCols {
var v interface{}
val := rowData[j]
b, ok := val.([]byte)
if ok {
v = string(b)
} else {
v = val
}
oneData[col] = v
}
backData = append(backData, oneData)
curFound++
}
err = rows.Err()
rows.Close()
if err != nil {
resultCh <- &selectResult{Res: nil, Err: err}
}
resultCh <- &selectResult{Res: backData, Err: nil}
}(i)
}
result := make([][]map[string]interface{}, shardTBLen)
// get all shard data.
for i := 0; i < shardTBLen; i++ {
res := <-resultCh
if res.Err != nil {
return nil, res.Err
}
result[i] = res.Res
}
// to merge all data from all shard.
mergedResult := []map[string]interface{}{}
if d.hasDescInOrder() { // for desc order data in shard tables
for i := (shardTBLen - 1); i >= 0; i-- {
if i == (shardTBLen - 1) {
mergedResult = result[i]
continue
}
for j := 0; j < len(result[i]); j++ {
mergedResult = append(mergedResult, result[i][j])
if limit != 0 && limit <= len(mergedResult) {
goto LAST
}
}
}
} else { // for asc order data in shard tables
for i := 0; i < shardTBLen; i++ {
if i == 0 {
mergedResult = result[i]
continue
}
for j := 0; j < len(result[i]); j++ {
mergedResult = append(mergedResult, result[i][j])
if limit != 0 && limit <= len(mergedResult) {
goto LAST
}
}
}
}
LAST:
return mergedResult, nil
}