Hello, SQLite community! I am a PostgreSQL SQLite foreign data wrapper contributor as C developer. I have successfully implemented inet (individual IP address) and cidr (IP subnet address with a mask) PostgreSQL data types support for SQLite. Storage conventions for blob affinity was documented:

m - mask as number of bits
a - bytes of IP address 

 IP v4 + cidr
┏━━━┳━━━┳━━━┳━━━┳━━━┓
┃ a ┃ a ┃ a ┃ a ┃ m ┃
┗━━━┻━━━┻━━━┻━━━┻━━━┛
  0   1   2   3   4  - byte index
 IP v6 + cidr
┏━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┓
┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ m ┃
┗━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┛
  0   1   2   3   4   5   5   7   8   9   10  11  12  13  14  15  16  17 - byte index

 IP v4
┏━━━┳━━━┳━━━┳━━━┓
┃ a ┃ a ┃ a ┃ a ┃
┗━━━┻━━━┻━━━┻━━━┛
  0   1   2   3  - byte index
 IP v6
┏━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┳━━━┓
┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃ a ┃
┗━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┻━━━┛
  0   1   2   3   4   5   5   7   8   9   10  11  12  13  14  15  16 - byte index

For our users and for testing SQLite internal data state there are some queries which gives usual, but not always canonical form of IPv6 address. For example:

select  case when typeof(ip) = 'blob' and (length(ip) = 16 or length(ip) = 17)
		then substr(lower(hex(ip)),1,4) || ':' ||
			 substr(lower(hex(ip)),5,4) || ':' ||
			 substr(lower(hex(ip)),9,4) || ':' ||
			 substr(lower(hex(ip)),13,4) || ':' ||
			 substr(lower(hex(ip)),17,4) || ':' ||
			 substr(lower(hex(ip)),21,4) || ':' ||
			 substr(lower(hex(ip)),25,4) || ':' ||			 
			 substr(lower(hex(ip)),29,4) ||
			 	case when length(ip) = 17 then '/x' || substr(hex(ip),33) else '' end
		else null
		end ipv6_text,
		hex(ip) hex_ipv6 
from "type_INET";

No problems if we have IPv6, but in case of IPv6 + cidr the last component substr(hex(ip),33) must be decimal. How can I convert hex text characters to decimal integer value by standard SQLite functions?

Also there is similar problem in case of IPv4 as blob. Obviously similar problem exists in IPv4 4 times and in IPv4 + cidr 5 times.

In case of IPv4 or IPv4 + cidr with integer affinity there are no problems:

select  case when typeof(ip) = 'integer' then
 ((ip >> 24) & 255) || '.' ||
 ((ip >> 16) & 255) || '.' ||
 ((ip >> 8) & 255) || '.' ||
 (ip & 255) ||
    case when (ip >> 32) > 0 then '/' || (ip >> 32) else '' end
 else null end ipv4_text,
 ip 
from "type_INET";

P. S. This forum contains nothing about IPv6 with except for this thread.

Mike

I am [a] C developer.

Did you know that you can write custom SQL functions for SQLite and add them at run-time? The fact that SQLite runs in-process, not as a separate server, makes adding custom SQL functions pretty simple. Probably you won't have to write much C code to implement an SQL function for SQLite that does exactly what you want.

There are lots and lots of examples of this kind of thing in the SQLite source tree (as well as from third parties). Search the SQLite source tree for "sqlite3_create_function" to find examples.

Thanks, Richard!

I know about this internal functions and have implemented some set for SQLite FDW by pointed examples.

My question was about a query for users which use standard SQLite without this functions, only SQL. Does it really unable to visualize standard IPv6 text form from a blob affinity value without additional functions?

There are no functions in the standard install of SQLite which understand the dotted IP address format. Almost no user of SQLite needs them.

However, it's not a complicated task. You can write your own external functions and perhaps even find some source code online from other people who've done so. Like you.

You can use instr as a way to get the value of a hex digit, and build upon that to convert a byte of a blob into an integer. The code to do so isn't pretty, and at this point I'd honestly prefer an extension myself, but if you must do it in SQL, here you go:

create table ip_example(ip, pretty);

insert into ip_example values (x'2002000000001234abcdffffc0a80101', '2002::1234:abcd:ffff:c0a8:101');
insert into ip_example values (x'2002000000001234abcdffffc0a8010140', '2002::1234:abcd:ffff:c0a8:101/64');
insert into ip_example values (x'c0a80000', '192.168.0.0');
insert into ip_example values (x'c0a8000010', '192.168.0.0/16');

select
    case 
        when typeof(ip) = 'blob' and (length(ip) = 16 or length(ip) = 17) then 
            lower(concat(
                substr(hex(ip),1,4), ':', 
                substr(hex(ip),5,4), ':', 
                substr(hex(ip),9,4), ':', 
                substr(hex(ip),13,4), ':', 
                substr(hex(ip),17,4), ':', 
                substr(hex(ip),21,4), ':', 
                substr(hex(ip),25,4), ':', 
                substr(hex(ip),29,4)
            )) || 
            case 
                when length(ip) = 17 then concat(
                    '/', 
                    (instr('123456789ABCDEF', substr(hex(ip),33,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),34,1))
                )
                else '' 
            end
        when typeof(ip) = 'blob' and (length(ip) = 4 or length(ip) = 5) then 
            concat(
                (instr('123456789ABCDEF', substr(hex(ip),1,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),2,1)), '.',
                (instr('123456789ABCDEF', substr(hex(ip),3,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),4,1)), '.',
                (instr('123456789ABCDEF', substr(hex(ip),5,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),6,1)), '.',
                (instr('123456789ABCDEF', substr(hex(ip),7,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),8,1))
            ) ||
            case 
                when length(ip) = 5 then concat(
                    '/', 
                    (instr('123456789ABCDEF', substr(hex(ip),9,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),10,1))
                )
                else '' 
            end
        else null
    end as ipv6_text,
    pretty
from ip_example;

Many thanks, RandomCoder!

This is exactly what I need! Official queries for SQLite FDW about IP addresses will based on your contribution. IP addresses support is planned for SQLite FDW before 2025-03. I hope PR with the queries will be on GitHub repo in some weeks.

FYI, here is edited official testing query for SQLite FDW with your contribution:

CREATE VIEW  "type_INET+" AS SELECT *, typeof("ip") t, length("ip") l, cast("ip" as text) tx,
		case 
			when typeof(ip) = 'blob' and (length(ip) = 16 or length(ip) = 17) then 
				lower(
					substr(hex(ip),1,4)  || ':' ||
					substr(hex(ip),5,4)  || ':' ||
					substr(hex(ip),9,4)  || ':' ||
					substr(hex(ip),13,4) || ':' ||
					substr(hex(ip),17,4) || ':' ||
					substr(hex(ip),21,4) || ':' ||
					substr(hex(ip),25,4) || ':' ||
					substr(hex(ip),29,4)
				) || 
				case 
					when length(ip) = 17 then 
						'/' || ((instr('123456789ABCDEF', substr(hex(ip),33,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),34,1)))
					else '' 
				end
			when typeof(ip) = 'blob' and (length(ip) = 4 or length(ip) = 5) then 
					((instr('123456789ABCDEF', substr(hex(ip),1,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),2,1))) || '.' ||
					((instr('123456789ABCDEF', substr(hex(ip),3,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),4,1))) || '.' ||
					((instr('123456789ABCDEF', substr(hex(ip),5,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),6,1))) || '.' ||
					((instr('123456789ABCDEF', substr(hex(ip),7,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),8,1)))
				||
				case 
					when length(ip) = 5 then 
						'/' || ((instr('123456789ABCDEF', substr(hex(ip),9,1)) << 4) + instr('123456789ABCDEF', substr(hex(ip),10,1)))
					else '' 
				end
			when typeof(ip) = 'integer'
				 then ((ip >> 24) & 255) || '.' || ((ip >> 16) & 255) || '.' || ((ip >> 8) & 255) || '.' || (ip & 255) ||
					case when (ip >> 32) > 0 then '/' || (ip >> 32) else '' end
			else null
		end as ip_text
		FROM "type_INET";