Add full support for Set and Enum Types

[leo-viana]

Goal

Add support for generating sql alchemy type of enum and set(for mysql databases) with their permitted values and validations

Description

Currently, Enum and Set types are not being generated in their ideal way(Set is currently being mapped to Enum and Enum does not validate its permitted values when inserting and updating values on the table).

The Set data type has a class defined by sqlalchemy on this path: To give support to the set data type, the following changes will have to be made:

• An import statement(from sqlalchemy.dialects.mysql.enumerated import SET) would have to be added dynamically on the api generation on the SQLAlchemyBuilder.py file, when the set type is identified to exist in any of the connected database tables.
• On the default api generation behaviour, the imports of the sqlalchemy classes are done as something like: sa.BIGINT, sa.Enum and the such, that will also be true for the SET import, it will come as sa.SET, so a logic would have to be applied, by identifying that, on the generated domain files, if there is a sa.SET on any of them, it would be replaced by SET only, image below may help to understand the logic of the current generation of an example Domain file and the desired new generation of the said file: To do the logics above, new functions can be created on DomainMigrationHandler.py
• On ApiGeneratorWorker.py, right after modify_swagger_related_files call, a call to the function with the logics above will have to added.
• On SQLAlchemyTypesUtils.py, handle_sql_to_sa_types_conversion function will have to be changed to remove the logic that maps the SET data type to the Enum(remove the TODO comment of it as well)
• On SQLAlchemyTypesUtils.py, convert_set_or_enum_to_Enum function will have to be changed to remove the logic that validates if the input_string is of the SET data type, as it will not be necessary.

To validate the values of Enum(and SET when the above is working), the following will have to be considered when changing files:

• The Enum data type is already supported in the current state of the api generation, but when you try to add entries to the table with values that are not part of the Enum rule, the query persists successfully, which is not ideal since on a normal sql query done by the /sql route, inserting unsupported values for Enums and Sets returns a "Data truncated for column 'enum_col', so throwing a generic error like this would be ideal. To accomplish the above, some kind of validation will need to be added when doing insert and update for the tables that have enum and set, this would maybe have to be a validation on the init of the domain class, a setter for the columns of these types or using sqlalchemy's event listener. This will have to be done carefully and completely tested, doing posts, patchs and gets for the enum and set columns multiple times to check the logic works for all cases.

  Tests

  To test it out, the steps would be:

• Spin up a docker container with any kind of test database, this article should help on that: https://medium.com/@seventechnologiescloud/create-instant-apis-with-mysql-on-local-containers-ac9172b1020c
• To generate an api using the modified code with the above changes, you can run the starter python file of the pythonrest with some script parameters on a terminal, using an example command like below that uses the database info of the database created on the article mentioned above: python pythonrest.py generate --mysql-connection-string mysql://admin:simple_yet_efficient_password@localhost:3306/simple_db
• After that, the readme of the generated API contains instructions to running the it using venv.
• When the API is running, you can test any of the domain routes on the GET, POST, PATCH, PUT, DELETE methods acessing the swagger of the domain via the localhost:5000/swagger/ url and test adding, updating, retrieving and deleting any number of objects to the tables with the Enum and SET types to assure that the code works correctly.

  Classes to be changed

  SQLAlchemyTypesUtils.py DomainMigrationHandler.py ApiGeneratorWorker.py SQLAlchemyBuilder.py -- this one will be changed dynamically by the api generation when it detects that the new SET import is necessary, so it must only be changed on the generated api folder file Generated Domain Classes -- These ones will be changed dynamically by the api generation when it detects that the table has set or enum, adding the validation of their values

[leo-viana]

Commit fe6be55 fixed this issue