//protected internal override INodeQueryCompiler CreateNodeQueryCompiler()
//{
// return new SqlCompiler();
//}
//protected internal override List<NodeToken> ExecuteQuery(NodeQuery query)
//{
// List<NodeToken> result = new List<NodeToken>();
// SqlCompiler compiler = new SqlCompiler();
// NodeQueryParameter[] parameters;
// string compiledCommandText = compiler.Compile(query, out parameters);
// SqlProcedure command = null;
// SqlDataReader reader = null;
// try
// {
// command = new SqlProcedure { CommandText = compiledCommandText };
// command.CommandType = CommandType.Text;
// foreach (var parameter in parameters)
// command.Parameters.Add(new SqlParameter(parameter.Name, parameter.Value));
// reader = command.ExecuteReader();
// ReadNodeTokens(reader, result);
// }
// finally
// {
// if (reader != null && !reader.IsClosed)
// reader.Close();
// command.Dispose();
// }
// return result;
//}
protected internal override void LoadNodes(Dictionary<int, NodeBuilder> buildersByVersionId)
{
List<string> versionInfo = new List<string>();
versionInfo.Add(String.Concat("VersionsId[count: ", buildersByVersionId.Count, "]"));
if (buildersByVersionId.Keys.Count > 20)
{
versionInfo.AddRange(buildersByVersionId.Keys.Take(20).Select(x => x.ToString()));
versionInfo.Add("...");
}
else
versionInfo.AddRange(buildersByVersionId.Keys.Select(x => x.ToString()).ToArray());
var operationTitle = String.Join(", ", versionInfo.ToArray());
using (var traceOperation = Logger.TraceOperation("SqlProvider.LoadNodes" + operationTitle))
{
var builders = buildersByVersionId; // Shortcut
SqlProcedure cmd = null;
SqlDataReader reader = null;
try
{
cmd = new SqlProcedure { CommandText = "proc_Node_LoadData_Batch" };
string xmlIds = CreateIdXmlForNodeInfoBatchLoad(builders);
cmd.Parameters.Add("@IdsInXml", SqlDbType.Xml).Value = xmlIds;
reader = cmd.ExecuteReader();
//-- #1: FlatProperties
//SELECT * FROM FlatProperties
// WHERE VersionId IN (select id from @versionids)
var versionIdIndex = reader.GetOrdinal("VersionId");
var pageIndex = reader.GetOrdinal("Page");
while (reader.Read())
{
int versionId = reader.GetInt32(versionIdIndex);
int page = reader.GetInt32(pageIndex);
NodeBuilder builder = builders[versionId];
foreach (PropertyType pt in builder.Token.AllPropertyTypes)
{
string mapping = PropertyMap.GetValidMapping(page, pt);
if (mapping.Length != 0)
{
// Mapped property appears in the given page
object val = reader[mapping];
builder.AddDynamicProperty(pt, (val == DBNull.Value) ? null : val);
}
}
}
reader.NextResult();
//-- #2: BinaryProperties
//SELECT BinaryPropertyId, VersionId, PropertyTypeId, ContentType, FileNameWithoutExtension,
// Extension, [Size], [Checksum], NULL AS Stream, 0 AS Loaded
//FROM dbo.BinaryProperties
//WHERE PropertyTypeId IN (select id from @binids) AND VersionId IN (select id from @versionids)
var binaryPropertyIdIndex = reader.GetOrdinal("BinaryPropertyId");
versionIdIndex = reader.GetOrdinal("VersionId");
var checksumPropertyIndex = reader.GetOrdinal("Checksum");
var propertyTypeIdIndex = reader.GetOrdinal("PropertyTypeId");
var contentTypeIndex = reader.GetOrdinal("ContentType");
var fileNameWithoutExtensionIndex = reader.GetOrdinal("FileNameWithoutExtension");
var extensionIndex = reader.GetOrdinal("Extension");
var sizeIndex = reader.GetOrdinal("Size");
while (reader.Read())
{
string ext = reader.GetString(extensionIndex);
if (ext.Length != 0)
ext = ext.Remove(0, 1); // Remove dot from the start if extension is not empty
string fn = reader.GetSafeString(fileNameWithoutExtensionIndex); // reader.IsDBNull(fileNameWithoutExtensionIndex) ? null : reader.GetString(fileNameWithoutExtensionIndex);
var x = new BinaryDataValue
{
Id = reader.GetInt32(binaryPropertyIdIndex),
Checksum = reader.GetSafeString(checksumPropertyIndex), //reader.IsDBNull(checksumPropertyIndex) ? null : reader.GetString(checksumPropertyIndex),
FileName = new BinaryFileName(fn, ext),
ContentType = reader.GetString(contentTypeIndex),
Size = reader.GetInt64(sizeIndex)
};
var versionId = reader.GetInt32(versionIdIndex);
var propertyTypeId = reader.GetInt32(propertyTypeIdIndex);
builders[versionId].AddDynamicProperty(propertyTypeId, x);
}
reader.NextResult();
//-- #3: ReferencePropertyInfo + Referred NodeToken
//SELECT VersionId, PropertyTypeId, ReferredNodeId
//FROM dbo.ReferenceProperties ref
//WHERE ref.VersionId IN (select id from @versionids)
versionIdIndex = reader.GetOrdinal("VersionId");
propertyTypeIdIndex = reader.GetOrdinal("PropertyTypeId");
var nodeIdIndex = reader.GetOrdinal("ReferredNodeId");
//-- Collect references to Dictionary<versionId, Dictionary<propertyTypeId, List<referredNodeId>>>
var referenceCollector = new Dictionary<int, Dictionary<int, List<int>>>();
while (reader.Read())
{
var versionId = reader.GetInt32(versionIdIndex);
var propertyTypeId = reader.GetInt32(propertyTypeIdIndex);
var referredNodeId = reader.GetInt32(nodeIdIndex);
if (!referenceCollector.ContainsKey(versionId))
referenceCollector.Add(versionId, new Dictionary<int, List<int>>());
var referenceCollectorPerVersion = referenceCollector[versionId];
if (!referenceCollectorPerVersion.ContainsKey(propertyTypeId))
referenceCollectorPerVersion.Add(propertyTypeId, new List<int>());
referenceCollectorPerVersion[propertyTypeId].Add(referredNodeId);
}
//-- Set references to NodeData
foreach (var versionId in referenceCollector.Keys)
{
var referenceCollectorPerVersion = referenceCollector[versionId];
foreach (var propertyTypeId in referenceCollectorPerVersion.Keys)
builders[versionId].AddDynamicProperty(propertyTypeId, referenceCollectorPerVersion[propertyTypeId]);
}
reader.NextResult();
//-- #4: TextPropertyInfo (NText:Lazy, NVarchar(4000):loaded)
//SELECT VersionId, PropertyTypeId, NULL AS Value, 0 AS Loaded
//FROM dbo.TextPropertiesNText
//WHERE VersionId IN (select id from @versionids)
//UNION ALL
//SELECT VersionId, PropertyTypeId, Value, 1 AS Loaded
//FROM dbo.TextPropertiesNVarchar
//WHERE VersionId IN (select id from @versionids)
versionIdIndex = reader.GetOrdinal("VersionID");
propertyTypeIdIndex = reader.GetOrdinal("PropertyTypeId");
var valueIndex = reader.GetOrdinal("Value");
var loadedIndex = reader.GetOrdinal("Loaded");
while (reader.Read())
{
int versionId = reader.GetInt32(versionIdIndex);
int propertyTypeId = reader.GetInt32(propertyTypeIdIndex);
string value = reader.GetSafeString(valueIndex); // (reader[valueIndex] == DBNull.Value) ? null : reader.GetString(valueIndex);
bool loaded = Convert.ToBoolean(reader.GetInt32(loadedIndex));
if (loaded)
builders[versionId].AddDynamicProperty(propertyTypeId, value);
}
reader.NextResult();
//-- #5: BaseData
//SELECT N.NodeId, N.NodeTypeId, N.ContentListTypeId, N.ContentListId, N.IsDeleted, N.IsInherited,
// N.ParentNodeId, N.[Name], N.[Path], N.[Index], N.Locked, N.LockedById,
// N.ETag, N.LockType, N.LockTimeout, N.LockDate, N.LockToken, N.LastLockUpdate,
// N.CreationDate AS NodeCreationDate, N.CreatedById AS NodeCreatedById,
// N.ModificationDate AS NodeModificationDate, N.ModifiedById AS NodeModifiedById,
// V.VersionId, V.MajorNumber, V.MinorNumber, V.CreationDate, V.CreatedById,
// V.ModificationDate, V.ModifiedById, V.[Status]
//FROM dbo.Nodes AS N
// INNER JOIN dbo.Versions AS V ON N.NodeId = V.NodeId ON N.NodeId = V.NodeId
//WHERE V.VersionId IN (select id from @versionids)
nodeIdIndex = reader.GetOrdinal("NodeId");
var nodeTypeIdIndex = reader.GetOrdinal("NodeTypeId");
var contentListTypeIdIndex = reader.GetOrdinal("ContentListTypeId");
var contentListIdIndex = reader.GetOrdinal("ContentListId");
var isDeletedIndex = reader.GetOrdinal("IsDeleted");
var isInheritedIndex = reader.GetOrdinal("IsInherited");
var parentNodeIdIndex = reader.GetOrdinal("ParentNodeId");
var nameIndex = reader.GetOrdinal("Name");
var displayNameIndex = reader.GetOrdinal("DisplayName");
var pathIndex = reader.GetOrdinal("Path");
var indexIndex = reader.GetOrdinal("Index");
var lockedIndex = reader.GetOrdinal("Locked");
var lockedByIdIndex = reader.GetOrdinal("LockedById");
var eTagIndex = reader.GetOrdinal("ETag");
var lockTypeIndex = reader.GetOrdinal("LockType");
var lockTimeoutIndex = reader.GetOrdinal("LockTimeout");
var lockDateIndex = reader.GetOrdinal("LockDate");
var lockTokenIndex = reader.GetOrdinal("LockToken");
var lastLockUpdateIndex = reader.GetOrdinal("LastLockUpdate");
var nodeCreationDateIndex = reader.GetOrdinal("NodeCreationDate");
var nodeCreatedByIdIndex = reader.GetOrdinal("NodeCreatedById");
var nodeModificationDateIndex = reader.GetOrdinal("NodeModificationDate");
var nodeModifiedByIdIndex = reader.GetOrdinal("NodeModifiedById");
var nodeTimestampIndex = reader.GetOrdinal("NodeTimestamp");
versionIdIndex = reader.GetOrdinal("VersionId");
var majorNumberIndex = reader.GetOrdinal("MajorNumber");
var minorNumberIndex = reader.GetOrdinal("MinorNumber");
var creationDateIndex = reader.GetOrdinal("CreationDate");
var createdByIdIndex = reader.GetOrdinal("CreatedById");
var modificationDateIndex = reader.GetOrdinal("ModificationDate");
var modifiedByIdIndex = reader.GetOrdinal("ModifiedById");
var status = reader.GetOrdinal("Status");
var versionTimestampIndex = reader.GetOrdinal("VersionTimestamp");
while (reader.Read())
{
int versionId = reader.GetInt32(versionIdIndex);
VersionNumber versionNumber = new VersionNumber(
reader.GetInt16(majorNumberIndex),
reader.GetInt16(minorNumberIndex),
(VersionStatus)reader.GetInt16(status));
builders[versionId].SetCoreAttributes(
reader.GetInt32(nodeIdIndex),
reader.GetInt32(nodeTypeIdIndex),
TypeConverter.ToInt32(reader.GetValue(contentListIdIndex)),
TypeConverter.ToInt32(reader.GetValue(contentListTypeIdIndex)),
Convert.ToBoolean(reader.GetByte(isDeletedIndex)),
Convert.ToBoolean(reader.GetByte(isInheritedIndex)),
reader.GetSafeInt32(parentNodeIdIndex), // reader.GetValue(parentNodeIdIndex) == DBNull.Value ? 0 : reader.GetInt32(parentNodeIdIndex), //parent,
reader.GetString(nameIndex),
reader.GetSafeString(displayNameIndex),
reader.GetString(pathIndex),
reader.GetInt32(indexIndex),
Convert.ToBoolean(reader.GetByte(lockedIndex)),
reader.GetSafeInt32(lockedByIdIndex), // reader.GetValue(lockedByIdIndex) == DBNull.Value ? 0 : reader.GetInt32(lockedByIdIndex),
reader.GetString(eTagIndex),
reader.GetInt32(lockTypeIndex),
reader.GetInt32(lockTimeoutIndex),
reader.GetDateTime(lockDateIndex),
reader.GetString(lockTokenIndex),
reader.GetDateTime(lastLockUpdateIndex),
versionId,
versionNumber,
reader.GetDateTime(creationDateIndex),
reader.GetInt32(createdByIdIndex),
reader.GetDateTime(modificationDateIndex),
reader.GetInt32(modifiedByIdIndex),
reader.GetDateTime(nodeCreationDateIndex),
reader.GetInt32(nodeCreatedByIdIndex),
reader.GetDateTime(nodeModificationDateIndex),
reader.GetInt32(nodeModifiedByIdIndex),
GetLongFromBytes((byte[])reader.GetValue(nodeTimestampIndex)),
GetLongFromBytes((byte[])reader.GetValue(versionTimestampIndex))
);
}
foreach (var builder in builders.Values)
builder.Finish();
}
finally
{
if (reader != null && !reader.IsClosed)
reader.Close();
cmd.Dispose();
}
traceOperation.IsSuccessful = true;
}
}
public int InsertBinaryProperty(int versionId, int propertyTypeId, BinaryDataValue value, bool isNewNode)
{
if (value.Stream != null && value.Stream.Length > Int32.MaxValue)
throw new NotSupportedException(); // MS-SQL does not support stream size over [Int32.MaxValue]
SqlProcedure cmd = null;
int id;
try
{
cmd = new SqlProcedure { CommandText = (isNewNode ? INSERT_BINARY_PROPERTY : DELETE_AND_INSERT_BINARY_PROPERTY), CommandType = CommandType.Text };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = (versionId != 0) ? (object)versionId : DBNull.Value;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = (propertyTypeId != 0) ? (object)propertyTypeId : DBNull.Value;
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar, 50).Value = value.ContentType;
cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.VarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? 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;
cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value = (value.Checksum != null) ? (object)value.Checksum : DBNull.Value;
if (value.Stream != null && value.Stream.Length > 0)
{
var streamSize = Convert.ToInt32(value.Stream.Length);
var buffer = new byte[streamSize];
value.Stream.Seek(0, SeekOrigin.Begin);
value.Stream.Read(buffer, 0, streamSize);
cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.VarBinary)).Value = buffer;
}
else
{
cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.VarBinary)).Value = DBNull.Value;
}
id = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.CurrentCulture);
}
finally
{
if (cmd != null)
cmd.Dispose();
}
return id;
}
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);
}
}
public int InsertBinaryPropertyOld(int versionId, int propertyTypeId, BinaryDataValue value)
{
if (value.Stream != null && value.Stream.Length > Int32.MaxValue)
throw new NotSupportedException(); // MS-SQL does not support stream size over [Int32.MaxValue]
SqlProcedure cmd = null;
//object pointer;
int id = 0;
try
{
cmd = new SqlProcedure { CommandText = "proc_BinaryProperty_Insert" };
cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = (versionId != 0) ? (object)versionId : DBNull.Value;
cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = (propertyTypeId != 0) ? (object)propertyTypeId : DBNull.Value;
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;
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;
id = Convert.ToInt32(cmd.ExecuteScalar(), CultureInfo.CurrentCulture);
//pointer = pointerParameter.Value;
}
finally
{
cmd.Dispose();
}
if (value.Stream != null && value.Stream.Length > 0)
{
// Stream exists -> write it
WriteBinaryStream(value.Stream, id);
}
return id;
}
