A critical look at Marvel vs. Capcom....

Kamil Podlesak

@Groaner said in A critical look at Marvel vs. Capcom....:

@Kamil-Podlesak said in A critical look at Marvel vs. Capcom....:

@Groaner said in A critical look at Marvel vs. Capcom....:

Having worked on an actual MVC project for once, and spent enough time deep in the patterns, I'm beginning to question its utility.

So the workflow in the app I maintain is something like this:

User clicks button to frobnicate widget.

HTML element onclick event calls [Java | Type]script method which gets or posts an HTTP endpoint

Endpoint is mapped to a controller

Controller calls a Service

Service calls a Repository

Repository calls a homegrown data layer that builds a SQL statement and executes it

SQL results are consumed into a strongly-typed DataTable

Mapper class takes values from this DataTable and maps values to a Model

Repository passes Model results to Service

Service maps Model results to a Viewmodel using a second mapper class and returns to Controller

Controller returns Viewmodel results via HTTP response

[Java | Type]script consumes results and does the needful

Now, when I was a youngling developer, the elders taught me the importance of separation of concerns, and that code should be divided into layers. Usually, it was presented as three layers:

Presentation layer: responsible for putting text on a page and mapping variables to onscreen values, as well as gathering user input and passing that on to the...

Business layer: handles operations on a very high level, that is to say, frobnicating a widget. It cares not for the nitty-gritty of how a widget is frobnicated in the database, as that's the concern of the...

Data layer: handles CRUD/persistence operations and maybe does some mapping to and from classes at best.

This makes for a rather simple architecture as there's a clean, logical separation of concerns, and not too many steps in between that would all need to be retrofitted should there need to be a major change in the workflow.

I don't really understand the this part. Apparently the application you work on use three-layer data model
as you describe it, but... something?

It's not a three-layer model. If it were, the call stack wouldn't be near a dozen layers deep.

Each layer can, of course, have some internal layers. And if you count each and every procedure call as a layer, then it's pretty necessary in some languages.

Of course, lasagna height should be kept minimal, which is probably not your case... which is why I have said that it's "a little convoluted". The "little" part means that I have seen worse. In my days, we have to waddle through snow, sleet and EJB1.0...

So yeah, it might be a little bit of Cargo Cult.

I used to work with a guy that would label all his classes with a Fowler citation of which pattern it implemented. He probably came from the Java world, so I was ultimately able to forgive him for it. Sort of.

In my 20 years as a Java developer, I haven't seen that. I guess I can count myself lucky :-)

Still, my point remains: There seems to be some Cargo Cult and some small level of -ery around you, but nothing particular to MVC.

"But what if your API needs to be consumed by an IoT toaster without a filesystem?" I would hope that this toaster has the ability to call stored procedures. I've worked on .NET CF devices with very stripped-down functionality, and even those could connect to a database. Failing that, make a webservice.

Stored procedures called directly?
Just no. Don't do that. Do a webservice (RPC, REST-like, whatever) or anything with proper API

I'm with you on at least putting it behind a webservice, but stored procedures already have the elements of an API: a unit of work, input parameters, and a contract (which can be altered at any time).

Yes, of course, it is an API. It's just quite a shitty API. Hence my choice of words: proper API.
Maybe I should be even more specific: prop, well-designed API.

boomzilla

@Groaner said in A critical look at Marvel vs. Capcom....:

@boomzilla said in A critical look at Marvel vs. Capcom....:

The main effect of putting it all in a service (and I already do have lots of services and service methods) would be to scatter my code around and have a zillion similarly named methods because I always need to access the same data in sliiiiightly different ways. Or you have monster methods building codethulu dynamic queries.

Where I draw the line is that if the same operation happens in two or more places, that must be encapsulated. Pretty cut and dry. There's no need to create a Service until there's an entire collection of such operations, at which point the extra effort not only makes maintenance easier, but creates the convenience of having a collection of solved problems and building blocks.

That's largely where we are with our java code. For our (new) angular front end it's a bit different, where we put all http calls in services, which makes it easy to mock them for testing and all. This actually makes a lot of sense even when only one place in the code calls some particular endpoint.

boomzilla

@MrL said in A critical look at Marvel vs. Capcom....:

@dkf said in A critical look at Marvel vs. Capcom....:

@MrL said in A critical look at Marvel vs. Capcom....:

You don't even have to think which tables were touched.

I've seen those things before. They're where it's all too easy to end up fetching a 100MB table from the database just to check whether there are any results, and then fetch all that data again just to get the first few values. And then people wonder vaguely why things aren't meeting their performance requirements.

I wouldn't say it's easy to do that. You would need to have zero understanding of your ORM, zero understanding of your db and fuck up on top of that. Certainly possible, as experience shows, but not really easy.

I have cow-orkers do shit like this all the damn time. You're right that they don't have a deep understanding of what's going on, of course, and they test their code with very small cases, leaving it to users to find the gnarly show stopping grind to a halt problems with their output.

MrL

@boomzilla said in A critical look at Marvel vs. Capcom....:

@MrL said in A critical look at Marvel vs. Capcom....:

@dkf said in A critical look at Marvel vs. Capcom....:

