The Daily WTF: Curious Perversions in Information Technology

jsmith · 2006-03-22 Reply Admin

Anonymous:
jsmith:
I like how you state the any use outside of "indexing rows" is a hack, but down below you mention indexes can be used to implement data integrity (which is more of a hack than covering indexes could ever be).
I didn't revise this statement. They have two purposes. I would go as so far to say that an index and an unique index are different beasts, simply because the latter are usually automatically generated and destroyed and have extreme constraints placed on them as to index method used and the like.

Covering indexes simply index better than non-covering indexes.
No, they don't. Once I have all the information needed to index the database, any other data is excessive.

By that logic clustered indexes and fillfactors are beyond the basic concept of indexing, so they are hacks too.
Rather, they are implementation details. However, you're correct in that they're beyond the basic concept.

Anonymous:
So how would you define a hack?
Any usage that abuses the abstract data structure. For a relational database, that's probably reasonable enough. And they're all over the place. Your problem is that you're assuming hack == bad, but that's no tthe case.

Anonymous:
But, I said "reversible".
And who cares? The entire point of an RDBMS is that most operations are indeed reversible.

Anonymous:
I said "new" maintenance issues.
Compouding existing issues is an isuse in and of itself.

Anonymous:
Like all indexes, they introduce overhead. So, you never always use any indexes. If you never use them, then you are leaving a lot of tuning on the table. Since we often use them, when appropriate,
Which is a far cry from saying their the vendor's intended solution. They are one solution of many. There are many other solutions, all legimiate, all recommended, to RDBMS performance problems.

Covering indexes are being used for data retrieval. Just optimized one step beyond non-covering indexes.
And that optimization changes how the index is being used. It's no longer merely a pointer.

It's like the fundamantals of Object Oriented Programming: it doesn't matter how an ArrayList is implemented, what matters is what it's does.
Ahh, but we frequently do care about both. We care about all sorts of semantics about an ArrayList's implementation.

Having some sort of chip on your shoulder about doing so is a bit weird.
I don't in the least, beyond the normal early optimization one.

So you're saying that if the vendor simply implemented a brand new type of object whose purpose was specifically to reduce index-to-table lookups, then it would all be fine because "that's what it's supposed to do"? The only reason this is a hack is that they are called "indexes"? That goes back to a "the world revolves around me" position. You don't like covering indexes because they aren't "right".

Here is an interesting analogy:

Indexes are called "indexes" because they work like the indexes in these things we call "books". Well, a typical book index contains a keyword and a page number. If you were looking for how many pages reference the word "widget" in a given book, you could entirely answer that question from the index. Hey, look, database servers do that too!!!! Covering indexes don't abuse the abstract concept, the concept of covering indexes has been in books for hundreds of years.

2006-03-22 Reply Admin

I did this already between MySQL 5.0 and SQL Server. They both support stored procs.

The data layer exposes interfaces from System.Data, but it's a factory that switches to MySQL or SQL Server that actually does the work. I use switch statements where needed for the subtle differences (booleans and names of stored procs params are different a bit), but the creation and population of stored procedure parameters is done by calling a factory to produce them, and returning instances that support the IDbParameter, for example. I found it to be quite painless.

Here's code that demonstrates:

<font size="1">        public static void Connect()</font>

<font size="1">        {</font>

<font size="1">            if (_connector == DataConnector.MySql)</font>

<font size="1">            {</font>

<font size="1">                _cn = new MySqlConnection();</font>

<font size="1">                _cn.ConnectionString = "server=" + _databaseHost + ";database=" +</font>

<font size="1">                    _databaseName + ";uid=" + _databaseLogon + ";pwd=" + _databasePassword;</font>

<font size="1">                _cn.Open();</font>

<font size="1">            }</font>

<font size="1">            if (_connector == DataConnector.SqlServer)</font>

<font size="1">            {</font>

<font size="1">                _cn = new SqlConnection();</font>

<font size="1">                _cn.ConnectionString = "Server=" + _databaseHost + ";uid=" + _databaseLogon + ";pwd=" +</font>

<font size="1">                    _databasePassword + ";Initial Catalog=" + _databaseName;</font>

<font size="1">                _cn.Open();</font>

<font size="1">            }</font>

<font size="1">        }</font>

