location-importer.in: Attempt to provide meaningful AS names if organisation handles are missing

  A decent amount of autnum objects - especially, but not exclusively in
the APNIC sector - does not contain a link to an organisation handle.

In such cases, this patch is going to use the first description line of
the atunum object in question (if available) as a string for its name.
The overwhelming majority of affected ASNs contains a valuable
information there, so this is almost as good as having an organisation
handle linked to it.

Fixes: #12660

Signed-off-by: Peter Müller <peter.mueller@ipfire.org>
---
 src/python/location-importer.in | 28 +++++++++++++++++++++++++---
 1 file changed, 25 insertions(+), 3 deletions(-)
  

Thank you. Merged.

Do we plan a release because of this soon?

> On 19 Jul 2021, at 22:34, Peter Müller <peter.mueller@ipfire.org> wrote:
> 
> A decent amount of autnum objects - especially, but not exclusively in
> the APNIC sector - does not contain a link to an organisation handle.
> 
> In such cases, this patch is going to use the first description line of
> the atunum object in question (if available) as a string for its name.
> The overwhelming majority of affected ASNs contains a valuable
> information there, so this is almost as good as having an organisation
> handle linked to it.
> 
> Fixes: #12660
> 
> Signed-off-by: Peter Müller <peter.mueller@ipfire.org>
> ---
> src/python/location-importer.in | 28 +++++++++++++++++++++++++---
> 1 file changed, 25 insertions(+), 3 deletions(-)
> 
> diff --git a/src/python/location-importer.in b/src/python/location-importer.in
> index d4b0ca2..bec0b62 100644
> --- a/src/python/location-importer.in
> +++ b/src/python/location-importer.in
> @@ -393,11 +393,11 @@ class CLI(object):
> 		with self.db.transaction():
> 			# Create some temporary tables to store parsed data
> 			self.db.execute("""
> -				CREATE TEMPORARY TABLE _autnums(number integer, organization text, source text NOT NULL)
> +				CREATE TEMPORARY TABLE _autnums(number integer NOT NULL, organization text NOT NULL, source text NOT NULL)
> 					ON COMMIT DROP;
> 				CREATE UNIQUE INDEX _autnums_number ON _autnums(number);
> 
> -				CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL, source text NOT NULL)
> +				CREATE TEMPORARY TABLE _organizations(handle text NOT NULL, name text NOT NULL, source text NOT NULL)
> 					ON COMMIT DROP;
> 				CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle);
> 
> @@ -604,10 +604,32 @@ class CLI(object):
> 			elif key == "org":
> 				autnum[key] = val.upper()
> 
> +			elif key == "descr":
> +				# Save the first description line as well...
> +				if not key in autnum:
> +					autnum[key] = val
> +
> 		# Skip empty objects
> -		if not autnum:
> +		if not autnum or not "asn" in autnum:
> 			return
> 
> +		# Insert a dummy organisation handle into our temporary organisations
> +		# table in case the AS does not have an organisation handle set, but
> +		# has a description (a quirk often observed in APNIC area), so we can
> +		# later display at least some string for this AS.
> +		if not "org" in autnum:
> +			if "descr" in autnum:
> +				autnum["org"] = "LIBLOC-%s-ORGHANDLE" % autnum.get("asn")
> +
> +				self.db.execute("INSERT INTO _organizations(handle, name, source) \
> +					VALUES(%s, %s, %s) ON CONFLICT (handle) DO NOTHING",
> +					autnum.get("org"), autnum.get("descr"), source_key,
> +				)
> +			else:
> +				log.warning("ASN %s neither has an organisation handle nor a description line set, omitting" % \
> +						autnum.get("asn"))
> +				return
> +
> 		# Insert into database
> 		self.db.execute("INSERT INTO _autnums(number, organization, source) \
> 			VALUES(%s, %s, %s) ON CONFLICT (number) DO UPDATE SET \
> -- 
> 2.20.1
>
  

Hello Michael,

thanks for your reply.

I would leave this up to you - some data-related issues are still on my list, but I
am unfortunately really short on spare time at the moment. So I guess we should get
in production what we have at hand... :-)

By the way: Thank you for fixing location01, SSH login works fine for me again. :-)

Thanks, and best regards,
Peter Müller


> Thank you. Merged.
> 
> Do we plan a release because of this soon?
> 
>> On 19 Jul 2021, at 22:34, Peter Müller <peter.mueller@ipfire.org> wrote:
>>
>> A decent amount of autnum objects - especially, but not exclusively in
>> the APNIC sector - does not contain a link to an organisation handle.
>>
>> In such cases, this patch is going to use the first description line of
>> the atunum object in question (if available) as a string for its name.
>> The overwhelming majority of affected ASNs contains a valuable
>> information there, so this is almost as good as having an organisation
>> handle linked to it.
>>
>> Fixes: #12660
>>
>> Signed-off-by: Peter Müller <peter.mueller@ipfire.org>
>> ---
>> src/python/location-importer.in | 28 +++++++++++++++++++++++++---
>> 1 file changed, 25 insertions(+), 3 deletions(-)
>>
>> diff --git a/src/python/location-importer.in b/src/python/location-importer.in
>> index d4b0ca2..bec0b62 100644
>> --- a/src/python/location-importer.in
>> +++ b/src/python/location-importer.in
>> @@ -393,11 +393,11 @@ class CLI(object):
>> 		with self.db.transaction():
>> 			# Create some temporary tables to store parsed data
>> 			self.db.execute("""
>> -				CREATE TEMPORARY TABLE _autnums(number integer, organization text, source text NOT NULL)
>> +				CREATE TEMPORARY TABLE _autnums(number integer NOT NULL, organization text NOT NULL, source text NOT NULL)
>> 					ON COMMIT DROP;
>> 				CREATE UNIQUE INDEX _autnums_number ON _autnums(number);
>>
>> -				CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL, source text NOT NULL)
>> +				CREATE TEMPORARY TABLE _organizations(handle text NOT NULL, name text NOT NULL, source text NOT NULL)
>> 					ON COMMIT DROP;
>> 				CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle);
>>
>> @@ -604,10 +604,32 @@ class CLI(object):
>> 			elif key == "org":
>> 				autnum[key] = val.upper()
>>
>> +			elif key == "descr":
>> +				# Save the first description line as well...
>> +				if not key in autnum:
>> +					autnum[key] = val
>> +
>> 		# Skip empty objects
>> -		if not autnum:
>> +		if not autnum or not "asn" in autnum:
>> 			return
>>
>> +		# Insert a dummy organisation handle into our temporary organisations
>> +		# table in case the AS does not have an organisation handle set, but
>> +		# has a description (a quirk often observed in APNIC area), so we can
>> +		# later display at least some string for this AS.
>> +		if not "org" in autnum:
>> +			if "descr" in autnum:
>> +				autnum["org"] = "LIBLOC-%s-ORGHANDLE" % autnum.get("asn")
>> +
>> +				self.db.execute("INSERT INTO _organizations(handle, name, source) \
>> +					VALUES(%s, %s, %s) ON CONFLICT (handle) DO NOTHING",
>> +					autnum.get("org"), autnum.get("descr"), source_key,
>> +				)
>> +			else:
>> +				log.warning("ASN %s neither has an organisation handle nor a description line set, omitting" % \
>> +						autnum.get("asn"))
>> +				return
>> +
>> 		# Insert into database
>> 		self.db.execute("INSERT INTO _autnums(number, organization, source) \
>> 			VALUES(%s, %s, %s) ON CONFLICT (number) DO UPDATE SET \
>> -- 
>> 2.20.1
>>
>
  

Hey,

> On 21 Jul 2021, at 22:23, Peter Müller <peter.mueller@ipfire.org> wrote:
> 
> Hello Michael,
> 
> thanks for your reply.
> 
> I would leave this up to you - some data-related issues are still on my list, but I
> am unfortunately really short on spare time at the moment. So I guess we should get
> in production what we have at hand... :-)

