/// <summary>
/// Marks orphaned file records (the ones that do not have a referencing binary record anymore) as Deleted.
/// </summary>
public void CleanupFilesSetDeleteFlag()
{
var isLocalTransaction = false;
if (!TransactionScope.IsActive)
{
TransactionScope.Begin();
isLocalTransaction = true;
}
try
{
using (var proc = new SqlProcedure {
CommandText = CleanupFileSetIsdeletedScript, CommandType = CommandType.Text
})
{
proc.CommandType = CommandType.Text;
proc.ExecuteNonQuery();
}
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Commit();
}
}
catch (Exception ex)
{
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Rollback();
}
throw new DataException("Error during setting deleted flag on files.", ex);
}
}
private void ExecuteScripts(List<StringBuilder> scripts)
{
using (var traceOperation = Logger.TraceOperation("Execute storage schema scripts."))
{
// Ensure transaction encapsulation
bool isLocalTransaction = !TransactionScope.IsActive;
if (isLocalTransaction)
TransactionScope.Begin();
try
{
if (_slotsToReset.Count > 0)
{
StringBuilder sb = new StringBuilder();
scripts.Insert(0, sb);
WriteSlotsToResetScripts(sb, _slotsToReset);
}
foreach (StringBuilder script in scripts)
{
using (var proc = new SqlProcedure { CommandText = script.ToString(), CommandType = CommandType.Text })
{
proc.ExecuteNonQuery();
}
}
if (isLocalTransaction)
TransactionScope.Commit();
traceOperation.IsSuccessful = true;
}
finally
{
if (isLocalTransaction && TransactionScope.IsActive)
TransactionScope.Rollback();
}
}
}
/// <summary>
/// Finalizes a chunked save operation.
/// </summary>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
/// <param name="fileId">File identifier.</param>
/// <param name="fullSize">Full size (stream length) of the binary value.</param>
/// <param name="source">Binary data containing metadata (e.g. content type).</param>
public void CommitChunk(int versionId, int propertyTypeId, int fileId, long fullSize, BinaryDataValue source = null)
{
// start a new transaction here if needed
var isLocalTransaction = !TransactionScope.IsActive;
if (isLocalTransaction)
{
TransactionScope.Begin();
}
try
{
// commit the process: set the final full size and checksum
using (var cmd = new SqlProcedure {
CommandText = CommitChunkScript, CommandType = CommandType.Text
})
{
cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = fileId;
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = fullSize;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = DBNull.Value;
cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 50).Value = source != null ? source.ContentType : string.Empty;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = source != null
? source.FileName.FileNameWithoutExtension == null
? DBNull.Value
: (object)source.FileName.FileNameWithoutExtension
: DBNull.Value;
cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = source != null?ValidateExtension(source.FileName.Extension) : string.Empty;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
// rollback the transaction if it was opened locally
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Rollback();
}
throw new DataException("Error during committing binary chunk to file stream.", ex);
}
finally
{
// commit the transaction if it was opened locally
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Commit();
}
}
}
/// <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 static void UpdateStream(BlobStorageContext context, Stream stream)
{
var fileId = context.FileId;
SqlProcedure cmd = null;
try
{
// We have to work with an integer since SQL does not support
// binary values bigger than [Int32.MaxValue].
var streamSize = Convert.ToInt32(stream.Length);
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_WriteStream"
};
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = fileId;
var offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int);
var valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize);
var offset = 0;
byte[] buffer = null;
stream.Seek(0, SeekOrigin.Begin);
// The 'while' loop is misleading here, because we write the whole
// stream at once. Bigger files should go to another blob provider.
while (offset < streamSize)
{
// Buffer size may be less at the end os the stream than the limit
var bufferSize = streamSize - offset;
if (buffer == null || buffer.Length != bufferSize)
{
buffer = new byte[bufferSize];
}
// Read bytes from the source
stream.Read(buffer, 0, bufferSize);
offsetParameter.Value = offset;
valueParameter.Value = buffer;
// Write full stream
cmd.ExecuteNonQuery();
offset += bufferSize;
}
}
finally
{
cmd?.Dispose();
}
}
public void SaveReferenceProperty(int versionId, PropertyType propertyType, IEnumerable <int> value)
{
if (propertyType == null)
{
throw new ArgumentNullException("propertyType");
}
for (short tryAgain = 3; tryAgain > 0; tryAgain--)
{
// Optimistic approach: try to save the value as is, without checking and compensate if it fails
try
{
// Create XML
var referredListXml = SqlProvider.CreateIdXmlForReferencePropertyUpdate(value);
// Execute SQL
using (var cmd = new SqlProcedure {
CommandText = "proc_ReferenceProperty_Update"
})
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.Parameters.Add("@ReferredNodeIdListXml", SqlDbType.Xml).Value = referredListXml;
cmd.ExecuteNonQuery();
// Success, don't try again
tryAgain = 0;
}
}
catch (SqlException exc)
{
// This was the last try and it failed, throw
if (tryAgain == 1)
{
throw;
}
// The value contains a node ID which no longer exists in the database, let's compensate for that
if (exc.Message.Contains("The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_ReferenceProperties_Nodes\"."))
{
// Get node heads for the IDs
var heads = DataProvider.Current.LoadNodeHeads(value);
// Select the IDs of the existing node heads
value = heads.Where(h => h != null).Select(h => h.Id);
}
else
{
// If the error is something else, just throw it up
throw;
}
}
}
}
private void ExecuteScript(string script, int versionId, int page, IEnumerable <SqlParameter> @params)
{
using (var cmd = new SqlProcedure {
CommandText = script
})
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@Page", SqlDbType.Int).Value = page;
cmd.Parameters.AddRange(@params.ToArray());
cmd.ExecuteNonQuery();
}
}
private static void WriteBinaryStream(Stream stream, int binaryPropertyId)
{
SqlProcedure cmd = null;
try
{
int streamSize = Convert.ToInt32(stream.Length);
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_WriteStream"
};
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId;
SqlParameter offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int);
SqlParameter valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize);
int offset = 0;
byte[] buffer = null;
stream.Seek(0, SeekOrigin.Begin);
while (offset < streamSize)
{
// Buffer size may be less at the end os the stream than the limit
int bufferSize = streamSize - offset;
//partial update is not supported on FILESTREAM columns
//if(bufferSize > SqlProvider.BinaryStreamBufferLength)
// bufferSize = SqlProvider.BinaryStreamBufferLength;
if (buffer == null || buffer.Length != bufferSize)
{
buffer = new byte[bufferSize];
}
// Write buffered stream segment
stream.Read(buffer, 0, bufferSize);
offsetParameter.Value = offset;
valueParameter.Value = buffer;
cmd.ExecuteNonQuery();
offset += bufferSize;
}
}
finally
{
cmd.Dispose();
}
}
/// <summary>
/// Deletes a binary property value from the metadata database, making the corresponding blbo storage entry orphaned.
/// </summary>
/// <param name="versionId">Content version id.</param>
/// <param name="propertyTypeId">Binary property type id.</param>
public void DeleteBinaryProperty(int versionId, int propertyTypeId)
{
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_Delete"
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
cmd.ExecuteNonQuery();
}
finally
{
cmd?.Dispose();
}
}
public void UpdateBinaryProperty(int binaryDataId, BinaryDataValue value)
{
if (value.Stream != null && value.Stream.Length > Int32.MaxValue)
{
throw new NotSupportedException(); // MS-SQL does not support stream size over [Int32.MaxValue]
}
bool isRepositoryStream = value.Stream is RepositoryStream;
SqlProcedure cmd = null;
//object pointer;
try
{
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_Update"
};
cmd.Parameters.Add("@BinaryPropertyId", SqlDbType.Int).Value = binaryDataId;
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar, 50).Value = value.ContentType;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.VarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? (object)DBNull.Value : (object)value.FileName.FileNameWithoutExtension;
cmd.Parameters.Add("@Extension", SqlDbType.VarChar, 50).Value = ValidateExtension(value.FileName.Extension);
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = value.Size;
// Do not update the stream field in the database if that is not loaded (other change happened)
cmd.Parameters.Add("@IsStreamModified", SqlDbType.TinyInt).Value = isRepositoryStream ? 0 : 1;
//cmd.Parameters.Add("@IsStreamModified", SqlDbType.TinyInt).Value = isLoaded ? 1 : 0;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = (value.Checksum != null) ? (object)value.Checksum : DBNull.Value;;
//SqlParameter pointerParameter = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
//pointerParameter.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
//pointer = pointerParameter.Value;
}
finally
{
cmd.Dispose();
}
if (!isRepositoryStream && value.Stream != null && value.Stream.Length > 0)
//if(isLoaded && stream != null && stream.Length > 0)
{
// Stream isloaded, exists -> write it
WriteBinaryStream(value.Stream, binaryDataId);
}
}
private void ExecuteScripts(List <StringBuilder> scripts)
{
using (var traceOperation = Logger.TraceOperation("Execute storage schema scripts."))
{
// Ensure transaction encapsulation
bool isLocalTransaction = !TransactionScope.IsActive;
if (isLocalTransaction)
{
TransactionScope.Begin();
}
try
{
if (_slotsToReset.Count > 0)
{
StringBuilder sb = new StringBuilder();
scripts.Insert(0, sb);
WriteSlotsToResetScripts(sb, _slotsToReset);
}
foreach (StringBuilder script in scripts)
{
using (var proc = new SqlProcedure {
CommandText = script.ToString(), CommandType = CommandType.Text
})
{
proc.ExecuteNonQuery();
}
}
if (isLocalTransaction)
{
TransactionScope.Commit();
}
traceOperation.IsSuccessful = true;
}
finally
{
if (isLocalTransaction && TransactionScope.IsActive)
{
TransactionScope.Rollback();
}
}
}
}
public void DeleteBinaryProperty(int versionId, PropertyType propertyType)
{
if (propertyType == null)
{
throw new ArgumentNullException("propertyType");
}
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_Delete"
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
/*============================================================================ Node Insert/Update */
public void UpdateSubTreePath(string oldPath, string newPath)
{
if (oldPath == null)
{
throw new ArgumentNullException("oldPath");
}
if (newPath == null)
{
throw new ArgumentNullException("newPath");
}
if (oldPath.Length == 0)
{
throw new ArgumentException("Old path cannot be empty.", "oldPath");
}
if (newPath.Length == 0)
{
throw new ArgumentException("New path cannot be empty.", "newPath");
}
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure {
CommandText = "proc_Node_UpdateSubTreePath"
};
cmd.Parameters.Add("@OldPath", SqlDbType.NVarChar, 450).Value = oldPath;
cmd.Parameters.Add("@NewPath", SqlDbType.NVarChar, 450).Value = newPath;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
private void InitializeNewStream(IndexBackup backup)
{
var sql = String.Format("UPDATE {0} SET [BackupFile] = @InitialStream WHERE BackupNumber = @BackupNumber", backup.TableName);
using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@BackupNumber", SqlDbType.Int));
cmd.Parameters["@BackupNumber"].Value = backup.BackupNumber;
cmd.Parameters.Add(new SqlParameter("@InitialStream", SqlDbType.VarBinary));
cmd.Parameters["@InitialStream"].Value = new byte[0];
cmd.ExecuteNonQuery();
}
}
protected internal override void SetActiveBackup(IndexBackup backup, IndexBackup lastBackup)
{
var sql = (lastBackup == null) ?
String.Format("UPDATE {0} SET IsActive = 1 WHERE BackupNumber = @ActiveBackupNumber", backup.TableName)
:
String.Format(@"UPDATE {0} SET IsActive = 1 WHERE BackupNumber = @ActiveBackupNumber
UPDATE {1} SET IsActive = 0 WHERE BackupNumber = @InactiveBackupNumber", backup.TableName, lastBackup.TableName);
using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
{
cmd.Parameters.Add(new SqlParameter("@ActiveBackupNumber", SqlDbType.Int)).Value = backup.BackupNumber;
if(lastBackup!=null)
cmd.Parameters.Add(new SqlParameter("@InactiveBackupNumber", SqlDbType.Int)).Value = lastBackup.BackupNumber;
cmd.ExecuteNonQuery();
}
}
public void DeleteBinaryProperty(int versionId, PropertyType propertyType)
{
if (propertyType == null)
throw new ArgumentNullException("propertyType");
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_Delete" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
protected internal override IndexBackup CreateBackup(int backupNumber)
{
var backup = new IndexBackup
{
BackupNumber = backupNumber,
AppDomainName = AppDomain.CurrentDomain.FriendlyName,
BackupDate = DateTime.Now,
ComputerName = Environment.MachineName,
};
var sql = String.Format(@"INSERT INTO {0} (BackupNumber, IsActive, BackupDate, ComputerName, [AppDomain]) VALUES
(@BackupNumber, 0, @BackupDate, @ComputerName, @AppDomain)", backup.TableName);
using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
{
cmd.Parameters.Add("@BackupNumber", SqlDbType.Int).Value = backup.BackupNumber;
cmd.Parameters.Add("@BackupDate", SqlDbType.DateTime).Value = backup.BackupDate;
cmd.Parameters.Add("@ComputerName", SqlDbType.NVarChar, 100).Value = backup.ComputerName;
cmd.Parameters.Add("@AppDomain", SqlDbType.NVarChar, 500).Value = backup.AppDomainName;
cmd.ExecuteNonQuery();
}
return backup;
}
public void SaveReferenceProperty(int versionId, PropertyType propertyType, IEnumerable<int> value)
{
if (propertyType == null)
throw new ArgumentNullException("propertyType");
// Remove/Add referredNodeIds to update the database state to the proper one
SqlProcedure cmd = null;
try
{
string referredListXml = SqlProvider.CreateIdXmlForReferencePropertyUpdate(value);
cmd = new SqlProcedure { CommandText = "proc_ReferenceProperty_Update" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.Parameters.Add("@ReferredNodeIdListXml", SqlDbType.Xml).Value = referredListXml;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
public void SaveTextProperty(int versionId, PropertyType propertyType, bool isLoaded, string value)
{
//if (propertyType.Name == "TextExtract" && !isLoaded)
// return;
if (propertyType == null)
{
throw new ArgumentNullException("propertyType");
}
if (!isLoaded)
{
throw new ApplicationException(); // There is no other data that could be 'IsModified'...
}
SqlProcedure cmd = null;
// Delete existing values (otherwise have to check which table used and then switch between
// the Insert or Update of the specific table. This way only insert is necessary.)
try
{
cmd = new SqlProcedure {
CommandText = "proc_TextProperty_Delete"
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
if (value == null)
{
return;
}
if (value.Length > SqlProvider.TextAlternationSizeLimit)
{
// NText
try
{
cmd = new SqlProcedure {
CommandText = "proc_TextProperty_InsertNText"
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.Parameters.Add("@Value", SqlDbType.NText).Value = value;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
else
{
// NVarchar
try
{
cmd = new SqlProcedure {
CommandText = "proc_TextProperty_InsertNVarchar"
};
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.Parameters.Add("@Value", SqlDbType.NVarChar, SqlProvider.TextAlternationSizeLimit).Value = value == null ? (object)DBNull.Value : (object)value;;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
}
protected internal override void CopyStagingToBinaryData(int versionId, int propertyTypeId, int stagingBinaryDataId, string checksum)
{
var sql = @"
UPDATE BinaryProperties
SET ContentType = S.ContentType,
FileNameWithoutExtension = S.FileNameWithoutExtension,
Extension = S.Extension,
Size = S.Size,
[Checksum] = @Checksum,
Stream = S.Stream
FROM BinaryProperties B
JOIN StagingBinaryProperties S ON S.VersionId = B.VersionId AND S.PropertyTypeId = B.PropertyTypeId
WHERE B.VersionId = @VersionId AND B.PropertyTypeId = @PropertyTypeId
";
using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
{
cmd.Parameters.Add(new SqlParameter("@VersionId", SqlDbType.Int)).Value = versionId;
cmd.Parameters.Add(new SqlParameter("@PropertyTypeId", SqlDbType.Int)).Value = propertyTypeId;
cmd.Parameters.Add(new SqlParameter("@Checksum", SqlDbType.VarChar, 200)).Value = checksum;
cmd.ExecuteNonQuery();
}
}
protected internal override Stream LoadBinaryPropertyValue(int versionId, int propertyTypeId)
{
// Retrieve binary pointer for chunk reading
int length = 0;
int pointer = 0;
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_GetPointer" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
SqlParameter pointerOutParam = cmd.Parameters.Add("@Id", SqlDbType.Int);
pointerOutParam.Direction = ParameterDirection.Output;
SqlParameter lengthOutParam = cmd.Parameters.Add("@Length", SqlDbType.Int);
lengthOutParam.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
if (lengthOutParam.Value != DBNull.Value)
length = (int)lengthOutParam.Value;
if (pointerOutParam.Value != DBNull.Value)
pointer = Convert.ToInt32(pointerOutParam.Value);
}
finally
{
cmd.Dispose();
}
if (pointer == 0)
return null;
// Read the stream by segments
cmd = null;
SqlDataReader reader = null;
Stream stream = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_ReadStream" };
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = pointer;
SqlParameter offsetParam = cmd.Parameters.Add("@Offset", SqlDbType.Int);
SqlParameter sizeParam = cmd.Parameters.Add("@Size", SqlDbType.Int);
int offset = 0;
offsetParam.Value = offset;
int size = BinaryStreamBufferLength;
sizeParam.Value = size;
byte[] buffer = new byte[BinaryStreamBufferLength];
stream = new MemoryStream(length);
if (length > 0)
{
do
{
// Calculate buffer size - may be less than BinaryStreamBufferLength for last block.
if ((offset + BinaryStreamBufferLength) >= length)
{
size = length - offset;
sizeParam.Value = size + 1;
}
reader = cmd.ExecuteReader(CommandBehavior.SingleResult);
reader.Read();
reader.GetBytes(0, 0, buffer, 0, size);
reader.Close();
stream.Write(buffer, 0, size);
// Set the new offset
offset += size;
offsetParam.Value = offset;
}
while (offset < length);
stream.Seek(0, SeekOrigin.Begin);
}
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
return stream;
}
protected internal override DataOperationResult DeleteNodeTreePsychical(int nodeId)
{
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_Node_DeletePhysical" };
cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeId;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
return DataOperationResult.Successful;
}
protected override void KeepOnlyLastIndexBackup()
{
var backup = LoadLastBackup();
if (backup == null)
return;
backup = new IndexBackup { BackupNumber = backup.BackupNumber - 1 };
var sql = "TRUNCATE TABLE " + backup.TableName;
using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
cmd.ExecuteNonQuery();
}
protected internal override DataOperationResult MoveNodeTree(int sourceNodeId, int targetNodeId)
{
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_Node_Move" };
cmd.Parameters.Add("@SourceNodeId", SqlDbType.Int).Value = sourceNodeId;
cmd.Parameters.Add("@TargetNodeId", SqlDbType.Int).Value = targetNodeId;
cmd.ExecuteNonQuery();
}
catch (SqlException e) //logged //rethrow
{
switch (e.State)
{
case 1: //'Invalid operation: moving a contentList / a subtree that contains a contentList under an another contentList.'
Logger.WriteException(e);
return DataOperationResult.Move_TargetContainsSameName;
case 2:
default:
throw;
}
}
finally
{
cmd.Dispose();
}
return 0;
}
internal static void AddStream(BlobStorageContext context, Stream stream)
{
FileStreamData fileStreamData = null;
var providerData = context.BlobProviderData as BuiltinBlobProviderData;
if (providerData != null)
{
fileStreamData = providerData.FileStreamData;
}
SqlProcedure cmd = null;
try
{
// if possible, write the stream using the special Filestream technology
if (BlobStorageBase.UseFileStream(stream.Length))
{
WriteSqlFileStream(stream, context.FileId, fileStreamData);
return;
}
// We have to work with an integer since SQL does not support
// binary values bigger than [Int32.MaxValue].
var streamSize = Convert.ToInt32(stream.Length);
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_WriteStream"
};
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = context.FileId;
var offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int);
var valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize);
if (BlobStorage.FileStreamEnabled)
{
var useFileStreamParameter = cmd.Parameters.Add("@UseFileStream", SqlDbType.TinyInt);
useFileStreamParameter.Value = false;
}
var offset = 0;
byte[] buffer = null;
stream.Seek(0, SeekOrigin.Begin);
// The 'while' loop is misleading here, because we write the whole
// stream at once. Bigger files should go to the Filestream
// column anyway.
while (offset < streamSize)
{
// Buffer size may be less at the end os the stream than the limit
var bufferSize = streamSize - offset;
if (buffer == null || buffer.Length != bufferSize)
{
buffer = new byte[bufferSize];
}
// Read bytes from the source
stream.Read(buffer, 0, bufferSize);
offsetParameter.Value = offset;
valueParameter.Value = buffer;
// Write full stream
cmd.ExecuteNonQuery();
offset += bufferSize;
}
}
finally
{
cmd?.Dispose();
}
}
private void ExecuteScript(string script, int versionId, int page, IEnumerable<SqlParameter> @params)
{
using (var cmd = new SqlProcedure { CommandText = script })
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@Page", SqlDbType.Int).Value = page;
cmd.Parameters.AddRange(@params.ToArray());
cmd.ExecuteNonQuery();
}
}
public void UpdateBinaryProperty(int binaryDataId, BinaryDataValue value)
{
if (!RepositoryConfiguration.FileStreamEnabled)
{
// MS-SQL does not support stream size over [Int32.MaxValue],
// but check only if Filestream is not enabled
if (value.Stream != null && value.Stream.Length > Int32.MaxValue)
{
throw new NotSupportedException();
}
}
var isRepositoryStream = value.Stream is RepositoryStream || value.Stream is SenseNetSqlFileStream;
FileStreamData fileStreamData = null;
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_Update"
};
cmd.Parameters.Add("@BinaryPropertyId", SqlDbType.Int).Value = binaryDataId;
cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 450).Value = value.ContentType;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? (object)DBNull.Value : (object)value.FileName.FileNameWithoutExtension;
cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = ValidateExtension(value.FileName.Extension);
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = value.Size;
// Do not update the stream field in the database if it is not loaded (other change happened)
cmd.Parameters.Add("@IsStreamModified", SqlDbType.TinyInt).Value = isRepositoryStream ? 0 : 1;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = (value.Checksum != null) ? (object)value.Checksum : DBNull.Value;
if (RepositoryConfiguration.FileStreamEnabled)
{
string path;
byte[] transactionContext;
//Update row and retrieve file path and
//transaction context for the Filestream column
using (var reader = cmd.ExecuteReader())
{
reader.Read();
path = reader.GetString(0);
transactionContext = reader.GetSqlBytes(1).Buffer;
}
if (!string.IsNullOrEmpty(path))
{
fileStreamData = new FileStreamData {
Path = path, TransactionContext = transactionContext
}
}
;
}
else
{
cmd.ExecuteNonQuery();
}
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
if (!isRepositoryStream && value.Stream != null && value.Stream.Length > 0)
{
// Stream exists and is loaded -> write it
WriteBinaryStream(value.Stream, binaryDataId, fileStreamData);
}
}
private static void WriteBinaryStream(Stream stream, int binaryPropertyId, FileStreamData fileStreamData = null)
{
SqlProcedure cmd = null;
try
{
var longStreamSize = stream.Length;
var useFileStream = RepositoryConfiguration.FileStreamEnabled &&
longStreamSize > RepositoryConfiguration.MinimumSizeForFileStreamInBytes;
//If possible, write the stream using the special Filestream technology
if (useFileStream)
{
WriteSqlFileStream(stream, binaryPropertyId, fileStreamData);
return;
}
//We have to work with an integer since SQL does not support
//binary values bigger than [Int32.MaxValue].
var streamSize = Convert.ToInt32(stream.Length);
cmd = new SqlProcedure {
CommandText = "proc_BinaryProperty_WriteStream"
};
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId;
var offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int);
var valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize);
if (RepositoryConfiguration.FileStreamEnabled)
{
var useFileStreamParameter = cmd.Parameters.Add("@UseFileStream", SqlDbType.TinyInt);
useFileStreamParameter.Value = useFileStream;
}
int offset = 0;
byte[] buffer = null;
stream.Seek(0, SeekOrigin.Begin);
//The 'while' loop is misleading here, because we write the whole
//stream at once. Bigger files should go to the Filestream
//column anyway.
while (offset < streamSize)
{
// Buffer size may be less at the end os the stream than the limit
int bufferSize = streamSize - offset;
if (buffer == null || buffer.Length != bufferSize)
{
buffer = new byte[bufferSize];
}
// Read bytes from the source
stream.Read(buffer, 0, bufferSize);
offsetParameter.Value = offset;
valueParameter.Value = buffer;
// Write full stream
cmd.ExecuteNonQuery();
offset += bufferSize;
}
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
}
protected internal override void SaveChunk(int stagingBinaryDataId, byte[] bytes, int offset)
{
var sql = "UPDATE StagingBinaryProperties SET [Stream].WRITE(@Buffer, @Offset, @Length) WHERE Id = @Id";
using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
{
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id", SqlDbType.Int)).Value = stagingBinaryDataId;
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Buffer", SqlDbType.VarBinary)).Value = bytes;
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Offset", SqlDbType.BigInt)).Value = offset;
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Length", SqlDbType.BigInt)).Value = bytes.LongLength;
cmd.ExecuteNonQuery();
}
}
public void SaveTextProperty(int versionId, PropertyType propertyType, bool isLoaded, string value)
{
//if (propertyType.Name == "TextExtract" && !isLoaded)
// return;
if (propertyType == null)
throw new ArgumentNullException("propertyType");
if (!isLoaded)
throw new ApplicationException(); // There is no other data that could be 'IsModified'...
SqlProcedure cmd = null;
// Delete existing values (otherwise have to check which table used and then switch between
// the Insert or Update of the specific table. This way only insert is necessary.)
try
{
cmd = new SqlProcedure { CommandText = "proc_TextProperty_Delete" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
if (value == null)
return;
if (value.Length > SqlProvider.TextAlternationSizeLimit)
{
// NText
try
{
cmd = new SqlProcedure { CommandText = "proc_TextProperty_InsertNText" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.Parameters.Add("@Value", SqlDbType.NText).Value = value;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
else
{
// NVarchar
try
{
cmd = new SqlProcedure { CommandText = "proc_TextProperty_InsertNVarchar" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyType.Id;
cmd.Parameters.Add("@Value", SqlDbType.NVarChar, SqlProvider.TextAlternationSizeLimit).Value = value == null ? (object)DBNull.Value : (object)value; ;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
}
protected internal override void DeleteStagingBinaryData(int stagingBinaryDataId)
{
var sql = "DELETE from StagingBinaryProperties WHERE Id = @Id";
using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
{
cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int)).Value = stagingBinaryDataId;
cmd.ExecuteNonQuery();
}
}
protected internal override void ExplicateGroupMemberships()
{
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_Security_ExplicateGroupMemberships" };
cmd.ExecuteNonQuery();
}
finally
{
if (cmd != null)
cmd.Dispose();
}
}
public void UpdateBinaryProperty(int binaryDataId, BinaryDataValue value)
{
if (value.Stream != null && value.Stream.Length > Int32.MaxValue)
throw new NotSupportedException(); // MS-SQL does not support stream size over [Int32.MaxValue]
bool isRepositoryStream = value.Stream is RepositoryStream;
SqlProcedure cmd = null;
//object pointer;
try
{
cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_Update" };
cmd.Parameters.Add("@BinaryPropertyId", SqlDbType.Int).Value = binaryDataId;
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar, 50).Value = value.ContentType;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.VarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? (object)DBNull.Value : (object)value.FileName.FileNameWithoutExtension;
cmd.Parameters.Add("@Extension", SqlDbType.VarChar, 50).Value = ValidateExtension(value.FileName.Extension);
cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value = value.Size;
// Do not update the stream field in the database if that is not loaded (other change happened)
cmd.Parameters.Add("@IsStreamModified", SqlDbType.TinyInt).Value = isRepositoryStream ? 0 : 1;
//cmd.Parameters.Add("@IsStreamModified", SqlDbType.TinyInt).Value = isLoaded ? 1 : 0;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = (value.Checksum != null) ? (object)value.Checksum : DBNull.Value; ;
//SqlParameter pointerParameter = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
//pointerParameter.Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
//pointer = pointerParameter.Value;
}
finally
{
cmd.Dispose();
}
if (!isRepositoryStream && value.Stream != null && value.Stream.Length > 0)
//if(isLoaded && stream != null && stream.Length > 0)
{
// Stream isloaded, exists -> write it
WriteBinaryStream(value.Stream, binaryDataId);
}
}
protected internal override void ExplicateOrganizationUnitMemberships(IUser user)
{
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_Security_ExplicateOrgUnitMemberships" };
cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = user.Id;
cmd.ExecuteNonQuery();
}
finally
{
if (cmd != null)
cmd.Dispose();
}
}
private void SetBreakInheritanceFlag(int nodeId, bool @break)
{
SqlProcedure cmd = null;
using (cmd = new SqlProcedure { CommandText = "proc_Security_BreakInheritance2" })
{
cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeId;
cmd.Parameters.Add("@BreakInheritanceValue", SqlDbType.TinyInt).Value = @break ? (byte)0 : (byte)1;
cmd.ExecuteNonQuery();
}
}
//======================================================
protected internal override void PersistUploadToken(UploadToken value)
{
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_ApplicationMessaging_PersistUploadToken" };
cmd.Parameters.Add("@Token", SqlDbType.UniqueIdentifier).Value = value.UploadGuid;
cmd.Parameters.Add("@UserId", SqlDbType.Int).Value = value.UserId;
cmd.Parameters.Add("@CreatedOn", SqlDbType.DateTime).Value = DateTime.Now;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
protected internal override void SetPermission(int principalId, int nodeId, PermissionType permissionType, bool isInheritable, PermissionValue permissionValue)
{
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_Security_SetPermission" };
cmd.Parameters.Add("@PrincipalId", SqlDbType.Int).Value = principalId;
cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeId;
cmd.Parameters.Add("@PermissionTypeId", SqlDbType.Int).Value = permissionType.Id;
cmd.Parameters.Add("@IsInheritable", SqlDbType.TinyInt).Value = isInheritable ? (byte)1 : (byte)0;
cmd.Parameters.Add("@PermissionValue", SqlDbType.TinyInt).Value = (byte)permissionValue;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
private static void WriteBinaryStream(Stream stream, int binaryPropertyId)
{
SqlProcedure cmd = null;
try
{
int streamSize = Convert.ToInt32(stream.Length);
cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_WriteStream" };
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId;
SqlParameter offsetParameter = cmd.Parameters.Add("@Offset", SqlDbType.Int);
SqlParameter valueParameter = cmd.Parameters.Add("@Value", SqlDbType.VarBinary, streamSize);
int offset = 0;
byte[] buffer = null;
stream.Seek(0, SeekOrigin.Begin);
while (offset < streamSize)
{
// Buffer size may be less at the end os the stream than the limit
int bufferSize = streamSize - offset;
//partial update is not supported on FILESTREAM columns
//if(bufferSize > SqlProvider.BinaryStreamBufferLength)
// bufferSize = SqlProvider.BinaryStreamBufferLength;
if (buffer == null || buffer.Length != bufferSize)
buffer = new byte[bufferSize];
// Write buffered stream segment
stream.Read(buffer, 0, bufferSize);
offsetParameter.Value = offset;
valueParameter.Value = buffer;
cmd.ExecuteNonQuery();
offset += bufferSize;
}
}
finally
{
cmd.Dispose();
}
}
protected internal override void SetPermission(SecurityEntry entry)
{
var sql = new StringBuilder();
if (entry.AllowBits + entry.DenyBits == 0)
{
sql.Append("DELETE FROM SecurityEntries WHERE DefinedOnNodeId = @NodeId AND PrincipalId = @PrincipalId AND IsInheritable = @IsInheritable");
}
else
{
sql.AppendLine("IF NOT EXISTS (SELECT SecurityEntryId FROM dbo.SecurityEntries WHERE DefinedOnNodeId = @NodeId AND PrincipalId = @PrincipalId AND IsInheritable = @IsInheritable)");
sql.AppendLine(" INSERT INTO dbo.SecurityEntries (DefinedOnNodeId, PrincipalId, IsInheritable) VALUES (@NodeId, @PrincipalId, @IsInheritable)");
sql.AppendLine("UPDATE SecurityEntries SET");
for (int i = 0; i < ActiveSchema.PermissionTypes.Count; i++)
{
var value = (byte)0;
var mask = 1<<i;
if((entry.DenyBits & mask)!=0)
value = (byte)2;
else if((entry.AllowBits & mask)!=0)
value = (byte)1;
if (i > 0)
sql.AppendLine(",");
sql.Append(" PermissionValue").Append(i + 1).Append(" = ").Append(value);
}
sql.AppendLine();
sql.AppendLine("WHERE DefinedOnNodeId = @NodeId AND PrincipalId = @PrincipalId AND IsInheritable = @IsInheritable");
}
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = sql.ToString(), CommandType = CommandType.Text };
cmd.Parameters.Add("@PrincipalId", SqlDbType.Int).Value = entry.PrincipalId;
cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = entry.DefinedOnNodeId;
cmd.Parameters.Add("@IsInheritable", SqlDbType.TinyInt).Value = entry.Propagates ? (byte)1 : (byte)0;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}
/*============================================================================ Node Insert/Update */
public void UpdateSubTreePath(string oldPath, string newPath)
{
if (oldPath == null)
throw new ArgumentNullException("oldPath");
if (newPath == null)
throw new ArgumentNullException("newPath");
if (oldPath.Length == 0)
throw new ArgumentException("Old path cannot be empty.", "oldPath");
if (newPath.Length == 0)
throw new ArgumentException("New path cannot be empty.", "newPath");
SqlProcedure cmd = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_Node_UpdateSubTreePath" };
cmd.Parameters.Add("@OldPath", SqlDbType.NVarChar, 450).Value = oldPath;
cmd.Parameters.Add("@NewPath", SqlDbType.NVarChar, 450).Value = newPath;
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
}
}