And some more..

 public static DataSet RunProc(IDbCommand command)
 {
 if (_cn == null || (_cn.State != ConnectionState.Open))
 {
 Connect();
 }

 IDbDataAdapter adapter = GetAdapter(command);
 DataSet data = new DataSet();
 adapter.Fill(data);
 return data;
 }

sebmol:
So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?

devdas · 2006-03-22 Reply Admin

ammoQ:
devdas:

I would say that treating the entire database as an object, and then having stored procedures being treated as accessor methods is probably the best thing you can do.

This "object" is the reference implementation of the God Object antipattern, right?
;-)

Nope.

The view to the world is that the database is an amorphous blob with data. It knows what the data should look like, but not the semantics of the data itself (This field is a numeric type is important, the fact that it represents the money in your bank account is not import to the database itself).

The database don't know anything more than it should. It is a large object, with lots of getters and setters, but it definitely does not do anything outside its domain (store valid and consistent relations of data).

The database does not implement business rules. It implements rules for data validation.

From the wikipedia link:

:
Instead, much of a program's overall functionality is coded into a single object.

The database object solves the data maintainance issue. It does not solve other parts of the application domain.

Application developers should not have to worry about the implementation of the storage system, tables and columns (or even if the tables exist). They need to be able to obtain data from the store, and put data into it, without worrying about where the data goes.

I would be happy to be enlightened why things should be otherwise.

2006-03-22 Reply Admin

firewireguy:

Don't be so short sighted.

My points are 1) vendor tie-in is overrated, esp if you're spending $100,000 + on software and hardware, that ties you in already, and 2) converting data would be just as big a headache as SPs, if not more so, and migrating apps etc. No DBA wants to go through what you described, converting MSSQL to Oracle, regardless of SPs.

Are you saying you use NO SPs, or just don't embed business logic in them, because they're different issues.

firewireguy:

I don't get the WTF in this article. It looks like it was code from a DB back end that no application programmer should have to touch - there are probably two or more layers of code on top of it. I would much rather use something like this using recordset objects than directly embedding SQL everywhere. Far easier to maintain.

I don't think a DB back end would be defining so much application-specific words, or SELECTs. Looks like an awful application design to me.

My 0.02

Dylan

2006-03-22 Reply Admin

jsmith:
So you're saying that if the vendor simply implemented a brand new type of object whose purpose was specifically to reduce index-to-table lookups, then it would all be fine because "that's what it's supposed to do"?

Certainly.

The only reason this is a hack is that they are called "indexes"?

Precisely. You're not using the data structure for what it was originally intended. It's meant to be a pointer to a lookup, no more, no less. The fact it's frequently useful from a performance standpoint to do so doesn't change the fact that such an action is a hack. It's not what the datastructure was meant to do.

You don't like covering indexes because they aren't "right".

I only don't like them when they're used inappropriately and have never said anything other than that. You seemd to anyway, suggest them as quite a regular solution and not as a performance optimization. That's the only thing they are, and it's critical to remember that.

If you were looking for how many pages reference the word "widget" in a given book, you could entirely answer that question from the index.

Actually, you may not be able to, because book indexes don't work like database indexes. I see your point however, the fact that a structure of pointers can be used for counting isn't a new concept, and is an acceptable use of such a strucutre.

Hey, look, database servers do that too!!!! Covering indexes don't abuse the abstract concept, the concept of covering indexes has been in books for hundreds of years.

That's a non-sequitur. Counting isn't covering, by your own definition.

2006-03-22 Reply Admin

It's a parameterized query. The database implicitly caches it in compiled form. After the first time it's used, running the query is just as efficient as calling a stored procedure. I'm a little curious why there's a PARAMS_FIRST and PARAMS_SECOND constant. On the databases that I've used, the place holder for a parameter is "?".

2006-03-22 Reply Admin

"the enterprisocity of an application is directly proportionate to the number of constants defined "

what theorem is this under? I don't recall this from college !

2006-03-22 Reply Admin

SELECT * doesn't guarantee any field order. To use it safely, you need to access fields by name rather than by position. If you still need the same fields, than except for the extra memory usage, additional columns should be irrelevant. Of course, if you are accessing SELECT * fields by position, you could be setting yourself up for a disaster.

2006-03-22 Reply Admin

Lets see you do a join? Also what happens if SqlColumns.PRODUCTS_ISACTIVE is not a column in SqlTables.PRODUCTS? Its still a runtime error. Its a little better but is it worth it?

So it doesn't solve world hunger, therefore it is worthless? I haven't used this trick for SQL specifically, but there are many other cases where it has proven to be invaluable. It's handy everywhere from referencing a window to displaying the value of an enumeration.

2006-03-22 Reply Admin

>In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!

The DB engine might find the results faster on the server, but if you only need data from one field on a 30 field table, how would returning all the fields across a network be faster?

2006-03-22 Reply Admin

Anonymous:

For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes). Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.

I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?

CAPTCHA: SOLDIER, I mean
CAPTCHA: salad

I've worked with DBA's like that for a very short while. They never wanted to work with me again when I throw the BULLSH*T flag at them and tell them that they'll lazy SOB's.

2006-03-22 Reply Admin

Well, really you should have:

public class SqlBits
{
 public const int BIT_ZERO = 0;
 public const int BIT_ONE = 1;

 public const int BIT_POSITION_ZERO = 0;
 public const int BIT_POSITION_ONE = 1;
 public const int BIT_POSITION_TWO = 2;
 // ...
}

public class SqlLetters
{
 public const string SPACE_CHAR = BIT_ZERO << BIT_POSITION_SEVEN +
 BIT_ZERO << BIT_POSITION_SIX +
 BIT_ONE << BIT_POSITION_FIVE +
 BIT_ZERO << BIT_POSITION_FOUR +
 BIT_ZERO << BIT_POSITION_THREE +
 BIT_ZERO << BIT_POSITION_TWO +
 BIT_ZERO << BIT_POSITION_ONE +
 BIT_ZERO << BIT_POSITION_ZERO;

 public const string A_CHAR = //... you get the idea
}

(There needs to be a vomit emoticon like Yahoo IM has...)

jsmith · 2006-03-22 Reply Admin

Anonymous:
jsmith:
So you're saying that if the vendor simply implemented a brand new type of object whose purpose was specifically to reduce index-to-table lookups, then it would all be fine because "that's what it's supposed to do"?
Certainly.

So, until someone names a technique, and you accept that name as a non-hack, it's a hack. Funny thing is that there is a name for a covering index, it's called a "covering index". A well accepted, widely used name. The only reason you won't release it from hack status is because you don't accept that name.

Anonymous:
The only reason this is a hack is that they are called "indexes"?
Precisely. You're not using the data structure for what it was originally intended. It's meant to be a pointer to a lookup, no more, no less. The fact it's frequently useful from a performance standpoint to do so doesn't change the fact that such an action is a hack. It's not what the datastructure was meant to do.

So, the evolution of any technology is always and forever, a hack? How about a mouse? Computers were not originally designed to be used with mice. Oh, wait, I forgot -- if you "accept" it, then it is absolved. BTW, indexes were created specifically to enhance performance. They really have no reason to exist other than to accelerate read operations.

Anonymous:
You don't like covering indexes because they aren't "right".
I only don't like them when they're used inappropriately and have never said anything other than that. You seemd to anyway, suggest them as quite a regular solution and not as a performance optimization. That's the only thing they are, and it's critical to remember that.

Both. They are used quite regularly as performance optimizations. From the beginning that's what this was about. Besides, you can't use the fact that a technology could be misapplied to categorize that technology as a hack. Maybe if it were likely to cause a level of confusion that often results in misuse. I figured that we were all professionals and it didn't need to be stated that any decision to index data would always be accompanied by an analysis of the costs and benefits and if it wasn't a net gian, it wouldn't be done.
Also, If I suggested them as "quite a regular solution and not as a performance optimization", then what did I suggest them for? My argument has been, from the beginning, that covering indexes are an excellent performance tool.

Anonymous:
If you were looking for how many pages reference the word "widget" in a given book, you could entirely answer that question from the index.
Actually, you may not be able to, because book indexes don't work like database indexes. I see your point however, the fact that a structure of pointers can be used for counting isn't a new concept, and is an acceptable use of such a strucutre.

:-)

Anonymous:
Hey, look, database servers do that too!!!! Covering indexes don't abuse the abstract concept, the concept of covering indexes has been in books for hundreds of years.
That's a non-sequitur. Counting isn't covering, by your own definition.

Come on.... It's trivial to modify the scenario to "finding out if a page is in the first or second half of a book". I wanted to keep the book example a bit more believable, so I used counting instead of some other read operation. Any type of lookup using the index key or bookmark information as the data is an analogy to a covering index.

jimolina · 2006-03-22 Reply Admin

I think the more 'enterprise' a system gets, the more WTF it gets. I took several ABAP training courses (the 'programming' language for SAP) and my jaw was perpetually dropped to the desk. Here I thought this 'enterprise' system was going to be so advanced, so perfectly architected (if that's a word) and developed by the best of the best. I thought I'd be learning top techniques for a top system. Oh. My. God.

It would take a 1000 page tome just to touch the surface of the abject ugliness behind the glossy corporate appearance of that system. For instance no SQL--it's all procedural loops. To do a Join on the Customers table from the Orders table, you instead nest a loop of the entire customers table inside the loop of the orders table (not the real table names--those are 4-digit cryptic German names, but that's another story).

Anyway, bottom line is that there is no santa claus. There is no "enterprise" system that's magically 'better'--better coded, better designed, etc, etc. than what you have.. If you have a system that does what it's supposed to for the users--then going 'enterprise' is probably going to be a few large steps backwards.

--Jim

2006-03-22 Reply Admin

Iago:
public class SqlBytes
{
 public const byte SqlByteZero = (SqlBits.SqlBitZero << 8) | (SqlBits.SqlBitZero << 7) | (SqlBits.SqlBitZero << 6) | (SqlBits.SqlBitZero << 5) | (SqlBits.SqlBitZero << 4) | (SqlBits.SqlBitZero << 3) | (SqlBits.SqlBitZero << 2) | (SqlBits.SqlBitZero << 1) | (SqlBits.SqlBitZero << 0);
 public const byte SqlByteOne = (SqlBits.SqlBitZero << 8) | (SqlBits.SqlBitZero << 7) | (SqlBits.SqlBitZero << 6) | (SqlBits.SqlBitZero << 5) | (SqlBits.SqlBitZero << 4) | (SqlBits.SqlBitZero << 3) | (SqlBits.SqlBitZero << 2) | (SqlBits.SqlBitZero << 1) | (SqlBits.SqlBitOne << 0);
 // ...snip...
 public const byte SqlByteTwoHundredAndFiftyFive = (SqlBits.SqlBitOne << 8) | (SqlBits.SqlBitOne << 7) | (SqlBits.SqlBitOne << 6) | (SqlBits.SqlBitOne << 5) | (SqlBits.SqlBitOne << 4) | (SqlBits.SqlBitOne << 3) | (SqlBits.SqlBitOne << 2) | (SqlBits.SqlBitOne << 1) | (SqlBits.SqlBitOne << 0);
}

And with 9 bits per byte, the computer will run 12.5% faster too!

2006-03-22 Reply Admin

Anonymous:
Thats not really bad.
In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!
-Ricky

Speed of response is hardly the only thing you should be looking at. Select * IS pretty bad.

2006-03-22 Reply Admin

public const string SELECT = " SELECT ";

I see the real WTF.

 " SELECT " should be an externalized string.

2006-03-22 Reply Admin

is there another way to select from a table besides "SELECT *"?

a major telecom company generally uses either the global "SELECT *" or its HUGE ADABAS tables, or the far-more-efficient "SELECT", followed by a complete list of all the columns in the table. and remember, these are tables with in excess of 400 columns.

we love job security.

2006-03-22 Reply Admin

Anonymous:
Iago:
public class SqlBytes
{
 public const byte SqlByteZero = (SqlBits.SqlBitZero << 8) | (SqlBits.SqlBitZero << 7) | (SqlBits.SqlBitZero << 6) | (SqlBits.SqlBitZero << 5) | (SqlBits.SqlBitZero << 4) | (SqlBits.SqlBitZero << 3) | (SqlBits.SqlBitZero << 2) | (SqlBits.SqlBitZero << 1) | (SqlBits.SqlBitZero << 0);
 public const byte SqlByteOne = (SqlBits.SqlBitZero << 8) | (SqlBits.SqlBitZero << 7) | (SqlBits.SqlBitZero << 6) | (SqlBits.SqlBitZero << 5) | (SqlBits.SqlBitZero << 4) | (SqlBits.SqlBitZero << 3) | (SqlBits.SqlBitZero << 2) | (SqlBits.SqlBitZero << 1) | (SqlBits.SqlBitOne << 0);
 // ...snip...
 public const byte SqlByteTwoHundredAndFiftyFive = (SqlBits.SqlBitOne << 8) | (SqlBits.SqlBitOne << 7) | (SqlBits.SqlBitOne << 6) | (SqlBits.SqlBitOne << 5) | (SqlBits.SqlBitOne << 4) | (SqlBits.SqlBitOne << 3) | (SqlBits.SqlBitOne << 2) | (SqlBits.SqlBitOne << 1) | (SqlBits.SqlBitOne << 0);
}

And with 9 bits per byte, the computer will run 12.5% faster too!

My bytes go to 11!

2006-03-22 Reply Admin

HEY!

db vendor independence? wtf?

If you aren't locked into a db vendor by blowing hundreds of thousands of dollars, YOU AREN'T ENTERPRISE!

Thank you for your time in this matter.

2006-03-23 Reply Admin

Ok, I agree, that this is more than just overdone.

But to answer your last sentence: Have you heard, that MySQL uses "LIMIT" instead of "TOP" ...

2006-03-23 Reply Admin

Amen! I'm a network guy, and the bane of my life is shitty little DB apps that work well on a LAN being implemented on a WAN. If the server that does the "SELECT *" is on the same LAN as the DB server then it doesn't affect me (its still crap though), but on a network it is poison.

2006-03-23 Reply Admin

I think if I found this code it would inspire a new form of SQL injection attack. This SQL causing the injection of my foot up the author's a$$!

2006-03-23 Reply Admin

The problem here is that the SQL words are all hard-coded, they should have been stored in a config file.

mlk · 2006-03-23 Reply Admin

Anonymous:
A great design indeed, if you want to write your SQL requests in klingon language.

Truly an Enterprise system! (despite the lack of XML)

Using this system, you could simply change the keywords, and bang you have XPath! Rar!

2006-03-23 Reply Admin

Vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line.

You forgot best-of-breed!

*smack*

2006-03-23 Reply Admin

hjweth:
Anonymous:
Alex Papadimoulis:
If you're looking at today's code and thinking, gee, shouldn't these be in stored procedures, or views, or something...

For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes). Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.

I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?

Well, once you start using stored procedures and foreign keys and triggers and other fancy bells and whistles like that, you run a real risk of actually enforcing data integrity, and nobody wants that as far as I can tell.

Thank you very very much. To DBAs who don't like to put 'business logic' in the database : WTF is a schema design all about ? Do you really design a database with no thought at all as to how it's going to actually be used?

Oh, and two words about triggers : referential integrity. It can be important, and unless you think your app programmers are as perfect as your DBAs ( note: it's called sarcasm, folks ) , you might want to guard against corrupting your data as much as possible. Of course, maybe you're using some half-assed database which doesn't include referential integrity support via triggers, or you just don't know how to do such things. I don't know. People build all sorts of crazy systems that end up being mission-critical.

2006-03-23 Reply Admin

No, you're not thinking enterprisey. It's:

public const string GIMMIE = " GIMMIE ";
public const string SELECT = GIMMIE;

Enterprisey my ass. Try

public const Object SELECT = new Object() { 
  public String toString() { 
    if (Enterprise.version>1.3 && Enterprise.version<1.5) 
      throw new EnterpriseException("SELECT is deprecated"); 
    else return GIMMIE; 
  } 
};

2006-03-23 Reply Admin

How I love this forum software. Sigh. I give up.

lofwyr · 2006-03-23 Reply Admin

Anonymous:

1) Stored procedures tie you to a particular vendor
2) Application logic in the database has to duplicate logic in the application
3) Horrible source code control (in every case I've witnessed)
4) Lack of OO design, hence the term "stored PROCEDURE"
5) Difficulty migrating to a different database vendor/version
6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record

1) Could very well be.
2) Now why would someone do this? If it's in the database, why duplicate it on another layer?
3) Don't see the logic here ... either you use source control or you don't.
4) Like OO is the end to all means ...
5) Like those database are so easy to replace. Different locking & transaction modells, anyone?
6) That is quite the boldest statement I've ever heard.

Anonymous:

Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.

And use a lot more ressources (with less performance) than without.

Anonymous:

I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end. But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems. All these points can, of course, be debated...

Not _can_, the are debateable. Database agnostic approach can have it's advantages, but all that you're doing is shifting the complexity of a given problem to another layer - and probably adding some (complexity) to it.

l.

2006-03-24 Reply Admin

I just have to say how inspiring I found this whole thing. I was moved to enhance it:
(pardon the syntax, I'm not a .net guy, consider it psuedocode in a language where you have an eval)

public const string SELECT = " SELECT ";
public const string STAR = " * ";
public const string FROM = " FROM ";

public const string SELECT_VAR_NAME = "SELECT";
public const string STAR_2VAR_NAME= "STAR";
public const string FROM_VAR_NAME= " FROM ";

public const string SPACE = " ";
String SELECT_STAR_FROM=eval("SELECT_VAR_NAME + STAR_VAR_NAME + FROM_VAR_NAME" );

...
public const string EVAL = "eval";
...
aaaaaaaaaaaaaaaaaaaah!

2006-03-24 Reply Admin

This is in many ways similar to code I see in my new job, except it's worse, much worse. The SQL string would actually look like this: (in Java)

public final String UPDATE_LOGON = new StringBuffer().append(SqlWords.UPDATE).append(SqlTables.CREDENTIALS).append(SqlWords.SET).append(SqlColumns.CREDENTIALS_LOGON_NAME). (etc....) .toString()

The reasoning behind this was that earlier versions of the java compiler was pretty darn stupid with regards to string concatenations, which is true, but still.. you gotta factor in the readability of such code when you decide to do such optimizations - thankfully today, the java compiler is much smarter and actually produces code that is faster than the above optimization, so I guess now we can optimize away the previous optimization.

2006-03-24 Reply Admin

???,????c???

?????????????

2006-03-29 Reply Admin

You can't really write a 100% generic data access layer unless it's a very trivial, read-only application (ie. list the tables in my database, pick a table, show the table columns and values etc...) and/or you use dynamic SQL all over the place essentially making your application a DAL generator at runtime. Essentially you'd end up writing a IDE for creating SQL queries against your database, and unless you're writing a database tool, it doesn't make sense.

Some reporting applications may use this kind of feature however, but usually you'd try to abstract your report designer to stick the domain you are working on, providing simpler expressions for the user to string together and evaluate the expressions at runtime.

For most scenarios, at some point you have to say what table/view/stored proc you are querying, and at some point you have to specify columns. Yes, it's very tedious, especially when the database schema changes.

However, you can use code generation against databases using generic templates. There are a ton of code generating programs that will build the DAL and BLL for you, then you can add custom code on top of that where the code generation can't do the job. CodeSmith is a good tool for this.

2006-03-29 Reply Admin

Anonymous:
Alex Papadimoulis:

If you're looking at today's code and thinking, gee, shouldn't these be in stored procedures, or views, or something...

For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes). Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.

I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?

..."you're starting to embed application logic in the database" as opposed to embedding SQL queries in the application code?

WTF?

Why not start doing what my company has started doing. We have always been required to use stored procedures but now we make constants for the stored procedure names and parameter names in the application. Actually we are currently "enterprising" our development process so all of these constants are externalized in a configuration file so that changes (like stored procedure name or parameter changes) can be made without recompiling the application.

All we have to do then is go into the code and modify the classes to handle any new data fields, make sure the data is validated by the application, make sure any new parameters added to the configuration file are used properly.

THEN we recompile the application.

Sincerely,

- SQL Server DBA (being seduced by the Dark Side of Oracle)

2006-03-29 Reply Admin

Anonymous:
..."you're starting to embed application logic in the database" as opposed to embedding SQL queries in the application code?

WTF?

Why not start doing what my company has started doing. We have always been required to use stored procedures but now we make constants for the stored procedure names and parameter names in the application. Actually we are currently "enterprising" our development process so all of these constants are externalized in a configuration file so that changes (like stored procedure name or parameter changes) can be made without recompiling the application.

All we have to do then is go into the code and modify the classes to handle any new data fields, make sure the data is validated by the application, make sure any new parameters added to the configuration file are used properly.

THEN we recompile the application.

Sincerely,

- SQL Server DBA (being seduced by the Dark Side of Oracle)

What the hell? If someone puts a single DB Query in the app code, they should be shot. This includes a SP name.

Ever hear of an ORM? Look it up...seriously...

SPs have their place, but they should not contain business logic.

2006-03-29 Reply Admin

Alex Papadimoulis:
Have you ever heard of ODBC? Or, perhaps, ADO / ADO.NET?

You are on a road to maintenance hell. If you are in the business of creating custom software, then unless you Invent Everything, you will depend on something else, be it a PDF-creator, Active Directory, File System, etc.

What you *should* be doing is designing your system so external dependency are kept in one logical layer: for example, the data access layer. This way, if you ever need to support a different vendor, it's simply a matter of editing the classes in your DAL and changing this. The time involved in doing this is magnitudes smaller than the time trying to invent some "generic data access layer".

Which is what you would get if you used something like Hibernate or ActiveRecord for Ruby (nHibernate for .NET). I know you love your SPs, but there are better, cleaner approaches.

What are you going to do when you need to optimize a query? You can't -- everyone does it differently. Orable uses comments, SQL Server uses keywords, DB2 uses [I have no idea]. So now your entire "generic layer" is useless.

This is yet another reason you need to be using stored procedure. Every real database [*] has them, and they all function the same way when you use something like ODBC or your own invented "abstract data layer." That's the whole point of stored procedures -- simplifed access to a complex database system.

[*] I do not acknowledge MySql as anything more than a toy. See http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx.

The point is, should you put any logic in the SPs or should it be outside the database? I am going to agree with the rest of the non-MS world and say "outside the db". The DB should be a receptacle, nothing more. No business logic should reside there. If you need one or two specific SPs for somehting silly, fine...I don't really care that much. But if you are going to use them to actually make business rules and things like that...you are just smoking crack.

And, speaking of this...the whole SP debate is one of those questions I ask when I am hiring someone for my consulting company. If they say "just create a SP", ..no thanks...

Not saying people who use them are stupid, but I have found they just don't understand complex systems enough to know why they are bad. Not enough real experience to know that SPs are not the way to get the job done.

Barnacle Wes · 2006-03-29 Reply Admin

Anonymous:

For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes). Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.

I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?

Yes, I have. I find the best tool for dealing with these sorts of DBAs to be a 2 foot piece of iron rebar with a nice duct tape handle for the API. Evil Bill Paul had another fine tool, a hardened steel spline about 4 inches in diameter and 10 inches long. 30 pounds of hardened steel, with those lovely serrated splines on the business end. It was known throughout the office as "the damn thing."

Coding "application logic" out in the application that could be done with a trigger is stupid, that's why triggers were invented. Ditto for the performance benefits of stored procedures, etc. Use the tools in the ways that make the application perform, not based on some idiot voodoo rules.

2006-03-29 Reply Admin

Barnacle Wes:

Coding "application logic" out in the application that could be done with a trigger is stupid, that's why triggers were invented. Ditto for the performance benefits of stored procedures, etc. Use the tools in the ways that make the application perform, not based on some idiot voodoo rules.

