March 2013 - Development Simply Put

A blog simplifies main concepts in IT development and provides tips, hints, advices and some re-usable code. "If you can't explain it simply, you don't understand it well enough" -Albert Einstein

  • Development Simply Put

    If you can't explain it simply, you don't understand it well enough.

    Read More
  • ITWorx

    ITWorx is a global software professional services organization. Headquartered in Egypt, the company offers Portals, Business Intelligence, Enterprise Application Integration and Application Development Outsourcing services to Global 2000 companies.

    Read More
  • Information Technology Institute

    ITI is a leading national institute established in 1993 by the Information and Decision Support Centre.

    Read More

2013-03-15

Having Fun With JavaScript And GreaseMonkey


Having Fun With JavaScript And GreaseMonkey

This time I am going to tell you about a great Firefox extension/addon called GreaseMonkey.

I think everyone of us has faced the situation when he found that some website is missing something which could have been done by just a bit of JavaScript. But, unfortunately we can do nothing regarding this except contacting the website author to try to convince him that this change is really good and he should do it.

I can now tell you that you don't have to go through all this hustle because you now have GreaseMonkey which provides you with the ability to run JavaScript across certain websites. It is like injecting the JavaScript you need into a website but for sure at your side only, not in the source website itself.

Using GreaseMonkey you can perform so many cool things. There are so many free scripts others wrote which you can search and use as you wish. You can find these scripts here or here


Me myself have wrote some scripts which made my life easier. Some alter web pages UI, others do some business depending on some rules, ...... but the most powerful ones which I really love are the links elongators.

As you know there are some services for shortening links like "1Tool", "TakeMyFile" and many otheres. These services takes a long URL and returns back to you a short one which you can share and post anywhere you wish. The only annoying thing regarding this is that when you use the short link you will be directed to a page with a time counter or ads or some annoying thing till you are finally re-directed to the main URL. This really made me mad.

This could be somehow acceptable when you just need to browse a certain URL, but what about a bunch of them. We all know about forums and how we can find in one thread an attachment which is so big in size that it is divided into a huge number of part files uploaded to some online hosting service like RapidShare or whatever. In this case, lets say that number of links is 30, will you click on each one of these 30 links to be redirected to some annoying page -each time of the 30 times- and wait for some counter and finally get your original link??!!!

This encouraged me to write my own GreaseMonkey scripts to undo what the shortening services already done by getting the original links and replacing the shortened ones in the page with their corresponding original ones.

This is just a sample of what you can do with GreaseMonkey and that's why I really encourage you give it a try, you will love it.

To know what you can do with GreaseMonkey, you can have a look on the code sample below
// ==UserScript==

// @name          1Tool Short Links Elongator

// @namespace     DevelopmentSimplyPut(developmentsimplyput.blogspot.com)

// @description   Elongates all 1Tool short links to their direct links

// @include       *

// ==/UserScript==

String.prototype.ReplaceAll = function(stringToFind,stringToReplace){
    var temp = this;
    var index = temp.indexOf(stringToFind);

        while(index != -1){

            temp = temp.replace(stringToFind,stringToReplace);
            index = temp.indexOf(stringToFind);
        }
        return temp;
    }

Array.prototype.unique = function () {
 var r = new Array();
 o:for(var i = 0, n = this.length; i < n; i++)
 {
  for(var x = 0, y = r.length; x < y; x++)
  {
   if(r[x]==this[i])
   {
    continue o;
   }
  }
  r[r.length] = this[i];
 }
 return r;
}

function Run(Urls)
{
 if(Urls.length>0)
 {
  for(i=0;i<Urls.length;i++)
   GetDirectLink(Urls[i]);
 }
}

function GetDirectLink(str)
{
 GM_xmlhttpRequest(
       {
        method: "GET",
        url: 'http://www.yahoo.com/.php?id=' + str.replace('http://1tool.biz/',""),
        headers:{'User-agent': 'Mozilla/4.0 (compatible) Greasemonkey', 'Content-type':'application/x-www-form-urlencoded'},
        onload:function(result)
        {
         var parts1=new Array();
         var parts2=new Array();
         parts1=result.responseText.split('onclick="NewWindow(');
         parts2=parts1[1].split("'");
         result=parts2[1];
         (document.getElementsByTagName("body"))[0].innerHTML=(document.getElementsByTagName("body"))[0].innerHTML.ReplaceAll(str,result);
        }
       }
      );
}

