Inheritance: IDataProcedure, IDisposable
Exemplo n.º 1
0
        private static List<int> GetExistingFlatPages(int versionId)
        {
            List<int> pages = new List<int>();
            SqlProcedure cmd = null;
            SqlDataReader reader = null;
            try
            {
                cmd = new SqlProcedure { CommandText = "proc_FlatProperties_GetExistingPages" };
                cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
                reader = cmd.ExecuteReader();

                int pageIndex = reader.GetOrdinal("Page");
                while (reader.Read())
                {
                    pages.Add(reader.GetInt32(pageIndex));
                }
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                cmd.Dispose();
            }
            return pages;
        }
        /// <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 static SqlProcedure GetBlobContextProcedure(int fileId, bool clearStream, int versionId, int propertyTypeId)
        {
            // this is a helper method to aid both the sync and
            // async version of the GetBlobContext operation

            var sql = GetBlobContextDataScript;

            // add clear stream prefix of necessary
            if (clearStream)
            {
                sql = ClearStreamByFileIdScript + sql;
            }

            var cmd = new SqlProcedure {
                CommandText = sql, CommandType = CommandType.Text
            };

            cmd.Parameters.Add("@FileId", SqlDbType.Int).Value = fileId;

            // security check: the given fileid must belong to the given version id and propertytypeid
            if (clearStream)
            {
                cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value      = versionId;
                cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
            }

            return(cmd);
        }
Exemplo n.º 4
0
        private static List <int> GetExistingFlatPages(int versionId)
        {
            List <int>    pages  = new List <int>();
            SqlProcedure  cmd    = null;
            SqlDataReader reader = null;

            try
            {
                cmd = new SqlProcedure {
                    CommandText = "proc_FlatProperties_GetExistingPages"
                };
                cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
                reader = cmd.ExecuteReader();

                int pageIndex = reader.GetOrdinal("Page");
                while (reader.Read())
                {
                    pages.Add(reader.GetInt32(pageIndex));
                }
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }

                cmd.Dispose();
            }
            return(pages);
        }
Exemplo n.º 5
0
        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();
            }
        }
Exemplo n.º 6
0
		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();
                }
            }
		}
Exemplo n.º 7
0
        //public int InsertNodeRow(NodeData nodeData)
        //{
        //    SqlProcedure cmd = null;
        //    SqlDataReader reader = null;
        //    var result = 0;
        //    try
        //    {
        //        cmd = new SqlProcedure { CommandText = "proc_Node_Insert" };
        //        cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value = nodeData.NodeTypeId;
        //        cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value;
        //        cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value;
        //        cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value = nodeData.IsDeleted ? 1 : 0;
        //        cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value = nodeData.IsInherited ? 1 : 0;
        //        cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value;
        //        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value = nodeData.Name;
        //        cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value = (object)nodeData.DisplayName ?? DBNull.Value;
        //        cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = nodeData.Path;
        //        cmd.Parameters.Add("@Index", SqlDbType.Int).Value = nodeData.Index;
        //        cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value = nodeData.Locked ? 1 : 0;
        //        cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value;
        //        cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value = nodeData.ETag ?? String.Empty;
        //        cmd.Parameters.Add("@LockType", SqlDbType.Int).Value = nodeData.LockType;
        //        cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value = nodeData.LockTimeout;
        //        cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value = nodeData.LockDate;
        //        cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value = nodeData.LockToken ?? String.Empty;
        //        cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value = nodeData.LastLockUpdate;
        //        cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.NodeCreationDate;
        //        cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.NodeCreatedById;
        //        cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.NodeModificationDate;
        //        cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.NodeModifiedById;

        //        reader = cmd.ExecuteReader();
        //        while (reader.Read())
        //        {
        //            // SELECT [NodeId], [Timestamp] FROM Nodes WHERE NodeId = @@IDENTITY
        //            result = Convert.ToInt32(reader[0]);
        //            nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]);
        //        }
        //    }
        //    catch (SqlException e) //rethrow
        //    {
        //        throw new DataException(e.Message, e);
        //    }
        //    finally
        //    {
        //        if (reader != null && !reader.IsClosed)
        //            reader.Close();
        //        cmd.Dispose();
        //    }
        //    return result;
        //}
        public void UpdateNodeRow(NodeData nodeData)
        {
            SqlProcedure  cmd    = null;
            SqlDataReader reader = null;

            try
            {
                cmd = new SqlProcedure {
                    CommandText = "proc_Node_Update"
                };
                cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value                = nodeData.Id;
                cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value            = nodeData.NodeTypeId;
                cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value     = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value;
                cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value         = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value;
                cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value         = nodeData.IsDeleted ? 1 : 0;
                cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value       = nodeData.IsInherited ? 1 : 0;
                cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value          = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value;
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value        = nodeData.Name;
                cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value = (object)nodeData.DisplayName ?? DBNull.Value;
                cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value        = nodeData.Path;
                cmd.Parameters.Add("@Index", SqlDbType.Int).Value                 = nodeData.Index;
                cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value            = nodeData.Locked ? 1 : 0;
                cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value            = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value;
                cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value          = nodeData.ETag ?? String.Empty;
                cmd.Parameters.Add("@LockType", SqlDbType.Int).Value              = nodeData.LockType;
                cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value           = nodeData.LockTimeout;
                cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value         = nodeData.LockDate;
                cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value     = nodeData.LockToken ?? String.Empty;
                cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value   = nodeData.LastLockUpdate;
                cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value     = nodeData.NodeCreationDate;
                cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value           = nodeData.NodeCreatedById;
                cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.NodeModificationDate;
                cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value          = nodeData.NodeModifiedById;
                cmd.Parameters.Add("@NodeTimestamp", SqlDbType.Timestamp).Value   = SqlProvider.GetBytesFromLong(nodeData.NodeTimestamp);

                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    // SELECT [Timestamp] FROM Nodes WHERE NodeId = @NodeId
                    nodeData.NodeTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[0]);
                }
            }
            catch (SqlException sex) //rethrow
            {
                if (sex.Message.StartsWith("Node is out of date"))
                {
                    throw new NodeIsOutOfDateException(nodeData.Id, nodeData.Path, nodeData.VersionId, nodeData.Version, sex, nodeData.NodeTimestamp);
                }
                throw new DataException(sex.Message, sex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                cmd.Dispose();
            }
        }