Triggers, stored procedures and the like are great when you are doing data related tasks (moving data from one db to another (i.e replication)) and all those common, day to day DBA tasks. If you put application logic into a stored procedure or trigger , you should just give up your programming rights. Seriously.

Maurits · 2006-03-29 Reply Admin

Anonymous:
If you put application logic into a stored procedure or trigger , you should just give up your programming rights. Seriously.

Go sit in the corner.

2006-04-01 Reply Admin

Anonymous:
Let's take this to n'th degree

public class SqlWords
{
public const string SPACE _CHAR= " ";

//...
public const string E_CHAR = "E";

//...
public const string L_CHAR = "L";

//....
public const string S_CHAR = "S";
}

public class SqlWords
{
public const string SELECT = SPACE_CHAR + S_CHAR + E_CHAR + //...

}

Perhaps that's n-2'th, but there's no way I'm going to hex of binary.

...
i'm still laughing...

2006-04-04 Reply Admin

Sadly enough, I used the corporate-eese at the beginning of this post for the beginning of a proposal for management. Literally:

<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on"><st1:City w:st="on">Enterprise</st1:City></st1:place> applications are vital for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>

With this paradigm in mind, <COMPANY Name>proposes to deliver a product ...

The boss word-smithed a few bullet points below that, but never blinked. Sad.

2006-04-11 Reply Admin

My company uses an application where the company has put ALL of their procedure code into fields in the db. So they call the procedure which in turn calls the database to see what it should do. Is this normal?!?!?!?!

2006-04-15 Reply Admin

???,????????????,????????

???????????????,??????????????,?????????????QQ:183808137

2006-04-16 Reply Admin

Actually, you don't need the separating spaces since the original has spaces surrounding each token. Of course, this means that concatenating them results in two spaces between tokens.

2006-04-19 Reply Admin

I know!

I mean, seriously - it's obviously:

public const string CHAR_E = "E";
public const string CHAR_L = "L";
public const string CHAR_S = "S";

!!

2006-04-26 Reply Admin

There is one good thing that happens when you use constants the way they are used in this WTF and that is that Java compilers do not check the syntactic correctness of your SQL when you compile. This way you at least can avoid typos.

"UDPATE * FORM FOO" will compile whereas

SqlWords.UDPATE + SqlWords.STAR + SqlWords.FORM + "FOO" will not (unless someone defines UDPATE and FORM)

ammoQ · 2006-04-26 Reply Admin

Anonymous:
There is one good thing that happens when you use constants the way they are used in this WTF and that is that Java compilers do not check the syntactic correctness of your SQL when you compile. This way you at least can avoid typos.

"UDPATE * FORM FOO" will compile whereas

SqlWords.UDPATE + SqlWords.STAR + SqlWords.FORM + "FOO" will not (unless someone defines UDPATE and FORM)

Use embedded SQL and a preprocessor (like the one Oracle offers) and it will not only check the syntax, but table names, column names etc. as well; and the code might be more readable, but this is rather a matter of taste (at least in Java; in C+SQL, it's no question).

2006-05-04 Reply Admin

Amusing. :) I guess one actual reason is to protect against spelling errors in embedded strings, since the compiler will catch an attempt to use the wrong constant. I do know it'd drive me insane though, and not just only because one of the constants in the snippet seems to be ... missspelled. Heh. I'm thinking of the first line in the second class, i.e. SqlQueries.SELECT_ACTIVE_PRODCUTS. I'm pretty sure that ought to end in "PRODUCTS" ... The irony! :D

2006-05-04 Reply Admin

Anonymous:
Alex Papadimoulis:
If you're looking at today's code and thinking, gee, shouldn't these be in stored procedures, or views, or something...

For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes). Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.

I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?

I'm not a DBA but just a practical developer with DB knowledge. I have used stored procedures (oracle, pl/sql) for years, but don't recommend them for anything but very simple logic and data handling. Indeed you are embedding application logic, and also spreading it out over multiple environments and languages. It will create a mess in a short time.
Views are, IMHO, the only exception.

Enterprise SQL

Leave a comment on “Enterprise SQL”