data type for ip addresses

Hi, how can I create a data type that can store ip addresses (ipv4 and ipv6)? We use IDS 10.00 UC6 and 11.50 UC3. Thanks Holger de Wall

On Jun 19, 3:59 am, Holger de Wall <hol...@dewall-net.de> wrote: > Hi, > > how can I create a data type that can store ip addresses (ipv4 and ipv6)? > We use IDS 10.00 UC6 and 11.50 UC3. > > Thanks > > Holger de Wall I would probably store it in a field CHAR(15) and just have "xxx.xxx.xxx.xxx" stored inside. However, you could also decide to store it as four fields of SMALLINT. If I am not mistaken, using four SMALLINT fields would store each octet as a number and only use 4 bytes, whereas the CHAR field would use 15 bytes and you would have to do conversions from the CHAR to an integer for each octet to do any validation of values. (ie. must be in range 1-255). I guess the question is how many rows are we talking about (to see a significant savings in row size) and will you want to do anything to the field in that data? SteveN

> -----Original Message----- > From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] > On Behalf Of steven_nospam at Yahoo! Canada > Sent: Friday, June 19, 2009 10:54 AM > To: informix-list@iiug.org > Subject: Re: data type for ip addresses > > On Jun 19, 3:59 am, Holger de Wall <hol...@dewall-net.de> wrote: > > Hi, > > > > how can I create a data type that can store ip addresses (ipv4 and ipv6)? > > We use IDS 10.00 UC6 and 11.50 UC3. > > > > Thanks > > > > Holger de Wall > > I would probably store it in a field CHAR(15) and just have > "xxx.xxx.xxx.xxx" stored inside. > > However, you could also decide to store it as four fields of SMALLINT. > If I am not mistaken, using four SMALLINT fields would store each > octet as a number and only use 4 bytes, whereas the CHAR field would > use 15 bytes and you would have to do conversions from the CHAR to an > integer for each octet to do any validation of values. (ie. must be in > range 1-255). A SMALLINT is a 2 byte signed integer, range -32767 to +32767. It would use eight bytes to store four octets as smallints. --EEM > > I guess the question is how many rows are we talking about (to see a > significant savings in row size) and will you want to do anything to > the field in that data? > > SteveN > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

Probably true. fwiw though - if the information is stored as 4 pairs of HEX digits (ie char(8), or char(11) with the dots), or 4 sets of full 3 decimal digit (padded with leading 0s or leading spaces) then it might still work in a character string (at least for ipv4 etc) for most usages. But as you say - its what he wants to do with it thats significant... On Friday 19 June 2009 20:10:50 Carsten Haese wrote: > If the answer to any of those questions is Yes, a character field is > probably not the right choice for you. (The follow-up question of "What > is the right choice, then?" can only be answered depending on what > your actual expectations are.) -- Mike Aubury http://www.aubit.com/ Aubit Computing Ltd is registered in England and Wales, Number: 3112827 Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ

Holger de Wall wrote: > Hi, > > how can I create a data type that can store ip addresses (ipv4 and ipv6)? > We use IDS 10.00 UC6 and 11.50 UC3. As plenty of responses have already pointed out, any sufficiently long character field can *store* ipv4 and ipv6 addresses. However, the question is, beyond the mere storage and retrieval of such animals, do you have any needs that you haven't mentioned? For example, do you expect the field to reject invalid values? Do you expect to be able to order results in this field in a particular way? Are you expecting to perform comparisons on the values in this field? If the answer to any of those questions is Yes, a character field is probably not the right choice for you. (The follow-up question of "What *is* the right choice, then?" can only be answered depending on what your actual expectations are.) HTH, -- Carsten Haese http://informixdb.sourceforge.net

> From: carsten.haese@gmail.com > However, the question is, beyond the mere storage and retrieval of such > animals, do you have any needs that you haven't mentioned? For example, > do you expect the field to reject invalid values? Do you expect to be > able to order results in this field in a particular way? Are you > expecting to perform comparisons on the values in this field? > > If the answer to any of those questions is Yes, a character field is > probably not the right choice for you. (The follow-up question of "What > *is* the right choice, then?" can only be answered depending on what > your actual expectations are.) > Actually I'd argue that it is. Note that IPV4 and IPV6 are different formats. There are two patterns and based on the length of the string, or the number of tokens where the token separator is a ':' or a '.' is a dead give away. You have a couple of options... Do you want to write a stored procedure in C or in Java? In either case, you can use a before insert trigger to call a validation SP that checks the input and sees if it does follow either the IPV4 or IPV6 reg ex pattern. I guess if you want to also go the route of using the extensibility portion of IDS you could create either an IP datatype or an IPV4 and IPV6 data types. Then the data type handles the validation of the data based on regex. But its easier to have a character string than to have a couple of small ints for the octets. HTH -G _________________________________________________________________ Windows Live™: Keep your life in sync. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_BR_life_in_synch_062009

Hello Holger, I like Ian's idea for integrity and everyone's ideas for simplicity. Plus Ian's regular expressions (like [0-9]) is probably the fastest way to check data. If you have a small or medium amount of data, then VARCHAR(39) would hold both IPv4 (i.e. 127.255.255.255) and IPv6 (i.e. 62001:0db8:85a3:0000:0000:8a2e:0370:7334). But if you have a large amount of data you probably want to create separate tables called "ipv4" and "ipv6". Then save each part of the IP address as SMALLINT (converting the IPv6 parts to base 10). Here is a large "ipv4" table example: CREATE TABLE ipv4 ( col1 SMALLINT, col2 SMALLINT, col3 SMALLINT,col4 SMALLINT). Then create a view to put all the parts back together: CREATE VIEW ipv4_view (full_ip) AS SELECT col1 || '.' || col2 ||'.' || col3 || '.' || col4) FROM ipv4 It looks crazy but it works. The view will make life easier for the users and programmers. But you get all the hard work with real tables, columns, checks, triggers, and/or stored procedures. Plus you can put indexes on any part of the real table. I hope this gives you some more ideas. -L.S.

Ian Michael Gumby wrote: > Actually I'd argue that it is. Gutsy, considering that we don't actually know the OP's requirements. (We know what he said he wants, but we don't know what he needs.) > Note that IPV4 and IPV6 are different formats. There are two patterns > and based on the length of the string, or the number of tokens where the > token separator is a ':' or a '.' is a dead give away. > > You have a couple of options... Do you want to write a stored procedure > in C or in Java? In either case, you can use a before insert trigger to > call a validation SP that checks the input and sees if it does follow > either the IPV4 or IPV6 reg ex pattern. And what exactly is an "IPV4" reg ex pattern? A regular expression that correctly matches a string if (and only if) it's a valid IPv4 address is actually horrendously difficult to write. (Hint: What's a regular expression for "any integer number between 1 and 254?") > I guess if you want to also go the route of using the extensibility > portion of IDS you could create either an IP datatype or an IPV4 and > IPV6 data types. Then the data type handles the validation of the data > based on regex. Depending on the OP's needs, that may be necessary. > But its easier to have a character string than to have a couple of small > ints for the octets. Of course it's easier, but we don't know if it is what the OP needs. Using strings will, for example, prevent him from reliably finding a row by its IP address, since there are different ways that the same IP address can be written. If the IP address is stored as "127.0.0.1" and you look for all computers whose IP address is "127.000.000.001", guess what's gonna happen. Summary: Saying that "X gets the job done" is risky if you don't know exactly what "the job" is. -- Carsten Haese http://informixdb.sourceforge.net

