Wednesday, July 29, 2009

Convert IP String to numeric representation and numeric representation to IP String in SQL Server

In the post Convert IP String to numeric representation and numeric representation to IP String in Java we dealt with IP conversions in Java. In this post we will do just the same, but instead using Java we will use SQL Server. Converting the Java code to MS SQL is very simple. Not much knowledge in T-SQL is needed, because we are mostly dealing with mathematical operations. We will simply create 2 functions. Here is the first function for converting bigint to IP:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[bigint_to_ip]
(
        @ip bigint
)
RETURNS varchar(15)
AS
BEGIN
    DECLARE @octet0 varchar(3)
    DECLARE @octet1 varchar(3)
    DECLARE @oct
et2 varchar(3)
    DECLARE @octet3 varchar(3)
    SET @octet3 = (@ip / power(2, 24)) % 256
    SET @octet2 = (@ip / power(2, 16)) % 256
    SET @octet1 = (@ip / power(2, 8)) % 256
    SET @octet0 = @ip % 256
    RETURN @octet3 + '.' + @octet2 + '.' + @octet1 + '.' + @octet0
END

The second function which converts IP to bigint is a bit more tricky, since unlike java “split” function, SQL Server T-SQL doesn’t present any comfortable way of handling strings in the manner Java does. Luckily, SQL Server does have a function called “PARSENAME” which is used for getting a part of an object. Assuming the parts of an object are separated with dots, it is possible to get any part of the object. for example:

DECLARE @myObject varchar(100)
SET @myObject = 'this.is.my.object'
PARSENAME(@ObjectName, 4) -- Return: this
PARSENAME(@ObjectName, 3) -- Return: is
PARSENAME(@ObjectName, 2) -- Return: my
PARSENAME(@ObjectName, 1) -- Return: object

Note, that the position is reversed to the intuitive logic, and that position “1” means the last part of the string and not the first.

We can use the “PARSENAME” function in order to easily split the IP string to its components. Here is the second function that converts IP to bigint:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ip_to_bigint]
(
        @ip varchar(15)
)
RETURNS bigint
AS
BEGIN
    DECLARE @octet0 bigint
    DECLARE @octet1 bigint
    DECLARE @octet2 bigint
    DECLARE @octet3 bigint
    SET @octet3 = ParseName(@ip, 4)
    SET @octet2 = ParseName(@ip, 3)
    SET @octet1 = ParseName(@ip, 2)
    SET @octet0 = ParseName(@ip, 1)
    RETURN @octet3 * power(2, 24) + @octet2 * power(2, 16) + @octet1 * power(2, 8) + @octet0
END

Friday, July 24, 2009

Convert IP String to numeric representation and numeric representation to IP String in Java

There are times we would like to convert IP string to its numeric representation. For example, if we would like to store in our database ranges of IPs and for each IP its Geo location or organization or ISP or any other IP based service. Storing the IP as a String doesn’t have much meaning, since we won’t be able to do range comparisons. For example if we had a database table named “country_ip” with the following columns:

  • begin_ip – Beginning of the IP range (long/bigint)
  • end_ip – End of IP range (long/bigint)
  • country_code – Country code corresponding to the IP range (2 or 3 characters depending on the standard used)

Note that the “being_ip” and “end_ip” are in their numeric representation and are stored as long/bigint values in the database.

Now, suppose we have an IP that we would like to find its country code.

First we will have to convert the IP to its numeric representation and then we would find the country code by writing a simple SQL query:

select country_code from country_ip where :param_ip >= begin_ip and :param_ip <= end_ip

The IP conversion functions, which convert an IP to its numeric representation and vise versa, are quite simple. A string IP is constructed from 4 octets separated by “.” (of course, this is going to change gradually with the entrance of IPv6, but as it is for now all services are still in IPv4). Each octet is a byte value from 0 to 255. In order to convert a string IP to its numeric representation all we have to do is split the IP string to its 4 octets and then put each octet into a long variable on its correct position. For example, the first octet from the right is entered as it is, from the “0” bit to the “7” bit, the second (from the right), should be placed on the “8” bit to the “15” bit and so on. Placing an octet on a specific location in the long variable can be done simply by multiplying it by 256 and adding its value (to the long variable), which is actually 2 power 8 (8 bits). The third octet which should be put on the 16th bit and therefore should be multiplied by 2 power 16 (65536) and so on. Since we are multiplying by numbers which are base of 2, it is more efficient to use shit left operation. This operation simply moves all the bits of a number to the left “n” times. This is how this operation is written in Java:

x << n

which means: move the bits in “x” “n” times to the left, or in other words: multiply “x” by 2 power “n”. or shortly:

x << n = x * 2 ^ n

where “^” is used as power operator.

For example:

10 << 1 = 10 * 2 ^ 1 = 10 * 2 = 20
10 << 3 = 10 * 2 ^ 3 = 10 * 8 = 80

Converting a numeric IP to a string IP can easily by done by doing the reverse operation, which means dividing and taking the reminder. Of course that instead of using straight division shift right operation can be used, which move all the bits of a number to the right “n” times.

Let’s have a look at the code of the IP conversion functions. It is very simple and pretty much speaks for itself:

package com.bashan.blog;
public class IpUtils {
  public static String longToIp(long longIp) {
    int octet3 = (int) ((longIp >> 24) % 256);
    int octet2 = (int) ((longIp >> 16) % 256);
    int octet1 = (int) ((longIp >> 8) % 256);
    int octet0 = (int) ((longIp) % 256);
    return octet3 + "." + octet2 + "." + octet1 + "." + octet0;
  }
  public static long ipToLong(String ip) {
    String[] octets = ip.split("\\.");
    return (Long.parseLong(octets[0]) << 24) + (Integer.parseInt(octets[1]) << 16) +
        (Integer.parseInt(octets[2]) << 8) + Integer.parseInt(octets[3]);
  }
  public static void main(String[] args) {
    System.out.println("IP 34.56.78.34 as a number: " + ipToLong("34.56.78.34"));
    System.out.println("and converting back to ip: " + longToIp(574115362));
    System.out.println("IP 0.0.0.0 as a number: " + ipToLong("0.0.0.0"));
    System.out.println("and converting back to ip: " + longToIp(0));
    System.out.println("IP 255.255.255.255 as a number: " + ipToLong("255.255.255.255"));
    System.out.println("and converting back to ip: " + longToIp(4294967295L));
  }
}

and this is the test program output:

IP 34.56.78.34 as a number: 574115362
and converting back to ip: 34.56.78.34
IP 0.0.0.0 as a number: 0
and converting back to ip: 0.0.0.0
IP 255.255.255.255 as a number: 4294967295
and converting back to ip: 255.255.255.255

Saturday, July 18, 2009

Getting all time zones of a Country in Java

In this post I wrote about how to get a complete time zone list in java. In terms of UI, it is not always the best thing to show a complete list of time zones. The list is quite long and the data is ordered by continent. Sometimes it makes it harder to find a time zone. A nice solution is allowing to filter time zones according to a selected country. Unfortunately, there is no built-in way to know the time zones of a given locale. But, there is a very nice open source project called ICU4J (International Components for Unicode) that allows very easily to get all time zones of a specific Locale. In order to that, we just have to call the getAvailableIDs method of the com.ibm.icu.util.TimeZone class. Here is an example:

String[] timeZones = com.ibm.icu.util.TimeZone.getAvailableIDs(countryCode);

This example will return all the available time zones in Israel. Note that the input to this function is country code. If we had a locale we could have written:

String[] timeZones = com.ibm.icu.util.TimeZone.getAvailableIDs(locale.getCountry());

Note that the result of this function is array of time zone ids. This result can be easily converted to an array of time zones:

String[] timeZones = com.ibm.icu.util.TimeZone.getAvailableIDs(countryCode);
List<TimeZone> timeZoneList = new ArrayList<TimeZone>();
for (String timeZone : timeZones)
{
  timeZoneList.add(TimeZone.getTimeZone(timeZone));
}

Saturday, July 11, 2009

Programmatically Delete a database in SQL server

Sometimes we would like to programmatically delete SQL server database. Simply writing:
DROP DATABASE 'database_name'
is not always a good solution, for 2 main reasons:
  1. The database may be already deleted, therefore we will get exception telling there is no such database to delete.
  2. There may still be existing connections to the database, preventing from the database being deleted.
The first problem can be easily solved by checking the existence of the database before deleting it:
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'database_name') BEGIN
DROP DATABASE 'database_name'
END
GO
Solving the second problem is a bit harder. In order to delete the database, all existing connections needed to be closed. This should be done carefully since we do not want to close our own connection. This script, first closes all existing connections and then drops the database:
DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255)
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'database_name') BEGIN
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID('database_name')
AND spid != @@SPID
PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'
WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID('database_name')
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END
DROP DATABASE 'database_name'
END
GO
Note that the name of the database (“database_name” in this example) can be replaced with SQL parameter.