Blough: TheRealGent: temp tables are dropped at the end of the connection.
Spruel: Xgc, a derived table? What would I be querying/
Stepien: TheRealGent: SELECT . FROM SELECT . AS derived_table LEFT JOIN .
Bouton: TheRealGent: That would be created with a UNION SELECT for each enum value
Island: Xgc, right, but the derived table query – you’re referring to the query against info schema?
Feldhake: TheRealGent: No. It’s totally inside the application to create that SQL part of the query.
Cezar: TheRealGent: That’s one simple way, without any duplication.
Clyatt: TheRealGent: Your application knows the list of the possible statuses at the time it sends that SELECT doesn’t it?
Stublaski: TheRealGent: and no need to touch the database.
Martell: TheRealGent: You said the java app has access to this java enum.
Drehobl: TheRealGent: The enum can even have a method to provide that SQL fragment.
Dingel: TheRealGent: Any report could use it without knowing the details.
Mura: That is not simple at all. That is horrendous. That implies 1 You’re using straight SQL/JDBC instead of ORMs or abstraction layers 2 You’d need to waste inordinate amounts of cpu cycles a day just iterating over enumeration values and dynamically generating strings that fill the heap unecessarily or 3 Design a caching system to avoid #2. None of which is “simple” or fun, or sane, or anything a dev in his right mind would like to do.
Bhatnagar: TheRealGent: Go away.
Voiles: Refuses to do the right thing and then whine about all the bad ways to get around doing the right thing.
Hitchings: There are other ways. Not going to waste the time.
Hoff: Xgc, don’t attack someone else’s critique of what you believe to be is correct.
Thoreson: I’ll go the outer join against info schema route. Thanks for the help guys.
Dingus: TheRealGent: YOU asked for other approaches. These were not suggested, until you refused to access the most appropriate.
Vandorien: Xgc, I did. And I heard you out, thanked, and settled on one of your proposed solutions. I just gave my own opinion on some of them, and obviously the last one didn’t sit well with your feelings/ego.
Clyatt: TheRealGent: You don’t want to waste precisous CPU cycles at your client app to prepare a mere string consisting of few values, but you are ready to waste CPU cycles at the database server to query Information_schema which in some cases can mean huge performance penalty for the entire server?
Dinges: Salle, and by entire server you mean one of the many abundant slaves that don’t have to support a mission critical app? Yes.
Hoen: TheRealGent: information_schema is a very bad idea, except for offline type operations.
Clyatt: TheRealGent: You managed to annoy the most patient and forgiving person here in this channel. That’s amazing achievement.
Magliano: The fact that he’s tied to some ORM is not material. That’s his choice and didn’t offer that at all in the discussion.
Strathman: Preferring an enum over a lookup table and getting mad about it.
Strathman: Changing the values in an ENUM requires ALGORITHM=COPY doesn’t it.
Korvin: Just the worst idea. Any first year student would have ruled out an enum for data that obviously needs to be in a list *inside the database* and accessible as a table.
Strathman: Yeah it does. Enjoy the downtime every time you want to execute DDL on that table to add a new ENUM type. It’s going to get painful once your table grows to a non-trivial size.
Brule: Xgc if you’re involved with Oracle/MySQL you should pitch the idea to deprecate the functionality.
Clyatt: TheRealGent: And changing the values in ENUM requires ALTER on your Master server and then wait for it to be replicated to all of your abundant slaves. Correct?
Weisenberger: TheRealGent: You don’t remove that as an option. You just teach people when to use which tools / constructs before they design databases.
Strathman: ENUM can be a sensible choice if you have a fixed set of possibilities that is not going to change over time.