var DirectUrls=new Array();
var Urls=new Array();
var UrlsPattern=/http:\/\/1tool\.biz\/(?:\w*)/g;
Urls=(document.getElementsByTagName("body"))[0].innerHTML.match(UrlsPattern);

Urls=Urls.unique();
Run(Urls);

That's it. This is just a scratch on the surface but not everything. You can check it with yourself and find what you can do with GreaseMonkey.

At last, you can have a look on my scripts here or here and for sure your feedback is so welcomed.


Resources & Links
  1. Greasespot
  2. Greasemonkey :: Add-ons for Firefox
  3. Userscripts.org: Power-ups for your browser
  4. AhmedTarekHasan's Scripts on GreasyFork.org
  5. AhmedTarekHasan's Scripts on Openuserjs.org

2013-03-08

How To Create SQL Custom User-Defined Aggregate Functions

How To Create SQL Custom User-Defined Aggregate Functions


Code samples on this post can be downloaded from here


Sometimes you need to perform some complex SQL queries depending on some aggregate operations or functions. In most cases, these complex operations could be achieved by series of queries with joins and groupings, but, sometimes it is almost impossible to achieve this by the basic queries and functions natively supported by SQL.

For example, assume you have a table of buyers called "Buyers" and a table of products called "Products". Also, each product has some properties like size which may have multiple values per product. Now, the business says that we need to list all the products but not with all the size values instead we will only list the most commonly purchased size.

To be more clear, lets assume that 10 buyers bought a certain mobile phone with size 5, while other 2 buyers bought the same phone but with size 6. Then, when we list this mobile phone product, we will only list mobile phone with the size 5 cause it is the most common purchased one.

So, to achieve this using only native SQL queries you will find it so complex especially when you need to apply the same logic on other columns (in our example, not only the size column). This leads us to custom user-defined aggregate functions.

SQL Server doesn’t have so many aggregate functions to use, only the basics such as COUNT, MIN, MAX and few others are implemented but still the list is quite small. However, SQL Server includes CLR integration which we can use to add our own custom user-defined aggregate functions.

To do this, we need to:
  1. Create a class library project targeting .NET Framework 3.5 or below (as the maximum supported .NET Framework version for SQL Server 2008 is 3.5)
  2. Implement the necessary structures
  3. Register the assembly into SQL
  4. Register the aggregate functions into SQL
These are the main basic steps to make your own SQL custom user-defined aggregate functions. So, now lets explain each step.

1. Create a class library project targeting .NET Framework 3.5 or below
You can do so by creating a new visual studio project of type class library. This is straight forward.

2. Implement the necessary structures
To do this, you need to know that:
  • An aggregate is created by defining a "Struct" not a "Class"
  • This struct must be decorated with the "SqlUserDefinedAggregate" attribute.
  • This attribute has some options which we need to set properly to achieve the desired behavior
    • Format:
      • Serialization format for the struct
      • The possible values are Native, Unknown and UserDefined
      • This attribute decides how SQL will serialize and deserialize your struct members
      • In case of "Native" format, the framework handles all the necessary steps to serialize and deserialize the structure.
      • In case of "UserDefined" format, the framework will use the serialize and deserialize methodlogy you provide for your struct through implementing the "IBinarySerialize" interface
      • Also, in case of "UserDefined" format, we must set the "MaxByteSize" option, find it explained below
    • IsInvariantToDuplicates (bool): 
      • Decides whether receiving the same value twice or more affects the result or not
      • Sometimes you need to work on unique values, like computing the max value in a column, you don't need to re-compute or re-compare an already processed value as it is already decided if it is the max value or another value is greater
    • IsInvariantToNulls (bool):
      • Decides whether receiving a NULL value changes the result or not
      • Sometimes you need to ignore the NULL values in your operation, then, you need to set this to true, else, false
    • IsInvariantToOrder (bool):
      • Decides whether the order of values affects the result or not
    • IsNullIfEmpty (bool):
      • Decides whether an empty set results to a NULL value or not
    • Name (string):
      • Name of the aggregate method which will be used in SQL to call our aggregate function
    • MaxByteSize (int):
      • Sets the maximum size of the aggregate instance.
      • -1 represents a value larger than 8000 bytes, up to 2 gigabytes
      • This is required when using the "UserDefined" format and providing our own serialization and de-serialization logic
  • To define this struct, we must provide implementation for some methods
    • Init():
      • Used in order to initialize any needed resources or variables
      • Called only once when the aggregate is called
    • Accumulate():
      • Decides what action to take when receiving a new row value
      • Called once for each row
      • It is mistakenly thought that this method should provide the actual implementation of the aggregate but this is not a must as we will see later
    • Merge():
      • Used because sometimes if the the column to aggregate contains a large number of data the SQL CLR may split those rows into groups and aggregate each group individually and then merge the results of those groups again using the Merge() method
    • Terminate():
      • Used to free any used resources and to return the result
      • Called at the end of the evaluation procedure
    • The sequence of invocation should be like this

