eduroam database version 2 adaptation

[zmousm]

eduroam db v2 schema changes

[realm]

• Changed: realm.xml renamed to ro.xml (and ro.json)
• Added: ROid: string, provided by eduroam OT
• Added: stage: 0=preproduction/test, 1=active (default)
• Changed: address_street, address_city -> multilingual
• Added: coordinates: string: lon,lat[,alt] (optional)
• Added: contact_type: 0=person (default), 1=service/department
• Added: contact_privacy: 0=private (default), 1=public
• Removed: stype

[institution]

• Added: instid: string, provided by NRO
• Added: ROid: string, provided by eduroam OT
• Changed: type: enumeration: int -> string
• Added: stage: 0=preproduction/test, 1=active (default)
• Renamed: org_name -> inst_name
• Changed: address_street, address_city -> multilingual
• Added: coordinates: string: lon,lat[,alt] (optional)
• Added: inst_type: string: IEEE 802.11-2012, clause 8.4.1.34 Venue Info (note: this is a pair of numbers separated with comma (,))
• Added: contact_type: 0=person (default), 1=service/department
• Added: contact_privacy: 0=private (default), 1=public
• Removed: realmid

[service_loc]

• Added: instid: string, provided by NRO
• Added: ROid: string, provided by eduroam OT
• Added: locationid: string, provided by NRO
• Added: coordinates: string: lon,lat[,alt][;lon,lat[,alt];...]
• Added: stage: 0=preproduction/test, 1=active (default)
• Added: type: 0=single spot (default), 1=area, 2=mobile
• Changed: address_street, address_city -> multilingual
• Added: location_type: string: IEEE 802.11-2012, clause 8.4.1.34 Venue Info (note: this is a pair of numbers separated with comma (,))
• Changed: enc_level: optional
• Removed: port_restrict
• Removed: transp_proxy
• Removed: IPv6
• Removed: NAT
• Added: tag: string, comma-separated, allowed values: port_restrict, transp_proxy, IPv6, NAT, HS2.0
• Changed: AP_no: blank if unknown
• Added: wired_no: number of sockets, blank if unknown
• Added: availability: 0=no restrictions (default), 1=physical access restrictions
• Added: operation_hours: string: free text, opening hours in local time
• Removed: institutionid
• Removed: longitude
• Removed: latitude
• Removed: wired

eduroam db v2 schema caveats

• Intitution type: schema switched from integer to string; we can not extend the tuple (ERTYPES)
  we use for mapping because django does not allow tuples with len > 2 to be used for choices
• Address: we want to use lang on the whole object, yet both json/xml schema specify lang on each
  of the street, city fields, which does not make sense; we shall have to transform as necessary
• Should service_loc type be derived from number of coordinates? 0 mobile, 1 spot, >1 area
  However it would never yield 0 (mobile) because service_loc requires number of coordinates >= 1
  In other words: does it make sense for a service_loc type=mobile to have any coordinates?
• No explicit versioning scheme defined for eduroam database requests/endpoints (only implicitly for
  realm->ro and json endpoints); if multiple versions are supported (backwards compat), a setting must define which version should be rendered

coordinates database modeling

• coordinates uniformly represented on all 3 tables: serialized tuples of (lon, lat[, alt]) joined with ;
  • realm: 0 or 1 tuples
  • institution: 0 or 1 tuples
  • service_loc: >= 1 tuples
• how to model? options:
  • custom CharField (like MultiSelectField) (de-)serializing from/to a list of objects
    • simple but inefficient (storage, queries)
  • GeoDjango: PointField and LineStringField (corresponding to polygon in the eduroam db schema def)
    • most efficient but GeoDjango out of scope currently (overkill)
  • separate model (table) with relation:
    • one of:
      • ForeignKeyField and sorted ManyToManyField
        • m2m sorting can be:
          • implemented manually through a field in the intermediate table
          • provided by django-sortedm2m along with form widgets, at the expense of adding a dependency
        • sorting also needed for other m2m relations
      • GenericRelation (django.contrib.contenttypes.fields)
        • polygon point sorting must be implemented manually
        • no intermediate table per m2m relation
        • no other particular reason (e.g. polymorphism) to prefer (more complicated) contenttypes framework?
    • db-persisted as separate fields -> somewhat efficient for querying; less efficient for storage (due to sorting records for all tuples would be updated upon save)
    • (de-)serialization and validation through model instance (rather than field) method(s)
• polygon validation (non self-intersecting) non trivial without GeoDjango or some GIS library (Shapely)
• compat considerations:
  • service_loc shall be restricted initially to exactly one set of coordinates
  • service_loc latitude, longitude properties should be preserved as proxies to the actual fields

djnro models changes

• Add enums/choices tuples
  • PRODUCTION_STATES
  • CONTACT_TYPES
  • CONTACT_PRIVACY
  • LOCATION_TYPES
  • PHYSICAL_AVAILABILITY_STATES
  • LOCATION_TAGS
  • ERTYPES_DB ?

Contact

• Add type PositiveIntegerField (choices=CONTACT_TYPES, default=0)
• Add privacy PositiveIntegerField (choices=CONTACT_PRIVACY, default=0)

Address_i18n (new model)

