[Semi-OT] Views, Stored Procedures, and Class::DBI

[jgallagher]

I'm looking at moving to PostgreSQL from MySQL for both my personal projects and a few small projects at work. I'm not trying to start a "which database is better and why" discussion, so I'll move on quickly from that point. :-) Now, one of the big things Pg proponents have said all along is that it supports views, stored procedures, triggers, and the like. I, probably like many here, am completely self-taught with respect to database theory. I can design a normalized table structure, but I've never played around with any of the above.

So, of course, I go looking for explanations of both how and, more importantly, why/when to use these "advanced" features. It seems, like in many things, there is more than one way to do it. Most of the examples fall into two categories:

• Simplifying complex queries: views for static but complicated joins, stored procedures otherwise
• Presenting a unified front, either for a redesign of the table structure or for hiding the table structure from applications (a quick overview of this is at Applying Advanced Database Features, found via this node)

My concern is that both of these are mostly (or entirely) unnecessary if one is using a dynamic SQL generator like Class::DBI. Google pointed me to this weblog post refuting a separate article promoting the use of stored procedures. In particular:

"I'm now almost stored procedure 'free' for 8 months now, and I love the feeling. The reason is obvious: the stress of maintaining a lot of stored procedures, to write another stored procedure for each thing you want to do, is gone. Dynamic SQL is the future. (Dynamic SQL is generated on the fly by a generic piece of code which gets various data as input and generates a parametrized query from it.)"

I'm curious. Do you use database features like the above if you've got something as powerful as CDBI running your application? Would there be a situation where they would be useful? It seems that, unless you were just using views that "looked" like regular tables, you would have to use set_sql or some such to take advantage of any of the above. I'm not against that by any means, but it almost seems like CDBI wasn't really planning on its users using a lot of database-side processing like this. Thoughts?

[Corion]

In my (warped) view of history, Class::DBI was created to do two things - first to allow easy row-based manipulation of database entries, and second to allow triggers for databases which don't have them.

I recently started reading Celko's "SQL for Smarties", and he uses triggers and constraints quite a lot, which is news to me, but I think I understand the rationale behind it. Constraints are like taint mode in Perl - they prevent the database from containing data that violates the assumptions. Triggers are a nice thing to have as soon as you start going from row-oriented manipulation of your data (read-modify-write) to set-oriented manipulation (UPDATE foo SET bar=baz WHERE ...) - here, triggers are the only way to specify a separate action.

In the end I guess it depends on what you do. I wouldn't go as far and put stored procedures into the database - this means that the "actual" code is distributed over too many (two) components. But I really like specifying my reports in SQL, because SQL makes it so much easier to formulate aggregations than Perl.

[rir]

I wouldn't go as far and put stored procedures into the database - this means that the "actual" code is distributed over too many (two) components.

But when you start to have many components consolidating code into the database via stored procedures can simplify the code of many clients.

Perhaps my view is too abstract, but I find the implication that a database's schema and implementation is not code to be limiting. If these limits really exist then fine; but here the discussion is premised on no lock-in to a particular database. Do not needlessly tie a free hand.

Be well,
rir

[dragonchild]

First off, MySQL 4.0 supports views. (I'm wrong here.) MySQL 5.0 supports stored procedures, triggers and updateable views. As MySQL 5.0 is going to be released any day now, that canard is gone.

As for why you would use each feature, here's a quick primer:

• Triggers - these are used to do something whenever something else happens. Generally, they're used for maintaining data integrity with something outside the database or for some sort of referential integrity that standard foreign keys cannot do. My opinion is that if you are depending on them, you either don't trust your programmers or you build it wrong.
• Stored Procedures - these are little programs in your database. They're very useful when doing lots and lots of crazy stuff that's DB specific. I've used them and they're pretty nice, especially when you have multiple apps in multiple languages hitting the same database. Frankly, I don't work on those kinds of things anymore, so I don't use them.
• Views - These are great. Often, you have a table with a lot of information, but some of it is sensitive. So, you provide a view for those users who aren't allowed to see the whole thing. This is useful for applications, as well. If you can limit what an app is allowed to do, you limit what the attacker who cracked your app can do.

  Also, they're great for frequent joins. Let's say you have a join of three tables that you do on a regular basis. By providing a view for that join, you can reduce the query time because the DB will keep that resultset around as a temporary table.

[tilly]

Triggers ... My opinion is that if you are depending on them, you either don't trust your programmers or you build it wrong.

You say it like that's a bad thing. :-)

There are times when you really don't want to trust programmers. For instance one common use of triggers is for auditing changes. You don't want to be in a position of having a change in your financial data that you can't explain. Depending on your database (I've seen it demonstrated with Oracle), it is possible to set things up so that nobody - including the DBA - can make a change without it being audited. Sure, you can turn auditing off. But the fact that you turned it off will itself get logged.

