Diagramming Tool For SQL Select Statements 156
alxtoth writes "Snowflake is a new BSD-licensed tool that parses SQL Select statements and generates a diagram. It shows parts of the underlying SQL directly in the diagram. For example: x=30, GROUP BY (year), SUM (sales), HAVING MIN (age) > 18. The primary reason for the tool was to avoid Cartesian joins and loops in SQL written by hand, with many joined tables. The database will execute such a statement, if syntactically correct, resulting in runaway queries that can bring the database down. If you sit close to the DBAs, you can hear them screaming... "
So, an alpha project for what exactly? (Score:5, Interesting)
Doesn't name WHICH RDBMS, and then you throw SQL at it? So what? For DB2 we have a thing called "Visual Explain" which NOT ONLY does this, but is free, provided by IBM, but also shows you other things like whch index is being used for each step, etc.
This is news? This isn't even worth a second look!
Existing tools (Score:3, Interesting)
Most PostgreSQL users don't seem to use the existing, and superior, tools like EXPLAIN, EXPLAIN ANALYZE, PgAdmin-III's graphical explain, etc. I'm sure the same is true for users of many other databases.
It's not like these tools are particularly difficult to use or understand. No training is required, though being willing to think and read a little documentation helps if you want to get the most out of them. Understanding at least vaguely how databases execute queries is handy for any database user anyway. The same understanding is required to get anything useful out of this just-posted tool.
Anyway, as I've noted elsewhere the exiting tools for this do a much better job due to integration with the RDBMS and superior knowledge of how the DB will execute the query.
Re:your programmers shouldn't be writing SQL (Score:3, Interesting)
Business logic (for the most part) is done on the server-side with PL/SQL packages, while the application itself is a Java fat client running on a Citrix cluster.
Before you make statements about keeping business logic separate from the database, this situation works well for this application, as it allows for less client-server communication, easier handling of commits and rollbacks, and much faster data access. A bonus is that when a severity 1 case is raised that is related to business logic, it doesn't require a long system outage.
The production server has read-only access for standard developers, and a logged full access account for support (and senior developers).
Every code change is reviewed by one or more senior developers to ensure it won't break existing functionality or contains (as you put it) "crapness".
From your comments I take it you are a DBA and have had bad experiences with poor programmers. In your case, maybe what you've suggested is a decent option for you, but I really don't think you should be stating it as the "right way".
As always, YMMV.
Comment removed (Score:5, Interesting)
Re:Bring a database down? (Score:3, Interesting)
AHAHAHAHAHAHAHAHAHA. Since I do run terabyte-sized databases, I'll contradict you - poor queries _can_ tank a server, even with small tables, if the query is poor enough. While it technically may be running, if nobody else can access it, then for practical purposes the server is down. And never underestimate the ability of one user with enough knowledge to be dangerous, to spread that selfsame query across as many people as possible.
Re:your programmers shouldn't be writing SQL (Score:3, Interesting)
Counterargument: Less readable code, as it's split into two places.
Counterargument: Harder to maintain - more upgrades will require the database to be revved as opposed to only the application, and synchronization between the two becomes more of an issue.
(Granted, IRL there needs to be robust infrastructure for database upgrades and downgrades no matter what -- but making previously code-only minor patches impact both components doesn't necessarily make things easier).
Yup; that is indeed a good reason to use stored procedures or views.
Of course the DBA will write better queries; that's why I advocate making DBA review mandatory for code changes impacting the data access layer. In shops with a good DBA, the programmers will come to the DBA first when they have a complex query to write anyhow; that's what happens where I'm at presently. (Our DBA is a rockstar, incidentally poached from my last employer, and very well-respected; at that last job, however, we had a CEO's-college-buddy incompetent before we had the rockstar, and I'd have hated to see him hold the power your workflow would grant).
From the perspective of the programmers writing the data access layer (you're doing a proper tiered application with business logic and data access broken off from each other, right?), they need to worry about interfacing with the DB no matter what; your proposal reduces their scope considerably (by making the code they maintain effectively into a collection of nearly-opaque stubs referencing logic stored elsewhere), but certainly doesn't eliminate the relevant work from development's domain.
I'm largely playing Devil's Advocate here: What you're advocating is a good workflow, but I think that calling it the only good workflow is a serious misrepresentation -- the problems it addresses can be resolved through other means, and at least some of the benefits are two-sided.