Since we do not make too much use of this data, yet, I would like to leave another release for a little bit and wait until the rest is coming in.

I generally like the release early and often thing. We should keep doing that.

> By the way: Thank you for fixing location01, SSH login works fine for me again. :-)

-Michael

> 
> Thanks, and best regards,
> Peter Müller
> 
> 
>> Thank you. Merged.
>> 
>> Do we plan a release because of this soon?
>> 
>>> On 19 Jul 2021, at 22:34, Peter Müller <peter.mueller@ipfire.org> wrote:
>>> 
>>> A decent amount of autnum objects - especially, but not exclusively in
>>> the APNIC sector - does not contain a link to an organisation handle.
>>> 
>>> In such cases, this patch is going to use the first description line of
>>> the atunum object in question (if available) as a string for its name.
>>> The overwhelming majority of affected ASNs contains a valuable
>>> information there, so this is almost as good as having an organisation
>>> handle linked to it.
>>> 
>>> Fixes: #12660
>>> 
>>> Signed-off-by: Peter Müller <peter.mueller@ipfire.org>
>>> ---
>>> src/python/location-importer.in | 28 +++++++++++++++++++++++++---
>>> 1 file changed, 25 insertions(+), 3 deletions(-)
>>> 
>>> diff --git a/src/python/location-importer.in b/src/python/location-importer.in
>>> index d4b0ca2..bec0b62 100644
>>> --- a/src/python/location-importer.in
>>> +++ b/src/python/location-importer.in
>>> @@ -393,11 +393,11 @@ class CLI(object):
>>> 		with self.db.transaction():
>>> 			# Create some temporary tables to store parsed data
>>> 			self.db.execute("""
>>> -				CREATE TEMPORARY TABLE _autnums(number integer, organization text, source text NOT NULL)
>>> +				CREATE TEMPORARY TABLE _autnums(number integer NOT NULL, organization text NOT NULL, source text NOT NULL)
>>> 					ON COMMIT DROP;
>>> 				CREATE UNIQUE INDEX _autnums_number ON _autnums(number);
>>> 
>>> -				CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL, source text NOT NULL)
>>> +				CREATE TEMPORARY TABLE _organizations(handle text NOT NULL, name text NOT NULL, source text NOT NULL)
>>> 					ON COMMIT DROP;
>>> 				CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle);
>>> 
>>> @@ -604,10 +604,32 @@ class CLI(object):
>>> 			elif key == "org":
>>> 				autnum[key] = val.upper()
>>> 
>>> +			elif key == "descr":
>>> +				# Save the first description line as well...
>>> +				if not key in autnum:
>>> +					autnum[key] = val
>>> +
>>> 		# Skip empty objects
>>> -		if not autnum:
>>> +		if not autnum or not "asn" in autnum:
>>> 			return
>>> 
>>> +		# Insert a dummy organisation handle into our temporary organisations
>>> +		# table in case the AS does not have an organisation handle set, but
>>> +		# has a description (a quirk often observed in APNIC area), so we can
>>> +		# later display at least some string for this AS.
>>> +		if not "org" in autnum:
>>> +			if "descr" in autnum:
>>> +				autnum["org"] = "LIBLOC-%s-ORGHANDLE" % autnum.get("asn")
>>> +
>>> +				self.db.execute("INSERT INTO _organizations(handle, name, source) \
>>> +					VALUES(%s, %s, %s) ON CONFLICT (handle) DO NOTHING",
>>> +					autnum.get("org"), autnum.get("descr"), source_key,
>>> +				)
>>> +			else:
>>> +				log.warning("ASN %s neither has an organisation handle nor a description line set, omitting" % \
>>> +						autnum.get("asn"))
>>> +				return
>>> +
>>> 		# Insert into database
>>> 		self.db.execute("INSERT INTO _autnums(number, organization, source) \
>>> 			VALUES(%s, %s, %s) ON CONFLICT (number) DO UPDATE SET \
>>> -- 
>>> 2.20.1
>>> 
>>
  

Hi Michael,

> Hey,
> 
>> On 21 Jul 2021, at 22:23, Peter Müller <peter.mueller@ipfire.org> wrote:
>>
>> Hello Michael,
>>
>> thanks for your reply.
>>
>> I would leave this up to you - some data-related issues are still on my list, but I
>> am unfortunately really short on spare time at the moment. So I guess we should get
>> in production what we have at hand... :-)
> 
> Since we do not make too much use of this data, yet, I would like to leave another release for a little bit and wait until the rest is coming in.

thought about this again, I think we could apply this patch to libloc running on location01,
as we probably won't release a next version of libloc within the next few weeks - primarily
due to me lacking spare time. :-/

Albeit it is hacky, I think it would be worth it. What do you think?

> I generally like the release early and often thing. We should keep doing that.

ACK.

Thanks, and best regards,
Peter Müller

> 
>> By the way: Thank you for fixing location01, SSH login works fine for me again. :-)
> 
> -Michael
> 
>>
>> Thanks, and best regards,
>> Peter Müller
>>
>>
>>> Thank you. Merged.
>>>
>>> Do we plan a release because of this soon?
>>>
>>>> On 19 Jul 2021, at 22:34, Peter Müller <peter.mueller@ipfire.org> wrote:
>>>>
>>>> A decent amount of autnum objects - especially, but not exclusively in
>>>> the APNIC sector - does not contain a link to an organisation handle.
>>>>
>>>> In such cases, this patch is going to use the first description line of
>>>> the atunum object in question (if available) as a string for its name.
>>>> The overwhelming majority of affected ASNs contains a valuable
>>>> information there, so this is almost as good as having an organisation
>>>> handle linked to it.
>>>>
>>>> Fixes: #12660
>>>>
>>>> Signed-off-by: Peter Müller <peter.mueller@ipfire.org>
>>>> ---
>>>> src/python/location-importer.in | 28 +++++++++++++++++++++++++---
>>>> 1 file changed, 25 insertions(+), 3 deletions(-)
>>>>
>>>> diff --git a/src/python/location-importer.in b/src/python/location-importer.in
>>>> index d4b0ca2..bec0b62 100644
>>>> --- a/src/python/location-importer.in
>>>> +++ b/src/python/location-importer.in
>>>> @@ -393,11 +393,11 @@ class CLI(object):
>>>> 		with self.db.transaction():
>>>> 			# Create some temporary tables to store parsed data
>>>> 			self.db.execute("""
>>>> -				CREATE TEMPORARY TABLE _autnums(number integer, organization text, source text NOT NULL)
>>>> +				CREATE TEMPORARY TABLE _autnums(number integer NOT NULL, organization text NOT NULL, source text NOT NULL)
>>>> 					ON COMMIT DROP;
>>>> 				CREATE UNIQUE INDEX _autnums_number ON _autnums(number);
>>>>
>>>> -				CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL, source text NOT NULL)
>>>> +				CREATE TEMPORARY TABLE _organizations(handle text NOT NULL, name text NOT NULL, source text NOT NULL)
>>>> 					ON COMMIT DROP;
>>>> 				CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle);
>>>>
>>>> @@ -604,10 +604,32 @@ class CLI(object):
>>>> 			elif key == "org":
>>>> 				autnum[key] = val.upper()
>>>>
>>>> +			elif key == "descr":
>>>> +				# Save the first description line as well...
>>>> +				if not key in autnum:
>>>> +					autnum[key] = val
>>>> +
>>>> 		# Skip empty objects
>>>> -		if not autnum:
>>>> +		if not autnum or not "asn" in autnum:
>>>> 			return
>>>>
>>>> +		# Insert a dummy organisation handle into our temporary organisations
>>>> +		# table in case the AS does not have an organisation handle set, but
>>>> +		# has a description (a quirk often observed in APNIC area), so we can
>>>> +		# later display at least some string for this AS.
>>>> +		if not "org" in autnum:
>>>> +			if "descr" in autnum:
>>>> +				autnum["org"] = "LIBLOC-%s-ORGHANDLE" % autnum.get("asn")
>>>> +
>>>> +				self.db.execute("INSERT INTO _organizations(handle, name, source) \
>>>> +					VALUES(%s, %s, %s) ON CONFLICT (handle) DO NOTHING",
>>>> +					autnum.get("org"), autnum.get("descr"), source_key,
>>>> +				)
>>>> +			else:
>>>> +				log.warning("ASN %s neither has an organisation handle nor a description line set, omitting" % \
>>>> +						autnum.get("asn"))
>>>> +				return
>>>> +
>>>> 		# Insert into database
>>>> 		self.db.execute("INSERT INTO _autnums(number, organization, source) \
>>>> 			VALUES(%s, %s, %s) ON CONFLICT (number) DO UPDATE SET \
>>>> -- 
>>>> 2.20.1
>>>>
>>>
>
  

Hello,

I would rather have it property tagged so that we can use our deployment process and can potentially roll back.

-Michael

> On 5 Aug 2021, at 20:49, Peter Müller <peter.mueller@ipfire.org> wrote:
> 
> Hi Michael,
> 
>> Hey,
>> 
>>> On 21 Jul 2021, at 22:23, Peter Müller <peter.mueller@ipfire.org> wrote:
>>> 
>>> Hello Michael,
>>> 
>>> thanks for your reply.
>>> 
>>> I would leave this up to you - some data-related issues are still on my list, but I
>>> am unfortunately really short on spare time at the moment. So I guess we should get
>>> in production what we have at hand... :-)
>> 
>> Since we do not make too much use of this data, yet, I would like to leave another release for a little bit and wait until the rest is coming in.
> 
> thought about this again, I think we could apply this patch to libloc running on location01,
> as we probably won't release a next version of libloc within the next few weeks - primarily
> due to me lacking spare time. :-/
> 
> Albeit it is hacky, I think it would be worth it. What do you think?
> 
>> I generally like the release early and often thing. We should keep doing that.
> 
> ACK.
> 
> Thanks, and best regards,
> Peter Müller
> 
>> 
>>> By the way: Thank you for fixing location01, SSH login works fine for me again. :-)
>> 
>> -Michael
>> 
>>> 
>>> Thanks, and best regards,
>>> Peter Müller
>>> 
>>> 
>>>> Thank you. Merged.
>>>> 
>>>> Do we plan a release because of this soon?
>>>> 
>>>>> On 19 Jul 2021, at 22:34, Peter Müller <peter.mueller@ipfire.org> wrote:
>>>>> 
>>>>> A decent amount of autnum objects - especially, but not exclusively in
>>>>> the APNIC sector - does not contain a link to an organisation handle.
>>>>> 
>>>>> In such cases, this patch is going to use the first description line of
>>>>> the atunum object in question (if available) as a string for its name.
>>>>> The overwhelming majority of affected ASNs contains a valuable
>>>>> information there, so this is almost as good as having an organisation
>>>>> handle linked to it.
>>>>> 
>>>>> Fixes: #12660
>>>>> 
>>>>> Signed-off-by: Peter Müller <peter.mueller@ipfire.org>
>>>>> ---
>>>>> src/python/location-importer.in | 28 +++++++++++++++++++++++++---
>>>>> 1 file changed, 25 insertions(+), 3 deletions(-)
>>>>> 
>>>>> diff --git a/src/python/location-importer.in b/src/python/location-importer.in
>>>>> index d4b0ca2..bec0b62 100644
>>>>> --- a/src/python/location-importer.in
>>>>> +++ b/src/python/location-importer.in
>>>>> @@ -393,11 +393,11 @@ class CLI(object):
>>>>> 		with self.db.transaction():
>>>>> 			# Create some temporary tables to store parsed data
>>>>> 			self.db.execute("""
>>>>> -				CREATE TEMPORARY TABLE _autnums(number integer, organization text, source text NOT NULL)
>>>>> +				CREATE TEMPORARY TABLE _autnums(number integer NOT NULL, organization text NOT NULL, source text NOT NULL)
>>>>> 					ON COMMIT DROP;
>>>>> 				CREATE UNIQUE INDEX _autnums_number ON _autnums(number);
>>>>> 
>>>>> -				CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL, source text NOT NULL)
>>>>> +				CREATE TEMPORARY TABLE _organizations(handle text NOT NULL, name text NOT NULL, source text NOT NULL)
>>>>> 					ON COMMIT DROP;
>>>>> 				CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle);
>>>>> 
>>>>> @@ -604,10 +604,32 @@ class CLI(object):
>>>>> 			elif key == "org":
>>>>> 				autnum[key] = val.upper()
>>>>> 
>>>>> +			elif key == "descr":
>>>>> +				# Save the first description line as well...
>>>>> +				if not key in autnum:
>>>>> +					autnum[key] = val
>>>>> +
>>>>> 		# Skip empty objects
>>>>> -		if not autnum:
>>>>> +		if not autnum or not "asn" in autnum:
>>>>> 			return
>>>>> 
>>>>> +		# Insert a dummy organisation handle into our temporary organisations
>>>>> +		# table in case the AS does not have an organisation handle set, but
>>>>> +		# has a description (a quirk often observed in APNIC area), so we can
>>>>> +		# later display at least some string for this AS.
>>>>> +		if not "org" in autnum:
>>>>> +			if "descr" in autnum:
>>>>> +				autnum["org"] = "LIBLOC-%s-ORGHANDLE" % autnum.get("asn")
>>>>> +
>>>>> +				self.db.execute("INSERT INTO _organizations(handle, name, source) \
>>>>> +					VALUES(%s, %s, %s) ON CONFLICT (handle) DO NOTHING",
>>>>> +					autnum.get("org"), autnum.get("descr"), source_key,
>>>>> +				)
>>>>> +			else:
>>>>> +				log.warning("ASN %s neither has an organisation handle nor a description line set, omitting" % \
>>>>> +						autnum.get("asn"))
>>>>> +				return
>>>>> +
>>>>> 		# Insert into database
>>>>> 		self.db.execute("INSERT INTO _autnums(number, organization, source) \
>>>>> 			VALUES(%s, %s, %s) ON CONFLICT (number) DO UPDATE SET \
>>>>> -- 
>>>>> 2.20.1