public List <tb_partition_model> GetClientCreateTime(DbConn conn)
{
try
{
List <tb_partition_model> list = new List <tb_partition_model>();
string sql = @"select partitionid,clientname,tb_consumer_partition.createtime from tb_consumer_partition,[tb_consumer] WITH(NOLOCK)
where [tb_consumer].tempid=tb_consumer_partition.lastconsumertempid";
DataTable dt = conn.SqlToDataTable(sql, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_partition_model model = new tb_partition_model();
model.partitionid = Convert.ToInt32(dr["partitionid"]);
model.createtime = Convert.ToDateTime(dr["createtime"]);
list.Add(model);
}
}
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 删除分区，0:正在使用，1，删除成功；-2：删除失败，-1，数据不存在
/// </summary>
/// <param name="conn"></param>
/// <param name="partitionId"></param>
/// <returns></returns>
public int DeletePartition(DbConn conn, int partitionId)
{
return(SqlHelper.Visit((ps) =>
{
tb_partition_model model = Get(conn, partitionId);
if (model != null)
{
if (model.isused)
{
return 0; //正在使用，不允许删除
}
else
{
if (Delete(conn, partitionId))
{
return 1; //删除成功
}
else
{
return -2; //删除失败
}
}
}
else
{
return -1; //不存在
}
}));
}
public ActionResult Add(int nodeid, int count)
{
try
{
using (DbConn conn = DbConfig.CreateConn(DataConfig.MqManage))
{
conn.Open();
try
{
conn.BeginTransaction();
int r = 0;
var partitons = new tb_partition_dal().GetPageList(conn, "", nodeid, -1, 1, 100, ref r);
List <int> usedpartitionids = new List <int>();
foreach (var d in partitons)
{
if (!usedpartitionids.Contains(d.partitionid))
{
usedpartitionids.Add(d.partitionid);
}
}
List <int> canusepartitionids = new List <int>();
for (var i = 1; i < 100; i++)
{
var partition = XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.GetPartitionID(new XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionIDInfo()
{
DataNodePartition = nodeid, TablePartition = i
});
if (!usedpartitionids.Contains(partition))
{
canusepartitionids.Add(partition);
}
}
for (int i = 0; i < count; i++)
{
tb_partition_model model = new tb_partition_model();
model.isused = false;
model.partitionid = canusepartitionids[i];
dal.AddPartition(conn, model);
}
conn.Commit();
}
catch (Exception exp)
{
conn.Rollback();
throw exp;
}
}
return(RedirectToAction("index", new { nodeId = nodeid }));
}
catch (Exception exp)
{
ModelState.AddModelError("Error", "添加失败" + exp.Message);
return(View());
}
}
/// <summary>
/// 添加分区
/// </summary>
/// <param name="conn"></param>
/// <param name="model"></param>
/// <returns></returns>
public int AddPartition(DbConn conn, tb_partition_model model)
{
return(SqlHelper.Visit((ps) =>
{
tb_partition_model result = Get(conn, model.partitionid);
if (result != null)
{
return 0; //已存在
}
return Add2(conn, model) ? 1 : -1;
}));
}
public virtual bool Add2(DbConn PubConn, tb_partition_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//是否已使用
new ProcedureParameter("@isused", model.isused),
new ProcedureParameter("@partitionid", model.partitionid),
};
int rev = PubConn.ExecuteSql(@"insert into tb_partition(partitionid,isused,createtime)
values(@partitionid,@isused,getdate())" , Par);
return(rev == 1);
}
public virtual bool Add(DbConn PubConn, tb_partition_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//是否已使用
new ProcedureParameter("@isused", model.isused),
//创建时间(以当前库时间为准)
new ProcedureParameter("@createtime", model.createtime)
};
int rev = PubConn.ExecuteSql(@"insert into tb_partition(isused,createtime)
values(@isused,@createtime)" , Par);
return(rev == 1);
}
private void MoveDataToToday(DateTime fromdate, DateTime serverdate, tb_partition_model p, Dictionary <int, tb_datanode_model> datanodemodels)
{
var partitioninfo = XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.GetPartitionIDInfo(p.partitionid);
var fromtablename = XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.GetTableName(partitioninfo.TablePartition, fromdate);
var currenttablename = XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.GetTableName(partitioninfo.TablePartition, serverdate);
//二分法查找起始点
long startmovemqid = StartMoveMQID(fromtablename, partitioninfo, datanodemodels, p, fromdate);
if (startmovemqid <= -1)
{
return;
}
//为了解决可能出现的并发插入，网络极度延迟可能导致的数据顺序乱掉，起始点再往前追溯1000条
startmovemqid = startmovemqid - 1000;
if (startmovemqid <= 0)
{
startmovemqid = 1;
}
//从起始点开始批量扫描
tb_messagequeue_dal dal = new tb_messagequeue_dal(); dal.TableName = fromtablename; var moveMessages = new List <tb_messagequeue_model>();
do
{
SqlHelper.ExcuteSql(this.GetDataNodeConnectString(datanodemodels[partitioninfo.DataNodePartition]), (c) =>
{
moveMessages = dal.GetListMoreThanID(c, 1000, startmovemqid);
});
//对每条数据进行逐条迁移
foreach (var message in moveMessages)
{
startmovemqid = Math.Max(startmovemqid, message.id);//起始id自增
if (message.sqlcreatetime.Date > fromdate.Date && message.state == (byte)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMessageState.CanRead)
{
SqlHelper.ExcuteSql(this.GetDataNodeConnectString(datanodemodels[partitioninfo.DataNodePartition]), (c) =>
{
tb_messagequeue_dal fromdal = new tb_messagequeue_dal(); fromdal.TableName = fromtablename;
tb_messagequeue_dal todal = new tb_messagequeue_dal(); todal.TableName = currenttablename;
todal.AddMove(c, new tb_messagequeue_model()
{
message = message.message,
state = (byte)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMessageState.CanRead,
mqcreatetime = message.mqcreatetime, source = (byte)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMessageSource.Moved
}); //迁移到最新使用表
fromdal.SetState(c, message.id, (byte)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMessageState.Moved); //原消息设置为已迁移
});
}
}
} while (moveMessages.Count > 0);
}
public virtual bool Edit(DbConn PubConn, tb_partition_model model)
{
List <ProcedureParameter> Par = new List <ProcedureParameter>()
{
//是否已使用
new ProcedureParameter("@isused", model.isused),
//创建时间(以当前库时间为准)
new ProcedureParameter("@createtime", model.createtime)
};
Par.Add(new ProcedureParameter("@partitionid", model.partitionid));
int rev = PubConn.ExecuteSql("update tb_partition set isused=@isused,createtime=@createtime where partitionid=@partitionid", Par);
return(rev == 1);
}
/// <summary>
/// 根据node节点获取分区节点
/// </summary>
/// <param name="conn"></param>
/// <param name="nodeId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="count"></param>
/// <returns></returns>
public IList <tb_partition_model> GetPageList(DbConn conn, string partitionid, int nodeId, int used, int pageIndex, int pageSize, ref int count)
{
int tempCount = 0;
IList <tb_partition_model> list = new List <tb_partition_model>();
var result = SqlHelper.Visit((ps) =>
{
StringBuilder where = new StringBuilder(" WHERE 1=1 ");
string sql = "SELECT ROW_NUMBER() OVER(ORDER BY createtime DESC) AS rownum,* FROM tb_partition WITH(NOLOCK)";
if (nodeId > 0)
{
string partitionId = this.GetNodePartition(nodeId);
where.AppendFormat("AND partitionid LIKE '{0}%' ", partitionId);
}
if (used >= 0)
{
where.AppendFormat("AND isused = '{0}' ", used);
}
if (!string.IsNullOrWhiteSpace(partitionid))
{
where.AppendFormat("AND partitionid = '{0}' ", partitionid);
}
string countSql = string.Concat("SELECT COUNT(1) FROM tb_partition WITH(NOLOCK)", where);
object obj = conn.ExecuteScalar(countSql, null);
if (obj != DBNull.Value && obj != null)
{
tempCount = LibConvert.ObjToInt(obj);
}
string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex);
DataTable dt = conn.SqlToDataTable(sqlPage, null);
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
tb_partition_model model = CreateModel(dr);
list.Add(model);
}
}
return(list);
});
count = tempCount;
return(result);
}
public virtual tb_partition_model CreateModel(DataRow dr)
{
var o = new tb_partition_model();
//分区id号,规则1+数据节点编号+表分区编号
if (dr.Table.Columns.Contains("partitionid"))
{
o.partitionid = dr["partitionid"].Toint();
}
//是否已使用
if (dr.Table.Columns.Contains("isused"))
{
o.isused = dr["isused"].Tobool();
}
//创建时间(以当前库时间为准)
if (dr.Table.Columns.Contains("createtime"))
{
o.createtime = dr["createtime"].ToDateTime();
}
return(o);
}
public override void Run()
{
ConfigHelper.LoadConfig(this.AppConfig["BusinessMQManageConnectString"]);
List <tb_datanode_model> nodeList = new List <tb_datanode_model>();
using (DbConn conn = DbConfig.CreateConn(this.AppConfig["BusinessMQManageConnectString"]))
{
conn.Open();
List <tb_partition_model> createTimeList = new tb_consumer_dal().GetClientCreateTime(conn);
var serverdate = conn.GetServerDate().Date; var timenow = conn.GetServerDate();
nodeList = new tb_datanode_dal().List(conn);
List <Exception> exps = new List <Exception>();
foreach (var item in nodeList)
{
try
{
string dataNode = PartitionRuleHelper.GetDataNodeName(item.datanodepartition);
string nodeConn = string.Format("server={0};Initial Catalog={1};User ID={2};Password={3};", item.serverip, dataNode, item.username, item.password);
string partitionId = PartitionRuleHelper.PartitionNameRule(item.datanodepartition);
string t = PartitionRuleHelper.GetDataNodeName(item.datanodepartition);
using (DbConn dataNodeConn = DbConfig.CreateConn(nodeConn))
{
dataNodeConn.Open();
var tablesinfo = new tb_messagequeue_dal().GetDataNodeTable(dataNodeConn, serverdate.AddDays(-30));
foreach (var table in tablesinfo)
{
string tablename = XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.GetTableName(table.TablePartition, table.Day);
TableInfo info = PartitionRuleHelper.GetTableInfo(tablename);
var beginId = XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.GetMQID(new MQIDInfo()
{
AutoID = 0, DataNodePartition = item.datanodepartition, Day = table.Day, TablePartition = table.TablePartition
});
int mqPartitionId = PartitionRuleHelper.GetPartitionID(new PartitionIDInfo()
{
DataNodePartition = item.datanodepartition, TablePartition = table.TablePartition
});
tb_partition_model timeModel = createTimeList.Where(q => q.partitionid == mqPartitionId).FirstOrDefault();
DateTime time = DateTime.Parse("2015-01-01");
if (timeModel != null)
{
time = timeModel.createtime;
}
var dal = new tb_messagequeue_dal(); dal.TableName = tablename;
int count = dal.GetCount(dataNodeConn, time);
long maxId = dal.GetMaxID(dataNodeConn, time); maxId = (maxId == 0 ? beginId : maxId);
long minId = dal.GetMinID(dataNodeConn, time); minId = (minId == 0 ? beginId : minId);
tb_partition_messagequeue_report_model model = new tb_partition_messagequeue_report_model();
model.partitionid = XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.GetPartitionID(new PartitionIDInfo()
{
DataNodePartition = item.datanodepartition, TablePartition = table.TablePartition
});
MQIDInfo mqInfo = PartitionRuleHelper.GetMQIDInfo(maxId);
model.day = mqInfo.Day;
model.lastupdatetime = timenow;
model.createtime = timenow;
model.mqmaxid = maxId;
model.mqminid = minId;
model.mqcount = count;
new tb_partition_messagequeue_report_dal().AddReport(conn, model);
}
}
}
catch (Exception exp)
{
exps.Add(exp);
}
}
Error(this.AppConfig["BusinessMQManageConnectString"], "消息统计出错", exps);
}
}
private long StartMoveMQID(string fromtablename, PartitionIDInfo info, Dictionary <int, tb_datanode_model> datanodemodels, tb_partition_model p, DateTime tabletime)
{
long r = -1;
SqlHelper.ExcuteSql(this.GetDataNodeConnectString(datanodemodels[info.DataNodePartition]), (c) =>
{
bool exsit = c.TableIsExist(fromtablename);
if (exsit == true)
{
tb_messagequeue_dal dal = new tb_messagequeue_dal(); dal.TableName = fromtablename;
long maxid = dal.GetMaxID(c); long minid = dal.GetMinID(c);
int findcount = 0;
while (findcount <= 30)//二分查找算法,超过30次意味着算法若对，查询过一亿的数据，说明算法有问题
{
findcount++;
KeyValuePair <long, DateTime> mininfo = dal.GetIDCreateTime(c, minid); KeyValuePair <long, DateTime> maxinfo = dal.GetIDCreateTime(c, maxid);
if (mininfo.Value.Date <= tabletime.Date && maxinfo.Value.Date > tabletime.Date)//最小id小于等于当天，最大id>当天，说明中间有数据要迁移
{
var midid = (long)((maxinfo.Key + mininfo.Key) / 2); KeyValuePair <long, DateTime> midinfo = dal.GetIDCreateTime(c, midid);
if (midinfo.Value.Date <= tabletime.Date)//往大的查找
{
minid = midid;
}
else//往小的查找
{
maxid = minid;
}
System.Threading.Thread.Sleep(50); //避免给数据库大的压力
}
else if (mininfo.Value.Date > tabletime.Date) //最小id的时间超过表当天
{
r = mininfo.Key; return;
}
else if (maxinfo.Value.Date <= tabletime.Date)//最大id的时间小于或等同当天
{
r = maxinfo.Key; return;
}
}
if (findcount >= 30)
{
throw new Exception("二分查找算法错误，查找次数超过30次");
}
}
});
return(r);
}