Exemplo n.º 8
0
        public void CopyAndUpdateVersion(NodeData nodeData, int previousVersionId, int destinationVersionId, out int lastMajorVersionId, out int lastMinorVersionId)
        {
            SqlProcedure  cmd    = null;
            SqlDataReader reader = null;

            lastMajorVersionId = 0;
            lastMinorVersionId = 0;

            try
            {
                cmd = new SqlProcedure {
                    CommandText = "proc_Version_CopyAndUpdate"
                };
                cmd.Parameters.Add("@PreviousVersionId", SqlDbType.Int).Value    = previousVersionId;
                cmd.Parameters.Add("@DestinationVersionId", SqlDbType.Int).Value = (destinationVersionId != 0) ? (object)destinationVersionId : DBNull.Value;
                cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value                = nodeData.Id;
                cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value      = nodeData.Version.Major;
                cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value      = nodeData.Version.Minor;
                cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value           = nodeData.Version.Status;
                cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value     = nodeData.VersionCreationDate;
                cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value           = nodeData.VersionCreatedById;
                cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.VersionModificationDate;
                cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value          = nodeData.VersionModifiedById;
                cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value         = JsonConvert.SerializeObject(nodeData.ChangedData);

                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    // SELECT VersionId, [Timestamp] FROM Versions WHERE VersionId = @NewVersionId
                    nodeData.VersionId        = Convert.ToInt32(reader[0]);
                    nodeData.NodeTimestamp    = SqlProvider.GetLongFromBytes((byte[])reader[1]);
                    nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[2]);

                    lastMajorVersionId = reader.GetSafeInt32(3);
                    lastMinorVersionId = reader.GetSafeInt32(4);
                }
                if (reader.NextResult())
                {
                    // SELECT BinaryPropertyId, PropertyTypeId FROM BinaryProperties WHERE VersionId = @NewVersionId
                    while (reader.Read())
                    {
                        var binId      = Convert.ToInt32(reader[0]);
                        var propId     = Convert.ToInt32(reader[1]);
                        var binaryData = (BinaryDataValue)nodeData.GetDynamicRawData(propId);
                        binaryData.Id = binId;
                    }
                }
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }

                cmd.Dispose();
            }
        }
        private SqlProcedure CreateInsertCommand(KeyValuePair <int, string>[] values)
        {
            var sql        = new StringBuilder();
            var parameters = new List <SqlParameter>();

            var paramIndex = 0;

            foreach (var item in _values)
            {
                var value = item.Value;
                if (value == null)
                {
                    continue;
                }

                string tableName;
                if (!DataProvider.Current.IsCacheableText(value))
                {
                    tableName = "TextPropertiesNText";
                    parameters.Add(new SqlParameter(VALUE_PARAMPREFIX + paramIndex, SqlDbType.NText)
                    {
                        Value = value
                    });
                }
                else
                {
                    tableName = "TextPropertiesNVarchar";
                    parameters.Add(new SqlParameter(VALUE_PARAMPREFIX + paramIndex, SqlDbType.NVarChar, SqlProvider.TextAlternationSizeLimit)
                    {
                        Value = value
                    });
                }
                sql.AppendFormat(INSERTSQL, tableName, PROPERTYTYPE_PARAMPREFIX + paramIndex, VALUE_PARAMPREFIX + paramIndex).AppendLine();
                parameters.Add(new SqlParameter(PROPERTYTYPE_PARAMPREFIX + paramIndex, SqlDbType.Int)
                {
                    Value = item.Key
                });
                paramIndex++;
            }

            if (sql.Length == 0)
            {
                return(null);
            }

            parameters.Add(new SqlParameter("@VersionId", SqlDbType.Int)
            {
                Value = _versionId
            });

            var cmd = new SqlProcedure {
                CommandText = sql.ToString(), CommandType = CommandType.Text
            };

            cmd.Parameters.AddRange(parameters.ToArray());

            return(cmd);
        }
Exemplo n.º 10
0
        // ============================================================================ "less roundtrip methods"

        public virtual void InsertNodeAndVersionRows(NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId)
        {
            using (var cmd = new SqlProcedure {
                CommandText = "proc_NodeAndVersion_Insert"
            })
            {
                cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value             = nodeData.NodeTypeId;
                cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value      = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value;
                cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value          = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value;
                cmd.Parameters.Add("@CreatingInProgress", SqlDbType.TinyInt).Value = nodeData.CreatingInProgress;
                cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value          = nodeData.IsDeleted ? 1 : 0;
                cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value        = 0;
                cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value           = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value;
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value         = nodeData.Name;
                cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value  = (object)nodeData.DisplayName ?? DBNull.Value;
                cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value         = nodeData.Path;
                cmd.Parameters.Add("@Index", SqlDbType.Int).Value                     = nodeData.Index;
                cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value                = nodeData.Locked ? 1 : 0;
                cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value                = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value;
                cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value              = nodeData.ETag ?? String.Empty;
                cmd.Parameters.Add("@LockType", SqlDbType.Int).Value                  = nodeData.LockType;
                cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value               = nodeData.LockTimeout;
                cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value             = nodeData.LockDate;
                cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value         = nodeData.LockToken ?? String.Empty;
                cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value       = nodeData.LastLockUpdate;
                cmd.Parameters.Add("@NodeCreationDate", SqlDbType.DateTime).Value     = nodeData.CreationDate;
                cmd.Parameters.Add("@NodeCreatedById", SqlDbType.Int).Value           = nodeData.CreatedById;
                cmd.Parameters.Add("@NodeModificationDate", SqlDbType.DateTime).Value = nodeData.ModificationDate;
                cmd.Parameters.Add("@NodeModifiedById", SqlDbType.Int).Value          = nodeData.ModifiedById;

                cmd.Parameters.Add("@IsSystem", SqlDbType.TinyInt).Value  = nodeData.IsSystem ? 1 : 0;
                cmd.Parameters.Add("@OwnerId", SqlDbType.Int).Value       = nodeData.OwnerId;
                cmd.Parameters.Add("@SavingState", SqlDbType.Int).Value   = (int)nodeData.SavingState;
                cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value = JsonConvert.SerializeObject(nodeData.ChangedData);

                cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value      = nodeData.Version.Major;
                cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value      = nodeData.Version.Minor;
                cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value           = nodeData.Version.Status;
                cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value     = nodeData.VersionCreationDate;
                cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value           = nodeData.VersionCreatedById;
                cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.VersionModificationDate;
                cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value          = nodeData.VersionModifiedById;

                using (var reader = cmd.ExecuteReader())
                {
                    reader.Read();
                    nodeData.Id               = Convert.ToInt32(reader[0]);
                    nodeData.VersionId        = Convert.ToInt32(reader[1]);
                    nodeData.NodeTimestamp    = SqlProvider.GetLongFromBytes((byte[])reader[2]);
                    nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[3]);

                    lastMajorVersionId = reader.GetSafeInt32(4);
                    lastMinorVersionId = reader.GetSafeInt32(5);

                    nodeData.Path = reader.GetSafeString(6);
                }
            }
        }
