IBM / sqlalchemy-ibmi

Db2 for i SQL Alchemy adapter
Apache License 2.0
30 stars 12 forks source link

Support "collation" option on String/Text types #81

Open kadler opened 4 years ago

kadler commented 4 years ago

String and Text both support a "collation" option to set the encoding of the parameter:

collation

Optional, a column-level collation for use in DDL and CAST expressions. Renders using the COLLATE keyword supported by SQLite, MySQL, and PostgreSQL. E.g.:

from sqlalchemy import cast, select, String
>> print(select([cast('some string', String(collation='utf8'))]))
SELECT CAST(:param_1 AS VARCHAR COLLATE utf8) AS anon_1

We could allow users to specify a CCSID here, eg.

from sqlalchemy import cast, select, String
>>> print(select([cast('some string', String(10, collation=37))]))
SELECT CAST(:param_1 AS VARCHAR(10) CCSID 37) AS anon_1

I think we target this for a future version.

kadler commented 3 years ago

Various built-in SQLAlchemy dialects add custom attributes. MySQL has quite a few that we could model a "ccsid" attribute on. See the code for setting these attributes here: https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/types.py#L66-L93 then using the attributes: https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/base.py#L2265-L2297