public async Task <IEnumerable <Aggregation> > LoadAggregatedUsageAsync(string dataType, TimeResolution resolution, DateTime startTime, DateTime endTimeExclusive,
CancellationToken cancellation)
{
var aggregations = new List <Aggregation>();
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(LoadAggregatedUsageScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
ctx.CreateParameter("@Resolution", DbType.String, resolution.ToString()),
ctx.CreateParameter("@StartTime", DbType.DateTime2, startTime),
ctx.CreateParameter("@EndTimeExclusive", DbType.DateTime2, endTimeExclusive),
});
}, async (reader, cancel) => {
while (await reader.ReadAsync(cancel))
{
aggregations.Add(GetAggregationFromReader(reader));
}
return(true);
}).ConfigureAwait(false);
}
return(aggregations);
}
public override void Execute(ExecutionContext context)
{
var count = 0;
using (var ctx = new MsSqlDataContext(CancellationToken.None))
{
ctx.ExecuteReaderAsync(Script, async(reader, cancel) =>
{
do
{
if (!reader.HasRows)
{
continue;
}
using (new SystemAccount())
{
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
Operate(reader.GetInt32(0));
count++;
}
}
} while (reader.NextResult());
return(Task.FromResult(0));
}).GetAwaiter().GetResult();
}
Logger.LogMessage(count < 1
? "No content was found with aspect reference field."
: $"Aspect references were updated on {count} content.");
}
public async Task EnumerateDataAsync(string dataType, DateTime startTime, DateTime endTimeExclusive,
Action <IStatisticalDataRecord> aggregatorCallback,
CancellationToken cancellation)
{
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(EnumerateDataScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
ctx.CreateParameter("@StartTime", DbType.DateTime2, startTime),
ctx.CreateParameter("@EndTimeExclusive", DbType.DateTime2, endTimeExclusive),
});
}, async (reader, cancel) =>
{
while (await reader.ReadAsync(cancel))
{
cancel.ThrowIfCancellationRequested();
var item = GetStatisticalDataRecordFromReader(reader);
aggregatorCallback(item);
}
return(true);
}).ConfigureAwait(false);
}
}
public async Task <DateTime?[]> LoadFirstAggregationTimesByResolutionsAsync(string dataType, CancellationToken cancellation)
{
var result = new DateTime?[4];
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(LoadFirstAggregationTimesByResolutionsScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
});
}, async (reader, cancel) => {
if (await reader.ReadAsync(cancel))
{
result[0] = reader.GetDateTimeUtcOrNull("Minute");
result[1] = reader.GetDateTimeUtcOrNull("Hour");
result[2] = reader.GetDateTimeUtcOrNull("Day");
result[3] = reader.GetDateTimeUtcOrNull("Month");
}
return(true);
}).ConfigureAwait(false);
}
return(result.ToArray());
}
/* ===================================================================================== SQL DATA HANDLING */
private static async Task <TimestampData> GetTimestampDataForOneNodeIntegrityCheckAsync(string path, int[] excludedNodeTypeIds)
{
var checkNodeSql = "SELECT N.NodeId, V.VersionId, CONVERT(bigint, n.timestamp) NodeTimestamp, CONVERT(bigint, v.timestamp) VersionTimestamp, N.LastMajorVersionId, N.LastMinorVersionId from Versions V join Nodes N on V.NodeId = N.NodeId WHERE N.Path = '{0}' COLLATE Latin1_General_CI_AS";
if (excludedNodeTypeIds != null && excludedNodeTypeIds.Length > 0)
{
checkNodeSql += $" AND N.NodeTypeId NOT IN ({string.Join(", ", excludedNodeTypeIds)})";
}
var sql = string.Format(checkNodeSql, path);
using var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString,
DataOptions.GetLegacyConfiguration(), CancellationToken.None);
return(await ctx.ExecuteReaderAsync(sql, async (reader, cancel) =>
{
cancel.ThrowIfCancellationRequested();
TimestampData dbNode = null;
if (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
dbNode = new TimestampData
{
NodeId = reader.GetSafeInt32(reader.GetOrdinal("NodeId")),
VersionId = reader.GetSafeInt32(reader.GetOrdinal("VersionId")),
NodeTimestamp = reader.GetSafeInt64(reader.GetOrdinal("NodeTimestamp")),
VersionTimestamp = reader.GetSafeInt64(reader.GetOrdinal("VersionTimestamp")),
LastMajorVersionId = reader.GetSafeInt32(reader.GetOrdinal("LastMajorVersionId")),
LastMinorVersionId = reader.GetSafeInt32(reader.GetOrdinal("LastMinorVersionId")),
};
}
return dbNode;
}).ConfigureAwait(false));
}
internal AssignedTaskResult AssignTasks(int taskCount, int timeoutInMinutes, CancellationToken cancellationToken)
{
var result = new List <int>();
int remainingTasks = 0;
using (var ctx = new MsSqlDataContext(_connectionStrings.Repository, _dataOptions, cancellationToken))
{
ctx.ExecuteReaderAsync(SqlScripts.AssignTasks, cmd =>
{
cmd.Parameters.Add("@AssignedTaskCount", SqlDbType.Int, taskCount);
cmd.Parameters.Add("@TimeOutInMinutes", SqlDbType.Int, timeoutInMinutes);
}, async(reader, cancel) =>
{
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
result.Add(reader.GetInt32(0));
}
await reader.NextResultAsync(cancel).ConfigureAwait(false);
await reader.ReadAsync(cancel).ConfigureAwait(false);
remainingTasks = reader.GetInt32(0);
return(Task.FromResult(0));
}).GetAwaiter().GetResult();
}
return(new AssignedTaskResult {
VersionIds = result.ToArray(), RemainingTaskCount = remainingTasks
});
}
private void ExecuteSql(string script, ExecutionContext context)
{
var connectionInfo = new ConnectionInfo
{
ConnectionName = null,
DataSource = (string)context.ResolveVariable(DataSource),
InitialCatalog = InitialCatalog.Initial,
InitialCatalogName = (string)context.ResolveVariable(InitialCatalogName),
UserName = (string)context.ResolveVariable(UserName),
Password = (string)context.ResolveVariable(Password)
};
var connectionString = MsSqlDataContext.GetConnectionString(connectionInfo) ?? ConnectionStrings.ConnectionString;
//TODO: [DIREF] get options from DI through constructor
using (var ctx = new MsSqlDataContext(connectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None))
{
ctx.ExecuteReaderAsync(script, async(reader, cancel) =>
{
do
{
if (reader.HasRows)
{
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
// empty code block, created only for checking the connection
}
}
} while (reader.NextResult());
return(Task.FromResult(0));
}).GetAwaiter().GetResult();
}
}
private void ExecuteSql(SqlScriptReader sqlReader, ExecutionContext context)
{
var connectionInfo = new ConnectionInfo
{
ConnectionName = (string)context.ResolveVariable(ConnectionName),
DataSource = (string)context.ResolveVariable(DataSource),
InitialCatalog = InitialCatalog,
InitialCatalogName = (string)context.ResolveVariable(InitialCatalogName),
UserName = (string)context.ResolveVariable(UserName),
Password = (string)context.ResolveVariable(Password)
};
var connectionString = MsSqlDataContext.GetConnectionString(connectionInfo, context.ConnectionStrings)
?? context.ConnectionStrings.Repository;
while (sqlReader.ReadScript())
{
var script = sqlReader.Script;
var sb = new StringBuilder();
//TODO: [DIREF] get options from DI through constructor
using (var ctx = new MsSqlDataContext(connectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None))
{
ctx.ExecuteReaderAsync(script, async(reader, cancel) =>
{
do
{
if (reader.HasRows)
{
var first = true;
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
if (first)
{
for (int i = 0; i < reader.FieldCount; i++)
{
sb.Append(reader.GetName(i)).Append("\t");
}
Logger.LogMessage(sb.ToString());
sb.Clear();
first = false;
}
for (int i = 0; i < reader.FieldCount; i++)
{
sb.Append(reader[i]).Append("\t");
}
Logger.LogMessage(sb.ToString());
sb.Clear();
}
}
} while (await reader.NextResultAsync(cancel).ConfigureAwait(false));
return(Task.FromResult(0));
}).GetAwaiter().GetResult();
}
}
Logger.LogMessage("Script is successfully executed.");
}
public List <int> GetAllNodeIds(CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(_connectionStrings.Repository, _dataOptions, cancellationToken))
{
return(ctx.ExecuteReaderAsync(SqlScripts.GetAllNodeIds, (reader, cancel) =>
{
var result = new List <int>();
while (reader.Read())
{
result.Add(reader.GetInt32(0));
}
return Task.FromResult(result);
}).GetAwaiter().GetResult());
}
}
private static async Task <TimestampData[]> GetTimestampDataForRecursiveIntegrityCheckAsync(string path, int[] excludedNodeTypeIds)
{
var typeFilter = excludedNodeTypeIds != null && excludedNodeTypeIds.Length > 0
? $"N.NodeTypeId NOT IN ({string.Join(", ", excludedNodeTypeIds)})"
: null;
string sql;
if (path == null)
{
sql = "SELECT N.NodeId, V.VersionId, CONVERT(bigint, n.timestamp) NodeTimestamp, CONVERT(bigint, v.timestamp) VersionTimestamp, N.LastMajorVersionId, N.LastMinorVersionId from Versions V join Nodes N on V.NodeId = N.NodeId";
if (!string.IsNullOrEmpty(typeFilter))
{
sql += " WHERE " + typeFilter;
}
}
else
{
sql = string.Format("SELECT N.NodeId, V.VersionId, CONVERT(bigint, n.timestamp) NodeTimestamp, CONVERT(bigint, v.timestamp) VersionTimestamp, N.LastMajorVersionId, N.LastMinorVersionId from Versions V join Nodes N on V.NodeId = N.NodeId WHERE (N.Path = '{0}' COLLATE Latin1_General_CI_AS OR N.Path LIKE REPLACE('{0}', '_', '[_]') + '/%' COLLATE Latin1_General_CI_AS)", path);
if (!string.IsNullOrEmpty(typeFilter))
{
sql += " AND " + typeFilter;
}
}
using var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString,
DataOptions.GetLegacyConfiguration(), CancellationToken.None);
return(await ctx.ExecuteReaderAsync(sql, async (reader, cancel) =>
{
cancel.ThrowIfCancellationRequested();
var result = new List <TimestampData>();
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
result.Add(new TimestampData
{
NodeId = reader.GetSafeInt32(reader.GetOrdinal("NodeId")),
VersionId = reader.GetSafeInt32(reader.GetOrdinal("VersionId")),
NodeTimestamp = reader.GetSafeInt64(reader.GetOrdinal("NodeTimestamp")),
VersionTimestamp = reader.GetSafeInt64(reader.GetOrdinal("VersionTimestamp")),
LastMajorVersionId = reader.GetSafeInt32(reader.GetOrdinal("LastMajorVersionId")),
LastMinorVersionId = reader.GetSafeInt32(reader.GetOrdinal("LastMinorVersionId")),
});
}
return result.ToArray();
}).ConfigureAwait(false));
}
public async Task <DateTime?[]> LoadLastAggregationTimesByResolutionsAsync(CancellationToken cancel)
{
var result = new DateTime?[4];
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(LoadLastAggregationTimesByResolutionsScript, async (reader, cancel) => {
if (await reader.ReadAsync(cancel))
{
result[0] = reader.GetDateTimeUtcOrNull("Minute");
result[1] = reader.GetDateTimeUtcOrNull("Hour");
result[2] = reader.GetDateTimeUtcOrNull("Day");
result[3] = reader.GetDateTimeUtcOrNull("Month");
}
return(true);
}).ConfigureAwait(false);
}
return(result.ToArray());
}
protected override async Task <byte[][]> GetRawDataAsync(int fileId)
{
string blobProvider = null;
string blobProviderData = null;
byte[] buffer = null;
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString,
new DataOptions(), CancellationToken.None))
{
var script = "SELECT BlobProvider, BlobProviderData, [Stream] FROM Files WHERE FileId = @FileId";
var _ = await ctx.ExecuteReaderAsync(script, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@FileId", SqlDbType.Int, fileId)
});
}, async (reader, cancel) =>
{
if (await reader.ReadAsync(cancel))
{
blobProvider = reader.GetSafeString("BlobProvider");
blobProviderData = reader.GetSafeString("BlobProviderData");
buffer = reader.GetSafeByteArray("Stream");
}
return(true);
}).ConfigureAwait(false);
}
if (blobProvider == null)
{
if (buffer.Length == 0)
{
return(new byte[0][]);
}
return(new [] { buffer });
}
return(GetRawDataAsync(blobProvider, blobProviderData));
}
public async Task <IEnumerable <IStatisticalDataRecord> > LoadUsageListAsync(string dataType, int[] relatedTargetIds, DateTime endTimeExclusive, int count, CancellationToken cancellation)
{
string sql;
if (relatedTargetIds == null || relatedTargetIds.Length == 0)
{
sql = LoadUsageListScript;
}
else
{
var ids = string.Join(", ", relatedTargetIds.Select(x => x.ToString()));
sql = string.Format(LoadUsageListByTargetIdsScript, ids);
}
var records = new List <IStatisticalDataRecord>();
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(sql, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@Take", DbType.Int32, count),
ctx.CreateParameter("@DataType", DbType.String, dataType),
ctx.CreateParameter("@EndTimeExclusive", DbType.DateTime2, endTimeExclusive),
});
}, async (reader, cancel) =>
{
while (await reader.ReadAsync(cancel))
{
records.Add(GetStatisticalDataRecordFromReader(reader));
}
return(true);
}).ConfigureAwait(false);
}
return(records);
}
private Dictionary <string, string> GetContentPathsWhereTheyAreAllowedChildren(List <string> names)
{
var result = new Dictionary <string, string>();
var whereClausePart = string.Join(Environment.NewLine + " OR" + Environment.NewLine,
names.Select(n =>
$" (t.Value like '{n}' OR t.Value like '% {n} %' OR t.Value like '{n} %' OR t.Value like '% {n}')"));
// testability: the first line is recognizable for the tests.
var sql = $"-- GetContentPathsWhereTheyAreAllowedChildren: [{string.Join(", ", names)}]" +
Environment.NewLine;
sql += @"SELECT n.Path, t.Value FROM LongTextProperties t
JOIN PropertyTypes p ON p.PropertyTypeId = t.PropertyTypeId
JOIN Versions v ON t.VersionId = v.VersionId
JOIN Nodes n ON n.NodeId = v.NodeId
WHERE p.Name = 'AllowedChildTypes' AND (
" + whereClausePart + @"
)
";
using (var ctx = new MsSqlDataContext(CancellationToken.None))
{
var _ = ctx.ExecuteReaderAsync(sql, async(reader, cancel) =>
{
cancel.ThrowIfCancellationRequested();
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
cancel.ThrowIfCancellationRequested();
result.Add(reader.GetString(0), reader.GetString(1));
}
return(Task.FromResult(0));
}).GetAwaiter().GetResult();
}
return(result);
}
