Create URI custom type and handle it in the backend.

[kgodey]

This issue is to set up a data type to work with URIs. We might choose to use an existing extension like https://github.com/petere/pguri or write our own MATHESAR_TYPES.URI type. Part of this issue involves evaluating both options.

As part of this issue, we need to do the following:

• Decide which approach to take – use pguri or write our own. @kgodey and @mathemancer should sign off before implementation.
• If we're using our own custom type, implement the MATHESAR_TYPES.URI custom type.
  • The type should be case-insensitive at the DB layer and uppercase in the API
• Ensure that users can use the API to change a column to the URI type if it's possible to do so.
• Set up automatic type inference during file import to suggest the URI type when it makes sense to do so.

Additional Context

• Blocked until Backend work for Mathesar email custom type. #409 is complete, to serve as a model for DB case insensitivity
• See PR Extend the v0/databases/'s types list to show both Mathesar and DB type information. #368 for code that calculates which types are allowed for a column.
• Mapping DB data types to Mathesar data types #269

[mathemancer]

If we can spare the time for implementation, I suggest we implement our own. This would avoid the need for installing a 3rd-party extension which isn't supported everywhere (e.g., on Amazon's RDS instances).

[kgodey]

pguri is not on AWS's supported extension list.

We might still want to use it to speed up implementation and consider writing our own at a later date. It depends on how much work it is to write our own. We could also try using/forking pguri as a starting point if we're writing our own.

[mathemancer]

Looking at the filtering / grouping requirements, we should be able to get away with a DOMAIN combined with a couple of splitting functions, very similar to our current implementation of the EMAIL type. I don't think it would be too difficult.

The pguri extension defines the type in C, so I don't think it will be too useful to start from if we want to implement our own. (In fact, even the regexes, etc. for identifying URIs are included in that C file, so they're not visible there.)

[kgodey]

@mathemancer makes sense. I do want to make it easy to extend to other URI parts in the future e.g. pguri supports port, query parameters, fragments, etc. (see docs).

[mathemancer]

@mathemancer makes sense. I do want to make it easy to extend to other URI parts in the future (e.g. pguri supports port, query parameters, fragments, etc. (see docs).

I do think it makes sense to try to use that extension (and other URI-mangling libraries) as inspiration at least. I just wish there was a good way to install extensions without access to the underlying server.