IPv4 addresses and networks relation

[spo0okie (Alexander Reviakin)]

Hi.
I using in my app some data about IPv4 adresses and Networks.
They stored in DB as integers. Simple it looks like

Networks:
id (PK)
addr (UNSIGNED INT)
mask (UNSIGNED INT) //11111111111111111111111100000000 (in binary)
mask_prefix (int) // 24 in decimal

Ips:
id (PK)
addr (UNSIGNED INT)

And logicaly (and mathematicaly) they are in a strait relations between each other:
Network mask says how many left bits (of 32 max) is fixed in network adresses, so if ip address is compare to network address in fixed network mask part it relates to network, otherwise - it not
here sample SQL conditions for that relation:
thru prefix (calculates minimal and maximal addresses in network)
(networks.addr <= net_ips.addr ) AND ((networks.addr + POWER(2,(32-networks.mask_prefix)) > net_ips.addr))

thru binary mask
(networks.addr & networks.mask) = (net_ips.addr & networks.mask)

so
in math logic that tables already related to each other as Parent-Child, just without a keys, and i know how to use it in raw SQL
but
i heavily need to make activeRecord realations with all tasty features of it as joinWith, eager loading and relation itself

if i do Networks to IP relation like this
return NetIps::find()->where([‘AND’,
[’>=’,‘addr’,$this->addr],
[’<’,‘addr’,$this->addr+$this->capacity] //capacity calculates from mask
])->all();
It works as relation, it give me IPs, but i cant use joinWith with that relation (it says that it is no relation at all)

if i do like that
return new ActiveQuery(NetIps::className(),[
‘link’=>[],
‘on’=>"(networks.addr <= net_ips.addr ) AND ((networks.addr + POWER(2,(32-networks.mask)) > net_ips.addr))",
‘multiple’=>true,
]);
then i see it bulds correct SQL join and request all correctly, but it not works as simple relation. it returns activeQuery class instead of NetIps forked from ActiveRecord

I need somehow declare a relation but without keys link
Help me please, give me at least a direction

[softark]

Hi @spo0okie, welcome to the forum.

The idea of yours is very interesting. So I spent some time reading framework’s source code and writing scratches for testing before I write this reply to you.

I’m not very happy to say it, but as long as I don’t miss something obvious, you have to have a relation based on a foreign key in order to establish a relation between 2 ActiveRecord models.

It’s true that the address range of a network and individual address in it have a 1 to N relation, but you can’t use it to link ActiveRecord models.

[spo0okie (Alexander Reviakin)]

May be i can make custom class from some base class? overwrite some methods? Please help me to look right way. I not very good to Yii and will not find fast best way. But i need it much
I have very deep relations
Room -> Computers hardware in it -> Operating systems installed on it -> IP adresses set up on it -> IP networks it fits in -> Vlans consist that network -> Security segments of that VLAN (open/closed perimeter/DMZ) -> Segement specs
And if that chain break in middle, then very bad things happen to DB requests count because lack of eager loading.
I ready to spend much time to rewrite some methods, it saves me much more instead. Just help me what classes i need look in to, i cant uderstand logic of Yii, it is very complicated at first sight.

[softark]

I would add a foreign key to ip table that refers network table.

I believe it’s the simplest, the fastest, the most straightforward and the most robust solution. I don’t understand why you have to avoid adding the foreign key.

I think you can set and update it automatically when you create or update a record in ip table by comparing the ip address to the addresses of the existing networks.

[spo0okie (Alexander Reviakin)]

because of need of mass recalc relations on save.
if i save 1 network i need recalc all IPs to check if they relate to that network. And IPs - it is kind of object which can counts of thousands. (i have bunch of /23 networks in my inventory, which can consists 512 IPs) It sounds like soulution which not very good at scale IMHO.

And you konw. In some logic it is a straitforward solution (in logic that you have fixed instruments and a task to deal)
And in some logic it just a hack to not to write custom instrument for custom relation logic.

I am not professional programmer, i just love it, and when i started to code (1990s) - computers was very expensive and slow and good style of code was to code more to compute less )) i understand that it is not correct rule now. but if i do such hack, i just will not like it)