Usually this would be overkill. But there are situations where you really do need to be able to trust, but verify to this level.

[mescanne]

I think you have it exactly right, but I'd like to expand on the example.

Imagine an organization that has a sizeable group in charge solely of the database -- including replication across many servers, rational backup schemes, auditing and authorizing changes, optimizing queries and maintaining performance, etc. -- and then a wide variety of other groups which both develop applications as well as users using a variety of SQL reporting tools. The entire exercise can get exceedingly complicated and it can be pretty clear how triggers and stored procedures can be immensely useful for the DB group to maintain control.

From a one database to one application relationship these tools don't seem to make as much sense.

Mark

[dragonchild]

You say it like that's a bad thing. :-)

*grins* It's not a bad thing. In fact, it's often the only recourse a DBA has, precisely for the kind of reason you're talking about.

[jgallagher]

If you create a view for a frequent join, what would be the best way to utilize that from CDBI? I would guess either (1) treating it as a separate table or (2) writing a "search view, map results into the 'real' table types" sub. Perhaps choose (1) if you don't have access to the base tables and (2) if you do?

[diotalevi]

So the views are updatable then?

[dragonchild]

That was one of the major features in MySQL 5.0 - the others being triggers, stored procedures, and the NDB table type.

[adrianh]

MySQL 5.0 supports stored procedures, triggers and ... views. As MySQL 5.0 is going to be released any day now, that canard is gone.

Depends what you want to use them for. See the MySQL Feature Restrictions. I'm not even sure it's a complete list since I'm pretty sure that triggers still don't fire on events caused by cascading foreign key actions - and that doesn't appear to be mentioned there.

The duck's still flying :-)

[mpeppler]

My experience is with Sybase, and that may color what follows...

Stored procs are great, and can be used to achieve a number of objectives. The first is speed - the proc is pre-compiled and pre-optimized, and the optimization of a complex multi-table join can take a significant amount of time. The second is access control - you give access to the proc, not to the underlying table, and so you control what DML a client app can execute. The third is modularity - you can hide some DDL changes within the proc architecture without having to affect the client apps (this is of course not a complete solution, but for some things it can work pretty well.)

I haven't used views as much, but they can achieve similar results.

I would advise against having dynamic SQL all over the place in your client code - this makes maintaining the database and the dependancies in the client code a nightmare. In addition the dynamic SQL is really quite difficult to optimize when you hit a bug in the DB where an optimization hint might be needed (such as index hints, abstract query plans, etc.). My experience here has been as a DBA for a team that develops with WebObjects, and the results are sometimes quite ugly.

This is of course the view from the DBA, not so much the SQL/app developer, but may still be of use to you.

Michael

[saberworks]

Most web applications have a single user accessing the database. When I say single user I mean a single DATABASE user. However, most databases are designed such that users are granted privileges. Check: Principle of Least Privilege.

Views are awesome. They let you define a query and treat that query as a table. So you can easily sort and filter on aggregate values and limit what certain users can see. It can severely reduce the amount of complex SQL that is sprinkled throughout your code - instead of multiple long join queries there are smaller ones that are easier to digest.

I don't think interacting with a view via Class::DBI would be beneficial, you don't update a view but rather the tables behind the view. People are mentioning updatable views, but to me that seems wicked because your view could contain aggregate data, and then what?

[tomhukins]

There's been some discussion about this out on the wilds of the Internet lately, including something by one of our monks at On Databases and the follow-up, Databases and Metadata.