Big Trouble In Little Arraytown

miq_group_filters.md

So I ran into a lovely issue while porting MiqGroup#filters (text blob, serialized hash) to Entitlement filter columns (Postgres Array columns, divided): Postgres multidimensional arrays must be of equal length.

The problem with this is that we currently store filters like this:

[["/managed/environment/dev"],
 ["/managed/location/chicago", "/managed/location/london"],
 ["/managed/prov_max_cpu/1"],
 ["/managed/service_level/gold"]]

I was originally going to leave the format as is because it affects too much to change it. But now I realize I can't do that (at least, not without just porting over a serialized text blob). The current format is pretty fragile, plus with multiple columns now the prefixes (i.e. /managed/) are pointless.

Maybe we should port this to a JSON column, an excellent tool for 'tagging'. Then...

{ "environment":   ["dev"],
  "location":      ["chicago", "london"],
  "service_level": ["gold"] }

Problems:

• These are just filters, not the actual tags themselves...so I'm guessing tags elsewhere will have to be changed. That could be a very, very large undertaking.
• My example is just for tag_filters ("managed" filters); I can't really deduce what the format is for 'belongsto' yet. I'm guessing it could work similarly.

Ultimately, we must do tagging in a better way now IMO, as the next step is for ✨ Multiple entitlements ✨ which need to be able to make sense of 'merging' filters. (Right?)

Thoughts? I'm unfamiliar with how tags are implemented all over, so I don't know if this sort of change is really possible or not, given time until feature freeze...

Tags are assigned via the Taggings table, so they are relatively simple. It might make sense to just leverage that directly and not worry about the Array column. My other thought on reading this is that I think this

[["/managed/environment/dev"],
 ["/managed/location/chicago", "/managed/location/london"],
 ["/managed/prov_max_cpu/1"],
 ["/managed/service_level/gold"]]

can just be stored like this

[["/managed/environment/dev"],
 ["/managed/location/chicago"],
 ["/managed/location/london"],
 ["/managed/prov_max_cpu/1"],
 ["/managed/service_level/gold"]]

or even without those intermediate arrays?

It might make sense to just leverage that directly and not worry about the Array column

That's a good point, though I hesitate and wonder what things make use of the format of the filters here (and how we'd have to go query off to that table, then). I also note (though this is my dev data, so might just be me...) that the taggings table seems to be only "managed" filters.

Your thought on the intermediate arrays makes sense to me, I don't understand why it was aggregated like that in the first place.

@lpichler was kind enough to show more data on the format of these things:

ap g = MiqGroup.where(:description=>"test_group").first.get_filters
  MiqGroup Load (0.5ms)  SELECT  "miq_groups".* FROM "miq_groups" WHERE "miq_groups"."description" = $1 ORDER BY "miq_groups"."id" ASC LIMIT $2  [["description", "test_group"], ["LIMIT", 1]]
  MiqGroup Inst Including Associations (0.1ms - 1rows)
{
      "managed" => [
        [0] [
            [0] "/managed/cc/001",
            [1] "/managed/cc/002"
        ],
        [1] [
            [0] "/managed/department/automotive",
            [1] "/managed/department/communication",
            [2] "/managed/department/defense"
        ]
    ],
    "belongsto" => [
        [0] "/belongsto/ExtManagementSystem|microsoft/EmsFolder|Datacenters/EmsFolder|SCVMM/EmsFolder|host/EmsCluster|hyperv_cluster",
        [1] "/belongsto/ExtManagementSystem|microsoft/EmsFolder|Datacenters/EmsFolder|SCVMM/EmsFolder|host/Host|dhcp129-212.brq.redhat.com",
        [2] "/belongsto/ExtManagementSystem|microsoft",
        [3] "/belongsto/ExtManagementSystem|microsoft/EmsFolder|Datacenters/EmsFolder|SCVMM",
        [4] "/belongsto/ExtManagementSystem|rhos7ga",
        [5] "/belongsto/ExtManagementSystem|vmware/EmsFolder|Datacenters/EmsFolder|CFME/EmsFolder|vm/EmsFolder|anewman/EmsFolder|Databases",
        [6] "/belongsto/ExtManagementSystem|vmware/EmsFolder|Datacenters/EmsFolder|CFME/EmsFolder|vm/EmsFolder|Admin_VMs",
        [7] "/belongsto/ExtManagementSystem|vmware/EmsFolder|Datacenters/EmsFolder|CFME/EmsFolder|vm/EmsFolder|anewman/EmsFolder|Templates"
    ]
}

Talking with @jrafanie yesterday, we can remove a whole lot of complexity and parsing by doing JSON as I mentioned; it will also make future merging considerations easier with multiple entitlements. I can also provide a utility class to allow filters to be converted back to 'the old format' as needed so that this can be gradually moved over.

@chrisarcand images re-uploaded
http://i.imgur.com/T4m1PLH.png
http://i.imgur.com/ldfNyqF.png
http://i.imgur.com/qciWmnd.png