3. Register the assembly into SQL
This can be done as follows:
  1. After creating your class library, build your DLL
  2. Run an SQL query to register this DLL into SQL for SQL to be able to recognize and use your struct
  3. This SQL query will be provided in the examples section

4. Register the aggregate functions into SQL
This can be done by running an SQL query which will register our new aggregate function into SQL to be used later. This SQL query will be provided in the examples section.

Now, it is time to see some examples


Example 1
We want to define a new aggregate function to calculate the count of NULLs in a column.

CountNulls.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.ObjectModel;
using System.Linq;
using System.Xml.Serialization;
using System.IO;
using System.Text;
using System.Collections;

namespace DevelopmentSimplyPut.SQLAggregateFunctions
{
    [System.Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
       Microsoft.SqlServer.Server.Format.UserDefined,
       IsInvariantToDuplicates = false, // receiving the same value again changes the result
       IsInvariantToNulls = false,      // receiving a NULL value changes the result
       IsInvariantToOrder = true,       // the order of the values doesn't affect the result
       IsNullIfEmpty = true,            // if no values are given the result is null
       MaxByteSize = -1,
       Name = "CountNulls"              // name of the aggregate
    )]
    public struct CountNulls
 {
  private int counter;
  public void Init()
  {
   counter = 0;
  }
  public void Accumulate(object Value)
  {
   if (Value == DBNull.Value)  // count just the rows that are not equal to NULL
    counter++;
  }
  public void Merge(CountNulls Group)
  {
   this.counter += Group.Counter; // when merge is needed the counter of other groups should be added
  }

  public SqlString Terminate()
  {
   return new SqlString(counter.ToString()); //returning the results
  }
 }
}

RegisterAssemblyAndCreatingAggregate.sql
--Turning on CLR functionality
--By default, CLR is disabled in SQL Server so to turn it on
--we need to run this command against our database
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- Creating the SQL assembly and linking it to the C# library DLL we created
CREATE ASSEMBLY SQLAggregateFunctions
AUTHORIZATION dbo
FROM 'C:\SQLAggregateFunctions.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE AGGREGATE dbo.CountNulls (@value nvarchar(MAX)) RETURNS nvarchar(MAX)
EXTERNAL NAME SQLAggregateFunctions.[DevelopmentSimplyPut.SQLAggregateFunctions.CountNulls]
--EXTERNAL NAME SQLAssemblyName.[C#NameSpace".C#ClassName].C#MethodName


/*
DROP AGGREGATE dbo.CountNulls
DROP ASSEMBLY SQLAggregateFunctions
*/

Testing.sql
SELECT dbo.CountNulls(MyColumn)
FROM MyTable


Example 2
We want to define a new aggregate function to get the most common value in a column

MostCommon.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.ObjectModel;
using System.Linq;
using System.Xml.Serialization;
using System.IO;
using System.Text;
using System.Collections;

namespace DevelopmentSimplyPut.SQLAggregateFunctions
{
    [System.Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
       Microsoft.SqlServer.Server.Format.UserDefined,
       IsInvariantToDuplicates = false, // receiving the same value again changes the result
       IsInvariantToNulls = false,      // receiving a NULL value changes the result
       IsInvariantToOrder = true,       // the order of the values doesn't affect the result
       IsNullIfEmpty = true,            // if no values are given the result is null
       MaxByteSize = -1,
       Name = "MostCommon"              // name of the aggregate
    )]
    public struct MostCommon : IBinarySerialize
    {
        private Collection<string> pool;
        private string result;
        private int count;

        public void Init()
        {
            pool = new Collection<string>();
            result = string.Empty;
            count = -1;
        }
        public void Accumulate(string value)
        {
            if (null != value)
            {
                pool.Add(value.ToString());
            }
        }
        public void Merge(MostCommon group)
        {
            if (null != group.pool && group.pool.Count > 0)
            {
                foreach (string entry in group.pool)
                {
                    pool.Add(entry);
                }
            }
        }
        public SqlString Terminate()
        {
            string[] distinctValues = pool.Distinct().ToArray();

            foreach (string distinctValue in distinctValues)
            {
                int tempCount = pool.Count(s => s.Equals(distinctValue, StringComparison.OrdinalIgnoreCase));

                if (tempCount > count)
                {
                    count = tempCount;
                    result = distinctValue;
                }
            }

            return new SqlString(result.ToString());
        }

        #region IBinarySerialize Members
        public void Read(System.IO.BinaryReader reader)
        {
            this.result = reader.ReadString();
            this.count = reader.ReadInt32();
            int itemsCount = reader.ReadInt32();
            
            pool = new Collection<string>();

            for (int i = 0; i < itemsCount; i++)
         {
             this.pool.Add(reader.ReadString());
         } 
        }
        public void Write(System.IO.BinaryWriter writer)
        {
            writer.Write(this.result);
            writer.Write(this.count);
            writer.Write(pool.Count);
            
            foreach (string entry in pool)
            {
                writer.Write(entry);
            } 
        }
        #endregion
    }
}

RegisterAssemblyAndCreatingAggregate.sql
--Turning on CLR functionality
--By default, CLR is disabled in SQL Server so to turn it on
--we need to run this command against our database
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- Creating the SQL assembly and linking it to the C# library DLL we created
CREATE ASSEMBLY SQLAggregateFunctions
AUTHORIZATION dbo
FROM 'C:\SQLAggregateFunctions.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE AGGREGATE dbo.MostCommon (@value nvarchar(MAX)) RETURNS nvarchar(MAX)
EXTERNAL NAME SQLAggregateFunctions.[DevelopmentSimplyPut.SQLAggregateFunctions.MostCommon]
--EXTERNAL NAME SQLAssemblyName.[C#NameSpace".C#ClassName].C#MethodName


/*
DROP AGGREGATE dbo.MostCommon
DROP ASSEMBLY SQLAggregateFunctions
*/

CreateTestDB.sql
USE [TestDB01]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T1](
 [Col1] [nvarchar](100) NOT NULL,
 [Col2] [int] NOT NULL,
 [Col3] [int] NOT NULL,
 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED 
(
 [Col1] ASC,
 [Col2] ASC,
 [Col3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


INSERT INTO T1(Col1, Col2, Col3)
VALUES('Ahmed', 1, 1)
, ('Ahmed', 1, 2)
, ('Ahmed', 1, 5)
, ('Ahmed', 1, 6)
, ('Ahmed', 2, 2)
, ('Ahmed', 3, 4)
, ('Ahmed', 4, 2)
, ('Ahmed', 5, 2)
, ('Ahmed', 6, 2)
, ('Tarek', 2, 1)
, ('Tarek', 2, 3)
, ('Tarek', 2, 4)
, ('Tarek', 2, 5)
, ('Tarek', 2, 7)
, ('Tarek', 2, 9)
, ('Tarek', 3, 5)
, ('Tarek', 4, 5)
, ('Tarek', 6, 5)
, ('Hasan', 2, 3)
, ('Hasan', 2, 7)
, ('Hasan', 5, 0)
, ('Hasan', 5, 1)
, ('Hasan', 5, 4)
, ('Hasan', 8, 6)

Test.sql
USE [TestDB01]
GO

SELECT
Col1
, (SELECT dbo.MostCommon(Col2) FROM T1 where main.Col1 = Col1 GROUP BY Col1) AS CommonCol2
, (SELECT dbo.MostCommon(Col3) FROM T1 where main.Col1 = Col1 GROUP BY Col1) AS CommonCol3
FROM T1 AS main
GROUP BY Col1


That's it for now. You can read more about this topic on the internet and know more about what you can and can't do using this technique. You can make use of the resources below.


Resources
  1. Custom Aggregates in SQL Server - CodeProject
  2. How to implement your own aggregate function in SQLCLR, SQL Server 2005 - Bashar Kokash' Blog
  3. System.Data.SqlTypes Namespace ()
  4. IBinarySerialize Interface (Microsoft.SqlServer.Server)