Exemplo n.º 11
0
        /// <summary>
        /// Loads a cache item into memory that either contains the raw binary (if its size fits into the limit) or
        /// just the blob metadata pointing to the blob storage.
        /// </summary>
        /// <param name="versionId">Content version id.</param>
        /// <param name="propertyTypeId">Binary property type id.</param>
        public BinaryCacheEntity LoadBinaryCacheEntity(int versionId, int propertyTypeId)
        {
            var commandText = string.Format(LoadBinaryCacheEntityFormatScript, BlobStorage.BinaryCacheSize);

            using (var cmd = new SqlProcedure {
                CommandText = commandText
            })
            {
                cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value      = versionId;
                cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
                cmd.CommandType = CommandType.Text;

                using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
                {
                    if (!reader.HasRows || !reader.Read())
                    {
                        return(null);
                    }

                    var length           = reader.GetInt64(0);
                    var binaryPropertyId = reader.GetInt32(1);
                    var fileId           = reader.GetInt32(2);

                    var providerName     = reader.GetSafeString(3);
                    var providerTextData = reader.GetSafeString(4);

                    byte[] rawData;
                    if (reader.IsDBNull(5))
                    {
                        rawData = null;
                    }
                    else
                    {
                        rawData = (byte[])reader.GetValue(5);
                    }

                    var provider = BlobStorageBase.GetProvider(providerName);
                    var context  = new BlobStorageContext(provider, providerTextData)
                    {
                        VersionId = versionId, PropertyTypeId = propertyTypeId, FileId = fileId, Length = length
                    };
                    if (provider == BlobStorageBase.BuiltInProvider)
                    {
                        context.BlobProviderData = new BuiltinBlobProviderData();
                    }

                    return(new BinaryCacheEntity
                    {
                        Length = length,
                        RawData = rawData,
                        BinaryPropertyId = binaryPropertyId,
                        FileId = fileId,
                        Context = context
                    });
                }
            }
        }
Exemplo n.º 12
0
        /// <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();
                }
            }
        }
Exemplo n.º 13
0
        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;
                    }
                }
            }
        }
Exemplo n.º 14
0
        /// <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();
            }
        }
Exemplo n.º 15
0
 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();
     }
 }
Exemplo n.º 16
0
        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();
            }
        }
Exemplo n.º 17
0
        //============================================================================ "less roundtrip methods"

        public void InsertNodeAndVersionRows(NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId)
        {
            using (var cmd = new SqlProcedure {
                CommandText = INSERT_NODE_AND_VERSION_ROWS, CommandType = CommandType.Text
            })
            {
                cmd.Parameters.Add("@NodeTypeId", SqlDbType.Int).Value            = nodeData.NodeTypeId;
                cmd.Parameters.Add("@ContentListTypeId", SqlDbType.Int).Value     = (nodeData.ContentListTypeId != 0) ? (object)nodeData.ContentListTypeId : DBNull.Value;
                cmd.Parameters.Add("@ContentListId", SqlDbType.Int).Value         = (nodeData.ContentListId != 0) ? (object)nodeData.ContentListId : DBNull.Value;
                cmd.Parameters.Add("@IsDeleted", SqlDbType.TinyInt).Value         = nodeData.IsDeleted ? 1 : 0;
                cmd.Parameters.Add("@IsInherited", SqlDbType.TinyInt).Value       = nodeData.IsInherited ? 1 : 0;
                cmd.Parameters.Add("@ParentNodeId", SqlDbType.Int).Value          = (nodeData.ParentId > 0) ? (object)nodeData.ParentId : DBNull.Value;
                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 450).Value        = nodeData.Name;
                cmd.Parameters.Add("@DisplayName", SqlDbType.NVarChar, 450).Value = (object)nodeData.DisplayName ?? DBNull.Value;
                cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value        = nodeData.Path;
                cmd.Parameters.Add("@Index", SqlDbType.Int).Value                     = nodeData.Index;
                cmd.Parameters.Add("@Locked", SqlDbType.TinyInt).Value                = nodeData.Locked ? 1 : 0;
                cmd.Parameters.Add("@LockedById", SqlDbType.Int).Value                = (nodeData.LockedById > 0) ? (object)nodeData.LockedById : DBNull.Value;
                cmd.Parameters.Add("@ETag", SqlDbType.VarChar, 50).Value              = nodeData.ETag ?? String.Empty;
                cmd.Parameters.Add("@LockType", SqlDbType.Int).Value                  = nodeData.LockType;
                cmd.Parameters.Add("@LockTimeout", SqlDbType.Int).Value               = nodeData.LockTimeout;
                cmd.Parameters.Add("@LockDate", SqlDbType.DateTime).Value             = nodeData.LockDate;
                cmd.Parameters.Add("@LockToken", SqlDbType.VarChar, 50).Value         = nodeData.LockToken ?? String.Empty;
                cmd.Parameters.Add("@LastLockUpdate", SqlDbType.DateTime).Value       = nodeData.LastLockUpdate;
                cmd.Parameters.Add("@NodeCreationDate", SqlDbType.DateTime).Value     = nodeData.NodeCreationDate;
                cmd.Parameters.Add("@NodeCreatedById", SqlDbType.Int).Value           = nodeData.NodeCreatedById;
                cmd.Parameters.Add("@NodeModificationDate", SqlDbType.DateTime).Value = nodeData.NodeModificationDate;
                cmd.Parameters.Add("@NodeModifiedById", SqlDbType.Int).Value          = nodeData.NodeModifiedById;

                cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value      = nodeData.Version.Major;
                cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value      = nodeData.Version.Minor;
                cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value           = nodeData.Version.Status;
                cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value     = nodeData.CreationDate;
                cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value           = nodeData.CreatedById;
                cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.ModificationDate;
                cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value          = nodeData.ModifiedById;

                using (var reader = cmd.ExecuteReader())
                {
                    reader.Read();
                    nodeData.Id               = Convert.ToInt32(reader[0]);
                    nodeData.VersionId        = Convert.ToInt32(reader[1]);
                    nodeData.NodeTimestamp    = SqlProvider.GetLongFromBytes((byte[])reader[2]);
                    nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[3]);

                    lastMajorVersionId = reader.GetSafeInt32(4);
                    lastMinorVersionId = reader.GetSafeInt32(5);
                }
            }
        }
