Mapping DB data types to Mathesar data types

[kgodey]

We will have "friendly" Mathesar data types to make DB types easier to work with for non-technical users. We'll map DB types to these Mathesar types so that the frontend can show both the DB type and the Mathesar type as appropriate.

We'll expand these over time as we support advanced functionality for more types in Mathesar.

Here's the mapping:

Mathesar Type	Postgres Types
number	NUMERIC, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, MATHESAR_TYPES.PERCENTAGE (Mathesar custom type)
text	VARCHAR, CHAR, TEXT
datetime	TIMESTAMP, DATE, TIME
duration	INTERVAL
boolean	BOOLEAN
other	SMALLSERIAL, SERIAL, BIGSERIAL, BYTEA, POINT,LINE,LSEG,BOX,PATH,PATH,POLYGON, CIRCLE, CIDR, INET, MACADDR, MACADDR8, BIT, BIT VARYING, TSQUERY, TSVECTOR, JSON, JSONB, XML, PG_LSN, PG_SNAPSHOT, TXID_SNAPSHOT, INT4RANGE, INT8RANGE, NUMRANGE, TSRANGE, TSTZRANGE, DATERANGE
money	MATHESAR_TYPES.MONEY (Mathesar custom type), MONEY
email	MATHESAR_TYPES.EMAIL (Mathesar custom type)
uri	MATHESAR_TYPES.URI (Mathesar custom type)
custom	Any type that's detected but not on the list above.

Mathesar types should be owned by the Mathesar web service.

To implement this issue, we need to:

• Store friendly to DB type mapping in the backend #367
• Extend the v0/databases/'s types list to show both Mathesar and DB type information. #368
• Ensure that the API only accepts DB types as input everywhere we use types. #369

[mathemancer]

Note for whenever we do flesh this out: The current table reflection logic results in any type in the DB being reflected in the API, including (for example) custom user-created types, as well as any types they have installed through extensions. We should try to come up with some "nice" way to handle those along with more properly supported types.

[ghislaineguerin]

@kgodey this makes sense to me. When they add a database to mathesar, are we converting any of their types? Do we want users to know the specific number type their data had or just default to number.

[kgodey]

@ghislaineguerin "Mathesar Types" are purely to make things more user friendly on the frontend; their data is always stored in one of the Postgres types. There won't be any conversion.

We do want users to be able to see the specific number type the data is stored in, but it can be hidden by default unless people want to see it. This is a design decision we need to make.

[kgodey]

Closing this since all sub-issues are closed.