Re: Perl to protect database field name info

[dragonchild]

I don't think I'd ever want to jump off a bridge into a valley of cactuses. So, discussing the best technique for doing so isn't very relevant to me.

Your database and your users should NEVER NEVER NEVER interface. Your application should provide a useful set of abstractions mapped to your users' domain. Your application should provide ANOTHER useful set of abstractions mapped to your datastore's needs. In my experience, and the experience of several people I trust, there has never been a case where the two domains were identical. Ever.

Instead, you should think about what you want your users to be able to do. Present that to them. Then, you should think about how your data wants to be stored. Then store it like that. Your application exists, in part, to help map the two domains together. Don't abrogate your responsibility.

[ptum]

I agree with this. I'm in the middle of writing an internal web application using CGI::Application and I succumbed to the temptation of mapping my database tables directly to user functions 'to save time'. It worked OK at first, while I was still in the simple prototype stage, but I am starting to feel the pain now, as my customers happily suggest changes. Because I don't have a very thick layer of abstraction between my user's domain and the database domain, I am finding it very painful to make changes that affect the structure of my database tables, and those modifications create ripples which threaten to destroy my application.

Besides, you pretty much have to agree with someone who can correctly spell 'abrogate' and use it effectively in a sentence.

[punch_card_don]

OK, then could someone please write something more concrete on this infamous abstraction?!?!

I mean, just how abstract can you get in receiving "first_name = bob", cleaning it, and writing it to users.fname?

Thanks.

[dragonchild]

In your case where you have a firstname field that is going to be stored completely within the firstname column, the abstraction doesn't make any sense. And, if you never get any more complicated than that, then the abstraction layer I'm talking about is counter-productive. Seeing as about 50% of all programmers never work on applications that need this kind of abstraction, 50% of all programmers would be ill-served in using it.

Let's say, however, that you're in the other 50% - the one that does need the abstraction. But, you don't understand why. The reason why is very simple - because your requirements will change. When requirements change, you will receive requirements that are from the user's perspective and within the user's domain of understanding. If you have designed your application correctly, you will be able to change the user-visible form WITHOUT touching the underlying database tables. This is not possible if your database table is used to generate your form.

For example, let's say that your company is so successful that you expand into Indonesia. Your application was a big part of that success, so you're leading the charge. And you run into someone without a first-name (many Indonesians only have one name instead of the two that we have in the US or Europe). Now, because your database table generates your form, you cannot abstract away the lack of a firstname within your code - your users have to do it for you.

Rule #1: Users will always make mistakes. Your application should make it hard for them to make common mistakes. It should never require the user to account for your mistakes.

Let's say that you've managed to account for the issues in Indonesia. Now, you're moving along and your application is being sold to the Australian aborigines. You recently had space issues, so you contracted all those VARCHAR(255)'s down to MAX(colname) + 10. In the case of firstname, that was 43, rounded up to 50. Whoops! Aborigines can have extremely long firstnames. Now, while this would require a database change anyways to account for them, instead of having constraints in the application as well as the database, the users see the database error.

Rule #2: Users should always be presented with useful errors that they understand. You should never present a user with an error from anywhere other than the immediate layer that they are interacting with. An error from the database is good only if the user is knowingly interacting with the database. Otherwise, you should be providing the error they see.

Rule #3: Processing should stop as soon as possible. Errors should be detected as soon as possible and handled as close to the source as possible. So, if there is a length problem with a field, that length should be encoded in the user interface. If the UI is a web browser, the length should be checked in Javascript, Perl, and the database.

Let's talk about #3 some more. Once you have separated out what's acceptable for the UI from what's acceptable for the database, you have some really neat possibilities. For example, you can have different restrictions based on different requirements, all going to the same database. So, if firstnames can only be 30 characters in Finland, but they have to be 45 characters in Denmark, you can easily handle that. Alternately, your database can now be used for an application you write and a completely separate application that your friend writes. Both of these situations are very common in large businesses. (I have maintained more of these than I can count.)

Does this help or would you like more discussion on the topic? It might help if you looked up MVC on the wikipedia.

[ptum]

I'll take a stab at it, although you might want to take what I say with several pinches of salt, since I haven't done very well at this lately.

Suppose I have a simple web application that gathers information about a customer -- their name, address, billing preferences, etc. Suppose I also want to store that information in a relational database. I think what dragonchild is saying is that how you gather that information from the user ought to be the way that best fits their domain -- that is, you might gather some or all of that information in a way that is user-friendly to them, whether squeezing it all on a single screen so that they don't have to keep hitting 'submit' all the time, or maybe you will slowly gather the information from them, snippets at a time, so as not to annoy your user. However you do this, it ought not to be constrained by your database structure or how you implement it on the back end -- in fact, you ought to 'abstract' the two domains (maybe by building classes that reflect the user's domain but which 'save' themselves to the database whenever they have enough information to do so). To make use of your example, it may not make sense for the user to separate his first and last name just because your database has a field for fname -- he may prefer to enter his full name in and let your application figure it out.

One way to think of it is as though two people (maybe they don't like each other very well) are writing the application -- one is a front-end developer and the other works on the database side. Your class modules will provide an API for the front-end, and another for the back-end, so that either can change independently of the other, and not break anything. So you might have a Customer class, which has methods for setting its private data elements (like name) and which knows how to save itself to one or more database tables. If the front-end guy decides to accept the customer's name in four fields (first, middle, last, suffix) your class just needs a method that can handle all four of those arguments and make sense of them. Similarly, if the back-end guy decides to munge all four fields into a single database field and moves that field to another table while he's at it, your class needs to know how to save the customer name, but the front-end guy shouldn't have to worry about it.

I fear I may not be doing a very good job at describing this concept, and I suspect it is something of which I have an imperfect understanding. Can anyone help me out with this?

Update: This thread: Design help: OO Perl and db tables dealt with some of the issues you raise, a month ago or so.

[punch_card_don]

On further thought, I'm unclear on how what you describe is not the same thing as what I suggested.

OK, so , in concrete terms, I believe that what you're saying is that on the user side you name an input form element "xyz". In your application, you're going to run the input value through some regex's to strip dangerous characters. And then on the database side, you're going to write the cleaned value to my_table.column_foo. Yes?

Then how is that different (other than leaving out the regex cleaner step for simplicity) from scrambling column_foo into 'xyz' and outputting an html form element of that name, then unscrambling 'xyz' into "column_foo" and writing the cleaned value to it?