Hi All, I am using sqlite to make an IP routing table. A routing table has a Destination and a Mask column. I chose blob for my types since IPv6 will be 16 bytes long, IPv4 is only 4 bytes.
The query will be some IP packet destination address that needs to be forwarded in the internet. This address is anded bitwise with the mask in the table then tested for equality with the desination. All the rows that pass this test will be candidates, then the row with the longest mask length is chosen. Packet is then forwarded towards the nexthop address that was stored as another column in the same table row. An IPv4 Example: create table rtm(dest blob, mask blob, nh blob); create index rtmi on rtm(dest); select nh from rtm where dest = bitwise_and(addr, mask) order by mask desc limit 1; hex(dest) hex(mask) rowid ---------------- ------------------ ----------- 01010100 FFFFFF00 1 00000000 000000000 2 01010000 FFFF0000 3 01000000 FF000000 4 04048000 FFFFC000 5 77777780 FFFFFFF0 6 So for a packet dest address 01010165, there will be four hits row 1,2,3,4 but 1 has the longest bit mask. Every thing works except for the indexing. My custom function bitwise_and is always called for all the rows in my table. Which means that indexing is not being used. What should I do to get around this problem. Typically I will have about 200,000 enteries in my routing table for the internet and I want to be more efficient and not query every row. I am open to all suggestions. Thanks, Alex
