Example #1
0
        //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;
            }
        }
Example #2
0
        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;
        }
Example #3
0
        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);
            }
        }
Example #4
0
        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;
        }