@MrL said in A critical look at Marvel vs. Capcom....:

You don't even have to think which tables were touched.

I've seen those things before. They're where it's all too easy to end up fetching a 100MB table from the database just to check whether there are any results, and then fetch all that data again just to get the first few values. And then people wonder vaguely why things aren't meeting their performance requirements.

I wouldn't say it's easy to do that. You would need to have zero understanding of your ORM, zero understanding of your db and fuck up on top of that. Certainly possible, as experience shows, but not really easy.

I have cow-orkers do shit like this all the damn time. You're right that they don't have a deep understanding of what's going on, of course, and they test their code with very small cases, leaving it to users to find the gnarly show stopping grind to a halt problems with their output.

Seems like I'm lucky to work with competent people.
Not the first time I get that impression.

Fuck, now I'm scared what's outside my bubble.

Bulb

@Kamil-Podlesak said in A critical look at Marvel vs. Capcom....:

Yes, of course, [stored procedures] is an API. It's just quite a shitty API. Hence my choice of words: proper API.
Maybe I should be even more specific: prop, well-designed API.

It's a real pity that database engines stagnated this much regarding their programmer tooling. Stored procedures—and even more so views, triggers and constraints—have a big benefit for ensuring consistency, because they run inside the transactions and because they cannot be bypassed. And once you start putting that first level of abstraction inside the database to take advantage of constraints, it would make sense to just put all of the first level of abstraction in there.

Unfortunately the in-database logic is harder to debug and harder to test. Not impossible. You can have unit tests for stored procedures. It just requires the right skill-set.

In these days, it also isn't more difficult to ensure you have the right version. There is enough tools for updating database schema that you just run when deploying or starting the application and they'll deploy the correct versions of the stored procedures too, so there is no problem.

The biggest problem is really the lack of skill-set, since relatively few programmers understand databases at more than very basic level, and the lack of tooling, which is a vicious cycle, because when nobody understands databases, nobody is trying to make them easier to use and when they are not easy to use, nobody wants to learn them.

I also blame the blind alley that is object oriented programming and the insistence that objects and entities are completely different things (when they are actually quite similar) that is fortunately dying down lately. The entity-relationship model of databases is still the best model for data we have, but for long time the higher levels were forced to the objective paradigm and lot of effort spent bridging the impedance mismatch instead of trying to find out how to work with the model used for storage also on the higher levels.

izzion

@Bulb said in A critical look at Marvel vs. Capcom....:

You can have unit tests for stored procedures. It just requires the right skill-set.

And the right tool set helps a lot.

That said, I'd rather go to one of @error's parties than use tSQLt, soooooooo.

error

@izzion said in A critical look at Marvel vs. Capcom....:

That said, I'd rather go to one of @error's parties than use tSQLt, soooooooo.

That's awesome! We'd love to have you!

Oh. Oh.

boomzilla

@Bulb said in A critical look at Marvel vs. Capcom....:

I also blame the blind alley that is object oriented programming and the insistence that objects and entities are completely different things (when they are actually quite similar) that is fortunately dying down lately.

"Completely different things?" Could you elaborate? It's totally possible that I'm just not up on all the hot theory (my degree wasn't in computer science or anything like that so I've never been super up on that sort of thing) and my view is too practical and literal to know what you're talking about here.

dkf

@boomzilla As I understand it, entities are abstractions over database rows and objects; they can either exist as a row or as an object, or they can be in-memory delegates for database rows. In that last case, they can either be live or copies: with a live entity, writes to it are written through to the database (and it should only really be available while a transaction is held), and with a copied entity, you see the state as it was in the transaction and continue to see it after the transaction finishes, but don't have the live-write-back property (they might even be read-only). Other things are possible… but ghastly and fragile and very much not what I'd recommend.

boomzilla

@dkf yeah...I still don't get it. Maybe because when you start talking about object orientation I'm thinking about my code outside of the DB. And my entities are objects that are abstractions of rows in the DB. Of course, not all objects are entities but all my entities are objects.

Maybe I'm also contaminated based on using Hibernate where they use the @Entity annotation to identify objects that persist in the DB.

dkf

@boomzilla I fully admit that I write my own these days. I tried doing it the other way and it was… not great. Especially as I needed a query that was awkward to express in HQL (and the whole of Hibernate has a really steep learning curve).

boomzilla

@dkf yeah, HQL is hugely useful in many ways but I'm not ashamed of using native SQL when performance or complexity requires it.

Bulb

@boomzilla said in A critical look at Marvel vs. Capcom....:

@Bulb said in A critical look at Marvel vs. Capcom....:

I also blame the blind alley that is object oriented programming and the insistence that objects and entities are completely different things (when they are actually quite similar) that is fortunately dying down lately.

"Completely different things?" Could you elaborate? It's totally possible that I'm just not up on all the hot theory (my degree wasn't in computer science or anything like that so I've never been super up on that sort of thing) and my view is too practical and literal to know what you're talking about here.

Database is composed of entities (tables) and relations (foreign keys and relation tables for joining them).

Object oriented program is composed of classes.