• street
• city
• lang ...

Realm

• Add roid CharField
• Add stage PositiveIntegerField (choices=PRODUCTION_STATES, default=1)
• coordinates? (see discussion)
• Add address GenericRelation(Address_i18n) field
• Remove stype field

Institution

• Add instid UUIDField
• Add stage PositiveIntegerField (choices=PRODUCTION_STATES, default=1)
• Rename org_name -> inst_name

InstitutionDetails

• Add address GenericRelation(Address_i18n) field
• coordinates? (see discussion)
• Add venue_info Charfield (validators=[validate_comma_separated_integer_list,validate_venue_info])
• Remove number_user field
• Remove number_id field

ServiceLoc

• Add slocid UUIDField
• coordinates? (see discussion)
• Add stage PositiveIntegerField (choices=PRODUCTION_STATES, default=1)
• geo_type field? derived from number of coordinates? default=mobile?
• Add address GenericRelation(Address_i18n) field
• Add venue_info CharField (validators=[validate_comma_separated_integer_list,validate_venue_info])
• enc_level already accepts blank/null?
• tags: add MultiSelectField like enc_level
• AP_no: change blank=True, null=True ?
• Add wired_no PositiveIntegerField? blank=True, null=True?
• Add physical_avail PositiveIntegerField (choices=PHYSICAL_AVAILABILITY_STATES, default=0)
• Add operation_hours CharField

RealmData

• remove model

djnro data migrations

• address_street, address_city -> create address lang=en for each of
  • Realm
  • InstitutionDetails
  • ServiceLoc
• ServiceLoc: port_restrict, transp_proxy, IPv6, NAT if yes -> tags
• ServiceLoc: latitude, longitude -> coordinates

djnro models cleanup

Realm

• Remove address_street field
• Remove address_city field

InstitutionDetails

• Remove address_street field
• Remove address_city field

ServiceLoc

• Remove address_street field
• Remove address_city field
• Remove port_restrict field
• Remove transp_proxy field
• Remove IPv6 field
• Remove NAT field
• Remove latitude, longitude? Replace with property methods?

TBA: forms, views, templates

[ghalse]

From mobility day, reporting on the recent GeGC meeting:

From September 2019, the OT will start naming and shaming people who've not upgraded to version 2 of the database format. From Q1 2020, version 1 will no longer be supported.

[dpenezic]

Short notice :
a) mobile service_loc is for example bus, GPS coordinate cover bus line
b) institution type change way of need to be print in finale file, but i dont see reason to be change in program solution at all
c) collecting system know when you provide v1 or v2 in few ways , so no need to be cover via provided files

[zmousm]

a) mobile service_loc is for example bus, GPS coordinate cover bus line

Thanks, I never considered that.

c) collecting system know when you provide v1 or v2 in few ways , so no need to be cover via provided files

Except that the server has no way of knowing what version is requested.

[vladimir-mencl-eresearch]

Hi @zmousm , have you already started looking at implementing this?

Wondering whether to dive into it myself. Would you know whether this would also need changes to the underlying data model, or whether we have all the information required to construct the right format in a view?

Cheers,
Vlad

[zmousm]

Hi @vladimir-mencl-eresearch. Yes I have (had), more than once. My patch set is half-baked so it is not pushed yet. I currently need to rebase on top of the final PY3 changes.

Changes to the models are necessary and unavoidable, as I tried to elaborate previously.

It would really help if you could cast your preference, providing arguments from a Django perspective, how to deal with the open questions and caveats, namely modeling coordinates.

[zmousm]

I had second thoughts about modeling coordinates, triggered after realizing that ServiceLoc deletion does not cascade to Coordinates deletion (this is how the FKs in the M2M through table work). I finally used a signal to work around that original annoyance, however I noticed how the reverse relations between Coordinates and ServiceLoc vs. Realm and InstitutionDetails are asymmetric and wondered how this could be simplified, without ab-using signals. I realized that a generic FK together with a OneToOneField pointing to itself (a linked list of sorts) would be enough to represent either a chain (for ServiceLoc) or a single set (for the other models) of coordinates. Something like this:

class Coordinates(models.Model):
    previous = models.OneToOneField("self", null=True, blank=True,
                                    related_name="next")
    content_type = models.ForeignKey(ContentType, blank=True, null=True)
    object_id = models.PositiveIntegerField(blank=True, null=True)
    content_object = fields.GenericForeignKey('content_type', 'object_id')

    class Meta:
        unique_together = ((..., 'previous', 'content_type', 'object_id'),)

The first instance in a chain (or the single instance otherwise) would get previous=None. Meta.unique_together would enforce the same constraints as the respective signals. The single set restriction, both initially/temporarily for ServiceLoc as well as for the other models, for would be enforced through a signal requiring previous=None. However such a linked list would be notoriously ineffective in terms of SQL queries, as it would typically require as many round-trips as the members of a chain (even if this might not be a big concern initially) and it would be complicated to handle with Django ORM. The better way to handle this is with a modified pre-order traversal tree with django-mptt. However the latter seemed somewhat too involved (at this stage) for this simple use case, especially considering that eventually we should be using GeoDjango (with PostGIS) to effectively handle such data.