/// <summary>
/// DO NOT USE DIRECTLY THIS METHOD FROM YOUR CODE.
/// Updates the stream in the appropriate row of the Files table specified by the context.
/// </summary>
public void UpdateStream(BlobStorageContext context, Stream stream)
{
// We have to work with an integer since SQL does not support
// binary values bigger than [Int32.MaxValue].
var bufferSize = Convert.ToInt32(stream.Length);
var buffer = new byte[bufferSize];
if (bufferSize > 0)
{
// Read bytes from the source
stream.Seek(0, SeekOrigin.Begin);
stream.Read(buffer, 0, bufferSize);
}
//UNDONE: [DIREF] get connection string through constructor
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, CancellationToken.None))
{
ctx.ExecuteNonQueryAsync(WriteStreamScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@Id", SqlDbType.Int, context.FileId),
ctx.CreateParameter("@Value", SqlDbType.VarBinary, bufferSize, buffer),
});
}).GetAwaiter().GetResult();
}
}
public override async Task <IEnumerable <int> > QueryNodesByTypeAndPathAndPropertyAsync(int[] nodeTypeIds, string pathStart, bool orderByPath,
List <QueryPropertyData> properties, CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(cancellationToken))
{
var typeCount = nodeTypeIds?.Length ?? 0;
var onlyNodes = true;
(bool IsNodeTable, bool IsColumn, string Column, DbType DataType, object Value)[] propertyMapping = null;
public static Task AddStreamAsync(BlobStorageContext context, Stream stream, MsSqlDataContext dataContext)
{
if (stream == null || stream.Length == 0L)
{
return(Task.CompletedTask);
}
return(UpdateStreamAsync(context, stream, dataContext));
}
public byte[] ReadRandom(BlobStorageContext context, long offset, int count)
{
using (var ctx = new MsSqlDataContext(_connectionString, DataOptions, CancellationToken.None))
{
return((byte[])ctx.ExecuteScalarAsync(LoadBinaryFragmentScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@FileId", SqlDbType.Int, context.FileId),
ctx.CreateParameter("@Position", SqlDbType.BigInt, offset + 1),
ctx.CreateParameter("@Count", SqlDbType.Int, count),
});
}).GetAwaiter().GetResult());
}
}
/// <summary>
/// Returns a context object that holds MsSql-specific data for blob storage operations.
/// </summary>
/// <param name="fileId">File identifier.</param>
/// <param name="clearStream">Whether the blob provider should clear the stream during assembling the context.</param>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
/// <param name="cancellationToken">The token to monitor for cancellation requests.</param>
public async Task <BlobStorageContext> GetBlobStorageContextAsync(int fileId, bool clearStream, int versionId, int propertyTypeId,
CancellationToken cancellationToken)
{
var sql = GetBlobStorageContextScript;
if (clearStream)
{
sql = ClearStreamByFileIdScript + sql;
}
cancellationToken.ThrowIfCancellationRequested();
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken))
{
return(await ctx.ExecuteReaderAsync(sql, cmd =>
{
cmd.Parameters.Add(ctx.CreateParameter("@FileId", DbType.Int32, fileId));
if (clearStream)
{
cmd.Parameters.Add(ctx.CreateParameter("@VersionId", DbType.Int32, versionId));
cmd.Parameters.Add(ctx.CreateParameter("@PropertyTypeId", DbType.Int32, propertyTypeId));
}
}, async (reader, cancel) =>
{
cancel.ThrowIfCancellationRequested();
if (!await reader.ReadAsync(cancel).ConfigureAwait(false))
{
return null;
}
var length = reader.GetSafeInt64(0);
var providerName = reader.GetSafeString(1);
var providerData = reader.GetSafeString(2);
var provider = Providers.GetProvider(providerName);
return new BlobStorageContext(provider, providerData)
{
VersionId = versionId,
PropertyTypeId = propertyTypeId,
FileId = fileId,
Length = length,
BlobProviderData = provider is IBuiltInBlobProvider
? new BuiltinBlobProviderData()
: provider.ParseData(providerData)
};
}).ConfigureAwait(false));
}
}
/// <inheritdoc />
public async Task WriteAsync(BlobStorageContext context, long offset, byte[] buffer, CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(_connectionString, DataOptions, cancellationToken))
{
await ctx.ExecuteNonQueryAsync(UpdateStreamWriteChunkScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@FileId", SqlDbType.Int, context.FileId),
ctx.CreateParameter("@VersionId", SqlDbType.Int, context.VersionId),
ctx.CreateParameter("@PropertyTypeId", SqlDbType.Int, context.PropertyTypeId),
ctx.CreateParameter("@Data", SqlDbType.VarBinary, buffer),
ctx.CreateParameter("@Offset", SqlDbType.BigInt, offset),
});
}).ConfigureAwait(false);
}
}
private async Task CleanupFilesSetDeleteFlagAsync(string script, CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken))
{
using (var transaction = ctx.BeginTransaction())
{
try
{
await ctx.ExecuteNonQueryAsync(script).ConfigureAwait(false);
transaction.Commit();
}
catch (Exception e)
{
throw new DataException("Error during setting deleted flag on files.", e);
}
}
}
}
public async Task <bool> CleanupFilesAsync(CancellationToken cancellationToken)
{
using (var dctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken))
{
return(await dctx.ExecuteReaderAsync(CleanupFileScript, async (reader, cancel) =>
{
try
{
var deleted = false;
var fileId = 0;
var size = 0L;
string providerName = null;
string providerData = null;
// We do not care about the number of deleted rows,
// we only want to know if a row was deleted or not.
if (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
deleted = true;
fileId = reader.GetSafeInt32(reader.GetOrdinal("FileId"));
size = reader.GetSafeInt64(reader.GetOrdinal("Size"));
providerName = reader.GetSafeString(reader.GetOrdinal("BlobProvider"));
providerData = reader.GetSafeString(reader.GetOrdinal("BlobProviderData"));
}
// delete bytes from the blob storage
var provider = Providers.GetProvider(providerName);
var ctx = new BlobStorageContext(provider, providerData)
{
VersionId = 0, PropertyTypeId = 0, FileId = fileId, Length = size
};
await ctx.Provider.DeleteAsync(ctx, cancel).ConfigureAwait(false);
return deleted;
}
catch (Exception ex)
{
throw new DataException("Error during binary cleanup.", ex);
}
}).ConfigureAwait(false));
}
}
public async Task CommitChunkAsync(int versionId, int propertyTypeId, int fileId, long fullSize, BinaryDataValue source,
CancellationToken cancellationToken)
{
try
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken))
{
using (var transaction = ctx.BeginTransaction())
{
await ctx.ExecuteNonQueryAsync(CommitChunkScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@FileId", DbType.Int32, fileId),
ctx.CreateParameter("@VersionId", DbType.Int32, versionId),
ctx.CreateParameter("@PropertyTypeId", DbType.Int32, propertyTypeId),
ctx.CreateParameter("@Size", DbType.Int64, fullSize),
ctx.CreateParameter("@Checksum", DbType.AnsiString, 200, DBNull.Value),
ctx.CreateParameter("@ContentType", DbType.String, 50, source != null ? source.ContentType : string.Empty),
ctx.CreateParameter("@FileNameWithoutExtension", DbType.String, 450, source != null
? source.FileName.FileNameWithoutExtension == null
? DBNull.Value
: (object)source.FileName.FileNameWithoutExtension
: DBNull.Value),
ctx.CreateParameter("@Extension", DbType.String, 50,
source != null ? ValidateExtension(source.FileName.Extension) : string.Empty),
});
}).ConfigureAwait(false);
transaction.Commit();
}
}
}
catch (Exception ex)
{
throw new DataException("Error during committing binary chunk to file stream.", ex);
}
}
/* =============================================================================== 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 async Task UpdateStreamAsync(BlobStorageContext context, Stream stream, MsSqlDataContext dataContext)
{
// We have to work with an integer since SQL does not support
// binary values bigger than [Int32.MaxValue].
var bufferSize = Convert.ToInt32(stream.Length);
var buffer = new byte[bufferSize];
if (bufferSize > 0)
{
// Read bytes from the source
stream.Seek(0, SeekOrigin.Begin);
stream.Read(buffer, 0, bufferSize);
}
await dataContext.ExecuteNonQueryAsync(WriteStreamScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
dataContext.CreateParameter("@Id", SqlDbType.Int, context.FileId),
dataContext.CreateParameter("@Value", SqlDbType.VarBinary, bufferSize, buffer),
});
}).ConfigureAwait(false);
}
public async Task <string> StartChunkAsync(IBlobProvider blobProvider, int versionId, int propertyTypeId, long fullSize,
CancellationToken cancellationToken)
{
var ctx = new BlobStorageContext(blobProvider)
{
VersionId = versionId, PropertyTypeId = propertyTypeId, FileId = 0, Length = fullSize
};
string blobProviderName = null;
string blobProviderData = null;
if (!(blobProvider is IBuiltInBlobProvider))
{
await blobProvider.AllocateAsync(ctx, cancellationToken).ConfigureAwait(false);
blobProviderName = blobProvider.GetType().FullName;
blobProviderData = BlobStorageContext.SerializeBlobProviderData(ctx.BlobProviderData);
}
try
{
using (var dctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken))
{
using (var transaction = dctx.BeginTransaction())
{
var result = await dctx.ExecuteReaderAsync(InsertStagingBinaryScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
dctx.CreateParameter("@VersionId", DbType.Int32, versionId),
dctx.CreateParameter("@PropertyTypeId", DbType.Int32, propertyTypeId),
dctx.CreateParameter("@Size", DbType.Int64, fullSize),
dctx.CreateParameter("@BlobProvider", DbType.String, 450, blobProviderName != null ? (object)blobProviderName : DBNull.Value),
dctx.CreateParameter("@BlobProviderData", DbType.String, int.MaxValue, blobProviderData != null ? (object)blobProviderData : DBNull.Value),
});
}, async (reader, cancel) =>
{
int binaryPropertyId;
int fileId;
cancel.ThrowIfCancellationRequested();
if (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
binaryPropertyId = reader.GetSafeInt32(0);
fileId = reader.GetSafeInt32(1);
}
else
{
throw new DataException("File row could not be inserted.");
}
ctx.FileId = fileId;
return(new ChunkToken
{
VersionId = versionId,
PropertyTypeId = propertyTypeId,
BinaryPropertyId = binaryPropertyId,
FileId = fileId
}.GetToken());
}).ConfigureAwait(false);
transaction.Commit();
return(result);
}
}
}
catch (Exception ex)
{
throw new DataException("Error during saving binary chunk to SQL Server.", ex);
}
}
public async Task <BinaryCacheEntity> LoadBinaryCacheEntityAsync(int versionId, int propertyTypeId, CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions, cancellationToken))
return(await LoadBinaryCacheEntityAsync(versionId, propertyTypeId, ctx).ConfigureAwait(false));
}
/* =========================================================================================== Platform specific implementations */
/* =============================================================================================== Nodes */
/* =============================================================================================== NodeHead */
/* =============================================================================================== NodeQuery */
public override async Task <IEnumerable <int> > QueryNodesByTypeAndPathAndNameAsync(int[] nodeTypeIds, string[] pathStart, bool orderByPath, string name,
CancellationToken cancellationToken)
{
var sql = new StringBuilder("SELECT NodeId FROM Nodes WHERE ");
var first = true;
if (pathStart != null && pathStart.Length > 0)
{
for (int i = 0; i < pathStart.Length; i++)
{
if (pathStart[i] != null)
{
pathStart[i] = pathStart[i].Replace("'", "''");
}
}
sql.AppendLine("(");
for (int i = 0; i < pathStart.Length; i++)
{
if (i > 0)
{
sql.AppendLine().Append(" OR ");
}
sql.Append(" Path LIKE N'");
sql.Append(EscapeForLikeOperator(pathStart[i]));
if (!pathStart[i].EndsWith(RepositoryPath.PathSeparator))
{
sql.Append(RepositoryPath.PathSeparator);
}
sql.Append("%' COLLATE Latin1_General_CI_AS");
}
sql.AppendLine(")");
first = false;
}
if (name != null)
{
name = name.Replace("'", "''");
if (!first)
{
sql.Append(" AND");
}
sql.Append(" Name = '").Append(name).Append("'");
first = false;
}
if (nodeTypeIds != null)
{
if (!first)
{
sql.Append(" AND");
}
sql.Append(" NodeTypeId");
if (nodeTypeIds.Length == 1)
{
sql.Append(" = ").Append(nodeTypeIds[0]);
}
else
{
sql.Append(" IN (").Append(string.Join(", ", nodeTypeIds)).Append(")");
}
}
if (orderByPath)
{
sql.AppendLine().Append("ORDER BY Path");
}
cancellationToken.ThrowIfCancellationRequested();
using (var ctx = new MsSqlDataContext(cancellationToken))
{
return(await ctx.ExecuteReaderAsync(sql.ToString(), async (reader, cancel) =>
{
cancel.ThrowIfCancellationRequested();
var result = new List <int>();
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
cancel.ThrowIfCancellationRequested();
result.Add(reader.GetSafeInt32(0));
}
return (IEnumerable <int>)result;
}).ConfigureAwait(false));
}
}