Exemplo n.º 18
0
        private static void WriteSqlFileStream(Stream stream, int binaryPropertyId, FileStreamData fileStreamData = null)
        {
            SqlProcedure cmd = null;

            try
            {
                //If we did not receive a path and transaction context, retrieve it now from the database.
                if (fileStreamData == null)
                {
                    cmd = new SqlProcedure {
                        CommandText = UPDATE_BINARY_PROPERTY_FILESTREAM, CommandType = CommandType.Text
                    };
                    cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId;

                    string path;
                    byte[] transactionContext;

                    //Set Stream column to NULL 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;
                    }

                    fileStreamData = new FileStreamData {
                        Path = path, TransactionContext = transactionContext
                    };
                }

                stream.Seek(0, SeekOrigin.Begin);

                //Write data using SqlFileStream
                using (var fs = new SqlFileStream(fileStreamData.Path, fileStreamData.TransactionContext, FileAccess.Write))
                {
                    //default buffer size is 4096
                    stream.CopyTo(fs);
                }
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
        }
Exemplo n.º 19
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);
        }
Exemplo n.º 20
0
        private SqlProcedure CreateDeleteCommand(KeyValuePair <int, string>[] values)
        {
            var sql = String.Format(DELETESQL, String.Join(", ", Enumerable.Range(0, _values.Count).Select(x => PROPERTYTYPE_PARAMPREFIX + x)));
            var cmd = new SqlProcedure {
                CommandText = sql, CommandType = CommandType.Text
            };

            cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = _versionId;

            for (int i = 0; i < values.Length; i++)
            {
                cmd.Parameters.Add(PROPERTYTYPE_PARAMPREFIX + i, SqlDbType.Int).Value = values[i].Key;
            }

            return(cmd);
        }
Exemplo n.º 21
0
        // ReSharper disable once SuggestBaseTypeForParameter
        private static SqlProcedure GetWriteChunkToSqlProcedure(BlobStorageContext context, long offset, byte[] buffer)
        {
            // This is a helper method to aid both the sync and async version of the write chunk operation.

            var cmd = new SqlProcedure {
                CommandText = UpdateStreamWriteChunkScript, CommandType = CommandType.Text
            };

            cmd.Parameters.Add("@FileId", SqlDbType.Int).Value         = context.FileId;
            cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value      = context.VersionId;
            cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = context.PropertyTypeId;
            cmd.Parameters.Add("@Data", SqlDbType.VarBinary).Value     = buffer;
            cmd.Parameters.Add("@Offset", SqlDbType.BigInt).Value      = offset;

            return(cmd);
        }
Exemplo n.º 22
0
        /*============================================================================ Version Insert/Update */

        //public int InsertVersionRow(NodeData nodeData)
        //{
        //    SqlProcedure cmd = null;
        //    SqlDataReader reader = null;
        //    int result = 0;
        //    try
        //    {
        //        cmd = new SqlProcedure { CommandText = "proc_Version_Insert" };
        //        cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value = nodeData.Id;
        //        cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major;
        //        cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor;
        //        cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value = nodeData.Version.Status;
        //        cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value = nodeData.CreationDate;
        //        cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value = nodeData.CreatedById;
        //        cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.ModificationDate;
        //        cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value = nodeData.ModifiedById;

        //        reader = cmd.ExecuteReader();
        //        while (reader.Read())
        //        {
        //            // SELECT VersionId, [Timestamp] FROM Versions WHERE VersionId = @@IDENTITY
        //            result = Convert.ToInt32(reader[0]);
        //            nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]);
        //        }
        //    }
        //    finally
        //    {
        //        if (reader != null && !reader.IsClosed)
        //            reader.Close();
        //        cmd.Dispose();
        //    }
        //    return result;
        //}
        public void UpdateVersionRow(NodeData nodeData, out int lastMajorVersionId, out int lastMinorVersionId)
        {
            SqlProcedure  cmd    = null;
            SqlDataReader reader = null;

            lastMajorVersionId = 0;
            lastMinorVersionId = 0;

            try
            {
                cmd = new SqlProcedure {
                    CommandText = "proc_Version_Update"
                };
                cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = nodeData.VersionId;
                cmd.Parameters.Add("@NodeId", SqlDbType.Int).Value    = nodeData.Id;
                if (nodeData.IsPropertyChanged("Version"))
                {
                    cmd.Parameters.Add("@MajorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Major;
                    cmd.Parameters.Add("@MinorNumber", SqlDbType.SmallInt).Value = nodeData.Version.Minor;
                    cmd.Parameters.Add("@Status", SqlDbType.SmallInt).Value      = nodeData.Version.Status;
                }
                cmd.Parameters.Add("@CreationDate", SqlDbType.DateTime).Value     = nodeData.VersionCreationDate;
                cmd.Parameters.Add("@CreatedById", SqlDbType.Int).Value           = nodeData.VersionCreatedById;
                cmd.Parameters.Add("@ModificationDate", SqlDbType.DateTime).Value = nodeData.VersionModificationDate;
                cmd.Parameters.Add("@ModifiedById", SqlDbType.Int).Value          = nodeData.VersionModifiedById;
                cmd.Parameters.Add("@ChangedData", SqlDbType.NText).Value         = JsonConvert.SerializeObject(nodeData.ChangedData);

                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    // SELECT [Timestamp] FROM Versions WHERE VersionId = @VersionId
                    nodeData.NodeTimestamp    = SqlProvider.GetLongFromBytes((byte[])reader[0]);
                    nodeData.VersionTimestamp = SqlProvider.GetLongFromBytes((byte[])reader[1]);

                    lastMajorVersionId = reader.GetSafeInt32(2);
                    lastMinorVersionId = reader.GetSafeInt32(3);
                }
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                cmd.Dispose();
            }
        }
