![]() Do the funny string transformations in the app layer.Īs for sending a base64 string into the db and then comparing it against stored uuid types, that is a conversion question already answered and explained here select substring(decode('pEUh0A+4St6AAjOFVFwzGA=','base64')::text from 3)::uuid Note that this does not change the uuid itself and you still should not be storing your uuid as text (or bytea, or anything "clever") for that matter. If you really, really want your database to do the legwork of base64 encoding your uuid, you can use the functions encode and uuid_send (undocumented) as seen here select encode(uuid_send('a44521d0-0fb8-4ade-8002-3385545c3318'::uuid),'base64') PostgreSQL also accepts the following alternative forms for input: use of upper-case digits, the standard format surrounded by braces, omitting some or all hyphens, adding a hyphen after any group of four digits. On the docs page, you will find the sampling of default permissible input string formats for ad-hoc work. Use the uuid datatype for database work with uuids handle special text formatting as near to the client as you can. would it make sense to save these short IDs directly as text in the database? Similarly the hexadecimal string you read in your client is also not the uuid - it is that same text representation of the uuid. It is just the text representation of the uuid. Return dialect.The text you pass to the database from your client (psql or pgAdmin for example) is not the uuid. Return dialect.type_descriptor(psqlUUID()) Uses Postgresql's UUID type, otherwise uses import uuidįrom sqlalchemy.types import TypeDecorator, BINARYįrom import UUID as psqlUUID Here is an approach based on the Backend agnostic GUID from the SQLAlchemy docs, but using a BINARY field to store the UUIDs in non-postgresql databases. The solution I came up with I was targeting MSSqlServer originally and then went MySql in the end, so I think my solution is a little more flexible as it seems to work fine on mysql and sqlite. If it doesn't, you would likely get errors when the table is created. ![]() ![]() Unless I'm missing something the above solution will work if the underlying database has a UUID type. I welcome any suggestions for improving it. Leave a comment if you see a glaring error with it. Other than that everything seems to work fine, and so I'm throwing it out there. One disadvantage I've found is that at least in phpymyadmin, you can't edit records because it implicitly tries to do some sort of character conversion for the "select * from table where id =." and there's miscellaneous display issues. I believe storing as binary(16 bytes) should end up being more efficient than the string representation(36 bytes?), And there seems to be some indication that indexing 16 byte blocks should be more efficient in mysql than strings. ![]() Return Column(id_column_name,UUID(),primary_key=True,default=uuid.uuid4) Raise ValueError,'value %s is not a valid uuid.UUID' % valueĭef process_result_value(self,value,dialect=None): If value and isinstance(value,uuid.UUID):Įlif value and not isinstance(value,uuid.UUID): Types.TypeDecorator._init_(self,length=)ĭef process_bind_param(self,value,dialect=None): from sqlalchemy import typesįrom import MSBinary I've been refining a UUID column type for the past few months and I think I've finally got it solid. I think it has some advantages over autoincrement that make it worth it. Regardless of how my colleagues who really care about proper database design feel about UUID's and GUIDs used for key fields. I wrote this and the domain is gone but here's the guts.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |