On Sun, Apr 20, 2008 at 09:29:34AM -0600, Dennis Cote scratched on the wall: > Jay A. Kreibich wrote: > > > > It breaks for everything except network_size == 16. > > > Why do you say that? > > You want something closer to (ip_addr & (~(~0 << network_size))) > > > In SQLite ~0 is -1. > > sqlite> select ~0; > -1 > > So your inner expression is only a more complicated way of saying the > same thing.
Each is a simple literal with a unary operator. How is that more complex? ~0 is also a bit-level operator that works with both signed and unsigned types, while -1 depends on a specific signed integer representation. > By complementing the result of the inner expression you have generated > an invalid netmask pattern with zeros in the high bits and ones in the > low bit positions. Yeah, I screwed that up. I was too caught up on the other error. In the original function (-1 << network_size) returns the wrong bit mask. For example, 10.0.0.0/8 should return a netmask of 255.0.0.0, or 0xFF000000. The original function will return 255.255.255.0, which is a /24 mask. My mind was thinking "you need to flip that", but did the wrong thing. We're looking for (ip_addr & (~0 << (32 - network_size))). Technically, you also need to mask the network side, as it is an acceptable notation to have a network like "10.0.0.1/8". > > Again, that only works for v4. > > > It was only intended to work for IPv4 as shown in the OP. Clearly, although in a followup to one of your earlier posts the original poster said he was interested in both v4 and v6. It would still be easy enough to build functions that can deal with those. This thread got me thinking, although not really about IP addresses. INET and CIDR are built-in types for Postgres, but part of the reason Postgres has so many weird types is that it is very easy to build user-defined types in the Postgres engine. SQLite already allows you to put pretty much anything you want in the type field of a CREATE TABLE statement. It also has the most flexible type systems of any RDBMS environment that I've worked with. I'm wondering how hard it would be to create a "USER DEFINED TYPE" affinity that could be associated with a series of user-functions like "input text"->type, or type->"display text." In many cases, I would assume the "storage type" would be a blob, but SQLite's manifest typing means it could be just about any native type. Some other affinity conversion functions and collation functions and it might not be that hard to define arbitrary user types. I don't understand the way SQLite tracks values types internally enough to really understand the cost... it just got me thinking. Given some of the unique ways that SQLite is used, there might be some value in user types. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