i wish to try to make custom child from some custom class from some base class, where just overload some methods to make custom join, custom get, custom save (there will be som dummy method, becuase it is indirect relation) and etc…

if you can help me to find where that methods are, i will be very gratefull

but i afraid that it can be that there is no exact class, may be code spread in a lot of classes and solution to rewrite them all will be ugly

Added later:
I thought about keys in IPs.
I don’t need to recalc all IPs on network Save.
I need find

• which were attached before save (to old network address)
• attached after save (to new network address)

That not really all of IPs in DB of course. But this is additional save of Key field in 10-1000 objects on network change. (and each IP save is not simple as just set. for each of detached IP objects we need in first find new Network object to link).
It will work. of course. But it will not be beatiful )
i afraid to build something fast enough to put it in prod, and slow enough to make it ususable soon after data scales up some
it is hard to me to use that as solution while i understand that i need 1 (simple!) req in DB to find IPs in network and 1 (simple!) req for find network for IP.

isn’t it interesting to make beatiful solution for active record with relations more complex than just foreign keys? It will make Yii more flexible and suitable for much more applications
Sorry for my stubborness and bad english)

[batyrmastyr]

you have to have a relation based on a foreign key

No, it’s not required to have explicit foreign key. It’s even possible to have parent table in MySQL and child in MongoDB etc. Still Yii needs explicit list of attributes values that are considered as relation.

[spo0okie (Alexander Reviakin)]

in Network table i have column addr, which is equal to IPmin, and i can add IPmax (will calc easily it on Save)
at IPs table there is already addr column
condition will be IP.addr >= Network.addr and IP.addr<=Network.IPmax

But i still do not understand how to add relation which uses comparing of two columns

If we simplify that task and abstracting from IP and Networks - it reduces to build relation between table of single integer and table of ranges of two integers (min,max)

[softark]

@batyrmastyr

you have to have a relation based on a foreign key

No, it’s not required to have explicit foreign key. It’s even possible to have parent table in MySQL and child in MongoDB etc. Still Yii needs explicit list of attributes values that are considered as relation.

Thank you @batyrmastyr. You are right.

It’s true that a foreign key constraint is not required, and also the relation between a relational db and a non-sql db is possible.

But the linkage between the 2 models must be defined with an array of [key => value] where key is a column name of a related table and value is a column name of this table, and the comparison between them must be an exact match (==) of raw values. This is the main obstacle to implement @spo0okie’s idea in which he wants to make a range comparison (>=, <, etc.) with calculated values.

[batyrmastyr]

Well, I suppose it’s quite hard to accomplish as Yii needs a “static” attribute values to link Records. I mean you need ip=127.0.01, not ip between ‘127.0.01’ and ‘127.0.0.3’.
I may be wrong, but it’s worth a try to pass an array as value:

public function getIps()
{
   return this->hasMany(Ip::class, [ip => 'possibleIps'])
}

protected function getPossibleIps()
{
    return [<list of network ips>];
}

Writing from a phone is not fun, so beware of some syntax errors in this code.

Yeah, it’ll generate quite lengthy requests

[softark]

Just to make things clearer, let me illustrate how the relational query works when we try to access the related models.

In a lazy loading scenario:

$ips = Ip::find()->all();
// select * from ip
foreach($ips as $ip) {
    echo $ip->network->id;
   // select * from network where id = $ip->network_id
}

This requires 1+N queries.

And in an eager loading scenario:

$ips = Ip::find()->with('network')->all();
// select * from ip
// make an array of all the unique network_id's from $ips array
// select * from network where id in ($network_ids)
foreach($ips as $ip) {
    echo $ip->network->id;
    // no sql query here
}

This requires 2 queries.

Note that the relational query in eager loading uses IN clause, which makes things harder for range comparison.

[softark]

@spo0okie

because of need of mass recalc relations on save.
if i save 1 network i need recalc all IPs to check if they relate to that network.

I think you can use yii\db\ActiveRecord::updateAll() method (or underlying yii\db\Command::update() method) to update network_id of ips when you have updated the address of a network.

$network = Network::findOne($id);

// delete the previous relations
Ip::updateAll(['network_id' => null], ['network_id' => $network->id]);
// create the new relations
Ip::updateAll(['network_id' => $network->id], [
    'and',
    ['>=', 'address', $network->address],  
    ['<', 'address', $network->address_max]
]);