Exemplo n.º 23
0
        /// <summary>
        /// Deletes file records that are marked as deleted from the metadata database and also from the blob storage.
        /// </summary>
        /// <returns>Whether there was at least one row that was deleted.</returns>
        public bool CleanupFiles()
        {
            using (var proc = new SqlProcedure {
                CommandText = CleanupFileScript, CommandType = CommandType.Text
            })
            {
                proc.CommandType = CommandType.Text;

                try
                {
                    var deleted = false;
                    using (var reader = proc.ExecuteReader())
                    {
                        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 (reader.Read())
                        {
                            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 = BlobStorageBase.GetProvider(providerName);
                        var ctx      = new BlobStorageContext(provider, providerData)
                        {
                            VersionId = 0, PropertyTypeId = 0, FileId = fileId, Length = size
                        };

                        ctx.Provider.Delete(ctx);
                    }
                    return(deleted);
                }
                catch (Exception ex)
                {
                    throw new DataException("Error during binary cleanup.", ex);
                }
            }
        }
Exemplo n.º 24
0
        /// <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();
            }
        }
Exemplo n.º 25
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);
            }
        }
Exemplo n.º 26
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);
        }
Exemplo n.º 27
0
 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();
             }
         }
     }
 }
Exemplo n.º 28
0
        internal static byte[] ReadRandom(BlobStorageContext context, long offset, int count)
        {
            var commandText = LoadBinaryFragmentScript;

            byte[] result;

            using (var cmd = new SqlProcedure {
                CommandText = commandText
            })
            {
                cmd.Parameters.Add("@FileId", SqlDbType.Int).Value      = context.FileId;
                cmd.Parameters.Add("@Position", SqlDbType.BigInt).Value = offset + 1;
                cmd.Parameters.Add("@Count", SqlDbType.Int).Value       = count;
                cmd.CommandType = CommandType.Text;

                result = (byte[])cmd.ExecuteScalar();
            }

            return(result);
        }
        /// <summary>
        /// Returns whether the FileStream feature is enabled in the blob provider or not.
        /// </summary>
        /// <returns></returns>
        public bool IsFilestreamEnabled()
        {
            bool         fsEnabled;
            const string sql = "SELECT COUNT(name) FROM sys.columns WHERE Name = N'FileStream' and Object_ID = Object_ID(N'Files')";

            using (var pro = new SqlProcedure {
                CommandText = sql, CommandType = CommandType.Text
            })
            {
                try
                {
                    fsEnabled = Convert.ToInt32(pro.ExecuteScalar()) > 0;
                }
                catch (Exception ex)
                {
                    SnLog.WriteException(ex);
                    fsEnabled = false;
                }
            }
            return(fsEnabled);
        }
Exemplo n.º 30
0
        // ReSharper disable once SuggestBaseTypeForParameter
        private static SqlProcedure GetWriteChunkToSqlProcedure(BlobStorageContext context, long offset, byte[] buffer)
        {
            // This is a helper method to aid both the sync and async version of the write chunk operation.

            // If Filestream is enabled but not used, we need to set it NULL
            // when inserting the chunk to the regular Stream column
            var cmdText = BlobStorage.FileStreamEnabled
                ? UpdateStreamWriteChunkFsScript
                : UpdateStreamWriteChunkScript;

            var cmd = new SqlProcedure {
                CommandText = cmdText, CommandType = CommandType.Text
            };

            cmd.Parameters.Add("@FileId", SqlDbType.Int).Value         = context.FileId;
            cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value      = context.VersionId;
            cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = context.PropertyTypeId;
            cmd.Parameters.Add("@Data", SqlDbType.VarBinary).Value     = buffer;
            cmd.Parameters.Add("@Offset", SqlDbType.BigInt).Value      = offset;

            return(cmd);
        }
Exemplo n.º 31
0
        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();
            }
        }
