All the Perl that's Practical to Extract and Report

SQL Presentation Preview

Ovid on 2003-04-07T16:21:20

Any and all feedback on the use strict 'sql'; presentation would be welcome. It still needs some work and naturally, since it's only slides, much of the context is absent. Plus, I biffed a few images and need to clean up the KPresenter HTML. Hopefully, I can get this done before Wednesday.

Miscellaneous notes for context:

  • Better SQL management can be too much overhead for small programs or systems.
  • The image with a copyright notice stated that I could use it if I attached said notice. I need to figure out what to do with the other images.
  • While phrasebooks can break if you change the database, a proper Persistent Object system can limit the necessary changes to a much smaller scope.
  • Proper management of SQL protects against minor database changes, not major ones (though it can often help).

The idea that I am working on is combining a very lightweight persistent object system with phrasebooks. This is hardly a novel solution, but what if the phrasebook SQL is built dynamically and cached to disk? Each object knows its table and column names and by running a registration system, each object can register its dynamic SQL via a registration class. However, in production, the registration class is not used and instead the cached copy of the SQL is used. If you need to change something in the database, you can update the information in one spot, reregister the changes and everything automatically picks it up. The SQL would be readily available to hand to a DBA and much less dynamically generated SQL would be required at runtime.

This would be very useful for building projects with much refactoring or redesigning an existing database, but might have less utility with a larger system that's already in place and fairly static. Also, trying to come up with a clean, simple API is going to be a challenge. Is it even worth it? Would anyone be interested in something like this?

Some of the POOP modules seem to have performance issues because they try to do so much. Currently, my core code is only about 400 lines, it's fast and wouldn't grow too much. I think it's a good idea, but it can easily be brought down by a poor implementation.

Slyness

petdance on 2003-04-07T19:35:56

It's always good to see a Disposable Heroes Of Hiphoprisy reference.

Re:Slyness

Ovid on 2003-04-07T19:54:03

You have made my day :)

Given that much of the context is left out (i.e., you don't get to here the presentation), did the slides make sense?

Re:Slyness

Ovid on 2003-04-07T19:54:39

s/here/hear/

Shoot!

Re:Slyness

petdance on 2003-04-07T21:11:03

I pretty much skimmed the slides. I'm interested in the Class::Phrasebook stuff, but not sure how I'd actually want to use it in real life.

As to the DHH reference, it's extra funny 'cause I sent geoffeg some mp3s from that album the other day...

Prepare-once queries?

dws on 2003-04-08T00:15:37

What about queries that you want to prepare once, then run multiple times with different parameter bindings?

A few comments

VSarkiss on 2003-04-08T01:19:16

Overall, I liked it, but I was a database/SQL guy before I was a Perl guy, so I do have some comments. Don't worry, I'm not going to be harsh. ;-)

  • Your point about not using select * and insert without a specific column list is very well taken. The problem you specify next, though, is that columns don't contain what they sound like they should. This is the same problem as mis-named variables, or abuse of globals in a Perl program. (Equally bad ideas, in other words.) However, it's not clear than any of the techniques you present later will fix this. If you're afraid of modifying the database, you may be equally afraid of modifying the class. "Let's just shove it in here" can be accomplished in any programming language, with any tool!
  • I like the use of phrasebooks, but do be aware that they're really stored procedures in a cross-platform disguise. The benefits you list (on slide 38) all accrue to stored procedures as well. Nonetheless, there are problems with them. First, knowing where to put the dividing line between the SQL and the rest of your code is something that I've found only comes with experience. I've seen many cases where a bad separation caused more headaches than it solved. And as you point out later, tests and good configuration management are key to making the technique work successfully. ("No, version 2.36 of the program needs version 3.85 of the SQL. Oh, but that needs version 16 of the data model. We're screwed.")
  • I'll be honest, I've never thought "avoid writing SQL" was a win. SQL is a non-procedural programming language that works really well for relational databases. A well-written SQL statement can be clearer and faster than a lot of the code you're showing. And as for performance, I've yet to see an OO-style database interface generator produce anything beyond trivial SQL. I realize this is similar to "assembly written by hand is always faster than compiled code", but in the case of OO databases -- as opposed to compiler technology -- I think it's still true.
  • Minor typo's: slide 14, last bullet point: "one's has" => "one has"; slide 33, last bullet: "propogate" => "propagate"

Now, it may not be clear from the above, but I really liked it. I think you make good overall points, and these are minor points I'm raising. But they may be raised by others as well, so be ready.

Re:A few comments

johnseq on 2003-04-08T13:57:29

A slightly different, possibly complementary approach to schema change management would be parsing the phrasebook SQL to identify the field/table name dependencies. If I change phone to home_phone, I just run my phrasebook through a SQL parser to determine effected queries, and optionally to rewrite them.

I like that approach (as opposed to inlining references to a fieldname registry) because it keeps the SQL a more readable, and the adoption curve lower.