Usually 'updateAll()` is quite fast for simple updating, and you don’t have to worry about the performance.

I’ve made a test case of randomly created 1,000 networks each with a mask of 22 to 24, and also randomly created 379,460 ips belonging to them (average 380 ips per network).

It costed 17 seconds to update all the network records and the corresponding ip records, i.e., 0.017 second to update a single network address and its related ips. I think it’s fast enough for a large scale network site, isn’t it?

(7 years old laptop with a dual core CPU, 16GB memory, 512 GB SSD, mariaDB and PHP 7.3)

[spo0okie (Alexander Reviakin)]

That’s a lot of work.
Thank you for your time.
I’ll try that and report results in couple days

[spo0okie (Alexander Reviakin)]

So.
Real task was little more complicated than i questioned. (That’s because i simplified question)
For real task we need also make choice which of many networks that can fit to our ip we will relate
For examle IP addr 192.168.0.1 fits to networks

• 192.168.0.0/31 (192.168.0.0 - 192.168.0.1)
• 192.168.0.0/30 (192.168.0.0 - 192.168.0.3)
• 192.168.0.0/29 (192.168.0.0 - 192.168.0.7)
• and so on, down to /0 mask

i can ignore that question and select just first found, but in that case i can get result in which

• 192.168.0.1, 192.168.0.3 - goes to one network (for example /29 from above)
• 192.168.0.2 - goes to another (for example /30 from above)

Just because of Holy Random wish that. That is absolutely mess for me)

So i prefered to use exact algorythm (which used in real world - in route tables on routers) - selecting less wide area
So i made next solution :

class NetIps extends \yii\db\ActiveRecord
{
	// --- skipped some stuff not related to our subject ---

	/**
	 * Network by Addr/Mask, not by foreign key
	 * (if many networks fits, then select least wide)
	 * @return Networks
	 */
	public function findNetwork()
	{
		return Networks::find()
			->where([	'AND',
				['<=','addr',$this->addr],
				['>','addr + Power(2,32-mask)',$this->addr]
			])
			->orderBy(['networks.mask'=>SORT_DESC])
			->one();
	}

	/**
	 * @inheritdoc
	 */
	public function beforeSave($insert)
	{
		if (parent::beforeSave($insert)) {
			// --- skipped some stuff not related to our subject ---
			
			//searching network which fits most for that IP
			if (is_object($network=$this->findNetwork())) 
				$this->networks_id=$network->id;
			else
				$this->networks_id=null;
		}
		return false;
	}
}


Class Networks extends \yii\db\ActiveRecord
{
	// --- skipped some stuff not related to our subject ---

	/**
	 * Related Ips (by Network Addr, Not by foreign key)
	 * @return NetIps[]
	 */
	public function findIps($addr=null,$mask=null)
	{
		if (is_null($addr)) $addr=$this->addr;
		if (is_null($mask)) $mask=$this->mask;
		return NetIps::find()->where(['AND',
			['>=','addr',$addr],
			['<','addr',$addr+(int)pow(2,(32-$mask))]
			])->all();
	}

	public function afterSave($insert, $changedAttributes)
	{
		parent::afterSave($insert, $changedAttributes);
		
		if ($insert || isset($changedAttributes['addr']) || isset($changedAttributes['mask'])) {
			// all IP objects, which were related with old ADDR/MASK values
			// plus new objects which become related with new ADDR/MASK values
			$totalIps=[];
			
			//previous (related by foreign key, which not yet recalculated in IP objects)
			foreach ($this->ips as $ip)
				$totalIps[$ip->id]=$ip;
			
			//new (by network addr)
			foreach ($this->findIps() as $ip)
				$totalIps[$ip->id]=$ip;
			
			//recalculating foreign keys on save
			foreach ($totalIps as $ip) $ip->save();
		}
	}
}

?>

And all looks working.
Fast enough because writes of network objects - is slow, but it is “manual only” operation in my App.
Writes of IP objects is automatic (by REST API), frequent, but it causes just one additional fast select request.
Until there will be support of custom relations in Yii i will levave that as it is.

Thank you.