Custom python class to represent "char" DB type

[mathemancer]

Problem

Currently, a columns of types NAME or "char" in the database show up as type VARCHAR in the API. This is because SQLAlchemy reflects these types as the String python class, which compiles to VARCHAR in the postgres dialect.

Proposed solution

We should:

• create a UserDefinedType representing "char", and another representing NAME.
• These types should be mapped to by the engine.dialect.ischema_names dictionary under the '"char"' and 'name' keys, respectively. (note that the string contains double-quotes)

Additional context

Based on a discussion: #1184

[Cronus1007]

@mathemancer I would like to work upon this issue.

[kgodey]

Go ahead @Cronus1007

[mathemancer]

@Cronus1007 Please don't hesitate to ask if you have questions. Please get one type done (rather than both) and submit a PR, or at least a draft, so we can see how it's going.

[Cronus1007]

@mathemancer Sure. Thanks 👍

[dmos62]

Hey @Cronus1007, this has become a dependency for a refactor I'm working on. I'd like to collaborate. Can we discuss this on matrix? My handle is @dominykas:matrix.mathesar.org.

[Cronus1007]

Hey @Cronus1007, this has become a dependency for a refactor I'm working on. I'd like to collaborate. Can we discuss this on matrix? My handle is @dominykas:matrix.mathesar.org.

@dmos62 Sure 👍

• These types should be mapped to by the engine.dialect.ischema_names dictionary under the '"char"' and 'name' keys, respectively. (note that the string contains double-quotes)

@mathemancer Can you please elaborate this statement?

[mathemancer]

• These types should be mapped to by the engine.dialect.ischema_names dictionary under the '"char"' and 'name' keys, respectively. (note that the string contains double-quotes)

@mathemancer Can you please elaborate this statement?

If you create an SQLAlchemy engine with the postgresql dialect (all engines in this project have that dialect), it has a dialect.ischema_names attribute that will be a dictionary. When reflecting a column, that dictionary defines the python class that will represent a given type. The types come from the DB as strings which are the keys of that dict. Notice that the 'name' and '"char"' keys both map to the sqlalchemy.sql.sqltypes.String python type. These need to be replaced with custom classes that compile to NAME and "CHAR" respectively, instead of VARCHAR, which is how the String class compiles. The custom classes can be injected to that dictionary wherever we use our engine by modifying the CUSTOM_TYPE_DICT in the db/types/__init__.py file.

[Cronus1007]

@mathemancer Any specific file where this code should be placed or shall I create a new file?

[mathemancer]

You'll need to create two new files in the db/types/ directory (see other files like email.py or uri.py in that directory for examples of how we're organizing things). Of course, there'll be some other changes to wire things up to the API. Look for how other custom types are wired up for help (or ask if you get stuck).

[dmos62]

@Cronus1007 as I mentioned in Matrix, this isn't a dependency of mine anymore, but here's something I came up with while I thought that it was. I didn't test this. You should be able to use the same setup for NAME and "CHAR".

from sqlalchemy.dialects.postgresql import VARCHAR as SA_VARCHAR
from sqlalchemy.types import TypeDecorator
from sqlalchemy.ext.compiler import compiles

from db.types.base import PostgresType


class CHARACTER_VARYING(TypeDecorator):
    impl = SA_VARCHAR
    cache_ok = True

    @classmethod
    def __str__(cls):
        return cls.__name__


@compiles(CHARACTER_VARYING, 'postgresql')
def _compile_character_varying(element, compiler, **kw):
    unchanged_compiled_string = compiler.visit_VARCHAR(element, **kw)
    unchanged_id = "VARCHAR"
    changed_id = PostgresType.CHARACTER_VARYING.value.upper()
    changed_compiled_string = unchanged_compiled_string.replace(unchanged_id, changed_id)
    return changed_compiled_string

[Cronus1007]

@dmos62 @mathemancer
The approach with which I have come up with is:-

• Declaration of User Defined Type of MATHESAR_CHAR similiar to that of MATHESAR_MONEY
• @mathemancer I am having a liitle bit of problems of how to compile the class So can you plzzz share some insights how to do it
• Including these user defined types in CUSTOM_TYPE_DICT
• Including these types in

class MathesarCustomType(Enum):
    """
    This is a list of custom Mathesar DB types.
    Keys returned by get_available_types are of the format 'mathesar_types.VALUE'
    """
    EMAIL = 'email'
    MATHESAR_MONEY = 'mathesar_money'
    MULTICURRENCY_MONEY = 'multicurrency_money'
    URI = 'uri'

@mathemancer @dmos62 Any suggestions about this approach?

[mathemancer]

A few things:

• Did you try just using a TypeDecorator? That will make sure the python class includes some extra functionality that's missing for UserDefinedTypes.
• For compilation, you only need this if you're doing the TypeDecorator. I think you should try to get that part working first, and then try to emulate the example compilation above from @dmos62 .
• You're correct that you need to punch the type into CUSTOM_TYPE_DICT.
• Instead of the MathesarCustomType enum, I think this should go in the PostgresType enum, since we're not creating any custom DB layer type for this. The MathesarCustomType enum is for types which have to be installed on the DB to work properly.

Edit: If you have more specific questions about the trouble you're having with compilation, I might be able to provide better help.

[Cronus1007]

A few things:

• Did you try just using a TypeDecorator? That will make sure the python class includes some extra functionality that's missing for UserDefinedTypes.

No I was just focusing more upon the UserDefinedType as the ticket mentioned.

• Instead of the MathesarCustomType enum, I think this should go in the PostgresType enum, since we're not creating any custom DB layer type for this. The MathesarCustomType enum is for types which have to be installed on the DB to work properly.

Sure I would go ahead In this way.

Edit: If you have more specific questions about the trouble you're having with compilation, I might be able to provide better help.

No since I am very new to the codebase and sqlachemy so I don't have much specific questions related to the compilation but if I would have any specific error while resolving the issue then would definitely refer you. @mathemancer Can you plzz share your matrix handle for discussions? I would be raising a draft pr by eod.

[mathemancer]

@mathemancer Can you plzz share your matrix handle for discussions? I would be raising a draft pr by eod.

@brent:matrix.mathesar.org

[Cronus1007]

@mathemancer @dmos62 Is there any way I would be able to test that whether the changes I have done are working correctly?

[mathemancer]

@Cronus1007 I'll answer on the PR, since that will be easier to track.

[Cronus1007]

@mathemancer @dmos62 I have completed work of '"char"' from my side in the PR linked. Once the PR gets merged I would love to start the work for name key as well.

[Cronus1007]

@mathemancer The PR#1260 doesn't close this issue. I need to build the python class for name as well so can you please reopen this issue so that I will be able to complete that as well.

[mathemancer]

Ah, that's correct.

[kgodey]

@Cronus1007 are you still working on this?

[dmos62]

Doesn't seem interesting anymore now that we're removing SQLAlchemy. Might be different if this didn't require a fair bit of input from core team.