Finding ancestors in a SQL-Server’s HierarchyId tree

Imagine this table, filled with more than a million records containing a 7-levels tree:

CREATE TABLE HierTest (
	Id		BIGINT PRIMARY KEY IDENTITY(1,1),
	Path	HIERARCHYID NOT NULL UNIQUE,
	Name	NVARCHAR(100) NOT NULL
)

Now imagine there’s a node with Id=1839861 and you want to get all of its ancestors. I know of two ways to do this (sources: Simon Ince’s blog post and StackOverflow):

DECLARE @Hiernode HIERARCHYID;
SELECT @Hiernode=[ht].[Path] FROM [TestSchema].[HierTest] AS [ht] WHERE [ht].[Id]=1839861;
SELECT
	*--,
	--[ht].[Path].ToString() AS PathString
FROM [TestSchema].[HierTest] AS [ht]
WHERE @Hiernode.[IsDescendantOf]([ht].[Path])=1
ORDER BY [ht].[Path];

and

WITH Tree(Id, Name, Path, AncestorPath) AS(
	SELECT
		[ht].[Id],
		[ht].[Name],
		[ht].[Path],
		[ht].[Path].GetAncestor(1) AS 'AncestorPath'
	FROM [TestSchema].[HierTest] AS [ht]
	WHERE [ht].[Id]=1839861

	UNION ALL

	SELECT
		[ht].[Id],
		[ht].[Name],
		[ht].[Path],
		[ht].[Path].GetAncestor(1) AS 'AncestorPath'
	FROM [TestSchema].[HierTest] AS [ht]
	INNER JOIN [Tree] t ON [ht].[Path] = [t].[AncestorPath]
)
SELECT
	[t].[Id],
	[t].[Name],
	[t].[Path]
FROM [Tree] t
ORDER BY [t].[Path]

Measuring the execution time shows the “Take II” to be more than 100 times faster than “Take I”!!

I’ll update this post with what I’ll learn in the future.


Download the scripts from: Hierarchy Test.

Windows TCP 49154 – What is it!!?

Just had a tough struggle with my “MUTE” friend – Windows Server – trying to understand what’s running behind tcp/49154 on my server, accepting connection.

There are many ALLOW ALL PROFILES WITH ANY PORTS listed on windows’ firewall by default and thinking of them as being secure is not right.

File Server Remote Management (WMI-In) was listening on that port! Disabled the rule and now I’m in peace again! 😀

 

Use Powershell 5 to start automatic services not running

There are times when windows doesn’t start some automatic services. In situations like this, the best way would be a clean fresh windows installation; but what if you can’t re-install the OS?

This script will be very handy when you can’t re-install the Windows. It runs those nasty services 🙂

clear
$svcs=Get-Service | Where-Object {$_.Status -eq 'stopped' -and $_.StartType -like "*Automatic*"}
foreach ($svc in $svcs){
    Write-Host Starting $svc.Name
    Start-Service $svc
}

Linux – Find last 10 modified folders.

This post will be updated, but for the mean time this should work:

find . -maxdepth 2 -type d -exec stat -c "%y %n" {} \; | sort | tail -n 20

Update 1:

This is much faster:

find . -maxdepth 2 -type d -printf "%TY-%Tm-%Td %TH:%TM:%TS %TZ %p\n" | sort | tail -n 20

🙂

%Path% variable analyzer

Use this in Powershell:

 $env:Path.Split(";".ToCharArray()) | Select-Object @{Name="Path";Expression={$_}},@{Name="Exists";Expression={[System.IO.Directory]::Exists($_)}}

کیبورد فارسی برای همه ویندوز ها

قبلاً در مورد کیبورد فارسی یک پست نوشته بودم. اون کیبورد فارسی یک ایراد کوچک داشت که توی این نسخه جدید برطرف شده. ایراد این بود که اگر کیبورد در حالت فارسی باشد و Caps Lock هم روشن باشد، نمیتوان با استفاده از Shift، کاراکترهایی مانند % ( )  و اینها را تایپ کرد؛ که خوب حالا می شود!!

