public static void Subscriber()
{
var sub = _conn.GetSubscriber();
sub.Subscribe("LogSql",
(channel, message) =>
{
try
{
var info = JsonConvert.DeserializeObject <SqlLog>(message);
TORM.Insert(info);
}
catch (Exception e)
{
_conn.GetDatabase().ListRightPush("Error:Subscriber:LogSql:" + DateTime.Today.ToShortDateString(), e.ToString());
}
});
sub.Subscribe("ExplainErrorLog",
(channel, message) =>
{
try
{
var info = JsonConvert.DeserializeObject <ExplainErrorLog>(message);
TORM.Insert(info);
}
catch (Exception e)
{
_conn.GetDatabase().ListRightPush("Error:Subscriber:ExplainErrorLog:" + DateTime.Today.ToShortDateString(), e.ToString());
}
});
}
public JsonResult GetAllData(DateTime start, DateTime end)
{
var particle = (end - start).TotalSeconds / _total; // 确定粒度
var date = new List <string>
{
$"{start.ToString(_format)}"
};
var sql = new StringBuilder();
for (var i = 0; i < _total; i++)
{
var e = start.AddSeconds(particle * (i + 1));
if (i != 0)
{
sql.Append("\r\n");
}
sql.Append($"SELECT COUNT(1) as Value FROM SqlLog WHERE EndTime > '{start.AddSeconds(particle * i):yyyy-MM-dd HH:mm:ss}' AND EndTime < '{e:yyyy-MM-dd HH:mm:ss}'");
if (i != _total - 1)
{
sql.Append(" UNION ALL");
}
date.Add($"{e.ToString(_format)}");
}
var data = TORM.Query <LongData>(sql.ToString()).Select(x => x.Value);
return(Json(new { data, date, particle = particle.ToString("0") }));
}
public void CountTest()
{
var result = TORM.Query <Rules>()
.Where(x => x.Id > 0)
.Count();
Debug.Assert(result > 3);
}
public void FindTest()
{
var result = TORM.Query <Rules>()
.Where(x => x.Id > 0 + 1)
.Find();
Debug.Assert(result.Any());
}
public void FindTopTest()
{
var result = TORM.Query <Rules>()
.Where(x => x.Id > 0)
.Limit(2);
Debug.Assert(result.Count() == 2);
}
public void UpdateTopTest()
{
var result = TORM.Update <Rules>()
.Set(x => x.DeletedAt, DateTime.Now)
.Where(x => x.Id > 1)
.Update(3);
Debug.Assert(result > 0 && result < 4);
}
public void UpdateAllTest()
{
var result = TORM.Update <Rules>()
.Set(x => x.CreatedAt, DateTime.Now)
.Where(x => x.Id > 1)
.Update();
Debug.Assert(result > 3);
}
public void GroupTest()
{
var result = TORM.Query <Rules>()
.Select(x => x.ScheduleId)
.Group(x => x.ScheduleId)
.Find();
Debug.Assert(result.Any());
}
public void JoinAndTest()
{
var result1 = TORM.Query <Rules, Schedules>()
.Select(((r, s) => r.Id, "RId"), ((r, s) => s.Id, "SId"))
.JoinL((r, s) => r.ScheduleId == s.Id && r.Id > 1)
.Find <View>();
Debug.Assert(result1.Any());
}
public void InsertOneTest()
{
var result = TORM.Insert(new Rules
{
CreatedAt = DateTime.Now.AddDays(-3)
});
Debug.Assert(result == 1);
}
public void InsertTest2()
{
var result = TORM.Insert(new City
{
Name = "123",
ProvinceId = 5
});
Debug.Assert(result == 1);
}
public void DeleteWhereTest()
{
var id = TORM.Query<Rules>()
.Select(x => x.Id)
.First<long>();
var result = TORM.Delete<Rules>()
.Where(x => x.Id == id)
.Delete();
Debug.Assert(result == 1);
}
public void DeleteByIdTest()
{
var id = TORM.Query<Rules>()
.Select(x => x.Id)
.Where(x => x.Id > 10)
.OrderD(x => x.Id)
.First<long>();
var result = TORM.Delete<Rules, long>(id);
Debug.Assert(result == 1);
}
public void PageTest()
{
var(data, total) = TORM.Query <Rules>()
.Select(x => x.Id, x => x.CreatedAt, x => x.DeletedAt)
.Where(x => x.Id > 0 && x.ScheduleId >= 0 && x.Type >= 0)
.OrderA(x => x.Id)
.OrderD(x => x.CreatedAt)
.Page(1, 3);
Debug.Assert(data.Any() && total > 0);
}
public void UpdateModelTest()
{
var result = TORM.Update(new Rules
{
Id = 2,
DeletedAt = DateTime.Today,
CreatedAt = DateTime.Today,
RuleDate = DateTime.Today,
ScheduleId = 999,
Type = 888,
UpdatedAt = DateTime.Now
});
Debug.Assert(result == 1);
}
public void InsertBatchTest()
{
var result = TORM.InsertBatch(new[]
{
new Rules
{
CreatedAt = DateTime.Now.AddDays(-3)
},
new Rules
{
CreatedAt = DateTime.Now.AddDays(-2)
},
new Rules
{
CreatedAt = DateTime.Now.AddDays(-1)
}
});
Debug.Assert(result == 3);
}
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.Configure <CookiePolicyOptions>(options =>
{
// This lambda determines whether user consent for non-essential cookies is needed for a given request.
options.CheckConsentNeeded = context => true;
options.MinimumSameSitePolicy = SameSiteMode.None;
});
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
// 配置
TORM.Options(options =>
{
options.DbConfig.Add("Log", "server=118.24.27.231;database=Log;uid=root;pwd=suncheng1994;");
});
TORM.AutoTable <SqlLog>();
TORM.AutoTable <ExplainErrorLog>();
// 在消费redis
Redis.Subscriber();
}
public BaseTest()
{
TORM.Options(options =>
{
options.Debug = true; // 调试模式
var redis = ConnectionMultiplexer.Connect("118.24.27.231:6379,password=suncheng1994");
options.RedisLog = redis; // 使用redis推送sql记录
redis.ErrorMessage += Redis_ErrorMessage;
redis.ConnectionFailed += Redis_ConnectionFailed;
// 配置数据库连接
options.DbConfig.Add("Test", "server=118.24.27.231;database=Test;uid=root;pwd=suncheng1994;");
options.DbConfig.Add("Log", "server=118.24.27.231;database=Log;uid=root;pwd=suncheng1994;");
});
//TORM.AutoTable<Rules>();
//TORM.AutoTable<Schedules>();
//TORM.AutoTable<Province>();
//TORM.AutoTable<City>();
//TORM.AutoTable<Town>();
}
public JsonResult GetTotalSpanData(DateTime start, DateTime end)
{
var date = new[] { "<0.1ms", "0.1ms~1ms", "1ms~5ms", "1ms~5ms", "5ms~20ms", "20ms~50ms", "50ms~100ms", "100ms~200ms", "200ms~500ms", "500ms~1000ms", "1s~5s", ">5s" };
var dataType = TORM.Query <SqlLog>()
.Select(x => x.DbName)
.Group(x => x.DbName)
.Find <string>();
var sql = new StringBuilder();
foreach (var item in dataType)
{
for (int i = 0; i < date.Length; i++)
{
if (i != 0)
{
sql.Append("\r\n");
}
sql.Append($"");
if (i != _total - 1)
{
sql.Append(" UNION ALL");
}
//if (date.Count < _total)
// date.Add($"{e.ToString(_format)}");
}
}
var result = Get <DecimalData, decimal>(
start,
end,
"SELECT AVG(ExplainSpan) as Value FROM SqlLog WHERE DbName = '{0}' AND EndTime > '{1}' AND EndTime < '{2}'");
return(Json(result));
}
private dynamic Get <T, T2>(DateTime start, DateTime end, string sqlStr) where T : BaseData <T2>
{
var particle = (end - start).TotalSeconds / _total; // 确定粒度
var date = new List <string>
{
$"{start.ToString(_format)}"
};
var dataType = TORM.Query <SqlLog>()
.Select(x => x.DbName)
.Group(x => x.DbName)
.Find <string>();
var data = new ArrayList();
foreach (var item in dataType)
{
var sql = new StringBuilder();
for (var i = 0; i < _total; i++)
{
var s = start.AddSeconds(particle * i);
var e = start.AddSeconds(particle * (i + 1));
if (i != 0)
{
sql.Append("\r\n");
}
sql.AppendFormat(sqlStr, item, $"{s:yyyy-MM-dd HH:mm:ss}", $"{e:yyyy-MM-dd HH:mm:ss}");
if (i != _total - 1)
{
sql.Append(" UNION ALL");
}
if (date.Count < _total)
{
date.Add($"{e.ToString(_format)}");
}
}
data.Add(new
{
name = item,
type = "line",
smooth = true,
symbol = "none",
sampling = "average",
itemStyle = new
{
color = _color[dataType.IndexOf(item)]
},
data = TORM.Query <T>(sql.ToString()).Select(x => x.Value)
});
}
var t = end.Ticks - start.Ticks;
var t2 = DateTime.Now.Ticks - start.Ticks;
//var t3 = t2 / (t / 100);
var scope = new[] { 100 - 16, 100 };
return(new
{
dataType,
data,
date,
scope,
particle
});
}