Reimplement natural sorting for interfaces

[jeremystretch]

Issue type

[x] Feature request
[ ] Bug report
[ ] Documentation

Environment

• Python version: 3.5.2
• NetBox version: 2.3.4

Description

We've had a number of issues raised around the natural ordering of device and virtual machine interfaces, which feels like a moving target. Currently, we use an offensive array of regular expressions to break apart and sort interface names at query time:

TYPE_RE = r"SUBSTRING({} FROM '^([^0-9]+)')"
ID_RE = r"CAST(SUBSTRING({} FROM '^(?:[^0-9]+)(\d{{1,9}})$') AS integer)"
SLOT_RE = r"CAST(SUBSTRING({} FROM '^(?:[^0-9]+)?(\d{{1,9}})\/') AS integer)"
SUBSLOT_RE = r"COALESCE(CAST(SUBSTRING({} FROM '^(?:[^0-9]+)?(?:\d{{1,9}}\/)(\d{{1,9}})') AS integer), 0)"
POSITION_RE = r"COALESCE(CAST(SUBSTRING({} FROM '^(?:[^0-9]+)?(?:\d{{1,9}}\/){{2}}(\d{{1,9}})') AS integer), 0)"
SUBPOSITION_RE = r"COALESCE(CAST(SUBSTRING({} FROM '^(?:[^0-9]+)?(?:\d{{1,9}}\/){{3}}(\d{{1,9}})') AS integer), 0)"
CHANNEL_RE = r"COALESCE(CAST(SUBSTRING({} FROM ':(\d{{1,9}})(\.\d{{1,9}})?$') AS integer), 0)"
VC_RE = r"COALESCE(CAST(SUBSTRING({} FROM '\.(\d{{1,9}})$') AS integer), 0)"

This is necessary because NetBox does not limit support to any particular platform, so interface names can take any arbitrary format. We try to capture all reasonably common scenarios, from e.g. eth0 to xe-0/1/2:3.456.

As a more maintainable approach, I'm considering moving this logic to a function called when an object is saved. We can add a set of integer columns to the Interface model to store each potential value from the set of regular expressions above: id, slot, position, etc. When an interface object is saved, these values are extracted from its name and recorded in the appropriate table columns. This allows us to sort on those numeric values naturally instead of having to extract and cast each at query time from the name string.

For example, given the following list of interfaces:

xe-0/0/0
xe-0/0/1
xe-0/2/0
xe-0/2/1
xe-1/0/0:0
xe-1/0/0:1
lo0
irb.123

The resulting table would look something like this (simplifying a bit here for clarity):

name	slot	position	id	channel	vc
xe-0/0/0	0	0	0	null	null
xe-0/0/1	0	0	1	null	null
xe-0/2/0	0	2	0	null	null
xe-0/2/1	0	2	1	null	null
xe-1/0/0:0	1	0	0	0	null
xe-1/0/0:1	1	0	0	1	null
lo0	null	null	0	null	null
irb.123	null	null	null	null	123

Ordering interfaces would then be as simple as ordering by (slot, position, id, channel, vc, name). (Name would come either first or last in the series depending on the method of ordering chosen for the device type.)

It's probably not an ideal solution but would be much more maintainable long-term than the current approach. I'm open to other suggestions.

[candlerb]

More generally, you could just split into alternate text and numeric parts:

>>> import re
>>> ifname = "xe-0/1/2:3.456"
>>> re.split(r'(\d+)', ifname)
['xe-', '0', '/', '1', '/', '2', ':', '3', '.', '456', '']
>>> ifname = "4/5"
>>> re.split(r'(\d+)', ifname)
['', '4', '/', '5', '']

Every alternate element is numeric, and can be converted with int(). At that point, interfaces are directly comparable:

>>> ['xe-', 0, '/', 1, '/', 2, ':', 3, '.', 456, ''] > ['ge-', 1, '/', 1]
True
>>> ['ge-', 0, '/', 0, '/', 0] > ['ge-', 0, '/', 0]
True

Doing this in postgres for server-side sorting might be a bit trickier. Could the interface name be decomposed into an array of a composite type of (string, integer) ? Or a JSON column?

[jeremystretch]

This might work, but I'm worried about scenarios where you have differing naming formats on one box. For example, on a Junos device you might have:

• xe-0/0/0 (slot/position/ID)
• xe-1/0/0:0 (slot/position/ID:channel)
• fxp0 (ID only)
• irb.123 (virtual circuit only)

With the current approach, fxp0 will be ordered after the xe interfaces, because slot is ordered before ID (fxp0 has a slot of null, which is ordered after zero). If we order only by the first number without regard to its "role," fxp0 will come before xe-1.

[DanSheps]

I hope this goes without saying, but I think supporting full names as well as their abbreviations would be something to look at as well.

In playing with some automation, ansible won't work with the abbreviations for certain things.

[jeremystretch]

I think supporting full names as well as their abbreviations would be something to look at as well.

Interfaces should be named as they appear on the platform. NetBox makes no assumptions with regard to abbreviations. This would be unrelated to ordering anyway.

[DanSheps]

I was referring more to the proposed split, I didn't want it to get missed in the shuffle where the assumption was made to only match on Gi/Xe or otherwise.

[jeremystretch]

Closed this in e97708a. After tweaking the coalescing and ordering of fields, DeviceType.interface_ordering is no longer needed. I've also introduced a test case for evaluating the ordering of some example Interface sets.