Journal Chacham's Journal: Rant: Programmers use database as fancy flat files. 38
We're using SQL Server (2005, currently), we're also using Toplink. Toplink does not support IDENTITY COLUMNs. So, the Java team here decided that we are adding a TABLE to hold all "sequences", that is, one row per sequence with a BIGINT stating what we're it's up to.
I have never seen a full user/security implementation in the database. Databases have supported them for years, and does it very well, with logging, fine-grained control, and it is guaranteed to work. The attitude, however, is "User's in the database?" Manager's won't have it. "No way. We'll give an application id and handle it in code." Security in the database? The programmers scoff. "It's so hard to change, the application already checks security, no reason to implement in the database too." How about relational integrity? They "let" DBA's get away with it, but for the most part, noone wants it. "Why restrict the data that can go in, and hurt loads and cause more errors. The application knows what to use." Here i see it yet again with IDENTITY. "Our tool doesn't support it, so we'll implement it ourselves." But in Oracle itself which supports it you'll use it with a TRIGGER? Not if the programmers have their way. "I want to get the id myself and put it in the TABLE. Why fiddle with a RETURNING clause." At least their happy with Toplink on that one.
In general, programmers want complete control with nothing inhibiting them. So why use a database? Because it's a convenient way to store and retrieve data. IOW, it's just a fancy flat file. Leave it to programmers, and they will reimplement databases one java routine at a time.
Put a programmer in the database and he'll use TRIGGERs to implement code, use CURSORs due to not understanding set logic, and not use VIEWs, SYNONYMs, or PROCEDUREs to do any simple INSERTs. IOW, they take the easy route.
UI is usually a mess of code, because usage has to seem simple but is quite complex. They do magic in code that i'm not as good at. But when it comes to databases, why don't programmer's respect database coders and designers? Why won't they accept that we can do databases better than they can and just trust our decisions just like we respect theirs?
It's at times like this that i just give up. I give them whatever they want. I'll suggest that which is good, but i'm not going to fight a losing battle.
yep (Score:2)
been there and seen that.
working with an app right now that takes a bunch of data and drops it as xml into a single field. Each record is a couple id fields and then a single text field that holds a spreadsheet (literally) of stuff.
But I've seen everything you mention and it seems to be the predominant approach.
Re: (Score:1)
The question is why they do that? And, is it worth it to fight for the cause? Should studies be done? Or do as the programmer's want. Wait until it breaks. Then fix it.
Re: (Score:2)
Why is usually due to ignorance. They don't know that better options exist or why they exist. That's what it has looked like in the cases I have dealt with.
Fighting with it isn't even an option at times. If I have the ability to do so, I will. These issues are core to performance and security.
I think the studies have been done and view it primarily as education.
Saying all that, I've stepped into situations where the opportunities to do it right passed a long time ago. So we made do and worked to try an
Re: (Score:1)
Sounds about the same. It's good to know i'm not alone.
It always surprised me that our programmers didn't know more about our RDBMS.
Laziness, perhaps? Or ignorance?
When a software engineer finally understands SQL (Score:2)
I was that way once, when I was young.
Now I see the power though- to the extent that I create NT groups for the network admins to add new users to, and then only allow them access to views and stored procedures that are needed for their role. NOBODY has the right to touch raw data.
Re: (Score:1)
Nice. What made you change?
I wish i could refuse access. I wanted to give each programmer limited rights,. They said they'd use the application id instead. I said the application id will have no more rights, as it could only EXECUTE stored PROCEDUREs. They said they are using Toplink, and Toplink needs all the rights.
Why is it that the larger ther organization, the more they use nonsensical standards, and the less they use the true power of each application?
Re: (Score:2)
Primarily- having to manage my own development server, and then promote through the DBAs, forced me to run it their way.
Re: (Score:1)
So, you were just fashioned by the DBAs? And you agreed after doing that for a while?
Re: (Score:2)
Sort of- when working for the state, I had to be DBA of my own machine- my development server. When code was ready, it had to work after the DBAs promoted it for me- security was so strict I had no access to TEST machines, let alone production. So it was just easier to mimic their groups and security setup. Once I realized that it could also simplify my VBA code as well- by using trusted domains instead of application level security- it all just fell into place.
Re: (Score:1)
IOW, you had to try it to like it?
Definitely had that. And the only way to know which things will "click" after using them for a while, is to respect the experts. Or at least, that's how it worked for me.
Thanx for the explanation.
The DB that I work with (Score:1)
Is relatively nice. I mean asides from being dog slow since it was someone's first large DB. Very very well normalized, maybe a bit too well normalized. :)
Anyway, yah, access controls are there based upon user's domain accounts. Everything is done through stored procedures, apps don't ever touch the DB directly. It doesn't use the best access control methodology around, but it integrates with Active Directory well enough, so maintaining it isn't that bad.
Our ORM was done through this nifty technique cal
Re: (Score:1)
Originally i was against the SP approach. Why force the application to use them when it can do actual INSERTs itself? I was enlightened by someone, that if SP are the libraries, and users cannot INSERT directly:
1) The SPs are treated like an API, so there's no back door INSERTs.
2) Each action is only done in one place. (As opposed to have multiple conflicting routines becauses they forgot to update them.)
3) The DB is seen as holding objects, not just data.
It's a tad harder at first, but so much benefit, as
Re: (Score:1)
Another huge bonus:
Someone who actually knows DBs writes the SP and lays out the DB. Programmers just access it and get to treat it as "a big dumb data repository", just as they prefer to. :)
Re: (Score:1)
Oh, so true.
Though, even SP writers sometimes have a tendency to use CURSORs because they don't understand sets. Or temp TABLEs, because they haven't figured out how to make it one query.
Re: (Score:1)
The DB I work with uses cursors here and there, I know that is bad, but I don't grok DB fully enough to fix the problem without potentially making it worse, so the cursors stay.
Re: (Score:1)
Heh. Fair enough. :)
I totally agree, ... (Score:1)
...let those whose expertise lies in a certain area handle that area. That's inarguably the smartest way to go.
But what you're running into, boiled down, is that programmers are lazy. This is a good thing and a bad thing. It's good because it spurs us to automate things that are too much work to do manually. It's bad because those without discipline (i.e. an awful lot of programmers) will usually try to take whatever is the easiest way out. (In fact, programmers are so lazy, this (worst practice) is even co
Re: (Score:1)
Wow, i agree with everything you said. Well put!
One part though i'd like to see otherwise: And as far as database security, I don't know why I would create more than a single user -- one for the data access layer of code to log in to.
I see the application as acting on behalf of the user. It should block not al;lowed actions, not because the application doesn't allow it, but as a convenience to the user who can't do it anyway. In a sense, let the user log into the DB directly if he wants to. After all, the a
Re: (Score:1)
Maybe it's a matter of which thing one considers to be the center of the project universe, the application or the data. I guess DBA's would say the latter, and programmers the former. I'd be more inclined to see it your way for an app that really was nothing more than data entry screens. But less inclined in some of the kinds of apps I've worked on, where the real value-added portion of the application was in offering various visualizations of the data, and running what-if scenarios, graphically. For exampl
Re: (Score:1)
Maybe it's a matter of which thing one considers to be the center of the project universe, the application or the data.
Absolutely. But, i guess, i am trying to satisfy both. The application owns the UI, the DB owns the data. Whether the data supports the user experience, but the UI is the main point, or the UI supports the data (via access), but the data is the main point, would definitely be based on the person's poistion.
1) The ease and flexibility in creating users in the database is dependent on the db
Re: (Score:1)
I'm trying to see your POV on this, but to me the application is the UI plus the business logic plus the data access logic. So to me the application includes the DB. And the user owns the (their) data. And pitting one component of the app against another, such as the UI vs. the DB, in a fight for which controls what, is to me like the two pockets of one's pants arguing which gets to hold how much of the wearer's change.
Re: (Score:1)
I'm trying to see your POV on this, but to me the application is the UI plus the business logic plus the data access logic.
IOW, The database is just a data store.
And pitting one component of the app against another, such as the UI vs. the DB, in a fight for which controls what, is to me like the two pockets of one's pants arguing which gets to hold how much of the wearer's change.
If i believed that were true, i'd agree with you. But having been on both sides (started as a coder, moved to DBs nearly exclusiv
Re: (Score:1)
IOW, The database is just a data store.
Not even that -- the database is just an *external* data store. Just like in Computer Science how you learn about the choices of data structures, or internal storage, and what qualities (such as those expressed in Big O Notation) are possessed by which and which should typically be used in which circumstances, same for choices in external data storage and retrieval. Sometimes all your app needs is maybe a linked list and an XML file. Other times it might need a hash ta
Re: (Score:1)
So, you're the type of guy i'm ranting about. :)
Something to think about, that programmers who haven't made the move that you have prolly indeed don't appreciate anywhere near enough.
On second thought, i may be untruthful here. My first coding job used a database (VX-REXX and DB2/2). I hadn't the slightest what a DB really was then, but i learnt over time. Eventually, i moved to DBs exclusively because they just make sense to me. I tend to think it is a personality thing. I see the objects and delineation o
Can I read the data? (Score:1)
I am just a lowly user (abbreviation: LUser), so is the bottom line, I can read but not write?
OK with me. Let some clerk do the writing, the important thing is the reading.
Where I work, they apparently don't know how to just let me read, and not write, so I am under orders to not contact the database directly (in fear I will mistakenly issue the command: "FUCKUP all tables"), but I am allowed to use these toy programs that contact the database, then I can scrape their output.
Lovely.
Re: (Score:1)
Just FYI, due to my feeling on profanity, i have opted to ignore this message.
Re: (Score:1)
That bad, eh? Why, you, you, you BofH!
Re: (Score:1)
At least i'm not foeing [slashdot.org] people (right away). :)
Re: (Score:1)
Which was worse? The abbreviation, or the undocumented SQL command?
Or maybe the characterization of those who write to DB as "clerks"?
Re: (Score:1)
At this point, i have no idea what you are talking about.
Re: (Score:1)
Well, I was not sure what profanity you referred to, and if I repeated a profanity, you might not respond.
So I tried to refer to the profanities without repeating them. In the case of "clerk", I was joking, in case you were joking the whole time.
Really, it is hard for me to see the objectional profanity of my initial posting, though you certainly may. It may not pass your filter, and for all I know, one or two of your responses might be programmatically generated, so I might be talking to a Turing machine
Re: (Score:1)
I just saw profanity, and didn't read the comment at all.
one divided by naught. Hmm... one divided by nothing? That would be G-d.
Re: (Score:1)
I can write it without profanity, easily enough:
I am just a lowly user, so is the bottom line, you recommend I can read but not write?
OK with me. Let someone else do the writing, the important thing is the reading.
Where I work, they apparently don't know how to just let me read, and not write, so I am under orders to not contact the database directly (in case I mess it up by writing), but I am allowed to use these toy programs that contact the database, then I can scrape their output.
Lovely.
Re: (Score:1)
By all means, allow each user (that is allowed) direct access to the DB. Also have VIEWs and the like to make it easy. In my mind, the UI is a convenience, not a restricted path. UIs come and go, but the data remains the same.
Re: (Score:1)
That would be G-d.
I'm slightly offended. "God" is not a cuss word that needs to have its internal characters scrambled to lessen the shock of seeing it in print.
Re: (Score:1)
There are long-standing Jewish traditions about spelling G-d's name. Searching google for G-d will bring up many articles. This has nothing to do with shock.
Re: (Score:1)
I stand educated. I still find it offensive, but I accept that the offense is not intentional, and that what is a degree of denial of the Lord to one man is reverence for Him to another.