Class diagram and Entity-Relationship diagram look almost the same except for using different symbology. And entities not having inheritance, but they can have composition and there is not much difference in the result anyway.

But—at least according to a lecture on software architecture I've attended recently, because I don't otherwise care about what the architecture astronauts write much—the key proponents of object oriented design, until lately, claimed that they are very different concepts, and only lately started admitting that they are not that different after all, especially since inheritance, the main additional feature of classes, is now considered harmful.

It's rather architecture astronaut stuff, but especially the Java frameworks are guided it.

Gąska

@Bulb said in A critical look at Marvel vs. Capcom....:

@boomzilla said in A critical look at Marvel vs. Capcom....:

@Bulb said in A critical look at Marvel vs. Capcom....:

I also blame the blind alley that is object oriented programming and the insistence that objects and entities are completely different things (when they are actually quite similar) that is fortunately dying down lately.

"Completely different things?" Could you elaborate? It's totally possible that I'm just not up on all the hot theory (my degree wasn't in computer science or anything like that so I've never been super up on that sort of thing) and my view is too practical and literal to know what you're talking about here.

Database is composed of entities (tables) and relationships

boomzilla

@Bulb said in A critical look at Marvel vs. Capcom....:

@boomzilla said in A critical look at Marvel vs. Capcom....:

@Bulb said in A critical look at Marvel vs. Capcom....:

I also blame the blind alley that is object oriented programming and the insistence that objects and entities are completely different things (when they are actually quite similar) that is fortunately dying down lately.

"Completely different things?" Could you elaborate? It's totally possible that I'm just not up on all the hot theory (my degree wasn't in computer science or anything like that so I've never been super up on that sort of thing) and my view is too practical and literal to know what you're talking about here.

Database is composed of entities (tables) and relations (foreign keys and relation tables for joining them).

Object oriented program is composed of classes.

Ah, right. I guess my more recent Hibernate knowledge was overriding my Entity-Relationship-Diagram knowledge.

Class diagram and Entity-Relationship diagram look almost the same except for using different symbology. And entities not having inheritance, but they can have composition and there is not much difference in the result anyway.

But—at least according to a lecture on software architecture I've attended recently, because I don't otherwise care about what the architecture astronauts write much—the key proponents of object oriented design, until lately, claimed that they are very different concepts, and only lately started admitting that they are not that different after all, especially since inheritance, the main additional feature of classes, is now considered harmful.

It's rather architecture astronaut stuff, but especially the Java frameworks are guided it.

Yeah, that's all a bunch of pendantic irrelevance and implementation details AFAIC.

Groaner

@MrL said in A critical look at Marvel vs. Capcom....:

@Groaner said in A critical look at Marvel vs. Capcom....:

@MrL said in A critical look at Marvel vs. Capcom....:

@Groaner said in A critical look at Marvel vs. Capcom....:

Why? Schema changes will require a similar amount of effort whether you do SQL in the application, use an ORM or whatever.

Changes in schema don't propagate through stored procedures, so you don't know what stopped working. You have no tests for SPs, so you don't know what stopped working correctly. Hell, you can easily forget to update something if it's rarely used.

That's why you always do a global search for references before making any change. Before even deciding to make any change to assess the impact of such a change.

Yes, you are forced to do searches, because business logic in SPs is a fragmented mess that you have no control of.

What?

If one doesn't even have control over the stored procedures that one wrote by oneself, one probably has bigger problems than business logic concerning the database being in the database.

And it's not like the application side is always going to be immaculate.

using(var context = new AdventureWorksContext())
{
    //do stuff

    context.Save();
}
Type safety, change propagation, error checking at compile time. You don't even have to think which tables were touched.
And still have all the run-time errors to deal with
What runtime errors?

Key/constraint violations, type conversion errors, etc.? Please tell me you don't just hit Build and pray.

with the added bonus of not easily being able to tune your queries.

You can tune queries in ORMs, at least in good ones. But you don't have to worry about it if you write your BL well, as @izzion said.

Can this magical ORM do table/index/query hints? Does it know how to aggregate rows into a temp table when needed? Does it retain compiled execution plans and allow users to address parameter sniffing?

Does this well-written BL also like haggis?

That's the most desired situation of course, a proper ORM. But you can do similar things by hand with some more effort. The main thing is: keep SQL away from business logic, keep db away from application.

Why?

Because SQL is a bad choice for writing business logic

Why?

and dealing with db in application is just added complexity.

When you're using an ORM, your application is still marrying itself to the schema. That much doesn't change.

Not really. You either design schema to mirror business domain, or you translate schema objects to business objects and do BL with them. Either way, db is just a CRUD machine you don't deal with directly.

Hint: if your application uses a database, it deals directly with a database AT SOME LEVEL. There's no escaping it.

If you add a new column with a constraint to dbo.Orders and want to use it in your app, you need to update all your business objects to reflect that. If you add a new table, you need to add corresponding business objects to reflect that. If you change the relationship between two tables, you have to update all your business objects to reflect that.

This coupling can be mitigated somewhat with layers of abstraction and contract surfaces, like views and stored pr.... oh. Oops.

I would agree with you, but there are these things called "legacy applications," so we'll have to deal with them for a while yet.

I worked with stuff done this way in the past and from then on turned down every offer that involved it. I may consider doing it again for a lot of , but those offers tend to pay less than market average

The most challenging job I ever had was also the one that paid the lowest. Really gets the noggin turning.

Groaner

@Bulb said in A critical look at Marvel vs. Capcom....:

In these days, it also isn't more difficult to ensure you have the right version. There is enough tools for updating database schema that you just run when deploying or starting the application and they'll deploy the correct versions of the stored procedures too, so there is no problem.

Three words: idempotent deployment scripts.

Or just get Redgate.

The biggest problem is really the lack of skill-set, since relatively few programmers understand databases at more than very basic level, and the lack of tooling, which is a vicious cycle, because when nobody understands databases, nobody is trying to make them easier to use and when they are not easy to use, nobody wants to learn them.

I prefer to see it as most developers are dragged into the RDBMS world kicking and screaming, and have an irrational hatred of SQL until and unless they take the time to learn it. Which requires leaving the comfortable bubble of C#/Java/Blub/etc.

I also blame the blind alley that is object oriented programming and the insistence that objects and entities are completely different things (when they are actually quite similar) that is fortunately dying down lately. The entity-relationship model of databases is still the best model for data we have, but for long time the higher levels were forced to the objective paradigm and lot of effort spent bridging the impedance mismatch instead of trying to find out how to work with the model used for storage also on the higher levels.

Relational model won out over the hierarchical model forty years ago because you can slice a relational model any number of ways, but doing the same with a hierarchical model requires traversing a fixed hierarchy in nontrivial ways to achieve the same functionality.

I've also been reading a bit about how modern game engine design (which used to be all about OOP and Patterns in many cases) is now moving toward a more functional/batched approach to squeeze out ever more performance - as if the benefits aren't just confined to databases.

MrL

@Groaner said in A critical look at Marvel vs. Capcom....:

Yes, you are forced to do searches, because business logic in SPs is a fragmented mess that you have no control of.

What?

If one doesn't even have control over the stored procedures that one wrote by oneself, one probably has bigger problems than business logic concerning the database being in the database.

You don't have control over BL in SPs as a whole, because for anything nontrivial it becomes a convoluted fragmented mess. So you have to do searches, hoping you'll find all relevant places, instead of being immediately shown by your tools.

What runtime errors?

Key/constraint violations, type conversion errors, etc.? Please tell me you don't just hit Build and pray.

ORM takes care of those.

INB4, yes, you can violate a constraint if you have no idea what you're doing.

Can this magical ORM do table/index/query hints?

Depends on ORM.

Does it know how to aggregate rows into a temp table when needed?

Doesn't really make sense when using ORM.

Does it retain compiled execution plans and allow users to address parameter sniffing?

DB does that.

Because SQL is a bad choice for writing business logic

Why?

It's a query language, with bad readability, verbose inconsistent syntax, terrible flow control and subpar tooling. It's great for, well, querying data.

Hint: if your application uses a database, it deals directly with a database AT SOME LEVEL. There's no escaping it.

If you add a new column with a constraint to dbo.Orders and want to use it in your app, you need to update all your business objects to reflect that. If you add a new table, you need to add corresponding business objects to reflect that. If you change the relationship between two tables, you have to update all your business objects to reflect that.

Yes, you update your mapping and immediately see where it makes a breaking difference. You fix it, run tests and immediately you know what pieces of logic were impacted.

Bulb

@boomzilla said in A critical look at Marvel vs. Capcom....:

@Bulb said in A critical look at Marvel vs. Capcom....:

But—at least according to a lecture on software architecture I've attended recently, because I don't otherwise care about what the architecture astronauts write much—the key proponents of object oriented design, until lately, claimed that they are very different concepts, and only lately started admitting that they are not that different after all, especially since inheritance, the main additional feature of classes, is now considered harmful.

It's rather architecture astronaut stuff, but especially the Java frameworks are guided it.

Yeah, that's all a bunch of pendantic irrelevance and implementation details AFAIC.

I would rather say it's irrelevant ideological details.

I generally tend to see things as the same all over again, not different. For me, classes are not some special different concept, but data structures with associated functions, and methods are just functions with implicit argument and different calling syntax. And inheritance is just implicit inclusion of the base class. And since table rows are also just data structures, it's all the same to me.

But the object oriented proponents are often touting it as something completely different and glorious and unlike the things before—and then of course programmers who just learned Java and don't have any background in databases or non-object-obsessed languages don't understand it and it's just a magic for them and they mess it up.

Carnage

@MrL said in A critical look at Marvel vs. Capcom....:

@boomzilla said in A critical look at Marvel vs. Capcom....:

@MrL said in A critical look at Marvel vs. Capcom....:

@dkf said in A critical look at Marvel vs. Capcom....:

@MrL said in A critical look at Marvel vs. Capcom....:

You don't even have to think which tables were touched.

I've seen those things before. They're where it's all too easy to end up fetching a 100MB table from the database just to check whether there are any results, and then fetch all that data again just to get the first few values. And then people wonder vaguely why things aren't meeting their performance requirements.

I wouldn't say it's easy to do that. You would need to have zero understanding of your ORM, zero understanding of your db and fuck up on top of that. Certainly possible, as experience shows, but not really easy.

I have cow-orkers do shit like this all the damn time. You're right that they don't have a deep understanding of what's going on, of course, and they test their code with very small cases, leaving it to users to find the gnarly show stopping grind to a halt problems with their output.

Seems like I'm lucky to work with competent people.
Not the first time I get that impression.

Fuck, now I'm scared what's outside my bubble.

I've once, in my entire time in the field, accidentally ended up in a project that by pure fucking happenstance managed to round up 8 highly skilled, highly competent developers once. It was a bloody dream. Every task got solved in a sane time, and a sane way. That team managed to create a big system on time, without any major bugs. The customer was very surprised, because it was the first time in the history of that company that a large software project was delivered on time in a functional state.
Most places you find one or two properly competent devs per 10 devs. Some have none.

So yeah, IT is a fucking joke. If you have competent people around you, stay there. The grass is not greener on the other side.

Bulb

@Groaner said in A critical look at Marvel vs. Capcom....:

@Bulb said in A critical look at Marvel vs. Capcom....:

The biggest problem is really the lack of skill-set, since relatively few programmers understand databases at more than very basic level, and the lack of tooling, which is a vicious cycle, because when nobody understands databases, nobody is trying to make them easier to use and when they are not easy to use, nobody wants to learn them.

I prefer to see it as most developers are dragged into the RDBMS world kicking and screaming, and have an irrational hatred of SQL until and unless they take the time to learn it. Which requires leaving the comfortable bubble of C#/Java/Blub/etc.

Which is a pity, because for some kinds of business logic, SQL is actually much simpler way to write it.

Most common applications are fairly simple. They have relatively few relationships and rarely need to do bulk operations. Just find a company, get all its contact addresses for it etc. That's easy with ORM.

But there are cases where you do joins over five, ten tables, and do unions, over select common attributes only and such. For example I used this a lot when I worked with maps. We basically created a large tree of SQL queries that created auxiliary tables for the next queries, and it was just so much shorter, more readable and more efficient than writing it all in objects.

And it did start out written in objects—it was originally read from files, it was me who introduced the database into it, because SQL was just so much better tool for the job.

Now there is a couple of issues with SQL, and its embedding to other languages. You should really have compile-time checking, and type-checking, for it, but in most languages you don't—so you again have the ORM instead that does these things. But when you know SQL, it's just… another thing to learn that does the same thing with different syntax, except is is usually not as powerful.

boomzilla

@Bulb said in A critical look at Marvel vs. Capcom....:

@boomzilla said in A critical look at Marvel vs. Capcom....:

@Bulb said in A critical look at Marvel vs. Capcom....:

But—at least according to a lecture on software architecture I've attended recently, because I don't otherwise care about what the architecture astronauts write much—the key proponents of object oriented design, until lately, claimed that they are very different concepts, and only lately started admitting that they are not that different after all, especially since inheritance, the main additional feature of classes, is now considered harmful.

It's rather architecture astronaut stuff, but especially the Java frameworks are guided it.

Yeah, that's all a bunch of pendantic irrelevance and implementation details AFAIC.

I would rather say it's irrelevant ideological details.

I generally tend to see things as the same all over again, not different. For me, classes are not some special different concept, but data structures with associated functions, and methods are just functions with implicit argument and different calling syntax. And inheritance is just implicit inclusion of the base class. And since table rows are also just data structures, it's all the same to me.

Yeah, that's more or less what I was thinking.

dkf

@Bulb said in A critical look at Marvel vs. Capcom....:

But when you know SQL, it's just… another thing to learn that does the same thing with different syntax, except is is usually not as powerful.

The tricks to doing SQL well seem to be to not see views (or temporary tables) as sinful and to actually have some appreciation of basic relational algebra. Also, JDBC is fucking terrible due to its total lack of support for binding by name, either on input to the DB or output from the DB, and that's spawned all sorts of ill-conceived attempts to build ORMs on top to try to avoid having to deal with indexing all the things by number (terribly error prone for real queries) but they're all also terrible.

boomzilla

@Groaner said in A critical look at Marvel vs. Capcom....:

Does this well-written BL also like haggis?

lolwhat

@Carnage said in A critical look at Marvel vs. Capcom....:

If you have competent people around you, stay there, unless manglement is a clusterfuck of buddy-buddy assholes who eventually drive away competent people.

FTFM. I start a new job in a couple weeks.

dkf

@boomzilla said in A critical look at Marvel vs. Capcom....:

Needs to be deep fried for true Scottish goodness!

Carnage

@lolwhat said in A critical look at Marvel vs. Capcom....:

@Carnage said in A critical look at Marvel vs. Capcom....:

If you have competent people around you, stay there, unless manglement is a clusterfuck of buddy-buddy assholes who eventually drive away competent people.

FTFM. I start a new job in a couple weeks.

That would make manglement incompetent.

lolwhat

@boomzilla said in A critical look at Marvel vs. Capcom....:

@Groaner said in A critical look at Marvel vs. Capcom....:

Does this well-written BL also like haggis?

