search

LouisK130 / IFCB-Annotate

A web-based interface for classifying IFCB image data
3 stars 2 forks source link

consider uuid to varchar cast #61

Open joefutrelle opened 6 years ago

joefutrelle commented 6 years ago

https://dba.stackexchange.com/questions/177733/postgresql-custom-operator-uuid-to-varchar

if this wouldn't mess up the Django ORM, it would be really convenient for writing raw sql queries. In the stack overflow question they're going the opposite way than we would -- we would make an implicit cast from uuid to varchar so we can compare timeseries IDs without having to cast the uuids to varchar.

this would be a custom sql migration and would probably not break the ORM, but I'm not going to try it on the live server!

joefutrelle commented 6 years ago

the alternative would be making a view of classify_timeseries that would include the cast, and use that for bulk queries, but that's a less elegant solution.

LouisK130 commented 6 years ago

I'm not sure I fully understand. The issue is that the type of timeseries_id is uuid in classify_timeseries but varchar in others, like classify_classification, so comparisons require casts?

Is it just a question of whether or not you have to include ::uuid or ::varchar during comparisons in a query?

joefutrelle commented 6 years ago

Yes, the only purpose of the cast would be to make queries slightly less annoying to write.

LouisK130 commented 6 years ago

Is it preferable to simply update classify_classification and classify_tag to have timeseries_id columns of type uuid? Shouldn't require too much effort. I honestly can't remember if I had a legitimate reason for making them text or if it was just ignorance.

On a related note, do you use the classify_winning_class view? I never noticed it until now, so I certainly haven't been using it in queries within the app. It would need to be regenerated if we change the schema.

joefutrelle commented 6 years ago

I'm loathe to change the schema of the django-controlled tables, especially right now when we have to deal with the at-sea database integration from EXPORTS.

wrt classify_winning_class view, that is in the 0003_summary_views migration, so it doesn't need to be regenerated if we make another migration.

Summary views are handy to avoid having to construct complex queries on the client side, when we have a few that we'll want to do routinely. Adding them as custom SQL migrations doesn't break the ORM, so we can add them willy-nilly, as far as I'm concerned.