Archive

Posts Tagged ‘Domain’

Extending SQLite functionality using .NET (UDF)

April 13th, 2010 No comments

Although SQLite does not contain builtin support for directly writing user defined functions (UDF) in PL/SQL, it features a very fast and convenient way for extended functionality using your programming language of choice.

Let me illustrate this by example: In this case we are looking at a string transformation algorithm that allows for sorting a Url string according to the top level domain, domain name and host name. E.g. http://www.wikipedia.de/favicon.ico should be sorted according to http://de.wikipedia.www/favicon.ico.

Using PL/SQL with its limited string processing functions, this can result in a lot of coding. Using .NET, it’s easy to write and surprisingly fast.

[SQLiteFunction( Name = "ReverseDomain", Arguments = 1, FuncType = FunctionType.Scalar )]
public class ReverseDomain: SQLiteFunction
{
    public override object Invoke( object[] args )
    {
        if ( args == null || args.Length < 1 || args[ 0 ] == null )
            return null;
        string[] ss = args[ 0 ].ToString().Split( new string[] { "://", "/" }, 3 );
        if ( ss.Length > 1 )
        {
            string[] sss = ss[ 1 ].Split( new char[] { '.' } );
            Array.Reverse( sss );
            return ss[ 0 ] + "://" + String.Join( ".", sss ) + "/" + ( ss.Length == 3 ? ss[ 2 ] : String.Empty );
        }
        return args[ 0 ];
    }
}

Alternatively, we could use the UriBuilder class:

[SQLiteFunction( Name = "ReverseDomain", Arguments = 1, FuncType = FunctionType.Scalar )]
public class ReverseDomain: SQLiteFunction
{
    public override object Invoke( object[] args )
    {
        if ( args == null || args.Length < 1 || args[ 0 ] == null )
            return null;
        UriBuilder u = new UriBuilder( args[ 0 ] as string );
        string[] ss = u.Host.Split( new char[] { '.' } );
        Array.Reverse( ss );
        u.Host = String.Join( ".", ss );
        return u.ToString();
    }
}

Where is the catch with UriBuilder? Compared to the custom string splitting solution it is about 5 times slower. It can transform only about 65000 urls per second on my machine, while the prior method can convert 350000 urls in the same time.

How is this new UDF called from SQL?

SELECT * FROM UrlTable ORDER BY ReverseDomain( Url )
Categories: Software Development Tags: , , , , , , , , , , ,