Nope! thread is blah blah.

MrL

@lolwhat said in A critical look at Marvel vs. Capcom....:

@Carnage said in A critical look at Marvel vs. Capcom....:

If you have competent people around you, stay there, unless manglement is a clusterfuck of buddy-buddy assholes who eventually drive away competent people.

The best team I ever worked in was destroyed exactly this way.

Bulb

@dkf said in A critical look at Marvel vs. Capcom....:

The tricks to doing SQL well seem to be to not see views (or temporary tables) as sinful and to actually have some appreciation of basic relational algebra.

Definitely. Views, subqueries and temporary tables are a way to structure complex logic. I also totally love the with prefix now that SQLite got it—I've just pulled SQLite into some script two months ago because I needed to shuffle through some dependency information and loading it in a database and sorting it out with SQL just seemed simpler than loading it in objects and writing the list comprehensions (python script).

@dkf said in A critical look at Marvel vs. Capcom....:

Also, JDBC is fucking terrible due to its total lack of support for binding by name, either on input to the DB or output from the DB

Seriously? I've used SQLite from many languages—though never Java—and it had binding by name in all of them. And it's even better in TCL (SQLite started out as a TCL plugin) where you just mention the variables in the {}-quoted query and it does the needful. Excellent language integration.

@dkf said in A critical look at Marvel vs. Capcom....:

that's spawned all sorts of ill-conceived attempts to build ORMs on top to try to avoid having to deal with indexing all the things by number (terribly error prone for real queries) but they're all also terrible.

What I want is a tool that takes the query embedded in the language that possibly contains variable references, binds those variables, checks the query against schema provided somewhere and generates appropriate type declarations for the host language to verify that you are binding variables of correct types.

Query builders come close, but it's yet another syntax and there is a risk that you won't be able to use some feature of the underlying database engine. Loading the schema into appropriate database engine and checking queries written as SQL there is both simpler and more powerful.

izzion

@Groaner said in A critical look at Marvel vs. Capcom....:

Can this magical ORM do table/index/query hints?

In general, no, and if you're working with things that are such a mess that they need those hints either (1) your database people need to get off their asses and fix the indexing so the query optimizer can build decent plans to begin with or (2) you're doing reporting that is sufficiently complex that you should use a purpose built tool (e.g. SSIS or whatever the $$$ORAORAORA equivalent is).

Does it know how to aggregate rows into a temp table when needed?

Generally the same as above. Though if you have a really grungy query that needs to do a lot of aggregation and temp table work, with the ORM solution, you have the option of explicitly pulling all of the data across to the application tier (read as: web server) and do the aggregation/calculation work there. Where, to paraphrase Brent Ozar, "you aren't paying $7,000 a core" to do that work, so it's probably more cost efficient to let the ORM pull everything down and crunch numbers in CLR code anyway!

Does it retain compiled execution plans and allow users to address parameter sniffing?

The SQL server will retain compiled execution plans for standard queries just as easily as it does for stored procedures (don't believe me? Install a SQL 2016+ dev box to play with somewhere, turn Query Store on, and throw a bunch of the same plain old query at it over and over again). I'll concede that if you do have a parameterized query that is susceptible to parameter sniffing, the ORM isn't going to ever really know about it or be able to do the stupid ~~human~~ stored procedure tricks that exist to break parameter sniffing. But (again, assuming modern SQL Server here, since that's my domain of expertise) you still have access to Query Store's tooling to strongly encourage the good plan, or you could look at tuning the query to actually fix the small plan / big plan problem or just opt into pulling everything down and doing the data processing that causes the parameter sniffing spills in the application tier's cheaper CPU/RAM.

boomzilla

@Bulb said in A critical look at Marvel vs. Capcom....:

@dkf said in A critical look at Marvel vs. Capcom....:

Also, JDBC is fucking terrible due to its total lack of support for binding by name, either on input to the DB or output from the DB

Seriously? I've used SQLite from many languages—though never Java—and it had binding by name in all of them. And it's even better in TCL (SQLite started out as a TCL plugin) where you just mention the variables in the {}-quoted query and it does the needful. Excellent language integration.

Yeah, "raw" JDBC does not, though it's common for libraries to add that on top of it. Hibernate's entity manager, for instance, supports it from the user side and translates it to positional parameters when it actually talks to the DB.

PleegWat

@Bulb said in A critical look at Marvel vs. Capcom....:

What I want is a tool that takes the query embedded in the language that possibly contains variable references, binds those variables, checks the query against schema provided somewhere and generates appropriate type declarations for the host language to verify that you are binding variables of correct types.

Programmer's Guide

Supplement to the Oracle Precompilers Guide

Programmer's Guide

Kamil Podlesak

@Bulb said in A critical look at Marvel vs. Capcom....:

@dkf said in A critical look at Marvel vs. Capcom....:

The tricks to doing SQL well seem to be to not see views (or temporary tables) as sinful and to actually have some appreciation of basic relational algebra.

Definitely. Views, subqueries and temporary tables are a way to structure complex logic. I also totally love the with prefix now that SQLite got it—I've just pulled SQLite into some script two months ago because I needed to shuffle through some dependency information and loading it in a database and sorting it out with SQL just seemed simpler than loading it in objects and writing the list comprehensions (python script).

