Add composite GenericForeignKey support

[Csirmaz Bendegúz]

This is a follow up to #373 (CompositePrimaryKey).

Proposal:

My proposal is to implement GenericForeignKey support with JSON.

1. object_id is a CharField (or TextField)
2. CompositePrimaryKey is stored as a JSON array in object_id e.g. object_id='[1, "abc"]'
3. JOINs can be achieved with JSON functions (varies per db backend)

Joins:

After some experimentation with JSON functions, I believe the simplest solution is to construct JOINs like this:

JOIN ON ((object_id::jsonb)->>0)::integer = id
    AND ((object_id::jsonb)->>1)::uuid = uuid

Casting is a pain point, especially when joining on DateTimeFields, as we need to make sure the two columns are in the same format.

Risks:

What if someone is using a JSON array as the primary key (but it's not a composite primary key)?
Before deserializing the JSON array, we need to check if the content type has a composite primary key or not.

What if the db backend doesn't support JSON functions?
All supported databases support JSON functions. MariaDB has some limitations handling surrogate pairs.

JOIN on JSON functions is not efficient
When storing a composite primary key in a CharField / TextField, JSON is the best option we have because it's widely supported by database backends. To achieve better performance, the composite pk shouldn't be stored in a CharField / TextField in the first place (see: alternatives).

Notes:

1. JOINs must work with Unicode characters
2. int, date, datetime, uuid, text fields must be supported
3. Django admin's LogEntry has its own implementation of "generic foreign keys". The approach we take with GenericForeignKey should also apply to LogEntry.

Alternatives:

It's possible to implement other strategies to deal with composite generic foreign keys.

1. JSONField - instead of CharField / TextField, we could make "object_id" a JSONField. This achieves the same thing but saves the ::jsonb cast, so it's slightly faster.
2. GenericForeignKey with multiple object_ids - less flexible, can't store both regular primary keys and composite primary keys. Fastest JOINs.

There's no reason we couldn't implement other strategies. I believe my proposal provides the most flexibility at the cost of performance. It should be implemented for backwards-compatibility, so systems using a CharField / TextField object_id (e.g. ​django-guardian) can easily integrate with composite primary keys. Other strategies can then be implemented to improve performance.

Any feedback is appreciated!

[Natalia Bidart]

Thank you Ben for creating this ticket. Accepting as described in ticket:373#comment:204.

[Csirmaz Bendegúz]

Thanks Natalia!

[Simon Charette]

I think the proposal looks great Ben, thanks for creating this ticket.

One thing that comes to mind as potentially problematic in JSON array comparison for JOIN is the treatment of Unicode characters as we're using json.dumps(ensure_ascii=False) as well as quotes and other characters that need to be escaped in JSON. It'd be great to add test coverage for these.

As for the risks I appreciate you raising them but I think that supporting composite generic foreign key composed of standard members (int, date, datetime, uuid, text) is already a good stretch and I would feel comfortable saying that anything that falls in the exotic primary key category should not necessarily be supported in the first place.

[Csirmaz Bendegúz]

Yes, thanks for raising these points Simon!