private string CreateTestArchive(Guid sessionId)
{
var tmpName = Path.GetTempFileName();
var timeData = new[] { 1L, 2L, 3L };
var timeColumn = new DataColumn(
new DataField <long>("time"),
timeData);
var numbersData = new[] { 42d, 1337d, 6.022e23 };
var numbersColumn = new DataColumn(
new DataField <double>("cool_numbers"),
numbersData);
var schema = new Schema(timeColumn.Field, numbersColumn.Field);
var json = new JObject {
["meta"] = new JObject(), ["user"] = new JObject()
};
using (var ms = new MemoryStream())
{
using (var parquetWriter = new ParquetWriter(schema, ms))
using (var groupWriter = parquetWriter.CreateRowGroup())
{
groupWriter.WriteColumn(timeColumn);
groupWriter.WriteColumn(numbersColumn);
}
ms.Position = 0;
using (var parquetReader = new ParquetReader(ms))
{
var tableInformation = new ArchiveTableInformation()
{
Columns = new List <DataField>(parquetReader.Schema.GetDataFields()),
Time = timeColumn.Field
};
var table = new ArchiveTable(json, parquetReader, tableInformation, "testData");
var archive = Archive.Create(tmpName);
var session = ArchiveSession.Create(archive, "testName");
var folder = ArchiveFolder.Create(archive, sessionId, "testFolder");
folder.AddChild(table);
session.AddChild(folder);
archive.AddSession(session);
archive.WriteFile().Wait();
archive.Close();
}
}
return(tmpName);
}
private void DataArchiveYear(ArchiveTable table, string archiveTableName, int year)
{
string bllTableName = table.TableName;
try
{
using (MySqlConnection conn = new MySqlConnection(EnvironmentInfo.ConnectionString))
{
conn.Open();
MySqlTransaction transaction = conn.BeginTransaction();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = int.MaxValue;//超时时间设置60分钟
cmd.Transaction = transaction;
try
{
string archiveStartDate = $"{year}-01-01";
string archiveEndDate = $"{year + 1}-01-01";
string timeField = GetTimeField(bllTableName);
string sql = $"insert into `{archiveTableName}` select * from `{bllTableName}` where {timeField} < '{archiveEndDate}' and {timeField} >'{archiveStartDate}'";
if (!string.IsNullOrEmpty(table.Where))
{
sql += $" {table.Where}";
}
cmd.CommandText = sql;
LogHelper.CommLogger.Info(sql);
int x = cmd.ExecuteNonQuery();
string script = $"delete from `{bllTableName}` where {timeField} < '{archiveEndDate}' and {timeField} >'{archiveStartDate}'";
if (!string.IsNullOrEmpty(table.Where))
{
sql += $" {table.Where}";
}
cmd.CommandText = script;
LogHelper.CommLogger.Info(script);
int y = cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
LogHelper.CommLogger.Error("数据归档遇到些问题，事务回滚：" + ex.ToString());
}
}
//progress += 20;
//DataArchivingViewModel.Instance().ShowMessage($"表{bllTableName}归档完成...请继续等待...", progress);
}
catch (Exception ex)
{
LogHelper.CommLogger.Error("数据归档遇到些问题：" + ex.ToString());
}
}
/// <summary>
/// 开始归档
/// </summary>
private void DataArchive(ArchiveTable table, string archiveTableName)
{
string bllTableName = table.TableName;
try
{
using (MySqlConnection conn = new MySqlConnection(EnvironmentInfo.ConnectionString))
{
conn.Open();
MySqlTransaction transaction = conn.BeginTransaction();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = int.MaxValue;//超时时间设置60分钟
cmd.Transaction = transaction;
try
{
DateTime archiveDate = DateTime.Now.Date.AddMonths(-EnvironmentInfo.AutoArchiveMonth);
string sql = $"insert into `{archiveTableName}` select * from `{bllTableName}` where {GetTimeField(bllTableName)} < '{archiveDate.ToString("yyyy-MM-dd HH:mm:ss")}'";
if (!string.IsNullOrEmpty(table.Where))
{
sql += $" {table.Where}";
}
cmd.CommandText = sql;
LogHelper.CommLogger.Info(sql);
int x = cmd.ExecuteNonQuery();
string script = $"delete from `{bllTableName}` where {GetTimeField(bllTableName)} < '{archiveDate.ToString("yyyy-MM-dd HH:mm:ss")}'";
if (!string.IsNullOrEmpty(table.Where))
{
sql += $" {table.Where}";
}
cmd.CommandText = script;
LogHelper.CommLogger.Info(script);
int y = cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
LogHelper.CommLogger.Error("数据归档遇到些问题，事务回滚：" + ex.ToString());
}
}
progress += 20;
DataArchivingViewModel.Instance().ShowMessage($"表{bllTableName}归档完成...请继续等待...", progress);
}
catch (Exception ex)
{
LogHelper.CommLogger.Error("数据归档遇到些问题：" + ex.ToString());
}
}
/// <summary>
/// Finds an archive bit by the specified archive code.
/// </summary>
public int FindArchiveBit(string archiveCode, int defaultArchiveBit)
{
if (string.IsNullOrEmpty(archiveCode))
{
return(defaultArchiveBit);
}
else if (ArchiveTable.SelectFirst(new TableFilter("Code", archiveCode)) is Archive archive)
{
return(archive.Bit);
}
else
{
return(ArchiveBit.Unknown);
}
}
public List <ArchiveTable> ArchiveTables()
{
string error = "";
DataTable dataTable = EnvironmentInfo.SqliteHelper.GetDataTable(out error, "select * from sys_archive_table;");
List <ArchiveTable> archiveTables = new List <ArchiveTable>();
foreach (DataRow dr in dataTable.Rows)
{
ArchiveTable table = new ArchiveTable();
table.Id = int.Parse(dr["Id"].ToString());
table.TableName = dr["TableName"].ToString();
table.DateField = dr["DateField"].ToString();
table.Where = dr["Where"].ToString();
archiveTables.Add(table);
}
return(archiveTables);
}
public void SaveSingleDataArchive()
{
Guid sessionId = Guid.NewGuid(); // Todo fake sessionId
var tmpName = Path.GetTempFileName();
var timeData = new[] { 1L, 2L, 3L };
var timeColumn = new DataColumn(
new DataField <long>("time"),
timeData);
var numbersData = new[] { 42d, 1337d, 6.022e23 };
var numbersColumn = new DataColumn(
new DataField <double>("cool_numbers"),
numbersData);
var schema = new Schema(timeColumn.Field, numbersColumn.Field);
var json = new JObject {
["meta"] = new JObject(), ["user"] = new JObject()
};
using (var ms = new MemoryStream())
{
using (var parquetWriter = new ParquetWriter(schema, ms))
using (var groupWriter = parquetWriter.CreateRowGroup())
{
groupWriter.WriteColumn(timeColumn);
groupWriter.WriteColumn(numbersColumn);
}
ms.Position = 0;
using (var parquetReader = new ParquetReader(ms)) {
var tableInformation = new ArchiveTableInformation()
{
Columns = new List <DataField>(parquetReader.Schema.GetDataFields()),
Time = timeColumn.Field
};
var table = new ArchiveTable(json, parquetReader, tableInformation, "testData");
var archive = Archive.Create(tmpName);
var session = ArchiveSession.Create(archive, "testName");
var folder = ArchiveFolder.Create(archive, sessionId, "testFolder");
folder.AddChild(table);
session.AddChild(folder);
archive.AddSession(session);
try
{
archive.WriteFile().Wait();
}
catch (Exception)
{
Assert.True(false);
}
archive.Close();
using (var fr = new FileReader(tmpName))
{
var openTask = Archive.Open(fr);
openTask.Wait();
var newArchive = openTask.Result;
AssertArchivesEqual(archive, newArchive);
Assert.Equal("testName", session.Name);
Assert.Single(newArchive.Sessions.First().Children);
var readFolder = newArchive.Sessions.First().Children.First();
Assert.Equal("testFolder", readFolder.Name);
Assert.Single(readFolder.Children);
var child = readFolder.Children.First();
Assert.Single(child.DataPoints);
Assert.IsAssignableFrom <ArchiveTable>(child);
var tableChild = (ArchiveTable)child;
var dataPoint = tableChild.DataPoints.First();
var context = new TimeSynchronizedContext();
context.AvailableTimeRangeChanged +=
(sender, from, to) => context.SetSelectedTimeRange(from, to);
var viewer = context.GetDataViewerFor(dataPoint);
viewer.Wait();
var dataViewer = viewer.Result;
Assert.IsAssignableFrom <ITimeSeriesViewer>(dataViewer);
var timeViewer = (ITimeSeriesViewer)dataViewer;
var data = timeViewer.GetCurrentData <double>();
Assert.Equal("cool_numbers", dataViewer.DataPoint.Name);
Assert.Equal(timeData, data.X.ToArray());
Assert.Equal(numbersData, data.Y.ToArray());
newArchive.Close();
}
}
}
File.Delete(tmpName);
}
public void AddArchiveTable(ArchiveTable archiveTable)
{
EnvironmentInfo.SqliteHelper.ExecuteSql($"insert into sys_archive_table (TableName,DateField,Where) values ('{archiveTable.TableName}','{archiveTable.DateField}','{archiveTable.Where}')");
}
public void RemoveArchiveTable(ArchiveTable archiveTable)
{
EnvironmentInfo.SqliteHelper.ExecuteSql($"delete from sys_archive_table where tablename='{archiveTable.TableName}'");
}
public void Handle(ArchiveTable c)
{
_repository.Perform(c.Id, game => game.Archive());
}
