[openstack-dev] IPAddress SQLAlchemy type and other db-specific datatypes

Monty Taylor mordred at inaugust.com
Mon Nov 12 16:22:47 UTC 2012



On 11/12/2012 08:09 AM, Jay Pipes wrote:
> On 11/12/2012 07:55 AM, Ionuț Arțăriși wrote:
>> Hi,
>>
>> I submitted a patch for review last week[1] which started a discussion
>> about implementing sqlalchemy dialect specific datatypes
>> (e.g. postgresql, mysql etc.).
>>
>> I think it would be great to abstract some common datatypes (such as for
>> ip addresses) so we can then add custom validations or use different
>> backend datatypes where that makes sense.
>
> I also think this is a good idea. It's painful to see CHAR(32) used for
> things like UUIDs stored as hex string values and VARCHAR(255) used to
> store IPv4 addresses, which are simply unsigned 4-byte integers.
>
>> The way the IPAddress is implemented should be pretty straightforward and
>> very backwards compatible (the INET datatype acts as a string for basic
>> operations). Just defining a new datatype:
>>
>> IPAddress = lambda: String(255).with_variant(postgresql.INET(),
>> 'postgresql')
>
> Might be good to name it IPv4Address instead of IPAddress, even though
> PostgreSQL's INET type supports both.
>
> With MySQL, you can use the INET_ATON() and INET_NTOA() functions (in
> SQLAlchemy' func.inet_aton as well...) to do the conversion to and from
> the underlying unsigned integer and extend an existing SQLAlchemy data
> type as shown here:
>
> http://docs.sqlalchemy.org/en/rel_0_7/core/types.html#augmenting-existing-types
>
>> which can then be used instead of the string like so:
>>
>> access_ip_v4 = Column(IPAddress())
>>
>> I guess now there's the question of where we want to store these
>> custom dataypes' implementations. Would it be enough to have a datatypes.py
>> module in there and have all our custom types inside?
>
> That would be fine with me -- especially if it was in Oslo eventually :)

Don't forget - you _might_ be able to land something like that upstream 
in sqlalchemy too.

Other than that- I agree.

Monty



More information about the OpenStack-dev mailing list