Re: [sqlite] Have SQLite handle values of my own type
Hi, IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of the four parts is always three digits long. IPv4 addresses are 32bit unsigned integers internally. The dotted -quad notation is 4 8bit unsigned integers that get concatenated together. If you store them as a 32bit integer in SQLite then you can use the regular arithmetic operators such as < and > to work out if a given address is in a particular subnet. You can also use the BETWEEN clause. Over Christmas I was working on an encoding for arbitrary struct-style datatypes that outputs valid UTF-8. It has the interesting property that the encoded values sort in the same order as the decoded values. i.e. the integer 2 sorts before 10 rather than what you'd get if you made the naive conversion to text. The primitive types it knows about include boolean, number, text and blob. The number type is really cool because it can represent any rational number, and the sorting property holds. The primitive types can be combined into compound types and you can control the relative order that different compound types sort in. I'm using this to implement a database-style system where the data may be in different backends (i.e. SQLite, Postgres or CSV) at different times but I need the ordering and the available types to be stable. The downside is that it's not "fast" to encode or decode and it's not as compact as a machine native encoding. Having said that, there are operations that can be done directly on the encoded data such as sorting, comparison and composition. It's not quite on topic for this list but if anyone's interested I can provide more information and links to implementations in Scheme and Javascript. andy...@ashurst.eu.org http://www.ashurst.eu.org/ http://www.gonumber.com/andyjpb 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
Windows TCP/IP userland used to be a port of the BSD networking tools (I
think via Lachman Associates) so that's not surprising. Allegedly they
reimplemented it at some point.
On Thu, May 23, 2019 at 3:57 PM Keith Medcalf wrote:
> On Thursday, 23 May, 2019 08:35, Dominique Devienne
> wrote:
>
> >On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera
> > wrote:
> >
> >> I have been working network for a long time, and I have never seen
> >> any application that takes "zeroed left-filled" IP addresses. Just
> >> sharing...
> >> Thanks.
>
> > Works for me with a .001 at least, as shown below. But that wasn't
> > really the point I was making, FWIW. --DD
>
> Well, actually, that depends on how the code in the network stack converts
> from presentation format to network format. Windows 10 1903 (at least)
> treats each component as an input number which means that a number that
> starts with a 0 is an octal number ... (ie, the BSD way)
>
> >ping 172.217.14.227
>
> Pinging 172.217.14.227 with 32 bytes of data:
> Reply from 172.217.14.227: bytes=32 time=26ms TTL=56
> Reply from 172.217.14.227: bytes=32 time=26ms TTL=56
>
> >ping 172.217.014.227
>
> Pinging 172.217.12.227 with 32 bytes of data:
> Reply from 172.217.12.227: bytes=32 time=73ms TTL=45
> Reply from 172.217.12.227: bytes=32 time=72ms TTL=45
>
> Other parsers may see the input as invalid:
>
> sqlite> select ipblobaddr(ipaddrblob('172.217.014.227'));
>
> sqlite> select ipblobaddr(ipaddrblob('172.217.14.227'));
> 172.217.14.227
>
> So really, what you get depends on who wrote the code that is doing the
> translation. Since the code that I used to implement ipblobaddr and
> ipaddrblob is taken from the ISC DNS Bind code based on code written by
> Paul Vixie in 1996, it is quite possible that many things will see leading
> 0's as invalid input. Some things (for example Cisco IOS) may also choose
> to just ignore the extra 0's. Other things may take it as an indicator
> that the value is base-8 rather than base-10.
>
> See
> https://tools.ietf.org/html/draft-main-ipaddr-text-rep-00
> https://en.wikipedia.org/wiki/Dot-decimal_notation
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
On Thursday, 23 May, 2019 08:35, Dominique Devienne wrote:
>On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera
> wrote:
>
>> I have been working network for a long time, and I have never seen
>> any application that takes "zeroed left-filled" IP addresses. Just
>> sharing...
>> Thanks.
> Works for me with a .001 at least, as shown below. But that wasn't
> really the point I was making, FWIW. --DD
Well, actually, that depends on how the code in the network stack converts from
presentation format to network format. Windows 10 1903 (at least) treats each
component as an input number which means that a number that starts with a 0 is
an octal number ... (ie, the BSD way)
>ping 172.217.14.227
Pinging 172.217.14.227 with 32 bytes of data:
Reply from 172.217.14.227: bytes=32 time=26ms TTL=56
Reply from 172.217.14.227: bytes=32 time=26ms TTL=56
>ping 172.217.014.227
Pinging 172.217.12.227 with 32 bytes of data:
Reply from 172.217.12.227: bytes=32 time=73ms TTL=45
Reply from 172.217.12.227: bytes=32 time=72ms TTL=45
Other parsers may see the input as invalid:
sqlite> select ipblobaddr(ipaddrblob('172.217.014.227'));
sqlite> select ipblobaddr(ipaddrblob('172.217.14.227'));
172.217.14.227
So really, what you get depends on who wrote the code that is doing the
translation. Since the code that I used to implement ipblobaddr and ipaddrblob
is taken from the ISC DNS Bind code based on code written by Paul Vixie in
1996, it is quite possible that many things will see leading 0's as invalid
input. Some things (for example Cisco IOS) may also choose to just ignore the
extra 0's. Other things may take it as an indicator that the value is base-8
rather than base-10.
See
https://tools.ietf.org/html/draft-main-ipaddr-text-rep-00
https://en.wikipedia.org/wiki/Dot-decimal_notation
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
On 23 May 2019, at 12:02pm, Dominique Devienne wrote: > On Thu, May 23, 2019 at 12:37 PM Simon Slavin wrote: > >> IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of the >> four parts is always three digits long. > > Sure. But representing it as 001.001.001.001 for 1.1.1.1 is not usual. You'd > better have a CHECK constraint to enforce your format. You misunderstood. This is a storage, selection and sorting format. Not one for interaction with users. Much like you would never show a user a number like 2348923.6484 without putting commas in or something. On 23 May 2019, at 4:35pm, Dominique Devienne wrote: > C:\Users\ddevienne>ping 10.65.9.030 > Pinging 10.65.9.24 with 32 bytes of data: OMG. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
On Thu, May 23, 2019 at 5:14 PM Jen Pollock wrote: > This is getting pretty far off topic, but I think at least some tools > will interpret values with leading zeroes as octal, which means 001 is > the same as 1, but 010 isn't 10, it's 8. > Good catch! That's indeed what's happening. Win7 BTW. --DD C:\Users\ddevienne>ping 10.65.9.30 Pinging 10.65.9.30 with 32 bytes of data: ... C:\Users\ddevienne>ping 10.65.9.030 Pinging 10.65.9.24 with 32 bytes of data: ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
This is getting pretty far off topic, but I think at least some tools will interpret values with leading zeroes as octal, which means 001 is the same as 1, but 010 isn't 10, it's 8. On Thu, May 23, 2019 at 04:35:02PM +0200, Dominique Devienne wrote: > On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera > wrote: > > > I have been working network for a long time, and I have never seen any > > application that takes "zeroed left-filled" IP addresses. Just sharing... > > Thanks. > > > > Works for me with a .001 at least, as shown below. But that wasn't really > the point I was making, FWIW. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
Dominique Devienne, on Thursday, May 23, 2019 10:35 AM, wrote... >On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera >wrote: > >Works for me with a .001 at least, as shown below. But that wasn't really >the point I was making, FWIW. --DD > >C:\Users\ddevienne>ping 192.168.223.001 > >Pinging 192.168.223.1 with 32 bytes of data: >Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 >Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 >Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 >Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 You must have one of those new Windows 10 machines. ;-) They probably have some trick to get rid of the pre-zeroes-filled numbers. If you look at the response, it does not have the 001. Just sayin'... :-) Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
On Thu, May 23, 2019 at 3:50 PM Jose Isaias Cabrera wrote: > I have been working network for a long time, and I have never seen any > application that takes "zeroed left-filled" IP addresses. Just sharing... > Thanks. > Works for me with a .001 at least, as shown below. But that wasn't really the point I was making, FWIW. --DD C:\Users\ddevienne>ping 192.168.223.1 Pinging 192.168.223.1 with 32 bytes of data: Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 Ping statistics for 192.168.223.1: Packets: Sent = 2, Received = 2, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms Control-C ^C C:\Users\ddevienne>ping 192.168.223.001 Pinging 192.168.223.1 with 32 bytes of data: Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 Reply from 192.168.223.1: bytes=32 time<1ms TTL=128 Ping statistics for 192.168.223.1: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
Dominique Devienne, on Thursday, May 23, 2019 07:02 AM, wrote... >On Thu, May 23, 2019 at 12:37 PM Simon Slavin wrote: >> On 23 May 2019, at 3:55am, Keith Medcalf wrote: >> > Technically, COLLATE only works on TEXT. Most people declare their own >> types as binary blobs and the programmer has to keep track of what is in >> there and how to work with it. >> >> So it would seem that rather than define a function which turns a value of >> my type into a BLOB, it would be better to create one which turns it into >> some sort of normalised text form. Then I can create whatever COLLATEs I >> need to do my specialised sorting and handling. >> >> IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of >> the four parts is always three digits long. > > >Sure. But representing it as 001.001.001.001 for 1.1.1.1 is not usual. >You'd better have a CHECK constraint to enforce your format. >Otherwise lexical ordering won't work with non-expected (but more natural) >1.1.1.1. >While writing a custom collation (the so called "natural ordering") would >work for either format. --DD Microsoft ping fails with 010.116.082.097, 9:45:39.71>ping 010.116.082.097 Ping request could not find host 010.116.082.097. Please check the name and try again. but, works with the normal unzeroed format, 9:45:55.21>ping 10.116.82.97 Pinging 10.116.82.97 with 32 bytes of data: Reply from 10.116.82.97: bytes=32 time<1ms TTL=128 Reply from 10.116.82.97: bytes=32 time<1ms TTL=128 Reply from 10.116.82.97: bytes=32 time<1ms TTL=128 Reply from 10.116.82.97: bytes=32 time<1ms TTL=128 Ping statistics for 10.116.82.97: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 0ms, Maximum = 0ms, Average = 0ms I have been working network for a long time, and I have never seen any application that takes "zeroed left-filled" IP addresses. Just sharing... Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
Don't forget IPv6 addresses. On Thu, May 23, 2019, 3:37 AM Simon Slavin wrote: > On 23 May 2019, at 3:55am, Keith Medcalf wrote: > > > Technically, COLLATE only works on TEXT. Most people declare their own > types as binary blobs and the programmer has to keep track of what is in > there and how to work with it. > > So it would seem that rather than define a function which turns a value of > my type into a BLOB, it would be better to create one which turns it into > some sort of normalised text form. Then I can create whatever COLLATEs I > need to do my specialised sorting and handling. > > IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of > the four parts is always three digits long. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
On Thu, May 23, 2019 at 12:37 PM Simon Slavin wrote: > On 23 May 2019, at 3:55am, Keith Medcalf wrote: > > Technically, COLLATE only works on TEXT. Most people declare their own > types as binary blobs and the programmer has to keep track of what is in > there and how to work with it. > > So it would seem that rather than define a function which turns a value of > my type into a BLOB, it would be better to create one which turns it into > some sort of normalised text form. Then I can create whatever COLLATEs I > need to do my specialised sorting and handling. > > IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of > the four parts is always three digits long. Sure. But representing it as 001.001.001.001 for 1.1.1.1 is not usual. You'd better have a CHECK constraint to enforce your format. Otherwise lexical ordering won't work with non-expected (but more natural) 1.1.1.1. While writing a custom collation (the so called "natural ordering") would work for either format. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
On 23 May 2019, at 3:55am, Keith Medcalf wrote: > Technically, COLLATE only works on TEXT. Most people declare their own types > as binary blobs and the programmer has to keep track of what is in there and > how to work with it. So it would seem that rather than define a function which turns a value of my type into a BLOB, it would be better to create one which turns it into some sort of normalised text form. Then I can create whatever COLLATEs I need to do my specialised sorting and handling. IP addresses are easy: convert to the form aaa.bbb.ccc.ddd where each of the four parts is always three digits long. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Have SQLite handle values of my own type
On Wednesday, 22 May, 2019 19:06, Simon Slavin wrote: >Since there are people posting who appear know about these things … >Suppose I want SQlite to handle my own type. Or to do its best to >simulate that. IP address, x/y location, something like that. What >should I be doing ? Do I store BLOBs and define my own COLLATEs ? >Or didn't I read somewhere that COLLATE works only on TEXT ? Technically, COLLATE only works on TEXT. Most people declare their own types as binary blobs and the programmer has to keep track of what is in there and how to work with it. In the case of IPAddresses it is a little simpler, since they are really just text strings, so writing an IPADDRESS collation to apply to text strings that contain IP Addresses and a function like IPSubnetContains is pretty straightforward. Other complex data types are more difficult since there is not builtin support for types. http://www.dessus.com/files/ipaddress.c The code is a little inefficient but it works ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Have SQLite handle values of my own type
Since there are people posting who appear know about these things … Suppose I want SQlite to handle my own type. Or to do its best to simulate that. IP address, x/y location, something like that. What should I be doing ? Do I store BLOBs and define my own COLLATEs ? Or didn't I read somewhere that COLLATE works only on TEXT ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