تغییر دیگری که ایجاد شده، استفاده از Zero-Width Space به جای Zero-Width Non-Joiner در Shift+Space است که کاربردش دقیقاً طراحی شده برای عبارتهایی مثل این: “در سده‌ی گذشته” کاراکتری که بدون اینکه فاصله ایجاد کند، باعث شود حروف به هم نچسبند.

حالا این کیبورد جدید رو از اینجا دانلود کنید: Farsi Keyboard Layout Patch

اطلاعات شهرهای ایران

فایل پیوست شامل فهرست نام و مشخصات تقسیمات کشوری در ایران، مشخصاً شهر ها، از دید پورتال وزارت کشور است.

Iran Cities (2015-SEP-01).xlsx

Make windows files/folders permissions right

There are times on windows when you migrate your files to a new system and you will face the corrupted permission-sets on your files or folders. These 4 cmd commands will help you gain your control over your files again 🙂

<Drive>:
CD \
TAKEOWN /F * /A /R 1 > NUL
ICACLS * /reset /T /Q

Just remember to create a batch file containing those lines above. 🙂

WPF Automatic FlowDirection

When developing WPF for a BiDi world – the world I live in now – I think it’s important to show the RIGHT paragraph settings for text to user.

The following Converter helps achieve such ability automatically based on text.

using System;
using System.Linq;
using System.Text.RegularExpressions;
using System.Windows;
using System.Windows.Data;

namespace DNE.WPF.Converters {

	/// <summary>
	/// This is a Helper Converter.<br/>
	/// It helps determine the <see cref="FlowDirection"/> property, automatically.
	/// </summary>
	public class TextToFlowDirectionConverter : IValueConverter {

		#region IValueConverter Members

		/// <summary>
		/// 
		/// </summary>
		/// <param name="value"></param>
		/// <param name="targetType"></param>
		/// <param name="parameter"></param>
		/// <param name="culture"></param>
		/// <returns></returns>
		public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture) {
			string format = parameter as string;
			if (!string.IsNullOrEmpty(format)) {
				return IsRTL(string.Format(culture, format, value)) ? FlowDirection.RightToLeft : FlowDirection.LeftToRight;
			}else{
				return IsRTL(string.Format(culture, "{0}", value)) ? FlowDirection.RightToLeft : FlowDirection.LeftToRight;
			}
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="value"></param>
		/// <param name="targetType"></param>
		/// <param name="parameter"></param>
		/// <param name="culture"></param>
		/// <returns></returns>
		public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture) {
			return null;
		}

		#endregion

		private bool IsRTL(string input) {
			bool result = false;
			Regex rPersianChars = new Regex("^(?:[^a-z\u060C\u061B\u061F\u0621-\u064A\u064B-\u0652\u0660-\u066D\u0670-\u06F9\uFB50-\uFBB1\uFBD3-\uFD3F\uFDF2\uFDF4\uFDFA-\uFDFB\uFE70-\uFE72\uFE74\uFE76-\uFEFC]*)(?<fachar>[\u060C\u061B\u061F\u0621-\u064A\u064B-\u0652\u0660-\u066D\u0670-\u06F9\uFB50-\uFBB1\uFBD3-\uFD3F\uFDF2\uFDF4\uFDFA-\uFDFB\uFE70-\uFE72\uFE74\uFE76-\uFEFC]+?)");
			MatchCollection mc = rPersianChars.Matches(input);
			if (mc.Count > 0 && mc.Cast<Match>().Any(m => m.Groups["fachar"].Success)) {
				result = true;
			}
			return result;
		}

	}

}

The usage would be really easy:

<Window x:Class="WPFTest.MainWindow"
		xmlns:dneConv="clr-namespace:DNE.WPF.Converters;assembly=DNE.WPF"
		xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
		xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
		xmlns:sys="clr-namespace:System;assembly=mscorlib"
		Title="MainWindow" Height="350" Width="525">

 <Window.Resources>
  <dneConv:TextToFlowDirectionConverter x:Key="TextToFlowDirectionConverter"/>
 </Window.Resources>

 <TextBox x:Name="EmailField"  FlowDirection="{Binding Text, Converter={StaticResource TextToFlowDirectionConverter}, RelativeSource={RelativeSource Self}}"/>

