3 ms·
I was handed maintenance of a database with horrid schemas, like table_1 joined table_2 on "substr(table_1.foo, 4, 3) = upper(table_2.bar) and table_1.type = ta
by kstrauser 5y ago
I was handed maintenance of a database with horrid schemas, like table_1 joined table_2 on "substr(table_1.foo, 4, 3) = upper(table_2.bar) and table_1.type = table_2.first_name", and crazy stuff like that. One table had an index on "upper(substr(purchase_id,1,1))". Another was indexed on "substring(table_3.xmlvars,'<KEY>(.*)</KEY>')". (I just looked up the old codebase and copied and pasted. These aren't exaggerations.) I had zero chance of fixing the schema because a giant legacy internal application was built with it, and I had to make my (new) application adapt to its schema instead.
I spent a couple of months exhaustively documenting these relationships in the form of SQLAlchemy models, so that I could eventually write table_1.join(table_2) and have it do the right thing without having to remember (and implement!) that complexity everywhere those two tables touched. It was sanity saving, and for that I’m hugely grateful. Thank you.
If you’re starting with a brand new DB schema, shiny and pure, perhaps you can get away with hardcoding a bunch of SQL. I’ve rarely had that luxury, so query builders are one of my favorite things.
- capitainenemo 5y agoSeems to me if there's a bunch of apps dependent on that legacy data, that cleanup could have been done more usefully and elegantly in the database with some views anyone could use. Or, if for some reason views couldn't be added (lack of access) a set of generic CTE includes?
- kstrauser 5y agoSigh, if only. OK, so the rest of the story was that the original app was written in Visual FoxPro. I was hired to build a web frontend for it. Well, turns out 1) there is, or at least was, no native VFP client for Unix, and 2) the Windows client was single threaded per host, so you couldn't even farm the connectivity out to a Windows process. After struggling with this for a while -- a cluster of Win XP hosts running a single-threaded VFP client and serving results to a Linux webserver via XMLRPC, as was the custom at the time -- I finally said "screw this, let's brute force it." That turned into https://github.com/kstrauser/pgdbf https://github.com/kstrauser/pgdbf, which is an app to convert a VFP table into a PostgreSQL table. We had it running on a cron job, which worked fine because the website was read-only and it was the VFP app that was actually writing to the tables. It was alright to have up to a few hours of latency between the VFP view of the data and the PostgreSQL view of it. When the company later committed to rewriting the VFP app in a sane language, they wrote it to run directly against PostgreSQL. That was quite a few years after I'd started there, though.
- jmnicolas 5y agoYou should receive a medal! I feel so dirty after just reading this, that I have to take a shower! ;)
- kstrauser 5y agoSometimes ya just do what ya’ve gotta do. :) At the time, I was so heads-down with the day to day challenges that I didn’t really think about it. It wasn’t until later that the enormity of it really sunk in.
- philwelch 5y agoIf you have to interact with a system that's so poorly designed that you need an adapter to make sense of it, then sure, use an adapter. The thing is, the more you use an ORM, the less and less thought you put into designing your database to have a sensible interface of its own, so of course your database isn't going to have a sensible interface. Probably not as horrific as what you're describing, but as much as an ORM can solve that kind of problem, it can also perpetuate it. I'm almost terrified to ask how your DB schema got that fucked, but I'm going to guess it's because it's very tightly coupled to that giant legacy internal application and no one thought to take it seriously as its own service component with a consciously designed interface.