public override DataProvider GetDataProvider()
{
var connOptions = Options.Create(ConnectionStringOptions.GetLegacyConnectionStrings());
return(new MsSqlDataProvider(Options.Create(DataOptions.GetLegacyConfiguration()), connOptions,
new MsSqlDataInstaller(connOptions, NullLoggerFactory.Instance.CreateLogger <MsSqlDataInstaller>())));
}
/* ===================================================================================== 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));
}
public void DC_MSSQL_Construction_ConnectionString()
{
var connectionString = "ConnectionString1";
var dataContext = new MsSqlDataContext(connectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None);
Assert.AreEqual(connectionString, dataContext.ConnectionString);
}
public override void Execute(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);
var initialData = InitialData.Load(new SenseNetServicesInitialData(), null);
var dataOptions = Options.Create(DataOptions.GetLegacyConfiguration());
var connOptions = Options.Create(new ConnectionStringOptions
{
ConnectionString = connectionString
});
var installer = new MsSqlDataInstaller(connOptions, NullLoggerFactory.Instance.CreateLogger <MsSqlDataInstaller>());
var dataProvider = new MsSqlDataProvider(dataOptions, connOptions, installer);
installer.InstallInitialDataAsync(initialData, dataProvider, CancellationToken.None).GetAwaiter().GetResult();
}
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();
}
}
public override IBlobStorageMetaDataProvider GetBlobMetaDataProvider(DataProvider dataProvider)
{
//TODO: get services and options from outside
return(new MsSqlBlobMetaDataProvider(Providers.Instance.BlobProviders,
Options.Create(DataOptions.GetLegacyConfiguration()),
Options.Create(BlobStorageOptions.GetLegacyConfiguration()),
Options.Create(ConnectionStringOptions.GetLegacyConnectionStrings())));
}
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 override DataProvider GetDataProvider(IServiceProvider services)
{
var connectionStringOptions = Options.Create(new ConnectionStringOptions {
Repository = RepositoryConnectionString
});
var dbInstallerOptions = Options.Create(new MsSqlDatabaseInstallationOptions());
return(new MsSqlDataProvider(Options.Create(DataOptions.GetLegacyConfiguration()), connectionStringOptions,
dbInstallerOptions,
new MsSqlDatabaseInstaller(dbInstallerOptions, NullLoggerFactory.Instance.CreateLogger <MsSqlDatabaseInstaller>()),
new MsSqlDataInstaller(connectionStringOptions, NullLoggerFactory.Instance.CreateLogger <MsSqlDataInstaller>()),
NullLoggerFactory.Instance.CreateLogger <MsSqlDataProvider>()));
}
private MsSqlDataProvider CreateDataProvider()
{
var connectionString = GetConnectionString();
var connOptions = Options.Create(new ConnectionStringOptions {
Repository = connectionString
});
var dbInstallerOptions = Options.Create(new MsSqlDatabaseInstallationOptions());
return(new MsSqlDataProvider(Options.Create(DataOptions.GetLegacyConfiguration()), connOptions,
dbInstallerOptions,
new MsSqlDatabaseInstaller(dbInstallerOptions,
NullLoggerFactory.Instance.CreateLogger <MsSqlDatabaseInstaller>()),
new MsSqlDataInstaller(connOptions, NullLoggerFactory.Instance.CreateLogger <MsSqlDataInstaller>()),
NullLoggerFactory.Instance.CreateLogger <MsSqlDataProvider>()));
}
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));
}
internal static bool ExecuteSql(string script, string connectionString)
{
//TODO: [DIREF] get options from DI through constructor
using (var ctx = new MsSqlDataContext(connectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None))
{
try
{
var result = ctx.ExecuteScalarAsync(script).GetAwaiter().GetResult();
if (result == null || Convert.IsDBNull(result))
{
return(false);
}
return(ConvertToBool(result));
}
catch (Exception ex)
{
throw new PackagingException("Error during SQL script execution. " + ex);
}
}
}
public override void Execute(ExecutionContext context)
{
Tracer.Write("Phase-0: Initializing.");
_dataHandler = new ReindexBinariesDataHandler(DataOptions.GetLegacyConfiguration(), context.ConnectionStrings);
_dataHandler.InstallTables(_cancel);
using (var op = Tracer.StartOperation("Phase-1: Reindex metadata."))
{
ReindexMetadata();
op.Successful = true;
}
using (var op = Tracer.StartOperation("Phase-2: Create background tasks."))
{
_dataHandler.StartBackgroundTasks(_cancel);
op.Successful = true;
}
// commit all buffered lines
SnTrace.Flush();
}
public override void Execute(ExecutionContext context)
{
var count = 0;
//TODO: [DIREF] get options from DI through constructor
using (var ctx = new MsSqlDataContext(context.ConnectionStrings.Repository,
DataOptions.GetLegacyConfiguration(),
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.");
}
private bool ExecuteSql(string script, ExecutionContext context)
{
var connectionInfo = new ConnectionInfo
{
DataSource = (string)context.ResolveVariable(DataSource),
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))
{
object result;
try
{
result = ctx.ExecuteScalarAsync(script).GetAwaiter().GetResult();
}
catch (Exception ex)
{
throw new PackagingException("Error during SQL script execution. " + ex);
}
if (result == null || Convert.IsDBNull(result))
{
return(false);
}
if (result is bool @bool)
{
return(@bool);
}
if (result is byte @bybte)
{
return(@bybte > 0);
}
if (result is decimal @decimal)
{
return(@decimal > 0);
}
if (result is double @double)
{
return(@double > 0);
}
if (result is float @float)
{
return(@float > 0);
}
if (result is int @int)
{
return(@int > 0);
}
if (result is long @long)
{
return(@long > 0);
}
if (result is sbyte @sbyte)
{
return(@sbyte > 0);
}
if (result is short @short)
{
return(@short > 0);
}
if (result is uint @uint)
{
return(@uint > 0);
}
if (result is ulong @ulong)
{
return(@ulong > 0);
}
if (result is ushort @ushort)
{
return(@ushort > 0);
}
if (result is string @string)
{
return(!string.IsNullOrEmpty(@string));
}
}
return(false);
}
public MsSqlCannotCommitDataProvider(string connectionString) : base(Options.Create(DataOptions.GetLegacyConfiguration()),
Options.Create(new ConnectionStringOptions
{
ConnectionString = connectionString
}), new MsSqlDataInstaller(Options.Create(new ConnectionStringOptions
{
ConnectionString = connectionString
}), NullLoggerFactory.Instance.CreateLogger <MsSqlDataInstaller>()))
{
_connectionString = connectionString;
}
private void InitializePackagingTest(RepositoryBuilder builder)
{
_log = new StringBuilder();
var loggers = new[] { new PackagingTestLogger(_log) };
var loggerAcc = new TypeAccessor(typeof(SenseNet.Packaging.Logger));
loggerAcc.SetStaticField("_loggers", loggers);
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None))
{
DropPackagesTable(ctx);
InstallPackagesTable(ctx);
}
RepositoryVersionInfo.Reset();
}
public static bool CheckFeature(CancellationToken cancellationToken)
{
try
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), cancellationToken))
{
var result = ctx.ExecuteScalarAsync(SqlScripts.CheckFeature).GetAwaiter().GetResult();
return(Convert.ToInt32(result) != 0);
}
}
catch (AggregateException ae)
{
if (ae.InnerException is NotSupportedException)
{
return(false);
}
throw;
}
catch (NotSupportedException)
{
return(false);
}
}
private void ResetDatabase(RepositoryBuilder builder)
{
using (var ctx = new MsSqlDataContext(Platform.RepositoryConnectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None))
{
ctx.ExecuteNonQueryAsync(MsSqlStatisticalDataProvider.DropScript).GetAwaiter().GetResult();
ctx.ExecuteNonQueryAsync(MsSqlStatisticalDataProvider.CreationScript).GetAwaiter().GetResult();
}
}
public MsSqlStatisticalDataProvider()
{
DataOptions = DataOptions.GetLegacyConfiguration();
ConnectionString = ConnectionStrings.ConnectionString;
}
internal static void StartBackgroundTasks(CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), cancellationToken))
ctx.ExecuteNonQueryAsync(SqlScripts.CreateTasks).GetAwaiter().GetResult();
}
public static DateTime LoadTimeLimit(CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), cancellationToken))
{
var result = ctx.ExecuteScalarAsync(SqlScripts.SelectTimeLimit).GetAwaiter().GetResult();
var timeLimit = Convert.ToDateTime(result).ToUniversalTime();
Tracer.Write("UTC timelimit: " + timeLimit.ToString("yyyy-MM-dd HH:mm:ss"));
return(timeLimit);
}
}
internal static AssignedTaskResult AssignTasks(int taskCount, int timeoutInMinutes, CancellationToken cancellationToken)
{
var result = new List <int>();
int remainingTasks = 0;
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), 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
});
}
/* ========================================================================================= */
public static void CreateTempTask(int versionId, int rank, CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), cancellationToken))
ctx.ExecuteNonQueryAsync(SqlScripts.FinishTask, cmd =>
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int, versionId);
cmd.Parameters.Add("@Rank", SqlDbType.Int, rank);
}).GetAwaiter().GetResult();
}
public static List <int> GetAllNodeIds(CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), 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());
}
}
/* =============================================================================== Methods for Steps */
public 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 + @"
)
";
//TODO: [DIREF] get options from DI through constructor
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), 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);
}
public static void DropTables(CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), cancellationToken))
ctx.ExecuteNonQueryAsync(SqlScripts.DropTables).GetAwaiter().GetResult();
}
