Another pre-wtf: Queries in tables
-
From the same minds that brought you [url="http://forums.thedailywtf.com/forums/t/24580.aspx"]using Offline Folders as a backup strategy[/url] (which, thankfully, never took off), comes Queries in Tables.
As with most hellish ideas, it was born of good intentions. We want to make maintaining and QAing the system's queries to be as easy as possible. For example, one might want to look over all the "UPDATE" statements at once to make sure they're using a particular field or table or whatever. Pretty much, have all of the queries in a certalized location so we can look at them at a glance.
Data Layer? hahahahahahahahah, oh don't be some academic. This is the Real World! Why, just last project I had to dismantle the data layer I built, and copy'n'paste the queries back into the code files where they belong.
You see, if we take all of the queries, and put them into a database table, then everything is all in one place. Instead of saying Execute("SELECT..."), we can say sql = Executre("SELECT sql from QueryTable WHERE query_id = 1"); Execute(sql);
That way, the QueryTable can have other flags, like is_insert, is_update, log_me. And then if we wanted to look at all the Update-type queries at once, there they are!
There is TOTALLY no downside to this. Okay, it will double the amount of DB hits-- and add a layer of difficulty onto the programmer every time they want to change the query-- and make it impossible to do a code search for the query text-- and add yet another layer of difficulty onto the programmer whenever they deploy code-- and make it impossible to use diff tools to look for query changes-- and will take all of they query code out of version/source control-- but aside from those little things, THERE'S NO DOWNSIDE AND ONLY MASSIVE BENEFITS OF KNOWING THE QUERY TYPE!
-
This sounds so terribly familiar...
-
I also store my queries in the database<br>some are called 'stored procedures' and others are called 'views' :)
-
@Lorne Kates said:
[...]
-- and make it impossible to use diff tools to look for query changes-- and will take all of they query code out of version/source control--
[...]
Why?
-
@Jonathan said:
@Lorne Kates said:
[...]
-- and make it impossible to use diff tools to look for query changes-- and will take all of they query code out of version/source control--
[...]
Why?
Because the actual query will be in the database, not in the code. So, for example, if someone changes which tables a query pulls from, there won't be a record of that in the code. And likely won't even be a record of the change in the database, beyond a "Last_Date_Changed" column.
-
Hmm, if only there were some sort of convention you could use in naming your stored procs in some predictable way...
-
@UrzaMTG said:
@Jonathan said:
@Lorne Kates said:
-- and make it impossible to use diff tools to look for query changes-- and will take all of they query code out of version/source control--
Why?
Because the actual query will be in the database, not in the code. So, for example, if someone changes which tables a query pulls from, there won't be a record of that in the code. And likely won't even be a record of the change in the database, beyond a "Last_Date_Changed" column.
It's only like that because their configuration management is as poor as their engineering. If I had to live with a system like this, I'd have all of the queries version controlled, and have some automation to easily blast the DB with an updated (or whatever version I needed) slate of queries.
-
@bjolling said:
I also store my queries in the database
some are called 'stored procedures' and others are called 'views' :)Retweet
-
@Jonathan said:
@Lorne Kates said:
[...]
-- and make it impossible to use diff tools to look for query changes-- and will take all of they query code out of version/source control--
[...]
Why?
Source Safe 6.0.
(And even if it could do that, it's being administered by people who only know Source Safe 6.0-- so it won't)
-
@bjolling said:
I also store my queries in the database<br>some are called 'stored procedures' and others are called 'views' :)
When I am using Sybase, I store them in tables called sysobjects and syscomments.
-
@bjolling said:
I also store my queries in the database
some are called 'stored procedures' and others are called 'views' :)Sure, but that doesn't really buy you very much...
sproc = Execute("SELECT sproc from SprocTable WHERE sproc_id = 1"); Execute(sproc);
-
@boomzilla said:
@bjolling said:
I also store my queries in the database
some are called 'stored procedures' and others are called 'views' :)Sure, but that doesn't really buy you very much...
sproc = Execute("SELECT sproc from SprocTable WHERE sproc_id = 1"); Execute(sproc);You wouldn't have to hit the database twice, though.
results = Execute("{call sproc_caller(42)}");
-
@Xyro said:
@boomzilla said:
@bjolling said:
I also store my queries in the database
some are called 'stored procedures' and others are called 'views' :)Sure, but that doesn't really buy you very much...
sproc = Execute("SELECT sproc from SprocTable WHERE sproc_id = 1"); Execute(sproc);You wouldn't have to hit the database twice, though.
results = Execute("{call sproc_caller(42)}");False.
caller = Execute("SELECT caller from CallerTable where id='sproc'"); results = Execute(caller);I tells ya'. The amount of short cuts on this site...just appalling.
-
@boomzilla said:
@Xyro said:
@boomzilla said:
@bjolling said:
I also store my queries in the database
some are called 'stored procedures' and others are called 'views' :)Sure, but that doesn't really buy you very much...
sproc = Execute("SELECT sproc from SprocTable WHERE sproc_id = 1"); Execute(sproc);You wouldn't have to hit the database twice, though.
results = Execute("{call sproc_caller(42)}");False.
caller = Execute("SELECT caller from CallerTable where id='sproc'"); results = Execute(caller);I tells ya'. The amount of short cuts on this site...just appalling.
This, too, is solvable.
results = Execute("{call sproc_caller_caller(1, 42)}");
-
And everyone (seems to be) ignoring both the security ramifications and the performance penalty of dynamic sql execution..
-
@Lorne Kates said:
sql = Executre("SELECT sql from QueryTable WHERE query_id = 1"); Execute(sql);
I'm looking forward to your stories about "for (sql in Execute(SELECT sql from QueryTable WHERE query_type = 'update')) Execute(sql);". That query belongs in the QueryTable as well, right?
Does your incarnation of SQL Server have any EVAL functionality that you could abuse to cut down the DB hits? :)
For extra fun, you could ask any dev around you to write a query that executes precisely all queries that do not execute themselves.
-
@beermouse said:
For extra fun, you could ask any dev around you to write a query that executes precisely all queries that do not execute themselves.
results = Execute("{call sproc_caller_caller_caller(calc_godel_number(sproc_caller_caller(1, 1)), ω, 42)}");
-
@beermouse said:
@Lorne Kates said:
sql = Executre("SELECT sql from QueryTable WHERE query_id = 1"); Execute(sql);
I'm looking forward to your stories about "for (sql in Execute(SELECT sql from QueryTable WHERE query_type = 'update')) Execute(sql);". That query belongs in the QueryTable as well, right?
Oh, did I forget to mention that part? We'll want to run all the update queries (inside a transaction, of course) to make sure they all only update the number of rows they're supposed to update.
No word on how the "expected rows" is supposed to be determined (never mind knowing which values to pass said queries).
(But this isn't a unit test, and these aren't stored procedures. Just so you know)
-
@Lorne Kates said:
@beermouse said:
@Lorne Kates said:
sql = Executre("SELECT sql from QueryTable WHERE query_id = 1"); Execute(sql);
I'm looking forward to your stories about "for (sql in Execute(SELECT sql from QueryTable WHERE query_type = 'update')) Execute(sql);". That query belongs in the QueryTable as well, right?
(But this isn't a unit test, and these aren't stored procedures. Just so you know)
That ... preemptively answered any questions I might have had; How is this coding rule enforced?
-
@beermouse said:
@Lorne Kates said:
@beermouse said:
@Lorne Kates said:
sql = Executre("SELECT sql from QueryTable WHERE query_id = 1"); Execute(sql);
I'm looking forward to your stories about "for (sql in Execute(SELECT sql from QueryTable WHERE query_type = 'update')) Execute(sql);". That query belongs in the QueryTable as well, right?
(But this isn't a unit test, and these aren't stored procedures. Just so you know)
That ... preemptively answered any questions I might have had; How is this coding rule enforced?
It isn't yet, and I'm hoping it'll just go away. So far I haven't found anyone who says "Yeah, that's a great idea! It'll solve so many problems, be super easy, and do things no other, better tool can do."
Not even the trolls. :|