</Window>

I hope this helps my fellow Iranian Devs 🙂

Suppression mechanism for DevTrends’ WCFDataAnnotations

If you’ve ever developed a Windows Communication Service (WCF) project, you’ll know that there comes a time you’re gonna need to validate objects passing through your service’s pipeline and luckily enough there’s already a very well-designed, easy-to-use Nuget package available for you; it’s called WCFDataAnnotations.

There is only simething left out on this great plugin. An escape door! When you decorate your service implementation with [ValidateDataAnnotationsBehavior], all of the service operations will get validated and there’s no way to tell the plugin to ignore validation on some of your operations.

Fortunately, the solution was really easy. Add the following class to its source code and decorate the service operations with [SuppressValidation] to disable validation on those operations.

SuppressValidationAttribute.cs

/*
 * Suppression Mechanism for DevTrends' WCF validation plugin.
 * Created by Mohammad M. Saffari http://blog.saffari.space
 * 2015-05-13
*/
using System;
using System.Linq;
using System.ServiceModel.Description;
 
namespace DevTrends.WCFDataAnnotations {
	/// <summary>
	/// Disables all validation when applied to a WCF service operation
	/// </summary>
	[AttributeUsage(AttributeTargets.Method, AllowMultiple = false, Inherited = false)]
	public class SuppressValidationAttribute : Attribute, IOperationBehavior {
 
		private bool _isActive = true;
 
		/// <summary>
		/// Enables or Disables the <see cref="SuppressValidationAttribute"/><para/>
		/// Default is <value>True</value> which means the <see cref="SuppressValidationAttribute"/> is Active
		/// </summary>
		public bool IsActive {
			get { return _isActive; }
			set { _isActive = value; }
		}
 
		/// <summary>
		/// Disables all validation when applied to a WCF service operation
		/// </summary>
		/// <param name="isActive">Default is <value>True</value> which means the <see cref="SuppressValidationAttribute"/> is Active</param>
		public SuppressValidationAttribute(bool isActive = true) {
			_isActive = isActive;
		}
 
 
		#region IOperationBehavior Members
 
		/// <summary>
		/// 
		/// </summary>
		/// <param name="operationDescription"></param>
		/// <param name="bindingParameters"></param>
		public void AddBindingParameters(OperationDescription operationDescription, System.ServiceModel.Channels.BindingParameterCollection bindingParameters) { }
 
		/// <summary>
		/// 
		/// </summary>
		/// <param name="operationDescription"></param>
		/// <param name="clientOperation"></param>
		public void ApplyClientBehavior(OperationDescription operationDescription, System.ServiceModel.Dispatcher.ClientOperation clientOperation) { }
 
		/// <summary>
		/// Removes the <see cref="ValidatingParameterInspector"/> from inspectors.
		/// </summary>
		/// <param name="operationDescription"></param>
		/// <param name="dispatchOperation"></param>
		public void ApplyDispatchBehavior(OperationDescription operationDescription, System.ServiceModel.Dispatcher.DispatchOperation dispatchOperation) {
			//dispatchOperation.ParameterInspectors.Add(new SuppressValidationParameterInspector());
			if (_isActive) {
				ValidatingParameterInspector[] pia = dispatchOperation.ParameterInspectors
						.Where(pi => pi is ValidatingParameterInspector)
						.Cast<ValidatingParameterInspector>()
						.ToArray();
				foreach (var vpi in pia) {
					dispatchOperation.ParameterInspectors.Remove(vpi);
				}
			}
		}
 
		/// <summary>
		/// 
		/// </summary>
		/// <param name="operationDescription"></param>
		public void Validate(OperationDescription operationDescription) { }
 
		#endregion
	}
}