@dkf said in A critical look at Marvel vs. Capcom....:

Also, JDBC is fucking terrible due to its total lack of support for binding by name, either on input to the DB or output from the DB

Seriously? I've used SQLite from many languages—though never Java—and it had binding by name in all of them. And it's even better in TCL (SQLite started out as a TCL plugin) where you just mention the variables in the {}-quoted query and it does the needful. Excellent language integration.

@dkf said in A critical look at Marvel vs. Capcom....:

that's spawned all sorts of ill-conceived attempts to build ORMs on top to try to avoid having to deal with indexing all the things by number (terribly error prone for real queries) but they're all also terrible.

What I want is a tool that takes the query embedded in the language that possibly contains variable references, binds those variables, checks the query against schema provided somewhere and generates appropriate type declarations for the host language to verify that you are binding variables of correct types.

Query builders come close, but it's yet another syntax and there is a risk that you won't be able to use some feature of the underlying database engine. Loading the schema into appropriate database engine and checking queries written as SQL there is both simpler and more powerful.

Funny thing is that this actually exists and was widely used in 90s. Usually known as "static SQL", it even has the named bindings.

Problem is that the very idea of integrating database access as a preprocessor with compiler is just a bad idea in 21st century and when combined with extremely bad asinine tooling, it is no wonder it just died.

error

@boomzilla said in A critical look at Marvel vs. Capcom....:

Yeah, that's all a bunch of pendantic irrelevance and implementation details AFAIC.

As far as you can what?

boomzilla

@error said in A critical look at Marvel vs. Capcom....:

@boomzilla said in A critical look at Marvel vs. Capcom....:

Yeah, that's all a bunch of pendantic irrelevance and implementation details AFAIC.

As far as you can what?

As Far As I'm Concerned.

error

@Carnage said in A critical look at Marvel vs. Capcom....:

If you have competent people around you, stay there.

I have competent people around me, and mountains of enforced procedures and bullshit that make everything take forever. (E.g. A pull request requires sign off by a tech lead, a solution architect, a DBA, and a product owner, then file a change request and get THAT approved, etc, etc).

It's annoying, but in the end I'm quite gruntled, because they compensate me well.

(Oh, and while the employees here are competent, at least half the work gets done by vendors or contractors who are very much not.)

Zecc

@boomzilla said in A critical look at Marvel vs. Capcom....:

@error said in A critical look at Marvel vs. Capcom....:

@boomzilla said in A critical look at Marvel vs. Capcom....:

Yeah, that's all a bunch of pendantic irrelevance and implementation details AFAIC.

As far as you can what?

As Far As I'm Concerned.

You might be the ultimate authority on what @boomzilla had in mind, but I choose to backronym it differently.

It's still means the same thing, As Far As I Care.

boomzilla

@Zecc said in A critical look at Marvel vs. Capcom....:

@boomzilla said in A critical look at Marvel vs. Capcom....:

@error said in A critical look at Marvel vs. Capcom....:

@boomzilla said in A critical look at Marvel vs. Capcom....:

Yeah, that's all a bunch of pendantic irrelevance and implementation details AFAIC.

As far as you can what?

As Far As I'm Concerned.

You might be the ultimate authority on what @boomzilla had in mind, but I choose to backronym it differently.

It's still means the same thing, As Far As I Care.

Close enough.

dcon

@lolwhat said in A critical look at Marvel vs. Capcom....:

@Carnage said in A critical look at Marvel vs. Capcom....:

If you have competent people around you, stay there, unless manglement is a clusterfuck of buddy-buddy assholes who eventually drive away competent people.

FTFM. I start a new job in a couple weeks.

Make sure you take them with you!

error

@dcon said in A critical look at Marvel vs. Capcom....:

Make sure you take them with you!

The last time I jumped ship, about 10 other people came along. From a company about 30 strong.

Filed under: Unintentional rhymes.

Jaloopa

@error said in A critical look at Marvel vs. Capcom....:

@dcon said in A critical look at Marvel vs. Capcom....:

Make sure you take them with you!

The last time I jumped ship, about 10 other people came along. From a company about 30 strong.

Filed under: Unintentional rhymes.

My current employer was formed when the previous incarnation was bought by a big player in the industry. The minute the old owner's no compete clause expired he set up a new company and about 50% of the people who had worked for the previous one jumped ship, with another 25% or so following in the next couple of years

Jaime

@Bulb said in A critical look at Marvel vs. Capcom....:

Stored procedures—and even more so views, triggers and constraints—have a big benefit for ensuring consistency, because they run inside the transactions and because they cannot be bypassed.

You can do that a layer up just as effectively. You can also create a stored procedure that doesn't enforce transactional consistency.

Here's two arguments against stored procedures:

The licenses for the database engine are far too expensive to be used for any business logic. Even you you are using open source DB technology, you are still creating scalability challenges by forcing more database sharding.
It is really hard to maintain the interface to a stored procedure. Your business layer can accept collection types and queryables, but stored procedures have a terrible time with them. On the return-side, it's not trivial to interrogate the return schema of a stored procedure that is anything but trivial. Even of you do, two procs that return data from the same source often don't return the same columns... so you end up with several incompatible representations of the same thing. It makes easy-sounding operations, like fetch-modify-save, into potential catastrophes.

