Use of Foreign Keys in DB Schema

[switchtower]

Perhaps I'm off the mark here, and if so, go ahead and delete this ticket, but looking at the PostgreSQL schema file, it doesn't appear the 'maps' use a foreign key lookup for the 'subnet' table. This would help reduce the amount of redundant data (serial and subnet in maps table) and perhaps speed up look ups while managing large data sets.

If was done on purpose, again, please ignore. :)

Thanks and keep up the good work. :)

[flan]

It actually does foreign-key, but it does so using those elements as a tuple, trusting that the database will optimise on the sorted primary key to keep things fast.

It was intentional at the time, since, when the project was in its infancy, its operators used PHPMyAdmin to tweak things directly and the human cost of using an integer value was far greater than any calculable loss to machine lookup speed. (Note that entries were automatically populated from a provisioning system, so people just made changes -- typing out the tuple each time would be tedious and there would likely be a different historic schema if that's how it evolved instead)

The model was ported to the other SQL backends for consistency and has been maintained with its central use-case being a human opening a table in a GUI of some sort and tweaking a row as needed, wanting to see the network-ID stuff in-line instead of an integer, and it should scale well to tens of thousands of entries without ever making any practical difference in performance, while also managing a very comparable on-disk footprint, because UNQUEing those two columns would still create an index, plus the two integers and their index, based on what I know of how the major engines work under the hood.

If performance is a factor, though, it's pretty easy to create a custom database class and use that instead, with https://github.com/flan/staticdhcpd/blob/2.0.x/staticDHCPd/extensions/official/httpdb.py showing the structure of a class that uses REST/JSON instead of a more traditional data-source -- you only need to implement lookupMAC() and, if you course options from conf.py, init() to defer the import; I can provide assistance if required.

On 14 July 2015 at 10:31, switchtower notifications@github.com wrote:

Perhaps I'm off the mark here, and if so, go ahead and delete this ticket, but looking at the PostgreSQL schema file, it doesn't appear the 'maps' use a foreign key lookup for the 'subnet' table. This would help reduce the amount of redundant data (serial and subnet in maps table) and perhaps speed up look ups while managing large data sets.

If was done on purpose, again, please ignore. :)

Thanks and keep up the good work. :)

— Reply to this email directly or view it on GitHub https://github.com/flan/staticdhcpd/issues/44.

[switchtower]

Thanks for the response. :) The HTTPDB extension looks promising, given how much I use Django in my projects. I could easily write something with the Django Rest Framework to create the API, but the only thing holding me back is the need for a single MAC address to have multiple IP addresses assigned to it from different subnets. I have users, with tablets, that travel from building to building and require the same IP address every time they connect. Not being the DHCP/RFC expert you are, perhaps there's something out there that would handle this for me or an easy, straight forward way of also sending the source network in the GET or POST to the REST API call?

Thanks for your help. :)

[flan]

The concept of a "subnet" is purely arbitrary in this system -- it's just there for legacy purposes and is actually a free-form string that serves only as an internal identifier: http://static.uguu.ca/projects/staticDHCPd/doc/commentary/database.html#subnet

For what you've described, I don't see any reason why you'd need to assign multiple IPs, as long as the clients can always hit the same gateway from whatever real-world subnet they happen to be on; if that's not the case, though, you might need to conditionally load a static route into the DHCP packets, unless the tablets are already configured to somehow do this themselves: http://static.uguu.ca/projects/staticDHCPd/doc/examples/index.html#examples and https://github.com/flan/staticdhcpd/blob/2.0.x/libpydhcpserver/libpydhcpserver/dhcp_types/constants.py#L360 (I thought I had the option-list somewhere in the docs, but I might have just included a link and that might be broken with the move from Google to github, so just use that; its type is IPV4_PLUS, so give it any number of IPv4 addresses (strings, objects, integers, whatever) in sets of two, with the first being the destination and the second being the router: http://www.networksorcery.com/enp/protocol/bootp/option033.htm)