Exemplo n.º 32
0
        /*============================================================================ 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();
            }
        }
Exemplo n.º 33
0
        //protected internal override void StoreBackupStream(string backupFilePath, IndexBackup2 backup, BackupProgress progress)
        //{
        //    var fileLength = new FileInfo(backupFilePath).Length;

        //    using (var stream = new FileStream(backupFilePath, FileMode.Open))
        //    {
        //        using (var reader = new BinaryReader(stream))
        //        {
        //            InitializeNewStream(backup);

        //            progress.Type = BackupProgressType.Storing;
        //            progress.Message = "Storing backup";
        //            progress.MaxValue = fileLength;

        //            var offset = 0L;
        //            while (offset < fileLength)
        //            {
        //                using (var writeCommand = CreateWriteCommand(backup))
        //                {
        //                    progress.Value = offset;
        //                    progress.NotifyChanged();

        //                    var remnant = fileLength - offset;
        //                    var length = remnant < BUFFERSIZE ? Convert.ToInt32(remnant) : BUFFERSIZE;
        //                    var buffer = reader.ReadBytes(length);
        //                    writeCommand.Parameters["@Buffer"].Value = buffer;
        //                    writeCommand.Parameters["@Offset"].Value = offset;
        //                    writeCommand.Parameters["@Length"].Value = length;
        //                    writeCommand.ExecuteNonQuery();
        //                    offset += BUFFERSIZE;
        //                }
        //            }
        //            progress.Value = fileLength;
        //            progress.NotifyChanged();
        //        }
        //    }
        //}
        private SqlProcedure CreateWriteCommand(IndexBackup backup)
        {
            var sql = String.Format("UPDATE {0} SET [BackupFile].WRITE(@Buffer, @Offset, @Length) WHERE BackupNumber = @BackupNumber", backup.TableName);
            var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text };
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@BackupNumber", SqlDbType.Int)).Value = backup.BackupNumber;
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Offset", SqlDbType.BigInt));
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Length", SqlDbType.BigInt));
            cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Buffer", SqlDbType.VarBinary));
            return cmd;
        }
Exemplo n.º 34
0
        protected internal override NodeHead.NodeVersion[] GetNodeVersions(int nodeId)
        {
            SqlProcedure cmd = null;
            SqlDataReader reader = null;
            try
            {
                string commandString = @"
                    SELECT VersionId, MajorNumber, MinorNumber, Status
                    FROM Versions
                    WHERE NodeId = @NodeId
                    ORDER BY MajorNumber, MinorNumber
                ";
                cmd = new SqlProcedure { CommandText = commandString };
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@NodeId", SqlDbType.NVarChar, 450).Value = nodeId;
                reader = cmd.ExecuteReader();

                List<NodeHead.NodeVersion> versionList = new List<NodeHead.NodeVersion>();

                while (reader.Read())
                {
                    var versionId = reader.GetInt32(0);
                    var major = reader.GetInt16(1);
                    var minor = reader.GetInt16(2);
                    var statusCode = reader.GetInt16(3);

                    var versionNumber = new VersionNumber(major, minor, (VersionStatus)statusCode);

                    versionList.Add(new NodeHead.NodeVersion(versionNumber, versionId));
                }

                return versionList.ToArray();

            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                if (cmd != null)
                    cmd.Dispose();
            }


        }
Exemplo n.º 35
0
        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;
        }
Exemplo n.º 36
0
        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;
        }
Exemplo n.º 37
0
 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();
     }
 }
Exemplo n.º 38
0
        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();
        }
Exemplo n.º 39
0
        //====================================================== Custom database script support

        protected internal override IDataProcedure CreateDataProcedureInternal(string commandText)
        {
            var proc = new SqlProcedure();
            proc.CommandText = commandText;
            return proc;
        }
Exemplo n.º 40
0
 private IDataProcedure CreateReadCommand(IndexBackup backup)
 {
     var sql = String.Format("SELECT SUBSTRING([BackupFile], @Offset, @Length) FROM {0} WHERE BackupNumber = @BackupNumber", backup.TableName);
     var cmd = new SqlProcedure { CommandText = sql, CommandType = System.Data.CommandType.Text };
     cmd.CommandType = CommandType.Text;
     cmd.Parameters.Add(new SqlParameter("@BackupNumber", SqlDbType.Int)).Value = backup.BackupNumber;
     cmd.Parameters.Add(new SqlParameter("@Offset", SqlDbType.BigInt));
     cmd.Parameters.Add(new SqlParameter("@Length", SqlDbType.BigInt));
     return cmd;
 }
Exemplo n.º 41
0
        protected internal override IEnumerable<IndexDocumentData> LoadIndexDocumentByVersionId(IEnumerable<int> versionId)
        {
            var fi = 0;
            var listCount = versionId.Count();
            var result = new List<IndexDocumentData>();

            while (fi * DOCSFRAGMENTSIZE < listCount)
            {
                var docsSegment = versionId.Skip(fi * DOCSFRAGMENTSIZE).Take(DOCSFRAGMENTSIZE).ToArray();
                var paramNames = docsSegment.Select((s, i) => "@vi" + i.ToString()).ToArray();
                var where = String.Concat("WHERE V.VersionId IN (", string.Join(", ", paramNames), ")");

                SqlProcedure cmd = null;
                var retry = 0;
                while (retry < 15)
                {
                    try
                    {
                        cmd = new SqlProcedure { CommandText = LOADINDEXDOCUMENTSCRIPT + where };
                        cmd.CommandType = CommandType.Text;
                        for (var i = 0; i < paramNames.Length; i++)
                        {
                            cmd.Parameters.AddWithValue(paramNames[i], docsSegment[i]);
                        }

                        using (var reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                result.Add(GetIndexDocumentDataFromReader(reader));
                        }
                        break;
                    }
                    catch (Exception ex)
                    {
                        Logger.WriteException(ex);
                        retry++;
                        System.Threading.Thread.Sleep(1000);
                    }
                    finally
                    {
                        if (cmd != null)
                            cmd.Dispose();
                    }
                }

                fi++;
            }

            return result;
        }
Exemplo n.º 42
0
        protected internal override IEnumerable<int> QueryNodesByTypeAndPathAndName(int[] nodeTypeIds, string pathStart, bool orderByPath, string name)
        {
            var sql = new StringBuilder("SELECT NodeId FROM Nodes WHERE");
            var first = true;

            if (pathStart != null)
            {
                sql.Append(" Path LIKE '");
                sql.Append(pathStart);
                if (!pathStart.EndsWith(RepositoryPath.PathSeparator))
                    sql.Append(RepositoryPath.PathSeparator);
                sql.Append("%'");
                first = false;
            }

            if (name != null)
            {
                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(")");

                first = false;
            }

            if (orderByPath)
                sql.AppendLine().Append("ORDER BY Path");

            var cmd = new SqlProcedure { CommandText = sql.ToString(), CommandType = CommandType.Text };
            SqlDataReader reader = null;
            var result = new List<int>();
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                    result.Add(reader.GetSafeInt32(0));
                return result;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                cmd.Dispose();
            }
        }
Exemplo n.º 43
0
 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();
     }
 }
Exemplo n.º 44
0
        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();
            }
        }
Exemplo n.º 45
0
 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();
     }
 }
Exemplo n.º 46
0
        //====================================================== Powershell provider

        protected internal override int InitializeStagingBinaryData(int versionId, int propertyTypeId, string fileName, long fileSize)
        {
            var sql = @"
                INSERT INTO StagingBinaryProperties ( VersionId,  PropertyTypeId,  ContentType,  FileNameWithoutExtension,  Extension,  Size,  Stream) VALUES
                                                    (@VersionId, @PropertyTypeId, @ContentType, @FileNameWithoutExtension, @Extension, @Size,    0x00)
                SELECT @@IDENTITY";
            using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
            {
                var fName = Path.GetFileNameWithoutExtension(fileName);
                var ext = Path.GetExtension(fileName);
                var mime =  MimeTable.GetMimeType(ext.ToLower(CultureInfo.InvariantCulture));
                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("@ContentType", SqlDbType.VarChar, 50)).Value = mime;
                cmd.Parameters.Add(new SqlParameter("@FileNameWithoutExtension", SqlDbType.NVarChar, 450)).Value = fName;
                cmd.Parameters.Add(new SqlParameter("@Extension", SqlDbType.NVarChar, 450)).Value = ext;
                cmd.Parameters.Add(new SqlParameter("@Size", SqlDbType.BigInt)).Value = fileSize;
                var result = cmd.ExecuteScalar();
                return Convert.ToInt32(result);
            }
        }
Exemplo n.º 47
0
        const int BUFFERSIZE = 1024 * 128; // * 512; // * 64; // * 8;

        protected internal override IndexBackup LoadLastBackup()
        {
            var sql = @"
SELECT [IndexBackupId], [BackupNumber], [BackupDate], [ComputerName], [AppDomain],
        DATALENGTH([BackupFile]) AS [BackupFileLength], [RowGuid], [Timestamp]
    FROM [IndexBackup] WHERE IsActive != 0
SELECT [IndexBackupId], [BackupNumber], [BackupDate], [ComputerName], [AppDomain],
        DATALENGTH([BackupFile]) AS [BackupFileLength], [RowGuid], [Timestamp]
    FROM [IndexBackup2] WHERE IsActive != 0
";
            IndexBackup result = null;
            using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
            {
                using (var reader = cmd.ExecuteReader())
                {
                    do
                    {
                        while (reader.Read())
                            result = GetBackupFromReader(reader);
                    } while (reader.NextResult());
                }
            }
            return result;
        }
Exemplo n.º 48
0
        protected internal override byte[] LoadBinaryFragment(int binaryPropertyId, long position, int count)
        {
            var commandText = @"
            SELECT
                SUBSTRING(Stream, @Position, @Count)
            FROM
	            dbo.BinaryProperties
            WHERE
                BinaryPropertyId = @BinaryPropertyId
            ";

            byte[] result;

            using (var cmd = new SqlProcedure { CommandText = commandText })
            {
                cmd.Parameters.Add("@BinaryPropertyId", SqlDbType.Int).Value = binaryPropertyId;
                cmd.Parameters.Add("@Position", SqlDbType.BigInt).Value = position + 1;
                cmd.Parameters.Add("@Count", SqlDbType.Int).Value = count;
                cmd.CommandType = CommandType.Text;

                result = (byte[])cmd.ExecuteScalar();
            }

            return result;
        }
Exemplo n.º 49
0
 protected internal override IndexDocumentData LoadIndexDocumentByVersionId(int versionId)
 {
     using (var cmd = new SqlProcedure { CommandText = LOADINDEXDOCUMENTSCRIPT + "WHERE V.VersionId = @VersionId" })
     {
         cmd.CommandType = CommandType.Text;
         cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
         using (var reader = cmd.ExecuteReader())
         {
             if (reader.Read())
                 return GetIndexDocumentDataFromReader(reader);
             return null;
         }
     }
 }
Exemplo n.º 50
0
        protected internal override VersionNumber[] GetVersionNumbers(string path)
        {
            List<VersionNumber> versions = new List<VersionNumber>();
            SqlProcedure cmd = null;
            SqlDataReader reader = null;
            try
            {
                cmd = new SqlProcedure { CommandText = "proc_VersionNumbers_GetByPath" };
                cmd.Parameters.Add("@Path", SqlDbType.NVarChar, 450).Value = path;
                reader = cmd.ExecuteReader();

                int majorNumberIndex = reader.GetOrdinal("MajorNumber");
                int minorNumberIndex = reader.GetOrdinal("MinorNumber");
                int statusIndex = reader.GetOrdinal("Status");

                while (reader.Read())
                {
                    versions.Add(new VersionNumber(
                        reader.GetInt32(majorNumberIndex),
                        reader.GetInt32(minorNumberIndex),
                        (VersionStatus)reader.GetInt32(statusIndex)));
                }
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                cmd.Dispose();
            }
            return versions.ToArray();
        }
Exemplo n.º 51
0
 public override string GetNameOfLastNodeWithNameBase(int parentId, string namebase, string extension)
 {
     var cmd = new SqlProcedure { CommandText = "SELECT TOP 1 Name FROM Nodes WHERE ParentNodeId=@ParentId AND Name LIKE @Name+'(%)' + @Extension ORDER BY LEN(Name) DESC, Name DESC", CommandType = CommandType.Text };
     cmd.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId;
     cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = namebase;
     cmd.Parameters.Add("@Extension", SqlDbType.NVarChar).Value = extension;
     try
     {
         var lastName = (string)cmd.ExecuteScalar();
         return lastName;
     }
     finally
     {
         if (cmd != null)
             cmd.Dispose();
     }
 }
Exemplo n.º 52
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;
            }
        }
Exemplo n.º 53
0
 protected override bool NodeExistsInDatabase(string path)
 {
     var cmd = new SqlProcedure { CommandText = "SELECT COUNT(*) FROM Nodes WHERE Path = @Path", CommandType = CommandType.Text };
     cmd.Parameters.Add("@Path", SqlDbType.NVarChar, PathMaxLength).Value = path;
     try
     {
         var count = (int)cmd.ExecuteScalar();
         return count > 0;
     }
     finally
     {
         if (cmd != null)
             cmd.Dispose();
     }
 }
Exemplo n.º 54
0
 protected internal override string LoadTextPropertyValue(int versionId, int propertyTypeId)
 {
     SqlProcedure cmd = null;
     try
     {
         cmd = new SqlProcedure { CommandText = "proc_TextProperty_LoadValue" };
         cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = versionId;
         cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
         var s = (string)cmd.ExecuteScalar();
         return s;
     }
     finally
     {
         cmd.Dispose();
     }
 }
Exemplo n.º 55
0
        protected internal override IEnumerable<int> QueryNodesByTypeAndPathAndProperty(int[] nodeTypeIds, string pathStart, bool orderByPath, List<QueryPropertyData> properties)
        {
            var sql = new StringBuilder("SELECT NodeId FROM SysSearchWithFlatsView WHERE");
            var first = true;

            if (pathStart != null)
            {
                sql.Append(" Path LIKE '");
                sql.Append(pathStart);
                if (!pathStart.EndsWith(RepositoryPath.PathSeparator))
                    sql.Append(RepositoryPath.PathSeparator);
                sql.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(")");

                first = false;
            }

            if (properties != null)
            {
                foreach (var queryPropVal in properties)
                {
                    if (string.IsNullOrEmpty(queryPropVal.PropertyName))
                        continue;
                    
                    var pt = PropertyType.GetByName(queryPropVal.PropertyName);
                    var pm = pt == null ? null : pt.GetDatabaseInfo();
                    var colName = pm == null ? GetNodeAttributeName(queryPropVal.PropertyName) : pm.ColumnName;
                    var dt = pt == null ? GetNodeAttributeType(queryPropVal.PropertyName) : pt.DataType;

                    if (!first)
                        sql.Append(" AND");

                    if (queryPropVal.Value != null)
                    {
                        switch (dt)
                        {
                            case DataType.DateTime:
                            case DataType.String:
                                switch (queryPropVal.QueryOperator)
                                {
                                    case Operator.Equal:
                                        sql.AppendFormat(" {0} = '{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.Contains:
                                        sql.AppendFormat(" {0} LIKE '%{1}%'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.StartsWith:
                                        sql.AppendFormat(" {0} LIKE '{1}%'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.EndsWith:
                                        sql.AppendFormat(" {0} LIKE '%{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.GreaterThan:
                                        sql.AppendFormat(" {0} > '{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.GreaterThanOrEqual:
                                        sql.AppendFormat(" {0} >= '{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.LessThan:
                                        sql.AppendFormat(" {0} < '{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.LessThanOrEqual:
                                        sql.AppendFormat(" {0} <= '{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.NotEqual:
                                        sql.AppendFormat(" {0} <> '{1}'", colName, queryPropVal.Value);
                                        break;
                                    default:
                                        throw new InvalidOperationException(string.Format("Direct query not implemented (data type: {0}, operator: {1})", dt, queryPropVal.QueryOperator));
                                }
                                break;
                            case DataType.Int:
                            case DataType.Currency:
                                switch (queryPropVal.QueryOperator)
                                {
                                    case Operator.Equal:
                                        sql.AppendFormat(" {0} = {1}", colName, queryPropVal.Value);
                                        break;
                                    case Operator.GreaterThan:
                                        sql.AppendFormat(" {0} > '{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.GreaterThanOrEqual:
                                        sql.AppendFormat(" {0} >= '{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.LessThan:
                                        sql.AppendFormat(" {0} < '{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.LessThanOrEqual:
                                        sql.AppendFormat(" {0} <= '{1}'", colName, queryPropVal.Value);
                                        break;
                                    case Operator.NotEqual:
                                        sql.AppendFormat(" {0} <> '{1}'", colName, queryPropVal.Value);
                                        break;
                                    default:
                                        throw new InvalidOperationException(string.Format("Direct query not implemented (data type: {0}, operator: {1})", dt, queryPropVal.QueryOperator));
                                }
                                break;
                            default:
                                throw new NotSupportedException("Not supported direct query dataType: " + dt);
                        }
                    }
                    else
                    {
                        sql.Append(" IS NULL");
                    }
                }
            }

            if (orderByPath)
                sql.AppendLine().Append("ORDER BY Path");

            var cmd = new SqlProcedure { CommandText = sql.ToString(), CommandType = CommandType.Text };
            SqlDataReader reader = null;
            var result = new List<int>();
            try
            {
                reader = cmd.ExecuteReader();
                while (reader.Read())
                    result.Add(reader.GetSafeInt32(0));
                return result;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                cmd.Dispose();
            }
        }
Exemplo n.º 56
0
 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();
     }
 }
Exemplo n.º 57
0
        protected internal override IEnumerable<int> QueryNodesByReferenceAndType(string referenceName, int referredNodeId, int[] allowedTypeIds)
        {
            if (referenceName == null)
                throw new ArgumentNullException("referenceName");
            if (referenceName.Length == 0)
                throw new ArgumentException("Argument referenceName cannot be empty.", "referenceName");
            var referenceProperty = ActiveSchema.PropertyTypes[referenceName];
            if (referenceProperty == null)
                throw new ArgumentException("PropertyType is not found: " + referenceName, "referenceName");
            var referencePropertyId = referenceProperty.Id;

            string sql;
            if (allowedTypeIds == null || allowedTypeIds.Length == 0)
            {
                sql = @"SELECT V.NodeId FROM ReferenceProperties R
	JOIN Versions V ON R.VersionId = V.VersionId
	JOIN Nodes N ON V.VersionId = N.LastMinorVersionId
WHERE R.PropertyTypeId = @PropertyTypeId AND R.ReferredNodeId = @ReferredNodeId";
            }
            else
            {
                sql = String.Format(@"SELECT N.NodeId FROM ReferenceProperties R
	JOIN Versions V ON R.VersionId = V.VersionId
	JOIN Nodes N ON V.VersionId = N.LastMinorVersionId
WHERE R.PropertyTypeId = @PropertyTypeId AND R.ReferredNodeId = @ReferredNodeId AND N.NodeTypeId IN ({0})", String.Join(", ", allowedTypeIds));
            }

            using (var cmd = new SqlProcedure { CommandText = sql, CommandType = CommandType.Text })
            {
                cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = referencePropertyId;
                cmd.Parameters.Add("@ReferredNodeId", SqlDbType.Int).Value = referredNodeId;
                var result = new List<int>();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        result.Add(reader.GetSafeInt32(0));
                    return result;
                }
            }
        }
Exemplo n.º 58
0
        protected internal override int InstanceCount(int[] nodeTypeIds)
        {
            var sql = new StringBuilder("SELECT COUNT(*) FROM Nodes WHERE NodeTypeId");
            if (nodeTypeIds.Length == 1)
                sql.Append(" = ").Append(nodeTypeIds[0]);
            else
                sql.Append(" IN (").Append(String.Join(", ", nodeTypeIds)).Append(")");

            var cmd = new SqlProcedure { CommandText = sql.ToString(), CommandType = CommandType.Text }; ;
            try
            {
                var count = (int)cmd.ExecuteScalar();
                return count;
            }
            finally
            {
                cmd.Dispose();
            }

        }
Exemplo n.º 59
0
 public override int GetLastActivityId()
 {
     using (var cmd = new SqlProcedure { CommandText = GETLASTACTIVITYIDSCRIPT, CommandType = CommandType.Text })
     {
         var x = cmd.ExecuteScalar();
         if (x == DBNull.Value)
             return 0;
         return Convert.ToInt32(x);
     }
 }
Exemplo n.º 60
0
        protected internal override BinaryCacheEntity LoadBinaryCacheEntity(int nodeVersionId, int propertyTypeId)
        {
            var commandText = string.Format(@"
            SELECT
	            Size,
	            CASE
		            WHEN Size < {0} THEN Stream
		            ELSE null
	            END AS Stream,
                BinaryPropertyId
            FROM
	            dbo.BinaryProperties
            WHERE
                VersionId = @VersionId
                AND
                PropertyTypeId = @PropertyTypeId
            ", RepositoryConfiguration.CachedBinarySize);

            using (var cmd = new SqlProcedure { CommandText = commandText })
            {
                cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value = nodeVersionId;
                cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value = propertyTypeId;
                cmd.CommandType = CommandType.Text;
                var reader = cmd.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult);
                if (reader.HasRows && reader.Read())
                {
                    long length = reader.GetInt64(0);
                    byte[] rawData;
                    if (reader.IsDBNull(1))
                        rawData = null;
                    else
                        rawData = (byte[])reader.GetValue(1);
                    int binaryPropertyId = reader.GetInt32(2);
                    reader.Close();

                    return new BinaryCacheEntity()
                    {
                        Length = length,
                        RawData = rawData,
                        BinaryPropertyId = binaryPropertyId
                    };
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
        }