Handle BOOLEAN data type in the backend

[kgodey]

This issue is to ensure that Mathesar can handle the Postgres BOOLEAN data type.

As part of this issue, we need to ensure that:

• Users can use the API to change a column to BOOLEAN if it's possible to do so.
• Automatic type inference during file import suggests BOOLEAN when it makes sense to do so.

Most of this is already implemented, this issue is to review the current implementation, ensure it works, and see if we can make improvements to it.

Additional Context

• 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.

[mathemancer]

We should double-check that the strings we cast to boolean are what we want. We're currently restricting what gets cast from the PostgreSQL default to only 't', 'f', 'true', 'false' being castable to BOOLEAN.

[kgodey]

It seems like we should at least accept the Postgres default values of true, t, yes. y, on, 1 and false, f, no, n, off, 0.

It would also be nice to ensure that any column that we're recommending to be BOOLEAN has only two values (plus empty values). e.g. we want to infer that a column is BOOLEAN if every value in the CSV is either yes or no, but we do not want to recommend it if they're mostly yes or no, but there's a couple of maybes in there.

[mathemancer]

It seems like we should at least accept the Postgres default values of true, t, yes. y, on, 1 and false, f, no, n, off, 0.

It would also be nice to ensure that any column that we're recommending to be BOOLEAN has only two values (plus empty values). e.g. we want to infer that a column is BOOLEAN if every value in the CSV is either yes or no, but we do not want to recommend it if they're mostly yes or no, but there's a couple of maybes in there.

At the moment, it's not possible to accomplish this combination of goals with our current inference functions, or the paradigm under which they work together. Please recall that we decided to stay away from trying to infer column type based on global attributes for the moment (such as "the column has only two non-null values"). We could change that, but it's a major overhaul of the entire inference system we currently have set up, and would be a large(ish) time investment.

Alternatively, we could hack together a "one-off" solution just for Booleans, but I'd rather come up with a type inference setup that's global-property-aware in general.

[kgodey]

@mathemancer I remember now, I'll set up a separate issue for overhauling our type inference setup to be global-property-aware (probably post-MVP).

Edited to add: here's the new issue #438

[powellc]

@kgodey do you think #438 blocks this task? Or is the issue just not being able to check the whole incoming column for only boolean values? So this ticket would implement a "rudimentary" BOOLEAN data type that would only check per row?

[kgodey]

@powellc #438 doesn't block this task, it's a future improvement. We should go ahead and implement the rudimentary version of the type. I'm trying to keep the scope for the MVP as small as feasible so we ship on time.

[kgodey]

@powellc Checking in to see if you have any questions.

[powellc]

Nope. Got a lot of clarity on how the type system works from Brent. Will dig into this later today. Thanks @kgodey

[powellc]

Juist a quick update here. I finally got my head around the API design and basics of adding and updating things and have implemented the ability to alter a column type to Boolean. This utilizes some functions that already existsed so it was really just turning on the feature in the PATCH method for Tables.

The type_suggestions endpoint already provides a guess at the right column type if there are only boolean-like values in, so that AC should be good.

I'm going to add some tests for the column altering and open a PR today.

[kgodey]

Sounds good, @powellc!

[powellc]

Moving this back to To do before talking with @kgodey tomorrow about PATCH verb on a columns endpoint. That's the only piece missing, as we already correctly infer Boolean column types.

[powellc]

@kgodey may I close this with your work work on the columns endpoint done? The inference of Boolean types works as expected and with the updating of column types from your work we should be good here.

[kgodey]

@powellc I think it would be good to add some BOOLEAN specific tests for both inference and column alteration at the API layer. I think our tests are currently only at the db layer.

[powellc]

@kgodey sounds good! I'll work on that this evening.