If you're creating your own database interface, though, you can do whatever magic you want. I'd probably let the system return an IP the normal way (or, heck, just make your database class generate complete dummy data, with no real database backing the server at all), then examine the relay address (giaddr in literature, relay_ip in http://static.uguu.ca/projects/staticDHCPd/doc/customisation/scripting.html#loaddhcppacket), and set the fields you need to support the client on its current link. Having an IP roam across routers might create confusion at the routing protocol layer, though, unless your ARP timeout is really short and nothing proxies incorrectly. I'm not really a networking person, though, just an linguistics enthusiast who wrote a DHCP server as part of a job.

REST performance is always going to be much, much slower than a two-item match in a join, though, if you're worried about optimisation.

On 14 July 2015 at 12:47, switchtower notifications@github.com wrote:

Thanks for the response. :) The HTTPDB extension looks promising, given how much I use Django in my projects. I could easily write something with the Django Rest Framework to create the API, but the only thing holding me back is the need for a single MAC address to have multiple IP addresses assigned to it from different subnets. I have users, with tablets, that travel from building to building and require the same IP address every time they connect. Not being the DHCP/RFC expert you are, perhaps there's something out there that would handle this for me or an easy, straight forward way of also sending the source network in the GET or POST to the REST API call?

Thanks for your help. :)

— Reply to this email directly or view it on GitHub https://github.com/flan/staticdhcpd/issues/44#issuecomment-121338419.

[switchtower]

The issue, with the network I manage, there's some authorization done based on IP address, there's multiple locations all under the same autonomous system, and IP address, in different locations, need to communicate with each other.

So, a laptop that is in one building needs to be given a specific IP address so it can communicate with devices in a different building, but still receive an IP address, in that other building, from the same L2 network so it can communicate without having to hit the default gateway or perform any routing black magic (to keep things simple).

I'll continue to look to see if there's anything I can do, so again, thank you a ton for your responses and detailed explanation of things. :)

[switchtower]

So, perhaps providing the requesting network in the httpdb extension would solve my issue. Modifying the httpdb.py to add the requesting network should be pretty easy, but figuring out how to get that information to the extension will be a different story for me. I'll have to look through the documentation on how to do just that.

[flan]

Where does the information come from? If your network is fairly static overall, you could just keep a static map in loadDHCPPacket() and do some transformative work there, assuming any is actually required, or otherwise use the 'extra' attribute of the lease Definition (it's covered near the top of the httpdb module) to store whatever identifiers you need from your database and do the magic in loadDHCPPacket() based on that: http://static.uguu.ca/projects/staticDHCPd/doc/customisation/scripting.html#loaddhcppacket gets the same Definition object you returned before

On 20 July 2015 at 08:35, switchtower notifications@github.com wrote:

So, perhaps providing the requesting network in the httpdb extension would solve my issue. Modifying the httpdb.py to add the requesting network should be pretty easy, but figuring out how to get that information to the extension will be a different story for me. I'll have to look through the documentation on how to do just that.

— Reply to this email directly or view it on GitHub https://github.com/flan/staticdhcpd/issues/44#issuecomment-122904230.

[switchtower]

The information is coming from another server setup to server up the JSON data needed to create the DHCPPacket. Because we're using relay's, I'm assuming I could use the relay IP address to search the db for a corresponding network and then lookup the mac address in a list of static addresses with the previous network as a foreign key, but how would I get the relay_ip into the 'database' lookup?

[flan]

If the MAC isn't transformed by any of the relays, just do the normal MAC-to-IP mapping thing behind the JSON layer and do some additional transformative work (another query, a hardcoded mapping) in loadDHCPPacket()

Or use the Null database type and do everything in http://static.uguu.ca/projects/staticDHCPd/doc/customisation/scripting.html#handleunknownmac, in which you have free-form reign over what you do with the received information, to the fullest extent allowed by Python, as long as you can return a Definition

On 20 July 2015 at 10:02, switchtower notifications@github.com wrote:

The information is coming from another server setup to server up the JSON data needed to create the DHCPPacket. Because we're using relay's, I'm assuming I could use the relay IP address to search the db for a corresponding network and then lookup the mac address in a list of static addresses with the previous network as a foreign key, but how would I get the relay_ip into the 'database' lookup?

— Reply to this email directly or view it on GitHub https://github.com/flan/staticdhcpd/issues/44#issuecomment-122927973.