Reply to: Mapping objects to a relational database...

[Cowboy Bob]

You did notice the winky face in my post, yes?

Basically what I'm getting at is that in some circumstances it makes sense to have business logic inside stored procs, and in other circumstances it makes sense to have it in the middle tier.

My last project was using the Spring/Hibernate model because that made more sense. In my current project we are using stored procs, and I believe it makes sense in this context too. Both were/are the correct decisions in the context of the respective projects.

Anyone who says business logic should only ever exist in one place or the other is wrong - be they DBA or developer.

[Joe Black]

But Bob, as any good DBA will tell you, the best place for business logic is in the stored procs...

[Cowboy Bob]

I'm a big Hibernate fan. It generates very clean and efficient SQL, and is very mature. It works on .NET as well as Java, so you Microsofties don't have to miss out on the fun anymore. Mix liberally with Spring to get full transactional integrity across database calls and you've got a killer framework.

Besides, I hate stored procs as they take too much power away from the developer and hand over too much power to the DBA. Giving the DBA too much power over a project is a *very* bad thing. Next thing you know, they'll be designing business logic instead of tweaking indexes and banging on about database security

[HankWangford]

Hmmmm

I think most of the people on here seem to work on hack job applications looking at some of the comments I've seen.

you complete cretin.......

[Joe Black]

Edited because arguing this is like banging your head against a wall

[Joe Black]

> Best top security practice is NO access to tables
Access only to views (select only, no insert or update) and SP's

if the data/business layer is compromised then likewise any SPs it has permission to access/execute are compromised as well, so it provides only a marginal gain

> Yes it adds an extra layer, but then the views and SP's are part of the data model.

they're not, the table properties and/or relationships form part of the design, SPs etc are merely the means of implementation

> Plus - if the data model changes or a table has a columns added its simpler to change an SP than to try and find the bit of complied SQL somewhere in an app which is inserting without a column list.

if the design is implemented correctly then it should either be automatically updated with the changes, or should throw an exception/not compile/indicate the source of any conflict...in any case it should be the driver driving the car, not the engine, the database should change with the design, not the other way round.

[Spacecadet]

I'm sticking with CSWD

Best top security practice is
NO access to tables
Access only to views (select only, no insert or update) and SP's

Yes it adds an extra layer, but then the views and SP's are part of the data model.

Plus - if the data model changes or a table has a columns added its simpler to change an SP than to try and find the bit of complied SQL somewhere in an app which is inserting without a column list.

[Joe Black]

I go with Hank on this one.

Why slap another layer of code on top of a design, e.g. TSQL, stored procs etc (in yet another language), when 90%+ of the time most data layers just add/update a couple of records.

If you're talking about analysing 30+ years of financial records, then maybe stored procs, a 'coding to the machine' philosophy makes sense for performance issues. For anything else I want a design, which when I change the design the design is reflected everywhere, not that I have to rummage about and change code, permissions, logic in umpteen places and however many languages...where design says can be up to 128 characters, but stored proc accepts only 32, and provides nothing more than a warning after the fact that the data was truncated.

A DB is an engine, it shouldn't be any more than that...any more than a DBA should be saying "no you can't have something called 'Yearly Sales', it has to be 'Yearly_Sales', or 'YRLYSLES'"...

Rant over...until the next time some 'DBA' won't allow me to create a database which best suits the design.

[HankWangford]

All I need is reflector! No more humans - they are all scum!

i always obfuscate....

[HankWangford]

Inline

I won't let any developers talk to the db unless it's via sprocs for this reason.

Ah your one of these DBA's who treat their DB better than their offspring)


You can write inline sql to use parameters thus avoiding the injection issue.

Let me guess you also have one business object and dal method per sproc? ZzzzzzZzzzzz, life is too short to code that crap

How is the inline method a mess? Granted when you have "SELECT bottle, boot, shop from PUBS...etc etc" hardcoded but with entity broker et al this isnt the case. The business entities are mapped directly from the db, hence strongly typed objects, no room for error there, whereas hand cranking these yourself can be a nightmare (creating numerous manual sqlparameters and giving one an incorrect sql data type is very easy)

