Page 1 of 1

Database views

Posted: Fri Feb 13, 2009 11:53 am
by alex.barylski
I always thought Views were Stored procedures...I was just introduced to Views and they seem to essentially be a phrasebook pattern.

Instead of hammering out a complex query in the code you invoke a View instead.

I'm not sure I like the idea or dislike the idea yet, but I find the solution interesting. Certainly if one could eliminate SQL from their code almost entirely, that would be a bonus as it's one thing I hate doing.

This after spending the last few weeks implementing a data mapper, etc...

opinions?

Re: Database views

Posted: Fri Feb 13, 2009 12:06 pm
by josh
Views don't eliminate the design problem datamappers solve. We should probably team up or something, my data mapper has come quite a ways as well. But yeah views can eliminate the need for some complex SQL in some cases

Re: Database views

Posted: Fri Feb 13, 2009 2:16 pm
by alex.barylski
I guess my question really was, what are the benefits to using a DB view as opposed to using phrasebook pattern?

Essentially you store the SQL as prepared statements in an external file and use a lookup function to fetch the SQL and pass the query string to be evaluated.

Views are probably cached, whereas a query lookup in phrasebook is not possible, at least to that degree.

Re: Database views

Posted: Fri Feb 13, 2009 3:02 pm
by josh
Well yeah I don't know crap about phrasebooks really, but basically the way I think views differ is that they're updated when their owning tables are updated. If that's the case I'd assume there's a performance penalty at write time but a benefit at read time.

Re: Database views

Posted: Fri Feb 13, 2009 5:17 pm
by alex.barylski
From what I can tell...views offer greater performance. Because they are stored at the DBS level far more advanced caching can occur.

I'm not convinced they are a best practice but under certain circumstances they may be.

Re: Database views

Posted: Fri Feb 13, 2009 7:12 pm
by josh
It definitely shouldn't be your primary approach to structuring business logic, but is definitely better then representing business logic as SQL which Codd himself admitted is flawed.

Re: Database views

Posted: Sat Feb 14, 2009 1:19 pm
by alex.barylski
I don't think business logic can existing in a View, can it?

From what I can tell, a view is simply a SQL stored in a centralized location, whereas stored procedures one might implement business logic, which I definetely disagree with.

I was familiar with stored procs, but views were totally new to me until now. I always thought they were a conceptual technique, much like phrasebook.

What interests me most about views, is my OQL is somewhat limited due to the expressive capacity of a OO fluent interface. There are some instances where explicit SQL 'might' be required in which case it would be neat if I could write a view and invoke the view using OQL.

Re: Database views

Posted: Sat Feb 14, 2009 1:57 pm
by josh
I don't know what the capabilities of views are exactly, but I guess I mean don't encode business logic in the view definition. See thread where I debate stored procedures for my stance ( its all about shape vs behavior ). Basically your behavior should not be implemented as views.

Re: Database views

Posted: Thu Feb 19, 2009 12:50 am
by wei