BTW, I'm a database guy. I've been using SQL Server since 1995 and I know it very well. I've spent years with Oracle and know that pretty well. When I write software today, I use a generated database layer to access databases. The only time I ever write "clever" queries is those rare times where someone needs my help with a problematic report or nightly process. I don't make these decisions because I don't know how databases work, I make these decisions because I know the trouble that putting any business logic below the app-database interface is going to cause.

Benjamin Hall

Well, you could do what we do and only implement the most basic CRUD operations as queries. Basically single-table, straight lookups. No joins, no filtering, no order by, just SELECT (columns) FROM table WHERE condition direct SQL and then do all the filtering, joining, etc on the server. Often necessitating multiple round trips to the database for things that should be a single query. And not even a complex one. Oh, and no sort of ORM, just straight SQL in source code (parameterized, so there is that).

Supposedly, implementing more complex logic in SQL queries is "too complex" and "not generic" and "can't be reused".

Or, rather, don't do that.

dkf

@Benjamin-Hall said in A critical look at Marvel vs. Capcom....:

Supposedly, implementing more complex logic in SQL queries is "too complex" and "not generic" and "can't be reused".

You really want to avoid doing a lot of string manipulation work inside the database. They're really not very good at that sort of thing. Pass them around, yes, match against them, yes, modify them, hell no!

Benjamin Hall

@dkf said in A critical look at Marvel vs. Capcom....:

@Benjamin-Hall said in A critical look at Marvel vs. Capcom....:

Supposedly, implementing more complex logic in SQL queries is "too complex" and "not generic" and "can't be reused".

You really want to avoid doing a lot of string manipulation work inside the database. They're really not very good at that sort of thing. Pass them around, yes, match against them, yes, modify them, hell no!

Sure. I agree. A place for everything. But when you're trying to construct a proper user object and to know what permissions they have on which clients, being able to do a joined query (they all key off of the user id) to get that all in one go seems a lot more efficient than

Get the user.
Query the permissions table for the clients and permissions.
Query the clients table for the details about the clients (often several clients at 1 per round trip querying by client id)
assemble that all into a single object on the server.

And similarly for all 1:many (or many:many) relationships.

dkf

@Benjamin-Hall Where possible, have views (or stored procedures) to build the general things you want out of the actual tables you have, and don't use SELECT * in any production code. Then, you can keep your code going even if the underlying tables need to change; the view can reconstruct anything that was gone and you ignore anything added. If you're losing information you were using and don't have a way to get it back, you've got bigger problems!

Benjamin Hall

@dkf said in A critical look at Marvel vs. Capcom....:

@Benjamin-Hall Where possible, have views (or stored procedures) to build the general things you want out of the actual tables you have, and don't use SELECT * in any production code. Then, you can keep your code going even if the underlying tables need to change; the view can reconstruct anything that was gone and you ignore anything added. If you're losing information you were using and don't have a way to get it back, you've got bigger problems!

First you'd need to have views. I'm not sure our version (ie past EOL Mysql with some extra extensions) really supports those. I remember having nasty nasty issues with parameterized reading from a view in MySQL a few years back.

And we're getting the data we need (and aren't generally doing select *), just rather inefficiently by doing what RDBSs do best (relational logic and set operations) using loops in the back-end server code rather than just writing a more complex query to begin with.

dkf

@Bulb said in A critical look at Marvel vs. Capcom....:

Seriously? I've used SQLite from many languages—though never Java—and it had binding by name in all of them. And it's even better in TCL (SQLite started out as a TCL plugin) where you just mention the variables in the {}-quoted query and it does the needful. Excellent language integration.

That style was forced into being used in the other modern database connectors, on the grounds that it was widely reckoned to be a Good Thing That Works Well In Practice, and any DB that has problems with that (*cough*MySQL*cough*) needs its client-side driver to handle the details because actual client code really shouldn't.

Dynamically-generating SQL is ever so error prone: I advise all to not do it if they can possibly avoid it.

Bulb

@PleegWat said in A critical look at Marvel vs. Capcom....:

Programmer's Guide

Supplement to the Oracle Precompilers Guide

Programmer's Guide

I've noticed Oracle has those things back in the day. There are other good reasons not to touch Oracle with a 10 foot pole though.

@Kamil-Podlesak said in A critical look at Marvel vs. Capcom....:

Funny thing is that this actually exists and was widely used in 90s. Usually known as "static SQL", it even has the named bindings.

I know it existed, but I don't think it was used that widely. What I've seen (only in passing) was also specific to one database and kinda weird.

Problem is that the very idea of integrating database access as a preprocessor with compiler is just a bad idea in 21st century and when combined with extremely bad asinine tooling, it is no wonder it just died.

The idea itself is a good one. It is the compiler and database-specificity that makes it impractical.

Many of the ORMs also sort of do it, but with query builders, because most current languages don't have a sensible interface for adding language extensions.