> From: carsten.haese@gmail.com > Subject: Re: data type for ip addresses > Date: Fri, 19 Jun 2009 17:05:00 -0400 > To: informix-list@iiug.org > > Ian Michael Gumby wrote: > > Actually I'd argue that it is. > > Gutsy, considering that we don't actually know the OP's requirements. > (We know what he said he wants, but we don't know what he needs.) > No, not gutsy. You have two different formats and patterns are different so if you break each field out to an octet you'd have 4 for IPV4 and more for IPV6. Not to mention that you then have to break down the IP address into each octet and store them in the right order, all at the app level. If you keep it as a string, your app just dumps it in to the database. Looking at Python, Ruby,Groovy, etc ... your scripting languages can handle the data as a string fairly efficiently. Then you can token-ize the string based first on '.' then on ':'. (If the number of tokens in the string == 1, then you know that your IP address isn't a IPV4 so you'd check on ':'. Then each token can be converted and the value checked to see if its in a valid range. (0-255 for IP4). If you want to make this a data type you can have a couple of things.... 1) A flag to indicate IPV4 or IPV6. 2) A routine to validate the IP number. 3) A routine to fetch an individual octet. 4) A routine to fetch the IP address as a string 5) A routine to fetch the IP address as the list/array of octets. You get the idea. Creating a data type could be overkill and that would be 'gutsy' unless you were doing this to add to the IIUG repository. > > Note that IPV4 and IPV6 are different formats. There are two patterns > > and based on the length of the string, or the number of tokens where the > > token separator is a ':' or a '.' is a dead give away. > > > > You have a couple of options... Do you want to write a stored procedure > > in C or in Java? In either case, you can use a before insert trigger to > > call a validation SP that checks the input and sees if it does follow > > either the IPV4 or IPV6 reg ex pattern. > > And what exactly is an "IPV4" reg ex pattern? A regular expression that > correctly matches a string if (and only if) it's a valid IPv4 address is > actually horrendously difficult to write. (Hint: What's a regular > expression for "any integer number between 1 and 254?") > IPV4 = "xxx.xxx.xxx.xxx" Where you may or may not have leading zeros. And the values of patter are >= '000' and <='255'. (Again leading zero can be dropped) > > I guess if you want to also go the route of using the extensibility > > portion of IDS you could create either an IP datatype or an IPV4 and > > IPV6 data types. Then the data type handles the validation of the data > > based on regex. > > Depending on the OP's needs, that may be necessary. > > > But its easier to have a character string than to have a couple of small > > ints for the octets. > > Of course it's easier, but we don't know if it is what the OP needs. > Using strings will, for example, prevent him from reliably finding a row > by its IP address, since there are different ways that the same IP > address can be written. If the IP address is stored as "127.0.0.1" and > you look for all computers whose IP address is "127.000.000.001", guess > what's gonna happen. > That's a good point. But that's fixable. Especially if you create your own IP data type. What do you think your code is going to look like when you have to say SELECT ... FROM foo WHERE first = 127 AND second = 0 AND third = 0 AND fourth = 1 Much less readable than '127.0.0.1' > Summary: Saying that "X gets the job done" is risky if you don't know > exactly what "the job" is. > According to the specifications given, the requirement is to effectively story IP addresses in the database. Nothing else provided. So the solution meets the requirements. ;-) _________________________________________________________________ Bing™ brings you maps, menus, and reviews organized in one place. Try it now. http://www.bing.com/search?q=restaurants&form=MLOGEN&publ=WLHMTAG&crea=TEXT_MLOGEN_Core_tagline_local_1x1

Ian Michael Gumby wrote: > IPV4 = "xxx.xxx.xxx.xxx" Where you may or may not have leading zeros. > And the values of patter are >= '000' and <='255'. Of course, but that's not a regular expression. That's exactly my point: regular expressions are rarely the right tool for anything, and they're definitely not the right tool for determining whether a string represents a valid IPv4 address. > What do you think your code is going to look like when you have to say > > SELECT ... > FROM foo > WHERE first = 127 > AND second = 0 > AND third = 0 > AND fourth = 1> > Much less readable than '127.0.0.1' True, but if I were to define a custom data type for an IPv4 address, I'd define casts to and from CHAR, so that I could write SELECT ... FROM foo where ip_addr == "127.0.0.1"::IPv4 instead. -Carsten