I bet you get more bugs with your procs than I do with the generated sql.

Inline sql quries are cached in sql 2000 +

Results pay, elegant, beautifull time consuming hand cranked proc / dal / business entity layers do not.

Agreed asp.net has turned into a novice camp, but the old posters on the architecture board thona, frans, rob howard, scott hanslemann etc are good.

[hyperD]

I think SQL 2000 caches inline SQL now for performance, but I always use SPs for the reasons stated. And table order for transactions to minimize deadlocks.

I have to admit I've moved from a more purist OO to a more service level architecture since using .net.

I got a bit spooked at reading some of the earlier posts - makes me feel like a charlatan! Oooops, better not tell the clients....!

[HankWangford]

Inline vs SP's

I feel the pain whenever I see discussions on this topic.

there are a few options, Hibernate being quite popular in his area.

But, most 3rd party tools, such as hibernate/nhibernate, neo, etc, etc does add an additional layer, which may or may not be a good thing (cache..), and abstraction is of course good, but...

and this is a but...

have you seen the actual access code being spitted out of these type of frameworks?

as someone normally hired to get most juice possible of out database(s), and whos primary skill is database engineering/modelling/coding etc, etc.. I cry when i see that. Mind you, it all falls back to the actual requirements of whatever you are building, granted, and the benefits of abstraction, etc can outweigh "simple" matters such as efficient sql, shuffling large resultsets across the wire between the db and the middle tier, but me, i prefer fast performing sql using in/out params and return values where I can

Cache is king, but alas, a pain to maintain, and not all data is possible to cache (contact center, presence, and other highly transient data)...

a quick look on google with ORM will give you plenty of good pointers to research though.

just my 2c...

Fridthjof-G

Got o disagree, there is no major gain by using sp's over inline sql. There was a great debate on asp.net forums and over many weblogs about this. The performance diff is negligable especially in crud operations and certainly not worth the pain of maintaining a large number of procs in a large DB. 4 procs * 300 tables, 1200 SP's.....YAK. SP's are usefull for complex reporting and IMHO thats all


check out this thread http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

[f_eriksen]

Agree the generated code does suck.

Glad to hear Although I can, as said earlier, see the points and benefits, I've yet to meet an OO/OR freak to understand that efficient/good sql and data access code is beneficial/crucial, but hey, I shouldnt complain. when I get hired to fix up a systems poor performance it is not too hard to point a finger

Because I am paid by the day to do that!

a good point indeed.

As always, the holy grail is somewhere in the middle.

[f_eriksen]

Orm

I feel the pain whenever I see discussions on this topic.

there are a few options, Hibernate being quite popular in his area.

But, most 3rd party tools, such as hibernate/nhibernate, neo, etc, etc does add an additional layer, which may or may not be a good thing (cache..), and abstraction is of course good, but...

and this is a but...

have you seen the actual access code being spitted out of these type of frameworks?

as someone normally hired to get most juice possible of out database(s), and whos primary skill is database engineering/modelling/coding etc, etc.. I cry when i see that. Mind you, it all falls back to the actual requirements of whatever you are building, granted, and the benefits of abstraction, etc can outweigh "simple" matters such as efficient sql, shuffling large resultsets across the wire between the db and the middle tier, but me, i prefer fast performing sql using in/out params and return values where I can

Cache is king, but alas, a pain to maintain, and not all data is possible to cache (contact center, presence, and other highly transient data)...

a quick look on google with ORM will give you plenty of good pointers to research though.

just my 2c...

Fridthjof-G

[HankWangford]

Not True

Yeah but I don't trust anything like that. It's another piece of third party crap to maintain and work around bugs in.

If you write it yourself, you know how it works!

These OR mappers are mature and have been tested / used by many people giving more test coveridge than a test team in one company could ever hope to achieve.
Why re-invent the boring wheel of creating business entities and crud methods to map objects to your rdbms when you can have them generated. The scope for bugs is much less than a bored developer cutting and pasting. Saves me hours and companies are well impressed with the time saved to concentrate on 'visible' functionality